Open XML SDK, a performant library for office automation

Microsoft.Office.Interop

Most of office automation task can be done with the Interop library provided by office package. It work fine. When come to performance it will become troublesome.

Microsoft also provides an Opensourrce SDK based on XML called Open XML SDK. It is a big library and it is bulky one.

One of the corn of Interop is that it require the MS Office to be installed on the local machines. Open XML doesn't require the driver.

When it comes to web apps Open XML could be a wiser option for developers.

Open XML SDK

Open XML can be added to any .Net project and can be used to generate Spread sheets, documents and presentations without office driver.

For new comers the Spread sheet generation with Open XML could be tedious.

One option for tackle this is, use another wrapper library upon Open XML SDK called Closed XML , which is far more simpler than the Open XML SDK, even though need not sacrifice any features the underlying library offers.

Open XML Spread sheet generation

We need to construct from cell style to data in order to create a work book with worksheet.

WorkbookPart workbookPart = document.AddWorkbookPart();
                    workbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                    WorkbookStylesPart sp = workbookPart.AddNewPart<WorkbookStylesPart>();
                    sp.Stylesheet = new DocumentFormat.OpenXml.Spreadsheet.Stylesheet();
                    Stylesheet stylesheet = sp.Stylesheet;
                    stylesheet.Fonts = new DocumentFormat.OpenXml.Spreadsheet.Fonts();
                    stylesheet.CellFormats = new DocumentFormat.OpenXml.Spreadsheet.CellFormats();



                    Font font = new Font(new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11 }, new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = HexBinaryValue.FromString("4b53cb") }, new DocumentFormat.OpenXml.Spreadsheet.Bold());


                    Font font2 = new Font(new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 15 }, new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = HexBinaryValue.FromString("c93653") }, new DocumentFormat.OpenXml.Spreadsheet.Italic());
                    stylesheet.Fonts.Append(font);
                    stylesheet.Fonts.Append(font2);

                    DocumentFormat.OpenXml.Spreadsheet.Fill fill = new DocumentFormat.OpenXml.Spreadsheet.Fill() { };
                    DocumentFormat.OpenXml.Spreadsheet.PatternFill patternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill() { PatternType = PatternValues.Solid };
                    DocumentFormat.OpenXml.Spreadsheet.BackgroundColor backgroundColor = new DocumentFormat.OpenXml.Spreadsheet.BackgroundColor() { Rgb = HexBinaryValue.FromString("ed5976") };
                    DocumentFormat.OpenXml.Spreadsheet.ForegroundColor foregroundColor = new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor() { Rgb = HexBinaryValue.FromString("ed5976") };

                    patternFill.Append(backgroundColor);
                    patternFill.Append(foregroundColor);

                    fill.Append(patternFill);
                    stylesheet.Fills = new Fills();
                    stylesheet.Fills.AppendChild(fill);

                    DocumentFormat.OpenXml.Spreadsheet.Border border2 = new DocumentFormat.OpenXml.Spreadsheet.Border();

                    DocumentFormat.OpenXml.Spreadsheet.LeftBorder leftBorder2 = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder() { Style = BorderStyleValues.Thin };
                    Color color1 = new Color() { Indexed = (UInt32Value)64U };

                    leftBorder2.Append(color1);

                    RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };
                    Color color2 = new Color() { Indexed = (UInt32Value)64U };

                    rightBorder2.Append(color2);

                    DocumentFormat.OpenXml.Spreadsheet.TopBorder topBorder2 = new DocumentFormat.OpenXml.Spreadsheet.TopBorder() { Style = BorderStyleValues.Thin };
                    Color color3 = new Color() { Indexed = (UInt32Value)64U };

                    topBorder2.Append(color3);

                    DocumentFormat.OpenXml.Spreadsheet.BottomBorder bottomBorder2 = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder() { Style = BorderStyleValues.Thin };
                    Color color4 = new Color() { Indexed = (UInt32Value)64U };

                    bottomBorder2.Append(color4);
                    DiagonalBorder diagonalBorder2 = new DiagonalBorder();

                    border2.Append(leftBorder2);
                    border2.Append(rightBorder2);
                    border2.Append(topBorder2);
                    border2.Append(bottomBorder2);
                    border2.Append(diagonalBorder2);

                    stylesheet.Borders = new Borders();

                    stylesheet.Borders.Append(border2);
                    CellFormat cellFormat1 = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 };
                    CellFormat cellFormat2 = new CellFormat() { FontId = 1, FillId = 0 };
                    CellFormat cellFormat3 = new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) { FontId = 1, FillId = 0 };

                    stylesheet.CellFormats.Append(cellFormat1);
                    stylesheet.CellFormats.Append(cellFormat2);
                    stylesheet.CellFormats.Append(cellFormat3);
                    stylesheet.Save();

                    SheetData sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    MergeCells mergeCells = new MergeCells();
                    Row workRow = new Row();
                      workRow.Append(CreateCell("Expense Tracker", 0));
                      
                        sheetData.Append(workRow);
                        
                        
                    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

                    Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Trial" };


                    sheets.Append(sheet);


                    workbookPart.Workbook.Save();
}
                    

The part objects (workbookpart, worksheetpart, stylesheetpart) hold setting of the worksheets and workbook. Worksheet data is stored in sheetdata.

Cell styles can be customized by creating cellformts which can be a combinations of fonts,fills,borders ect.

Now let's look at the Closed XML example

Closed XML example

 var workbook = new XLWorkbook();
            var ws = workbook.Worksheets.Add("TRIAL");

            var columnFromWorksheet = ws.Column(1);
            columnFromWorksheet.Cell(1).Value = "Expense Tracker";
            var columnFromRange = ws.Range("A1:K2").Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
              workbook.SaveAs("closedxl.xlsx");
              

I hope the article helps you.