/*
 * XLSX.File.Writer.cs
 * 
 * Copyright © 2017
 * Diversified Technical Systems, Inc.
 * All Rights Reserved
 */

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DTS.Common.Enums;
using DTS.Common.Enums.Sensors;
using DTS.Common.Interface.ExportData;
using DTS.Common.SerializationPlus;
using DTS.Common.Utilities.Logging;
using DTS.Slice.Control;
// ReSharper disable PossiblyMistakenUseOfParamsMethod

namespace DTS.Serialization.XLSX
{
    public partial class File
    {
        /// <summary>
        /// implementation of the Serialization.File.Writer class for XLSX
        /// http://fogbugz/fogbugz/default.asp?9920
        /// </summary>
        public class Writer : Writer<File>, IWriter<Test>
        {
            #region properties
            /// <summary>
            /// the owning file that controls this writer
            /// </summary>
            internal File WriterParent { get; }
            /// <summary>
            /// controls whether to export ADC or not
            /// </summary>
            public bool ExportADC { get; set; }
            /// <summary>
            /// controls whether to export EU or not
            /// </summary>
            public bool ExportEU { get; set; }
            /// <summary>
            /// controls whether to export MV or not
            /// </summary>
            public bool ExportMv { get; set; }
            /// <summary>
            /// the starting
            /// </summary>
            public double Start { get; set; }
            public double Stop { get; set; }
            public bool Filtered { get; set; }
            /// <summary>
            /// FB 6410 The list of user selected headers to export 
            /// </summary>
            public List<IExportHeader> ExportHeaders { get; set; }

            #endregion

            #region enums and constants

            /// <summary>
            /// every 1000 samples update progress
            /// </summary>
            private const int UPDATE_INTERVAL = 1000;
            #endregion

            #region methods
            /// <summary>
            /// writes out test to given path
            /// </summary>
            /// <param name="pathname"></param>
            /// <param name="id"></param>
            /// <param name="test"></param>
            /// <param name="bFiltering"></param>
            /// <param name="includeGroupNameInISOExport"></param>
            /// <param name="dataCollectionLength"></param>
            /// <param name="minStartTime"></param>
            public void Write(string pathname, string id, Test test, bool bFiltering, bool includeGroupNameInISOExport, double minStartTime, int dataCollectionLength)
            {
            }
            /// <summary>
            /// returns a datascaler for the given channel
            /// </summary>
            /// <param name="currentAnalogChannel"></param>
            /// <returns></returns>
            public static Common.DAS.Concepts.DataScaler GetDataScaler(Test.Module.AnalogInputChannel currentAnalogChannel)
            {
                var scaler = new Common.DAS.Concepts.DataScaler
                {
                    IsInverted = currentAnalogChannel.IsInverted,
                    IEPE = currentAnalogChannel.Bridge == SensorConstants.BridgeType.IEPE,
                    UnitConversion = currentAnalogChannel.UnitConversion,
                    BasedOnOutputAtCapacity = currentAnalogChannel.AtCapacity,
                    CapacityOutputIsBasedOn = currentAnalogChannel.CapacityOutputIsBasedOn,
                    SensitivityUnits = currentAnalogChannel.SensitivityUnits,
                    Multiplier = currentAnalogChannel.Multiplier,
                    UserOffsetEU = currentAnalogChannel.UserOffsetEU
                };

                scaler.SetLinearizationFormula(currentAnalogChannel.LinearizationFormula);
                scaler.SetScaleFactorMv(currentAnalogChannel.Data.ScaleFactorMv);
                scaler.SetScaleFactorEU(currentAnalogChannel.Data.ScaleFactorEU);
                scaler.SetUseEUScaleFactors(currentAnalogChannel.Data.UseEUScaleFactors);
                scaler.SetMvPerEu(currentAnalogChannel.Data.MvPerEu);
                try
                {
                    scaler.SetInitialOffset(currentAnalogChannel.InitialOffset);
                    scaler.ZeroMethodType = currentAnalogChannel.ZeroMethod;
                    scaler.SetRemovedADC(currentAnalogChannel.RemovedADC);
                    scaler.SetRemovedInternalADC(currentAnalogChannel.RemovedInternalADC);
                    scaler.SetDataZeroLevelADC(currentAnalogChannel.DataZeroLevelAdc);
                    scaler.SetZeroMvInADC(currentAnalogChannel.ZeroMvInADC);
                    try
                    {
                        //note the window average is the average over time when the average is not in your dataset
                        //sliceware is the only software that sets this currently
                        scaler.SetWindowAverageADC(currentAnalogChannel.WindowAverageADC);
                    }
                    catch (System.Exception ex)
                    {
                        APILogger.Log("WindowAverageADC failed to set", ex);
                    }
                }
                catch (System.Exception ex)
                {
                    APILogger.Log("Failed to set parameters on scaler", ex);
                }

                scaler.NominalExcitationVoltage = currentAnalogChannel.ExcitationVoltage;
                if (currentAnalogChannel.MeasuredExcitationVoltageValid)
                {
                    try
                    {
                        scaler.MeasuredExcitationVoltage = currentAnalogChannel.MeasuredExcitationVoltage;
                    }
                    catch (System.Exception ex)
                    {
                        APILogger.Log("failed to get measured excitation voltage", ex);
                    }
                }
                if (currentAnalogChannel.FactoryExcitationVoltageValid)
                {
                    try
                    {
                        scaler.FactoryExcitationVoltage = currentAnalogChannel.FactoryExcitationVoltage;
                    }
                    catch (System.Exception ex)
                    {
                        APILogger.Log("failed to get factory excitation", ex);
                    }
                }

                //14469 Excel export EU values incorrect for digital input channel
                //digital properties were not being set 
                scaler.Digital = currentAnalogChannel.IsDigital();
                scaler.DigitalMode = currentAnalogChannel.DigitalMode;
                scaler.SetDigitalMultiplier(currentAnalogChannel.DigitalMultiplier);

                scaler.ProportionalToExcitation = currentAnalogChannel.ProportionalToExcitation;
                return scaler;
            }

