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); }