noisedestroyers/DP44
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
DP44/DataPRO/Reports/ReportBase.cs
noisedestroyers bc3ac1d4c9 init
2026-04-17 14:55:32 -04:00

2283 lines
108 KiB
C#
Raw Permalink Blame History

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.ComponentModel;
using C1.WPF.Pdf;
using System.Windows.Media;
using System.Windows;
namespace DTS.Reports
{
/// <summary>
/// this class handles outputting generic reports in excel using templates
/// we can override it later if we need additional functionality, but
/// I think right now it contains everything we need
/// </summary>
public class ReportBase : DTS.Common.Base.BasePropertyChanged
{
public delegate void ReportFileInUseDelegate(object o);
public delegate bool ReportFileWillBeOverwrittenDelegate(object o);
public ReportFileInUseDelegate ReportFileInUseHandler = null;
public ReportFileWillBeOverwrittenDelegate ReportFileWillBeOverwrittenHandler = null;
protected void ReportFileInUse(object o)
{
ReportFileInUseHandler?.Invoke(o);
}
protected void ReportFileWillBeOverwritten(object o)
{
ReportFileWillBeOverwrittenHandler?.Invoke(o);
}
/// <summary>
/// many reports need to be timestamped. This function supplies a unified way of formatting the timestamp
/// </summary>
/// <returns>formatted timestamp as a string</returns>
public static string GetFilenameTimeStamp()
{
DateTime now = DateTime.Now;
return string.Format("{0:0000}-{1:00}-{2:00}T{3:00}{4:00}{5:00}", now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second);
}
/// <summary>
/// most of the tests contain the test setup name, so it is abstracted to a common base class here
/// The test setup name is the name of the current test setup
/// </summary>
public string TestSetupName { get; set; }
/// <summary>
/// a test setup can contain a description. This is the description of the current test setup.
/// </summary>
public string TestDescription { get; set; }
/// <summary>
/// this is the current test id. Note the current test id may be blank if you are creating a report from before a test id is provided [say from edit test setup]
/// </summary>
public string TestId { get; set; }
/// <summary>
/// the date and time of the current test, or of the report if the test hasn't been started yet
/// </summary>
public DateTime ReportDate { get; set; }
/// <summary>
/// some reports show a das serial number in the header.
/// This field is optional on some reports
/// </summary>
public string DASSerial { get; set; }
/// <summary>
/// the sample rate of the current test.
/// this field is optional on some reports
/// </summary>
public double SampleRate { get; set; }
/// <summary>
/// the recording mode of the current test.
/// this field is optional on some reports
/// </summary>
public string RecordingMode { get; set; }
/// <summary>
/// the pre-trigger time of the current test.
/// this field is optional on some reports
/// </summary>
public double PreTriggerTime { get; set; }
/// <summary>
/// the post-trigger time of the current test.
/// this field is optional on some reports
/// </summary>
public double PostTriggerTime { get; set; }
/// <summary>
/// the circuit closure for the trigger line.
/// this field is optional on some reports
/// </summary>
public string TriggerClosure { get; set; }
/// <summary>
/// the circuit closure for the start line.
/// this field is optional on some reports
/// </summary>
public string StartClosure { get; set; }
/// <summary>
/// full path to template file
/// this should be the location of the report template
/// </summary>
private string _templateFileName;
public string TemplateFilename
{
get => _templateFileName;
set => SetProperty(ref _templateFileName, value, "TemplateFilename");
}
/// <summary>
/// full path to output file
/// this should be the destination of the report
/// </summary>
private string _outputFileName;
public string OutputFilename
{
get => _outputFileName;
set => SetProperty(ref _outputFileName, value, "OutputFilename");
}
private string _destinationTemplateFilename;
public string DestinationTemplateFilename
{
get => _destinationTemplateFilename;
set => SetProperty(ref _destinationTemplateFilename, value, "DestinationTemplateFilename");
}
/// <summary>
/// we will be doing a lot of named range lookups, so we build a table out of them for efficiency
/// the key is the name of the named range, the value is the actual named range
/// </summary>
protected Dictionary<string, DefinedName> _nameLookup = new Dictionary<string, DefinedName>();
/// <summary>
/// outputs a report, first deleting any existing report in the output location, then copying in the template to modify
/// </summary>
/// <param name="tables"></param>
public virtual void OutputReport(DataTable[] tables, DataTable[] cellstyles)
{
DeleteExistingReport();
CopyTemplate();
using (SpreadsheetDocument sp = SpreadsheetDocument.Open(OutputFilename, true))
{
//in xlsx, any string literal is usually part of a global list of strings, so we get that global list
//so we can add to it as we need to
SharedStringTablePart shareStringPart;
if (sp.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = sp.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = sp.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
//we will be relying on named ranges in order to find actual cells that we
//need to put values in, so we build a dictionary of all the named ranges in the spreadsheet
DefinedNames definedNames = sp.WorkbookPart.Workbook.DefinedNames;
foreach (DefinedName dn in definedNames)
{
_nameLookup[dn.Name.Value] = dn;
}
//#FF7EFB6A
UInt32Value fid = createFill(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, System.Drawing.Color.FromArgb(Convert.ToInt32(0xFF), Convert.ToInt32(0x7E),
Convert.ToInt32(0xFB), Convert.ToInt32(0x6a)));
_passStyle = createCellFormat(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, null, fid, null);
fid = createFill(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, System.Drawing.Color.LightPink);
_failStyle = createCellFormat(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, null, fid, null);
fid = createFill(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, System.Drawing.Color.CornflowerBlue);
_warnStyle = createCellFormat(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, null, fid, null);
//the spreadsheet can contain multiple pages
//for now this handles each table as a different page
int idx = 0;
foreach (DataTable dt in tables)
{
ProcessTable(dt, ref shareStringPart, sp, (null == cellstyles) ? null : cellstyles[idx]);
idx++;
}
}
}
/// <summary>
/// outputs a PDF report, first deleting any existing report in the output location, then copying in the template to modify
/// </summary>
/// <param name="tables"></param>
public virtual void OutputReportPDF(DataTable[] tables, DataTable[] cellStyles, string sensitivityDisplayFormat)
{
DeleteExistingReport();
DeleteExistingPDFTemplate();
CopyExcelTemplateForPDF();
C1PdfDocument pdf = new C1PdfDocument();
using (SpreadsheetDocument sp = SpreadsheetDocument.Open(DestinationTemplateFilename, true))
{
//in xlsx, any string literal is usually part of a global list of strings, so we get that global list
//so we can add to it as we need to
SharedStringTablePart shareStringPart;
if (sp.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = sp.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = sp.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
//we will be relying on named ranges in order to find actual cells that we
//need to put values in, so we build a dictionary of all the named ranges in the spreadsheet
DefinedNames definedNames = sp.WorkbookPart.Workbook.DefinedNames;
foreach (DefinedName dn in definedNames)
{
_nameLookup[dn.Name.Value] = dn;
}
//#FF7EFB6A
UInt32Value fid = createFill(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, System.Drawing.Color.FromArgb(Convert.ToInt32(0xFF), Convert.ToInt32(0x7E),
Convert.ToInt32(0xFB), Convert.ToInt32(0x6a)));
_passStyle = createCellFormat(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, null, fid, null);
fid = createFill(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, System.Drawing.Color.LightPink);
_failStyle = createCellFormat(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, null, fid, null);
fid = createFill(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, System.Drawing.Color.CornflowerBlue);
_warnStyle = createCellFormat(sp.WorkbookPart.WorkbookStylesPart.Stylesheet, null, fid, null);
//the spreadsheet can contain multiple pages
//for now this handles each table as a different page
int idx = 0;
foreach (DataTable dt in tables)
{
if ((dt.Rows.Count > 0) && (idx > 0))
{
pdf.NewPage();
}
switch (dt.TableName)
{
case "List":
pdf.Landscape = false;
LeftMargin = 72;
RightMargin = 72;
PageHeight = 792;
PageWidth = 612;
RowsPerPage = 45;
ColsPerPage = 9; // A-I
DataRowsPerPage = 36;
break;
case "Layout":
pdf.Landscape = true;
LeftMargin = 50;
RightMargin = 50;
PageHeight = 612;
PageWidth = 792;
RowsPerPage = 33;
ColsPerPage = 14; // A-N
DataRowsPerPage = 22;
break;
case "Squibs":
pdf.Landscape = true;
LeftMargin = 50;
RightMargin = 50;
PageHeight = 612;
PageWidth = 792;
RowsPerPage = 34;
ColsPerPage = 14; // A-N
DataRowsPerPage = 26;
break;
case "Analog":
pdf.Landscape = true;
LeftMargin = 50;
RightMargin = 50;
PageHeight = 612;
PageWidth = 792;
RowsPerPage = 33;
ColsPerPage = 14; // A-N
DataRowsPerPage = 25;
break;
case "Digital_Channels":
case "Squib_Channels":
case "Calculated_Channels":
pdf.Landscape = true;
LeftMargin = 50;
RightMargin = 50;
PageHeight = 612;
PageWidth = 792;
RowsPerPage = 33;
ColsPerPage = 14; // A-N
DataRowsPerPage = 26;
break;
case "Sensor_Channels":
pdf.Landscape = true;
LeftMargin = 50;
RightMargin = 50;
PageHeight = 612;
PageWidth = 792;
RowsPerPage = 38;
ColsPerPage = 18; // A-R
DataRowsPerPage = 29;
break;
case "DAS":
if (OutputFilename.Contains("TestSummary"))
{
pdf.Landscape = true;
LeftMargin = 50;
RightMargin = 50;
PageHeight = 612;
PageWidth = 792;
ColsPerPage = 14; // A-N
RowsPerPage = 36;
DataRowsPerPage = 27;
}
else
{
//Diagnostics
pdf.Landscape = true;
LeftMargin = 50;
RightMargin = 50;
PageHeight = 612;
PageWidth = 792;
ColsPerPage = 14; // A-N
RowsPerPage = 34;
DataRowsPerPage = 25;
}
break;
}
var x = sp.WorkbookPart.GetPartsOfType<WorksheetPart>().First();
var y = x.Worksheet.First();
ProcessTablePDF(dt, ref shareStringPart, sp, (null == cellStyles) ? null : cellStyles[idx], pdf, sensitivityDisplayFormat);
idx++;
}
}
pdf.Save(OutputFilename);
DeleteExistingPDFTemplate();
}
public void CopyReportIfNeeded(string folderNameId, string outputFileName)
{
var destinationFolder = System.IO.Path.Combine(folderNameId, Common.Constants.REPORT_DIR_NAME);
var DestinationFilename = System.IO.Path.Combine(destinationFolder, System.IO.Path.GetFileName(outputFileName));
var fullPathOutputFilename = System.IO.Path.GetFullPath(OutputFilename);
var fullPathDestinationFilename = System.IO.Path.GetFullPath(DestinationFilename);
if (fullPathOutputFilename != fullPathDestinationFilename)
{
if (System.IO.File.Exists(DestinationFilename)) { System.IO.File.Delete(DestinationFilename); }
if (!System.IO.Directory.Exists(destinationFolder))
{
System.IO.Directory.CreateDirectory(destinationFolder);
}
System.IO.File.Copy(OutputFilename, DestinationFilename);
System.IO.File.Delete(OutputFilename);
}
}
/// <summary>
/// helper class for dealing with pages in the excel report
/// </summary>
public class TablePage
{
/// <summary>
/// row index for the page start. assumes all pages are done vertically, not horizontally
/// we need extra logic if we want to handle going horizontal too
/// </summary>
public int PageStart { get; set; }
/// <summary>
/// row index for the page end. Assumes vertical, just like PageStart
/// </summary>
public int PageEnd { get; set; }
/// <summary>
/// row index for the table on the page
/// header row inclusive
/// </summary>
public int RowStart { get; set; }
/// <summary>
/// row index for the last row in the table
/// </summary>
public int RowEnd { get; set; }
/// <summary>
/// total number of table rows handled by this page
/// </summary>
public int RowsHandled => RowEnd - RowStart;//rowstart includes header row, so don't add +1
}
/// <summary>
/// gets a list of all the pages and the table on that page, given a sheet name
/// </summary>
/// <param name="tablename"></param>
/// <returns></returns>
protected virtual List<TablePage> GetTablePages(string sheetname)
{
var currentPage = 1;
var bDone = false;
var pages = new List<TablePage>();
while (!bDone)
{
var pageStart = GetDefinedName($"{sheetname}Page{currentPage}Start");
var pageEnd = GetDefinedName($"{sheetname}Page{currentPage}End");
var tableStart = GetDefinedName($"{sheetname}Table{currentPage}Start");
var tableEnd = GetDefinedName($"{sheetname}Table{currentPage}End");
if (null != pageStart && null != pageEnd && null != tableStart && null != tableEnd)
{
pages.Add(new TablePage()
{
RowStart = GetRowIndex(tableStart),
RowEnd = GetRowIndex(tableEnd),
PageStart = GetRowIndex(pageStart),
PageEnd = GetRowIndex(pageEnd)
});
}
else { bDone = true; }
currentPage++;
}
return pages;
}
/// <summary>
/// we have to do a lot of string parsing to go from cell references to sheet/column/row values
/// so, we declare these ahead of time to save time doing it over and over again
/// </summary>
protected static char[] _sheetSplitter = new char[] { '!' };
protected static char[] _cellSplitter = new char[] { '$' };
/// <summary>
/// gets a row index given an excel named range
/// </summary>
/// <param name="dn"></param>
/// <returns></returns>
protected int GetRowIndex(DefinedName dn)
{
//defined names in excel are in the form SheetId!$Column$Row
var tokens = dn.Text.Split(_sheetSplitter);
tokens = tokens[1].Split(_cellSplitter);
return int.Parse(tokens.Last());
}
/// <summary>
/// looks for a defined name in the worksheet, returns null otherwise
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
protected virtual DefinedName GetDefinedName(string name)
{
if (_nameLookup.ContainsKey(name)) { return _nameLookup[name]; }
else { return null; }
}
/// <summary>
/// fill styles applies to diagnostic reports, or pass/fail style entries
/// </summary>
public enum FillStyles
{
Pass,
Fail,
NotTested,
Warn
}
/// <summary>
/// pass and fail styles are created at runtime, we store their index here for later use
/// </summary>
public UInt32Value _failStyle;
public UInt32Value _passStyle;
public UInt32Value _warnStyle;
//private UInt32Value _notTestedStyle;
/// <summary>
/// these are fields that may appear in a report header/page header
/// they are not required, the code will look for them in a page, and populate the fields if they are found
/// </summary>
protected enum PageFields
{
TestSetupName,
TestDescription,
TestId,
ReportDate,
DASSerial,
DASSPS,
RecordMode,
PreTrigger,
PostTrigger,
TriggerClosure,
StartClosure,
RecordingMode
}
/// <summary>
/// handles a one worksheet in the workbook
/// A worksheet is a set of pages with a table on each page
/// each table is populated using the datatable passed in
/// </summary>
/// <param name="dt">table to process TableName is the SheetId</param>
/// <param name="shareStringPart"></param>
/// <param name="sp"></param>
protected virtual void ProcessTable(DataTable dt, ref SharedStringTablePart shareStringPart, SpreadsheetDocument sp, DataTable dtStyle)
{
var ws = GetWorksheetPartByName(sp, dt.TableName);
if (null == ws) { return; }
//get a list of all pages on this sheet
var pages = GetTablePages(dt.TableName);
//build a list of pages we don't need
//if we have already handled all the rows we need in the sheet, we don't need anymore pages
var toDelete = new List<TablePage>();
var rowsHandledSoFar = 0;
foreach (var page in pages)
{
if (rowsHandledSoFar >= dt.Rows.Count)
{
toDelete.Add(page);
}
else { rowsHandledSoFar += page.RowsHandled; }
}
//delete unnecessary pages
foreach (var page in toDelete)
{
pages.Remove(page);
DeletePage(page, ws.Worksheet);
}
//if there are no rows, delete the whole worksheet ...
if (dt.Rows.Count < 1)
{
DeleteWorksheet(sp, dt.TableName);
return;
}
//fill in each page
for (var i = 0; i < pages.Count; i++)
{
//set the page footer
var key = $"{dt.TableName}Page{1 + i}Footer";
var dn = GetDefinedName(key);
if (null == dn) { continue; }
var cell = GetCell(ws.Worksheet, dn.Text);
if (null == cell) { continue; }
cell.CellValue = new CellValue(InsertSharedStringItem($"{dt.TableName} Page {1 + i}/{pages.Count}", shareStringPart).ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
var fields = Enum.GetValues(typeof(PageFields)).Cast<PageFields>().ToArray();
foreach (var field in fields)
{
var name = $"{dt.TableName}Page{1 + i}{field.ToString()}";
dn = GetDefinedName(name);
if (null != dn)
{
cell = GetCell(ws.Worksheet, dn.Text);
if (null != cell)
{
switch (field)
{
case PageFields.DASSerial:
cell.CellValue = new CellValue(InsertSharedStringItem(DASSerial, shareStringPart).ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
break;
case PageFields.DASSPS:
cell.CellValue = new CellValue(SampleRate.ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.Number };
break;
case PageFields.PostTrigger:
cell.CellValue = new CellValue(PostTriggerTime.ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.Number };
break;
case PageFields.PreTrigger:
cell.CellValue = new CellValue(PreTriggerTime.ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.Number };
break;
case PageFields.RecordMode:
case PageFields.RecordingMode:
cell.CellValue = new CellValue(InsertSharedStringItem(RecordingMode, shareStringPart).ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
break;
case PageFields.ReportDate:
//cell.CellValue = new CellValue(InsertSharedStringItem(ReportDate, shareStringPart).ToString());
cell.CellValue = new CellValue(ReportDate.ToOADate().ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.Number };
break;
case PageFields.StartClosure:
cell.CellValue = new CellValue(InsertSharedStringItem(StartClosure, shareStringPart).ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
break;
case PageFields.TestDescription:
cell.CellValue = new CellValue(InsertSharedStringItem(TestDescription, shareStringPart).ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
break;
case PageFields.TestId:
cell.CellValue = new CellValue(InsertSharedStringItem(TestId, shareStringPart).ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
break;
case PageFields.TestSetupName:
cell.CellValue = new CellValue(InsertSharedStringItem(TestSetupName, shareStringPart).ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
break;
case PageFields.TriggerClosure:
cell.CellValue = new CellValue(InsertSharedStringItem(TriggerClosure, shareStringPart).ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
break;
}
}
}
}
}
//now we get to fill in the table(s)
var row = 1;
for (var i = 0; i < dt.Rows.Count; i++)
{
foreach (DataColumn c in dt.Columns)
{
var name = $"{dt.TableName}{c.ColumnName}{row}";
var dn = GetDefinedName(name);
if (null == dn) { continue; } //user isn't required to have every column in their template if they don't want
// if we don't find the cell, we go on our merry way
var o = dt.Rows[i][c.ColumnName];
var cell = GetCell(ws.Worksheet, dn.Text);
if (null == cell) { continue; }
if (null != dtStyle)
{
if (null != dtStyle.Rows[i][c.ColumnName])
{
var istyle = Convert.ToInt32(dtStyle.Rows[i][c.ColumnName]);
var fs = (FillStyles)istyle;
if (fs == FillStyles.Fail) { cell.StyleIndex = _failStyle; }
else if (fs == FillStyles.Pass) { cell.StyleIndex = _passStyle; }
else if (fs == FillStyles.Warn) { cell.StyleIndex = _warnStyle; }
}
}
if (c.DataType == typeof(string))
{
cell.CellValue = new CellValue(InsertSharedStringItem(o as string, shareStringPart).ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
}
else if (c.DataType == typeof(int))
{
cell.CellValue = new CellValue(Convert.ToUInt32(o).ToString(System.Globalization.CultureInfo.InvariantCulture));
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.Number };
}
else if (c.DataType == typeof(bool))
{
cell.CellValue = new CellValue(Convert.ToBoolean(o) ? "1" : "0");
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.Boolean };
}
else if (c.DataType == typeof(double))
{
var d = Convert.ToDouble(o);
if (double.IsNaN(d))
{
cell.CellValue = new CellValue(InsertSharedStringItem("---", shareStringPart).ToString());
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
}
else
{
cell.CellValue = new CellValue(d.ToString(System.Globalization.CultureInfo.InvariantCulture));
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.Number };
}
}
else if (c.DataType == typeof(DateTime))
{
var date = Convert.ToDateTime(o);
if (date.Year < 1900)
{
//there is a limitation to date values in excel, however here we are protecting from values that we just don't want to show
//if the date is <1900 for our purposes, it's probably not real
}
else
{
cell.CellValue = new CellValue(date.ToOADate().ToString(System.Globalization.CultureInfo.InvariantCulture));
cell.DataType = new EnumValue<CellValues>() { Value = CellValues.Number };
}
}
else { throw new NotSupportedException("can't handle: " + c.DataType.ToString() + " yet"); }
}
row++;
}
ws.Worksheet.Save();
}
public int PageHeight { get; set; } = 792;
public int PageWidth { get; set; } = 612;
public int LeftMargin { get; set; } = 72;
public int RightMargin { get; set; } = 72;
const int TopMargin = 72;
const int BottomMargin = 72;
public int RowsPerPage { get; set; } = 45;
public int DataRowsPerPage { get; set; } = 36;
// A - I
public int ColsPerPage { get; set; } = 9;
public int RowSize => (PageHeight - (TopMargin + BottomMargin)) / RowsPerPage;
public int ColSize => (PageWidth - (LeftMargin + RightMargin)) / ColsPerPage;
public int ColNumber { get; set; } = 0;
public int RowNumber { get; set; } = 0;
public int XPoint => (ColNumber * ColSize) + LeftMargin;
public int YPoint => (RowNumber * RowSize) + TopMargin;
public int FieldSize(int numColumns)
{
return numColumns * ColSize;
}
/// <summary>
/// handles a one worksheet in the workbook
/// A worksheet is a set of pages with a table on each page
/// each table is populated using the datatable passed in
/// </summary>
/// <param name="dt">table to process TableName is the SheetId</param>
/// <param name="shareStringPart"></param>
/// <param name="sp"></param>
protected virtual void ProcessTablePDF(DataTable dt, ref SharedStringTablePart shareStringPart, SpreadsheetDocument sp, DataTable dtStyle,
C1PdfDocument pdf, string sensitivityDisplayFormat)
{
var font = new C1.WPF.Pdf.Font("Segoe UI", 10);
// 10884 - PDF version of diagnostic report not readable
// http://fogbugz/fogbugz/default.asp?10884
var color = System.Windows.Media.Colors.Black;
var size = new Size();
var point = new Point();
var rect = pdf.PageRectangle;
var alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ"; //temp
var rowCol = new string[] { };
var ws = GetWorksheetPartByName(sp, dt.TableName);
if (null == ws) { return; }
//get a list of all pages on this sheet
var pages = GetTablePages(dt.TableName);
//build a list of pages we don't need
//if we have already handled all the rows we need in the sheet, we don't need anymore pages
var toDelete = new List<TablePage>();
var rowsHandledSoFar = 0;
foreach (var page in pages)
{
if (rowsHandledSoFar >= dt.Rows.Count)
{
toDelete.Add(page);
}
else { rowsHandledSoFar += page.RowsHandled; }
}
//delete unnecessary pages
foreach (var page in toDelete)
{
pages.Remove(page);
DeletePage(page, ws.Worksheet);
}
//if there are no rows, delete the whole worksheet ...
if (dt.Rows.Count < 1)
{
DeleteWorksheet(sp, dt.TableName);
return;
}
//fill in each page
var pen = new C1.WPF.Pdf.Pen(((SolidColorBrush)Application.Current.FindResource("Brush_ApplicationContentGroupBorder")).Color, 1);
var dtRow = 0;
for (var pageNum = 0; pageNum < pages.Count; pageNum++)
{
var cell = new Cell();
//set the page footer
var key = $"{dt.TableName}Page{(1 + pageNum)}Footer";
var dn = GetDefinedName(key);
if (dn != null)
{
cell = GetCell(ws.Worksheet, dn.Text);
if (cell != null)
{
font = GetPDFFont(sp, cell);
rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
size = new Size(FieldSize(4), RowSize); //4 is temp
point = new Point(XPoint, YPoint);
rect = new Rect(point, size);
var sf = new StringFormat();
sf.Alignment = HorizontalAlignment.Center;
pdf.DrawString($"{dt.TableName} Page {1 + pageNum}/{pages.Count}", font, color, rect, sf);//Shouldn't be string literal
}
}
var fields = Enum.GetValues(typeof(PageFields)).Cast<PageFields>().ToArray();
var cellString = string.Empty;
foreach (var field in fields)
{
var name = $"{dt.TableName}Page{1 + pageNum}{field.ToString()}";
dn = GetDefinedName(name);
if (dn != null)
{
cell = GetCell(ws.Worksheet, dn.Text);
if (cell != null)
{
font = GetPDFFont(sp, cell);
rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
switch (field)
{
case PageFields.ReportDate:
case PageFields.TestDescription:
case PageFields.TestId:
case PageFields.TestSetupName:
case PageFields.RecordingMode:
//In ChannelCalibration, these are 5
//In TestSummary, these are 12, 14, 12, 12, 12, and 7
//In DASLayout, these are 12
//In DiagnosticResults, these are 10
//So, use the minimum???
size = new Size(FieldSize(5), RowSize); //5 is temp
break;
case PageFields.DASSerial:
case PageFields.DASSPS:
case PageFields.RecordMode:
case PageFields.PreTrigger:
case PageFields.PostTrigger:
case PageFields.TriggerClosure:
case PageFields.StartClosure:
size = new Size(FieldSize(2), RowSize); //2 is temp
break;
default:
break;
}
point = new Point(XPoint, YPoint);
rect = new Rect(point, size);
switch (field)
{
case PageFields.DASSerial:
case PageFields.DASSPS:
case PageFields.RecordMode:
case PageFields.PreTrigger:
case PageFields.PostTrigger:
case PageFields.TriggerClosure:
case PageFields.StartClosure:
pdf.DrawLine(pen, rect.TopLeft, rect.BottomLeft);
pdf.DrawLine(pen, rect.TopRight, rect.BottomRight);
pdf.DrawLine(pen, rect.TopLeft, rect.TopRight);
pdf.DrawLine(pen, rect.BottomLeft, rect.BottomRight);
break;
default:
break;
}
var sf = new StringFormat();
sf.Alignment = HorizontalAlignment.Right;
var alignment = GetCellAlignment(sp, cell);
if ((alignment != null) && (alignment.Horizontal != null))
{
switch (alignment.Horizontal.Value)
{
case HorizontalAlignmentValues.Left:
sf.Alignment = HorizontalAlignment.Left;
break;
}
}
switch (field)
{
case PageFields.ReportDate:
pdf.DrawString(ReportDate.ToLongDateString(), font, color, rect, sf);
break;
case PageFields.TestDescription:
pdf.DrawString(TestDescription, font, color, rect);
break;
case PageFields.TestId:
pdf.DrawString(TestId, font, color, rect);
break;
case PageFields.TestSetupName:
pdf.DrawString(TestSetupName, font, color, rect);
break;
case PageFields.DASSerial:
pdf.DrawString((DASSerial.PadLeft(DASSerial.Length + 1)).PadRight(DASSerial.Length + 1), font, color, rect);
break;
case PageFields.DASSPS:
pdf.DrawString(SampleRate.ToString(), font, color, rect, sf);
break;
case PageFields.RecordMode: //In colored header of Layout
if (RecordingMode != null)
{
pdf.DrawString((RecordingMode.PadLeft(RecordingMode.Length + 1)).PadRight(RecordingMode.Length + 1), font, color, rect);
}
break;
case PageFields.PreTrigger:
pdf.DrawString(string.Format("{0:0.0}", PreTriggerTime).ToString(), font, color, rect, sf);
break;
case PageFields.PostTrigger:
pdf.DrawString(string.Format("{0:0.0}", PostTriggerTime).ToString(), font, color, rect, sf);
break;
case PageFields.TriggerClosure:
pdf.DrawString((TriggerClosure.PadLeft(TriggerClosure.Length + 1)).PadRight(TriggerClosure.Length + 1), font, color, rect);
break;
case PageFields.StartClosure:
pdf.DrawString((StartClosure.PadLeft(StartClosure.Length + 1)).PadRight(StartClosure.Length + 1), font, color, rect);
break;
case PageFields.RecordingMode: //In top of DAS sheet of TestSummary
pdf.DrawString(RecordingMode, font, color, rect);
break;
default:
break;
}
var columnLetter = "A";
switch (field)
{
case PageFields.ReportDate:
case PageFields.TestDescription:
case PageFields.TestId:
case PageFields.TestSetupName:
case PageFields.RecordingMode:
ColNumber = 0;
RowNumber = GetRowNumber(rowCol[2], pageNum);
columnLetter = "A";
cell = GetCell(ws.Worksheet, columnLetter, (uint)RowNumber); //change RowNumber (and others) to uint?
font = GetPDFFont(sp, cell);
size = new Size(FieldSize(4), RowSize); //4 is temp
point = new Point(XPoint, YPoint);
rect = new Rect(point, size);
cellString = GetCellString(dn.Text.Replace(rowCol[1], "A"), ws, shareStringPart);
pdf.DrawString(cellString, font, color, rect);
break;
case PageFields.DASSerial:
case PageFields.DASSPS:
case PageFields.RecordMode:
case PageFields.PreTrigger:
case PageFields.PostTrigger:
case PageFields.TriggerClosure:
case PageFields.StartClosure:
ColNumber = alphabet.IndexOf(rowCol[1]);
RowNumber--;
columnLetter = rowCol[1];
cell = GetCell(ws.Worksheet, columnLetter, (uint)RowNumber); //change RowNumber (and others) to uint?
font = GetPDFFont(sp, cell);
size = new Size(FieldSize(2), RowSize); //4 is temp
point = new Point(XPoint, YPoint);
rect = new Rect(point, size);
pdf.FillRectangle(System.Windows.Media.Color.FromRgb(221, 235, 247), rect);
pdf.DrawLine(pen, rect.TopLeft, rect.BottomLeft);
pdf.DrawLine(pen, rect.TopRight, rect.BottomRight);
pdf.DrawLine(pen, rect.TopLeft, rect.TopRight);
pdf.DrawLine(pen, rect.BottomLeft, rect.BottomRight);
cellString = GetCellString(columnLetter, (uint)RowNumber, ws, shareStringPart);
pdf.DrawString(" " + cellString, font, color, rect);
break;
default:
break;
}
}
}
}
//Add these to the PageFields above?
key = $"{dt.TableName}Page{1 + pageNum}ChannelCalibrationListLabel";
dn = GetDefinedName(key);
if (dn != null)
{
cell = GetCell(ws.Worksheet, dn.Text);
if (cell != null)
{
font = GetPDFFont(sp, cell);
rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
size = new Size(FieldSize(3), RowSize); //3 is temp
point = new Point(XPoint, YPoint);
rect = new Rect(point, size);
cellString = GetCellString(dn.Text, ws, shareStringPart);
pdf.DrawString(" " + cellString, font, color, rect);
}
}
key = $"{dt.TableName}Page{(1 + pageNum)}ChannelsLabel";
dn = GetDefinedName(key);
if (dn != null)
{
cell = GetCell(ws.Worksheet, dn.Text);
if (cell != null)
{
font = GetPDFFont(sp, cell);
rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
size = new Size(FieldSize(2), RowSize); //2 is temp
point = new Point(XPoint, YPoint);
rect = new Rect(point, size);
cellString = GetCellString(dn.Text, ws, shareStringPart);
pdf.DrawString(" " + cellString, font, color, rect);
}
}
key = $"{dt.TableName}Page{1 + pageNum}DASLabel";
dn = GetDefinedName(key);
if (dn != null)
{
cell = GetCell(ws.Worksheet, dn.Text);
if (cell != null)
{
font = GetPDFFont(sp, cell);
rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
size = new Size(FieldSize(2), RowSize); //2 is temp
point = new Point(XPoint, YPoint);
rect = new Rect(point, size);
cellString = GetCellString(dn.Text, ws, shareStringPart);
pdf.DrawString(" " + cellString, font, color, rect);
}
}
key = $"{dt.TableName}Page{1 + pageNum}SensorChannelsLabel";
dn = GetDefinedName(key);
if (dn != null)
{
cell = GetCell(ws.Worksheet, dn.Text);
if (cell != null)
{
font = GetPDFFont(sp, cell);
rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
size = new Size(FieldSize(3), RowSize); //3 is temp
point = new Point(XPoint, YPoint);
rect = new Rect(point, size);
cellString = GetCellString(dn.Text, ws, shareStringPart);
pdf.DrawString(" " + cellString, font, color, rect);
}
}
key = $"{dt.TableName}Page{1 + pageNum}SquibsLabel";
dn = GetDefinedName(key);
if (dn != null)
{
cell = GetCell(ws.Worksheet, dn.Text);
if (cell != null)
{
font = GetPDFFont(sp, cell);
rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
size = new Size(FieldSize(3), RowSize); //3 is temp
point = new Point(XPoint, YPoint);
rect = new Rect(point, size);
cellString = GetCellString(dn.Text, ws, shareStringPart);
pdf.DrawString(" " + cellString, font, color, rect);
}
}
key = $"{dt.TableName}Page{1 + pageNum}SquibChannelsLabel";
dn = GetDefinedName(key);
if (dn != null)
{
cell = GetCell(ws.Worksheet, dn.Text);
if (cell != null)
{
font = GetPDFFont(sp, cell);
rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
size = new Size(FieldSize(3), RowSize); //3 is temp
point = new Point(XPoint, YPoint);
rect = new Rect(point, size);
cellString = GetCellString(dn.Text, ws, shareStringPart);
pdf.DrawString(" " + cellString, font, color, rect);
}
}
//Column headers
key = $"{dt.TableName}Table{1 + pageNum}Start";
dn = GetDefinedName(key);
if (dn != null)
{
cell = GetCell(ws.Worksheet, dn.Text);
if (cell != null)
{
if (dn.Text.StartsWith("List")) //ChannelCalibrationList
{
DrawChannelCalibrationHeaderFields(sp, cell, dn, alphabet, pageNum, pdf, pen, ws, shareStringPart, color);
}
else if (dn.Text.StartsWith("Layout")) //Layout
{
DrawLayoutHeaderFields(sp, cell, dn, alphabet, pageNum, pdf, pen, ws, shareStringPart, color);
}
else if (dn.Text.StartsWith("DAS") && OutputFilename.Contains("TestSummary")) //TestSummary
{
DrawDASHeaderFields(sp, cell, dn, alphabet, pageNum, pdf, pen, ws, shareStringPart, color);
}
else if (dn.Text.StartsWith("Sensor_Channels")) //TestSummary
{
DrawSensor_ChannelsHeaderFields(sp, cell, dn, alphabet, pageNum, pdf, pen, ws, shareStringPart, color);
}
else if (dn.Text.StartsWith("Digital_Channels")) //TestSummary
{
DrawDigital_ChannelsHeaderFields(sp, cell, dn, alphabet, pageNum, pdf, pen, ws, shareStringPart, color);
}
else if (dn.Text.StartsWith("Squib_Channels")) //TestSummary
{
DrawSquib_ChannelsHeaderFields(sp, cell, dn, alphabet, pageNum, pdf, pen, ws, shareStringPart, color);
}
else if (dn.Text.StartsWith("Calculated_Channels")) //TestSummary
{
DrawCalculated_ChannelsHeaderFields(sp, cell, dn, alphabet, pageNum, pdf, pen, ws, shareStringPart, color);
}
else if ((dn.Text.StartsWith("DAS")) && (OutputFilename.Contains("Diagnostics"))) //Diagnostics
{
DrawDiagnosticsDASHeaderFields(sp, cell, dn, alphabet, pageNum, pdf, pen, ws, shareStringPart, color);
}
else if (dn.Text.StartsWith("Analog")) //Diagnostics
{
DrawAnalogHeaderFields(sp, cell, dn, alphabet, pageNum, pdf, pen, ws, shareStringPart, color);
}
else if (dn.Text.StartsWith("Squibs")) //Diagnostics
{
DrawSquibsHeaderFields(sp, cell, dn, alphabet, pageNum, pdf, pen, ws, shareStringPart, color);
}
}
}
//now we get to fill in the table(s)
var pageDataRow = 1;
while (pageDataRow <= DataRowsPerPage)
{
foreach (DataColumn c in dt.Columns)
{
if ((!(((dt.TableName == "Sensor_Channels") || (dt.TableName == "Squib_Channels")) && (c.ColumnName == "Module"))) &&
(c.ColumnName != "LTrig")) //These are currently hidden
{
var name = $"{dt.TableName}{c.ColumnName}{dtRow + 1}";
dn = GetDefinedName(name);
if (null == dn) { continue; } //user isn't required to have every column in their template if they don't want
// if we don't find the cell, we go on our merry way
var o = new object();
if (dtRow >= dt.Rows.Count)
{
o = string.Empty; //Add an empty cell in an empty row
}
else
{
o = dt.Rows[dtRow][c.ColumnName];
cell = GetCell(ws.Worksheet, dn.Text);
if (null == cell) { continue; }
if (c.DataType == typeof(double))
{
double d = Convert.ToDouble(o);
if (double.IsNaN(d))
{
o = "---";
}
else
{
switch (c.ColumnName)
{
case "Exc":
case "Shunt":
case "Noise":
case "Delay":
case "Duration":
case "Resistance":
o = string.Format("{0:0.00}", d).ToString(System.Globalization.CultureInfo.CurrentCulture);
break;
case "Offset":
case "Range":
case "SPS":
case "AAF":
case "Voltage":
case "Volt":
o = string.Format("{0:0}", d).ToString(System.Globalization.CultureInfo.CurrentCulture);
break;
case "Sens":
o = d.ToString(sensitivityDisplayFormat);
break;
default:
o = string.Format("{0:0.0}", d).ToString(System.Globalization.CultureInfo.CurrentCulture);
break;
}
}
}
else if (c.DataType == typeof(DateTime))
{
var date = Convert.ToDateTime(o);
if (date.ToShortDateString() == "1/1/0001")
{
o = string.Empty;
}
else
{
o = date.ToShortDateString();
}
}
font = GetPDFFont(sp, cell);
}
rowCol = GetRowCol(dn.Text);
ColNumber = alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A');
RowNumber = GetRowNumber(rowCol[2], pageNum);
if (name.Contains("Date"))
{
o = string.Format("{0:M/d/yyyy}", o);
}
if (name.StartsWith("ListNextCal") ||
name.StartsWith("LayoutDCH") ||
name.StartsWith("LayoutModule") ||
name.StartsWith("LayoutChannel") ||
name.StartsWith("LayoutRange") ||
(name.StartsWith("LayoutSens") &&
(!name.StartsWith("LayoutSensor"))) ||
name.StartsWith("LayoutFilter") ||
name.StartsWith("LayoutVolt") ||
name.StartsWith("LayoutTrigger") ||
name.StartsWith("DASAAF") ||
name.StartsWith("DASPreTrigger") ||
name.StartsWith("DASPostTrigger") ||
name.StartsWith("DASChannels") ||
name.StartsWith("Sensor_ChannelsAbsChanNum") ||
name.StartsWith("Sensor_ChannelsChannel") ||
name.StartsWith("Sensor_ChannelsEID") ||
name.StartsWith("Sensor_ChannelsEU") ||
name.StartsWith("Sensor_ChannelsRange") ||
(name.StartsWith("Sensor_ChannelsSens") &&
(!name.StartsWith("Sensor_ChannelsSensorSN"))) ||
name.StartsWith("Sensor_ChannelsPropToExc") ||
name.StartsWith("Sensor_ChannelsPolarity") ||
name.StartsWith("Sensor_ChannelsVoltage") ||
name.StartsWith("Sensor_ChannelsFilter") ||
name.StartsWith("Squib_ChannelsAbsChanNum") ||
name.StartsWith("Squib_ChannelsChannel") ||
name.StartsWith("Squib_ChannelsSensorSN") ||
name.StartsWith("Squib_ChannelsEID") ||
name.StartsWith("Squib_ChannelsDelay") ||
name.StartsWith("Squib_ChannelsDuration") ||
name.StartsWith("DASDAS") ||
name.StartsWith("AnalogExportNum") ||
name.StartsWith("AnalogDAS") ||
name.StartsWith("AnalogModule") ||
name.StartsWith("AnalogChannel") ||
name.StartsWith("AnalogObject") ||
name.StartsWith("AnalogExc") ||
name.StartsWith("AnalogOffset") ||
name.StartsWith("AnalogShunt") ||
name.StartsWith("AnalogRange") ||
name.StartsWith("AnalogNoise") ||
name.StartsWith("SquibsDAS") ||
name.StartsWith("SquibsModule") ||
name.StartsWith("SquibsChannel") ||
name.StartsWith("SquibsDelay"))
{
size = new Size(FieldSize(1), RowSize);
}
else if (name.StartsWith("DASConnection") ||
name.StartsWith("Squib_ChannelsDescription") ||
name.StartsWith("Squib_ChannelsSquibFireMode"))
{
size = new Size(FieldSize(3), RowSize);
}
else
{
size = new Size(FieldSize(2), RowSize);
}
point = new Point(XPoint, YPoint);
rect = new Rect(point, size);
//If text will not fit in field, decrease the font size
var textSize = pdf.MeasureString(" " + o, font);
var savedFont = font;
while ((textSize.Width > size.Width) && (font.Size > 7.0))
{
var smallerFontSize = font.Size;
smallerFontSize--;
font = new C1.WPF.Pdf.Font(font.Name, smallerFontSize, savedFont.Style);
textSize = pdf.MeasureString(" " + o, font);
}
while (textSize.Width > size.Width)
{
o = (o as string).Remove((o as string).Length - 1);
textSize = pdf.MeasureString(" " + o, font);
}
var alternatingRow = -1;
if (OutputFilename.Contains("ChannelCalibration"))
{
Math.DivRem(RowNumber, 4, out alternatingRow);
if ((alternatingRow == 1) || (alternatingRow == 2))
{
pdf.FillRectangle(System.Windows.Media.Color.FromRgb(242, 242, 242), rect);
}
if ((alternatingRow == 2) || (alternatingRow == 0))
{
pdf.DrawLine(pen, rect.BottomLeft, rect.BottomRight);
}
}
else
{
Math.DivRem(pageDataRow, 2, out alternatingRow);
if (alternatingRow == 0)
{
pdf.FillRectangle(System.Windows.Media.Color.FromRgb(242, 242, 242), rect);
}
pdf.DrawLine(pen, rect.TopLeft, rect.TopRight);
pdf.DrawLine(pen, rect.BottomLeft, rect.BottomRight);
pdf.DrawLine(pen, rect.TopLeft, rect.BottomLeft);
pdf.DrawLine(pen, rect.TopRight, rect.BottomRight);
}
if ((null != dtStyle) && (dtRow < dtStyle.Rows.Count))
{
if (null != dtStyle.Rows[dtRow][c.ColumnName])
{
if (!string.IsNullOrWhiteSpace((o as string)))
{
var istyle = Convert.ToInt32(dtStyle.Rows[dtRow][c.ColumnName]);
var fs = (FillStyles)istyle;
if (fs == FillStyles.Fail)
{
pdf.FillRectangle(System.Windows.Media.Color.FromRgb(255, 204, 204), rect);
}
else if (fs == FillStyles.Pass)
{
pdf.FillRectangle(System.Windows.Media.Color.FromRgb(126, 251, 106), rect);
}
else if (fs == FillStyles.Warn)
{
pdf.FillRectangle(System.Windows.Media.Color.FromRgb(100, 149, 237), rect);
}
}
}
}
pdf.DrawString(" " + o, font, color, rect);
font = savedFont;
}
}
dtRow++;
pageDataRow++;
if ((pageDataRow > DataRowsPerPage) && (dtRow < dt.Rows.Count))
{
pdf.NewPage();
}
}
}
}
private void DrawHeaderField(int numColumns, int numRows, string columnLetter, C1PdfDocument pdf, C1.WPF.Pdf.Pen pen, WorksheetPart ws,
SharedStringTablePart shareStringPart, C1.WPF.Pdf.Font font, System.Windows.Media.Color color)
{
var size = new Size(FieldSize(numColumns), RowSize * numRows); //1 is temp
var point = new Point(XPoint, YPoint);
var rect = new Rect(point, size);
pdf.FillRectangle(System.Windows.Media.Color.FromRgb(221, 235, 247), rect);
pdf.DrawLine(pen, rect.TopLeft, rect.BottomLeft);
pdf.DrawLine(pen, rect.TopRight, rect.BottomRight);
pdf.DrawLine(pen, rect.TopLeft, rect.TopRight);
pdf.DrawLine(pen, rect.BottomLeft, rect.BottomRight);
var cellString = GetCellString(columnLetter, (uint)RowNumber, ws, shareStringPart);
var sf = new StringFormat();
sf.LineAlignment = VerticalAlignment.Bottom;
pdf.DrawString(" " + cellString, font, color, rect, sf);
}
private void DrawChannelCalibrationHeaderFields(SpreadsheetDocument sp, Cell cell, DefinedName dn, string alphabet, int pageNum, C1PdfDocument pdf,
C1.WPF.Pdf.Pen pen, WorksheetPart ws, SharedStringTablePart shareStringPart, System.Windows.Media.Color color)
{
// ("Location | Sensor Axis | Serial No | Cal | Next Cal")
var font = GetPDFFont(sp, cell);
var rowCol = GetRowCol(dn.Text);
ColNumber = alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A');
RowNumber = GetRowNumber(rowCol[2], pageNum);
DrawHeaderField(2, 1, "A", pdf, pen, ws, shareStringPart, font, color); //Location
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "C", pdf, pen, ws, shareStringPart, font, color); //Sensor Axis
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "E", pdf, pen, ws, shareStringPart, font, color); //Serial No
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "G", pdf, pen, ws, shareStringPart, font, color); //Cal
ColNumber++;
ColNumber++;
DrawHeaderField(1, 1, "I", pdf, pen, ws, shareStringPart, font, color); //Next Cal
}
private void DrawLayoutHeaderFields(SpreadsheetDocument sp, Cell cell, DefinedName dn, string alphabet, int pageNum, C1PdfDocument pdf,
C1.WPF.Pdf.Pen pen, WorksheetPart ws, SharedStringTablePart shareStringPart, System.Windows.Media.Color color)
{
var font = GetPDFFont(sp, cell);
var rowCol = GetRowCol(dn.Text);
ColNumber = alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A');
RowNumber = GetRowNumber(rowCol[2], pageNum);
RowNumber--;
DrawHeaderField(1, 2, "A", pdf, pen, ws, shareStringPart, font, color); //DCH
ColNumber++;
DrawHeaderField(1, 2, "B", pdf, pen, ws, shareStringPart, font, color); //Module
ColNumber++;
DrawHeaderField(1, 2, "C", pdf, pen, ws, shareStringPart, font, color); //Channel
ColNumber++;
DrawHeaderField(2, 2, "D", pdf, pen, ws, shareStringPart, font, color); //Description
ColNumber++;
ColNumber++;
DrawHeaderField(2, 2, "F", pdf, pen, ws, shareStringPart, font, color); //Sensor S/N
ColNumber++;
ColNumber++;
DrawHeaderField(2, 2, "H", pdf, pen, ws, shareStringPart, font, color); //ISOCode
ColNumber++;
ColNumber++;
DrawHeaderField(1, 1, "J", pdf, pen, ws, shareStringPart, font, color); //Range
ColNumber++;
DrawHeaderField(1, 1, "K", pdf, pen, ws, shareStringPart, font, color); //Sens
ColNumber--;
RowNumber++;
DrawHeaderField(2, 1, "J", pdf, pen, ws, shareStringPart, font, color); //Squib Fire Mode
ColNumber++;
ColNumber++;
RowNumber--;
DrawHeaderField(1, 1, "L", pdf, pen, ws, shareStringPart, font, color); //Filter
RowNumber++;
DrawHeaderField(1, 1, "L", pdf, pen, ws, shareStringPart, font, color); //Delay
ColNumber++;
RowNumber--;
DrawHeaderField(1, 1, "M", pdf, pen, ws, shareStringPart, font, color); //Volt
ColNumber++;
DrawHeaderField(1, 1, "N", pdf, pen, ws, shareStringPart, font, color); //L-Trigger
ColNumber--;
RowNumber++;
DrawHeaderField(2, 1, "M", pdf, pen, ws, shareStringPart, font, color); //Duration
}
private void DrawDiagnosticsDASHeaderFields(SpreadsheetDocument sp, Cell cell, DefinedName dn, string alphabet, int pageNum, C1PdfDocument pdf,
C1.WPF.Pdf.Pen pen, WorksheetPart ws, SharedStringTablePart shareStringPart, System.Windows.Media.Color color)
{
var font = GetPDFFont(sp, cell);
var rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
DrawHeaderField(1, 1, "A", pdf, pen, ws, shareStringPart, font, color); //DAS S/N
ColNumber++;
DrawHeaderField(2, 1, "B", pdf, pen, ws, shareStringPart, font, color); //Firmware
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "D", pdf, pen, ws, shareStringPart, font, color); //Cal Date
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "F", pdf, pen, ws, shareStringPart, font, color); //Due Date
ColNumber++;
ColNumber++;
DrawHeaderField(3, 1, "H", pdf, pen, ws, shareStringPart, font, color); //Connection
ColNumber++;
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "K", pdf, pen, ws, shareStringPart, font, color); //Input (V)
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "M", pdf, pen, ws, shareStringPart, font, color); //Battery (V)
}
private void DrawDASHeaderFields(SpreadsheetDocument sp, Cell cell, DefinedName dn, string alphabet, int pageNum, C1PdfDocument pdf,
C1.WPF.Pdf.Pen pen, WorksheetPart ws, SharedStringTablePart shareStringPart, System.Windows.Media.Color color)
{
var font = GetPDFFont(sp, cell);
var rowCol = GetRowCol(dn.Text);
ColNumber = alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A');
RowNumber = GetRowNumber(rowCol[2], pageNum);
DrawHeaderField(2, 1, "A", pdf, pen, ws, shareStringPart, font, color); //DAS S/N
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "C", pdf, pen, ws, shareStringPart, font, color); //Firmware
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "E", pdf, pen, ws, shareStringPart, font, color); //Cal Date
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "G", pdf, pen, ws, shareStringPart, font, color); //Due Date
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "I", pdf, pen, ws, shareStringPart, font, color); //SPS
ColNumber++;
ColNumber++;
DrawHeaderField(1, 1, "K", pdf, pen, ws, shareStringPart, font, color); //AAF
ColNumber++;
DrawHeaderField(1, 1, "L", pdf, pen, ws, shareStringPart, font, color); //Pre Trig
ColNumber++;
DrawHeaderField(1, 1, "M", pdf, pen, ws, shareStringPart, font, color); //Post Trig
ColNumber++;
DrawHeaderField(1, 1, "N", pdf, pen, ws, shareStringPart, font, color); //Channels
}
private void DrawSensor_ChannelsHeaderFields(SpreadsheetDocument sp, Cell cell, DefinedName dn, string alphabet, int pageNum, C1PdfDocument pdf,
C1.WPF.Pdf.Pen pen, WorksheetPart ws, SharedStringTablePart shareStringPart, System.Windows.Media.Color color)
{
// ("# |DAS/Rack |Channel |Sensor S/N |EID |Description |ISOCode |EU |Range (CAC) |Sensitivity |Prop Exc |Cal Due Date |Polarity |Volt |Filter ")
// Module and Ltrig are hidden for now
var font = GetPDFFont(sp, cell);
var rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
DrawHeaderField(1, 1, "A", pdf, pen, ws, shareStringPart, font, color); //#
ColNumber++;
DrawHeaderField(2, 1, "B", pdf, pen, ws, shareStringPart, font, color); //DAS/Rack
ColNumber++;
ColNumber++;
DrawHeaderField(1, 1, "D", pdf, pen, ws, shareStringPart, font, color); //Channel
ColNumber++;
DrawHeaderField(2, 1, "E", pdf, pen, ws, shareStringPart, font, color); //Sensor S/N
ColNumber++;
ColNumber++;
DrawHeaderField(1, 1, "G", pdf, pen, ws, shareStringPart, font, color); //EID
ColNumber++;
DrawHeaderField(2, 1, "H", pdf, pen, ws, shareStringPart, font, color); //Description
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "J", pdf, pen, ws, shareStringPart, font, color); //ISOCode
ColNumber++;
ColNumber++;
DrawHeaderField(1, 1, "L", pdf, pen, ws, shareStringPart, font, color); //EU
ColNumber++;
DrawHeaderField(1, 1, "M", pdf, pen, ws, shareStringPart, font, color); //Range (CAC)
ColNumber++;
DrawHeaderField(1, 1, "N", pdf, pen, ws, shareStringPart, font, color); //Sensitivity
ColNumber++;
DrawHeaderField(1, 1, "O", pdf, pen, ws, shareStringPart, font, color); //Prop Exc
ColNumber++;
DrawHeaderField(2, 1, "P", pdf, pen, ws, shareStringPart, font, color); //Cal Due Date
ColNumber++;
ColNumber++;
DrawHeaderField(1, 1, "R", pdf, pen, ws, shareStringPart, font, color); //Polarity
ColNumber++;
DrawHeaderField(1, 1, "S", pdf, pen, ws, shareStringPart, font, color); //Volt
ColNumber++;
DrawHeaderField(1, 1, "T", pdf, pen, ws, shareStringPart, font, color); //Filter
}
private void DrawDigital_ChannelsHeaderFields(SpreadsheetDocument sp, Cell cell, DefinedName dn, string alphabet, int pageNum, C1PdfDocument pdf,
C1.WPF.Pdf.Pen pen, WorksheetPart ws, SharedStringTablePart shareStringPart, System.Windows.Media.Color color)
{
// ("# |DAS |Module |Channel |Description |ISOCode |Scale |INV ")
var font = GetPDFFont(sp, cell);
var rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
DrawHeaderField(1, 1, "A", pdf, pen, ws, shareStringPart, font, color); //#
ColNumber++;
DrawHeaderField(1, 1, "B", pdf, pen, ws, shareStringPart, font, color); //DAS
ColNumber++;
DrawHeaderField(1, 1, "C", pdf, pen, ws, shareStringPart, font, color); //Module
ColNumber++;
DrawHeaderField(1, 1, "D", pdf, pen, ws, shareStringPart, font, color); //Channel
ColNumber++;
DrawHeaderField(3, 1, "E", pdf, pen, ws, shareStringPart, font, color); //Description
ColNumber++;
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "H", pdf, pen, ws, shareStringPart, font, color); //ISOCode
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "H", pdf, pen, ws, shareStringPart, font, color); //Scale
ColNumber++;
ColNumber++;
DrawHeaderField(3, 1, "H", pdf, pen, ws, shareStringPart, font, color); //INV
}
private void DrawSquib_ChannelsHeaderFields(SpreadsheetDocument sp, Cell cell, DefinedName dn, string alphabet, int pageNum, C1PdfDocument pdf,
C1.WPF.Pdf.Pen pen, WorksheetPart ws, SharedStringTablePart shareStringPart, System.Windows.Media.Color color)
{
// ("# |DAS/Rack |Channel |Name |EID |Description |ISOCode |Delay |Duration |Squib Fire Mode ")
var font = GetPDFFont(sp, cell);
var rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
DrawHeaderField(1, 1, "A", pdf, pen, ws, shareStringPart, font, color); //#
ColNumber++;
DrawHeaderField(2, 1, "B", pdf, pen, ws, shareStringPart, font, color); //DAS/Rack
ColNumber++;
//DrawHeaderField(1, 1, "C", pdf, pen, ws, shareStringPart, font, color); //Module
ColNumber++;
DrawHeaderField(1, 1, "D", pdf, pen, ws, shareStringPart, font, color); //Channel
ColNumber++;
DrawHeaderField(1, 1, "E", pdf, pen, ws, shareStringPart, font, color); //Name
ColNumber++;
DrawHeaderField(1, 1, "F", pdf, pen, ws, shareStringPart, font, color); //EID
ColNumber++;
DrawHeaderField(3, 1, "G", pdf, pen, ws, shareStringPart, font, color); //Description
ColNumber++;
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "J", pdf, pen, ws, shareStringPart, font, color); //ISOCode
ColNumber++;
ColNumber++;
DrawHeaderField(1, 1, "L", pdf, pen, ws, shareStringPart, font, color); //Delay
ColNumber++;
DrawHeaderField(1, 1, "M", pdf, pen, ws, shareStringPart, font, color); //Duration
ColNumber++;
DrawHeaderField(3, 1, "N", pdf, pen, ws, shareStringPart, font, color); //Squib Fire Mode
}
private void DrawCalculated_ChannelsHeaderFields(SpreadsheetDocument sp, Cell cell, DefinedName dn, string alphabet, int pageNum, C1PdfDocument pdf,
C1.WPF.Pdf.Pen pen, WorksheetPart ws, SharedStringTablePart shareStringPart, System.Windows.Media.Color color)
{
// ("# |Description |ISOCode |Type |Val1 |Val2 |Val3 |Units |Input Channels ")
var font = GetPDFFont(sp, cell);
var rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
DrawHeaderField(1, 1, "A", pdf, pen, ws, shareStringPart, font, color); //#
ColNumber++;
DrawHeaderField(3, 1, "B", pdf, pen, ws, shareStringPart, font, color); //Description
ColNumber++;
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "E", pdf, pen, ws, shareStringPart, font, color); //ISOCode
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "G", pdf, pen, ws, shareStringPart, font, color); //Type
ColNumber++;
ColNumber++;
DrawHeaderField(1, 1, "I", pdf, pen, ws, shareStringPart, font, color); //Val1
ColNumber++;
DrawHeaderField(1, 1, "J", pdf, pen, ws, shareStringPart, font, color); //Val2
ColNumber++;
DrawHeaderField(1, 1, "K", pdf, pen, ws, shareStringPart, font, color); //Val3
ColNumber++;
DrawHeaderField(1, 1, "L", pdf, pen, ws, shareStringPart, font, color); //Units
ColNumber++;
DrawHeaderField(2, 1, "M", pdf, pen, ws, shareStringPart, font, color); //Input Channels
}
private void DrawAnalogHeaderFields(SpreadsheetDocument sp, Cell cell, DefinedName dn, string alphabet, int pageNum, C1PdfDocument pdf,
C1.WPF.Pdf.Pen pen, WorksheetPart ws, SharedStringTablePart shareStringPart, System.Windows.Media.Color color)
{
// ("# |DAS |Module |Channel |Group |Sens S/N |Description |Exc |Offset |Shunt |Range |Noise ")
var font = GetPDFFont(sp, cell);
var rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
DrawHeaderField(1, 1, "A", pdf, pen, ws, shareStringPart, font, color); //#
ColNumber++;
DrawHeaderField(1, 1, "B", pdf, pen, ws, shareStringPart, font, color); //DAS
ColNumber++;
DrawHeaderField(1, 1, "C", pdf, pen, ws, shareStringPart, font, color); //Module
ColNumber++;
DrawHeaderField(1, 1, "D", pdf, pen, ws, shareStringPart, font, color); //Channel
ColNumber++;
DrawHeaderField(1, 1, "E", pdf, pen, ws, shareStringPart, font, color); //Group
ColNumber++;
DrawHeaderField(2, 1, "F", pdf, pen, ws, shareStringPart, font, color); //Sens S/N
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "H", pdf, pen, ws, shareStringPart, font, color); //Description
ColNumber++;
ColNumber++;
DrawHeaderField(1, 1, "J", pdf, pen, ws, shareStringPart, font, color); //Exc
ColNumber++;
DrawHeaderField(1, 1, "K", pdf, pen, ws, shareStringPart, font, color); //Offset
ColNumber++;
DrawHeaderField(1, 1, "L", pdf, pen, ws, shareStringPart, font, color); //Shunt
ColNumber++;
DrawHeaderField(1, 1, "M", pdf, pen, ws, shareStringPart, font, color); //Range
ColNumber++;
DrawHeaderField(1, 1, "N", pdf, pen, ws, shareStringPart, font, color); //Noise
}
private void DrawSquibsHeaderFields(SpreadsheetDocument sp, Cell cell, DefinedName dn, string alphabet, int pageNum, C1PdfDocument pdf,
C1.WPF.Pdf.Pen pen, WorksheetPart ws, SharedStringTablePart shareStringPart, System.Windows.Media.Color color)
{
// ("DAS |Module |Channel |Description |ISOCode |Fire Mode |Delay |Duration ")
var font = GetPDFFont(sp, cell);
var rowCol = GetRowCol(dn.Text);
ColNumber = (alphabet.IndexOf(rowCol[1]) - alphabet.IndexOf('A'));
RowNumber = GetRowNumber(rowCol[2], pageNum);
DrawHeaderField(1, 1, "A", pdf, pen, ws, shareStringPart, font, color); //DAS
ColNumber++;
DrawHeaderField(1, 1, "B", pdf, pen, ws, shareStringPart, font, color); //Module
ColNumber++;
DrawHeaderField(1, 1, "C", pdf, pen, ws, shareStringPart, font, color); //Channel
ColNumber++;
DrawHeaderField(2, 1, "D", pdf, pen, ws, shareStringPart, font, color); //Description
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "F", pdf, pen, ws, shareStringPart, font, color); //ISOCode
ColNumber++;
ColNumber++;
DrawHeaderField(2, 1, "H", pdf, pen, ws, shareStringPart, font, color); //Fire Mode
ColNumber++;
ColNumber++;
DrawHeaderField(1, 1, "J", pdf, pen, ws, shareStringPart, font, color); //Delay
ColNumber++;
DrawHeaderField(2, 1, "K", pdf, pen, ws, shareStringPart, font, color); //Duration
}
public string GetCellString(string dnText, WorksheetPart ws, SharedStringTablePart shareStringPart)
{
var cellValue = GetCellValue(ws.Worksheet, dnText);
if (cellValue == null)
{
return null;
}
int.TryParse(cellValue.Text, out int cellValueInt);
var sharedStringItems = shareStringPart.SharedStringTable.Descendants<SharedStringItem>();
var sharedStringItemsArray = sharedStringItems.ToArray();
return sharedStringItemsArray[cellValueInt].Text.Text.ToString();
}
private string GetCellString(string columnLetter, uint rowNumber, WorksheetPart ws, SharedStringTablePart shareStringPart)
{
var cellValue = GetCellValue(ws.Worksheet, columnLetter, rowNumber);
if (cellValue == null)
{
return null;
}
int.TryParse(cellValue.Text, out int cellValueInt);
var sharedStringItems = shareStringPart.SharedStringTable.Descendants<SharedStringItem>();
var sharedStringItemsArray = sharedStringItems.ToArray();
return sharedStringItemsArray[cellValueInt].Text.Text.ToString();
}
private Alignment GetCellAlignment(SpreadsheetDocument sp, Cell cell)
{
var styles = sp.WorkbookPart.WorkbookStylesPart;
var cellStyleIndex = (int)cell.StyleIndex.Value;
var cellFormat = (CellFormat)styles.Stylesheet.CellFormats.ChildElements[cellStyleIndex];
return cellFormat.Alignment;
}
public C1.WPF.Pdf.Font GetPDFFont(SpreadsheetDocument sp, Cell cell)
{
var pdfFontStyle = PdfFontStyle.Regular;
var cellFont = GetCellFont(sp, cell);
var cellFontName = cellFont.FontName.Val;
var cellFontSize = cellFont.FontSize.Val;
if (cellFont.Bold != null)
{
pdfFontStyle |= PdfFontStyle.Bold;
}
if (cellFont.Italic != null)
{
pdfFontStyle |= PdfFontStyle.Italic;
}
if (cellFont.Strike != null)
{
pdfFontStyle |= PdfFontStyle.Strikeout;
}
if (cellFont.Underline != null)
{
pdfFontStyle |= PdfFontStyle.Underline;
}
return new C1.WPF.Pdf.Font(cellFontName, cellFontSize, pdfFontStyle);
}
private DocumentFormat.OpenXml.Spreadsheet.Font GetCellFont(SpreadsheetDocument sp, Cell cell)
{
var f = new DocumentFormat.OpenXml.Spreadsheet.Font(); //.Bold b = new Bold();
var styles = sp.WorkbookPart.WorkbookStylesPart;
DocumentFormat.OpenXml.Spreadsheet.Fonts fonts = styles.Stylesheet.Fonts;
var cellStyleIndex = (int)cell.StyleIndex.Value;
var cellFormat = (CellFormat)styles.Stylesheet.CellFormats.ChildElements[cellStyleIndex];
return (DocumentFormat.OpenXml.Spreadsheet.Font)fonts.ChildElements[int.Parse(cellFormat.FontId.ToString())];
}
public int GetRowNumber(string rowCol, int pageNum)
{
int.TryParse(rowCol, out int rowNumber);
return rowNumber - (RowsPerPage * pageNum); //pageNum is 0-relative
}
/// <summary>
/// creates a new cellformat, returns the index of the format
/// </summary>
/// <param name="styleSheet"></param>
/// <param name="fontIndex"></param>
/// <param name="fillIndex"></param>
/// <param name="numberFormatId"></param>
/// <returns></returns>
public UInt32Value createCellFormat(
Stylesheet styleSheet,
UInt32Value fontIndex,
UInt32Value fillIndex,
UInt32Value numberFormatId)
{
var cellFormat = new CellFormat();
if (fontIndex != null)
cellFormat.FontId = fontIndex;
if (fillIndex != null)
cellFormat.FillId = fillIndex;
if (numberFormatId != null)
{
cellFormat.NumberFormatId = numberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
}
styleSheet.CellFormats.Append(cellFormat);
var result = styleSheet.CellFormats.Count;
styleSheet.CellFormats.Count++;
return result;
}
/// <summary>
/// creates a new fill for a style
/// </summary>
/// <param name="styleSheet"></param>
/// <param name="fillColor"></param>
/// <returns></returns>
public UInt32Value createFill(
Stylesheet styleSheet,
System.Drawing.Color fillColor)
{
var fill = new Fill(
new PatternFill(
new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value =
System.Drawing.ColorTranslator.ToHtml(
System.Drawing.Color.FromArgb(
fillColor.A,
fillColor.R,
fillColor.G,
fillColor.B)).Replace("#", "")
}
})
{
PatternType = PatternValues.Solid
}
);
styleSheet.Fills.Append(fill);
UInt32Value result = styleSheet.Fills.Count;
styleSheet.Fills.Count++;
return result;
}
/// <summary>
/// delete a page in the worksheet
/// this involves deleting all the rows corresponding to the page
/// as well as any merged cells
/// </summary>
/// <param name="page"></param>
/// <param name="ws"></param>
protected virtual void DeletePage(TablePage page, Worksheet ws)
{
for (var i = Convert.ToUInt32(page.PageEnd); i >= Convert.ToUInt32(page.PageStart); i--)
{
GetRow(ws, i).Remove();
var toDelete = new List<MergeCell>();
var mergeCells = ws.Descendants<MergeCell>();
foreach (var m in mergeCells) { if (GetRowIndex(m) == i) { toDelete.Add(m); } }
foreach (var m in toDelete) { m.Remove(); }
}
}
/// <summary>
/// ranges in excel come in the form of A1:D30, here for convenious we store the splitter for efficiency when parsing named ranges
/// </summary>
protected static char[] _rangeSplitter = new char[] { ':' };
/// <summary>
/// gets the row index of a mergecell
/// merge cells are in the form XXYY:JJKK
/// where XX is the column of the upper left corner, YY is the row of the upper left corner,
/// and JJ is the column of the lower right, and KK is the row of the lower right
/// note that columns are in letters while rows are in digits
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
protected virtual int GetRowIndex(MergeCell m)
{
var tokens = m.Reference.Value.Split(_rangeSplitter);
//token [0] shold be in the form AA12, but we don't have a helper class for cellreference?
var numbers = new string((from c in tokens[0] where char.IsDigit(c) select c).ToArray());
return int.Parse(numbers);
}
/// <summary>
/// delete file if one already exists in the output directory
/// we could alternatively time stamp then
/// </summary>
protected virtual void DeleteExistingReport()
{
if (System.IO.File.Exists(OutputFilename)) { System.IO.File.Delete(OutputFilename); }
}
protected virtual void DeleteExistingPDFTemplate()
{
if (System.IO.File.Exists(DestinationTemplateFilename)) { System.IO.File.Delete(DestinationTemplateFilename); }
}
/// <summary>
/// copy our template file to the output directory
/// presumbably the templates are all in a template directory
/// and the output directory is in the test\reports directory
/// </summary>
protected virtual void CopyTemplate()
{
var fi = new System.IO.FileInfo(OutputFilename);
if (!fi.Directory.Exists)
{
System.IO.Directory.CreateDirectory(fi.Directory.FullName);
}
System.IO.File.Copy(TemplateFilename, OutputFilename);
}
protected virtual void CopyExcelTemplateForPDF()
{
System.IO.File.Copy(TemplateFilename, DestinationTemplateFilename);
}
/// <summary>
/// we've got lots of strings to go through, so we build a dictionary of all we know about for efficiency
/// </summary>
private Dictionary<string, int> _stringLookup = null;
// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text
// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
protected int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
if (null == text) { text = ""; }
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
if (null == _stringLookup)
{
var i = 0;
_stringLookup = new Dictionary<string, int>();
foreach (var item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
_stringLookup[item.InnerText] = i++;
}
}
//if the string is already in the table, return it's index, otherwise add a new string to the table and return the new index
if (_stringLookup.ContainsKey(text)) { return _stringLookup[text]; }
else
{
var i = _stringLookup.Count;
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
shareStringPart.SharedStringTable.Save();
_stringLookup[text] = i;
return i;
}
}
/// <summary>
/// gets a worksheet, given a sheet id and a spreadsheet
/// </summary>
/// <param name="document"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
protected WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
{
var sheets =
document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
Elements<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
return null;
}
var relationshipId = sheets.First().Id.Value;
var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
return worksheetPart;
}
/// <summary>
/// gets a cell given a work sheet and a strong cell reference
/// cell reference should be in the form SheetId!$Column$Row,
/// like DefinedNames use
/// </summary>
/// <param name="worksheet"></param>
/// <param name="nameref"></param>
/// <returns></returns>
protected Cell GetCell(Worksheet worksheet, string nameref)
{
var tokens = nameref.Split(new char[] { '!' });
nameref = tokens[1];
nameref = nameref.Replace("$", "");
var theCell = worksheet.Descendants<Cell>().
Where(c => c.CellReference == nameref).FirstOrDefault();
return theCell;
}
protected CellValue GetCellValue(Worksheet worksheet, string nameref)
{
var tokens = nameref.Split(new char[] { '!' });
nameref = tokens[1];
nameref = nameref.Replace("$", "");
var theCell = worksheet.Descendants<Cell>().
Where(c => c.CellReference == nameref).FirstOrDefault();
return theCell.CellValue;
}
protected CellValue GetCellValue(Worksheet worksheet, string columnLetter, uint rowNumber)
{
var nameref = columnLetter + rowNumber.ToString();
var theCell = worksheet.Descendants<Cell>().
Where(c => c.CellReference == nameref).FirstOrDefault();
return theCell.CellValue;
}
protected string[] GetRowCol(string nameref)
{
var tokens = nameref.Split(new char[] { '!' });
nameref = tokens[1];
var rowCol = nameref.Split(new char[] { '$' });
return rowCol;
}
/// <summary>
/// returns a cell, given a worksheet, a column, and a row
/// columns are letters in excel (example AA-ZZ)
/// </summary>
/// <param name="worksheet"></param>
/// <param name="columnName"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
protected Cell GetCell(Worksheet worksheet,
string columnName, uint rowIndex)
{
var row = GetRow(worksheet, rowIndex);
if (row == null)
return null;
return row.Elements<Cell>().Where(c => string.Compare
(c.CellReference.Value, columnName +
rowIndex, true) == 0).First();
}
/// <summary>
/// returns a row, given it's row index
/// </summary>
/// <param name="worksheet"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
private Row GetRow(Worksheet worksheet, uint rowIndex)
{
return worksheet.GetFirstChild<SheetData>().
Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
protected void DeleteWorksheet(SpreadsheetDocument document, string sheetid)
{
var Sheetid = "";
//Open the workbook
//using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart wbPart = document.WorkbookPart;
// Get the pivot Table Parts
var pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts;
var pvtTableCacheDefinationPart = new Dictionary<PivotTableCacheDefinitionPart, string>();
foreach (var Item in pvtTableCacheParts)
{
var pvtCacheDef = Item.PivotCacheDefinition;
//Check if this CacheSource is linked to SheetToDelete
var pvtCahce = pvtCacheDef.Descendants<CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetid);
if (pvtCahce.Count() > 0)
{
pvtTableCacheDefinationPart.Add(Item, Item.ToString());
}
}
foreach (var Item in pvtTableCacheDefinationPart)
{
wbPart.DeletePart(Item.Key);
}
//Get the SheetToDelete from workbook.xml
var theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetid).FirstOrDefault();
if (theSheet == null)
{
// The specified sheet doesn't exist.
}
//Store the SheetID for the reference
Sheetid = theSheet.SheetId;
// Remove the sheet reference from the workbook.
var worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
theSheet.Remove();
// Delete the worksheet part.
wbPart.DeletePart(worksheetPart);
//Get the DefinedNames
var definedNames = wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault();
if (definedNames != null)
{
var defNamesToDelete = new List<DefinedName>();
foreach (DefinedName Item in definedNames)
{
// This condition checks to delete only those names which are part of Sheet in question
if (Item.Text.Contains(sheetid + "!"))
{
//System.Diagnostics.Trace.WriteLine("deleting " + Item.Text);
defNamesToDelete.Add(Item);
}
}
foreach (DefinedName Item in defNamesToDelete)
{
Item.Remove();
}
}
// Get the CalculationChainPart
//Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the
//workbook whose value is calculated from any formula
CalculationChainPart calChainPart;
calChainPart = wbPart.CalculationChainPart;
if (calChainPart != null)
{
var calChainEntries = calChainPart.CalculationChain.Descendants<CalculationCell>().Where(c => c.SheetId == Sheetid);
var calcsToDelete = new List<CalculationCell>();
foreach (var Item in calChainEntries)
{
calcsToDelete.Add(Item);
}
foreach (var Item in calcsToDelete)
{
Item.Remove();
}
if (calChainPart.CalculationChain.Count() == 0)
{
wbPart.DeletePart(calChainPart);
}
}
// Save the workbook.
wbPart.Workbook.Save();
}
}
public static string GetTemplateReportPath()
{
return System.IO.Path.Combine(Environment.CurrentDirectory.Trim(), Common.Constants.REPORT_TEMPLATE_DIR_NAME);
}
public static string GetReportPath(string testDirectory)
{
return System.IO.Path.Combine(testDirectory.Trim(), Common.Constants.REPORT_DIR_NAME);
}
}
}
Reference in New Issue View Git Blame Copy Permalink