Dumping a Dataset into Excel
One question we get asked from time to time is if FlexCel has a method like “DumpDataset” or “CopyFromRecordset” (which is the actual name in OLE Automation). This method should take a dataset and dump it into a sheet.
And the answer is no, we don’t include such a method because it would be too limiting: Normally you will have to customize how the cells are written, and you can’t do that with a single method that does the full export in one piece. So instead, we provide 2 other different solutions to the problem.
FlexCel has reports, which you can use to dump a dataset “as is”, mostly as a DumpDataSet method would, but it also lets you do the formatting.
As an added advantage reports allow you to easily modify the resulting sheets without modifying the code at all, so even the final user can do it.
From reports, the most similar thing to "DumpDataset" is to create an empty spreadsheet, write "<#mydataset.*>" on it, save it, and then run the report against your dataset. You can find an example of this in the demos Generic Reports and Generic Reports 2
Of course those 2 demos focus in “generic” datasets which you don’t know beforehand. If you know what data you are outputting, then you can just write <#dataset.field> in the cells where you want that field written, and format each field as you want.
Reports are explicitly designed to deal with the problem of exporting datasets into Excel, but allowing formatting.
The reports in FlexCel work exclusively by calling the FlexCel API, so anything you can do in a report, you can do it directly with the API. So if for any reason you can’t or don’t want to use reports, you can use a method like this:
static void DumpDataSet(DataTable ds)
{
var xls = new XlsFile(1, TExcelFileFormat.v2019, true);
//Generate the formats we will be using to format dates and times.
var Fmt = xls.GetDefaultFormat;
Fmt.Format = "dd/mm/yyyy hh:mm";
var DateTimeXF = xls.AddFormat(Fmt);
//Now loop over all records and send them to the file.
for (int row = 0; row < ds.Rows.Count; row++)
{
for (int col = 0; col < ds.Columns.Count; col++)
{
object value = ds.Rows[row][col];
if (value is DateTime)
{
xls.SetCellValue(row + 1, col + 1, value, DateTimeXF);
}
else
{
xls.SetCellValue(row + 1, col + 1, value);
}
}
xls.Save("test.xlsx");
}
}