Here is a partial method to create a byte array that could be written to an xslx file that takes a DataSet and exports all the tables in that DataSet as sheets in an Excel workbook.... EXCEPT that all I have been able to do so far is get the sheets itself created, I can't for the life of me figure out how to populate those sheets with actual data:
private byte[] DataSetToXlsx (DataSet ds)
{
byte[] bytes;
using (var stream = new MemoryStream())
{
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData()); // This line needs to be here and I don't fully understand why
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
for (int iTable = 0; iTable < ds.Tables.Count; iTable++)
{
DataTable dt = ds.Tables[iTable];
var sheet = new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)iTable + 1,
Name = string.IsNullOrWhiteSpace(dt.TableName) ? $"Sheet {iTable + 1}" : dt.TableName
};
// Now I want to iterate through the DataRows on the table to transfer the values into the sheet, but I just can't figure out how to do it.
sheets.Append(sheet);
}
workbookPart.Workbook.Save();
}
stream.Seek(0, SeekOrigin.Begin);
bytes = stream.ToArray();
}
return bytes;
}
I realize that partly I don't fully grasp the difference between a Worksheet and a Sheet. And as my comments in the code would suggest, I don't fully understand WNY I need to create a single new Worksheet but one Sheet for every DataTable in the DataSet.
But my code does work to create a new workbook, and to put in that workbook a sheet for every DataTable.
But more to the point, I do not understand how to start creating rows in the Sheet instances so that the actual data itself gets reflected. I am just baffled at how to do this. Every attempt I have made compiles just fine but creates corrupted files that Excel can't read.
I would love to know the trick!
Please do not point me to third party libraries, or to Excel interrop.
I figured out the answer - here is my modified code:
private byte[] DataSetToXlsx (DataSet ds)
{
if (ds == null || ds.Tables.Count < 1)
{
return null;
}
byte[] bytes;
using (var stream = new MemoryStream())
{
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
// Setup requirements for minimal spreadsheet document - workbook and sheets.
WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
for (int iTable = 0; iTable < ds.Tables.Count; iTable++)
{
// Add a new worksheet part for the table
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
DataTable dt = ds.Tables[iTable];
// Add sheet to the workbook with ID from worksheet
sheets.Append(new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)iTable + 1,
Name = string.IsNullOrWhiteSpace(dt.TableName) ? $"Sheet {iTable + 1}" : dt.TableName
});
if (dt.Columns.Count < 1)
{
continue; // No data in this table.
}
// Get the sheetData for the grid on which we will place rows.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
CellValues[] columnTypes = new CellValues[dt.Columns.Count];
// Retrieve header data from the table columns and place in first row of the sheet
var row = new Row { RowIndex = 1 };
for (int iColumn = 0; iColumn < dt.Columns.Count; iColumn++)
{
DataColumn dc = dt.Columns[iColumn];
columnTypes[iColumn] = FindCellValuesForColumn(dc);
row.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue(dc.ColumnName) });
}
sheetData?.Append(row);
// Retrieve remaining data from table rows and populate the sheet
for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
{
DataRow dr = dt.Rows[iRow];
row = new Row { RowIndex = (uint)iRow + 2 };
for(int iColumn = 0; iColumn < dt.Columns.Count; iColumn++)
{
row.Append(new Cell { DataType = columnTypes[iColumn], CellValue = CellValueFromObject(dr[iColumn]) });
}
sheetData?.Append(row);
}
}
workbookPart.Workbook.Save();
}
stream.Seek(0, SeekOrigin.Begin);
bytes = stream.ToArray();
}
return bytes;
}
This does depend on a couple of helper methods and a static lookup table which I have also included here:
private static readonly IReadOnlyDictionary<Type, Tuple<CellValues, Func<object, CellValue>>> TypeLookup = new ReadOnlyDictionary<Type, Tuple<CellValues, Func<object, CellValue>>>(new Dictionary<Type, Tuple<CellValues, Func<object, CellValue>>>
{
{ typeof(char), new Tuple<CellValues, Func<object, CellValue>>(CellValues.String, o => new CellValue(o.ToString())) },
{ typeof(string), new Tuple<CellValues, Func<object, CellValue>>(CellValues.String, o => new CellValue((string)o)) },
{ typeof(DateTime), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Date, o => new CellValue((DateTime)o)) },
{ typeof(bool), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Boolean, o => new CellValue((bool)o)) },
{ typeof(byte), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Number, o => new CellValue(Convert.ToInt32(o))) },
{ typeof(short), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Number, o => new CellValue(Convert.ToInt32(o))) },
{ typeof(int), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Number, o => new CellValue((int)o)) },
{ typeof(long), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Number, o => new CellValue(o.ToString())) },
{ typeof(ushort), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Number, o => new CellValue(o.ToString())) },
{ typeof(uint), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Number, o => new CellValue(o.ToString())) },
{ typeof(ulong), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Number, o => new CellValue(o.ToString())) },
{ typeof(float), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Number, o => new CellValue(Convert.ToDouble(o))) },
{ typeof(double), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Number, o => new CellValue((double)o)) },
{ typeof(decimal), new Tuple<CellValues, Func<object, CellValue>>(CellValues.Number, o => new CellValue((decimal)o)) }
});
private static CellValue CellValueFromObject (object obj)
{
return obj == null
? new CellValue()
: TypeLookup.TryGetValue(obj.GetType(), out Tuple<CellValues, Func<object, CellValue>> tuple)
? tuple.Item2(obj)
: new CellValue(Convert.ToString(obj));
}
private static CellValues FindCellValuesForColumn(DataColumn dc)
{
dc = dc ?? throw new ArgumentNullException(nameof(dc));
if (TypeLookup.TryGetValue(dc.DataType, out Tuple<CellValues, Func<object, CellValue>> tuple))
{
return tuple.Item1;
}
throw new InvalidOperationException($"No CellValues mapping available for data type \"{dc.DataType.FullName}\" for column \"{dc.ColumnName}\" in table \"{dc.Table?.TableName}\" in dataset \"{dc.Table?.DataSet?.DataSetName}\"");
}