public Tuple <MemoryStream, string> GenerateExcel(string filter = "{}")
{
var Query = CCROGarmentHistoryBySectionReportLogic.GetQuery(filter);
var data = Query.ToList();
DataTable result = new DataTable();
var offset = 7;
result.Columns.Add(new DataColumn()
{
ColumnName = "No", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "No RO", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Seksi", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Kode Buyer", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nama Buyer", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Article", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Shipment", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Validasi CC Kabag Md", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "validasi CC IE", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Validasi CC Purch", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Validasi CC Kadiv Md", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Validasi RO Md", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Validasi RO Sample", DataType = typeof(String)
});
Dictionary <string, string> Rowcount = new Dictionary <string, string>();
if (Query.ToArray().Count() == 0)
{
result.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "", ""); // to allow column name to be generated properly for empty data as template
}
else
{
int index = 0;
foreach (CCROGarmentHistoryBySectionReportViewModel item in data)
{
index++;
string ApprovedDate1 = item.DeliveryDate == new DateTime(1970, 1, 1) ? "-" : item.DeliveryDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string ApprovedDate2 = item.ApprovalMDDate == null ? "-" : item.ApprovalMDDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string ApprovedDate3 = item.ApprovalIEDate == null ? "-" : item.ApprovalIEDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string ApprovedDate4 = item.ApprovalPurchDate == null ? "-" : item.ApprovalPurchDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string ApprovedDate5 = item.ApprovalKadivMDDate == null ? "-" : item.ApprovalKadivMDDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string ApprovedDate6 = item.ValidatedMDDate == null ? "-" : item.ValidatedMDDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string ApprovedDate7 = item.ValidatedSampleDate == null ? "-" : item.ValidatedSampleDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
result.Rows.Add(index, item.RO_Number, item.Section, item.BrandCode, item.BrandName, item.Article, ApprovedDate1,
ApprovedDate2, ApprovedDate3, ApprovedDate4, ApprovedDate5, ApprovedDate6, ApprovedDate7);
}
}
ExcelPackage package = new ExcelPackage();
var sheet = package.Workbook.Worksheets.Add("CC RO Garment History");
sheet.Cells["A1"].LoadFromDataTable(result, true, OfficeOpenXml.Table.TableStyles.Light16);
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
MemoryStream streamExcel = new MemoryStream();
package.SaveAs(streamExcel);
string fileName = string.Concat("CC RO Garment History", ".xlsx");
return(Tuple.Create(streamExcel, fileName));
}
