/// <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); }