using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.ComponentModel;
using System.IO;
using C1.WPF.Pdf;
using System.Windows;
using System.Windows.Media;
using DTS.Common.Enums;

namespace DTS.Reports
{
    public class TestPreviewReport : ReportBase
    {
        public TestPreviewReport()
        {

        }
        private enum Tables
        {
            DAS,
            Sensor_Channels,
            Digital_Channels,
            Squib_Channels,
            Calculated_Channels
        }
        private enum StyleTables
        {
            DAS_Styles,
            Sensor_Channels_Styles,
            Digital_Channels_Styles,
            Squib_Channels_Styles,
            Calculated_Channels_Styles
        }
        private const string ISOPageId = "ISO";

        public enum DASFields
        {
            SN,
            FW,
            CalDate,
            DueDate,
            SPS,
            AAF,
            PreTrigger,
            PostTrigger,
            Channels
        }
        public class DASHelper
        {
            public string SerialNumber { get; set; }
            public string Firmware { get; set; }
            public DateTime CalDate { get; set; }
            public DateTime DueDate { get; set; }
            public double SPS { get; set; }
            public double AAF { get; set; }
            public double PreTrigger { get; set; }
            public double PostTrigger { get; set; }
            public string Channels { get; set; }
        }
        public enum SquibFields
        {
            AbsChanNum,
            DAS,
            Module,
            Channel,
            SensorSN,
            EID,
            Description,
            ISO,
            Delay,
            Duration,
            SquibFireMode
        }
        public class SquibHelper : IComparable<SquibHelper>
        {
            public int AbsoluteChannelNumber { get; set; }
            public int DisplayOrder { get; set; }
            public string DAS { get; set; }
            public string Module { get; set; }
            public string Channel { get; set; }
            public string SensorSerialNumber { get; set; }
            public string EID { get; set; }
            public string Description { get; set; }
            public string ISO { get; set; }
            public double Delay { get; set; }
            public double Duration { get; set; }
            public string SquibFireMode { get; set; }
            public int CompareTo(SquibHelper right)
            {
                if (this == right) { return 0; }
                if (DAS == right.DAS)
                {
                    int ret = DisplayOrder.CompareTo(right.DisplayOrder);
                    if (0 == ret) { return Channel.CompareTo(right.Channel); }
                    else { return ret; }
                }
                else
                {
                    if (null == DAS && null == right.DAS)
                    {
                        return AbsoluteChannelNumber.CompareTo(right.AbsoluteChannelNumber);
                    }
                    else if (null == DAS)
                    {
                        return 1;
                    }
                    else if (null == right.DAS) { return -1; }
                    else { return DAS.CompareTo(right.DAS); }
                }
            }
        }
        public enum SensorFields
        {
            AbsChanNum,
            DAS,
            Module,
            Channel,
            SensorSN,
            EID,
            Description,
            ISO,
            EU,
            Range,
            Sens,//sensitivity
            PropToExc,
            CalDueDate,
            Voltage,
            Filter,
            LTrig,
            Polarity
        }
        public class SensorHelper : IComparable<SensorHelper>
        {
            public enum ResultStatus
            {
                OK,
                Overdue,
                Warn
            }
            public int AbsoluteChannelNumber { get; set; }
            public int DisplayOrder { get; set; }
            public string DAS { get; set; }
            public string Module { get; set; }
            public string Channel { get; set; }
            public string SensorSerialNumber { get; set; }
            public string SensorSerialWithAxis { get; set; }
            public string EID { get; set; }
            public string Description { get; set; }
            public string ISO { get; set; }
            public string EU { get; set; }
            public double Range { get; set; }
            public double Sensitivity { get; set; } = 0;
            public bool IsProportional { get; set; }
            public string Polarity { get; set; }
            public string CalDueDate { get; set; }
            public double Voltage { get; set; }
            public string Filter { get; set; }
            public string LevelTrigger { get; set; }
            public ResultStatus CalStatus { get; set; }
            public int CompareTo(SensorHelper right)
            {
                if (this == right) { return 0; }
                if (DAS == right.DAS)
                {
                    int ret = DisplayOrder.CompareTo(right.DisplayOrder);
                    if (0 == ret) { return Channel.CompareTo(right.Channel); }
                    else { return ret; }
                }
                else
                {
                    if (null == DAS && null == right.DAS)
                    {
                        return AbsoluteChannelNumber.CompareTo(right.AbsoluteChannelNumber);
                    }
                    else if (null == DAS)
                    {
                        return 1;
                    }
                    else if (null == right.DAS) { return -1; }
                    else { return DAS.CompareTo(right.DAS); }
                }
            }
        }
        public enum ISOSummaryFields
        {
            ISOSummaryPageTestSetupName,
            ISOSummaryPageTestDescription,
            ISOSummaryPageTestId,
            ISOSummaryPageReportDate,
            ISOLaboratoryName,
            ISOLaboratoryContactName,
            ISOLaboratoryContactPhone,
            ISOLaboratoryContactFax,
            ISOLaboratoryContactEmail,
            ISOLaboratoryTestRefNumber,
            ISOLaboratoryProjectRefNumber,
            ISOCustomerName,
            ISOCustomerTestRefNumber,
            ISOCustomerProjectRefNumber,
            ISOCustomerOrderNumber,
            ISOCustomerCostUnit,
            ISOTestEngineerName,
            ISOTestEngineerPhone,
            ISOTestEngineerFax,
            ISOTestEngineerEmail,
            ISOTitle,
            ISONumberOfMedia,
            ISOTimestamp,
            ISOTypeOfTest,
            ISOSubtypeOfTest,
            ISORegulation,
            ISOReferenceTemperature,
            ISORelativeAirHumidity,
            ISODateOfTest,
            ISONumberOfTestObjects//,
            //ISOSummaryPageFooter
        }
        public class ISOSummaryHelper
        {
            private Dictionary<ISOSummaryFields, string> _values = new Dictionary<ISOSummaryFields, string>();
            public void SetField(ISOSummaryFields field, string value)
            {
                _values[field] = value;
            }
            public string GetField(ISOSummaryFields field)
            {
                if (_values.ContainsKey(field)) { return _values[field]; }
                else { return ""; }
            }
        }
        private const string TestObjectPrepend = "ISOTestObject";
        public enum TestObjectPageFields
        {
            PageStart,
            Footer,
            PageEnd
        }
        public enum TestObjectFields
        {
            TestSetupName,
            TestDescription,
            TestId,
            ReportDate,
            Comment1,
            Comment2,
            Comment3,
            NameOfTestObject,
            VelocityOfTestObject,
            VelocityMeasurementUnit,
            MassOfTestObject,
            DriversPositionOfObject,
            ImpactSideOfTestObject,
            TypeOfTestObject,
            ClassOfTestObject,
            CodeOfTestObject,
            RefNumberOfTestObject,
            Offset,
            BarrierWidth,
            BarrierHeight,
            YawAngle,
            ReferenceSystem,
            OriginX,
            OriginY,
            OriginZ,
            NumberOfLoadCells
        }
        public class TestObjectHelper
        {
            private Dictionary<TestObjectFields, string> _properties = new Dictionary<TestObjectFields, string>();
            public void SetField(TestObjectFields field, string value) { _properties[field] = value; }
            public string GetField(TestObjectFields field)
            {
                if (_properties.ContainsKey(field)) { return _properties[field]; }
                else { return ""; }
            }
        }

