public static SpreadsheetTable GetSpreadsheetTableFromDataTable(DataTable dataTable, string tableName)
{
var table = new SpreadsheetTable(tableName);
table.Columns = new List <SpreadsheetTableColumn>();
table.Rows = new List <SpreadsheetRow>();
foreach (DataColumn dataColumn in dataTable.Columns)
{
table.Columns.Add(new SpreadsheetTableColumn()
{
Name = dataColumn.Caption
});
}
foreach (DataRow dataRow in dataTable.Rows)
{
var row = new SpreadsheetRow();
foreach (var o in dataRow.ItemArray)
{
row.AddCell(new SpreadsheetCell()
{
Value = sanitizeString(o.ToString())
});
}
table.Rows.Add(row);
}
return(table);
}
public static SpreadsheetTable GetMonstrosity(string tableName)
{
var table = new SpreadsheetTable(tableName);
table.Columns = new List <SpreadsheetTableColumn>();
table.Rows = new List <SpreadsheetRow>();
for (int i = 0; i < 10; i++)
{
table.Columns.Add(new SpreadsheetTableColumn()
{
Name = "Column" + i
});
}
for (int i = 0; i < 500000; i++)
{
var row = new SpreadsheetRow();
for (int j = 0; j < 10; j++)
{
row.AddCell(new SpreadsheetCell()
{
Value = string.Format("{0}-{1}", i, j)
});
}
table.Rows.Add(row);
}
return(table);
}
private void trackMaxChars(SpreadsheetRow row, SpreadsheetLocation location, bool isTableHeaderRow = false)
{
for (var i = 0; i < row.RowCells.Count; i++)
{
var cell = row.RowCells[i];
trackMaxChars(location.ColumnIndex + i, cell, isTableHeaderRow);
}
}
private void addRow(SpreadsheetRow row, int columnIndex, int rowIndex, bool isTableHeaderRow)
{
if (_addAdditionalItemsDisabled)
{
throw new InvalidOperationException("Additional elements addition is disabled");
}
_rows[new SpreadsheetLocation(rowIndex, columnIndex)] = row;
if (_rowWidthsTrackedSoFar < MaxRowWidthsToTrack)
{
trackMaxChars(row, new SpreadsheetLocation(rowIndex, columnIndex), isTableHeaderRow);
_rowWidthsTrackedSoFar++;
}
var newMaxColumnIndex = columnIndex + row.RowCells.Count;
if (newMaxColumnIndex > _maxColumnIndex)
{
_maxColumnIndex = newMaxColumnIndex;
}
if (rowIndex > _maxRowIndex)
{
_maxRowIndex = rowIndex;
}
}
public void AddTable(SpreadsheetTable table, int columnIndex, int rowIndex, HeaderCellStyle headerCellStyle = null)
{
if (_addAdditionalItemsDisabled)
{
throw new InvalidOperationException("Additional elements addition is disabled");
}
_tables[new SpreadsheetLocation(rowIndex, columnIndex)] = table;
var headerRow = new SpreadsheetRow();
for (var i = 0; i < table.Columns.Count; i++)
{
var column = table.Columns[i];
var headerCell = new SpreadsheetCell();
headerCell.Value = column.Name;
if (headerCellStyle != null)
{
headerCell.BackgroundColor = headerCellStyle.BackgroundColor;
headerCell.ForegroundColor = headerCellStyle.ForegroundColor;
headerCell.Font = headerCellStyle.Font;
}
headerRow.AddCell(headerCell);
trackMaxChars(columnIndex + i, headerCell);
}
if (table.ShowHeaderRow)
{
addRow(headerRow, columnIndex, rowIndex, true);
rowIndex++;
}
if (!table.IsInStreamingMode)
{
foreach (var row in table.Rows)
{
AddRow(row, columnIndex, rowIndex);
rowIndex++;
}
}
else
{
var enumerator = table.GetStreamingEnumerator();
// we have to add some rows to calculate column widths(we dont use the excel feature because it's slow when dealing with large amounts of data)
// the rest of the rows will be stream directyl to the openxmlwriter
// MaxRowWidthsToTrackPerTable rows is not so much that it would cause an memory issue, the rest will be written one by one
var rowsToGet = MaxRowWidthsToTrackPerTable;
var endOfTableIndex = rowIndex;
while (rowsToGet > 0 && enumerator.MoveNext())
{
AddRow(enumerator.Current, columnIndex, rowIndex);
rowsToGet--;
rowIndex++;
endOfTableIndex++;
}
// if we have more items to stream, disable manually adding rows after the table
_addAdditionalItemsDisabled = enumerator.Current != null;
if (endOfTableIndex > _maxRowIndex)
{
_maxRowIndex = endOfTableIndex;
}
}
}
public void AddRow(SpreadsheetRow row, int columnIndex, int rowIndex)
{
addRow(row, columnIndex, rowIndex, false);
}
public void AddRow(SpreadsheetRow row)
{
AddRow(row, 1, _maxRowIndex + 1);
}