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