/// <summary>
/// Writes a DataTable object to a StreamWriter
/// </summary>
/// <param name="reader"></param>
/// <param name="textWriter"></param>
/// <param name="sep"></param>
/// <param name="shouldQuoteStrings"></param>
/// <param name="isoDateFormat"></param>
/// <param name="statusProgress"></param>
/// <returns></returns>
public static int WriteToStream(this ADOTabular.AdomdClientWrappers.AdomdDataReader reader, TextWriter textWriter, string sep, bool shouldQuoteStrings, string isoDateFormat, IStatusBarMessage statusProgress)
{
int iMaxCol = reader.FieldCount - 1;
int iRowCnt = 0;
using (var csvWriter = new CsvHelper.CsvWriter(textWriter, CultureInfo.InvariantCulture))
{
// CSV Writer config
csvWriter.Configuration.Delimiter = sep;
// Datetime as ISOFormat
csvWriter.Configuration.TypeConverterOptionsCache.AddOptions(
typeof(DateTime),
new CsvHelper.TypeConversion.TypeConverterOptions()
{
Formats = new string[] { isoDateFormat }
});
// write out clean column names
foreach (var colName in reader.CleanColumnNames())
{
csvWriter.WriteField(colName);
}
csvWriter.NextRecord();
while (reader.Read())
{
iRowCnt++;
for (int iCol = 0; iCol < reader.FieldCount; iCol++)
{
var fieldValue = reader[iCol];
// quote all string fields
if (reader.GetFieldType(iCol) == typeof(string))
{
if (reader.IsDBNull(iCol))
{
csvWriter.WriteField("", shouldQuoteStrings);
}
else
{
csvWriter.WriteField(fieldValue.ToString(), shouldQuoteStrings);
}
}
else
{
csvWriter.WriteField(fieldValue);
}
}
csvWriter.NextRecord();
if (iRowCnt % 1000 == 0)
{
statusProgress.Update($"Written {iRowCnt:n0} rows to the file output");
}
}
}
return(iRowCnt);
}
/// <summary>
/// Writes a ADODataReader to a Worksheet
/// </summary>
public static int WriteToWorksheet(ADOTabular.AdomdClientWrappers.AdomdDataReader reader, XlsxWriter xlsxWriter, IStatusBarMessage statusProgress, IQueryRunner runner)
{
int iMaxCol = reader.FieldCount - 1;
int iRowCnt = 0;
ADOTabularColumn daxCol;
int colIdx = 0;
XlsxStyle[] columnStyles = new XlsxStyle[reader.FieldCount];
var headerStyle = new XlsxStyle(
new XlsxFont("Segoe UI", 9, Color.White, bold: true),
new XlsxFill(Color.FromArgb(0, 0x45, 0x86)),
XlsxStyle.Default.Border,
XlsxStyle.Default.NumberFormat,
XlsxAlignment.Default);
var wrapStyle = XlsxStyle.Default.With(new XlsxAlignment(vertical: Vertical.Top, wrapText: true));
var defaultStyle = XlsxStyle.Default;
// Write out Header Row
xlsxWriter.SetDefaultStyle(headerStyle).BeginRow();
foreach (var colName in reader.CleanColumnNames())
{
// write out the column name
xlsxWriter.Write(colName);
// cache the column formatstrings as Excel Styles
reader.Connection.Columns.TryGetValue(reader.GetName(colIdx), out daxCol);
if (daxCol != null)
{
columnStyles[colIdx] = GetStyle(daxCol);
}
else
{
columnStyles[colIdx] = defaultStyle;
}
colIdx++;
}
xlsxWriter.SetDefaultStyle(defaultStyle);
while (reader.Read())
{
// check if we have reached the limit of an xlsx file
if (iRowCnt >= 999999)
{
runner.OutputWarning("Results truncated, reached the maximum row limit for an Excel file");
break;
}
// increment row count
iRowCnt++;
// start outputting the next row
xlsxWriter.BeginRow();
for (int iCol = 0; iCol < reader.FieldCount; iCol++)
{
var fieldValue = reader[iCol];
switch (fieldValue)
{
case int i:
xlsxWriter.Write(i, columnStyles[iCol]);
break;
case double dbl:
xlsxWriter.Write(dbl, columnStyles[iCol]);
break;
case decimal dec:
xlsxWriter.Write(dec, columnStyles[iCol]);
break;
case DateTime dt:
xlsxWriter.Write(dt, columnStyles[iCol]);
break;
case string str:
if (str.Contains("\n") || str.Contains("\r"))
{
xlsxWriter.Write(str, wrapStyle);
}
else
{
xlsxWriter.Write(str);
}
break;
case bool b:
xlsxWriter.Write(b.ToString()); // Writes out TRUE/FALSE
break;
case null:
xlsxWriter.Write();
break;
case long lng:
if (lng < int.MaxValue && lng > int.MinValue)
{
xlsxWriter.Write(Convert.ToInt32(lng), columnStyles[iCol]);
}
else // TODO - should we be converting large long values to double??
{
xlsxWriter.Write(lng.ToString()); // write numbers outside the size of int as strings
}
break;
default:
xlsxWriter.Write(fieldValue.ToString());
break;
}
}
if (iRowCnt % 1000 == 0)
{
statusProgress.Update($"Written {iRowCnt:n0} rows to the file output");
}
}
return(iRowCnt);
}