public static string[] CleanColumnNames(this ADOTabular.AdomdClientWrappers.AdomdDataReader reader)
{
string[] columns = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
columns[i] = reader.GetName(i);
}
var columnPattern = new Regex(@"\[(?<col>.*)]\d*$", RegexOptions.Compiled);
var mdxPattern = new Regex(@"\[[^\]]*\].\[[^\]]*\]");
const string MEASURES_MDX = "[Measures].";
var newColumnNames = new Dictionary <string, DaxColumn>();
// If at least one column has the Mdx syntax, identify the result as an MDX query (hoping the assumption is always true...)
//bool isMdxResult = (from DataColumn col in dataTable.Columns
// where col.ColumnName.IndexOf("].[") > 0
// select col).Count() > 0;
bool isMdxResult = (from col in columns
where mdxPattern.IsMatch(col)
select col).Count() > 0;
var measuresColumns = (from col in columns
where col.IndexOf(MEASURES_MDX) >= 0
select col);
bool hasPlainMeasures = (from col in measuresColumns
where col.IndexOf("].[", col.IndexOf(MEASURES_MDX) + MEASURES_MDX.Length) > 0
select col).Count() == 0;
foreach (string columnName in columns)
{
bool removeCaption = false;
string name = columnName;
bool removeSquareBrackets = !isMdxResult;
int measuresMdxPos = name.IndexOf(MEASURES_MDX);// + MEASURES_MDX.Length;
if (isMdxResult)
{
if ((measuresMdxPos >= 0))
{
if ((name.IndexOf("].[", measuresMdxPos + MEASURES_MDX.Length) == -1) &&
(name.IndexOf("].[", 0) == MEASURES_MDX.Length - 2))
{
removeSquareBrackets = true;
}
name = name.Replace(MEASURES_MDX, measuresMdxPos > 0 ? "\n" : "");
}
else
{
removeCaption = hasPlainMeasures;
}
}
if (removeSquareBrackets)
{
var m = columnPattern.Match(name);
if (m.Success)
{
name = m.Groups["col"].Value;
}
// Format column naming for DAX result or if it is a measure name
//int firstBracket = name.IndexOf('[') + 1;
//name = firstBracket == 0 ? name : name.Substring(firstBracket, name.Length - firstBracket - 1);
}
var dc = new DaxColumn()
{
//OriginalCaption = col.Caption,
OriginalName = columnName,
//NewCaption = (removeCaption) ? "" : name,
NewName = name.Replace(' ', '`').Replace(',', '`'),
};
newColumnNames.Add(dc.OriginalName, dc);
//col.Caption = (removeCaption) ? "" : name;
//col.ColumnName = name.Replace(' ', '_');
}
// check for duplicate names
for (var outerIdx = 0; outerIdx < newColumnNames.Count; outerIdx++)
{
for (var innerIdx = outerIdx + 1; innerIdx < newColumnNames.Count; innerIdx++)
{
if (newColumnNames.ElementAt(outerIdx).Value.NewName == newColumnNames.ElementAt(innerIdx).Value.NewName)
{
newColumnNames.ElementAt(outerIdx).Value.UseOriginalName = true;
newColumnNames.ElementAt(innerIdx).Value.UseOriginalName = true;
}
}
}
string[] newNames = new string[columns.Length];
// Update names
for (int i = 0; i < columns.Length; i++)
{
var c = newColumnNames.ElementAt(i).Value;
newNames[i] = c.UseOriginalName ? c.OriginalName : c.NewName;
}
return(newNames);
}
/// <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);
}