private static void ExportDataTableCollection(System.Data.DataTableCollection WorksheetCollection, SpreadsheetDocument spreadsheet)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
// Create the Excel file 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 DocumentFormat.OpenXml.Spreadsheet.BookViews(new DocumentFormat.OpenXml.Spreadsheet.WorkbookView()));
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart <WorkbookStylesPart>("rIdStyles");
DocumentFormat.OpenXml.Spreadsheet.Stylesheet stylesheet = new DocumentFormat.OpenXml.Spreadsheet.Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
foreach (System.Data.DataTable dt in WorksheetCollection)
{
// For each worksheet you want to create
string worksheetName = dt.TableName;
// Create worksheet part, and add it to the sheets collection in workbook
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>();
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName
};
// If you want to define the Column Widths for a Worksheet, you need to do this *before* appending the SheetData
// http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/1d93eca8-2949-4d12-8dd9-15cc24128b10/
sheets.Append(sheet);
// Append this worksheet's data to our Workbook, using OpenXmlWriter, to prevent memory problems
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
worksheetNumber++;
}
spreadsheet.WorkbookPart.Workbook.Save();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
/// <summary>
/// Adds the basic styles to the workbook
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <returns>True if succesful</returns>
public static bool AddBasicStyles(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet)
{
DocumentFormat.OpenXml.Spreadsheet.Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
// Numbering formats (x:numFmts)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>(new DocumentFormat.OpenXml.Spreadsheet.NumberingFormats(), 0);
// Currency
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormat>(
new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat()
{
NumberFormatId = 164,
FormatCode = "#,##0.00"
+ "\\ \"" + System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencySymbol + "\""
}, 0);
// Fonts (x:fonts)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fonts>(new DocumentFormat.OpenXml.Spreadsheet.Fonts(), 1);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fonts>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Font>(
new DocumentFormat.OpenXml.Spreadsheet.Font()
{
FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize()
{
Val = 11
},
FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName()
{
Val = "Calibri"
}
}, 0);
// Fills (x:fills)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fills>(new DocumentFormat.OpenXml.Spreadsheet.Fills(), 2);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fill>(
new DocumentFormat.OpenXml.Spreadsheet.Fill()
{
PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()
{
PatternType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.PatternValues>()
{
Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.None
}
}
}, 0);
// Borders (x:borders)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Borders>(new DocumentFormat.OpenXml.Spreadsheet.Borders(), 3);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Borders>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Border>(
new DocumentFormat.OpenXml.Spreadsheet.Border()
{
LeftBorder = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(),
RightBorder = new DocumentFormat.OpenXml.Spreadsheet.RightBorder(),
TopBorder = new DocumentFormat.OpenXml.Spreadsheet.TopBorder(),
BottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(),
DiagonalBorder = new DocumentFormat.OpenXml.Spreadsheet.DiagonalBorder()
}, 0);
// Cell style formats (x:CellStyleXfs)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats(), 4);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
}, 0);
// Cell formats (x:CellXfs)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellFormats(), 5);
// General text
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
FormatId = 0,
NumberFormatId = 0
}, 0);
// Date
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 22,
FontId = 0,
FillId = 0,
BorderId = 0
},
1);
// Currency
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 164,
FontId = 0,
FillId = 0,
BorderId = 0
},
2);
// Percentage
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 10,
FontId = 0,
FillId = 0,
BorderId = 0
},
3);
stylesheet.Save();
return(true);
}
