Exemple #1
0
        public void DownloadExcel(int invoiceId)
        {
            var dbInvoice = invoiceService.GetInvoice(invoiceId);
            var dbClient  = clientService.GetClient(dbInvoice.ClientId);
            var dbCharges = chargeService.GetInvoiceCharges(invoiceId).ToList();

            var calculationResult  = chargeService.CalculateCharge(invoiceId);
            var unitsPerChargeName = chargeService.GetUnits(dbCharges);
            var ratePerChargeName  = chargeService.GetRates(dbCharges);

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            ExcelPackage   Ep    = new ExcelPackage();
            ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("Invoice Print");

            // Sheet labels
            Sheet.Cells["B6"].Value = "Contract";
            Sheet.Cells["B4"].Value = "Invoice number";
            Sheet.Cells["G2"].Value = "Invoice date";
            Sheet.Cells["B2"].Value = "Company name";
            Sheet.Cells["G4"].Value = "Start date";
            Sheet.Cells["G6"].Value = "End date";

            // Sheet values
            Sheet.Cells["D6"].Value = dbClient.Contract;
            Sheet.Cells["D4"].Value = dbInvoice.InvoiceNumber;
            Sheet.Cells["I2"].Value = string.Format("{0:d.M.yyyy}", dbInvoice.InvoiceDate);
            Sheet.Cells["D2"].Value = dbInvoice.CompanyName;
            Sheet.Cells["I4"].Value = string.Format("{0:d.M.yyyy}", dbInvoice.StartDate);
            Sheet.Cells["I6"].Value = string.Format("{0:d.M.yyyy}", dbInvoice.EndDate);

            // Sheet labels
            Sheet.Cells["A10"].Value = "Charge";
            Sheet.Cells["A11"].Value = "Day";
            Sheet.Cells["A12"].Value = "Night";
            Sheet.Cells["A13"].Value = "Weekend";
            Sheet.Cells["C10"].Value = "Units";
            Sheet.Cells["E10"].Value = "Rate";
            Sheet.Cells["I10"].Value = "Amount";

            // Sheet values
            Sheet.Cells["C11"].Value = unitsPerChargeName["Day"];       // Units per day
            Sheet.Cells["C12"].Value = unitsPerChargeName["Night"];     // Units per night
            Sheet.Cells["C13"].Value = unitsPerChargeName["Weekend"];   // Units per weekend
            Sheet.Cells["E11"].Value = ratePerChargeName["Day"];;       // Rate per day
            Sheet.Cells["E12"].Value = ratePerChargeName["Night"];;     // Rate per night
            Sheet.Cells["E13"].Value = ratePerChargeName["Weekend"];;   // Rate per weekend

            Sheet.Cells["I11"].Value = unitsPerChargeName["Day"] * ratePerChargeName["Day"];
            Sheet.Cells["I12"].Value = unitsPerChargeName["Night"] * ratePerChargeName["Night"];
            Sheet.Cells["I13"].Value = unitsPerChargeName["Weekend"] * ratePerChargeName["Weekend"];

            // Sheet labels
            Sheet.Cells["A16"].Value = "Total";
            Sheet.Cells["A17"].Value = "Tax";
            Sheet.Cells["A18"].Value = "Grand Total";

            // Sheet values
            Sheet.Cells["I16"].Value = calculationResult["Total"];
            Sheet.Cells["I17"].Value = calculationResult["Tax"];
            Sheet.Cells["I18"].Value = calculationResult["GrandTotal"];

            // Execute
            Sheet.Cells["A:AZ"].AutoFitColumns();
            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment: filename=" + "Report.xlsx");
            Response.BinaryWrite(Ep.GetAsByteArray());
            Response.End();
        }