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