static void WriteExcelDocument(DataSet dataset, SpreadsheetDocument spreadsheet)
{
// Create the Excel document contents.
// This function is used when creating an Excel file either writing to a file, or writing to a MemoryStream.
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
var workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart <WorkbookStylesPart>("rIdStyles");
var stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
foreach (DataTable dataTable in dataset.Tables)
{
// For each worksheet you want to create
var workSheetID = "rId" + worksheetNumber.ToString();
var worksheetName = dataTable.TableName;
var newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet();
// create sheet data
newWorksheetPart.Worksheet.AppendChild(new SheetData());
// save worksheet
ExcelService.WriteDataTableToExcelWorksheet(dataTable, newWorksheetPart);
newWorksheetPart.Worksheet.Save();
// create the worksheet to workbook relation
if (worksheetNumber == 1)
{
spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());
}
spreadsheet.WorkbookPart.Workbook.GetFirstChild <Sheets>().AppendChild(new Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = (uint)worksheetNumber,
Name = dataTable.TableName
});
worksheetNumber++;
}
spreadsheet.WorkbookPart.Workbook.Save();
}
/// <summary>
/// Creates a stream that contains Excel document from this data-set
/// </summary>
/// <param name="dataset">DataSet containing the data to be written to the Excel in OpenXML format</param>
/// <returns>A stream that contains the Excel document</returns>
/// <remarks>The stream that contains an Excel document in OpenXML format with MIME type is 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'</remarks>
public static MemoryStream SaveAsExcel(this DataSet dataset)
{
// check dataset
if (dataset == null || dataset.Tables == null || dataset.Tables.Count < 1)
{
throw new InformationNotFoundException("DataSet must be not null and contains at least one table");
}
// write dataset into stream
var stream = UtilityService.CreateMemoryStream();
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
{
ExcelService.WriteExcelDocument(dataset, document);
}
return(stream);
}
static void WriteDataTableToExcelWorksheet(DataTable dataTable, WorksheetPart worksheetPart)
{
var worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild <SheetData>();
var cellValue = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
var numberOfColumns = dataTable.Columns.Count;
var isNumericColumn = new bool[numberOfColumns];
var excelColumnNames = new string[numberOfColumns];
for (var column = 0; column < numberOfColumns; column++)
{
excelColumnNames[column] = ExcelService.GetExcelColumnName(column);
}
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
// add a row at the top of spreadsheet
var headerRow = new Row
{
RowIndex = rowIndex
};
sheetData.Append(headerRow);
for (int index = 0; index < numberOfColumns; index++)
{
var col = dataTable.Columns[index];
ExcelService.AppendTextCell(excelColumnNames[index] + "1", col.ColumnName, headerRow);
isNumericColumn[index] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32");
}
//
// Now, step through each row of data in our DataTable...
//
double cellNumericValue = 0;
foreach (DataRow dataRow in dataTable.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
// add a row at the top of spreadsheet
var newExcelRow = new Row
{
RowIndex = rowIndex
};
sheetData.Append(newExcelRow);
for (var index = 0; index < numberOfColumns; index++)
{
cellValue = dataRow.ItemArray[index].ToString();
// Create cell with data
if (isNumericColumn[index])
{
// For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
cellValue = cellNumericValue.ToString();
ExcelService.AppendNumericCell(excelColumnNames[index] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
// For text cells, just write the input data straight out to the Excel file.
else
{
ExcelService.AppendTextCell(excelColumnNames[index] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
}
}
