Beispiel #1
0
        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"));
        }
Beispiel #2
0
        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();
        }