public ActionResult DistInd([Bind(Include = "Region,Province,District,Round,Measure")] requestfilter req)
{
Int16 Region;
Int16 Province;
Int16 District;
Int16 Round;
string Measure;
Region = req.Region;
Province = req.Province;
District = req.District;
Round = req.Round;
Measure = req.Measure;
var data = _context.IndDistircts.Select
(x => new
{
Region = x.Region,
Province = x.Province,
District = x.District,
Year = x.Year,
Month = x.Month,
Round = x.Round,
ShorName = x.ShortName,
Indicator = x.Indicator,
Measure = x.Measure,
Numerator = x.Numerator,
Denominator = x.Denominator,
RegionId = x.RegionId,
ProvinceId = x.ProvinceId,
DistrictId = x.DistrictId,
RoundId = x.RoundId,
CampaignType = x.CampaignType,
Form = x.Form
}
).ToList();
if (!Region.Equals(0) & !Province.Equals(0) & !District.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province) & m.DistrictId.Equals(District)).ToList();
}
else if (!Region.Equals(0) & !Province.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province)).ToList();
}
else if (!Region.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region)).ToList();
}
if (!Round.Equals(0))
{
data = data.Where(m => m.RoundId.Equals(Round)).ToList();
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook;
workbook = application.Workbooks.Create(3);
IWorksheet sheet = workbook.Worksheets[0];
if (!data.Any())
{
return(HttpNotFound());
}
sheet.ImportData(data, 1, 1, true);
sheet.Name = "Data";
IWorksheet pivotSheet = workbook.Worksheets[1];
pivotSheet.Name = "Percentage";
IPivotCache cash_data = workbook.PivotCaches.Add(sheet[sheet.Range.AddressLocal]);
//Adding calculated fields to cache.
PivotCacheFieldImpl Result = (cash_data as PivotCacheImpl).CacheFields.AddNewField("Result", "Numerator/Denominator");
PivotCacheFieldImpl Result2 = (cash_data as PivotCacheImpl).CacheFields.AddNewField("Result2", "Numerator*1");
//Percentage indicator - Pivot Table (Percentage)
#region "Percentage Pivot Table"
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A5"], cash_data);
IPivotField field = pivotTable.Fields["Result"];
pivotTable.DataFields.Add(field, "%", PivotSubtotalTypes.Sum);
pivotTable.Fields["Region"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Province"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["District"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Round"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Month"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Measure"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Form"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Indicator"].Axis = PivotAxisTypes.Row;
IPivotField num = pivotTable.Fields["Numerator"];
IPivotField denom = pivotTable.Fields["Denominator"];
if (Measure.Equals("1"))
{
pivotTable.Fields["Region"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by region";
}
else if (Measure.Equals("2"))
{
pivotTable.Fields["Province"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by province";
}
else if (Measure.Equals("3"))
{
pivotTable.Fields["District"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by district";
}
else if (Measure.Equals("4"))
{
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by year";
}
else if (Measure.Equals("5"))
{
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotTable.Fields["Month"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by month";
IPivotField pivotField = pivotTable.Fields["Month"];
pivotField.Sort(new string[12] {
"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
});
}
pivotSheet.Range["A2"].CellStyle.Font.Size = 14f;
pivotSheet.Range["A2"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
pivotSheet.Range["A3"].CellStyle.Font.Size = 10f;
pivotSheet.Range["A3"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].CellStyle.Font.Italic = true;
pivotTable.Fields["Measure"].Axis = PivotAxisTypes.Page;
IPivotField pageField = pivotTable.Fields["Measure"];
pageField.Items[0].Visible = false;
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
PivotTableOptions pivotOption = pivotTable.Options as PivotTableOptions;
pivotOption.ShowGridDropZone = true;
IPivotTableOptions option = pivotTable.Options;
option.ErrorString = "NA";
option.ColumnHeaderCaption = "CompareBy";
option.RowHeaderCaption = "Indicator";
#endregion
//Number indicator - Pivot Table (Numeric)
#region "Numeric Pivot Table"
IWorksheet pivotSheet2 = workbook.Worksheets[2];
pivotSheet2.Name = "Numeric";
IPivotTable pivotTable2 = pivotSheet2.PivotTables.Add("PivotTable2", pivotSheet["A5"], cash_data);
IPivotField field2 = pivotTable2.Fields["Result2"];
pivotTable2.DataFields.Add(field2, "#", PivotSubtotalTypes.Sum);
pivotTable2.Fields["Region"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Province"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["District"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Round"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Month"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Form"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Indicator"].Axis = PivotAxisTypes.Row;
IPivotField num2 = pivotTable2.Fields["Numerator"];
IPivotField denom2 = pivotTable2.Fields["Denominator"];
if (Measure.Equals("1"))
{
pivotTable2.Fields["Region"].Axis = PivotAxisTypes.Column;
pivotSheet2["A2"].Text = "Indicators comparison by region";
}
else if (Measure.Equals("2"))
{
pivotTable2.Fields["Province"].Axis = PivotAxisTypes.Column;
pivotSheet2["A2"].Text = "Indicators comparison by province";
}
else if (Measure.Equals("3"))
{
pivotTable2.Fields["District"].Axis = PivotAxisTypes.Column;
pivotSheet2["A2"].Text = "Indicators comparison by district";
}
else if (Measure.Equals("4"))
{
pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotSheet2["A2"].Text = "Indicators comparison by year";
}
else if (Measure.Equals("5"))
{
pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotTable2.Fields["Month"].Axis = PivotAxisTypes.Column;
pivotSheet2["A2"].Text = "Indicators comparison by month";
IPivotField pivotField = pivotTable2.Fields["Month"];
pivotField.Sort(new string[12] {
"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
});
}
pivotSheet2.Range["A2"].CellStyle.Font.Size = 14f;
pivotSheet2.Range["A2"].CellStyle.Font.Bold = true;
pivotSheet2.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
pivotSheet2.Range["A3"].CellStyle.Font.Size = 10f;
pivotSheet2.Range["A3"].CellStyle.Font.Bold = true;
pivotSheet2.Range["A3"].CellStyle.Font.Italic = true;
pivotTable2.Fields["Measure"].Axis = PivotAxisTypes.Page;
IPivotField pageField2 = pivotTable2.Fields["Measure"];
pageField2.Items[1].Visible = false;
pivotTable2.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
pivotTable2.ColumnGrand = false;
pivotTable2.RowGrand = false;
PivotTableOptions pivotOption2 = pivotTable2.Options as PivotTableOptions;
pivotOption2.ShowGridDropZone = true;
IPivotTableOptions option2 = pivotTable2.Options;
option2.ErrorString = "NA";
option2.ColumnHeaderCaption = "CompareBy";
option2.RowHeaderCaption = "Indicator";
pivotTable.Fields["Result"].NumberFormat = "#.0%";
pivotTable2.Fields["Result2"].NumberFormat = "#";
(pivotTable.Options as PivotTableOptions).ShowGridDropZone = true;
(pivotTable2.Options as PivotTableOptions).ShowGridDropZone = true;
(pivotTable as PivotTableImpl).Cache.IsRefreshOnLoad = true;
(pivotTable2 as PivotTableImpl).Cache.IsRefreshOnLoad = true;
pivotSheet2.Range["A5"].Activate();
pivotSheet2.Activate();
#endregion
string ContentType = "Application/msexcel";
string filename = "Report_" + "donor" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
workbook.Close();
excelEngine.Dispose();
return(File(ms, ContentType, filename));
}
public ActionResult CreateChart()
{
var data = _context.lqasLust.Select
(x => new
{
Region = x.Region,
Province = x.Province,
District = x.District,
Year = x.Year,
Month = x.Month,
Round = x.Round,
MonthName = x.MonthName,
Comments = x.Comments,
LotNo = x.LotNo
}
).Where(m => m.Month < 5).ToList();
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook templateWorkbook;
templateWorkbook = application.Workbooks.Open(Server.MapPath("/App_Data/book1.xlsx"));
IWorksheet sheet = templateWorkbook.Worksheets[0];
IWorkbook workbook;
workbook = application.Workbooks.Create(3);
IWorksheet worksheet = workbook.Worksheets[0];
if (!data.Any())
{
return(HttpNotFound());
}
sheet.ImportData(data, 1, 1, true);
sheet.Name = "Data";
IWorksheet pivotSheet = templateWorkbook.Worksheets[1];
pivotSheet.Name = "Pivot";
IPivotCache cash_data = workbook.PivotCaches.Add(sheet[sheet.Range.AddressLocal]);
//Percentage indicator - Pivot Table (Percentage)
#region "Percentage Pivot Table"
IWorksheet pivotTableSheet = templateWorkbook.Worksheets[1];
IPivotTable pivotTable = pivotTableSheet.PivotTables[0];
IPivotField pivotField = pivotTable.Fields["MonthName"];
pivotField.Sort(new string[12] {
"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
});
IChartShape pivotChartSheet = templateWorkbook.Worksheets[2].Charts[0];
//Set the Pivot source for the Pivot Chart.
pivotChartSheet.PivotSource = pivotTable;
//Set the Pivot Chart Type.
pivotChartSheet.PivotChartType = ExcelChartType.Column_Stacked_100;
pivotChartSheet.Series[0].SerieFormat.CommonSerieOptions.Overlap = 100;
pivotChartSheet.Series[0].SerieFormat.CommonSerieOptions.GapWidth = 10;
pivotChartSheet.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
pivotChartSheet.Series[1].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
pivotChartSheet.Series[2].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
pivotChartSheet.Series[0].SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.LightGreen;
pivotChartSheet.Series[1].SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.Green;
pivotChartSheet.Series[2].SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.Red;
pivotChartSheet.TopRow = 2;
pivotChartSheet.BottomRow = 30;
pivotChartSheet.LeftColumn = 1;
pivotChartSheet.RightColumn = 20;
pivotChartSheet.ChartTitle = "This is a sample chart";
pivotChartSheet.ShowAllFieldButtons = false;
(pivotTable as PivotTableImpl).Cache.SourceRange = sheet.UsedRange;
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
PivotTableOptions pivotOption = pivotTable.Options as PivotTableOptions;
pivotOption.ShowGridDropZone = true;
IPivotTableOptions option = pivotTable.Options;
option.ErrorString = "NA";
#endregion
(pivotTable.Options as PivotTableOptions).ShowGridDropZone = true;
(pivotTable as PivotTableImpl).Cache.IsRefreshOnLoad = true;
IWorksheet pivotChart = templateWorkbook.Worksheets[2];
pivotChart.Name = "Chart";
pivotChart.Activate();
string ContentType = "Application/msexcel";
string filename = "Report_" + "my" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";
MemoryStream ms = new MemoryStream();
templateWorkbook.SaveAs(ms);
ms.Position = 0;
workbook.Close();
excelEngine.Dispose();
return(File(ms, ContentType, filename));
}
public ActionResult pivotNewInpatients([Bind(Include = "ProvCode,FacilityId,Year")] CreateReq req)
{
string ProvinceCode;
int FacilityId, Year;
ProvinceCode = req.ProvCode;
FacilityId = req.FacilityId;
Year = req.Year;
var data = _context.PvtNewInpatientCases.AsNoTracking().ToList();
if (ProvinceCode != "" && FacilityId != 0 && Year != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Year.Equals(Year) && m.Province.Equals(ProvinceCode) && m.FacilityId.Equals(FacilityId)).ToList();
}
else if (ProvinceCode != "" & FacilityId != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Province.Equals(ProvinceCode) && m.FacilityId.Equals(FacilityId)).ToList();
}
else if (ProvinceCode != "" && Year != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Province.Equals(ProvinceCode) && m.Year.Equals(Year)).ToList();
}
else if (ProvinceCode != "")
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Province.Equals(ProvinceCode)).ToList();
}
else if (FacilityId != 0 && Year != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.FacilityId.Equals(FacilityId) && m.Year.Equals(Year)).ToList();
}
else if (FacilityId != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.FacilityId.Equals(FacilityId)).ToList();
}
else if (Year != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Year.Equals(Year)).ToList();
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
sheet.ImportData(data, 1, 1, true);
sheet.Name = "Data";
IWorksheet pivotSheet = workbook.Worksheets[1];
pivotSheet.Name = "PivotTable";
pivotSheet["A2"].Text = "HMIR New Inpatient Cases";
pivotSheet.Range["A2"].CellStyle.Font.Size = 14f;
pivotSheet.Range["A2"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
pivotSheet.Range["A3"].CellStyle.Font.Size = 10f;
pivotSheet.Range["A3"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].CellStyle.Font.Italic = true;
if (sheet == null)
{
ModelState.AddModelError("Error", "There was error in selection or there is no data for the selected criteria");
return(RedirectToAction("Index"));
}
IPivotCache cash_data = workbook.PivotCaches.Add(sheet.UsedRange);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A5"], cash_data);
IPivotTableOptions options = pivotTable.Options;
options.ShowFieldList = false;
pivotTable.Fields["District"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Province"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["FacilityId"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["FacilityName"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["FacilityType"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Month"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["ElementName"].Axis = PivotAxisTypes.Row;
IPivotField FemaleOver5 = pivotTable.Fields["FemaleOver5"];
IPivotField FemaleUnder5 = pivotTable.Fields["FemaleUnder5"];
IPivotField MaleOver5 = pivotTable.Fields["MaleOver5"];
IPivotField MaleUnder5 = pivotTable.Fields["MaleUnder5"];
IPivotField ReferreddIn = pivotTable.Fields["ReferredIn"];
IPivotField ReferredOut = pivotTable.Fields["ReferredOut"];
IPivotField Deaths = pivotTable.Fields["Deaths"];
IPivotField Total = pivotTable.Fields["Total"];
pivotTable.DataFields.Add(FemaleOver5, "FO5", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(FemaleUnder5, "FU5", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(MaleOver5, "MO5", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(MaleUnder5, "MU5", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(ReferreddIn, "ReferIns", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(ReferredOut, "ReferOuts", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(Deaths, "Deaths", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(Total, "Total", PivotSubtotalTypes.Sum);
IPivotTableOptions option = pivotTable.Options;
option.ErrorString = "X";
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark20;
pivotSheet.Activate();
string ContentType = "Application/msexcel";
string filename = "HMIR_" + "NewInpatientCases" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
workbook.Close();
excelEngine.Dispose();
return(File(ms, ContentType, filename));
}
public ActionResult DistInd([Bind(Include = "Region,Province,District,Round,Measure,Filter")] requestfilter req)
{
Int16 Region;
Int16 Province;
Int16 District;
Int16 Round;
string Measure;
string Filter;
Region = req.Region;
Province = req.Province;
District = req.District;
Round = req.Round;
Measure = req.Measure;
Filter = req.Filter;
var data = _context.IndDistircts.Select
(x => new
{
Region = x.Region,
Province = x.Province,
District = x.District,
Year = x.Year,
Month = x.Month,
Round = x.Round,
ShorName = x.ShortName,
Indicator = x.Indicator,
Measure = x.Measure,
Numerator = x.Numerator,
Denominator = x.Denominator,
RegionId = x.RegionId,
ProvinceId = x.ProvinceId,
DistrictId = x.DistrictId,
RoundId = x.RoundId,
CampaignType = x.CampaignType,
Form = x.Form
}
).ToList() /*.Where(m=>m.Measure.Equals(Measure) & m.Denominator>0)*/;
if (!Region.Equals(0) & !Province.Equals(0) & !District.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province) & m.DistrictId.Equals(District)).ToList();
}
else if (!Region.Equals(0) & !Province.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province)).ToList();
}
else if (!Region.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region)).ToList();
}
if (!Round.Equals(0))
{
data = data.Where(m => m.RoundId.Equals(Round)).ToList();
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook;
workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
if (!data.Any())
{
return(HttpNotFound());
}
sheet.ImportData(data, 1, 1, true);
sheet.Name = "Data";
IWorksheet pivotSheet = workbook.Worksheets[1];
pivotSheet.Name = "Result";
IPivotCache cash_data = workbook.PivotCaches.Add(sheet[sheet.Range.AddressLocal]);
//Percentage indicator
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A5"], cash_data);
//IPivotTableOptions options = pivotTable.Options;
pivotTable.Fields["Region"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Province"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["District"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Round"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Month"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Form"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Indicator"].Axis = PivotAxisTypes.Row;
IPivotField num = pivotTable.Fields["Numerator"];
IPivotField denom = pivotTable.Fields["Denominator"];
if (Measure.Equals("1"))
{
pivotTable.Fields["Region"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by region";
}
else if (Measure.Equals("2"))
{
pivotTable.Fields["Province"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by province";
}
else if (Measure.Equals("3"))
{
pivotTable.Fields["District"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by district";
}
else if (Measure.Equals("4"))
{
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by year";
}
else if (Measure.Equals("5"))
{
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotTable.Fields["Month"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by month";
IPivotField pivotField = pivotTable.Fields["Month"];
pivotField.Sort(new string[1] {
pivotField.Items[1].Text
});
}
pivotSheet.Range["A2"].CellStyle.Font.Size = 14f;
pivotSheet.Range["A2"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
pivotSheet.Range["A3"].CellStyle.Font.Size = 10f;
pivotSheet.Range["A3"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].CellStyle.Font.Italic = true;
pivotTable.Fields["Measure"].Axis = PivotAxisTypes.Page;
IPivotField pageField = pivotTable.Fields["Measure"];
pageField.Items[0].Visible = false;
if (Filter == "%")
{
IPivotField Result = pivotTable.CalculatedFields.Add("Result", "Numerator/Denominator");
Result.Name = " Result";
Result.NumberFormat = "#.0%";
}
else if (Filter == "#")
{
IPivotField Result = pivotTable.CalculatedFields.Add("Result", "Numerator*1");
Result.Name = " Result";
Result.NumberFormat = "#";
}
//IPivotField pivotField = pivotTable.Fields[0];
//pivotField.Sort(new string[1] { pivotField.Items[4].Text });
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
PivotTableOptions pivotOption = pivotTable.Options as PivotTableOptions;
pivotOption.ShowGridDropZone = true;
IPivotTableOptions option = pivotTable.Options;
option.ErrorString = "NA";
option.ColumnHeaderCaption = "CompareBy";
option.RowHeaderCaption = "Indicator";
pivotSheet.Activate();
//else if (Filter == "#")
//{
// //Number indicator
// IWorksheet pivotSheet2 = workbook.Worksheets[1];
// pivotSheet2.Name = "Numeric";
// IPivotTable pivotTable2 = pivotSheet2.PivotTables.Add("PivotTable2", pivotSheet["A5"], cash_data);
// //IPivotTableOptions options2 = pivotTable2.Options;
// //options2.ShowFieldList = true;
// pivotTable2.Fields["Region"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Province"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["District"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Round"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Month"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Form"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Indicator"].Axis = PivotAxisTypes.Row;
// IPivotField num2 = pivotTable2.Fields["Numerator"];
// IPivotField denom2 = pivotTable2.Fields["Denominator"];
// if (Measure.Equals("1"))
// {
// pivotTable2.Fields["Region"].Axis = PivotAxisTypes.Column;
// pivotSheet2["A2"].Text = "Indicators comparison by region";
// }
// else if (Measure.Equals("2"))
// {
// pivotTable2.Fields["Province"].Axis = PivotAxisTypes.Page;
// pivotSheet2["A2"].Text = "Indicators comparison by province";
// }
// else if (Measure.Equals("3"))
// {
// pivotTable2.Fields["District"].Axis = PivotAxisTypes.Page;
// pivotSheet2["A2"].Text = "Indicators comparison by district";
// }
// else if (Measure.Equals("4"))
// {
// pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Page;
// pivotSheet2["A2"].Text = "Indicators comparison by year";
// }
// else if (Measure.Equals("5"))
// {
// pivotTable2.Fields["Month"].Axis = PivotAxisTypes.Page;
// pivotSheet2["A2"].Text = "Indicators comparison by month";
// }
// pivotSheet2.Range["A2"].CellStyle.Font.Size = 14f;
// pivotSheet2.Range["A2"].CellStyle.Font.Bold = true;
// pivotSheet2.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString(); ;
// pivotSheet2.Range["A3"].CellStyle.Font.Size = 10f;
// pivotSheet2.Range["A3"].CellStyle.Font.Bold = true;
// pivotSheet2.Range["A3"].CellStyle.Font.Italic = true;
// IPivotField result2 = pivotTable2.CalculatedFields.Add("Result2", "Numerator*1");
// result2.Name = " #";
// result2.NumberFormat = "#";
// pivotTable2.Fields["Measure"].Axis = PivotAxisTypes.Page;
// IPivotField pageField2 = pivotTable2.Fields["Measure"];
// pageField2.Items[1].Visible = false;
// pivotTable2.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
// pivotTable2.ColumnGrand = false;
// pivotTable2.RowGrand = false;
// PivotTableOptions pivotOption2 = pivotTable2.Options as PivotTableOptions;
// pivotOption2.ShowGridDropZone = true;
// IPivotTableOptions option2 = pivotTable2.Options;
// option2.ErrorString = "NA";
// option2.ColumnHeaderCaption = "CompareBy";
// option2.RowHeaderCaption = "Indicator";
// pivotSheet2.Range["A5"].Activate();
// pivotSheet2.Activate();
//}
string ContentType = "Application/msexcel";
string filename = "NEOC_" + "Indicators" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
workbook.Close();
excelEngine.Dispose();
return(File(ms, ContentType, filename));
}
