private void CreateSheetForCountry(InvestmentByCountryViewModel data, ISheet sheet, Dictionary <string, ICellStyle> styles) { CreateTitleForCountry(sheet, styles, data); CreateHeaderForCountry(sheet, styles, data); CreateRowForCountry(sheet, styles, data); sheet.AutoSizeColumn(0, true); sheet.AutoSizeColumn(1, true); }
// GET: Report public ActionResult InvestmentByCountry(InvestmentByCountryViewModel model) { model.InvestmentByCountries = ReportRepository.GetInvestmentByCountry(model); model.Countries = model.InvestmentByCountries.Select(s => s.Country).Distinct().OrderBy(c => c).Select(c => new Country { Name = c }).ToList(); model.Sectors = StatusRepository.GetStatusByGroup("Sector"); //model.FromDate = DateTime.Now; //model.ToDate = DateTime.Now; return(View(model)); }
public IWorkbook ExportByCountry(InvestmentByCountryViewModel model) { //Create new Excel Workbook var workbook = CreateWorkbook(); var styles = CreateStyles(workbook); //Create new Excel Sheet var sheet = workbook.CreateSheet("Country Total"); CreateSheetForCountry(model, sheet, styles); //Write the Workbook to a memory stream return(workbook); }
public ActionResult InvestmentByCountryExport(InvestmentByCountryViewModel model) { if (ModelState.IsValid) { try { using (var ms = new MemoryStream()) { model.InvestmentByCountries = ReportRepository.GetInvestmentByCountry(model); model.Countries = model.InvestmentByCountries.Select(s => s.Country).Distinct().OrderBy(c => c).Select(c => new Country { Name = c }).ToList(); model.Sectors = StatusRepository.GetStatusByGroup("Sector"); var exporterService = new ExcelExportService(); var workbook = exporterService.ExportByCountry(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 static void CreateRowForCountry(ISheet sheet, Dictionary <string, ICellStyle> styles, InvestmentByCountryViewModel data) { var rowNo = 5; var recordNo = 1; foreach (var country in data.Countries) { 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(country.Name); var columNo = 2; data.Sectors.ForEach(sector => { var investment = data.InvestmentByCountries.Where(i => i.Country == country.Name && 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 CreateHeaderForCountry(ISheet sheet, Dictionary <string, ICellStyle> styles, InvestmentByCountryViewModel 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("Country"); 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 CreateTitleForCountry(ISheet sheet, Dictionary <string, ICellStyle> styles, InvestmentByCountryViewModel 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"); } } } 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 <InvestmentByCountry> GetInvestmentByCountry(InvestmentByCountryViewModel 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 investmentCountryDto = (from i in investmentDto group i by new { i.InvestingCountry, i.Sector } into grouping select new InvestmentByCountry { Country = grouping.Key.InvestingCountry, Sector = grouping.Key.Sector, Amount = grouping.Sum(i => i.TotalAmountofCapital), Quantity = grouping.Count() }).ToList(); return(investmentCountryDto); } }