public void WriteDataTableToResponse(DataTable dt, HttpResponse response, bool endResponse = true)
{
WTS.Util.ExcelWorkbook ew = new WTS.Util.ExcelWorkbook();
Worksheet ws = ew.Workbook.Worksheets[0];
ws.Cells.ImportDataTable(dt, true, 0, 0, false, false);
ws.AutoFitColumns();
ew.WriteToResponse(response, true);
}
/// <summary>
/// This version allows you to specify multiple worksheets worth of data sets.
/// </summary>
/// <param name="dtSheets"></param>
/// <param name="response"></param>
/// <param name="endResponse"></param>
public void WriteMultiLevelCrosswalkToResponse(
List <List <DataTable> > dtSheets,
HttpResponse response,
bool endResponse = true)
{
WTS.Util.ExcelWorkbook ew = new WTS.Util.ExcelWorkbook();
if (dtSheets == null)
{
throw new InvalidDataException("Data sheets cannot be null.");
}
ew.Workbook.Worksheets.Clear();
for (int i = 0; i < dtSheets.Count; i++)
{
ew.Workbook.Worksheets.Add();
}
for (int i = 0; i < dtSheets.Count; i++)
{
if (sheetNames != null && sheetNames.ContainsKey(i))
{
ew.Workbook.Worksheets[i].Name = sheetNames[i];
}
}
for (int i = 0; i < dtSheets.Count; i++)
{
Worksheet ws = ew.Workbook.Worksheets[i];
List <DataTable> dts = dtSheets[i];
if (dts != null && dts.Count > 0)
{
List <ExcelDataTableFormat> formats = null;
if (sheetFormats.ContainsKey(i))
{
formats = sheetFormats[i];
}
ExportDataTablesToWorksheet(ew, ws, dts, formats);
ws.AutoFitColumns();
// now that we've auto-fit columns, we go back and re-set the widths for columns that have explicit preferences
for (int f = 0; formats != null && f < formats.Count; f++)
{
ExcelDataTableFormat format = formats[f];
foreach (string key in format.ColumnWidths.Keys)
{
if (key.StartsWith("COL_"))
{
var width = format.ColumnWidths[key];
int col = Int32.Parse(key.Substring(4));
ws.Cells.SetColumnWidth(col, width);
}
}
}
// now that we're done, extend all styles out 100 columns
for (int r = 0; r < ws.Cells.Rows.Count; r++)
{
Style lastRowStyle = null;
Style finalRowStyle = null; // this allows us to share the style for the whole row
for (int c = 0; c < 100; c++)
{
Cell cell = ws.Cells[r, c];
if (cell != null)
{
if (cell.IsStyleSet)
{
lastRowStyle = cell.GetStyle();
}
else
{
// we've reached the "end" of the rows set in the data tables
if (finalRowStyle == null)
{
finalRowStyle = new Style();
finalRowStyle.Copy(lastRowStyle);
finalRowStyle.IsTextWrapped = false;
}
cell.SetStyle(finalRowStyle);
}
}
}
}
}
}
ew.WriteToResponse(response, true);
}
