public ActionResult viewPRExcel(Int16 fiscalYear, Int16? coeID)
{
ModelState.Clear();
var viewModel = new PRViewModel
{
//allCoEs = db.CoEs.ToList(),
allCoEs = db.CoEs.ToList(),
allMaps = db.Indicator_CoE_Maps.ToList(),
allFootnoteMaps = db.Indicator_Footnote_Maps.ToList()
};
// Create the workbook
var wb = new XLWorkbook();
var prBlue = ExcelGlobalVariables.prBlue;// XLColor.FromArgb(0, 51, 102);
var prGreen = ExcelGlobalVariables.prGreen;//XLColor.FromArgb(0, 118, 53);
var prYellow = ExcelGlobalVariables.prYellow; //XLColor.FromArgb(255, 192, 0);
var prRed = ExcelGlobalVariables.prRed;// XLColor.FromArgb(255, 0, 0);
var prHeader1Fill = ExcelGlobalVariables.prHeader1Fill;//prBlue;
var prHeader1Font = ExcelGlobalVariables.prHeader1Font;//XLColor.White;
var prHeader2Fill = ExcelGlobalVariables.prHeader2Fill;//XLColor.White;
var prHeader2Font = ExcelGlobalVariables.prHeader2Font;//XLColor.Black;
var prBorder = ExcelGlobalVariables.prBorder;//XLColor.FromArgb(0, 0, 0);
var prAreaFill = ExcelGlobalVariables.prAreaFill;//XLColor.FromArgb(192, 192, 192);
var prAreaFont = ExcelGlobalVariables.prAreaFont;//XLColor.Black;
var prBorderWidth = XLBorderStyleValues.Thin;
var prFontSize = 10;
var prTitleFont = 20;
var prFootnoteSize = 8;
var prHeightSeperator = 7.5;
var prAreaObjectiveFontsize = 8;
var indentLength = 2;
var newLineHeight = 12.6;
var defNote = "Portal data from the Canadian Institute for Health Information (CIHI) has been used to generate data within this report with acknowledgement to CIHI, the Ministry of Health and Long-Term Care (MOHLTC) and Stats Canada (as applicable). Views are not those of the acknowledged sources. Facility identifiable data other than Mount Sinai Hospital (MSH) is not to be published without the consent of that organization (except where reported at an aggregate level). As this is not a database supported by MSH, please demonstrate caution with use and interpretation of the information. MSH is not responsible for any changes derived from the source data/canned reports. Data may be subject to change.";
var prNumberWidth = 4;
var prIndicatorWidth = 55;
var prValueWidth = 11;
var prDefWidth = 100;
var prRatiWidth = 50;
var prCompWidth = 50;
//var fitRatio = 3.77;
var fitRatio = 1.7;
List<int> fitAdjustableRows = new List<int>();
var prFootnoteCharsNewLine = 125;
var prObjectivesCharsNewLine = 226;
var allCoes = new List<CoEs>();
if (coeID != 0 && coeID != null)
{
allCoes = viewModel.allCoEs.Where(x => x.CoE_ID == coeID).ToList();
}
else
{
allCoes = viewModel.allCoEs.ToList();
}
foreach (var coe in allCoes)
{
var wsPRName = coe.CoE_Abbr != null && coe.CoE_Abbr != "" ? coe.CoE_Abbr : "Indicators";
var wsDefName = coe.CoE_Abbr != null && coe.CoE_Abbr != "" ? "Def_" + coe.CoE_Abbr : "Def_Indicators";
var wsPR = wb.Worksheets.Add(wsPRName);
var wsDef = wb.Worksheets.Add(wsDefName);
List<IXLWorksheet> wsList = new List<IXLWorksheet>();
wsList.Add(wsPR);
wsList.Add(wsDef);
foreach (var ws in wsList)
{
var currentRow = 4;
ws.Row(2).Height = 21;
int startRow;
int indicatorNumber = 1;
ws.PageSetup.Margins.Top = 0;
ws.PageSetup.Margins.Header = 0;
ws.PageSetup.Margins.Left = 0.5;
ws.PageSetup.Margins.Right = 0.5;
ws.PageSetup.Margins.Bottom = 0.5;
ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
ws.PageSetup.PaperSize = XLPaperSize.LegalPaper;
ws.PageSetup.FitToPages(1, 1);
string[,] columnHeaders = new string[0, 0];
if (ws.Name == wsPRName)
{
var prHeadder2Title = FiscalYear.FYStrFull("FY_", fiscalYear) + "Performance";
prHeadder2Title = prHeadder2Title.Replace("_", " ");
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{FiscalYear.FYStrFull("FY_3", fiscalYear), ""},
{FiscalYear.FYStrFull("FY_2", fiscalYear),""},
{FiscalYear.FYStrFull("FY_1", fiscalYear),""},
{prHeadder2Title,"Q1"},
{prHeadder2Title,"Q2"},
{prHeadder2Title,"Q3"},
{prHeadder2Title,"Q4"},
{prHeadder2Title,"YTD"},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Target",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Performance_Threshold",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator",""}
};
}
else if (ws.Name == wsDefName)
{
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Definition_Calculation",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Target_Rationale",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator_Source",""}
};
}
var currentCol = 1;
var prHeader2ColStart = 99;
var prHeader2ColEnd = 1;
int maxCol = columnHeaders.GetUpperBound(0) + 1;
var prTitle = ws.Cell(currentRow, 1);
prTitle.Value = coe.CoE;
prTitle.Style.Font.FontSize = prTitleFont;
prTitle.Style.Font.Bold = true;
prTitle.Style.Font.FontColor = prHeader1Font;
prTitle.Style.Fill.BackgroundColor = prHeader1Fill;
ws.Range(ws.Cell(currentRow, 1), ws.Cell(currentRow, maxCol)).Merge();
ws.Range(ws.Cell(currentRow + 1, 1), ws.Cell(currentRow + 1, maxCol)).Merge();
ws.Row(currentRow + 1).Height = prHeightSeperator;
currentRow += 2;
startRow = currentRow;
for (int i = 0; i <= columnHeaders.GetUpperBound(0); i++)
{
if (columnHeaders[i, 1] == "")
{
var columnField = columnHeaders[i, 0];
string cellValue;
Type t = typeof(Indicators);
cellValue = t.GetProperty(columnField) != null ?
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicators), columnField).DisplayName :
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicator_CoE_Maps), columnField).DisplayName;
ws.Cell(currentRow, currentCol).Value = cellValue;
ws.Range(ws.Cell(currentRow, currentCol), ws.Cell(currentRow + 1, currentCol)).Merge();
currentCol++;
}
else
{
var columnField = columnHeaders[i, 1];
var columnFieldTop = columnHeaders[i, 0];
ws.Cell(currentRow + 1, currentCol).Value = columnField;
ws.Cell(currentRow, currentCol).Value = columnFieldTop;
if (currentCol < prHeader2ColStart) { prHeader2ColStart = currentCol; }
if (currentCol > prHeader2ColEnd) { prHeader2ColEnd = currentCol; }
currentCol++;
}
}
currentCol--;
ws.Range(ws.Cell(currentRow, prHeader2ColStart).Address, ws.Cell(currentRow, prHeader2ColEnd).Address).Merge();
var prHeader1 = ws.Range(ws.Cell(currentRow, 1).Address, ws.Cell(currentRow + 1, currentCol).Address);
var prHeader2 = ws.Range(ws.Cell(currentRow + 1, prHeader2ColStart).Address, ws.Cell(currentRow + 1, prHeader2ColEnd).Address);
prHeader1.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
prHeader1.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
prHeader1.Style.Fill.BackgroundColor = prHeader1Fill;
prHeader1.Style.Font.FontColor = prHeader1Font;
prHeader2.Style.Fill.BackgroundColor = prHeader2Fill;
prHeader2.Style.Font.FontColor = prHeader2Font;
currentRow += 2;
List<Footnotes> footnotes = new List<Footnotes>();
foreach (var areaMap in coe.Area_CoE_Map.Where(x => x.Fiscal_Year == fiscalYear).OrderBy(x => x.Area.Sort))
{
var cellLengthObjective = 0;
var prArea = ws.Range(ws.Cell(currentRow, 1), ws.Cell(currentRow, maxCol));
//fitAdjustableRows.Add(currentRow);
prArea.Merge();
prArea.Style.Fill.BackgroundColor = prAreaFill;
prArea.Style.Font.FontColor = prAreaFont;
prArea.FirstCell().RichText.AddText(areaMap.Area.Area).Bold = true;
cellLengthObjective += areaMap.Area.Area.Length;
if (ws == wsPR)
{
var indent = new string('_', indentLength);
var stringSeperators = new string[] { "•" };
if (areaMap.Objective != null)
{
var objectives = Regex.Matches(areaMap.Objective, @"\[.*?\]").Cast<Match>().Select(m => m.Value.Substring(1, m.Value.Length - 2)).ToList();
//for (var i = 1; i < objectives.Length; i++)
var i = 1;
foreach (var objective in objectives)
{
prArea.FirstCell().RichText.AddNewLine();
ws.Row(currentRow).Height += newLineHeight;
prArea.FirstCell().RichText.AddText(indent).SetFontColor(prAreaFill).SetFontSize(prAreaObjectiveFontsize);
prArea.FirstCell().RichText.AddText(" " + i +". " + objective).FontSize = prAreaObjectiveFontsize;
i++;
}
}
}
currentRow++;
var allMaps = viewModel.allMaps.Where(x => x.Fiscal_Year == fiscalYear).Where(e => e.Indicator.Area.Equals(areaMap.Area)).Where(d => d.CoE.CoE != null && d.CoE.CoE.Contains(coe.CoE)).OrderBy(f => f.Number).ToList();
var allNValues = new List<Indicator_CoE_Maps>();
if (ws.Name == wsPRName)
{
allNValues = viewModel.allMaps.Where(x => x.Fiscal_Year == fiscalYear && x.Indicator.Indicator_N_Value == true).ToList();
}
var allMapsWithNValues = new List<Indicator_CoE_Maps>();
foreach (var nValue in allNValues)
{
var indicatorIndex = allMaps.FirstOrDefault(x => x.Indicator_ID == nValue.Indicator.Indicator_N_Value_ID);
if (indicatorIndex != null)
{
var position = allMaps.IndexOf(indicatorIndex);
allMapsWithNValues.Add(indicatorIndex);
allMaps.Insert(position + 1, nValue);
}
}
foreach (var map in allMaps)
{
fitAdjustableRows.Add(currentRow);
currentCol = 1;
int rowSpan = 1;
if (allMapsWithNValues.Contains(map) || !allNValues.Contains(map))
{
if (allMapsWithNValues.Contains(map))
{
rowSpan = 2;
ws.Range(ws.Cell(currentRow, currentCol), ws.Cell(currentRow + 1, currentCol)).Merge();
ws.Range(ws.Cell(currentRow, currentCol + 1), ws.Cell(currentRow + 1, currentCol + 1)).Merge();
}
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorder = prBorderWidth;
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorderColor = prBorder;
ws.Cell(currentRow, currentCol).Value = indicatorNumber;
indicatorNumber++;
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
currentCol++;
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorder = prBorderWidth;
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorderColor = prBorder;
int j = 0;
ws.Cell(currentRow, currentCol).Value = map.Indicator.Indicator;
foreach (var footnote in map.Indicator.Indicator_Footnote_Map.Where(x => x.Fiscal_Year == fiscalYear).Where(e => e.Indicator_ID == map.Indicator_ID).OrderBy(e => e.Indicator_ID))
{
if (!footnotes.Contains(footnote.Footnote)) { footnotes.Add(footnote.Footnote); }
if (j != 0)
{
ws.Cell(currentRow, currentCol).RichText.AddText(",").VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
}
ws.Cell(currentRow, currentCol).RichText.AddText(footnote.Footnote.Footnote_Symbol).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
j++;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
}
else
{
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorder = prBorderWidth;
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorderColor = prBorder;
currentCol += 2;
rowSpan = 0;
}
if (ws.Name == wsPRName)
{
for (var i = 3; i <= 15; i++)
{
ws.Column(i).Width = ws.Name == wsPRName ? prValueWidth : prDefWidth;
}
var obj = map.Indicator;
var type = obj.GetType();
string[,] columnIndicators = new string[,]{
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_3",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_3",fiscalYear) + "_Sup").GetValue(obj,null),
"",
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear) + "_Sup").GetValue(obj,null),
"",
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear) + "_Sup").GetValue(obj,null),
"",
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1_Color").GetValue(obj,null),
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2_Color").GetValue(obj,null),
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3_Color").GetValue(obj,null),
"1",
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4_Color").GetValue(obj,null),
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD_Color").GetValue(obj,null),
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target_Sup").GetValue(obj,null),
"",
rowSpan.ToString()
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Performance_Threshold").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Performance_Threshold_Sup").GetValue(obj,null),
"",
rowSpan.ToString()
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator_Sup").GetValue(obj,null),
"",
rowSpan.ToString()
},
};
var startCol = currentCol;
int k = 1;
for (var i = 0; i <= columnIndicators.GetUpperBound(0); i++)
{
for (var j = 0; j <= columnIndicators.GetUpperBound(1); j++)
{
if (columnIndicators[i, j] != null)
{
columnIndicators[i, j] = columnIndicators[i, j].Replace("<b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sub>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sub>", "");
}
}
if (i != columnIndicators.GetUpperBound(0) && columnIndicators[i, 0] == "=")
{
k = 1;
while (columnIndicators[i + k, 0] == "=") { k++; }
ws.Range(ws.Cell(currentRow, startCol + i - 1), ws.Cell(currentRow, startCol + i + k - 1)).Merge();
i += k - 1;
k = 1;
}
else if (columnIndicators[i, 0] != "=")
{
ws.Cell(currentRow, currentCol + i).Style.Border.OutsideBorder = prBorderWidth;
ws.Cell(currentRow, currentCol + i).Style.Border.OutsideBorderColor = prBorder;
if (columnIndicators[i, 3] != "0")
{
if (columnIndicators[i, 3] == "2") {
ws.Range(ws.Cell(currentRow, currentCol + i), ws.Cell(currentRow + 1, currentCol + i)).Merge();
}
if (allNValues.Contains(map))
{
ws.Cell(currentRow, currentCol + i).Style.Border.TopBorder = XLBorderStyleValues.None;
}
else if (allMapsWithNValues.Contains(map))
{
ws.Cell(currentRow, currentCol + i).Style.Border.BottomBorder = XLBorderStyleValues.None;
}
var cell = ws.Cell(currentRow, currentCol + i);
string cellValue = "";
if (columnIndicators[i, 0] != null)
{
cellValue = columnIndicators[i, 0].ToString();
}
if (cellValue.Contains("$"))
{
}
cell.Value = "'" + cellValue;
if (columnIndicators[i, 1] != null)
{
cell.RichText.AddText(columnIndicators[i, 1]).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
}
switch (columnIndicators[i, 2])
{
case "cssWhite":
cell.RichText.SetFontColor(XLColor.Black);
cell.Style.Fill.BackgroundColor = XLColor.White;
break;
case "cssGreen":
cell.RichText.SetFontColor(XLColor.White);
cell.Style.Fill.BackgroundColor = prGreen;
break;
case "cssYellow":
cell.RichText.SetFontColor(XLColor.Black);
cell.Style.Fill.BackgroundColor = prYellow;
break;
case "cssRed":
cell.RichText.SetFontColor(XLColor.White);
cell.Style.Fill.BackgroundColor = prRed;
break;
}
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
}
}
currentRow++;
}
else if (ws.Name == wsDefName)
{
ws.Column(3).Width = prDefWidth;
ws.Column(4).Width = prRatiWidth;
ws.Column(5).Width = prCompWidth;
var obj = map.Indicator;
var type = obj.GetType();
string defn = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Definition_Calculation").GetValue(obj, null);
string rationale = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Target_Rationale").GetValue(obj, null);
string comp = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator_Source").GetValue(obj, null);
double maxLines = 1;
double lines;
if (defn != null)
{
lines = defn.Length / ws.Column(currentCol).Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cell(currentRow, currentCol).Value = defn;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
if (rationale != null)
{
lines = rationale.Length / ws.Column(currentCol).Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cell(currentRow, currentCol).Value = rationale;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
if (comp != null)
{
lines = comp.Length / ws.Column(currentCol).Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cell(currentRow, currentCol).Value = comp;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
ws.Row(currentRow).Height = newLineHeight * Math.Ceiling(maxLines);
currentRow++;
}
}
}
var footnoteRow = ws.Range(ws.Cell(currentRow, 1), ws.Cell(currentRow, maxCol));
footnoteRow.Merge();
footnoteRow.Style.Font.FontSize = prFootnoteSize;
/*Footnotes defaultFootnote = db.Footnotes.FirstOrDefault(x => x.Footnote_Symbol == "*");
if (!footnotes.Contains(defaultFootnote))
{
footnotes.Add(defaultFootnote);
}*/
int cellLengthFootnote = 0;
if (ws.Name == wsPRName)
{
foreach (var footnote in footnotes.OrderBy(x=>x.Footnote_Order))
{
ws.Cell(currentRow, 1).RichText.AddText(footnote.Footnote_Symbol).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
ws.Cell(currentRow, 1).RichText.AddText(" " + footnote.Footnote + ";");
ws.Cell(currentRow, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
cellLengthFootnote += footnote.Footnote_Symbol.ToString().Length + footnote.Footnote.ToString().Length + 2;
if (cellLengthFootnote > prFootnoteCharsNewLine)
{
ws.Cell(currentRow, 1).RichText.AddNewLine();
cellLengthFootnote = 0;
ws.Row(currentRow).Height += newLineHeight;
}
}
}
else
{
ws.Cell(currentRow, 1).Value = defNote;
ws.Row(currentRow).Height = 28;
}
var pr = ws.Range(ws.Cell(startRow, 1), ws.Cell(currentRow - 1, maxCol));
if (pr.Worksheet.Name == wsDefName)
{
pr.Style.Border.InsideBorder = prBorderWidth;
pr.Style.Border.InsideBorderColor = prBorder;
}
pr.Style.Border.OutsideBorder = prBorderWidth;
pr.Style.Border.OutsideBorderColor = prBorder;
pr.Style.Font.FontSize = prFontSize;
pr = ws.Range(ws.Cell(startRow, 1), ws.Cell(currentRow, maxCol));
pr.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
pr.Style.Alignment.WrapText = true;
ws.Column(1).Width = prNumberWidth;
ws.Column(2).Width = prIndicatorWidth;
footnotes.Clear();
indicatorNumber = 1;
var totalHeight = ExcelFunctions.getTotalHeight(ws, 4);
var totalWidth = ExcelFunctions.getTotalWidth(ws, 1);
var fitHeight = (int)(totalWidth / fitRatio);
var fitWidth = (int)(totalHeight * fitRatio);
if (ws.Name == "Def_WIH Obs") { System.Diagnostics.Debugger.Break(); }
if (fitHeight > totalHeight)
{
var fitAddHeightTotal = (fitHeight - totalHeight);
var fitAddHeightPerRow = fitAddHeightTotal / fitAdjustableRows.Count;
foreach (var row in fitAdjustableRows)
{
ws.Row(row).Height += fitAddHeightPerRow;
}
}
else
{
while ((fitWidth - totalWidth) / fitWidth > 0.001)
{
var fitAddWidthTotal = (fitWidth - totalWidth) / 10;
var fitAddWidthPerRow = fitAddWidthTotal / (ws.LastColumnUsed().ColumnNumber() - 1);
foreach (var col in ws.Columns(2, ws.LastColumnUsed().ColumnNumber()))
{
col.Width += fitAddWidthPerRow / 5.69;
}
ExcelFunctions.AutoFitWorksheet(ws, 2, 3, newLineHeight);
totalHeight = ExcelFunctions.getTotalHeight(ws, 4);
totalWidth = ExcelFunctions.getTotalWidth(ws, 1);
fitHeight = (int)(totalWidth / fitRatio);
fitWidth = (int)(totalHeight * fitRatio);
}
}
}
}
MemoryStream preImage = new MemoryStream();
wb.SaveAs(preImage);
//Aspose.Cells.Workbook test = new Aspose.Cells.Workbook(preImage);
//test.Save(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logo.pdf"), Aspose.Cells.SaveFormat.Pdf);
MemoryStream postImage = new MemoryStream();
SLDocument postImageWb = new SLDocument(preImage);
string picPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logo.png");
SLPicture picLogo = new SLPicture(picPath);
string picPathOPEO = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logoOPEO.png");
SLPicture picLogoOPEO = new SLPicture(picPathOPEO);
string picMonthlyPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/Monthly.png");
SLPicture picMonthly = new SLPicture(picMonthlyPath);
string picQuaterlyPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/quaterly.png");
SLPicture picQuaterly = new SLPicture(picQuaterlyPath);
string picNAPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/na.png");
SLPicture picNA = new SLPicture(picNAPath);
string picTargetPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/target.png");
SLPicture picTarget = new SLPicture(picTargetPath);
foreach (var ws in wb.Worksheets)
{
postImageWb.SelectWorksheet(ws.Name);
for (int i = 1; i < 20; ++i)
{
var a = postImageWb.GetRowHeight(i);
}
picLogo.SetPosition(0, 0);
picLogo.ResizeInPercentage(25, 25);
postImageWb.InsertPicture(picLogo);
picLogoOPEO.SetRelativePositionInPixels(0, ws.LastColumnUsed().ColumnNumber() + 1, -140, 0);
picLogoOPEO.ResizeInPercentage(45, 45);
postImageWb.InsertPicture(picLogoOPEO);
if (ws.Name.Substring(0, 3) != "Def")
{
picTarget.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -240, 1);
picNA.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -400, 1);
picMonthly.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -500, 1);
picQuaterly.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -490, 1);
picMonthly.ResizeInPercentage(70, 70);
picQuaterly.ResizeInPercentage(70, 70);
picNA.ResizeInPercentage(70, 70);
picTarget.ResizeInPercentage(70, 70);
postImageWb.InsertPicture(picMonthly);
postImageWb.InsertPicture(picQuaterly);
postImageWb.InsertPicture(picNA);
postImageWb.InsertPicture(picTarget);
}
}
// Prepare the response
HttpResponse httpResponse = this.HttpContext.ApplicationInstance.Context.Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
httpResponse.AddHeader("content-disposition", "attachment;filename=\"test.xlsx\"");
//httpResponse.ContentType = "application/pdf";
//httpResponse.AddHeader("content-disposition", "attachment;filename=\"test.pdf\"");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
postImageWb.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
return View(viewModel);
}