        private ISOSummaryHelper _isoSummary;
        private TestObjectHelper[] _testObjects;

        public static string GetReportFilenamePath(string reportPath, string testId)
        {
            var filename = RunTestVariables.OutputXLSMReports ? "Test Summary Preview.xlsm" : "Test Summary Preview.xlsx"; // Template default
            if (false == string.IsNullOrEmpty(testId))
            {
                filename = $"{testId} ";
                if (RunTestVariables.OutputXLSMReports)
                {
                    filename += "TestSummary.xlsm";
                }
                else
                {
                    filename += "TestSummary.xlsx";
                }
            }
            return Path.Combine(reportPath, filename);
        }

        public void DoReport(ISOSummaryHelper summary, SensorHelper[] sensors, TestObjectHelper[] testObjects, DASHelper[] das, SquibHelper[] squibs, string outputFileName,
            string folderNameId, string sensitivityDisplayFormat, bool generateExcelReports, bool generatePDFReports)
        {
            _isoSummary = summary;
            _testObjects = testObjects;

            var tableNames = Enum.GetValues(typeof(Tables)).Cast<Tables>().ToArray();
            var tables = new DataTable[tableNames.Length];
            var styleTables = new DataTable[tableNames.Length];

            foreach (var t in tableNames)
            {
                switch (t)
                {
                    case Tables.DAS:
                        tables[(int)t] = InitializeDASTable();
                        PopulateDASTable(tables[(int)t], das);
                        break;
                    case Tables.Sensor_Channels:
                        styleTables[(int)t] = new DataTable(StyleTables.Sensor_Channels_Styles.ToString());
                        tables[(int)t] = InitializeSensorsTable(styleTables[(int)t]);
                        PopulateSensorsTable(tables[(int)t], sensors, styleTables[(int)t]);
                        break;
                    case Tables.Squib_Channels:
                        styleTables[(int)t] = new DataTable(StyleTables.Squib_Channels_Styles.ToString());
                        tables[(int)t] = InitializeSquibTable(styleTables[(int)t]);
                        PopulateSquibsTable(tables[(int)t], squibs, styleTables[(int)t]);
                        break;
                    case Tables.Digital_Channels:
                        tables[(int)t] = InitializeDigitalTable();
                        break;
                    case Tables.Calculated_Channels:
                        tables[(int)t] = InitializeCalculatedChannels();
                        break;
                    default:
                        break;
                }
            }


            TemplateFilename = GetReportFilenamePath(GetTemplateReportPath(), string.Empty);
            OutputFilename = outputFileName;

            if (File.Exists(OutputFilename))
            {
                ReportFileWillBeOverwritten(outputFileName);
                try
                {
                    File.Delete(OutputFilename);
                }
                catch (Exception)
                {
                    ReportFileInUse(outputFileName);
                }
            }

            if (generateExcelReports)
            {
                OutputReport(tables, styleTables);
                CopyReportIfNeeded(folderNameId, outputFileName);
            }

            if (generatePDFReports)
            {
                //Make a PDF with the same name
                outputFileName = outputFileName.Replace(".xlsx", ".pdf").Replace(".xlsm", ".pdf");

                //Don't overwrite the Excel file that may be the filled-in template.
                DestinationTemplateFilename = OutputFilename.Replace(".xlsx", Common.Constants.TEMP_FILE_EXTENSION).Replace(".xlsm", Common.Constants.TEMP_FILE_EXTENSION);
                OutputFilename = outputFileName;
                OutputReportPDF(tables, styleTables, sensitivityDisplayFormat);
                CopyReportIfNeeded(folderNameId, outputFileName);
            }

            for (var i = 0; i < tables.Length; i++)
            {
                if (null != tables[i]) { tables[i].Dispose(); tables[i] = null; }
            }
        }
        private void PopulateDASTable(DataTable dt, DASHelper[] das)
        {
            var minDate = new DateTime(1970, 1, 1);
            var fields = Enum.GetValues(typeof(DASFields)).Cast<DASFields>().ToArray();

            foreach (var d in das)
            {
                var dr = dt.NewRow();
                foreach (var field in fields)
                {
                    object o = null;
                    switch (field)
                    {
                        case DASFields.SPS:
                            o = d.SPS;
                            break;
                        case DASFields.SN:
                            o = d.SerialNumber;
                            break;
                        case DASFields.PreTrigger:
                            o = d.PreTrigger;
                            break;
                        case DASFields.PostTrigger:
                            o = d.PostTrigger;
                            break;
                        case DASFields.FW:
                            o = d.Firmware;
                            break;
                        case DASFields.DueDate:
                            if (d.CalDate == minDate)
                            {
                                o = "--";
                            }
                            else
                            {
                                o = d.DueDate.ToShortDateString();
                            }
                            break;
                        case DASFields.Channels:
                            o = d.Channels;
                            break;
                        case DASFields.CalDate:
                            if (d.CalDate == minDate)
                            {
                                o = "--";
                            }
                            else
                            {
                                o = d.CalDate.ToShortDateString();
                            }
                            break;
                        case DASFields.AAF:
                            o = d.AAF;
                            break;
                        default:
                            throw new NotSupportedException("unknown field: " + field.ToString());
                    }

                    dr[field.ToString()] = o;
                }
                dt.Rows.Add(dr);
            }
        }
        private void PopulateSquibsTable(DataTable dt, SquibHelper[] squibs, DataTable styleTable)
        {
            var fields = Enum.GetValues(typeof(SquibFields)).Cast<SquibFields>().ToArray();
            foreach (var s in squibs)
            {
                var dr = dt.NewRow();
                var dr2 = styleTable.NewRow();
                foreach (var field in fields)
                {
                    object o = null;
                    dr2[field.ToString()] = -1;
                    switch (field)
                    {
                        case SquibFields.AbsChanNum:
                            o = s.AbsoluteChannelNumber;
                            break;
                        case SquibFields.Channel:
                            o = s.Channel;
                            break;
                        case SquibFields.DAS:
                            o = s.DAS;
                            break;
                        case SquibFields.Delay:
                            o = s.Delay;
                            break;
                        case SquibFields.Description:
                            o = s.Description;
                            break;
                        case SquibFields.Duration:
                            o = s.Duration;
                            break;
                        case SquibFields.EID:
                            o = s.EID;
                            break;
                        case SquibFields.ISO:
                            o = s.ISO;
                            break;
                        case SquibFields.Module:
                            o = s.Module;
                            break;
                        case SquibFields.SensorSN:
                            o = s.SensorSerialNumber;
                            break;
                        case SquibFields.SquibFireMode:
                            o = s.SquibFireMode;
                            break;
                        default:
                            throw new NotSupportedException("unknown field: " + field.ToString());
                    }

                    dr[field.ToString()] = o;
                }
                dt.Rows.Add(dr);
                styleTable.Rows.Add(dr2);
            }
        }
        private void PopulateSensorsTable(DataTable dt, SensorHelper[] sensors, DataTable styleTable)
        {
            var fields = Enum.GetValues(typeof(SensorFields)).Cast<SensorFields>().ToArray();
            foreach (var s in sensors)
            {
                var dr = dt.NewRow();
                var dr2 = styleTable.NewRow();
                foreach (var field in fields)
                {
                    object o = null;
                    dr2[field.ToString()] = -1;
                    switch (field)
                    {
                        case SensorFields.AbsChanNum:
                            o = s.AbsoluteChannelNumber;
                            break;
                        case SensorFields.CalDueDate:
                            if (!(s.CalDueDate == DateTime.MinValue.ToShortDateString()))
                            {
                                o = s.CalDueDate;
                                switch (s.CalStatus)
                                {
                                    case SensorHelper.ResultStatus.OK:
                                        dr2[field.ToString()] = (int)ReportBase.FillStyles.Pass;
                                        break;
                                    case SensorHelper.ResultStatus.Warn:
                                        dr2[field.ToString()] = (int)ReportBase.FillStyles.Warn;
                                        break;
                                    case SensorHelper.ResultStatus.Overdue:
                                        dr2[field.ToString()] = (int)ReportBase.FillStyles.Fail;
                                        break;
                                }
                            }
                            break;
                        case SensorFields.Channel:
                            o = s.Channel;
                            break;
                        case SensorFields.DAS:
                            o = s.DAS;
                            break;
                        case SensorFields.Description:
                            o = s.Description;
                            break;
                        case SensorFields.EID:
                            o = s.EID;
                            break;
                        case SensorFields.EU:
                            o = s.EU;
                            break;
                        case SensorFields.Filter:
                            o = s.Filter;
                            break;
                        case SensorFields.ISO:
                            o = s.ISO;
                            break;
                        case SensorFields.LTrig:
                            o = s.LevelTrigger;
                            break;
                        case SensorFields.Module:
                            o = s.Module;
                            break;
                        case SensorFields.Polarity:
                            o = s.Polarity;
                            break;
                        case SensorFields.PropToExc:
                            o = s.IsProportional ? "x" : " ";
                            break;
                        case SensorFields.Range:
                            o = s.Range;
                            break;
                        case SensorFields.Sens:
                            o = s.Sensitivity.ToString("N8");
                            break;
                        case SensorFields.SensorSN:
                            o = s.SensorSerialWithAxis;
                            break;
                        case SensorFields.Voltage:
                            o = s.Voltage;
                            break;
                        default:
                            throw new NotSupportedException("unknown field: " + field.ToString());
                    }
                    dr[field.ToString()] = o;
                }
                dt.Rows.Add(dr);
                styleTable.Rows.Add(dr2);
            }
        }
        private DataTable InitializeDASTable()
        {
            var dt = new DataTable(Tables.DAS.ToString());
            var fields = Enum.GetValues(typeof(DASFields)).Cast<DASFields>().ToArray();
            foreach (var field in fields)
            {
                DataColumn c = new DataColumn(field.ToString());
                switch (field)
                {
                    case DASFields.AAF:
                        c.DataType = typeof(double);
                        break;
                    case DASFields.CalDate:
                        c.DataType = typeof(string);
                        break;
                    case DASFields.Channels:
                        c.DataType = typeof(string);
                        break;
                    case DASFields.DueDate:
                        c.DataType = typeof(string);
                        break;
                    case DASFields.FW:
                        c.DataType = typeof(string);
                        break;
                    case DASFields.PostTrigger:
                        c.DataType = typeof(double);
                        break;
                    case DASFields.PreTrigger:
                        c.DataType = typeof(double);
                        break;
                    case DASFields.SN:
                        c.DataType = typeof(string);
                        break;
                    case DASFields.SPS:
                        c.DataType = typeof(double);
                        break;
                }
                dt.Columns.Add(c);
            }
            return dt;
        }
        private DataTable InitializeSensorsTable(DataTable styleTable)
        {
            var dt = new DataTable(Tables.Sensor_Channels.ToString());
            var fields = Enum.GetValues(typeof(SensorFields)).Cast<SensorFields>().ToArray();
            foreach (var field in fields)
            {
                var c = new DataColumn(field.ToString());
                var c2 = styleTable.Columns.Add(field.ToString(), typeof(int));
                switch (field)
                {
                    case SensorFields.AbsChanNum:
                        c.DataType = typeof(int);
                        break;
                    case SensorFields.CalDueDate:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.Channel:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.DAS:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.Description:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.EID:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.EU:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.Filter:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.ISO:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.LTrig:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.Module:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.Polarity:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.PropToExc:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.Range:
                        c.DataType = typeof(double);
                        break;
                    case SensorFields.Sens:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.SensorSN:
                        c.DataType = typeof(string);
                        break;
                    case SensorFields.Voltage:
                        c.DataType = typeof(double);
                        break;
                }
                dt.Columns.Add(c);
            }
            return dt;
        }
        private DataTable InitializeSquibTable(DataTable styleTable)
        {
            var dt = new DataTable(Tables.Squib_Channels.ToString());
            var fields = Enum.GetValues(typeof(SquibFields)).Cast<SquibFields>().ToArray();
            foreach (var field in fields)
            {
                var c = new DataColumn(field.ToString());
                var c2 = styleTable.Columns.Add(field.ToString(), typeof(int));
                switch (field)
                {
                    case SquibFields.AbsChanNum:
                        c.DataType = typeof(int);
                        break;
                    case SquibFields.Channel:
                        c.DataType = typeof(string);
                        break;
                    case SquibFields.DAS:
                        c.DataType = typeof(string);
                        break;
                    case SquibFields.Delay:
                        c.DataType = typeof(double);
                        break;
                    case SquibFields.Description:
                        c.DataType = typeof(string);
                        break;
                    case SquibFields.Duration:
                        c.DataType = typeof(double);
                        break;
                    case SquibFields.EID:
                        c.DataType = typeof(string);
                        break;
                    case SquibFields.ISO:
                        c.DataType = typeof(string);
                        break;
                    case SquibFields.Module:
                        c.DataType = typeof(string);
                        break;
                    case SquibFields.SensorSN:
                        c.DataType = typeof(string);
                        break;
                    case SquibFields.SquibFireMode:
                        c.DataType = typeof(string);
                        break;
                }
                dt.Columns.Add(c);
            }
            return dt;
        }
        private DataTable InitializeDigitalTable()
        {
            var dt = new DataTable(Tables.Digital_Channels.ToString());
            return dt;
        }
        private DataTable InitializeCalculatedChannels()
        {
            var dt = new DataTable(Tables.Calculated_Channels.ToString());
            return dt;
        }
        private void SetCellText(string key, Worksheet ws, SharedStringTablePart sharedString, string text)
        {
            var dn = GetDefinedName(key);
            if (null != dn)
            {
                var cell = GetCell(ws, dn.Text);
                if (null != cell)
                {
                    cell.CellValue = new CellValue(InsertSharedStringItem(text, sharedString).ToString());
                    cell.DataType = new EnumValue<CellValues>() { Value = CellValues.SharedString };
                }
            }
        }
        private void SetCellDate(string key, Worksheet ws, string text)
        {
            var dn = GetDefinedName(key);
            if (null != dn)
            {
                var cell = GetCell(ws, dn.Text);
                if (null != cell)
                {
                    cell.CellValue = new CellValue(text);
                    cell.DataType = new EnumValue<CellValues>() { Value = CellValues.Number };
                }
            }
        }
        public override void OutputReport(DataTable[] tables, DataTable[] cellStyles)
        {
            DeleteExistingReport();
            CopyTemplate();
            using (var 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
                var definedNames = sp.WorkbookPart.Workbook.DefinedNames;
                foreach (DefinedName dn in definedNames)
                {
                    if (_nameLookup.ContainsKey(dn.Name.Value))
                    {
                    }
                    _nameLookup[dn.Name.Value] = dn;
                }
                //#FF7EFB6A
                var 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
                var idx = 0;
                foreach (var dt in tables)
                {
                    ProcessTable(dt, ref shareStringPart, sp, cellStyles[idx]);
                    idx++;
                }

                //now iso is a bit unusual
                ProcessISOSheet(ref shareStringPart, sp);
            }
        }
        public void OutputReportPDF(DataTable[] tables, DataTable[] cellStyles, string sensitivityDisplayFormat)
        {
            DeleteExistingReport();
            DeleteExistingPDFTemplate();
            CopyExcelTemplateForPDF();
            var pdf = new C1PdfDocument();

            using (var 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
                var definedNames = sp.WorkbookPart.Workbook.DefinedNames;
                foreach (DefinedName dn in definedNames)
                {
                    if (_nameLookup.ContainsKey(dn.Name.Value))
                    {
                    }
                    _nameLookup[dn.Name.Value] = dn;
                }
                //the spreadsheet can contain multiple pages
                //for now this handles each table as a different page
                var idx = 0;
                foreach (var dt in tables)
                {
                    if (dt.Rows.Count >= 1)
                    {
                        pdf.Landscape = true;
                        LeftMargin = 50;
                        RightMargin = 50;
                        PageHeight = 612;
                        PageWidth = 792;
                        switch (dt.TableName)
                        {
                            case "Digital_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 "Squib_Channels":
                                pdf.Landscape = true;
                                LeftMargin = 50;
                                RightMargin = 50;
                                PageHeight = 612;
                                PageWidth = 792;
                                RowsPerPage = 33;
                                ColsPerPage = 16; // A-P
                                DataRowsPerPage = 25;
                                break;
                            case "Sensor_Channels":
                                pdf.Landscape = true;
                                LeftMargin = 10;
                                RightMargin = 10;
                                PageHeight = 612;
                                PageWidth = 792;
                                RowsPerPage = 38;
                                ColsPerPage = 20; // A-T
                                DataRowsPerPage = 29;
                                break;
                            case "DAS": //TestSummary
                                pdf.Landscape = true;
                                LeftMargin = 50;
                                RightMargin = 50;
                                PageHeight = 612;
                                PageWidth = 792;
                                ColsPerPage = 14; // A-N
                                RowsPerPage = 36;
                                DataRowsPerPage = 27;
                                break;
                        }
                        ProcessTablePDF(dt, ref shareStringPart, sp, (null == cellStyles[idx]) ? null : cellStyles[idx], pdf, sensitivityDisplayFormat);
                        pdf.NewPage();
                    }
                    idx++;
                }

                //now iso is a bit unusual
                pdf.Landscape = false;
                LeftMargin = 72;
                RightMargin = 72;
                PageHeight = 792;
                PageWidth = 612;
                RowsPerPage = 50;
                ColsPerPage = 9; // A-I
                ProcessISOSheetPDF(ref shareStringPart, sp, pdf);
            }
            pdf.Save(OutputFilename);
            DeleteExistingPDFTemplate();
        }
        private List<TablePage> GetISOPages()
        {
            var currentPage = 1;
            var bDone = false;
            var pages = new List<TablePage>();

            while (!bDone)
            {
                var pageStart = GetDefinedName($"{TestObjectPrepend}{currentPage}{TestObjectPageFields.PageStart.ToString()}");
                var pageEnd = GetDefinedName($"{TestObjectPrepend}{currentPage}{TestObjectPageFields.PageEnd.ToString()}");

                if (null != pageStart && null != pageEnd)
                {
                    pages.Add(new TablePage()
                    {
                        PageStart = GetRowIndex(pageStart),
                        PageEnd = GetRowIndex(pageEnd)
                    });
                }
                else { bDone = true; }

                currentPage++;
            }

            return pages;
        }
        private void ProcessISOSheet(ref SharedStringTablePart sharedString, SpreadsheetDocument sp)
        {
            var ws = GetWorksheetPartByName(sp, ISOPageId);
            if (null == ws) { return; }

            //get a list of all pages on this sheet
            var pages = GetISOPages();//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>();
            for (var i = _testObjects.Length; i < pages.Count; i++)
            {
                toDelete.Add(pages[i]);
            }

            foreach (var page in toDelete)
            {
                pages.Remove(page);
                DeletePage(page, ws.Worksheet);
            }
            var key = string.Format("ISOSummaryPageFooter");
            SetCellText(key, ws.Worksheet, sharedString, $"ISO Page 1/{1 + _testObjects.Length}");

            for (var i = 0; i < _testObjects.Length; i++)
            {
                key = $"{TestObjectPrepend}{1 + i}{TestObjectPageFields.Footer.ToString()}";
                SetCellText(key, ws.Worksheet, sharedString, string.Format("ISO Page {0}/{1}", (2 + i), (1 + _testObjects.Length)));
            }

            var summaryFields = Enum.GetValues(typeof(ISOSummaryFields)).Cast<ISOSummaryFields>().ToArray();

            foreach (var field in summaryFields)
            {
                switch (field)
                {
                    case ISOSummaryFields.ISOSummaryPageReportDate:
                        SetCellDate(field.ToString(), ws.Worksheet, _isoSummary.GetField(field));
                        break;
                    default:
                        SetCellText(field.ToString(), ws.Worksheet, sharedString, _isoSummary.GetField(field));
                        break;
                }

            }
            var toFields = Enum.GetValues(typeof(TestObjectFields)).Cast<TestObjectFields>().ToArray();
            for (var i = 0; i < _testObjects.Length; i++)
            {
                var to = _testObjects[i];
                foreach (var toField in toFields)
                {
                    key = $"{TestObjectPrepend}{1 + i}{toField.ToString()}";
                    switch (toField)
                    {
                        case TestObjectFields.ReportDate:
                            SetCellDate(key, ws.Worksheet, ReportDate.ToOADate().ToString());
                            break;
                        case TestObjectFields.TestDescription:
                            SetCellText(key, ws.Worksheet, sharedString, TestDescription);
                            break;
                        case TestObjectFields.TestId:
                            SetCellText(key, ws.Worksheet, sharedString, TestId);
                            break;
                        case TestObjectFields.TestSetupName:
                            SetCellText(key, ws.Worksheet, sharedString, TestSetupName);
                            break;
                        default:
                            SetCellText(key, ws.Worksheet, sharedString, to.GetField(toField));
                            break;
                    }
                }
            }
            ws.Worksheet.Save();
        }
        private void ProcessISOSheetPDF(ref SharedStringTablePart sharedString, SpreadsheetDocument sp, C1PdfDocument pdf)
        {
            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 pen = new C1.WPF.Pdf.Pen(((SolidColorBrush)Application.Current.FindResource("Brush_ApplicationContentGroupBorder")).Color, 1);

            var ws = GetWorksheetPartByName(sp, ISOPageId);
            if (null == ws) { return; }

            //get a list of all pages on this sheet
            var pages = GetISOPages();//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>();
            for (var i = _testObjects.Length; i < pages.Count; i++)
            {
                toDelete.Add(pages[i]);
            }

            foreach (var page in toDelete)
            {
                pages.Remove(page);
                DeletePage(page, ws.Worksheet);
            }
            var cell = new Cell();
            var key = string.Format("ISOSummaryPageFooter");
            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], 0);
                    size = new Size(FieldSize(3), RowSize); //3 is temp
                    point = new Point(XPoint, YPoint);
                    rect = new Rect(point, size);
                    var sf = new StringFormat();
                    sf.Alignment = HorizontalAlignment.Center;
                    pdf.DrawString($"ISO Page 1/{1 + _testObjects.Length}", font, color, rect, sf);//Shouldn't be string literal
                }
            }

            var summaryFields = Enum.GetValues(typeof(ISOSummaryFields)).Cast<ISOSummaryFields>().ToArray();

            foreach (var field in summaryFields)
            {
                dn = GetDefinedName(field.ToString());
                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], 0);
                        size = new Size(FieldSize(3), RowSize); //3 is temp
                        point = new Point(XPoint, YPoint);
                        rect = new Rect(point, size);
                        StringFormat sf = new StringFormat();
                        sf.Alignment = HorizontalAlignment.Center;
                        string isoSummaryField = _isoSummary.GetField(field);
                        if (field == ISOSummaryFields.ISOSummaryPageReportDate)
                        {
                            sf.Alignment = HorizontalAlignment.Left;
                            DateTime convertedDate = DateTime.FromOADate(Convert.ToDouble(isoSummaryField));
                            pdf.DrawString(convertedDate.ToLongDateString(), font, color, rect, sf);
                        }
                        else
                        {
                            pdf.DrawString(" " + isoSummaryField, font, color, rect);
                        }
                        switch (field)
                        {
                            case ISOSummaryFields.ISOSummaryPageTestSetupName:
                            case ISOSummaryFields.ISOSummaryPageTestDescription:
                            case ISOSummaryFields.ISOSummaryPageTestId:
                            case ISOSummaryFields.ISOSummaryPageReportDate:
                                break;
                            default:
                                size = new Size(FieldSize(6), RowSize); //6 is temp
                                point = new Point(XPoint, YPoint);
                                rect = new Rect(point, size);
                                pdf.DrawLine(pen, rect.BottomLeft, rect.BottomRight);
                                break;
                        }

                        ColNumber = 0;
                        RowNumber = GetRowNumber(rowCol[2], 0);
                        string columnLetter = "A";
                        cell = GetCell(ws.Worksheet, columnLetter, (uint)RowNumber); //change RowNumber (and others) to uint?
                        font = GetPDFFont(sp, cell);
                        size = new Size(FieldSize(2), RowSize); //2 is temp
                        point = new Point(XPoint, YPoint);
                        rect = new Rect(point, size);
                        string cellString = GetCellString(dn.Text.Replace(rowCol[1], "A"), ws, sharedString);
                        pdf.DrawString(cellString, font, color, rect);
                    }
                }
            }
            var toFields = Enum.GetValues(typeof(TestObjectFields)).Cast<TestObjectFields>().ToArray();

            for (var i = 0; i < _testObjects.Length; i++)
            {
                pdf.NewPage();
                key = $"{TestObjectPrepend}{1 + i}{TestObjectPageFields.Footer.ToString()}";
                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], i + 1);
                        size = new Size(FieldSize(3), RowSize); //3 is temp
                        point = new Point(XPoint, YPoint);
                        rect = new Rect(point, size);
                        StringFormat sf = new StringFormat();
                        sf.Alignment = HorizontalAlignment.Center;
                        pdf.DrawString($"ISO Page {2 + i}/{1 + _testObjects.Length}", font, color, rect, sf);//Shouldn't be string literal
                    }
                }
                var to = _testObjects[i];
                foreach (var toField in toFields)
                {
                    key = $"{TestObjectPrepend}{1 + i}{toField.ToString()}";
                    dn = GetDefinedName(key.ToString());
                    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], i + 1);
                            size = new Size(FieldSize(3), RowSize); //3 is temp
                            point = new Point(XPoint, YPoint);
                            rect = new Rect(point, size);
                            var sf = new StringFormat();
                            sf.Alignment = HorizontalAlignment.Center;
                            string toFieldValue = to.GetField(toField);
                            if (toField == TestObjectFields.ReportDate)
                            {
                                sf.Alignment = HorizontalAlignment.Left;
                                var convertedDate = DateTime.FromOADate(Convert.ToDouble(toFieldValue));
                                pdf.DrawString(convertedDate.ToLongDateString(), font, color, rect, sf);
                            }
                            else
                            {
                                pdf.DrawString(" " + toFieldValue, font, color, rect);
                            }
                            switch (toField)
                            {
                                case TestObjectFields.TestSetupName:
                                case TestObjectFields.TestDescription:
                                case TestObjectFields.TestId:
                                case TestObjectFields.ReportDate:
                                    break;
                                default:
                                    size = new Size(FieldSize(6), RowSize); //6 is temp
                                    point = new Point(XPoint, YPoint);
                                    rect = new Rect(point, size);
                                    pdf.DrawLine(pen, rect.BottomLeft, rect.BottomRight);
                                    break;
                            }

                            ColNumber = 0;
                            RowNumber = GetRowNumber(rowCol[2], i + 1);
                            var columnLetter = "A";
                            cell = GetCell(ws.Worksheet, columnLetter, (uint)RowNumber); //change RowNumber (and others) to uint?
                            font = GetPDFFont(sp, cell);
                            size = new Size(FieldSize(2), RowSize); //2 is temp
                            point = new Point(XPoint, YPoint);
                            rect = new Rect(point, size);
                            var cellString = GetCellString(dn.Text.Replace(rowCol[1], "A"), ws, sharedString);
                            pdf.DrawString(cellString, font, color, rect);
                        }
                    }
                }
            }
            ws.Worksheet.Save();
        }
    }
}