internal SLPicture Clone()
{
SLPicture pic = new SLPicture();
pic.DataIsInFile = this.DataIsInFile;
pic.PictureFileName = this.PictureFileName;
pic.PictureByteData = new byte[this.PictureByteData.Length];
for (int i = 0; i < this.PictureByteData.Length; ++i)
{
pic.PictureByteData[i] = this.PictureByteData[i];
}
pic.PictureImagePartType = this.PictureImagePartType;
pic.TopPosition = this.TopPosition;
pic.LeftPosition = this.LeftPosition;
pic.UseEasyPositioning = this.UseEasyPositioning;
pic.UseRelativePositioning = this.UseRelativePositioning;
pic.AnchorRowIndex = this.AnchorRowIndex;
pic.AnchorColumnIndex = this.AnchorColumnIndex;
pic.OffsetX = this.OffsetX;
pic.OffsetY = this.OffsetY;
pic.WidthInEMU = this.WidthInEMU;
pic.HeightInEMU = this.HeightInEMU;
pic.WidthInPixels = this.WidthInPixels;
pic.HeightInPixels = this.HeightInPixels;
pic.fHorizontalResolution = this.fHorizontalResolution;
pic.fVerticalResolution = this.fVerticalResolution;
pic.fTargetHorizontalResolution = this.fTargetHorizontalResolution;
pic.fTargetVerticalResolution = this.fTargetVerticalResolution;
pic.fCurrentHorizontalResolution = this.fCurrentHorizontalResolution;
pic.fCurrentVerticalResolution = this.fCurrentVerticalResolution;
pic.fHorizontalResolutionRatio = this.fHorizontalResolutionRatio;
pic.fVerticalResolutionRatio = this.fVerticalResolutionRatio;
pic.sAlternativeText = this.sAlternativeText;
pic.bLockWithSheet = this.bLockWithSheet;
pic.bPrintWithSheet = this.bPrintWithSheet;
pic.vCompressionState = this.vCompressionState;
pic.decBrightness = this.decBrightness;
pic.decContrast = this.decContrast;
pic.ShapeProperties = this.ShapeProperties.Clone();
pic.HasUri = this.HasUri;
pic.HyperlinkUri = this.HyperlinkUri;
pic.HyperlinkUriKind = this.HyperlinkUriKind;
pic.IsHyperlinkExternal = this.IsHyperlinkExternal;
return pic;
}
public ActionResult viewPRExcel()
{
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 = XLColor.FromArgb(0, 51, 102);
var prGreen = XLColor.FromArgb(0,118,53);
var prYellow = XLColor.FromArgb(255,192,0);
var prRed = XLColor.FromArgb(255,0,0);
var prHeader1Fill = prBlue;
var prHeader1Font = XLColor.White;
var prHeader2Fill = XLColor.White;
var prHeader2Font = XLColor.Black;
var prBorder = XLColor.FromArgb(0, 0, 0);
var prAreaFill = XLColor.FromArgb(192, 192, 192);
var prAreaFont = XLColor.Black;
var prBorderWidth = XLBorderStyleValues.Thin;
var prFontSize = 10;
var prTitleFont = 20;
var prFootnoteSize = 8;
var prHeighSeperator = 7.5;
var prNumberWidth = 4;
var prIndicatorWidth = 50;
var prValueWidth = 10;
var prDefWidth = 100;
var prFootnoteCharsNewLine = 125;
var wsPRName = "PR";
var wsDefName = "Definitions";
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 = 1;
int startRow;
int indicatorNumber = 1;
foreach (var coe in viewModel.allCoEs)
{
string[,] columnHeaders = new string[0,0];
if (ws.Name == wsPRName)
{
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{"FY_10_11",""},
{"FY_11_12",""},
{"FY_12_13",""},
{"FY 13 14 Performance","FY_13_14_Q1"},
{"FY 13 14 Performance","FY_13_14_Q2"},
{"FY 13 14 Performance","FY_13_14_Q3"},
{"FY 13 14 Performance","FY_13_14_Q4"},
{"FY 13 14 Performance","FY_13_14_YTD"},
{"Target",""},
{"Performance_Threshold",""},
{"Comparator",""}
};
}
else if (ws.Name == wsDefName)
{
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{"Definition_Calculation",""},
{"Target_Rationale",""},
{"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 = prHeighSeperator;
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 = ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicators), columnField).DisplayName;
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.OrderBy(x=> x.Area.Sort))
{
var prArea = ws.Range(ws.Cell(currentRow, 1), ws.Cell(currentRow, maxCol));
prArea.Merge();
prArea.Style.Fill.BackgroundColor = prAreaFill;
prArea.Style.Font.FontColor = prAreaFont;
prArea.Value = areaMap.Area.Area;
currentRow++;
foreach (var map in viewModel.allMaps.Where(e => e.Indicator.Area.Equals(areaMap.Area)).Where(d => d.CoE.CoE.Contains(coe.CoE)).OrderBy(f => f.Number))
{
currentCol = 1;
ws.Cell(currentRow, currentCol).Value = indicatorNumber;
indicatorNumber++;
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
currentCol++;
int j = 0;
ws.Cell(currentRow, currentCol).Value = map.Indicator.Indicator;
foreach (var footnote in map.Indicator.Indicator_Footnote_Map.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_ID).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
j++;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
if (ws.Name == wsPRName)
{
string[,] columnIndicators = new string[,]{
{map.Indicator.FY_10_11, map.Indicator.FY_10_11_Sup,""},
{map.Indicator.FY_11_12, map.Indicator.FY_11_12_Sup,""},
{map.Indicator.FY_12_13, map.Indicator.FY_12_13_Sup,""},
{map.Indicator.FY_13_14_Q1, map.Indicator.FY_13_14_Q1_Sup,map.Indicator.Q1_Color,},
{map.Indicator.FY_13_14_Q2, map.Indicator.FY_13_14_Q2_Sup,map.Indicator.Q2_Color,},
{map.Indicator.FY_13_14_Q3, map.Indicator.FY_13_14_Q3_Sup,map.Indicator.Q3_Color,},
{map.Indicator.FY_13_14_Q4, map.Indicator.FY_13_14_Q4_Sup,map.Indicator.Q4_Color,},
{map.Indicator.FY_13_14_YTD, map.Indicator.FY_13_14_YTD_Sup,map.Indicator.YTD_Color,},
{map.Indicator.Target, map.Indicator.Target_Sup,""},
{map.Indicator.Performance_Threshold, map.Indicator.Performance_Threshold_Sup,""},
{map.Indicator.Comparator, map.Indicator.Comparator_Sup,""},
};
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.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] != "=")
{
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.Cell(currentRow, currentCol).Value = map.Indicator.Definition_Calculation;
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
ws.Cell(currentRow, currentCol).Value = map.Indicator.Target_Rationale;
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
ws.Cell(currentRow, currentCol).Value = map.Indicator.Comparator_Source;
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
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 cellLength = 0;
foreach (var footnote in footnotes.OrderBy(x => x.Footnote_Symbol))
{
ws.Cell(currentRow, 1).RichText.AddText(footnote.Footnote_Symbol).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
ws.Cell(currentRow, 1).RichText.AddText(" " + footnote.Footnote + ";");
cellLength += footnote.Footnote_Symbol.ToString().Length + footnote.Footnote.ToString().Length + 2;
if (cellLength > prFootnoteCharsNewLine)
{
ws.Cell(currentRow, 1).RichText.AddNewLine();
cellLength = 0;
ws.Row(currentRow).Height += 15;
}
}
var pr = ws.Range(ws.Cell(startRow, 1), ws.Cell(currentRow, maxCol));
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.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
pr.Style.Alignment.WrapText = true;
ws.Column(1).Width = prNumberWidth;
ws.Column(2).Width = prIndicatorWidth;
for (var i = 3; i <= 15; i++)
{
ws.Column(i).Width = ws.Name == wsPRName ? prValueWidth : prDefWidth;
}
currentRow += 2;
footnotes.Clear();
indicatorNumber = 1;
}
}
MemoryStream preImage = new MemoryStream();
wb.SaveAs(preImage);
MemoryStream postImage = new MemoryStream();
SLDocument postImageWb = new SLDocument(preImage, wsPRName);
string imagePath = this.HttpContext.ApplicationInstance.Server.MapPath("~/Images/logo.png");
SLPicture picLogo = new SLPicture(imagePath);
picLogo.SetPosition(0, 0);
postImageWb.InsertPicture(picLogo);
// 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\"");
// 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);
}
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);
}
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);
}
protected string GenerateServiceReport(int? orderID)
{
int signatureMaxWidth = 200, signatureMaxHeight = 180;
double excelBorderWidth = 0.01;
string returnFilePath = null;
IOrderService orderService = AutoSessionServiceFactory.GetOrderService(ApplicationSetting.Current.DefaultConnectionString);
string templateFilePath = System.Web.Hosting.HostingEnvironment.MapPath("~/ReportTemplate/REC_CRM_Service_Report_Template.xlsx");
try
{
SLDocument template = new SLDocument(templateFilePath, "Sheet1");
ServiceReportOrderDTO order = orderService.GetServiceReportOrderByID(orderID.Value);
string completionDate = order.dCompletionDate.HasValue ? order.dCompletionDate.Value.ToString("yyyy/MM/dd") : string.Empty;
string customerName = string.IsNullOrEmpty(order.sCustomerName) ? string.Empty : order.sCustomerName;
string customerCode = string.IsNullOrEmpty(order.sCustomerCode) ? string.Empty : order.sCustomerCode;
string workingAddress = string.IsNullOrEmpty(order.sAddress) ? string.Empty : order.sAddress;
string contactPerson = string.IsNullOrEmpty(order.sContactPersonName) ? string.Empty : order.sContactPersonName;
string model = string.IsNullOrEmpty(order.sModel) ? string.Empty : order.sModel;
string fault = string.IsNullOrEmpty(order.sFault) ? string.Empty : order.sFault;
string remark = string.IsNullOrEmpty(order.sRemark) ? string.Empty : order.sRemark;
string repair = string.IsNullOrEmpty(order.sRepair) ? string.Empty : order.sRepair;
string report = string.IsNullOrEmpty(order.sReport) ? string.Empty : order.sReport;
string reviewerName = string.IsNullOrEmpty(order.sReviewerName) ? string.Empty : order.sReviewerName;
string technicians = string.IsNullOrEmpty(order.sTechnicianNameString) ? string.Empty : order.sTechnicianNameString;
string startTime = order.tStartTime.HasValue ? order.tStartTime.Value.ToString(@"hh\:mm") : string.Empty;
string endTime = order.tEndTime.HasValue ? order.tEndTime.Value.ToString(@"hh\:mm") : string.Empty;
string signDate = order.dReportSignDate.HasValue ? order.dReportSignDate.Value.ToString("yyyy/MM/dd") : string.Empty;
string customerOpinion = string.IsNullOrEmpty(order.sCustomerOpinion) ? string.Empty : order.sCustomerOpinion;
string receipt = string.IsNullOrEmpty(order.sReceipt) ? string.Empty : order.sReceipt;
#region Fill Data
List<SLDefinedName> definedNames = template.GetDefinedNames();
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "RepairDate"), completionDate);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerName"), customerName);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerCode"), customerCode);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "WorkingAddress"), workingAddress);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "ContactPerson"), contactPerson);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Model"), model);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Fault"), fault);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Remark"), remark);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Repair"), repair);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Report"), report);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Technicians"), technicians);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "StartTime"), startTime);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "EndTime"), endTime);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "ReviewerName"), reviewerName);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "SignDate"), signDate);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerOpinion"), customerOpinion);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Receipt"), receipt);
#region Insert Signature
string signaturePath = ApplicationSetting.Current.RootFolderPath + order.sReportSignPath;
string signatureCellReference = this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerSignature");
int signatureColumnIndex = SLConvert.ToColumnIndex(signatureCellReference);
int signatureRowIndex = int.Parse(signatureCellReference.Substring(1));
try
{
//Get Signature Dimension
System.Drawing.Bitmap bm = new System.Drawing.Bitmap(signaturePath);
double pictureHeight = bm.Height;
double pictureWidth = bm.Width;
bm.Dispose();
int scale = 0;
if (pictureHeight / pictureWidth > ((double)signatureMaxHeight) / ((double)signatureMaxWidth))
{
scale = (int)(((double)signatureMaxHeight / pictureHeight) * 100.0);
}
else
{
scale = (int)(((double)signatureMaxWidth / pictureWidth) * 100.0);
}
SLPicture pic = new SLPicture(signaturePath);
pic.SetPosition(signatureRowIndex - 1 + excelBorderWidth, signatureColumnIndex - 1 + excelBorderWidth);
pic.ResizeInPercentage(scale, scale);
template.InsertPicture(pic);
}
catch (Exception ex)
{
//File path not correct or file not exist
}
#endregion Insert Signature
#endregion Fill Data
#region Auto Fit Row
for (int i = 9; i <= 13; i++)
{
template.AutoFitRow(i);
}
#endregion Auto Fit Row
returnFilePath = string.Format(@"{0}\ServiceReport_{1}_{2}.xlsx", _serviceReportTempFolderPath, orderID, DateTime.Now.ToString("yyyyMMddHHmmss"));
template.SaveAs(returnFilePath);
}
catch (Exception e)
{
LogManager.Write(e, "RecWomCrm", "System");
returnFilePath = null;
}
return returnFilePath;
}
internal SLPicture Clone()
{
SLPicture pic = new SLPicture();
pic.DataIsInFile = this.DataIsInFile;
pic.PictureFileName = this.PictureFileName;
pic.PictureByteData = new byte[this.PictureByteData.Length];
for (int i = 0; i < this.PictureByteData.Length; ++i)
{
pic.PictureByteData[i] = this.PictureByteData[i];
}
pic.PictureImagePartType = this.PictureImagePartType;
pic.TopPosition = this.TopPosition;
pic.LeftPosition = this.LeftPosition;
pic.UseEasyPositioning = this.UseEasyPositioning;
pic.UseRelativePositioning = this.UseRelativePositioning;
pic.AnchorRowIndex = this.AnchorRowIndex;
pic.AnchorColumnIndex = this.AnchorColumnIndex;
pic.OffsetX = this.OffsetX;
pic.OffsetY = this.OffsetY;
pic.WidthInEMU = this.WidthInEMU;
pic.HeightInEMU = this.HeightInEMU;
pic.WidthInPixels = this.WidthInPixels;
pic.HeightInPixels = this.HeightInPixels;
pic.fHorizontalResolution = this.fHorizontalResolution;
pic.fVerticalResolution = this.fVerticalResolution;
pic.fTargetHorizontalResolution = this.fTargetHorizontalResolution;
pic.fTargetVerticalResolution = this.fTargetVerticalResolution;
pic.fCurrentHorizontalResolution = this.fCurrentHorizontalResolution;
pic.fCurrentVerticalResolution = this.fCurrentVerticalResolution;
pic.fHorizontalResolutionRatio = this.fHorizontalResolutionRatio;
pic.fVerticalResolutionRatio = this.fVerticalResolutionRatio;
pic.sAlternativeText = this.sAlternativeText;
pic.bLockWithSheet = this.bLockWithSheet;
pic.bPrintWithSheet = this.bPrintWithSheet;
pic.vCompressionState = this.vCompressionState;
pic.decBrightness = this.decBrightness;
pic.decContrast = this.decContrast;
pic.vPictureShape = this.vPictureShape;
pic.FillType = this.FillType;
pic.FillClassInnerXml = this.FillClassInnerXml;
pic.HasOutline = this.HasOutline;
pic.PictureOutline = this.PictureOutline;
pic.HasOutlineFill = this.HasOutlineFill;
pic.PictureOutlineFill = this.PictureOutlineFill;
pic.HasGlow = this.HasGlow;
pic.GlowRadius = this.GlowRadius;
pic.GlowColorInnerXml = this.GlowColorInnerXml;
pic.HasInnerShadow = this.HasInnerShadow;
pic.PictureInnerShadow = this.PictureInnerShadow;
pic.HasOuterShadow = this.HasOuterShadow;
pic.PictureOuterShadow = this.PictureOuterShadow;
pic.HasReflection = this.HasReflection;
pic.ReflectionBlurRadius = this.ReflectionBlurRadius;
pic.ReflectionStartOpacity = this.ReflectionStartOpacity;
pic.ReflectionStartPosition = this.ReflectionStartPosition;
pic.ReflectionEndAlpha = this.ReflectionEndAlpha;
pic.ReflectionEndPosition = this.ReflectionEndPosition;
pic.ReflectionDistance = this.ReflectionDistance;
pic.ReflectionDirection = this.ReflectionDirection;
pic.ReflectionFadeDirection = this.ReflectionFadeDirection;
pic.ReflectionHorizontalRatio = this.ReflectionHorizontalRatio;
pic.ReflectionVerticalRatio = this.ReflectionVerticalRatio;
pic.ReflectionHorizontalSkew = this.ReflectionHorizontalSkew;
pic.ReflectionVerticalSkew = this.ReflectionVerticalSkew;
pic.ReflectionAlignment = this.ReflectionAlignment;
pic.ReflectionRotateWithShape = this.ReflectionRotateWithShape;
pic.HasSoftEdge = this.HasSoftEdge;
pic.SoftEdgeRadius = this.SoftEdgeRadius;
pic.HasScene3D = this.HasScene3D;
pic.CameraLatitude = this.CameraLatitude;
pic.CameraLongitude = this.CameraLongitude;
pic.CameraRevolution = this.CameraRevolution;
pic.CameraPreset = this.CameraPreset;
pic.CameraFieldOfView = this.CameraFieldOfView;
pic.CameraZoom = this.CameraZoom;
pic.LightRigLatitude = this.LightRigLatitude;
pic.LightRigLongitude = this.LightRigLongitude;
pic.LightRigRevolution = this.LightRigRevolution;
pic.LightRigType = this.LightRigType;
pic.LightRigDirection = this.LightRigDirection;
pic.HasBevelTop = this.HasBevelTop;
pic.BevelTopPreset = this.BevelTopPreset;
pic.BevelTopWidth = this.BevelTopWidth;
pic.BevelTopHeight = this.BevelTopHeight;
pic.HasBevelBottom = this.HasBevelBottom;
pic.BevelBottomPreset = this.BevelBottomPreset;
pic.BevelBottomWidth = this.BevelBottomWidth;
pic.BevelBottomHeight = this.BevelBottomHeight;
pic.HasExtrusion = this.HasExtrusion;
pic.ExtrusionHeight = this.ExtrusionHeight;
pic.ExtrusionColorInnerXml = this.ExtrusionColorInnerXml;
pic.HasContour = this.HasContour;
pic.ContourWidth = this.ContourWidth;
pic.ContourColorInnerXml = this.ContourColorInnerXml;
pic.HasMaterialType = this.HasMaterialType;
pic.MaterialType = this.MaterialType;
pic.HasZDistance = this.HasZDistance;
pic.ZDistance = this.ZDistance;
pic.HasUri = this.HasUri;
pic.HyperlinkUri = this.HyperlinkUri;
pic.HyperlinkUriKind = this.HyperlinkUriKind;
pic.IsHyperlinkExternal = this.IsHyperlinkExternal;
return pic;
}
protected string GenerateServiceChecklist(int? orderID)
{
#region Style Definition
SLStyle companyAddressStyle = new SLStyle();
companyAddressStyle.Font.FontName = "Arial";
companyAddressStyle.Font.FontSize = 10;
companyAddressStyle.SetWrapText(true);
companyAddressStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
companyAddressStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
SLStyle titleStyle = new SLStyle();
titleStyle.Font.FontName = "Arial";
titleStyle.Font.FontSize = 16;
titleStyle.Font.Bold = true;
titleStyle.SetWrapText(true);
titleStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
titleStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
SLStyle jobInformationLabelStyle = new SLStyle();
jobInformationLabelStyle.Font.FontName = "Arial";
jobInformationLabelStyle.Font.FontSize = 12;
jobInformationLabelStyle.SetWrapText(true);
jobInformationLabelStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
jobInformationLabelStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
SLStyle jobInformationContentStyle = new SLStyle();
jobInformationContentStyle.Font.FontName = "Arial";
jobInformationContentStyle.Font.FontSize = 12;
jobInformationContentStyle.SetWrapText(true);
jobInformationContentStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
jobInformationContentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
SLStyle itemIndexStyle = new SLStyle();
itemIndexStyle.Font.FontName = "Arial";
itemIndexStyle.Font.FontSize = 11;
itemIndexStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Top);
itemIndexStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
itemIndexStyle.Border.BottomBorder.BorderStyle = itemIndexStyle.Border.TopBorder.BorderStyle = itemIndexStyle.Border.LeftBorder.BorderStyle = itemIndexStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin;
itemIndexStyle.Border.BottomBorder.Color = itemIndexStyle.Border.TopBorder.Color = itemIndexStyle.Border.LeftBorder.Color = itemIndexStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
SLStyle itemRowHeaderStyle = new SLStyle();
itemRowHeaderStyle.Font.FontName = "Arial";
itemRowHeaderStyle.Font.FontSize = 11;
itemRowHeaderStyle.SetWrapText(true);
itemRowHeaderStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Top);
itemRowHeaderStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
itemRowHeaderStyle.Border.BottomBorder.BorderStyle = itemRowHeaderStyle.Border.TopBorder.BorderStyle = itemRowHeaderStyle.Border.LeftBorder.BorderStyle = itemRowHeaderStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin;
itemRowHeaderStyle.Border.BottomBorder.Color = itemRowHeaderStyle.Border.TopBorder.Color = itemRowHeaderStyle.Border.LeftBorder.Color = itemRowHeaderStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
SLStyle itemContentStyle = new SLStyle();
itemContentStyle.Font.FontName = "Arial";
itemContentStyle.Font.FontSize = 11;
itemContentStyle.SetWrapText(true);
itemContentStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
itemContentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
itemContentStyle.Border.BottomBorder.BorderStyle = itemContentStyle.Border.TopBorder.BorderStyle = itemContentStyle.Border.LeftBorder.BorderStyle = itemContentStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin;
itemContentStyle.Border.BottomBorder.Color = itemContentStyle.Border.TopBorder.Color = itemContentStyle.Border.LeftBorder.Color = itemContentStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
SLStyle topBorderStyle = new SLStyle();
topBorderStyle.Border.SetTopBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle bottomBorderStyle = new SLStyle();
bottomBorderStyle.Border.SetBottomBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle leftBorderStyle = new SLStyle();
leftBorderStyle.Border.SetLeftBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle rightBorderStyle = new SLStyle();
rightBorderStyle.Border.SetRightBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle footerLabelStyle = new SLStyle();
footerLabelStyle.Font.FontName = "Arial";
footerLabelStyle.Font.FontSize = 11;
footerLabelStyle.Alignment.WrapText = true;
footerLabelStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Top);
SLStyle remarkStyle = new SLStyle();
remarkStyle.Font.FontName = "Arial";
remarkStyle.Font.FontSize = 11;
remarkStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
remarkStyle.Border.BottomBorder.BorderStyle = remarkStyle.Border.TopBorder.BorderStyle = remarkStyle.Border.LeftBorder.BorderStyle = remarkStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin;
remarkStyle.Border.BottomBorder.Color = remarkStyle.Border.TopBorder.Color = remarkStyle.Border.LeftBorder.Color = remarkStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
SLStyle legendLabelStyle = new SLStyle();
legendLabelStyle.Font.FontName = "Arial";
legendLabelStyle.Font.FontSize = 11;
legendLabelStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
SLStyle leftAlignmentStyle = new SLStyle();
leftAlignmentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
SLStyle rightAlignmentStyle = new SLStyle();
rightAlignmentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
SLStyle centerAlignmentStyle = new SLStyle();
centerAlignmentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
SLStyle wrapTextStyle = new SLStyle();
wrapTextStyle.Alignment.WrapText = true;
#endregion Style Definition
int signatureMaxWidth = 160, signatureMaxHeight = 140;
string returnFilePath = null;
string logoPath = System.Web.Hosting.HostingEnvironment.MapPath("~/CSS/Layout/Images/logo.png");
double _borderWidth = 0.01;
int _valueStartRow = 13;
int _valueStartColumn = 4;
int _valueEndColumn = 7;
int valueEndRow = 13;
int currentSheetIndex = 1;
Dictionary<Guid, int> subItemRowIndex = new Dictionary<Guid, int>();
IJobFormService jobFormService = AutoSessionServiceFactory.GetJobFormService(ApplicationSetting.Current.DefaultConnectionString);
IFormItemService formItemService = AutoSessionServiceFactory.GetFormItemService(ApplicationSetting.Current.DefaultConnectionString);
SLDocument document = new SLDocument();
document.RenameWorksheet("Sheet" + currentSheetIndex, "Checklist" + currentSheetIndex);
document.SelectWorksheet("Checklist" + currentSheetIndex);
try
{
#region Get Data
ServiceChecklistDTO checklist = jobFormService.GetServiceChecklist(orderID.Value);
IEnumerable<ServiceChecklistFormItemDTO> formItems = formItemService.GetServiceChecklistFormItems();
#endregion Get Data
#region Validation
if (checklist == null || checklist.valueGroups == null || checklist.valueGroups.Count() == 0) return "No-Record";
#endregion Validation
#region Title Section
document.MergeWorksheetCells(2, 1, 4, 4);
lock (logoSyncLock)
{
SLPicture logo = new SLPicture(logoPath);
logo.SetPosition(1 + _borderWidth, _borderWidth);
document.InsertPicture(logo);
}
for (int i = 2; i <= 4; i++)
{
document.MergeWorksheetCells(i, 5, i, 7);
}
document.SetCellValue(2, 5, "Units A-D, 15/F, Goodman Kwai Chung Logistics Centre, 585-609 Castle Peak Rood, Kwai Chung, N.T., Hong Kong");
document.SetCellValue(3, 5, "香港新界葵涌青山道585-609號嘉民葵涌物流中心15樓A-D室");
document.SetCellValue(4, 5, "電話 Tel: (852) 2494 9455 傳真 Fax: (852) 2481 2051");
document.SetCellStyle(2, 5, 4, 8, companyAddressStyle);
document.MergeWorksheetCells(6, 1, 6, 7);
document.SetCellValue(6, 1, "Routine Check / Service Checklist" + Environment.NewLine + "例行檢查 / 記錄表");
document.SetCellStyle(6, 1, titleStyle);
#endregion Title Section
#region Job Information
string customerCode = string.IsNullOrEmpty(checklist.sCustomerCode) ? string.Empty : checklist.sCustomerCode;
string customerName = checklist.nLanguage == 1 ? string.IsNullOrEmpty(checklist.sCustomerChiName) ? string.Empty : checklist.sCustomerChiName : string.IsNullOrEmpty(checklist.sCustomerName) ? string.Empty : checklist.sCustomerName;
string completionDate = checklist.dCompletionDate.HasValue ? checklist.dCompletionDate.Value.ToString("dd-MM-yyyy") : string.Empty;
string equipmentType = string.IsNullOrEmpty(checklist.sEquipmentType) ? string.Empty : checklist.sEquipmentType;
string equipmentTypeRemark = string.IsNullOrEmpty(checklist.sEquipmentTypeRemark) ? string.Empty : checklist.sEquipmentTypeRemark;
string location = string.IsNullOrEmpty(checklist.sWorkingAddress) ? string.Empty : checklist.sWorkingAddress;
document.MergeWorksheetCells(7, 1, 8, 2);
document.SetCellValue(7, 1, "Name of Customer" + Environment.NewLine + "客戶名稱 :");
document.SetCellStyle(7, 1, jobInformationLabelStyle);
document.MergeWorksheetCells(7, 3, 8, 3);
document.SetCellValue(7, 3, customerName);
document.SetCellStyle(7, 3, jobInformationContentStyle);
document.MergeWorksheetCells(7, 4, 7, 5);
document.SetCellValue(7, 4, "Job / ACM No. :");
document.SetCellStyle(7, 4, jobInformationLabelStyle);
document.MergeWorksheetCells(7, 6, 7, 7);
document.SetCellValue(7, 6, customerCode);
document.SetCellStyle(7, 6, jobInformationContentStyle);
document.MergeWorksheetCells(8, 4, 8, 5);
document.SetCellValue(8, 4, "Work Date 工作日期 :");
document.SetCellStyle(8, 4, jobInformationLabelStyle);
document.MergeWorksheetCells(8, 6, 8, 7);
document.SetCellValue(8, 6, completionDate);
document.SetCellStyle(8, 6, jobInformationContentStyle);
document.MergeWorksheetCells(9, 1, 9, 2);
document.SetCellValue(9, 1, "Location" + Environment.NewLine + "工作地址 :");
document.SetCellStyle(9, 1, jobInformationLabelStyle);
document.MergeWorksheetCells(9, 3, 9, 7);
document.SetCellValue(9, 3, location);
document.SetCellStyle(9, 3, jobInformationContentStyle);
document.MergeWorksheetCells(10, 1, 10, 2);
document.SetCellValue(10, 1, "Equipment Type" + Environment.NewLine + "設備類別 :");
document.SetCellStyle(10, 1, jobInformationLabelStyle);
document.SetCellValue(10, 3, equipmentType);
document.SetCellStyle(10, 3, jobInformationContentStyle);
document.MergeWorksheetCells(10, 4, 10, 5);
document.SetCellValue(10, 4, "Equipment Type Remark" + Environment.NewLine + "設備類別備註 :");
document.SetCellStyle(10, 4, jobInformationLabelStyle);
document.MergeWorksheetCells(10, 6, 10, 7);
document.SetCellValue(10, 6, equipmentTypeRemark);
document.SetCellStyle(10, 6, jobInformationContentStyle);
#endregion Job Information
#region Checklist Header
document.SetCellValue(12, 2, "Check List");
document.SetCellValue(12, 3, "檢查內容");
document.MergeWorksheetCells(12, _valueStartColumn, 12, _valueEndColumn);
int currentRow = _valueStartRow;
int currentFormItemStartRow = _valueStartRow;
foreach (ServiceChecklistFormItemDTO formItem in formItems)
{
string formItemPrefix = string.IsNullOrEmpty(formItem.sPrefix) ? string.Empty : formItem.sPrefix;
string formItemName = string.IsNullOrEmpty(formItem.sName) ? string.Empty : formItem.sName;
string formItemEngName = string.IsNullOrEmpty(formItem.sEngName) ? string.Empty : formItem.sEngName;
document.SetCellValue(currentRow, 1, formItemPrefix);
document.SetCellValue(currentRow, 2, formItemEngName);
document.SetCellValue(currentRow, 3, formItemName);
if (formItem.subItems != null && formItem.subItems.Count() > 0)
{
if (formItem.subItems.Count() == 1)
{
subItemRowIndex.Add(formItem.subItems.ElementAt(0).gID, currentRow);
}
else
{
foreach (ServiceChecklistFormSubItemDTO subItem in formItem.subItems)
{
currentRow++;
string subItemPrefix = string.IsNullOrEmpty(subItem.sPrefix) ? string.Empty : subItem.sPrefix;
string subItemName = string.IsNullOrEmpty(subItem.sName) ? string.Empty : subItem.sName;
string subItemEngName = string.IsNullOrEmpty(subItem.sEngName) ? string.Empty : subItem.sEngName;
string subItemSuffix = string.IsNullOrEmpty(subItem.sSuffix) ? string.Empty : subItem.sSuffix;
document.SetCellValue(currentRow, 2, string.Format("{0}{1}{2}", string.IsNullOrEmpty(subItemPrefix) ? string.Empty : subItemPrefix + " ", subItemEngName, string.IsNullOrEmpty(subItemSuffix) ? string.Empty : " (" + subItemSuffix + ")"));
document.SetCellValue(currentRow, 3, subItem.sName);
subItemRowIndex.Add(subItem.gID, currentRow);
}
}
}
if (currentRow != currentFormItemStartRow)
{
document.MergeWorksheetCells(currentFormItemStartRow, 1, currentRow, 1);
}
currentFormItemStartRow = currentRow + 1;
currentRow++;
}
valueEndRow = currentRow - 1;
document.SetCellStyle(_valueStartRow - 1, 1, valueEndRow, 1, itemIndexStyle);
document.SetCellStyle(_valueStartRow - 1, 2, valueEndRow, 3, itemRowHeaderStyle);
document.SetCellStyle(_valueStartRow - 1, _valueStartColumn, valueEndRow, _valueEndColumn, itemContentStyle);
#endregion Checklist Header
#region Footer Section
#region Checklist Remark
int remarkRow = currentRow;
string checklistRemark = string.IsNullOrEmpty(checklist.sRemark) ? string.Empty : checklist.sRemark;
document.MergeWorksheetCells(currentRow, 1, currentRow, 2);
document.MergeWorksheetCells(currentRow, 3, currentRow, 7);
document.SetCellValue(currentRow, 1, "Remarks 備註");
document.SetCellValue(currentRow, 3, checklistRemark);
#region Set Style
SLStyle currentStyle = remarkStyle;
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
document.SetCellStyle(currentRow, 1, currentRow, 2, currentStyle);
currentStyle = remarkStyle;
currentStyle.SetWrapText(true);
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
document.SetCellStyle(currentRow, 3, currentRow, 7, currentStyle);
#endregion Set Style
#endregion Checklist Remark
currentRow++;
#region Legend
int legendRow = currentRow;
document.MergeWorksheetCells(currentRow, 1, currentRow, 2);
document.MergeWorksheetCells(currentRow, 3, currentRow, 7);
document.SetCellValue(currentRow, 1, "Legend 說明");
document.SetCellValue(currentRow, 3, "OK : OK 良好 R : Require Repair 需維修 L : Replacement 更換 N : Not Applicable 不適用");
#region Set Style
currentStyle = legendLabelStyle;
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
document.SetCellStyle(currentRow, 1, currentRow, 2, currentStyle);
currentStyle = legendLabelStyle;
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
document.SetCellStyle(currentRow, 3, currentRow, 7, currentStyle);
#endregion Set Style
#endregion Legend
currentRow++;
int footerBoxStartRow = currentRow;
string technicians = string.IsNullOrEmpty(checklist.sTechnicianNameString) ? string.Empty : checklist.sTechnicianNameString;
string startTime = checklist.tActualStartTime.HasValue ? checklist.tActualStartTime.Value.ToString(@"hh\:mm") : string.Empty;
string endTime = checklist.tActualEndTime.HasValue ? checklist.tActualEndTime.Value.ToString(@"hh\:mm") : string.Empty;
string customerOpinion = string.IsNullOrEmpty(checklist.sCustomerOpinion) ? string.Empty : checklist.sCustomerOpinion;
string signDate = checklist.dSignDate.HasValue ? checklist.dSignDate.Value.ToString("dd-MM-yyyy") : string.Empty;
document.MergeWorksheetCells(currentRow, 1, currentRow, 2);
document.SetCellValue(currentRow, 1, "Service Done by 檢查員:");
document.SetCellValue(currentRow, 3, technicians);
document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow, _valueEndColumn);
document.SetCellValue(currentRow, _valueStartColumn, "Opinion of Customer(s), if any 客戶意見 :");
currentRow++;
document.MergeWorksheetCells(currentRow, 1, currentRow, 2);
document.SetCellValue(currentRow, 1, "Work Start Time 工作開始時間:");
document.SetCellValue(currentRow, 3, startTime);
document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow + 1, _valueEndColumn);
document.SetCellValue(currentRow, 4, customerOpinion);
currentRow++;
document.MergeWorksheetCells(currentRow, 1, currentRow, 2);
document.SetCellValue(currentRow, 1, "Work Completion Time 工作完成時間:");
document.SetCellValue(currentRow, 3, endTime);
currentRow++;
int signatureStartRow = currentRow;
document.MergeWorksheetCells(currentRow, 1, currentRow + 5, 3);
document.SetCellValue(currentRow, 1, "Office use 公司填寫");
document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow, _valueStartColumn + 1);
document.SetCellValue(currentRow, _valueStartColumn, "Authorized Signature & Chop for Work Completion to Satisfaction");
currentRow++;
document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow, _valueStartColumn + 1);
document.SetCellValue(currentRow, _valueStartColumn, "以上工作認可完成請客戶蓋章及簽署");
currentRow = currentRow + 4;
document.SetCellValue(currentRow, _valueStartColumn + 1, "Date 簽署日期:");
document.MergeWorksheetCells(currentRow, _valueEndColumn - 1, currentRow, _valueEndColumn);
document.SetCellValue(currentRow, _valueEndColumn - 1, signDate);
document.MergeWorksheetCells(signatureStartRow, _valueEndColumn - 1, signatureStartRow + 4, _valueEndColumn);
#region Signature
try
{
if (!string.IsNullOrEmpty(checklist.sSignaturePath))
{
string signatureFullPath = ApplicationSetting.Current.RootFolderPath + checklist.sSignaturePath;
SLPicture signature = new SLPicture(signatureFullPath);
//Get Signature Dimension
System.Drawing.Bitmap bm = new System.Drawing.Bitmap(signatureFullPath);
double pictureHeight = bm.Height;
double pictureWidth = bm.Width;
bm.Dispose();
int scale = 0;
if (pictureHeight / pictureWidth > ((double)signatureMaxHeight) / ((double)signatureMaxWidth))
{
scale = (int)(((double)signatureMaxHeight / pictureHeight) * 100.0);
}
else
{
scale = (int)(((double)signatureMaxWidth / pictureWidth) * 100.0);
}
signature.SetPosition(signatureStartRow - 1 + _borderWidth, _valueEndColumn - 2 + _borderWidth);
signature.ResizeInPercentage(scale, scale);
document.InsertPicture(signature);
}
}
catch
{
}
#endregion Signature
document.SetCellStyle(footerBoxStartRow, 1, currentRow, _valueEndColumn, footerLabelStyle);
currentStyle = footerLabelStyle;
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
document.SetCellStyle(footerBoxStartRow, 1, currentRow, 2, currentStyle);
document.SetCellStyle(footerBoxStartRow, 3, signatureStartRow - 1, _valueEndColumn, currentStyle);
currentStyle = footerLabelStyle;
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
document.SetCellStyle(signatureStartRow, _valueStartColumn, currentRow, _valueStartColumn + 1, currentStyle);
#region Draw Box
document.SetCellStyle(footerBoxStartRow, _valueStartColumn, footerBoxStartRow, _valueEndColumn, topBorderStyle);
document.SetCellStyle(footerBoxStartRow, _valueStartColumn, currentRow, _valueStartColumn, leftBorderStyle);
document.SetCellStyle(footerBoxStartRow, _valueEndColumn, currentRow, _valueEndColumn, rightBorderStyle);
document.SetCellStyle(currentRow, _valueStartColumn, currentRow, _valueEndColumn, bottomBorderStyle);
#endregion Draw Box
#endregion Footer Section
#region Print Setting
SLPageSettings pageSetting = new SLPageSettings();
pageSetting.PaperSize = SLPaperSizeValues.A4Paper;
pageSetting.Orientation = DocumentFormat.OpenXml.Spreadsheet.OrientationValues.Portrait;
pageSetting.ScalePage(1, 1);
pageSetting.TopMargin = pageSetting.BottomMargin = 0.3;
pageSetting.LeftMargin = pageSetting.RightMargin = 0.7;
document.SetPageSettings(pageSetting);
#endregion Print Setting
#region Column Width Setting
document.SetColumnWidth(1, 5.0);
document.SetColumnWidth(2, 40.0);
document.SetColumnWidth(3, 30.0);
for (int i = _valueStartColumn; i <= _valueEndColumn; i++)
{
document.SetColumnWidth(i, 20.0);
}
#endregion Column Width Setting
#region Row Height Setting
document.SetRowHeight(2, 30.0);
document.SetRowHeight(6, 45.0);
document.SetRowHeight(9, 10, 30.0);
document.SetRowHeight(remarkRow, 30.0);
document.SetRowHeight(legendRow, 30.0);
document.SetRowHeight(legendRow + 1, 75.0);
document.SetRowHeight(footerBoxStartRow + 1, currentRow, 20.0);
document.SetRowHeight(signatureStartRow, 30.0);
#endregion Row Height Setting
#region Fill Values
if (checklist.valueGroups != null && checklist.valueGroups.Count() > 0)
{
for (int i = 0; i < checklist.valueGroups.Count(); i++)
{
if (i > 3 && i % 4 == 0)
{
string activeSheetName = document.GetCurrentWorksheetName();
document.AddWorksheet("Dummy");
//Open New Worksheet
currentSheetIndex++;
string currentSheetName = "Checklist" + currentSheetIndex;
bool isCopySuccess = document.CopyWorksheet(activeSheetName, currentSheetName);
document.SelectWorksheet(currentSheetName);
document.DeleteWorksheet("Dummy");
//document.CopyCellFromWorksheet(activeSheetName, 1, 1, currentRow, _valueEndColumn, 1, 1, SLPasteTypeValues.Paste);
//Clear Value Part
for (int c = _valueStartColumn; c <= _valueEndColumn; c++)
{
for (int r = _valueStartRow; r <= valueEndRow; r++)
{
document.SetCellValue(r, c, string.Empty);
}
}
}
ServiceChecklistValueGroupDTO valueGroup = checklist.valueGroups.ElementAt(i);
int columnIndex = (i % 4) + _valueStartColumn;
foreach (ServiceChecklistValueDTO value in valueGroup.values)
{
int rowIndex = subItemRowIndex[value.gFormSubItemID.Value];
document.SetCellValue(rowIndex, columnIndex, value.sValue);
}
}
}
#endregion Fill Values
returnFilePath = string.Format(@"{0}\ServiceChecklist_{1}.xlsx", _serviceChecklistTempFolderPath, DateTime.Now.ToString("yyyyMMddHHmmss"));
document.SaveAs(returnFilePath);
}
catch (Exception e)
{
LogManager.Write(e, "RecWomCrm", "System");
returnFilePath = null;
}
finally
{
document.Dispose();
}
return returnFilePath;
}
