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(); }