/* * 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 DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using DTS.Common.Utilities.Logging; using DTS.Slice.Control; // ReSharper disable PossiblyMistakenUseOfParamsMethod namespace DTS.Serialization.XLSX { public partial class File { /// /// implementation of the Serialization.File.Writer class for XLSX /// http://fogbugz/fogbugz/default.asp?9920 /// public class Writer : Writer, IWriter { #region properties /// /// the owning file that controls this writer /// internal File WriterParent { get; private set; } /// /// controls whether to export ADC or not /// public bool ExportADC { get; set; } /// /// controls whether to export EU or not /// public bool ExportEU { get; set; } /// /// controls whether to export MV or not /// public bool ExportMv { get; set; } /// /// the starting /// public double Start { get; set; } public double Stop { get ; set ; } public bool Filtered { get; set; } //private readonly Property> _filteredChannelData = new Property>("FilteredChannelData",new List(),true); /// /// Get/set the filtered channel data. If this list is supplied, the corresponding test /// channel data values will be supplied from this list. /// //public List FilteredChannelData //{ // get { return _filteredChannelData.Value; } // set { _filteredChannelData.Value = value; } //} #endregion #region enums and constants /// /// every 1000 samples update progress /// private const int UPDATE_INTERVAL = 1000; #endregion #region methods /// /// writes out test to given path /// /// /// /// /// /// public void Write(string pathname, string id, Test test, bool bFiltering, bool includeGroupNameInISOExport, double minStartTime, int dataCollectionLength) { } /// /// returns a datascaler for the given channel /// /// /// private static Common.DAS.Concepts.DataScaler GetDataScaler(Test.Module.AnalogInputChannel currentAnalogChannel) { var scaler = new Common.DAS.Concepts.DataScaler { IsInverted = currentAnalogChannel.IsInverted, IEPE = currentAnalogChannel.Bridge == Common.DAS.Concepts.Test.Module.Channel.Sensor.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.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); } } scaler.ProportionalToExcitation = currentAnalogChannel.ProportionalToExcitation; return scaler; } /// /// updates the progress if possible /// /// /// private void UpdateProgress(double dValue, TickEventHandler tickEventHandler) { if (null != tickEventHandler) { tickEventHandler(this, dValue); } } private static readonly Dictionary RowIndexToRow = new Dictionary(); /// /// 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 /// /// /// /// /// /// protected Cell GetCell(Worksheet worksheet, string xColumn, UInt32 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().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(); // 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().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().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(). Elements().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 _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(); foreach (var item in shareStringPart.SharedStringTable.Elements()) { _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.SharedString); cell.CellValue = new CellValue(InsertSharedStringItem(time.ToShortTimeString(), sharedString).ToString()); cell.StyleIndex = _dateFormatIndex; } private void WriteDate(WorksheetPart ws, string column, uint row, DateTime date) { var cell = GetCell(ws.Worksheet, column, row); cell.DataType = new EnumValue(CellValues.Number); cell.CellValue = new CellValue(date.ToOADate().ToString(CultureInfo.InvariantCulture)); cell.StyleIndex = _dateFormatIndex; } 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)); } 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 { Value = CellValues.SharedString }; } 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(); } /// /// writes out test to given path /// /// /// /// /// /// /// /// /// /// /// /// /// /// /// /// 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(); _stringLookup = null; if (null != beginEventHandler) { beginEventHandler(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; System.IO.File.Copy(sourceFile, pathname); using (var sp = SpreadsheetDocument.Open(pathname, true)) { AddStyleSheet(sp); var shareStringPart = sp.WorkbookPart.GetPartsOfType().First(); sp.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; sp.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; var dataWs = GetWorksheetPartByName(sp, "Data"); var rows = dataWs.Worksheet.Descendants(); foreach (var row in rows) { RowIndexToRow[row.RowIndex] = row; } WriteDate(dataWs, "B", 2, test.InceptionDate); WriteTime(dataWs, "B", 3, test.InceptionDate, shareStringPart); WriteString(dataWs, "B", 4, shareStringPart, test.Id); WriteString(dataWs, "B", 5, shareStringPart, test.Description); //go through all the channels, and include whatever data is requested for (var i = 0; i < test.Channels.Count; i++) { var aic = test.Channels[i] as Test.Module.AnalogInputChannel; //for now ignore the channel if it's not an analoginputchannel, this probably includes everything except maybe the calculated channels ... if (null == aic) { continue; } if (double.IsNaN(expectedWrites)) { expectedWrites = (double)test.Channels.Count * dataCollectionLength; } var column = GetColumn(i); WriteDouble(dataWs, column, 6, aic.ParentModule.SampleRateHz); //WriteString(dataWs, column, 7, shareStringPart, aic.HardwareChannelName); WriteDouble(dataWs, column, 7, aic.ParentModule.AaFilterRateHz); WriteDouble(dataWs, column, 8, (double) 1 + i); WriteString(dataWs, column, 9, shareStringPart, aic.IsoCode); WriteString(dataWs, column, 10, shareStringPart, aic.ChannelName2); WriteString(dataWs, column, 12, shareStringPart, aic.SerialNumber); var filter = Event.Module.Channel.SaeJ211Filter.Parse(aic.SoftwareFilter); WriteDouble(dataWs, column, 13, bFiltering ? filter.CutoffFrequencyHz : 0D); if (bFiltering) { WriteString(dataWs, column, 14, shareStringPart, filter.Name); } WriteString(dataWs, column, 15, shareStringPart, aic.EngineeringUnits); var preTriggerSamples = (double)aic.ParentModule.TriggerSampleNumbers[0] - (double)aic.ParentModule.StartRecordSampleNumber; if (preTriggerSamples < 0) { preTriggerSamples = 0D; } if (preTriggerSamples > (double)aic.ParentModule.NumberOfSamples) { preTriggerSamples = aic.ParentModule.NumberOfSamples; } if (preTriggerSamples > Start * aic.ParentModule.SampleRateHz) { preTriggerSamples = Math.Truncate(Start * aic.ParentModule.SampleRateHz); } WriteDouble(dataWs, column, 17, preTriggerSamples); var postTriggerSamples = (double)aic.ParentModule.NumberOfSamples - preTriggerSamples; if (postTriggerSamples < 0) { postTriggerSamples = 0D; } if (postTriggerSamples > Stop * aic.ParentModule.SampleRateHz) { postTriggerSamples = Math.Truncate(Stop * aic.ParentModule.SampleRateHz); } WriteDouble(dataWs, column, 18, postTriggerSamples); WriteDouble(dataWs, column, 19, aic.DataZeroLevelAdc); var ds = GetDataScaler(aic); WriteDouble(dataWs, column, 20, ds.GetAdcToEuScalingFactor()); WriteDouble(dataWs, column, 21, ds.GetAdcToMvScalingFactor()); WriteString(dataWs, column, 23, shareStringPart, string.Format("Chan {0}: {1}", 1 + i, 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(); var replacementPartId = workbookPart.GetIdOfPart(replacementPart); var reader = OpenXmlReader.Create(worksheetPart); var writer = OpenXmlWriter.Create(replacementPart); var filteredData = new double[test.Channels.Count][]; if (Filtered) { for (var iChannel = 0; iChannel < test.Channels.Count; iChannel++) { var aic = test.Channels[iChannel] as Test.Module.AnalogInputChannel; if (null == aic) { continue; } var ds = GetDataScaler(aic); filteredData[iChannel] = new double[aic.PersistentChannelInfo.Data.Length]; for (var iSampleIdx = 0; iSampleIdx < aic.PersistentChannelInfo.Data.Length; iSampleIdx ++) { filteredData[iChannel][iSampleIdx] = ds.GetEU(aic.PersistentChannelInfo.Data[iSampleIdx]); } var filter = Event.Module.Channel.SaeJ211Filter.Parse(aic.SoftwareFilter); filteredData[iChannel] = filter.Apply(filteredData[iChannel], aic.ParentModule.SampleRateHz, UseLegacyTDCSoftwareFiltering); } } 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(24 + sampleIndex)}; writer.WriteStartElement(row); for (var iChannel = 0; iChannel < test.Channels.Count; iChannel ++) { var aic = test.Channels[iChannel] as Test.Module.AnalogInputChannel; if (null == aic) { continue; } var ds = GetDataScaler(aic); var dStartTime = (double)aic.ParentModule.StartRecordSampleNumber / (double)aic.ParentModule.SampleRateHz; if (aic.ParentModule.TriggerSampleNumbers.Count > 0) { dStartTime -= (double)aic.ParentModule.TriggerSampleNumbers[0] / (double)aic.ParentModule.SampleRateHz; } var thisChannelsIndexAtCurrentTime = (int)sampleIndex - (int)((dStartTime - minStartTime) * aic.ParentModule.SampleRateHz); if (0 == iChannel) { var time = (aic.ParentModule.StartRecordSampleNumber - (double) aic.ParentModule.TriggerSampleNumbers[0] + thisChannelsIndexAtCurrentTime) / aic.ParentModule.SampleRateHz; var c = new Cell { CellValue = new CellValue(time.ToString(CultureInfo.InvariantCulture)) }; writer.WriteElement(c); } var value = Filtered ? filteredData[iChannel][thisChannelsIndexAtCurrentTime] : ds.GetEU(aic.PersistentChannelInfo.Data[thisChannelsIndexAtCurrentTime]); 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().Where(s => s.Id.Value.Equals(origninalSheetId)).First(); sheet.Id.Value = replacementPartId; workbookPart.DeletePart(worksheetPart); filteredData = null; } foreach (var channel in test.Channels) { var aic = channel as Test.Module.AnalogInputChannel; if (null == aic) continue; aic.PersistentChannelInfo.UnSet(); System.Runtime.GCSettings.LatencyMode = System.Runtime.GCLatencyMode.Batch; System.Runtime.GCSettings.LargeObjectHeapCompactionMode = System.Runtime.GCLargeObjectHeapCompactionMode.CompactOnce; GC.Collect(); } } catch (System.Exception ex) { if (null != errorEventHandler) { errorEventHandler(this, ex); } } finally { if (null != tickEventHandler) { tickEventHandler(this, 100D); } if (null != endEventHandler) { endEventHandler(this); } } } #endregion /// /// constructs the writer with a given file and encoding /// /// /// internal Writer(File fileType, int encoding) : base( fileType, encoding) { ExportADC = false; ExportEU = true; ExportMv = false; WriterParent = fileType; } /// /// initializes the writer /// /// /// /// /// /// /// /// /// /// /// /// /// /// /// /// 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) { } } } }