protected override Stream RenderToStream(Document document)
{
using (SLDocument slDocument = new SLDocument())
{
var sheets = document.Sheets.ToList();
if (!string.IsNullOrWhiteSpace(sheets[0].Name))
{
slDocument.RenameWorksheet(slDocument.GetCurrentWorksheetName(), sheets[0].Name);
}
int rowCounter = 0;
foreach (var row in sheets[0].Rows)
{
rowCounter++;
if (row == null)
{
continue;
}
this.RenderRow(slDocument, row, rowCounter);
}
var sheetIndex = 1;
foreach (var sheet in sheets.Skip(1))
{
var res = slDocument.AddWorksheet($"Sheet{sheetIndex++}");
if (!string.IsNullOrWhiteSpace(sheet.Name))
{
slDocument.RenameWorksheet(slDocument.GetCurrentWorksheetName(), sheet.Name);
}
rowCounter = 0;
foreach (var row in sheet.Rows)
{
rowCounter++;
if (row == null)
{
continue;
}
this.RenderRow(slDocument, row, rowCounter);
}
}
var stream = new MemoryStream();
slDocument.SaveAs(stream);
stream.Seek(0, SeekOrigin.Begin);
return(stream);
}
}
public static DataTable CreateDataTable(string fileName, string sheetName)
{
using (var sl = new SLDocument(fileName, sheetName))
{
var stats = sl.GetWorksheetStatistics();
var tableName = sl.GetCurrentWorksheetName();
var table = new DataTable(tableName);
var firstLine = true;
for (var row = stats.StartRowIndex + 1; row <= stats.EndRowIndex; row++)
{
var newRow = table.NewRow();
for (var column = stats.StartColumnIndex; column <= stats.EndColumnIndex; column++)
{
var columnName = sl.GetCellValueAsString(1, column).Trim();
var value = sl.GetCellValueAsString(row, column);
if (firstLine && !table.Columns.Contains(columnName))
{
table.Columns.Add(columnName, typeof(string));
}
newRow[columnName] = value;
}
table.Rows.Add(newRow);
firstLine = false;
}
return(table);
}
}
/// <summary>
/// Writes to excel.
/// </summary>
/// <param name="dt">The data table.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <param name="orgName">Name of the org.</param>
/// <param name="sheetHeader">The sheet header.</param>
/// <returns>
/// the excel file.
/// </returns>
public static byte[] WriteToExcel(DataTable dt, string sheetName, string orgName, string sheetHeader)
{
MemoryStream stream = new MemoryStream();
using (SLDocument document = new SLDocument())
{
var currentSheet = document.GetCurrentWorksheetName();
document.RenameWorksheet(currentSheet, sheetName);
// Set current Org name as main header for the sheet
//document.SetCellValue(1, 1, orgName);
//document.MergeWorksheetCells(1, 1, 1, dt.Columns.Count);
//SLStyle orgNameStyle = document.CreateStyle();
//orgNameStyle.Font.FontName = "Calibri";
//orgNameStyle.Font.FontSize = 14;
//orgNameStyle.Font.FontColor = System.Drawing.Color.Black;
//orgNameStyle.Font.Bold = true;
//orgNameStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
//orgNameStyle.Alignment.Vertical = VerticalAlignmentValues.Center;
//orgNameStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, SLThemeColorIndexValues.Light1Color);
//orgNameStyle.Fill.SetPatternBackgroundColor(SLThemeColorIndexValues.Light1Color);
//document.SetCellStyle(1, 1, orgNameStyle);
// Set sheet title and merge the sheet name
document.SetCellValue(1, 1, sheetHeader);
document.MergeWorksheetCells(1, 1, 1, dt.Columns.Count);
SLStyle header = document.CreateStyle();
header.Font.FontName = "Calibri";
header.Font.FontSize = 12;
header.Font.FontColor = System.Drawing.Color.Black;
header.Font.Bold = true;
header.Alignment.Horizontal = HorizontalAlignmentValues.Center;
header.Alignment.Vertical = VerticalAlignmentValues.Center;
document.SetCellStyle(1, 1, header);
SLStyle columnHeader = document.CreateStyle();
columnHeader.Font.Bold = true;
document.ImportDataTable(2, 1, dt, true);
document.AutoFitColumn(1, dt.Columns.Count);
document.AutoFitRow(2, dt.Rows.Count);
document.SetCellStyle(2, 1, 2, dt.Columns.Count, columnHeader);
var footerIndex = dt.Rows.Count + 5;
document.SetCellValue(footerIndex, 1, string.Format("Generated on {0}", DateTime.Now.ToString("dd/MM/yyyy")));
document.MergeWorksheetCells(footerIndex, 1, footerIndex, 2);
document.SetCellValue(footerIndex, 3, "This report is generated by Krishna Infotech.");
document.MergeWorksheetCells(footerIndex, 3, footerIndex, dt.Columns.Count);
document.SaveAs(stream);
}
return(stream.ToArray());
}
private List <string> ListColumns()
{
List <string> columns = new List <string>();
for (int x = 1; !string.IsNullOrEmpty(slDocument.GetCellValueAsString(1, x)); x++)
{
columns.Add(slDocument.GetCurrentWorksheetName() + "[.]" + slDocument.GetCellValueAsString(1, x));
}
return(columns);
}
/// <summary>
/// Execute report generation
/// </summary>
///
public void Execute()
{
using (doc = new SLDocument(templateName))
{
// save current worksheet
var activeSheet = doc.GetCurrentWorksheetName();
FillReportWithData(jsonData);
ClearDefinedNames();
// restore current worksheet
doc.SelectWorksheet(activeSheet);
doc.SaveAs(outputFile);
}
doc = null;
}
/// <summary>
/// Converts the HTML to EXCEL.
/// </summary>
/// <returns>Converted document as byte array.</returns>
public byte[] ConvertHTMLToXL(string htmlStr)
{
//Load html document
var htmlDocument = this.HtmlConverterHelper.GetNewHtmlDocument(htmlStr);
int rowIndex = 1, colIndex = 1;
MemoryStream MemoryStream = new MemoryStream();
var tables = htmlDocument.DocumentNode.Descendants("table").ToList();
tables.ForEach(table =>
{
this.SetSLStyles(table);
});
using (var document = new SLDocument())
{
var rows = tables[0].Descendants("tr");
document.RenameWorksheet(document.GetCurrentWorksheetName(), tables[0].Id);
foreach (var row in rows)
{
var cells = row.Descendants("td");
var rowStyles = this.tableRowStyle[tables[0].Id];
var cellStyles = this.tableCellStyle[tables[0].Id];
colIndex = 1;
foreach (var cell in cells)
{
HtslCellStyle style;
var styleExists = cellStyles.TryGetValue(string.Format(HtslConstants.RowColPlaceHolder, rowIndex, colIndex), out style);
document.SetCellValue(rowIndex, colIndex, cell.InnerText);
if (styleExists)
{
document.SetCellStyle(rowIndex, colIndex, style as SLStyle);
}
++colIndex;
}
++rowIndex;
}
document.SaveAs(MemoryStream);
}
return(MemoryStream.ToArray());
}
/// <summary>
/// Creates the data table.
/// </summary>
/// <param name="sheet">The sheet.</param>
/// <param name="hasHeaders">if set to <c>true</c> [has headers].</param>
/// <returns>DataTable.</returns>
public static DataTable CreateDataTable(this SLDocument sheet, bool hasHeaders = true)
{
DataTable dt = new DataTable(sheet.GetCurrentWorksheetName());
var stats = sheet.GetWorksheetStatistics();
var columnsCount = stats.NumberOfColumns;
var rowsCount = stats.NumberOfRows;
string colName;
for (int i = 0; i < columnsCount; i++)
{
colName = null;
var index = i + 1;
if (hasHeaders)
{
colName = sheet.GetCellValueAsString(1, i + 1);
if (!String.IsNullOrWhiteSpace(colName) && dt.Columns.Contains(colName))
{
//Set unknown alias due to the column will be ignored.
//This solution avoids alternative that is to count the similar names to increment a counter.
colName = String.Concat(colName, "-", Guid.NewGuid().ToString("N"));
}
}
if (String.IsNullOrWhiteSpace(colName))
{
colName = $"Col{index.ToString().PadLeft(4, '0')}";
}
dt.Columns.Add(new DataColumn(colName));
}
for (int i = 1; i <= rowsCount; i++)
{
if (hasHeaders && i == 1)
{
continue;
}
DataRow dr = dt.NewRow();
for (int j = 1; j <= columnsCount; j++)
{
dr[j - 1] = sheet.GetCellValueAsString(i, j);
}
dt.Rows.Add(dr);
}
return(dt);
}
public mvReply vna([FromBody] mvSecurity model)
{
mvReply oR = new mvReply();
oR.result = 0;
if (!Verify(model.token))
{
oR.message = "No Autorizado";
return(oR);
}
try
{
using (ExamenEntities db = new ExamenEntities())
{
string ruta = "C:\\Users\\danie\\Desktop\\EjercicioDictum.xlsx";
SLDocument sl = new SLDocument(ruta);
SpreadSheet oSpreadSheet = new SpreadSheet(sl.GetCurrentWorksheetName());
List <mvInputs> oModel = new List <mvInputs>();
int iCol = 1;
const int rowHead = 1;
const int row = 2;
while (sl.GetCellValueAsString(rowHead, iCol) != "TASA")
{
oModel.Add(new mvInputs()
{
Name = sl.GetCellValueAsString(rowHead, iCol),
Valor = sl.GetCellValueAsInt32(row, iCol)
});
iCol++;
}
oSpreadSheet.set(oModel);
oSpreadSheet.calculate();
oR.data = oSpreadSheet.get();
oR.message = "resultado:" + oSpreadSheet.vna();
oR.result = 1;
}
}
catch (Exception ex)
{
oR.message = "Ocurrio un error en el servidor, inteta más tarde";
}
return(oR);
}
/// <summary>
/// Writes to excel.
/// </summary>
/// <param name="dt">The data table.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <returns>
/// the excel file.
/// </returns>
public static byte[] WriteToExcel(DataTable dt, string sheetName)
{
MemoryStream stream = new MemoryStream();
using (SLDocument document = new SLDocument())
{
var currentSheet = document.GetCurrentWorksheetName();
document.RenameWorksheet(currentSheet, sheetName);
SLStyle columnHeader = document.CreateStyle();
columnHeader.Font.Bold = true;
document.ImportDataTable(1, 1, dt, true);
document.SetCellStyle(1, 1, 1, dt.Columns.Count, columnHeader);
document.AutoFitColumn(1, dt.Columns.Count);
document.AutoFitRow(1, dt.Rows.Count);
document.SaveAs(stream);
}
return(stream.ToArray());
}
public FileReaderResult Read(String fullFilepath, IList <FileReaderMap> maps, Boolean firstRowAreTitles = true)
{
if (String.IsNullOrEmpty(fullFilepath))
{
throw new ArgumentException("fullFilepath");
}
if (!File.Exists(fullFilepath))
{
throw new InvalidOperationException("File's not found.");
}
var result = new FileReaderResult();
using (var doc = new SLDocument(fullFilepath))
{
doc.SelectWorksheet(doc.GetCurrentWorksheetName());
var stats = doc.GetWorksheetStatistics();
for (int row = 1; row <= stats.EndRowIndex; row++)
{
for (int col = 1; col <= stats.EndColumnIndex; col++)
{
String data = doc.GetCellValueAsString(row, col);
String cleaned = String.IsNullOrEmpty(data) ? data : data.Trim();
if (firstRowAreTitles && row == 1)
{
result.Titles.Add(cleaned);
}
else
{
result.Data.Add(new GridData {
Row = row, Column = col, CellValue = cleaned
});
}
}
}
}
return(result);
}
//TODO refactor with Validate Clickstreams for the report extraction bit
public void validatePresentations()
{
Dictionary <string, string> presreport = new Dictionary <string, string>(); //Reported Presenation IDs
Dictionary <string, bool> pubformpres = new Dictionary <string, bool>(); //Publishing Form Presenation IDs - bool indicates if validated or not
SortedDictionary <string, string> missingpubs = new SortedDictionary <string, string>();
//Grab Presentation IDs from each publishing form using presentation report
//List publising forms
listFileNames(pubfolder); //Populate list of Publishing Form spreadsheets
foreach (var f in pubforms)
{
SLDocument pubform = openPubForm(pubfolder + "\\" + f.Value);
string curSheet = pubform.GetCurrentWorksheetName();
pubform.SelectWorksheet(PresTab);
string PresID = pubform.GetCellValueAsString(PresIDCell);
try { pubformpres.Add(PresID, false); } //Default to not validated
catch (Exception e) { if (e.HResult == -2147024809)
{
}
else
{
throw e;
} } //Allow for duplicates
}
//Then verify against the Presentation IDs in Veeva (and those coded into "GotoSlide" in the pages? No, rely on manual checks for that for now)
//Use publishing report
//Open Excel Clickstream Report File and extract list of clickstreamids
SLDocument repform = new SLDocument(presrepfile);
SLWorksheetStatistics stats1 = repform.GetWorksheetStatistics();
for (int j = repstartrow; j <= stats1.EndRowIndex; j++)
{
string Pres = repform.GetCellValueAsString(prescolumn + j); //Presentation ID
try { presreport.Add(Pres, Pres); }
catch (Exception e) { if (e.HResult == -2147024809)
{
}
else
{
throw e;
} } //Allow for duplicates
}
//Now Compare
HashSet <string> lines = new HashSet <string>();
lines.Add("Presentation IDs Missing from Publishing Forms :");
string[] headermessage = new string[] { "Presentations found in Publising Form but not found in Veeva" };
//Validate names in publishing form match folders
//Don't use a clever (big O) comparison as may not be a perfect match
foreach (var v in presreport)
{
if (pubformpres.ContainsKey(v.Key))
{
pubformpres[v.Key] = true; //Flag as validated
}
else
{
//Presentation from spreadsheets has no corresponding entry in Veeva
missingpubs.Add(v.Key, v.Value);
}
}
//Identify any pubforms that are still invalid (i.e. no presenation in veeva matches)
if (pubformpres.ContainsValue(false))
{
var matches = pubformpres.Where(pair => pair.Value != true).Select(pair => pair.Key);
Console.Write(matches.ToString());
System.IO.File.WriteAllLines(folderPath + "\\PresentationValidation" + logfile, headermessage);
System.IO.File.AppendAllLines(folderPath + "\\PresentationValidation" + logfile, matches.ToArray <string>());
}
//Also need to identify if any veeva presentations lack a corresponding pub form
foreach (var v in missingpubs)
{
Console.WriteLine(v.Key);
lines.Add(v.Key);
}
System.IO.File.AppendAllLines(folderPath + "\\PresentationValidation" + logfile, lines.ToArray <string>());
//For completeness, probably worth writing out the "true", i.e. validated, publishing form names, so can see that all the lists tie up together.
}
//TODO compare common code to ExtractClickstream and refactor
private void ExtractKeyMessage(string filename, string thisFolder, bool current = true)
{
//Extract key messages from publishing form
string PresID = "";
SortedDictionary <int, string> thisPresKeyMessages = new SortedDictionary <int, string>();
SLDocument pubform = new SLDocument(thisFolder + "\\" + filename);
string curSheet = pubform.GetCurrentWorksheetName();
if (curSheet != "")
{
pubform.SelectWorksheet(PresTab);
}
SLWorksheetStatistics stats1 = pubform.GetWorksheetStatistics();
//Get Presentation ID
PresID = pubform.GetCellValueAsString(PresIDCell);
//Vulnerability on EndRowIndex (AbbVie Care and Safety Profile for RA)//Think I fixed it - need to ensure worksheet is selected before making the Statistics call
for (int j = keymessagestartrow; j <= stats1.EndRowIndex; j++)
{
string kmzip = pubform.GetCellValueAsString(KeyMessageCol + j);
int DisplayOrder = pubform.GetCellValueAsInt32(pubdisplaynumbercolumn + j);
if (kmzip.Contains(".zip"))
{
try
{
if (current)
{
thisPresKeyMessages.Add(DisplayOrder, kmzip.Replace(".zip", "")); //Doing it this way round means that where a slide is duplicated we still get it in each presentation for the tree view
keymessages.Add(kmzip.Replace(".zip", ""), PresID); //Temp To Lower
}
else
{
oldkeymessages.Add(kmzip.Replace(".zip", ""), PresID);
} //Temp To Lower
}
catch (Exception e)
{ if (e.HResult == -2147024809)
{
//Ignore as duplicate keys can occur if same key message in more than one presentation
}
else
{
throw e;
} }
}
else
{//May be shared message row
try {
var skm = pubform.GetCellValueAsString(pubsharedcolumn + j);
if (skm.Contains(".zip"))
{
sharedkeymessages.Add(PresID, skm); //We can do this by Presentation rather than by key message
}
else
{//Trying to cater for other eventualities
if (pubform.GetCellValueAsString(pubsharedcolumn + (j - 1)).Contains("Shared Resources\nFile Name"))
{
sharedkeymessages.Add(PresID, skm);
}
}
}catch (Exception e) { if (e.HResult == -2147024809)
{
}
else
{
throw e;
} }
}
}
//Resort thisPresKeymessages into display order
thisPresKeyMessages.OrderBy(key => key.Key);
try
{
orderedKeymessages.Add(PresID, thisPresKeyMessages);
}
catch (Exception e)
{
if (e.HResult == -2147024809)
{
}
else
{
throw e;
}
}
}
private void SetRowData(int colCount)
{
int colNo = 0;
int startCol = 4;
rowNo++;
try
{
foreach (string item in RowData)
{
if (colNo + 1 > colCount + startCol)
{
colNo = 0;
}
if (entryNumber != currentEntryNum)
{
formNmbr = selectedPrefix + entryNumber.ToString();
currentEntryNum = entryNumber;
printFormNumber = true;
}
if (colNo == 0)
{
sld.SetCellStyle(rowNo, colNo + 1, stylNormal);
sld.SetCellValue(rowNo, colNo + 1, sectName);
sld.SetCellValue(rowNo - 1, colNo + 2, "Modified By");
sld.SetCellValue(rowNo, colNo + 2, modName);
sld.SetCellValue(rowNo - 1, colNo + 3, "Status");
sld.SetCellValue(rowNo, colNo + 3, status);
sld.SetCellStyle(rowNo - 1, colNo + 1, stylNormal);
if (printFormNumber)
{ //put FormNumber into col1 row1
stylNormal.Font.Bold = true;
sld.SetCellStyle(rowNo - 1, colNo + 1, stylNormal);
sld.SetCellValue(rowNo - 1, ++colNo, formNmbr);
stylNormal.Font.Bold = false;
}
colNo = 3;
if (sld.GetCurrentWorksheetName().Equals("Sheet1"))
{
sld.RenameWorksheet("Sheet1", selectedPrefix);
}
printFormNumber = false;
sld.SetCellValue(rowNo, ++colNo, item);
}
else
{
colNo++;
}
sld.SetCellValue(rowNo, colNo, item);
}
}
catch (IndexOutOfRangeException ex)
{
lm.Write("OutputManager/FormatAttachment: IOOR Exception " + ex.Message);
}
catch (Exception ex)
{
lm.Write("OutputManager/FormatAttachment: Exception " + ex.Message);
}
}
/// <summary>
/// Exports the poll result report.
/// </summary>
/// <param name="poll">The poll.</param>
/// <param name="pollResponse">The poll response.</param>
/// <param name="workSheetName">Name of the work sheet.</param>
/// <returns>
/// excel byte array
/// </returns>
public byte[] GetFailureExportReport(IEnumerable <AssetManager.Models.FailureView> failures, string workSheetName)
{
var textCenterBold = new SLStyle()
{
Alignment = new SLAlignment()
{
Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center
}, Font = { Bold = true }
};
var textCenter = new SLStyle()
{
Alignment = { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
};
var textLeft = new SLStyle()
{
Alignment = { Horizontal = HorizontalAlignmentValues.Left }
};
var headColoumnStyle = new SLStyle()
{
Font = { FontSize = 12 }
};
SLStyle highlightStyle = new SLStyle()
{
Font = { FontSize = 12, Bold = true }
};
highlightStyle.SetWrapText(true);
highlightStyle.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
highlightStyle.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
highlightStyle.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
highlightStyle.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetWrapText(true);
highlightStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
highlightStyle.Alignment.Vertical = VerticalAlignmentValues.Center;
MemoryStream memoryStream = new MemoryStream();
using (SLDocument document = new SLDocument())
{
var currentSheetName = document.GetCurrentWorksheetName();
document.RenameWorksheet(currentSheetName, workSheetName);
document.SetCellValue(1, 1, string.Format("SFR Report {0}", DateTime.Now.ToString("dd/MM/yyyy")));
document.SetCellStyle(1, 1, textCenterBold);
document.MergeWorksheetCells(1, 1, 1, 16);
document.SetCellValue(2, 1, "SL");
document.SetColumnWidth(1, 4);
document.SetColumnWidth(2, 4);
document.SetColumnWidth(3, 3.84);
document.SetColumnWidth(4, 8);
document.SetColumnWidth(5, 8);
document.SetColumnWidth(6, 8);
document.SetColumnWidth(7, 8);
document.SetColumnWidth(8, 19.84);
document.SetColumnWidth(9, 10);
document.SetColumnWidth(10, 10);
document.SetColumnWidth(11, 9.75);
document.SetColumnWidth(12, 11);
document.SetColumnWidth(13, 9);
document.SetColumnWidth(14, 11);
document.SetColumnWidth(15, 9);
document.SetColumnWidth(16, 8.75);
//document.SetColumnWidth(17, 8.75);
document.SetCellValue(2, 2, "SFRNo");
document.SetCellValue(2, 3, "Reported");
document.SetCellValue(2, 4, "Chargeable / Non Chargeable");
document.SetCellValue(2, 5, "Station");
document.SetCellValue(2, 6, "Gear at Fault");
document.SetCellValue(2, 7, "Sub Gear at Fault");
document.SetCellValue(2, 8, "Brief Description");
document.SetCellValue(2, 9, "Cause of Failure");
document.SetCellValue(2, 10, "Sub Cause of Failure");
//document.SetCellValue(1, 7, "Train Loss Punctuality");
document.SetCellValue(2, 11, "Train Detained");
//document.SetCellValue(1, 9, "Failure Chargeable");
//document.SetCellValue(1, 8, "Department");
document.SetCellValue(2, 12, "Time of Occ urrence");
document.SetCellValue(2, 13, "Time Signal Main In formed");
document.SetCellValue(2, 14, "Time Signal Main Reached");
document.SetCellValue(2, 15, "Time Rectified");
document.SetCellValue(2, 16, "Dur ation");
//document.SetCellValue(2, 17, "Last Visit");
var pageSettings = document.GetPageSettings();
pageSettings.ScalePage(95);
pageSettings.PaperSize = SLPaperSizeValues.A4Paper;
pageSettings.Orientation = OrientationValues.Landscape;
pageSettings.HeaderMargin = 0.3;
pageSettings.TopMargin = 0.2;
pageSettings.BottomMargin = 0.2;
pageSettings.LeftMargin = 0.2;
pageSettings.RightMargin = 0.2;
pageSettings.FooterMargin = 0.3;
pageSettings.OddFooterText = "Designed and Developded by Krishna Infotech";
pageSettings.AppendOddFooter(SLHeaderFooterFormatCodeValues.Right);
document.SetPageSettings(pageSettings);
document.SetCellStyle(2, 1, 2, 16, highlightStyle);
int lastActiveRow = 3;
failures.ForEach(failure =>
{
document.SetCellValue(lastActiveRow, 1, lastActiveRow - 2);
document.SetCellValue(lastActiveRow, 2, failure.SFRNo);
document.SetCellValue(lastActiveRow, 3, failure.Reported);
document.SetCellValue(lastActiveRow, 4, failure.FailureChargeable ? "Yes" : "No");
document.SetCellValue(lastActiveRow, 5, failure.Station);
document.SetCellValue(lastActiveRow, 6, failure.GearFault);
document.SetCellValue(lastActiveRow, 7, failure.SubGearFault);
document.SetCellValue(lastActiveRow, 8, $"{failure.Description} ---- SSE:{failure.SSELastVisitOn.ToString()} JE:{failure.JELastVisitOn.ToString()} ESM: {failure.ESMLastVisitOn.ToString()}");
document.SetCellValue(lastActiveRow, 9, failure.CauseOfFailureValue);
document.SetCellValue(lastActiveRow, 10, failure.SubCauseOfFailureValue);
document.SetCellValue(lastActiveRow, 11, failure.TrainDetained);
document.SetCellValue(lastActiveRow, 12, failure.TimeOfOccurance.ToString());
document.SetCellValue(lastActiveRow, 13, failure.TimeSignalMainInformed.ToString());
document.SetCellValue(lastActiveRow, 14, failure.TimeSignalMainReached.ToString());
document.SetCellValue(lastActiveRow, 15, failure.TimeRectified.ToString());
document.SetCellValue(lastActiveRow, 16, failure.TotalDuration.HasValue ? Math.Round(TimeSpan.FromHours(failure.TotalDuration.Value).TotalMinutes).ToString() : string.Empty);
lastActiveRow++;
});
document.SetCellStyle(3, 1, lastActiveRow, 16, textCenter);
var footerIndex = failures.Count() + 6;
document.SetCellValue(footerIndex, 1, string.Format("Generated on {0}", DateTime.Now.ToString("dd/MM/yyyy")));
document.MergeWorksheetCells(footerIndex, 1, footerIndex, 3);
document.SetCellValue(footerIndex, 4, "This report is generated by SFR Software");
document.MergeWorksheetCells(footerIndex, 4, footerIndex, 9);
document.SaveAs(memoryStream);
return(memoryStream.ToArray());
}
}