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);
        }
    }
}