public ActionResult ExportToExcel()
{
try
{
ExcelPackage Package = new ExcelPackage();
ExcelWorksheet ws = Package.Workbook.Worksheets.Add("Data");
Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#00b0f0");
OfficeOpenXml.Style.ExcelBorderStyle DefaultBorder = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
ws.Cells["A1"].LoadFromText("No");
ws.Cells["A1"].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["A1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["A1"].Style.Font.Bold = true;
ws.Cells["A1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["A1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["A1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["A1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["A1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["A1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["A1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["A1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["B1"].LoadFromText("Tanggal Transaksi");
ws.Cells["B1"].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["B1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["B1"].Style.Font.Bold = true;
ws.Cells["B1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["B1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["B1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["B1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["B1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["B1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["B1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["B1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["C1"].LoadFromText("NIP");
ws.Cells["C1"].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["C1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["C1"].Style.Font.Bold = true;
ws.Cells["C1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["C1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["C1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["C1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["C1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["C1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["C1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["C1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["D1"].LoadFromText("Pengguna");
ws.Cells["D1"].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["D1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["D1"].Style.Font.Bold = true;
ws.Cells["D1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["D1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["D1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["D1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["D1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["D1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["D1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["D1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["E1"].LoadFromText("Fuel");
ws.Cells["E1"].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["E1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["E1"].Style.Font.Bold = true;
ws.Cells["E1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["E1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["E1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["E1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["E1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["E1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["E1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["E1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["F1"].LoadFromText("Qty");
ws.Cells["F1"].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["F1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["F1"].Style.Font.Bold = true;
ws.Cells["F1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["F1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["F1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["F1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["F1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["F1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["F1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["F1"].Style.Border.Right.Style = DefaultBorder;
int idx = 1;
foreach (var item in OrdersLogic.getInstance().GetAllHistoryExport().ToList())
{
idx++;
ws.Cells["A" + idx.ToString()].LoadFromText(item.No.ToString());
ws.Cells["A" + idx.ToString()].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["A" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["A" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["A" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["A" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["A" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["A" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
DateTime?createdDate = item.CreateDate;
string strCreatedDate = createdDate.Value.ToString("dd MMM yy HH:mm");
ws.Cells["B" + idx.ToString()].LoadFromText(strCreatedDate);
ws.Cells["B" + idx.ToString()].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["B" + idx.ToString()].Style.Numberformat.Format = "dd MMM yy HH:mm";
ws.Cells["B" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["B" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["B" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["B" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["B" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["B" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].LoadFromText(item.NIP);
ws.Cells["C" + idx.ToString()].Style.Numberformat.Format = "##0";
ws.Cells["C" + idx.ToString()].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["C" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["C" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["C" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["D" + idx.ToString()].LoadFromText(item.Name);
ws.Cells["D" + idx.ToString()].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["D" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["D" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["D" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["D" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["D" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["D" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["E" + idx.ToString()].LoadFromText(item.Fuel);
ws.Cells["E" + idx.ToString()].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["E" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["E" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["E" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["E" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["E" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["E" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["F" + idx.ToString()].LoadFromText(item.Liter.ToString());
ws.Cells["F" + idx.ToString()].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["F" + idx.ToString()].Style.Numberformat.Format = "#,#0 Liter";
ws.Cells["F" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["F" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["F" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["F" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["F" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["F" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=RiwayatPengambilanBBM_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx");
Response.BinaryWrite(Package.GetAsByteArray());
Response.End();
}
catch (Exception e)
{
Logging.Log.getInstance().CreateLogError(e);
}
return(View("Index"));
}
private void ExcelReportCashOut(DashboardViewModels model)
{
ExcelPackage Package = new ExcelPackage();
ExcelWorksheet ws = Package.Workbook.Worksheets.Add("Data");
ExcelWorksheet wsComChart = Package.Workbook.Worksheets.Add("Chart");
OfficeOpenXml.Style.ExcelBorderStyle DefaultBorder = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#00b0f0");
ws.Cells["A1"].LoadFromText("Month");
ws.Cells["A1"].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["A1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["A1"].Style.Font.Bold = true;
ws.Cells["A1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["A1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["A1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["A1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["A1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["A1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["A1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["A1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["B1"].LoadFromText("Operation");
ws.Cells["B1"].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["B1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["B1"].Style.Font.Bold = true;
ws.Cells["B1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["B1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["B1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["B1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["B1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["B1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["B1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["B1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["C1"].LoadFromText("Accounting");
ws.Cells["C1"].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["C1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["C1"].Style.Font.Bold = true;
ws.Cells["C1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["C1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["C1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["C1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["C1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["C1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["C1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["C1"].Style.Border.Right.Style = DefaultBorder;
int idx = 1;
var list = DashboardBusinessLogic.getInstance().getReportOfAccountCashIn(model);
foreach (var item in list)
{
idx++;
ws.Cells["A" + idx.ToString()].LoadFromText(item.Month);
ws.Cells["A" + idx.ToString()].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["A" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["A" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["A" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["A" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["A" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["A" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["B" + idx.ToString()].LoadFromText((Convert.ToDecimal(item.Accounting)).ToString());
ws.Cells["B" + idx.ToString()].Style.Numberformat.Format = "#,##";
ws.Cells["B" + idx.ToString()].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["B" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["B" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["B" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["B" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["B" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["B" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].LoadFromText((Convert.ToDecimal(item.Operation)).ToString());
ws.Cells["C" + idx.ToString()].Style.Numberformat.Format = "#,##";
ws.Cells["C" + idx.ToString()].Style.Font.SetFromFont(new Font("Cambria", 10));
ws.Cells["C" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["C" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["C" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
}
List <DataSerie> SeriesList = new List <DataSerie>();
SeriesList.Add(new DataSerie()
{
name = "Month", Series = ws.Cells["A" + (idx - 1) + ":A" + idx], xSeries = ws.Cells["B" + (idx - 1) + ":C" + idx]
});
SeriesList.Add(new DataSerie()
{
name = "Accounting", Series = ws.Cells["B" + (idx - 1) + ":B" + idx], xSeries = ws.Cells["B" + (idx - 1) + ":C" + idx]
});
SeriesList.Add(new DataSerie()
{
name = "Operation", Series = ws.Cells["C" + (idx - 1) + ":C" + idx], xSeries = ws.Cells["B" + (idx - 1) + ":C" + idx]
});
ExcelChart chart = AddBarChart(wsComChart, "ReportOfAccountCashOut", OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered, SeriesList);
chart.Title.Text = "Report Of Account Cash Out";
chart.SetPosition(0, 0, 0, 0);
chart.SetSize(BarOptions.width, BarOptions.Height);
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=ReportOfAccountCashOut_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx");
Response.BinaryWrite(Package.GetAsByteArray());
Response.End();
}
private void GenerateExcel(OutputViewModels model)
{
ExcelPackage Package = new ExcelPackage();
ExcelWorksheet ws = Package.Workbook.Worksheets.Add("Data");
Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#00b0f0");
OfficeOpenXml.Style.ExcelBorderStyle DefaultBorder = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
ws.Cells["A1"].LoadFromText("Date");
ws.Cells["A1"].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["A1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["A1"].Style.Font.Bold = true;
ws.Cells["A1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["A1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["A1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["A1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["A1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["A1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["A1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["A1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["B1"].LoadFromText("Account");
ws.Cells["B1"].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["B1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["B1"].Style.Font.Bold = true;
ws.Cells["B1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["B1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["B1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["B1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["B1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["B1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["B1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["B1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["C1"].LoadFromText("Bank");
ws.Cells["C1"].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["C1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["C1"].Style.Font.Bold = true;
ws.Cells["C1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["C1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["C1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["C1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["C1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["C1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["C1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["C1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["D1"].LoadFromText("Description");
ws.Cells["D1"].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["D1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["D1"].Style.Font.Bold = true;
ws.Cells["D1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["D1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["D1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["D1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["D1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["D1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["D1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["D1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["E1"].LoadFromText("Cash Out");
ws.Cells["E1"].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["E1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["E1"].Style.Font.Bold = true;
ws.Cells["E1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["E1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["E1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["E1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["E1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["E1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["E1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["E1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["F1"].LoadFromText("Cash In");
ws.Cells["F1"].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["F1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["F1"].Style.Font.Bold = true;
ws.Cells["F1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["F1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["F1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["F1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["F1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["F1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["F1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["F1"].Style.Border.Right.Style = DefaultBorder;
ws.Cells["G1"].LoadFromText("Balance");
ws.Cells["G1"].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["G1"].Style.Font.Color.SetColor(Color.White);
ws.Cells["G1"].Style.Font.Bold = true;
ws.Cells["G1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["G1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
ws.Cells["G1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["G1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["G1"].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["G1"].Style.Border.Left.Style = DefaultBorder;
ws.Cells["G1"].Style.Border.Top.Style = DefaultBorder;
ws.Cells["G1"].Style.Border.Right.Style = DefaultBorder;
int idx = 1;
foreach (var item in model.TodaysJournal.Content)
{
idx++;
ws.Cells["A" + idx.ToString()].LoadFromText(item.Date);
ws.Cells["A" + idx.ToString()].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["A" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["A" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["A" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["A" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["A" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["A" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["B" + idx.ToString()].LoadFromText(item.Account);
ws.Cells["B" + idx.ToString()].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["B" + idx.ToString()].Style.Numberformat.Format = "dd MMM yy HH:mm";
ws.Cells["B" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["B" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["B" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["B" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["B" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["B" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].LoadFromText(item.BankAccount);
ws.Cells["C" + idx.ToString()].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["C" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["C" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["C" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["C" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["D" + idx.ToString()].LoadFromText(item.Description);
ws.Cells["D" + idx.ToString()].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["D" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["D" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["D" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["D" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["D" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["D" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["E" + idx.ToString()].LoadFromText(item.CashOut);
ws.Cells["E" + idx.ToString()].Style.Numberformat.Format = "##0";
ws.Cells["E" + idx.ToString()].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["E" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["E" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["E" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["E" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["E" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["E" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["F" + idx.ToString()].LoadFromText(item.CashIn);
ws.Cells["F" + idx.ToString()].Style.Numberformat.Format = "##0";
ws.Cells["F" + idx.ToString()].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["F" + idx.ToString()].Style.Numberformat.Format = "#,#0 Liter";
ws.Cells["F" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["F" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["F" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["F" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["F" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["F" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
ws.Cells["G" + idx.ToString()].LoadFromText(item.Balance);
ws.Cells["G" + idx.ToString()].Style.Numberformat.Format = "##0";
ws.Cells["G" + idx.ToString()].Style.Font.SetFromFont(new System.Drawing.Font("Cambria", 10));
ws.Cells["G" + idx.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells["G" + idx.ToString()].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Cells["G" + idx.ToString()].Style.Border.Bottom.Style = DefaultBorder;
ws.Cells["G" + idx.ToString()].Style.Border.Left.Style = DefaultBorder;
ws.Cells["G" + idx.ToString()].Style.Border.Top.Style = DefaultBorder;
ws.Cells["G" + idx.ToString()].Style.Border.Right.Style = DefaultBorder;
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=Journals_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx");
Response.BinaryWrite(Package.GetAsByteArray());
Response.End();
}