I'm trying to export data from a DataTable into an Excel file using SpreadsheetGear. The worksheet in the Excel file will be copied from a template with column headers and specific formatting. For ease of maintenance (change a stored proc and the template to change report content, rather than redeploy a service) I don't want to do certain column formatting in code, namely column fill colors.
I use SpreadsheetGear's CopyFromDataTable to do the export. However, the InsertCells flag does not cause the data to take on the format (column fill colors) of the rows it's inserted in as the description notes ("This allows the cells to be preformatted").
I'm working around this by not using the InsertCells flag and instead just setting the column fill colors on the entire columns in the template. However, this causes the entire column to be colored, including beyond the used range. I want to limit the formatting to the used range.
Is there some way to clear any formatting outside of the UsedRange? As I'm writing this I'm realizing this approach is convoluted. But the formatting is mandatory and I want to simplify maintenance to only require a stored proc change and template change. I'm open to any other approach there might be to facilitate that.
The SetDataFlags.InserCells enum option should allow for formatting to be picked up in the range populated only by your DataTable. However, there are exact requirements to make this work properly, as stated in the documentation:
"When specifying this flag [InsertCells], you must provide a range with exactly two rows for data, preceded by a row for column headers if SpreadsheetGear.Data.SetDataFlags.NoColumnHeaders is not specified."
The "two rows for data" should be formatted however you require (interior/fill color, etc). The Explorer Samples Solutions that came with SpreadsheetGear (found in the SpreadsheetGear folder in your Start Menu/Screen) includes a sample that demonstrates this, under Reporting > DataSet to Workbook.
UPDATE
Below is an example that demonstrates how to use the InsertCells flag in combination with NoColumnHeaders. This code assumes you have a worksheet already setup with the formatted range and that you can obtain a DataTable whose size corresponds to the number of columns in your formatted range.
using SpreadsheetGear;
using SpreadsheetGear.Data;
// Generate DataTable. For this example, let's say it is 5 columns wide and
// 10 rows deep...
DataTable dataTable = // ...get DataTable...
// We're not interested in a "Column Header" row, so for the above dataTable,
// an IRange 5 columns wide and 2 rows deep needs to be specified where those
// two rows are already setup with the desired formatting. In this case B2:F3
// are formatted accordingly.
worksheet.Cells["B2:F3"].CopyFromDataTable(dataTable, SetDataFlags.InsertCells |
SetDataFlags.NoColumnHeaders);
// Sheet should how have B2:F11 populated with your dataTable data, with all
// rows having picked up the formatting as originally specified in B2:F3