            /// <summary>
            /// updates the progress if possible
            /// </summary>
            /// <param name="dValue"></param>
            /// <param name="tickEventHandler"></param>
            private void UpdateProgress(double dValue, TickEventHandler tickEventHandler)
            {
                tickEventHandler?.Invoke(this, dValue);
            }

            private static readonly Dictionary<uint, Row> _rowIndexToRow = new Dictionary<uint, Row>();

            /// <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="xColumn"></param>
            /// <param name="rowIndex"></param>
            /// <param name="bLookForCell"></param>
            /// <returns></returns>
            protected Cell GetCell(Worksheet worksheet, string xColumn, uint rowIndex, bool bLookForCell = true)
            {
                var nameref = string.Format("{0}{1}", xColumn, rowIndex);
                // ReSharper disable once ReplaceWithSingleCallToFirstOrDefault
                Row theRow;
                if (_rowIndexToRow.ContainsKey(rowIndex))
                {
                    theRow = _rowIndexToRow[rowIndex];
                }
                else
                {
                    return InsertCellInWorksheet(xColumn, rowIndex, worksheet.WorksheetPart, nameref, false);
                }
                if (!bLookForCell)
                {
                    InsertCellInWorksheet(xColumn, rowIndex, worksheet.WorksheetPart, nameref, false);
                }
                // ReSharper disable once ReplaceWithSingleCallToFirstOrDefault
                var theCell = theRow.Descendants<Cell>().Where(c => c.CellReference == nameref).FirstOrDefault();
                return theCell ?? InsertCellInWorksheet(xColumn, rowIndex, worksheet.WorksheetPart, nameref, false);
            }
            // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
            // If the cell already exists, returns it. 
            private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart, string cellReference, bool bLookForCell)
            {
                var worksheet = worksheetPart.Worksheet;
                var sheetData = worksheet.GetFirstChild<SheetData>();

                // If the worksheet does not contain a row with the specified row index, insert one.
                Row row;
                if (_rowIndexToRow.ContainsKey(rowIndex))
                {
                    row = _rowIndexToRow[rowIndex];
                }
                else
                {
                    row = new Row { RowIndex = rowIndex };
                    _rowIndexToRow[rowIndex] = row;
                    sheetData.Append(row);
                }

                // If there is not a cell with the specified column name, insert one.  
                if (bLookForCell)
                {
                    var matches = row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex);
                    //resharper suggests these should be made into an array first
                    var matchesAsArray = matches as Cell[] ?? matches.ToArray();
                    if (matchesAsArray.Any())
                    {
                        return matchesAsArray.First();
                    }
                }
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                var refCell = row.Elements<Cell>().Where(cell => cell.CellReference.Value.Length == cellReference.Length).FirstOrDefault(cell => string.Compare(cell.CellReference.Value, cellReference, StringComparison.OrdinalIgnoreCase) > 0);

                var newCell = new Cell { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                return newCell;
            }
            protected WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
            {
                var sheets =
                   document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
                   Elements<Sheet>().Where(s => s.Name == sheetName);

                var enumerable = sheets as Sheet[] ?? sheets.ToArray();
                if (!enumerable.Any())
                {
                    // The specified worksheet does not exist.
                    return null;
                }

                var relationshipId = enumerable.First().Id.Value;
                var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
                return worksheetPart;
            }
            private Dictionary<string, int> _stringLookup;
            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();
                }

