public void DataTableToExcel(bool displayTime, bool displayQuality, bool localTime,
IHistoricalQuery[] queries, IHistoricalPropertyValue[] values)
{
int i = 0;
int startrow = 1, row = 1;
int qcol, tcol, vcol;
int unitcols = 1;
_displayTime = displayTime;
_displayQuality = displayQuality;
_localTime = localTime;
_queries = queries;
Excel.Worksheet sheet = ((Excel.Worksheet)_thisAddIn.Application.ActiveWorkbook.ActiveSheet);
sheet.Cells.Clear();
var objectInfoResults = _objectServiceOperations.GetObjectInfos(queries.Select(a => a.PropertyId.GetContext()).ToArray());
var objectInfos = objectInfoResults.Where(a => a.Success).Select(a => a.Value).ToArray();
var serviceInfoResults = _objectServiceOperations.GetServiceInfos(queries.Select(a => a.PropertyId.GetContext()).ToArray());
var serviceInfos = serviceInfoResults.Where(a => a.Success).Select(a => a.Value).ToArray();
vcol = _startCol;
tcol = 0;
qcol = 0;
if (displayTime)
{
tcol = _startCol;
vcol = tcol + 1;
unitcols++;
}
if (displayQuality)
{
qcol = vcol + 1;
unitcols++;
}
foreach (IHistoricalQuery query in queries)
{
row = startrow;
row = WriteLabels(sheet, row, tcol, vcol, qcol, objectInfos[i], serviceInfos[i], query, localTime);
//Write labels
row++;
WriteCommon(sheet, row, vcol, "Values");
if (tcol > 0)
WriteCommon(sheet, row, tcol, "Timestamps");
if (qcol > 0)
WriteCommon(sheet, row, qcol, "Qualities");
//Write timezone
row++;
string timezone;
if (localTime)
timezone = "Local time";
else
timezone = "UTC time";
if (tcol > 0)
WriteCommon(sheet, row, tcol, "(" + timezone + ")");
vcol += unitcols;
if (displayTime)
tcol = vcol - 1;
if (displayQuality)
qcol = vcol + 1;
i++;
}
vcol = _startCol;
tcol = 0;
qcol = 0;
if (displayTime)
{
tcol = _startCol;
vcol = tcol + 1;
}
if (displayQuality)
{
qcol = vcol + 1;
}
startrow = row + 1;
foreach (IHistoricalPropertyValue value in values)
{
row = startrow;
if (value.Values.Length == 0)
{
WriteCommon(sheet, row, vcol, "<empty dataset>");
if (tcol > 0)
WriteCommon(sheet, row, tcol, "<empty dataset>");
if (qcol > 0)
WriteCommon(sheet, row, qcol, "<empty dataset>");
}
foreach (IHistoricalValue v in value.Values)
{
WriteDataTableValue(sheet, row, tcol, vcol, qcol, v, localTime);
row++;
}
vcol += unitcols;
if (displayTime)
tcol = vcol - 1;
if (displayQuality)
qcol = vcol + 1;
}
_dataStyle = 0;
sheet.Columns.AutoFit();
_thisAddIn.CloseBrowse();
}
public void EventListToExcel(bool displayTime, bool displayQuality, bool localTime,
IHistoricalQuery[] queries, IHistoricalPropertyValue[] historicalPropertyValues)
{
int i = 0;
int startrow = 1, row = 1;
int qcol, tcol, vcol, icol;
_displayTime = displayTime;
_displayQuality = displayQuality;
_localTime = localTime;
_queries = queries;
Excel.Worksheet sheet = ((Excel.Worksheet)_thisAddIn.Application.ActiveWorkbook.ActiveSheet);
sheet.Cells.Clear();
var objectInfoResults = _objectServiceOperations.GetObjectInfos(queries.Select(a => a.PropertyId.GetContext()).ToArray());
var objectInfos = objectInfoResults.Where(a => a.Success).Select(a => a.Value).ToArray();
var serviceInfoResults = _objectServiceOperations.GetServiceInfos(queries.Select(a => a.PropertyId.GetContext()).ToArray());
var serviceInfos = serviceInfoResults.Where(a => a.Success).Select(a => a.Value).ToArray();
icol = _startCol;
vcol = _startCol + 1;
tcol = 0;
qcol = 0;
if (displayTime)
{
tcol = _startCol;
icol = _startCol + 1;
vcol = _startCol + 2;
}
if (displayQuality)
{
qcol = vcol + 1;
}
foreach (IHistoricalQuery query in queries)
{
row = startrow;
row = WriteLabels(sheet, row, _startCol, _startCol + i + 1, 0, objectInfos[i], serviceInfos[i], query, localTime);
//Write value labels
row++;
if (tcol > 0)
WriteCommon(sheet, row, tcol, "Timestamps");
WriteCommon(sheet, row, icol, "Item ID");
WriteCommon(sheet, row, vcol, "Values");
if (qcol > 0)
WriteCommon(sheet, row, qcol, "Qualities");
//Write timezone
row++;
string timezone;
if (localTime)
timezone = "Local time";
else
timezone = "UTC time";
if (tcol > 0)
WriteCommon(sheet, row, tcol, "(" + timezone + ")");
i++;
}
int[] indexes = new int[historicalPropertyValues.Length];
for (i = 0; i < indexes.Length; i++)
{
indexes[i] = 0;
}
while (true)
{
IHistoricalValue historicalValue = null;
int t = 0;
for (i = 0; i < indexes.Length; ++i)
{
if (indexes[i] < historicalPropertyValues[i].Values.Length)
{
historicalValue = historicalPropertyValues[i].Values[indexes[i]];
t = i;
break;
}
}
if (historicalValue == null)
{
break;
}
for (i = t + 1; i < indexes.Length; ++i)
{
if (indexes[i] < historicalPropertyValues[i].Values.Length)
{
if (historicalPropertyValues[i].Values[indexes[i]].Time < historicalValue.Time)
{
historicalValue = historicalPropertyValues[i].Values[indexes[i]];
t = i;
}
}
}
//var formattedTime = localTime ? _valueFormatter.Format(historicalValue.Time.ToLocalTime()) : _valueFormatter.Format(historicalValue.Time);
//var formattedValue = _valueFormatter.Format(historicalValue.Value);
WriteCommon(sheet, row, icol, objectInfos[t].Name);
WriteNumber(sheet, row, vcol, historicalValue.Value);
if (tcol > 0)
{
DateTime dt;
if (localTime)
dt = historicalValue.Time.ToLocalTime();
else
dt = historicalValue.Time;
WriteTime(sheet, row, tcol, dt);
}
if (qcol > 0)
{
WriteCommon(sheet, row, qcol, historicalValue.Quality.Quality);
}
row++;
indexes[t]++;
if (row > MAX_ROWS)
break;
}
_dataStyle = 1;
sheet.Columns.AutoFit();
_thisAddIn.CloseBrowse();
}
