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);
}
}
