public FileResult ExportAmessbleDailyInputDataForExcel(QAReportSearchVM search)
{
var stream = new MemoryStream();
var fileName = PathHelper.SetGridExportExcelName("");
search.Count = 10000;
OQCReportExcel detailData = GetOQCReportForExportExcel(search);
if (detailData.SumData.Count != 0)
{
using (var excelPackage = new ExcelPackage(stream))
{
var worksheetYield = excelPackage.Workbook.Worksheets.Add("Yield");
var worksheetDetails = excelPackage.Workbook.Worksheets.Add("Details");
string title = this.CurrentUser.GetUserInfo.OrgInfo.First().Plant + "_" + search.ProjectName + "_" + search.FunPlant + " " +
search.MaterialType + (string.IsNullOrEmpty(search.Color) ? "" : "(" + search.Color + ") ")
+ search.ProductDate.Date.ToShortDateString() + this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "QA.2Htestreport");
WriteYieldDataToExcel(worksheetYield, detailData, title, search.FunPlant);
if (detailData.TopFive.Count != 0)
{
WriteDetailsDataToExcel(worksheetDetails, detailData);
}
excelPackage.Save();
}
}
return(new FileContentResult(stream.ToArray(), "application/octet-stream")
{
FileDownloadName = Server.UrlEncode(fileName)
});
}
public FileResult ExportReportExcel(string query)
{
OQCReportExcel Report = new OQCReportExcel();
QAReportSearchVM search = JsonConvert.DeserializeObject <QAReportSearchVM>(query);
search.Count = 5;
Report = GetOQCReportForExportExcel(search);
var stream = new MemoryStream();
var fileName = PathHelper.SetGridExportExcelName("");
using (var excelPackage = new ExcelPackage(stream))
{
var worksheetYield = excelPackage.Workbook.Worksheets.Add("Yield");
var worksheetTopFive = excelPackage.Workbook.Worksheets.Add("Top5");
string title = this.CurrentUser.GetUserInfo.OrgInfo.First().Plant + "_" + search.ProjectName + "_" + search.FunPlant + " " +
search.MaterialType + (string.IsNullOrEmpty(search.Color) ? "" : "(" + search.Color + ") ")
+ search.ProductDate.Date.ToShortDateString() + this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "QA.2Htestreport");
WriteYieldDataToExcel(worksheetYield, Report, title, search.FunPlant);
if (Report.TopFive.Count != 0)
{
WriteTopFiveToExcel(worksheetTopFive, Report, search.FunPlant);
}
excelPackage.Save();
}
return(new FileContentResult(stream.ToArray(), "application/octet-stream")
{
FileDownloadName = Server.UrlEncode(fileName)
});
}
private void WriteTopFiveToExcel(ExcelWorksheet worksheet, OQCReportExcel data, string funplant)
{
List <OQCReprotTopFiveTypeVM> TopFive = data.TopFive;
var process = from T in TopFive
select T.Process;
List <string> ProcessList = process.Distinct().ToList();
//第一次将只有五个排名的Process存放在里面
List <OQCReprotTopFiveTypeVM> fiveRandk = new List <OQCReprotTopFiveTypeVM>();
int rowIndex = 0;
foreach (string pro in ProcessList)
{
List <OQCReprotTopFiveTypeVM> tempList = TopFive.Where(x => x.Process == pro).ToList();
if (tempList.Count >= 5)
{
WriteTopFiveByProcess(worksheet, tempList, rowIndex);
rowIndex = rowIndex + 8;
}
else
{
fiveRandk.AddRange(tempList);
}
}
if (fiveRandk.Count > 0)
{
WriteTopFiveByProcess(worksheet, fiveRandk, rowIndex);
rowIndex = rowIndex + 8;
}
worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
string cellsIndex = funplant == "OQC" ? "A1:H" + rowIndex.ToString() : "A1:D8";
worksheet.Cells[cellsIndex].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells[cellsIndex].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[cellsIndex].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[cellsIndex].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells.AutoFitColumns();
}
private void WriteDetailsDataToExcel(ExcelWorksheet worksheet, OQCReportExcel TopFive)
{
#region 设置标题信息
string CellColumn = "A1:E2";
worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
worksheet.Cells[CellColumn].Merge = true;
worksheet.Cells[1, 1].Value = this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "QA.Detaildata");
worksheet.Cells[CellColumn].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[CellColumn].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Pink);
worksheet.Cells[CellColumn].Style.Font.Size = 28;
SetExcelCellStyle(worksheet, CellColumn);
worksheet.Cells[3, 1].Value = this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "Common.Seq");
worksheet.Cells[3, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[3, 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Gray);
worksheet.Cells[CellColumn].Style.Font.Size = 16;
SetExcelCellStyle(worksheet, "A3");
worksheet.Cells[3, 3].Value = this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "QA.Badname");
worksheet.Cells[3, 3].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[3, 3].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Gray);
worksheet.Cells[CellColumn].Style.Font.Size = 16;
SetExcelCellStyle(worksheet, "C3");
worksheet.Cells[3, 2].Value = this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "Production.Process_Station");
worksheet.Cells[3, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[3, 2].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Gray);
worksheet.Cells[CellColumn].Style.Font.Size = 16;
SetExcelCellStyle(worksheet, "B3");
worksheet.Cells[3, 4].Value = this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "QA.Quantity");
worksheet.Cells[3, 4].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[3, 4].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Gray);
worksheet.Cells[CellColumn].Style.Font.Size = 16;
SetExcelCellStyle(worksheet, "D3");
worksheet.Cells[3, 5].Value = this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "QA.Badrate");
worksheet.Cells[3, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[3, 5].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Gray);
worksheet.Cells[CellColumn].Style.Font.Size = 16;
SetExcelCellStyle(worksheet, "E5");
#endregion
#region ------- Detail ExceptionTypes info
int detailIndex = 4;
foreach (OQCReprotTopFiveTypeVM DetailInfo in TopFive.TopFive)
{
worksheet.Cells[detailIndex, 1].Value = detailIndex - 4 + 1;
worksheet.Cells[detailIndex, 2].Value = DetailInfo.Process;
worksheet.Cells[detailIndex, 3].Value = DetailInfo.TypeName;
worksheet.Cells[detailIndex, 4].Value = DetailInfo.Qty;
worksheet.Cells[detailIndex, 5].Value = DetailInfo.YieldRate;
detailIndex++;
}
#endregion
}
private void WriteYieldDataToExcel(ExcelWorksheet worksheet, OQCReportExcel data, string title, string funplant)
{
#region 设置标题信息
worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
worksheet.Cells["A1:P1"].Merge = true;
worksheet.Cells[1, 1].Value = title;
SetExcelCellStyle(worksheet, 28, "A1:P1");
worksheet.Cells[2, 1].Value = funplant == "OQC" ? "OQC FlOW" : "FLOW";
worksheet.Cells[2, 2].Value = this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "QA.Dayshifts");
worksheet.Cells["B2:E2"].Merge = true;
worksheet.Cells[2, 6].Value = this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "QA.Nightshifts");
worksheet.Cells["F2:I2"].Merge = true;
worksheet.Cells[2, 10].Value = "24 hours";
worksheet.Cells["J2:N2"].Merge = true;
worksheet.Cells[3, 1].Value = "Item";
worksheet.Cells[3, 2].Value = "Input";
worksheet.Cells[3, 3].Value = "OK";
worksheet.Cells[3, 4].Value = "NG";
worksheet.Cells[3, 5].Value = this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "QA.Rework");
worksheet.Cells[3, 6].Value = "Input";
worksheet.Cells[3, 7].Value = "OK";
worksheet.Cells[3, 8].Value = "NG";
worksheet.Cells[3, 9].Value = this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "QA.Rework");
worksheet.Cells[3, 10].Value = "Total";
worksheet.Cells[3, 11].Value = "OK";
worksheet.Cells[3, 12].Value = "NG";
worksheet.Cells[3, 13].Value = this.CurrentUser.GetLocaleStringResource(PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID, "QA.Rework");
worksheet.Cells[3, 14].Value = "WIP";
worksheet.Cells[3, 15].Value = "First Yield Rate";
worksheet.Cells[3, 16].Value = "Second Yield Rate";
#endregion
int index = 4;
#region Yield data
List <OQCReprotVM> sumData = data.SumData;
foreach (OQCReprotVM temp in sumData)
{
worksheet.Cells[index, 1].Value = temp.Process;
worksheet.Cells[index, 2].Value = temp.DailyInput;
worksheet.Cells[index, 3].Value = temp.DailyOK;
worksheet.Cells[index, 4].Value = temp.DailyNG;
worksheet.Cells[index, 5].Value = temp.DailyRework;
worksheet.Cells[index, 6].Value = temp.NightInput;
worksheet.Cells[index, 7].Value = temp.NightOK;
worksheet.Cells[index, 8].Value = temp.NightNG;
worksheet.Cells[index, 9].Value = temp.NightRework;
worksheet.Cells[index, 10].Value = temp.Input;
worksheet.Cells[index, 11].Value = temp.OK;
worksheet.Cells[index, 12].Value = temp.NG;
worksheet.Cells[index, 13].Value = temp.Rework;
worksheet.Cells[index, 14].Value = temp.WIP;
worksheet.Cells[index, 15].Value = temp.FirstYieldRate;
worksheet.Cells[index, 16].Value = temp.SecondYieldRate;
index++;
}
#endregion
string cellsIndex = "A1:P" + (index - 1).ToString();
worksheet.Cells[cellsIndex].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells[cellsIndex].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[cellsIndex].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[cellsIndex].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells.AutoFitColumns();
}
