/// <summary>
/// Uploads a file to the webserver.
/// </summary>
/// <param name="title">The title for the file, to be used in the Database.</param>
/// <param name="type">What type of file is uploaded. Example: 'FirstAid' or 'DoctorsNote'.</param>
/// <param name="fileLocation">The local filepath to the document.</param>
/// <param name="url">The url of the server, where the file will be uploaded.</param>
/// <returns>Returns a bool wether the operation was completed or not.</returns>
private bool UploadFile(string title, string type, string fileLocation, string url)
{
Spire.Pdf.PdfDocument document = new Spire.Pdf.PdfDocument();
FileInfo file = new FileInfo(fileLocation);
if (!file.Exists)
{
return(false);
}
if (file.Extension != ".pdf")
{
Spire.Pdf.PdfPageBase page = document.Pages.Add();
PdfImage image = PdfImage.FromFile(fileLocation);
fileLocation = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}.pdf");
float widthFitRate = image.PhysicalDimension.Width / page.Canvas.ClientSize.Width;
float heightFitRate = image.PhysicalDimension.Height / page.Canvas.ClientSize.Height;
float fitRate = Math.Max(widthFitRate, heightFitRate);
float fitWidth = image.PhysicalDimension.Width / fitRate;
float fitHeight = image.PhysicalDimension.Height / fitRate;
page.Canvas.DrawImage(image, 30, 30, fitWidth, fitHeight);
document.DocumentInformation.Title = $"{Session.LoggedInUser.Fullname} - {type}";
document.DocumentInformation.Author = Session.LoggedInUser.Fullname;
document.DocumentInformation.CreationDate = DateTime.Now;
document.SaveToFile(fileLocation);
document.Close();
}
else
{
document.LoadFromFile(fileLocation);
document.DocumentInformation.Title = $"{Session.LoggedInUser.Fullname} - {type}";
document.DocumentInformation.Author = Session.LoggedInUser.Fullname;
document.DocumentInformation.CreationDate = DateTime.Now;
document.SaveToFile(fileLocation);
document.Close();
}
document.Dispose();
string fileUrl = SendToServer(fileLocation, url);
if (fileUrl == "null")
{
return(false);
}
if (!MySql.UploadDocument(title, type, DateTime.Today, Session.LoggedInUser.Id, fileUrl))
{
return(false);
}
return(true);
}
public static string MergePDFFiles(List <string> filesPaths)
{
Spire.Pdf.PdfDocument[] tickets = new Spire.Pdf.PdfDocument[filesPaths.Count()];
for (int i = 0; i < tickets.Length; i++)
{
tickets[i] = new Spire.Pdf.PdfDocument(filesPaths[i]);
}
Spire.Pdf.PdfDocument document = new Spire.Pdf.PdfDocument();
for (int i = 0; i < tickets.Length; i++)
{
document.InsertPage(tickets[i], 0);
}
string now = DateTime.Now.ToString("yyyy/MM/dd_HH.mm.ss.fff");
document.SaveToFile(@"D:\Virtuace\HW6\Booking\Booking\Tickets\" + now + ".pdf");
return(@"D:\Virtuace\HW6\Booking\Booking\Tickets\" + now + ".pdf");
}
public async Task GeneratePdf(string html, string filePath)
{
await Task.Run(() =>
{
using (FileStream ms = new FileStream(filePath, FileMode.Create))
{
PdfSharp.Pdf.PdfDocument pdf = TheArtOfDev.HtmlRenderer.PdfSharp.PdfGenerator.GeneratePdf(html, PdfSharp.PageSize.A4);
pdf.Save(ms);
ms.Close();
}
Spire.Pdf.PdfDocument abc = new Spire.Pdf.PdfDocument(filePath);
Spire.Pdf.Graphics.PdfImage image = Spire.Pdf.Graphics.PdfImage.FromFile(@"D:\Logo.png");
float width = image.Width * 0.75f;
float height = image.Height * 0.75f;
Spire.Pdf.PdfPageBase page0 = abc.Pages[0];
float x = (page0.Canvas.ClientSize.Width - width) / 2;
page0.Canvas.DrawImage(image, x, 60, width, height);
abc.SaveToFile(filePath);
});
}
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
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var ef = new ExcelPackage();
var wb = ef.Workbook;
var prBlue = ExcelColor.FromArgb(0, 51, 102);
var prGreen = ExcelColor.FromArgb(0, 118, 53);
var prYellow = ExcelColor.FromArgb(255, 192, 0);
var prRed = ExcelColor.FromArgb(255, 0, 0);
var prHeader1Fill = prBlue;
var prHeader1Font = ExcelColor.White;
var prHeader2Fill = ExcelColor.White;
var prHeader2Font = ExcelColor.Black;
var prBorder = ExcelColor.FromArgb(0, 0, 0);
var prAreaFill = ExcelColor.FromArgb(192, 192, 192);
var prAreaFont = ExcelColor.Black;
var prBorderWidth = XLBorderStyleValues.Thin;
var prFontSize = 10;
var prTitleFont = 20;
var prFootnoteSize = 8;
var prHeightSeperator = 7.5;
var prAreaObjectiveFontsize = 8;
var indentLength = 24;
var firstIndentLength = 20;
var innerIndentLength = 5;
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;
//DELETE THIS
//coeID = null;
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;
var wsDefName = "Def_" + coe.CoE_Abbr;
var wsPR = wb.Worksheets.Add(wsPRName);
var wsDef = wb.Worksheets.Add(wsDefName);
List<OfficeOpenXml.ExcelWorksheet> wsList = new List<OfficeOpenXml.ExcelWorksheet>();
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.PrinterSettings.TopMargin = 0;
ws.PrinterSettings.HeaderMargin = 0;
ws.PrinterSettings.BottomMargin = 0.5M;
ws.PrinterSettings.LeftMargin = 0;
ws.PrinterSettings.RightMargin = 0;
ws.PrinterSettings.Orientation = eOrientation.Landscape;
ws.PrinterSettings.PaperSize = ePaperSize.Legal;
ws.PrinterSettings.FitToHeight = 1;
ws.PrinterSettings.FitToWidth = 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.Cells[currentRow, 1];
prTitle.Value = coe.CoE;
prTitle.Style.Font.Size = prTitleFont;
prTitle.Style.Font.Bold = true;
prTitle.Style.Font.Color.SetColor(prHeader1Font);
prTitle.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prTitle.Style.Fill.BackgroundColor.SetColor(prHeader1Fill);
ws.Cells[currentRow, 1, currentRow, maxCol].Merge = true;
ws.Cells[currentRow + 1, 1, currentRow + 1, maxCol].Merge = true;
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.Cells[currentRow, currentCol].Value = cellValue;
ws.Cells[currentRow, currentCol, currentRow + 1, currentCol].Merge = true;
currentCol++;
}
else
{
var columnField = columnHeaders[i, 1];
var columnFieldTop = columnHeaders[i, 0];
ws.Cells[currentRow + 1, currentCol].Value = columnField;
ws.Cells[currentRow, currentCol].Value = columnFieldTop;
if (currentCol < prHeader2ColStart) { prHeader2ColStart = currentCol; }
if (currentCol > prHeader2ColEnd) { prHeader2ColEnd = currentCol; }
currentCol++;
}
}
currentCol--;
var prHeader1 = ws.Cells[currentRow, 1, currentRow + 1, currentCol];
if (prHeader2ColStart != 99)
{
ws.Cells[currentRow, prHeader2ColStart, currentRow, prHeader2ColEnd].Merge = true;
var prHeader2 = ws.Cells[currentRow + 1, prHeader2ColStart, currentRow + 1, prHeader2ColEnd];
prHeader2.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prHeader2.Style.Fill.BackgroundColor.SetColor(prHeader2Fill);
prHeader2.Style.Font.Color.SetColor(prHeader2Font);
}
prHeader1.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
prHeader1.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
prHeader1.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prHeader1.Style.Fill.BackgroundColor.SetColor(prHeader1Fill);
prHeader1.Style.Font.Color.SetColor(prHeader1Font);
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.Cells[currentRow, 1, currentRow, maxCol];
//fitAdjustableRows.Add(currentRow);
prArea.Merge = true;
prArea.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prArea.Style.Fill.BackgroundColor.SetColor(prAreaFill);
prArea.Style.Font.Color.SetColor(prAreaFont);
prArea.FirstOrDefault().RichText.Add(areaMap.Area.Area).Bold = true;
cellLengthObjective += areaMap.Area.Area.Length;
if (ws == wsPR)
{
var indent = new string('_', indentLength);
var innerIndent = new string('_', innerIndentLength);
var firstIndent = indent.Substring(0, firstIndentLength - areaMap.Area.Area.Length);
var stringSeperators = new string[] { "•" };
/*
if (areaMap.Objective != null)
{
var objectives = areaMap.Objective.Split(stringSeperators, StringSplitOptions.None);
for (var i = 1; i < objectives.Length; i++)
{
if (i == 1)
{
prArea.FirstOrDefault().RichText.Add(firstIndent).Size = prAreaObjectiveFontsize;
cellLengthObjective += firstIndent.Length;
}
//var innerIndentAdj = new string('_', maxObjectiveLength < objectives[i].Length ? 0 : maxObjectiveLength - objectives[i].Length);
var innerIndentAdj = "";
cellLengthObjective += objectives[i].Length + innerIndent.Length + innerIndentAdj.Length;
if (cellLengthObjective > prObjectivesCharsNewLine)
{
prArea.FirstOrDefault().RichText.Add("\r\n");
ws.Row(currentRow).Height += (int)newLineHeight;
prArea.FirstOrDefault().RichText.Add(indent).Color = ClosedXML.Excel.XLColor.FromColor(prAreaFill);
prArea.FirstOrDefault().RichText.Add(indent).SetFontColor( ClosedXML.Excel.XLColor.FromColor(prAreaFont)).SetFontSize(prAreaObjectiveFontsize);
cellLengthObjective = indent.Length;
}
prArea.FirstCell().RichText.AddText(innerIndent + innerIndentAdj).FontColor = ClosedXML.Excel.XLColor.FromColor(prAreaFill);
prArea.FirstCell().RichText.AddText(" •" + objectives[i]).FontSize = prAreaObjectiveFontsize;
cellLengthObjective += objectives[i].Length;
}
}*/
}
currentRow++;
foreach (var map in viewModel.allMaps.Where(x => x.Fiscal_Year == fiscalYear).Where(e => e.Indicator.Area.Equals(areaMap.Area)).Where(d => d.CoE.CoE.Contains(coe.CoE)).OrderBy(f => f.Number))
{
fitAdjustableRows.Add(currentRow);
currentCol = 1;
ws.Cells[currentRow, currentCol].Value = indicatorNumber;
indicatorNumber++;
ws.Cells[currentRow, currentCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
currentCol++;
int j = 0;
ws.Cells[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.Cells[currentRow, currentCol].RichText.Add(",").VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
}
ws.Cells[currentRow, currentCol].RichText.Add(footnote.Footnote.Footnote_Symbol).VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
j++;
}
ws.Cells[currentRow, currentCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
currentCol++;
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),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear) + "_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear) + "_Sup").GetValue(obj,null),
""
},
{(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),
},
{(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),
},
{(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),
},
{(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),
},
{(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),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target_Sup").GetValue(obj,null),
""
},
{(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),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator_Sup").GetValue(obj,null),
""
},
};
var startCol = currentCol;
int k = 1;
for (var i = 0; i <= columnIndicators.GetUpperBound(0); i++)
{
for (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.Cells[currentRow, startCol + i - 1, currentRow, startCol + i + k - 1].Merge = true;
i += k - 1;
k = 1;
}
else if (columnIndicators[i, 0] != "=")
{
var cell = ws.Cells[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.Add(columnIndicators[i, 1]).VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
}
switch (columnIndicators[i, 2])
{
case "cssWhite":
//cell.RichText.SetFontColor(XLColor.Black);
cell.Style.Font.Color.SetColor(ExcelColor.Black);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(ExcelColor.White);
break;
case "cssGreen":
cell.Style.Font.Color.SetColor(ExcelColor.White);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(prGreen);
break;
case "cssYellow":
cell.Style.Font.Color.SetColor(ExcelColor.Black);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(prYellow);
break;
case "cssRed":
cell.Style.Font.Color.SetColor(ExcelColor.White);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(prRed);
break;
}
cell.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.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;
var listColumnStrings = new List<string>();
listColumnStrings.Add(defn);
listColumnStrings.Add(rationale);
listColumnStrings.Add(comp);
foreach (var str in listColumnStrings)
{
if (str != null)
{
lines = str.Length / ws.Column(currentCol).Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cells[currentRow, currentCol].Value = str;
}
ws.Cells[currentRow, currentCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
currentCol++;
}
ws.Row(currentRow).Height = newLineHeight*Math.Ceiling(maxLines);
currentRow++;
}
}
}
var footnoteRow = ws.Cells[currentRow, 1, currentRow, maxCol];
footnoteRow.Merge = true;
footnoteRow.Style.Font.Size = 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)
{
ws.Cells[currentRow, 1].RichText.Add(footnote.Footnote_Symbol).VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
ws.Cells[currentRow, 1].RichText.Add(" " + footnote.Footnote + ";");
ws.Cells[currentRow, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
cellLengthFootnote += footnote.Footnote_Symbol.ToString().Length + footnote.Footnote.ToString().Length + 2;
if (cellLengthFootnote > prFootnoteCharsNewLine)
{
ws.Cells[currentRow, 1].RichText.Add("\r\n");;
cellLengthFootnote = 0;
ws.Row(currentRow).Height += newLineHeight;
}
}
}
else
{
ws.Cells[currentRow, 1].Value = defNote;
ws.Row(currentRow).Height = 28;
}
var pr = ws.Cells[startRow, 1, currentRow - 1, maxCol];
//pr.Style.Border.InsideBorder = prBorderWidth;
//pr.Style.Border.InsideBorderColor = prBorder;
//pr.Style.Border.OutsideBorder = prBorderWidth;
//pr.Style.Border.OutsideBorderColor = prBorder;
pr.Style.Font.Size = prFontSize;
pr = ws.Cells[startRow, 1, currentRow, maxCol];
pr.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
pr.Style.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);
}
}*/
}
}
FileStream fs = new FileStream(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test1.xlsx"), FileMode.Create);
ef.SaveAs(fs);
fs.Close();
//var test = GemBox.Spreadsheet.ExcelFile.Load(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test.xlsx"), LoadOptions.XlsxDefault);
var test = new Spire.Xls.Workbook();
test.LoadFromFile(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test1.xlsx"));
// Set PDF template
Spire.Pdf.PdfDocument pdfDocument = new Spire.Pdf.PdfDocument();
pdfDocument.PageSettings.Orientation = Spire.Pdf.PdfPageOrientation.Landscape;
pdfDocument.PageSettings.Width = 970;
pdfDocument.PageSettings.Height = 850;
//Convert Excel to PDF using the template above
Spire.Xls.Converter.PdfConverter pdfConverter = new Spire.Xls.Converter.PdfConverter(test);
Spire.Xls.Converter.PdfConverterSettings settings = new Spire.Xls.Converter.PdfConverterSettings();
settings.TemplateDocument = pdfDocument;
pdfDocument = pdfConverter.Convert(settings);
// Save and preview PDF
pdfDocument.SaveToFile(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test1.pdf"));
//wb.SaveAs(preImage);
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);
}
