旧版报表、仓库
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 

532 lines
24 KiB

using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Http;
using Warehouse.DAL.TestDataDetail;
using Warehouse.WebApi.Model;
namespace Warehouse.WebApi
{ // 最终测试数据
[RoutePrefix("api/TestDataDetail")]
public class TestDataDetailController : BaseController
{
TestDataDetail db = new TestDataDetail();
//真实数据
[Route("QueryData")]
[HttpPost]
public IHttpActionResult QueryData(dynamic para)
{
// string bt = para.benging;
string et = para.endtime;
string workshop = para.workshop;
string wo = para.workorder;
string lots = para.serialno;
string pallet_nb = para.pallet_nbr;
string bt = para.begintime;
string sale_order = para.sale_order;
string containerno= para.containerno;
string check_nbr = para.check_nbr;
string lot= string.Empty;
if (lots == "")
{
lot = "";
}
else
{
string trim1 = Regex.Replace(lots, @"\s", " ");
lot = "'" + trim1.Replace(" ", "','") + "'";
}
var res = db.QueryData(lot, wo, bt, et, workshop, pallet_nb,sale_order,containerno,check_nbr);
return Json<dynamic>(res);
//return Json<dynamic>(new { AA = "aa", BB = "cc" });
}
[HttpPost, Route("QueryDataNew")]
public IHttpActionResult QueryDataNew(FinalPowerTestReportSearchModel model)
{
try
{
var serialNum = "";
if (!string.IsNullOrEmpty(model.SerialNum))
{
var serialNumGet = model.SerialNum.Split(new string[] { "\r\n", "\n" }, StringSplitOptions.RemoveEmptyEntries).ToList();
var serialNumList = serialNumGet.Select(x => x.Trim()).Distinct().Where(s => !string.IsNullOrEmpty(s)).ToList();
serialNum = string.Join("','", serialNumList);
serialNum = "'" + serialNum + "'";
}
//获取数据
var res = db.QueryData(serialNum, model.WorkOrder, model.TestTimeBegin,
model.TestTimeEnd, model.WorkShop, model.PalletNum, model.OrderNum,
model.ContainerNum, model.CheckNum);
//处理数据
var list = new List<dynamic>();
res.ForEach(x => {
list.Add(new {
ContainerNum = x.container_nbr.ToString() ?? "-",
PalletNum = x.pallet_nbr.ToString() ?? "-",
PackRank = x.pack_seq.ToString() ?? "-",
WorkOrder = x.workorder.ToString() ?? "-",
OrderNum = x.sale_order.ToString() ?? "-",
SerialNum = x.serial_nbr.ToString() ?? "-",
TestTime = x.wks_visit_date.ToString() ?? "-", //.ToString("yyyy-MM-dd HH:mm:ss")
TestMachineNum = x.wks_id.ToString() ?? "-",
PowerGrade = x.power_grade.ToString() ?? "-",
CurrentGrade = x.current_grade.ToString() ?? "-",
AppearanceGrade = x.exterior_grade.ToString() ?? "-",
ElGrade = x.el_grade.ToString() ?? "-",
FinalGrade = x.final_grade.ToString() ?? "-",
Pmax = x.pmax.ToString() ?? "-",
ISC = x.isc.ToString() ?? "-",
VOC = x.voc.ToString() ?? "-",
IPM = x.ipm.ToString() ?? "-",
VPM = x.vpm.ToString() ?? "-",
FF = x.ff.ToString() ?? "-",
RS = x.rs.ToString() ?? "-",
RSH = x.rsh.ToString() ?? "-",
EFF = x.eff.ToString() ?? "-",
Envtmp = x.env_temp.ToString() ?? "-",
Tmod = x.tmod.ToString() ?? "-",
BatteryChipManufacturers = x.cell_supplier.ToString() ?? "-",
SingleChipPower = x.cell_uop.ToString() ?? "-",
BatteryChipColor = x.cell_color.ToString() ?? "-",
BatteryChipGrade = x.cell_grade.ToString() ?? "-",
BatteryChipType = x.cell_type.ToString() ?? "-",
BatteryChipEfficiencyFile = x.cell_eff.ToString() ?? "-",
PalletStatus = x.pallet_status_desc.ToString() ?? "-",
BatteryChipCount = x.cell_qty.ToString() ?? "-",
RatedPower = x.EDGL.ToString() ?? "-",
ROI = x.fff.ToString() ?? "-",
CrystalType = x.crys_type.ToString() ?? "-",
BatteryChipSize = x.cell_size.ToString() ?? "-",
});
});
//返回数据
return Success(new
{
data = list
});
}
catch (Exception e)
{
//TODO 记录日志
}
return Failure("查询异常");
}
//九江正泰客户衰减数据
[Route("QueryDataFake")]
[HttpPost]
public IHttpActionResult QueryDataFake(dynamic para)
{
// string bt = para.benging;
string et = para.endtime;
string workshop = para.workshop;
string wo = para.workorder;
string lot = para.serialno;
string pallet_nb = para.pallet_nbr;
string bt = para.begintime;
string sale_order = para.sale_order;
string containerno = para.containerno;
string check_nbr = para.check_nbr;
var res = db.QueryDataFake(lot, wo, bt, et, workshop, pallet_nb, sale_order, containerno, check_nbr);
return Json<dynamic>(res);
//return Json<dynamic>(new { AA = "aa", BB = "cc" });
}
#region 下载
[HttpPost, Route("DownloadFinalPowerTestReport")]
public HttpResponseMessage DownloadFinalPowerTestReport(DownloadFinalPowerTestReportModel model)
{
try
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
hssfworkbook.CreateSheet("最终功率测试报表");
ICellStyle style = hssfworkbook.CreateCellStyle();
//设置单元格的样式:水平对齐居中
style.Alignment = HorizontalAlignment.Center;
ISheet sheet = null;
sheet = hssfworkbook.GetSheetAt(0);
IRow row = null;
#region 添加表头
var thList = new List<string> {
"柜号",
"托盘号",
"包装顺序",
"工单号",
"订单号",
"组件序列号",
"测试日期时间",
"测试机台号",
"功率档位",
"电流档位",
"外观等级",
"EL等级",
"最终等级",
"Pmax",
"ISC",
"VOC",
"IPM",
"VPM",
"FF",
"RS",
"RSH",
"EFF",
"Envtmp",
"Tmod",
"电池片厂家",
"单片功率",
"电池片颜色",
"电池片等级",
"电池片类型",
"电池片效率档",
"托盘状态",
"片源用量",
"额定功率",
"投入产出比",
"晶体类型",
"片源规格"
};
//设置第一列列宽
sheet.SetColumnWidth(0, 20 * 256);
//设置第一列之后的列宽
for (int i = 1; i < thList.Count; i++)
{
sheet.SetColumnWidth(i, 10 * 256);
}
var rowCount = sheet.LastRowNum;
row = sheet.CreateRow(rowCount);
//添加表头并设置样式字体居中
for (int i = 0; i < thList.Count; i++)
{
row.CreateCell(i).SetCellValue(thList[i]);
row.GetCell(i).CellStyle = style;
}
#endregion
foreach (var item in model.DownloadDataList.ToList())
{
var rowCount1 = sheet.LastRowNum + 1;
row = sheet.CreateRow(rowCount1);
row.CreateCell(0).SetCellValue(item.ContainerNum.Value);
row.GetCell(0).CellStyle = style;
row.CreateCell(1).SetCellValue(item.PalletNum.Value);
row.GetCell(1).CellStyle = style;
row.CreateCell(2).SetCellValue(item.PackRank.Value);
row.GetCell(2).CellStyle = style;
row.CreateCell(3).SetCellValue(item.WorkOrder.Value);
row.GetCell(3).CellStyle = style;
row.CreateCell(4).SetCellValue(item.OrderNum.Value);
row.GetCell(4).CellStyle = style;
row.CreateCell(5).SetCellValue(item.SerialNum.Value);
row.GetCell(5).CellStyle = style;
row.CreateCell(6).SetCellValue(item.TestTime.Value);
row.GetCell(6).CellStyle = style;
row.CreateCell(7).SetCellValue(item.TestMachineNum.Value);
row.GetCell(7).CellStyle = style;
row.CreateCell(8).SetCellValue(item.PowerGrade.Value);
row.GetCell(8).CellStyle = style;
row.CreateCell(9).SetCellValue(item.CurrentGrade.Value);
row.GetCell(9).CellStyle = style;
row.CreateCell(10).SetCellValue(item.AppearanceGrade.Value);
row.GetCell(10).CellStyle = style;
row.CreateCell(11).SetCellValue(item.ElGrade.Value);
row.GetCell(11).CellStyle = style;
row.CreateCell(12).SetCellValue(item.FinalGrade.Value);
row.GetCell(12).CellStyle = style;
row.CreateCell(13).SetCellValue(item.Pmax.Value);
row.GetCell(13).CellStyle = style;
row.CreateCell(14).SetCellValue(item.ISC.Value);
row.GetCell(14).CellStyle = style;
row.CreateCell(15).SetCellValue(item.VOC.Value);
row.GetCell(15).CellStyle = style;
row.CreateCell(16).SetCellValue(item.IPM.Value);
row.GetCell(16).CellStyle = style;
row.CreateCell(17).SetCellValue(item.VPM.Value);
row.GetCell(17).CellStyle = style;
row.CreateCell(18).SetCellValue(item.FF.Value);
row.GetCell(18).CellStyle = style;
row.CreateCell(19).SetCellValue(item.RS.Value);
row.GetCell(19).CellStyle = style;
row.CreateCell(20).SetCellValue(item.RSH.Value);
row.GetCell(20).CellStyle = style;
row.CreateCell(21).SetCellValue(item.EFF.Value);
row.GetCell(21).CellStyle = style;
row.CreateCell(22).SetCellValue(item.Envtmp.Value);
row.GetCell(22).CellStyle = style;
row.CreateCell(23).SetCellValue(item.Tmod.Value);
row.GetCell(23).CellStyle = style;
row.CreateCell(24).SetCellValue(item.BatteryChipManufacturers.Value);
row.GetCell(24).CellStyle = style;
row.CreateCell(25).SetCellValue(item.SingleChipPower.Value);
row.GetCell(25).CellStyle = style;
row.CreateCell(26).SetCellValue(item.BatteryChipColor.Value);
row.GetCell(26).CellStyle = style;
row.CreateCell(27).SetCellValue(item.BatteryChipGrade.Value);
row.GetCell(27).CellStyle = style;
row.CreateCell(28).SetCellValue(item.BatteryChipType.Value);
row.GetCell(28).CellStyle = style;
row.CreateCell(29).SetCellValue(item.BatteryChipEfficiencyFile.Value);
row.GetCell(29).CellStyle = style;
row.CreateCell(30).SetCellValue(item.PalletStatus.Value);
row.GetCell(30).CellStyle = style;
row.CreateCell(31).SetCellValue(item.BatteryChipCount.Value);
row.GetCell(31).CellStyle = style;
row.CreateCell(32).SetCellValue(item.RatedPower.Value);
row.GetCell(32).CellStyle = style;
row.CreateCell(33).SetCellValue(item.ROI.Value);
row.GetCell(33).CellStyle = style;
row.CreateCell(34).SetCellValue(item.CrystalType.Value);
row.GetCell(34).CellStyle = style;
row.CreateCell(35).SetCellValue(item.BatteryChipSize.Value);
row.GetCell(35).CellStyle = style;
}
var date = DateTime.Now.ToString("yyyy-MM-dd");
Random r = new Random();
int num = r.Next(100);
var path = HttpContext.Current.Server.MapPath($"~/最终功率测试报表/{date}最终功率测试报表{num}.xls");
//创建excel文件
if (Directory.Exists(HttpContext.Current.Server.MapPath($"~/最终功率测试报表")))
{
FileStream file = new FileStream(path, FileMode.Create);
//把数据写入excel文件中
hssfworkbook.Write(file);
file.Close();
}
else
{
var files = Directory.CreateDirectory(HttpContext.Current.Server.MapPath($"~/最终功率测试报表"));
FileStream file = new FileStream(path, FileMode.Create);
//把数据写入excel文件中
hssfworkbook.Write(file);
file.Close();
}
//下载
string fileName = Path.GetFileName(path);
var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
HttpResponseMessage resp = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new StreamContent(stream)
};
//类型:下载
resp.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = fileName
};
//application/octet-stream --扩展名全类型格式
resp.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
resp.Content.Headers.ContentLength = stream.Length;
return resp;
//return Success("下载完成");
}
catch (Exception e)
{
}
return new HttpResponseMessage(HttpStatusCode.NoContent);
}
[HttpGet, Route("DownloadFinalPowerTestReportGet")]
public HttpResponseMessage DownloadFinalPowerTestReportGet()
{
try
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
hssfworkbook.CreateSheet("最终功率测试报表");
ICellStyle style = hssfworkbook.CreateCellStyle();
//设置单元格的样式:水平对齐居中
style.Alignment = HorizontalAlignment.Center;
ISheet sheet = null;
sheet = hssfworkbook.GetSheetAt(0);
IRow row = null;
#region 添加表头
var thList = new List<string> {
"柜号",
"托盘号",
"包装顺序",
"工单号",
"订单号",
"组件序列号",
"测试日期时间",
"测试机台号",
"功率档位",
"电流档位",
"外观等级",
"EL等级",
"最终等级",
"Pmax",
"ISC",
"VOC",
"IPM",
"VPM",
"FF",
"RS",
"RSH",
"EFF",
"Envtmp",
"Tmod",
"电池片厂家",
"单片功率",
"电池片颜色",
"电池片等级",
"电池片类型",
"电池片效率档",
"托盘状态",
"片源用量",
"额定功率",
"投入产出比",
"晶体类型",
"片源规格"
};
//设置第一列列宽
sheet.SetColumnWidth(0, 20 * 256);
//设置第一列之后的列宽
for (int i = 1; i < thList.Count; i++)
{
sheet.SetColumnWidth(i, 10 * 256);
}
var rowCount = sheet.LastRowNum;
row = sheet.CreateRow(rowCount);
//添加表头并设置样式字体居中
for (int i = 0; i < thList.Count; i++)
{
row.CreateCell(i).SetCellValue(thList[i]);
row.GetCell(i).CellStyle = style;
}
#endregion
//获取数据
var res = db.QueryData("", "", "",
"", "", "", "",
"", "");
foreach (var item in res.ToList())
{
var rowCount1 = sheet.LastRowNum + 1;
row = sheet.CreateRow(rowCount1);
row.CreateCell(0).SetCellValue(item.container_nbr.ToString());
row.GetCell(0).CellStyle = style;
row.CreateCell(1).SetCellValue(item.pallet_nbr.ToString());
row.GetCell(1).CellStyle = style;
row.CreateCell(2).SetCellValue(item.pack_seq.ToString());
row.GetCell(2).CellStyle = style;
row.CreateCell(3).SetCellValue(item.workorder.ToString());
row.GetCell(3).CellStyle = style;
row.CreateCell(4).SetCellValue(item.sale_order.ToString());
row.GetCell(4).CellStyle = style;
row.CreateCell(5).SetCellValue(item.serial_nbr.ToString());
row.GetCell(5).CellStyle = style;
row.CreateCell(6).SetCellValue(item.wks_visit_date.ToString());
row.GetCell(6).CellStyle = style;
row.CreateCell(7).SetCellValue(item.wks_id.ToString());
row.GetCell(7).CellStyle = style;
row.CreateCell(8).SetCellValue(item.power_grade.ToString());
row.GetCell(8).CellStyle = style;
row.CreateCell(9).SetCellValue(item.current_grade.ToString());
row.GetCell(9).CellStyle = style;
row.CreateCell(10).SetCellValue(item.exterior_grade.ToString());
row.GetCell(10).CellStyle = style;
row.CreateCell(11).SetCellValue(item.el_grade.ToString());
row.GetCell(11).CellStyle = style;
row.CreateCell(12).SetCellValue(item.final_grade.ToString());
row.GetCell(12).CellStyle = style;
row.CreateCell(13).SetCellValue(item.pmax.ToString());
row.GetCell(13).CellStyle = style;
row.CreateCell(14).SetCellValue(item.isc.ToString());
row.GetCell(14).CellStyle = style;
row.CreateCell(15).SetCellValue(item.voc.ToString());
row.GetCell(15).CellStyle = style;
row.CreateCell(16).SetCellValue(item.ipm.ToString());
row.GetCell(16).CellStyle = style;
row.CreateCell(17).SetCellValue(item.vpm.ToString());
row.GetCell(17).CellStyle = style;
row.CreateCell(18).SetCellValue(item.ff.ToString());
row.GetCell(18).CellStyle = style;
row.CreateCell(19).SetCellValue(item.rs.ToString());
row.GetCell(19).CellStyle = style;
row.CreateCell(20).SetCellValue(item.rsh.ToString());
row.GetCell(20).CellStyle = style;
row.CreateCell(21).SetCellValue(item.eff.ToString());
row.GetCell(21).CellStyle = style;
row.CreateCell(22).SetCellValue(item.env_temp.ToString());
row.GetCell(22).CellStyle = style;
row.CreateCell(23).SetCellValue(item.tmod.ToString());
row.GetCell(23).CellStyle = style;
row.CreateCell(24).SetCellValue(item.cell_supplier.ToString());
row.GetCell(24).CellStyle = style;
row.CreateCell(25).SetCellValue(item.cell_uop.ToString());
row.GetCell(25).CellStyle = style;
row.CreateCell(26).SetCellValue(item.cell_color.ToString());
row.GetCell(26).CellStyle = style;
row.CreateCell(27).SetCellValue(item.cell_grade.ToString());
row.GetCell(27).CellStyle = style;
row.CreateCell(28).SetCellValue(item.cell_type.ToString());
row.GetCell(28).CellStyle = style;
row.CreateCell(29).SetCellValue(item.cell_eff.ToString());
row.GetCell(29).CellStyle = style;
row.CreateCell(30).SetCellValue(item.pallet_status_desc.ToString());
row.GetCell(30).CellStyle = style;
row.CreateCell(31).SetCellValue(item.cell_qty.ToString());
row.GetCell(31).CellStyle = style;
row.CreateCell(32).SetCellValue(item.EDGL.ToString());
row.GetCell(32).CellStyle = style;
row.CreateCell(33).SetCellValue(item.fff.ToString());
row.GetCell(33).CellStyle = style;
row.CreateCell(34).SetCellValue(item.crys_type.ToString());
row.GetCell(34).CellStyle = style;
row.CreateCell(35).SetCellValue(item.cell_size.ToString());
row.GetCell(35).CellStyle = style;
}
MemoryStream ms = new MemoryStream(); //创建内存流用于写入文件
hssfworkbook.Write(ms);//将Excel写入流
ms.Flush();
ms.Position = 0;
HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
result.Content = new StreamContent(ms);
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = "Data.xls"
};
return result;
}
catch (Exception)
{
}
return new HttpResponseMessage(HttpStatusCode.NoContent);
}
#endregion
}
}