private void AddHeaderToRow(ExcelWorkbook ew, Worksheet ws, int level, int totalLevels, int row, DataTable dt, List <ExcelDataTableFormat> formats, Dictionary <string, Style> styleCollection)
{
int currentColumn = 0;
ExcelDataTableFormat format = formats != null && level < formats.Count ? formats[level] : ExcelDataTableFormat.GetDefaultFormat(level, totalLevels);
for (int c = 0; c < dt.Columns.Count; c++)
{
string columnName = dt.Columns[c].ColumnName;
if (!IsColumnHidden(columnName))
{
string name = columnName;
if (columnRenames != null && columnRenames.ContainsKey(columnName))
{
name = columnRenames[columnName];
}
WriteCell(ew, ws, level, totalLevels, row, currentColumn, name, format, true, styleCollection);
if (format.ColumnWidths.ContainsKey(columnName))
{
format.ColumnWidths["COL_" + currentColumn.ToString()] = format.ColumnWidths[columnName];
}
currentColumn++;
}
}
}
private void AddDataToRow(ExcelWorkbook ew, Worksheet ws, int level, int totalLevels, ref int row, List <DataTable> dts, DataRow dr, List <ExcelDataTableFormat> formats, Dictionary <string, Style> styleCollection)
{
int currentColumn = 0;
DataTable dt = dts[level];
ExcelDataTableFormat format = formats != null && level < formats.Count ? formats[level] : ExcelDataTableFormat.GetDefaultFormat(level, totalLevels);
for (int c = 0; c < dt.Columns.Count; c++)
{
string columnName = dt.Columns[c].ColumnName;
if (!IsColumnHidden(columnName))
{
object data = dr[dt.Columns[c]];
WriteCell(ew, ws, level, totalLevels, row, currentColumn, data, format, false, styleCollection);
if (format.ColumnWidths.ContainsKey(columnName))
{
format.ColumnWidths["COL_" + currentColumn.ToString()] = format.ColumnWidths[columnName]; // store the width off for later
}
currentColumn++;
}
}
row++;
if ((level + 1) < dts.Count)
{
DataTable childDt = dts[level + 1];
ExcelDataTableFormat childFormat = formats != null && (level + 1) < formats.Count ? formats[level + 1] : ExcelDataTableFormat.GetDefaultFormat(level, totalLevels);
if (childDt != null && childDt.Rows.Count > 0)
{
List <DataRow> childRows = GetChildRowsForCurrentRow(dt, dr, childDt);
if (childRows != null && childRows.Count > 0)
{
int rowStart = row;
if (childFormat.ShowHeader)
{
AddHeaderToRow(ew, ws, level + 1, totalLevels, row++, childDt, formats, styleCollection);
}
for (int c = 0; c < childRows.Count; c++)
{
DataRow childRow = childRows[c];
AddDataToRow(ew, ws, level + 1, totalLevels, ref row, dts, childRow, formats, styleCollection);
childRow.Delete(); // by removing the child rows that have matches as we progress we save time for future comparisons (each child row can have only one match)
}
int rowFinish = row;
if (GroupRows)
{
if (rowFinish - rowStart > 0)
{
ws.Cells.GroupRows(rowStart, rowFinish - 1);
}
}
childDt.AcceptChanges();
}
}
}
}
/// <summary>
/// This function does the work of combining multiple levels of data tables into one worksheet.
/// </summary>
/// <param name="dts"></param>
/// <param name="formats"></param>
private void ExportDataTablesToWorksheet(ExcelWorkbook ew, Worksheet ws, List <DataTable> dts, List <ExcelDataTableFormat> formats)
{
DataTable dt = dts[0];
Dictionary <string, Style> styleCollection = new Dictionary <string, Style>(); // we use this to cache and share styles because Excel can only handle a certain number of unique styles before it errors
int level = 0;
int row = 0;
ExcelDataTableFormat format = formats != null && level < formats.Count ? formats[level] : ExcelDataTableFormat.GetDefaultFormat(level, dts.Count);
if (format.ShowHeader)
{
AddHeaderToRow(ew, ws, level, dts.Count, row++, dt, formats, styleCollection);
}
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
int rowStart = row;
AddDataToRow(ew, ws, level, dts.Count, ref row, dts, dr, formats, styleCollection);
int rowFinish = row;
if (GroupRows && level < dts.Count)
{
if (rowFinish - rowStart > 1)
{
ws.Cells.GroupRows(rowStart + 1, rowFinish - 1);
}
}
}
}
}