954 lines
51 KiB
C#
954 lines
51 KiB
C#
/*
|
|
* 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)
|
|
{
|
|
}
|
|
}
|
|
}
|
|
}
|