public FileResult ReportedMovementsToExcel(int accountStatusReportID) { AccountStatusReport Reporte = db.AccountStatusReport.Find(accountStatusReportID); List <TableToExportExcel> datatables = new List <TableToExportExcel>(); //Getting house data to exportr VMHouse vmHouse = Reporte.house.getVM(); var houseList = new List <VMHouse>(); houseList.Add(vmHouse); DataTable dtHOuse = ExcelTools.listToDatatable <VMHouse>(houseList); //Exporting movements data List <VMReportedMovementes> vmReporte = VMReportedMovementes.listToVMReportedMovements(Reporte.ReportedMovements.ToList()); DataTable dtReporte = ExcelTools.listToDatatable <VMReportedMovementes>(vmReporte); //Generating excel string heading = "Monthly Statement " + Reporte.dateMonth.ToString("MMMM-yyyy"); datatables.Add(new TableToExportExcel(dtHOuse, "House")); datatables.Add(new TableToExportExcel(dtReporte, heading)); ExcelPackage package = ExcelTools.exportToExcel(datatables, heading); byte[] bytesFile = package.GetAsByteArray(); //Preparing download file string usrName = Reporte.house.ApplicationUser.UserName; usrName = usrName.Substring(0, usrName.IndexOf('@')); return(File(bytesFile, ExcelTools.EXCEL_MIME_TYPE, String.Format("{0}_{1}_{2}{3}", heading, usrName, Reporte.house.name, ExcelTools.EXCEL_FORMAT))); }
public FileResult HouseMovementsToExcel(int houseID, DateTime?fecha) { House house = db.Houses.Find(houseID); DateTime fechaConArgumentos = new DateTime(); if (fecha == null) { //Si no viene fecha, se establece por defecto el mes actual fechaConArgumentos = DateTime.Now; fechaConArgumentos = new DateTime(fechaConArgumentos.Year, fechaConArgumentos.Month, 1); } else { //Si dentro de la transaccion viene con fecha, se ignora la hora fechaConArgumentos = fecha.Value; } //Getting data to export List <Movement> movimientos = new List <Movement>(); if (User.IsInRole(ApplicationUser.RoleNames.ADMINISTRADOR)) { movimientos = db.Movements.Where(mov => mov.houseID == houseID && mov.transactionDate.Month == fechaConArgumentos.Month && mov.transactionDate.Year == fechaConArgumentos.Year).OrderBy(move => move.transactionDate).ToList(); } else { movimientos = db.Movements.Where(mov => mov.houseID == houseID && mov.state == true && mov.transactionDate.Year == fechaConArgumentos.Year && mov.transactionDate.Month == fechaConArgumentos.Month).OrderBy(move => move.transactionDate).ToList(); } //Preparing data to introduce to excel List <VMReportedMovementes> vmMovementes = ReportedMovements.VMReportedMovementes.listToVMReportedMovements(movimientos); DataTable dtMovementes = ExcelTools.listToDatatable <VMReportedMovementes>(vmMovementes); List <TableToExportExcel> datatables = new List <TableToExportExcel>(); //Getting house data to introduce in excel VMHouse vmHouse = house.getVM(); var houseList = new List <VMHouse>(); houseList.Add(vmHouse); DataTable dtHOuse = ExcelTools.listToDatatable <VMHouse>(houseList); //Generating excel string heading = "Monthly Statement " + fecha.Value.ToString("MMMM-yyyy"); datatables.Add(new TableToExportExcel(dtHOuse, "House")); datatables.Add(new TableToExportExcel(dtMovementes, "Movements of House" + fecha.Value.ToString("MMMM-yyyy"))); ExcelPackage package = ExcelTools.exportToExcel(datatables, heading); byte[] bytesFile = package.GetAsByteArray(); //Preparing download file string usrName = house.ApplicationUser.UserName; usrName = usrName.Substring(0, usrName.IndexOf('@')); return(File(bytesFile, ExcelTools.EXCEL_MIME_TYPE, String.Format("{0}_{1}_{2}{3}", heading, usrName, house.name, ExcelTools.EXCEL_FORMAT))); }
public FileResult ProfitAndLossToExcel(String id, DateTime?fecha1, DateTime?fecha2, bool onlyContributions = false, int houseID = 0) { House house = null; ApplicationUser owner; //Re-Create the report if (houseID == 0) //If house is not specified, just { owner = db.Users.Find(id); } else //If house is specified, the owner is find from it { house = db.Houses.Find(houseID); owner = house.ApplicationUser; } ApplicationUser.ProfitAndLossReport profitAndLossReport = owner.generateProfitAndLossReport(fecha1.Value, fecha2.Value, houseID); //Pre-processed to be exported List <VMProfitAndLossRow> vmProfitList = VMProfitAndLossRow.listToVMMovements(profitAndLossReport.profitReport.ToList()); DataTable dtProfit = ExcelTools.listToDatatable <VMProfitAndLossRow>(vmProfitList); List <VMProfitAndLossRow> vmLossList = VMProfitAndLossRow.listToVMMovements(profitAndLossReport.lossReport.ToList()); DataTable dtLoss = ExcelTools.listToDatatable <VMProfitAndLossRow>(vmLossList); List <VMProfitAndLossRow> vmContribs = VMProfitAndLossRow.listToVMMovements(profitAndLossReport.contributionReport.ToList()); DataTable dtContribs = ExcelTools.listToDatatable <VMProfitAndLossRow>(vmContribs); List <TableToExportExcel> datatables = new List <TableToExportExcel>(); if (house != null) //If house was specified, house data is added to the Excel File { //Getting house data to exportr VMHouse vmHouse = house.getVM(); var houseList = new List <VMHouse>(); houseList.Add(vmHouse); DataTable dtHOuse = ExcelTools.listToDatatable <VMHouse>(houseList); datatables.Add(new TableToExportExcel(dtHOuse, "House")); } if (onlyContributions) { datatables.Add(new TableToExportExcel(dtContribs, "Contributions")); } else { datatables.Add(new TableToExportExcel(dtProfit, "Income")); datatables.Add(new TableToExportExcel(dtLoss, "Loss")); } //Exported ExcelPackage package = ExcelTools.exportToExcel(datatables, onlyContributions? "Contributions" : "Profit and Loss"); var reportTotals = profitAndLossReport.totals; ExcelWorksheet worksheet = package.Workbook.Worksheets.ElementAt(0); int startRow = worksheet.Dimension.End.Row + 2; worksheet.Cells["A" + (startRow)].Value = "Totals"; worksheet.Cells["A" + (startRow)].Style.Font.Size = 20; startRow++; int counter = startRow; foreach (var item in reportTotals) { if (onlyContributions && item.concept == ProfitAndLossTotalConcepts.TotalContribution || !onlyContributions && (item.concept != ProfitAndLossTotalConcepts.TotalContribution)) { worksheet.Cells["A" + counter].Value = item.conceptName; worksheet.Cells["B" + counter].Value = item.total; counter++; } } // format cells - add borders using (ExcelRange r = worksheet.Cells[startRow, 1, counter - 1, 2]) { r.Style.Border.Top.Style = ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; r.Style.Border.Left.Style = ExcelBorderStyle.Thin; r.Style.Border.Right.Style = ExcelBorderStyle.Thin; r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black); } worksheet.InsertRow(1, 2); worksheet.Cells["A1"].Value = "From"; worksheet.Cells["B1"].Value = fecha1.Value.ToString("dd/MMM/yyyy"); worksheet.Cells["C1"].Value = "To"; worksheet.Cells["D1"].Value = fecha2.Value.ToString("dd/MMM/yyyy"); worksheet.Cells["A1"].Style.Font.Bold = true; worksheet.Cells["C1"].Style.Font.Bold = true; if (house == null) //House not specified, owner name is added in the header of the file { worksheet.Cells["E1"].Value = "Owner"; worksheet.Cells["E1"].Style.Font.Bold = true; worksheet.Cells["F1"].Value = owner.OwnerName; } byte[] bytesFile = package.GetAsByteArray(); return(File(bytesFile, ExcelTools.EXCEL_MIME_TYPE, profitAndLossReport.ToString(onlyContributions) + ExcelTools.EXCEL_FORMAT)); }