public ActionResult InvestmentByRegionExport(InvestmentByRegionViewModel model) { if (ModelState.IsValid) { try { using (var ms = new MemoryStream()) { model.InvestmentByRegions = ReportRepository.GetInvestmentByRegion(model); model.Regions = model.InvestmentByRegions.Select(s => s.Region).Distinct().OrderBy(c => c).ToList(); model.Sectors = StatusRepository.GetStatusByGroup("Sector"); var exporterService = new ExcelExportService(); var workbook = exporterService.ExportByRegion(model); //Write the Workbook to a memory stream workbook.Write(ms); var fileName = "Foreign investment Chart (" + DateTime.Now.ToString("ddMMyyyy-HHmmss") + ")." + exporterService.Extension; Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); //Return the result to the end user return(File(ms.ToArray(), exporterService.MIME)); //Suggested file name in the "Save as" dialog which will be displayed to the end user } } catch (Exception ex) { ModelState.AddModelError("", "Error in - ExportToExcel :" + ex.Message); return(View("InvestmentByCountryExport")); } } return(View("InvestmentByCountryExport")); }
private void CreateSheetForRegion(InvestmentByRegionViewModel data, ISheet sheet, Dictionary <string, ICellStyle> styles) { CreateTitleForRegion(sheet, styles, data); CreateHeaderForRegion(sheet, styles, data); CreateRowForRegion(sheet, styles, data); sheet.AutoSizeColumn(0, true); sheet.AutoSizeColumn(1, true); }
public ActionResult InvestmentByRegion(InvestmentByRegionViewModel model) { model.InvestmentByRegions = ReportRepository.GetInvestmentByRegion(model); model.Regions = model.InvestmentByRegions.Select(s => s.Region).Distinct().OrderBy(r => r).ToList(); model.Sectors = StatusRepository.GetStatusByGroup("Sector"); //model.FromDate = DateTime.Now; //model.ToDate = DateTime.Now; return(View(model)); }
public IWorkbook ExportByRegion(InvestmentByRegionViewModel model) { //Create new Excel Workbook var workbook = CreateWorkbook(); var styles = CreateStyles(workbook); //Create new Excel Sheet var sheet = workbook.CreateSheet("Region Total"); CreateSheetForRegion(model, sheet, styles); //Write the Workbook to a memory stream return(workbook); }
private static void CreateRowForRegion(ISheet sheet, Dictionary <string, ICellStyle> styles, InvestmentByRegionViewModel data) { var rowNo = 5; var recordNo = 1; foreach (var region in data.Regions) { var row = sheet.CreateRow(rowNo++); var cell = row.CreateCell(0); cell.CellStyle = styles["General"]; cell.SetCellValue(recordNo++); cell = row.CreateCell(1); cell.CellStyle = styles["General"]; cell.SetCellValue(region); var columNo = 2; data.Sectors.ForEach(sector => { var investment = data.InvestmentByRegions.Where(i => i.Region == region && i.Sector == sector.Name).FirstOrDefault(); cell = row.CreateCell(columNo++); cell.CellStyle = styles["2Decimal"]; if (investment != null) { cell.SetCellValue(Convert.ToDouble(investment.Amount)); } cell = row.CreateCell(columNo++); cell.CellStyle = styles["General"]; if (investment != null) { cell.SetCellValue(investment.Quantity); } }); } }
private static void CreateHeaderForRegion(ISheet sheet, Dictionary <string, ICellStyle> styles, InvestmentByRegionViewModel data) { var row = sheet.CreateRow(3); var cell = row.CreateCell(0); cell.CellStyle = styles["Header"]; cell.SetCellValue("#"); cell = row.CreateCell(1); cell.CellStyle = styles["Header"]; cell.SetCellValue("Region"); var columNo = 2; data.Sectors.ForEach(s => { var firstColumnNo = columNo; cell = row.CreateCell(columNo++); cell.CellStyle = styles["Header"]; cell.SetCellValue(s.Name); cell = row.CreateCell(columNo); cell.CellStyle = styles["Header"]; cell.SetCellValue(s.Name); sheet.AddMergedRegion(new CellRangeAddress( 3, //first row (0-based) 3, //last row (0-based) firstColumnNo, //first column (0-based) columNo++ //last column (0-based) )); }); row = sheet.CreateRow(4); cell = row.CreateCell(0); cell.CellStyle = styles["Header"]; cell = row.CreateCell(1); cell.CellStyle = styles["Header"]; columNo = 2; data.Sectors.ForEach(s => { cell = row.CreateCell(columNo++); cell.CellStyle = styles["Header"]; cell.SetCellValue("Amount"); cell = row.CreateCell(columNo++); cell.CellStyle = styles["Header"]; cell.SetCellValue("Qty"); }); sheet.AddMergedRegion(new CellRangeAddress( 3, //first row (0-based) 4, //last row (0-based) 0, //first column (0-based) 0 //last column (0-based) )); sheet.AddMergedRegion(new CellRangeAddress( 3, //first row (0-based) 4, //last row (0-based) 1, //first column (0-based) 1 //last column (0-based) )); }
private static void CreateTitleForRegion(ISheet sheet, Dictionary <string, ICellStyle> styles, InvestmentByRegionViewModel data) { //Create a header row var row = sheet.CreateRow(0); for (int i = 0; i <= (data.Sectors.Count * 2) + 1; ++i) { var cell = row.CreateCell(i); cell.CellStyle = styles["Title"]; if (i == 0) { cell.SetCellValue("Appendix (A)"); } } sheet.AddMergedRegion(new CellRangeAddress( 0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) (data.Sectors.Count * 2) + 1 //last column (0-based) )); row = sheet.CreateRow(1); for (int i = 0; i <= (data.Sectors.Count * 2) + 1; ++i) { var cell = row.CreateCell(i); cell.CellStyle = styles["SubTitle"]; if (i == 0) { var title = string.Empty; if (data.FromDate != null && data.ToDate != null) { title = string.Format("Foreign Investment of Existing Enterprises from ({0}) to ({1})", data.FromDate.Value.ToString("dd/MMM/yyyy"), data.ToDate.Value.ToString("dd/MMM/yyyy")); } else { title = string.Format("Foreign Investment of Existing Enterprises"); } cell.SetCellValue(title); } } sheet.AddMergedRegion(new CellRangeAddress( 1, //first row (0-based) 1, //last row (0-based) 0, //first column (0-based) (data.Sectors.Count * 2) + 1 //last column (0-based) )); row = sheet.CreateRow(2); for (int i = 0; i <= (data.Sectors.Count * 2) + 1; ++i) { var cell = row.CreateCell(i); cell.CellStyle = styles["Title"]; if (i == 0) { cell.SetCellValue("US Dollar (Million)"); } } sheet.AddMergedRegion(new CellRangeAddress( 2, //first row (0-based) 2, //last row (0-based) 0, //first column (0-based) (data.Sectors.Count * 2) + 1 //last column (0-based) )); }
public static List <InvestmentByRegion> GetInvestmentByRegion(InvestmentByRegionViewModel criteria) { using (var db = new ApplicationDbContext()) { var query = (from investment in db.Investments join sector in db.Statuses on investment.Sector equals sector.Value join investmentPermittedAddress in db.Addresses on investment.InvestmentPermittedAddressId equals investmentPermittedAddress.UID join formofinvestment in db.Statuses on investment.FormofInvestment equals formofinvestment.Value join investingCountry in db.Countries on investment.InvestingCountry equals investingCountry.ISO select new InvestmentViewModel { UID = investment.UID, TypeOfInvestmentValue = investment.TypeOfInvestment, InvestorName = investment.InvestorName, Citizenship = investment.Citizenship, OrganizationName = investment.OrganizationName, IncorporationPlace = investment.IncorporationPlace, BusinessType = investment.BusinessType, InvestmentPermittedAddress = investmentPermittedAddress, AmountofForeignCapital = investment.AmountofForeignCapital, PeriodforForeignCapitalBroughtin = investment.PeriodforForeignCapitalBroughtin, PeriodforForeignCapitalBroughtinType = investment.PeriodforForeignCapitalBroughtinType, TotalAmountofCapital = investment.TotalAmountofCapital, CapitalCurrency = investment.CapitalCurrency, ConstructionPeriod = investment.ConstructionPeriod, ConstructionPeriodType = investment.ConstructionPeriodType, ValidityofInvestmentPermit = investment.ValidityofInvestmentPermit, ValidityofInvestmentPermitPeriodType = investment.ValidityofInvestmentPermitPeriodType, FormofInvestment = formofinvestment.Name, CompanyNameinMyanmar = investment.CompanyNameinMyanmar, PermitNo = investment.PermitNo, PermitDate = investment.PermitDate, Sector = sector.Name, InvestingCountry = investingCountry.Name, SectorValue = sector.Value, InvestingCountryValue = investingCountry.ISO, }); if (!string.IsNullOrEmpty(criteria.TypeOfInvestment)) { query = query.Where(i => i.TypeOfInvestmentValue.Equals(criteria.TypeOfInvestment)); } if (!string.IsNullOrEmpty(criteria.Sector)) { query = query.Where(i => i.SectorValue.Equals(criteria.Sector)); } if (!string.IsNullOrEmpty(criteria.InvestingCountry)) { query = query.Where(i => i.InvestingCountryValue.Equals(criteria.InvestingCountry)); } if (criteria.FromDate != null && criteria.FromDate != DateTime.MinValue) { query = query.Where(r => DbFunctions.TruncateTime(r.PermitDate) >= DbFunctions.TruncateTime(criteria.FromDate)); } if (criteria.ToDate != null && criteria.ToDate != DateTime.MinValue) { query = query.Where(r => DbFunctions.TruncateTime(r.PermitDate) <= DbFunctions.TruncateTime(criteria.ToDate)); } var investmentDto = query.ToList(); var investmentRegionDto = (from i in investmentDto group i by new { i.InvestmentPermittedAddress.State, i.Sector } into grouping select new InvestmentByRegion { Region = grouping.Key.State, Sector = grouping.Key.Sector, Amount = grouping.Sum(i => i.TotalAmountofCapital), Quantity = grouping.Count() }).ToList(); return(investmentRegionDto); } }