                int i;
                if (null == _stringLookup)
                {
                    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]; }
                i = _stringLookup.Count;
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
                shareStringPart.SharedStringTable.Save();
                _stringLookup[text] = i;
                return i;
            }

            private void WriteTime(WorksheetPart ws,
                string column,
                uint row,
                DateTime time, SharedStringTablePart sharedString)
            {
                var cell = GetCell(ws.Worksheet, column, row);
                cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                cell.CellValue = new CellValue(InsertSharedStringItem(time.ToShortTimeString(), sharedString).ToString());
                cell.StyleIndex = _dateFormatIndex;
            }
            /// <summary>
            /// FB 6410 The override for WriteTime to use the correct row for the requested headerName
            /// </summary>
            /// <param name="ws"></param>
            /// <param name="column"></param>
            /// <param name="headerName"></param>
            /// <param name="time"></param>
            /// <param name="sharedString"></param>
            private void WriteTime(WorksheetPart ws,
             string column,
             string headerName,
             DateTime time, SharedStringTablePart sharedString)
            {
                if (!_headerRowLineIndex.ContainsKey(headerName))
                {
                    return;
                }
                WriteTime(ws, column, _headerRowLineIndex[headerName], time, sharedString);
            }

            private void WriteDate(WorksheetPart ws,
                string column,
                uint row,
                DateTime date)
            {
                var cell = GetCell(ws.Worksheet, column, row);
                cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                cell.CellValue =
                    new CellValue(date.ToOADate().ToString(CultureInfo.InvariantCulture));
                cell.StyleIndex = _dateFormatIndex;
            }
            /// <summary>
            /// FB 6410 The override for WriteDate to use the correct row for the requested headerName
            /// </summary>
            /// <param name="ws"></param>
            /// <param name="column"></param>
            /// <param name="headerName"></param>
            /// <param name="date"></param>
            private void WriteDate(WorksheetPart ws,
              string column,
              string headerName,
              DateTime date)
            {
                if (!_headerRowLineIndex.ContainsKey(headerName))
                {
                    return;
                }
                WriteDate(ws, column, _headerRowLineIndex[headerName], date);
            }

            private void WriteDouble(WorksheetPart ws,
                string column,
                uint row,
                double value)
            {
                var cell = GetCell(ws.Worksheet, column, row, false);
                cell.CellValue = new CellValue(value.ToString(CultureInfo.InvariantCulture));
            }

            /// <summary>
            /// FB 6410 The override for WriteDouble to use the correct row for the requested headerName
            /// </summary>
            /// <param name="ws"></param>
            /// <param name="column"></param>
            /// <param name="headerName"></param>
            /// <param name="value"></param>
            private void WriteDouble(WorksheetPart ws,
              string column,
              string headerName,
              double value)
            {
                if (!_headerRowLineIndex.ContainsKey(headerName))
                {
                    return;
                }
                WriteDouble(ws, column, _headerRowLineIndex[headerName], value);
            }

            private void WriteString(WorksheetPart ws,
                string column,
                uint row,
                SharedStringTablePart sharedStringTablePart,
                string value)
            {
                var cell = GetCell(ws.Worksheet, column, row, false);
                cell.CellValue = new CellValue(InsertSharedStringItem(value, sharedStringTablePart).ToString());
                cell.DataType = new EnumValue<CellValues> { Value = CellValues.SharedString };
            }

            /// <summary>
            /// FB 6410 The override for WriteString to use the correct row for the requested headerName
            /// </summary>
            /// <param name="ws"></param>
            /// <param name="column"></param>
            /// <param name="headerName"></param>
            /// <param name="sharedStringTablePart"></param>
            /// <param name="value"></param>
            private void WriteString(WorksheetPart ws,
                string column,
                string headerName,
                SharedStringTablePart sharedStringTablePart,
                string value)
            {
                //If we can't find the headerName that header is not selected by user and it's not in this dictionary skip this header
                if (!_headerRowLineIndex.ContainsKey(headerName))
                {
                    return;
                }
                // Do not eneter the header name twice if it's already entered 
                if (_alreadyEnteredHeader.Contains(value))
                {
                    return;
                }
                WriteString(ws, column, _headerRowLineIndex[headerName], sharedStringTablePart, value);
                _alreadyEnteredHeader.Add(headerName);
            }

            private List<string> _alreadyEnteredHeader = new List<string>();

            private static string GetColumn(int index)
            {
                var dividend = index + 2;//we are starting at b...
                var columnName = string.Empty;

                while (dividend > 0)
                {
                    var modulo = (dividend - 1) % 26;
                    columnName = Convert.ToChar(65 + modulo) + columnName;
                    dividend = (dividend - modulo) / 26;
                }

                return columnName;

            }

            private uint _dateFormatIndex;
            private void AddStyleSheet(SpreadsheetDocument sp)
            {
                var stylesheet = sp.WorkbookPart.WorkbookStylesPart.Stylesheet;
                stylesheet.CellFormats.AppendChild(new CellFormat
                {
                    NumberFormatId = 14,
                    ApplyNumberFormat = true
                });
                _dateFormatIndex = stylesheet.CellFormats.Count;
                stylesheet.Save();
            }
            /// <summary>
            /// Insert an empty row after the last entered row
            /// </summary>
            /// <param name="worksheetPart"></param>
            private void InsertRow(WorksheetPart worksheetPart)
            {
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                Row lastRow = sheetData.Elements<Row>().LastOrDefault();

                if (lastRow != null)
                {
                    sheetData.InsertAfter(new Row() { RowIndex = (lastRow.RowIndex + 1) }, lastRow);
                }
            }

            private Dictionary<string, uint> _headerRowLineIndex = new Dictionary<string, uint>();

            /// <summary>
            /// This dictionary holds the datascaler for the AICs for the test. This was done for performance reasons. There
            /// are several for/foreach loops that are in the write function that all need the scaler, but dont all need to
            /// get it everytime.
            /// </summary>
            private Dictionary<int, Common.DAS.Concepts.DataScaler> _aicToScaler;
            /// <summary>
            /// writes out test to given path
            /// </summary>
            /// <param name="pathname"></param>
            /// <param name="id"></param>
            /// <param name="dataFolder"></param>
            /// <param name="test"></param>
            /// <param name="bFiltering"></param>
            /// <param name="includeGroupNameInISOExport"></param>
            /// <param name="fd"></param>
            /// <param name="tmChannel"></param>
            /// <param name="channelNumber"></param>
            /// <param name="beginEventHandler"></param>
            /// <param name="cancelEventHandler"></param>
            /// <param name="endEventHandler"></param>
            /// <param name="tickEventHandler"></param>
            /// <param name="errorEventHandler"></param>
            /// <param name="cancelRequested"></param>
            /// <param name="minStartTime"></param>
            /// <param name="dataCollectionLength"></param>
            public void Write(string pathname,
                               string id,
                               string dataFolder,
                               Test test,
                               bool bFiltering,
                               bool includeGroupNameInISOExport,
                               FilteredData fd,
                               Test.Module.Channel tmChannel,
                               int channelNumber,
                               BeginEventHandler beginEventHandler,
                               CancelEventHandler cancelEventHandler,
                               EndEventHandler endEventHandler,
                               TickEventHandler tickEventHandler,
                               ErrorEventHandler errorEventHandler,
                               CancelRequested cancelRequested,
                               double minStartTime,
                               int dataCollectionLength)
            {
                try
                {
                    _rowIndexToRow.Clear();
                    _headerRowLineIndex.Clear();
                    _aicToScaler = new Dictionary<int, Common.DAS.Concepts.DataScaler>();
                    _stringLookup = null;
                    beginEventHandler?.Invoke(this, 100);
                    //pathname has the full path of our destination file
                    var sourceFile = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ReportTemplates",
                        "XLSXExportTemplate.xlsx");

                    var expectedWrites = double.NaN;
                    if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(pathname)))
                    {
                        System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(pathname));
                    }
                    System.IO.File.Copy(sourceFile, pathname, true);
                    //FB 6410 Build the dictionary to keep the name of the headers need to be exported along with the actual row number in the excel file
                    uint headerRowIndex = 1;

                    _headerRowLineIndex.Add(XLSXExportHeaderLine.Headers.GetDescription(), headerRowIndex);

                    foreach (var header in ExportHeaders)
                    {
                        if (header.IsSelected)
                        {
                            headerRowIndex++;
                            _headerRowLineIndex.Add(header.HeaderName, headerRowIndex);
                        }
                    }

                    headerRowIndex++;
                    _headerRowLineIndex.Add(XLSXExportHeaderLine.DataStart.GetDescription(), headerRowIndex);

                    headerRowIndex++;
                    _headerRowLineIndex.Add(XLSXExportHeaderLine.Labels.GetDescription(), headerRowIndex);

                    var maxSampleRate = test.Channels.Select(ch => ch.ParentModule.SampleRateHz).Max();

                    //14659 Excel (xlsx) export all (filtered) fails, but says export finished in green
                    //we need to use channel order rather than display order as display order in some data sets
                    //so that we can index the channels
                    var channels = new List<Test.Module.Channel>();
                    channels.AddRange(test.Channels);
                    channels.Sort((a, b) =>
                    {
                        var res = a.AbsoluteDisplayOrder.CompareTo(b.AbsoluteDisplayOrder);
                        if (res != 0) { return res; }
                        return a.Number.CompareTo(b.Number);
                    });

                    using (var sp = SpreadsheetDocument.Open(pathname, true))
                    {
                        AddStyleSheet(sp);
                        var shareStringPart = sp.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                        sp.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
                        sp.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
                        var dataWs = GetWorksheetPartByName(sp, "Data");
                        var rows = dataWs.Worksheet.Descendants<Row>();
                        //FB 6410 insert rows for each selected header in the excel sheet which will be filled later on 
                        for (int i = 0; i < _headerRowLineIndex.Count - 1; i++)
                        {
                            InsertRow(dataWs);
                        }

                        foreach (var row in rows)
                        {
                            _rowIndexToRow[row.RowIndex] = row;
                        }


                        WriteString(dataWs, "A", XLSXExportHeaderLine.TestDate.GetDescription(), shareStringPart, XLSXExportHeaderLine.TestDate.GetDescription());
                        WriteDate(dataWs, "B", XLSXExportHeaderLine.TestDate.GetDescription(), test.InceptionDate);

                        WriteString(dataWs, "A", XLSXExportHeaderLine.TestTime.GetDescription(), shareStringPart, XLSXExportHeaderLine.TestTime.GetDescription());
                        WriteTime(dataWs, "B", XLSXExportHeaderLine.TestTime.GetDescription(), test.InceptionDate, shareStringPart);

                        WriteString(dataWs, "A", XLSXExportHeaderLine.TestId.GetDescription(), shareStringPart, XLSXExportHeaderLine.TestId.GetDescription());
                        WriteString(dataWs, "B", XLSXExportHeaderLine.TestId.GetDescription(), shareStringPart, id);

                        WriteString(dataWs, "A", XLSXExportHeaderLine.TestDescription.GetDescription(), shareStringPart, XLSXExportHeaderLine.TestDescription.GetDescription());
                        WriteString(dataWs, "B", XLSXExportHeaderLine.TestDescription.GetDescription(), shareStringPart, test.Description);

                        //go through all the channels, and include whatever data is requested
                        foreach (var channel in channels)
                        {
                            //for now ignore the channel if it's not an analoginputchannel, this probably includes everything except maybe the calculated channels ...
                            if (!(channel is Test.Module.AnalogInputChannel aic)) { continue; }
                            //this should divide evenly, so ceiling is necessary in theory
                            var rate = Convert.ToInt32(Math.Ceiling(maxSampleRate / aic.ParentModule.SampleRateHz));

                            var iChannel = channels.IndexOf(channel);
                            if (double.IsNaN(expectedWrites))
                            {
                                expectedWrites = (double)test.Channels.Count * dataCollectionLength;
                            }
                            var column = GetColumn(iChannel);

                            WriteString(dataWs, "A", XLSXExportHeaderLine.SampleRate.GetDescription(), shareStringPart, XLSXExportHeaderLine.SampleRate.GetDescription());
                            WriteDouble(dataWs, column, XLSXExportHeaderLine.SampleRate.GetDescription(), maxSampleRate);

                            WriteString(dataWs, "A", XLSXExportHeaderLine.HardwareAntiAliasFilter.GetDescription(), shareStringPart, XLSXExportHeaderLine.HardwareAntiAliasFilter.GetDescription());
                            WriteDouble(dataWs, column, XLSXExportHeaderLine.HardwareAntiAliasFilter.GetDescription(), aic.ParentModule.AaFilterRateHz);

                            WriteString(dataWs, "A", XLSXExportHeaderLine.DataChannelNumber.GetDescription(), shareStringPart, XLSXExportHeaderLine.DataChannelNumber.GetDescription());
                            WriteDouble(dataWs, column, XLSXExportHeaderLine.DataChannelNumber.GetDescription(), (double)1 + iChannel);
                            var isoCode = "";
                            if (null != WriterParent)
                            {
                                switch (WriterParent.ISOViewMode)
                                {
                                    case Common.Enums.IsoViewMode.ISOOnly:
                                        isoCode = aic.IsoCode;
                                        break;
                                    case Common.Enums.IsoViewMode.ISOAndUserCode:
                                        isoCode = $"{aic.IsoCode}/{aic.UserCode}";
                                        break;
                                    case Common.Enums.IsoViewMode.UserCodeOnly:
                                        isoCode = aic.UserCode;
                                        break;
                                    case Common.Enums.IsoViewMode.ChannelNameOnly:
                                        isoCode = string.Empty;
                                        break;
                                }
                            }
                            WriteString(dataWs, "A", XLSXExportHeaderLine.IsoCode.GetDescription(), shareStringPart, XLSXExportHeaderLine.IsoCode.GetDescription());
                            WriteString(dataWs, column, XLSXExportHeaderLine.IsoCode.GetDescription(), shareStringPart, isoCode);

                            WriteString(dataWs, "A", XLSXExportHeaderLine.ChannelDescription.GetDescription(), shareStringPart, XLSXExportHeaderLine.ChannelDescription.GetDescription());
                            WriteString(dataWs, column, XLSXExportHeaderLine.ChannelDescription.GetDescription(), shareStringPart, aic.ChannelName2);

                            WriteString(dataWs, "A", XLSXExportHeaderLine.ChannelLocation.GetDescription(), shareStringPart, XLSXExportHeaderLine.ChannelLocation.GetDescription());

                            WriteString(dataWs, "A", XLSXExportHeaderLine.SensorSerialNumber.GetDescription(), shareStringPart, XLSXExportHeaderLine.SensorSerialNumber.GetDescription());
                            WriteString(dataWs, column, XLSXExportHeaderLine.SensorSerialNumber.GetDescription(), shareStringPart, aic.SerialNumber);

                            var filter = SaeJ211Filter.Parse(aic.SoftwareFilter);

                            WriteString(dataWs, "A", XLSXExportHeaderLine.SoftwareFilter.GetDescription(), shareStringPart, XLSXExportHeaderLine.SoftwareFilter.GetDescription());
                            //FB 18024 Show NONE for unfiltered
                            if (Filtered)
                            {
                                WriteString(dataWs, column, XLSXExportHeaderLine.SoftwareFilter.GetDescription(), shareStringPart, filter.Name);
                            }
                            else
                            {
                                WriteString(dataWs, column, XLSXExportHeaderLine.SoftwareFilter.GetDescription(), shareStringPart, "NONE");
                            }

                            if (bFiltering)
                            {
                                WriteString(dataWs, "A", XLSXExportHeaderLine.SoftwareFilterDb.GetDescription(), shareStringPart, XLSXExportHeaderLine.SoftwareFilterDb.GetDescription());
                                //FB 18024 Show NONE for unfiltered
                                if (Filtered)
                                {
                                    WriteDouble(dataWs, column, XLSXExportHeaderLine.SoftwareFilterDb.GetDescription(), bFiltering ? filter.CutoffFrequencyHz : 0D);
                                }
                                else
                                {
                                    WriteString(dataWs, column, XLSXExportHeaderLine.SoftwareFilterDb.GetDescription(), shareStringPart, "NONE");
                                }
                            }

                            WriteString(dataWs, "A", XLSXExportHeaderLine.EngineeringUnits.GetDescription(), shareStringPart, XLSXExportHeaderLine.EngineeringUnits.GetDescription());
                            WriteString(dataWs, column, XLSXExportHeaderLine.EngineeringUnits.GetDescription(), shareStringPart, aic.EngineeringUnits);

                            var preTriggerSamples = aic.ParentModule.TriggerSampleNumbers[0] - (double)aic.ParentModule.StartRecordSampleNumber;
                            if (preTriggerSamples < 0) { preTriggerSamples = 0D; }
                            if (preTriggerSamples > aic.ParentModule.NumberOfSamples) { preTriggerSamples = aic.ParentModule.NumberOfSamples; }
                            if (preTriggerSamples > Start * aic.ParentModule.SampleRateHz) { preTriggerSamples = Math.Truncate(Start * aic.ParentModule.SampleRateHz); }
                            WriteString(dataWs, "A", XLSXExportHeaderLine.UserComment.GetDescription(), shareStringPart, XLSXExportHeaderLine.UserComment.GetDescription());

                            WriteString(dataWs, "A", XLSXExportHeaderLine.PreZero.GetDescription(), shareStringPart, XLSXExportHeaderLine.PreZero.GetDescription());
                            WriteDouble(dataWs, column, XLSXExportHeaderLine.PreZero.GetDescription(), rate * preTriggerSamples);

                            var postTriggerSamples = aic.ParentModule.NumberOfSamples - preTriggerSamples;
                            if (postTriggerSamples < 0) { postTriggerSamples = 0D; }
                            if (postTriggerSamples > Stop * aic.ParentModule.SampleRateHz) { postTriggerSamples = Math.Truncate(Stop * aic.ParentModule.SampleRateHz); }
                            WriteString(dataWs, "A", XLSXExportHeaderLine.PostZero.GetDescription(), shareStringPart, XLSXExportHeaderLine.PostZero.GetDescription());
                            WriteDouble(dataWs, column, XLSXExportHeaderLine.PostZero.GetDescription(), rate * postTriggerSamples);

                            WriteString(dataWs, "A", XLSXExportHeaderLine.DataZero.GetDescription(), shareStringPart, XLSXExportHeaderLine.DataZero.GetDescription());
                            WriteDouble(dataWs, column, XLSXExportHeaderLine.DataZero.GetDescription(), aic.DataZeroLevelAdc);

                            _aicToScaler[iChannel] = GetDataScaler(aic);

                            WriteString(dataWs, "A", XLSXExportHeaderLine.ScaleEu.GetDescription(), shareStringPart, XLSXExportHeaderLine.ScaleEu.GetDescription());
                            WriteDouble(dataWs, column, XLSXExportHeaderLine.ScaleEu.GetDescription(), _aicToScaler[iChannel].GetAdcToEuScalingFactor());

                            WriteString(dataWs, "A", XLSXExportHeaderLine.ScaleMv.GetDescription(), shareStringPart, XLSXExportHeaderLine.ScaleMv.GetDescription());
                            WriteDouble(dataWs, column, XLSXExportHeaderLine.ScaleMv.GetDescription(), _aicToScaler[iChannel].GetAdcToMvScalingFactor());

                            WriteString(dataWs, "A", XLSXExportHeaderLine.DataStart.GetDescription(), shareStringPart, XLSXExportHeaderLine.DataStart.GetDescription());

                            WriteString(dataWs, "A", XLSXExportHeaderLine.Labels.GetDescription(), shareStringPart, XLSXExportHeaderLine.Labels.GetDescription());
                            WriteString(dataWs, column, XLSXExportHeaderLine.Labels.GetDescription(), shareStringPart, $"Chan {1 + iChannel}: {aic.ChannelName2}");
                        }
                        dataWs.Worksheet.Save();
                    }
                    //now that we are done with all the strings, open up as SAX and write all the values
                    //the numeric cells are easy, but non numeric have data types and sharedstringpart s to manipulate, so 
                    //that's why I split the two up

                    using (var myDoc = SpreadsheetDocument.Open(pathname, true))
                    {
                        var workbookPart = myDoc.WorkbookPart;

                        var worksheetPart = GetWorksheetPartByName(myDoc, "Data");
                        var origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);

                        var replacementPart =
                            workbookPart.AddNewPart<WorksheetPart>();
                        var replacementPartId = workbookPart.GetIdOfPart(replacementPart);

                        var reader = OpenXmlReader.Create(worksheetPart);
                        var writer = OpenXmlWriter.Create(replacementPart);
                        var filteredData = new double[test.Channels.Count][];
                        if (Filtered)
                        {
                            var data = filteredData;

                            Parallel.ForEach(channels, channel =>
                            {
                                var aic = (Test.Module.AnalogInputChannel)channel;
                                if (null == aic) { return; }
                                var iChannel = channels.IndexOf(channel);
                                var sampleCount = aic.PersistentChannelInfo.Data.Length;
                                data[iChannel] = new double[sampleCount];
                                for (var iSampleIdx = 0;
                                    iSampleIdx < sampleCount;
                                    iSampleIdx++)
                                {
                                    data[iChannel][iSampleIdx] = _aicToScaler[iChannel].GetEU(aic.PersistentChannelInfo.Data[iSampleIdx]);
                                }
                                var filter = SaeJ211Filter.Parse(aic.SoftwareFilter);
                                data[iChannel] = filter.Apply(data[iChannel], aic.ParentModule.SampleRateHz, UseLegacyTDCSoftwareFiltering);
                            });
                            filteredData = data;
                        }
                        headerRowIndex++;
                        while (reader.Read())
                        {
                            if (reader.ElementType == typeof(SheetData) && !reader.IsEndElement)
                            {
                                writer.WriteStartElement(new SheetData());
                            }
                            else if (reader.ElementType == typeof(SheetData) && reader.IsEndElement)
                            {
                                for (var sampleIndex = 0UL;
                                    (int)sampleIndex <= dataCollectionLength;
                                    sampleIndex++)
                                {

                                    var row = new Row { RowIndex = Convert.ToUInt32(headerRowIndex + sampleIndex) };
                                    writer.WriteStartElement(row);

                                    foreach (var channel in channels)
                                    {
                                        if (!(channel is Test.Module.AnalogInputChannel aic)) { continue; }
                                        var iChannel = channels.IndexOf(channel);

                                        var dStartTime = (double)aic.ParentModule.StartRecordSampleNumber / aic.ParentModule.SampleRateHz;
                                        if (aic.ParentModule.TriggerSampleNumbers.Count > 0)
                                        {
                                            dStartTime -= (double)aic.ParentModule.TriggerSampleNumbers[0] / aic.ParentModule.SampleRateHz;
                                        }
                                        //14513 double rounding error causing data to be offset by one sample incorrectly
                                        // note that using decimals will fix the imprecision issue, but will use more time ...
                                        var delta = Convert.ToDecimal(dStartTime) - Convert.ToDecimal(minStartTime);
                                        var channelOffsetStart = Convert.ToInt32(delta * Convert.ToDecimal(aic.ParentModule.SampleRateHz));
                                        var rate = maxSampleRate / aic.ParentModule.SampleRateHz;
                                        var indexAtCurrentTime = ((double)sampleIndex - channelOffsetStart) / rate;
                                        var thisChannelsIndexAtCurrentTime = Convert.ToInt32(Math.Floor(indexAtCurrentTime));
                                        var step = Convert.ToInt32(Math.Ceiling(indexAtCurrentTime) - thisChannelsIndexAtCurrentTime);

                                        if (0 == iChannel)
                                        {
                                            var time = minStartTime + sampleIndex / maxSampleRate;
                                            var c = new Cell
                                            {
                                                CellValue = new CellValue(time.ToString(CultureInfo.InvariantCulture))
                                            };
                                            writer.WriteElement(c);
                                        }
                                        var value = Filtered ? filteredData[iChannel][thisChannelsIndexAtCurrentTime] : _aicToScaler[iChannel].GetEU(aic.PersistentChannelInfo.Data[thisChannelsIndexAtCurrentTime]);
                                        if (step > 0) //INTERPOLATE
                                        {
                                            var increment = 0D;
                                            if (Filtered)
                                            {
                                                if ((1 + thisChannelsIndexAtCurrentTime) < filteredData[iChannel].Length)
                                                {
                                                    increment = (filteredData[iChannel][1 + thisChannelsIndexAtCurrentTime] - value) / rate;
                                                }
                                                else
                                                {
                                                    increment = (value - filteredData[iChannel][thisChannelsIndexAtCurrentTime - 1]) / rate;
                                                }
                                            }
                                            else
                                            {
                                                if ((1 + thisChannelsIndexAtCurrentTime) < aic.PersistentChannelInfo.Data.Length)
                                                {
                                                    increment = (_aicToScaler[iChannel].GetEU(aic.PersistentChannelInfo.Data[thisChannelsIndexAtCurrentTime + 1]) - value) / rate;
                                                }
                                                else
                                                {
                                                    increment = (value - _aicToScaler[iChannel].GetEU(aic.PersistentChannelInfo.Data[thisChannelsIndexAtCurrentTime - 1])) / rate;
                                                }
                                            }
                                            value = value + increment * step;
                                        }
                                        var cell = new Cell
                                        {
                                            CellValue = new CellValue(
                                                value.ToString(CultureInfo.InvariantCulture))
                                        };
                                        writer.WriteElement(cell);
                                    }

                                    if (0 == sampleIndex % UPDATE_INTERVAL)
                                    {
                                        var currentWrite = (double)sampleIndex * test.Channels.Count;
                                        UpdateProgress(100D * currentWrite / expectedWrites, tickEventHandler);
                                    }

                                    writer.WriteEndElement();
                                }

                                writer.WriteEndElement();
                            }
                            else
                            {
                                if (reader.ElementType == typeof(Row) && reader.IsStartElement)
                                {
                                    writer.WriteElement(reader.LoadCurrentElement());
                                }
                                else
                                {
                                    if (reader.IsStartElement)
                                    {
                                        writer.WriteStartElement(reader);
                                    }
                                    else if (reader.IsEndElement)
                                    {
                                        writer.WriteEndElement();
                                    }
                                }
                            }
                        }

                        reader.Close();
                        writer.Close();

                        var sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First();
                        sheet.Id.Value = replacementPartId;
                        workbookPart.DeletePart(worksheetPart);
                        filteredData = null;
                    }
                    foreach (var channel in test.Channels)
                    {
                        if (!(channel is Test.Module.AnalogInputChannel aic)) continue;
                        aic.PersistentChannelInfo.UnSet();

                        // These garbage collection calls cause a significant performance hit and were removed becasue of this. They
                        // were probably put here when DP was still 32bit only.

                        //System.Runtime.GCSettings.LatencyMode = System.Runtime.GCLatencyMode.Batch;
                        //System.Runtime.GCSettings.LargeObjectHeapCompactionMode = System.Runtime.GCLargeObjectHeapCompactionMode.CompactOnce;
                        //GC.Collect();
                    }
                }
                catch (System.Exception ex)
                {
                    APILogger.Log("encountered problem writing XLSX test files", ex);
                    errorEventHandler?.Invoke(this, ex);
                }
                finally
                {
                    tickEventHandler?.Invoke(this, 100D);
                    endEventHandler?.Invoke(this);
                }
            }
            #endregion

            /// <summary>
            /// constructs the writer with a given file and encoding
            /// </summary>
            /// <param name="fileType"></param>
            /// <param name="encoding"></param>
            internal Writer(File fileType, int encoding)
                : base(fileType, encoding)
            {
                ExportADC = false;
                ExportEU = true;
                ExportMv = false;
                WriterParent = fileType;
            }

            /// <summary>
            /// initializes the writer
            /// </summary>
            /// <param name="pathname"></param>
            /// <param name="id"></param>
            /// <param name="dataFolder"></param>
            /// <param name="test"></param>
            /// <param name="bFiltering"></param>
            /// <param name="includeGroupNameInISOExport"></param>
            /// <param name="fd"></param> 
            /// <param name="tmChannel"></param>
            /// <param name="channelNumber"></param>
            /// <param name="beginEventHandler"></param>
            /// <param name="cancelEventHandler"></param>
            /// <param name="endEventHandler"></param>
            /// <param name="tickEventHandler"></param>
            /// <param name="errorEventHandler"></param>
            /// <param name="cancelRequested"></param>
            public void Initialize(string pathname,
                                string id,
                                string dataFolder,
                               Test test,
                               bool bFiltering,
                               bool includeGroupNameInISOExport,
                               FilteredData fd,
                               Test.Module.Channel tmChannel,
                               int channelNumber,
                               BeginEventHandler beginEventHandler,
                               CancelEventHandler cancelEventHandler,
                               EndEventHandler endEventHandler,
                               TickEventHandler tickEventHandler,
                               ErrorEventHandler errorEventHandler,
                               CancelRequested cancelRequested)
            {
            }
        }
    }
}