Skip to content

Open XML SDK Quick Guide

Jump to bottom
otakuprojects edited this page Dec 27, 2023 · 5 revisions

Official Documentation

Welcome to the Open XML SDK for Office
https://learn.microsoft.com/en-us/office/open-xml/open-xml-sdk

>Welcome to the Open XML SDK for Office
>Spreadsheets
>Structure of a SpreadsheetML document
>Worksheet Class
https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.worksheet?view=openxml-2.8.1&redirectedfrom=MSDN

(Microsoft always provides API documentation only, lack of sample codes and complete workflow tutorial, or is hard to find, very unfriendly.)

Generate Sample Code

Prepare a simple Excel/Word/Powerpoint, I created an Excel that contains a table with filter, border, and fields of numeric, date, text value.

  1. Open Open XML SDK 2.5 Productivity Tool
  2. Click Open File..., select the target excel then Open
  3. Click Reflect Code
  4. Copy the generated cs code to your Visual Studio

Sample Code and Explanation

// ...

        // Adds child parts and generates content of the specified part.
        private void CreateParts(SpreadsheetDocument document)
        {
            ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
            GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);

            WorkbookPart workbookPart1 = document.AddWorkbookPart();
            GenerateWorkbookPart1Content(workbookPart1);

            WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId3");
            GenerateWorkbookStylesPart1Content(workbookStylesPart1);

            ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId2");
            GenerateThemePart1Content(themePart1);

            WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
            GenerateWorksheetPart1Content(worksheetPart1);

            SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart1 = worksheetPart1.AddNewPart<SpreadsheetPrinterSettingsPart>("rId1");
            GenerateSpreadsheetPrinterSettingsPart1Content(spreadsheetPrinterSettingsPart1);

            SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId4");
            GenerateSharedStringTablePart1Content(sharedStringTablePart1);

            SetPackageProperties(document);
        }
// ...

GenerateWorkbookPart1Content()

  • workbook
  • worksheet(spreadsheet)

GenerateWorkbookStylesPart1Content()

  • Sheet style
  • Numbering format (date/time/datetime/numeric format...)
  • Font (size, font style, color...)
  • Fill (cell background color)
  • Border
  • Cell Style Format (a combination of font master, fill master, border master)
  • Cell formats (associated with the above numbering format master, font master, fill master, border master, cell style format master, ApplyBorder flag, ApplyNumberFormat flag, ApplyAlignment flag, Alignment)
  • Alignment
            Borders borders1 = new Borders(){ Count = (UInt32Value)2U };
            Border border1 = new Border();
//...

            Border border2 = new Border();
//...

            borders1.Append(border1); // index = 0
            borders1.Append(border2); // index = 1

            CellFormats cellFormats1 = new CellFormats(){ Count = (UInt32Value)6U };

            CellFormat cellFormat7 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyBorder = true, ApplyAlignment = true };
            Alignment alignment2 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };

            cellFormat7.Append(alignment2);

            cellFormats1.Append(cellFormat2); // index = 0
            cellFormats1.Append(cellFormat3); // index = 1
            cellFormats1.Append(cellFormat4); // index = 2
            cellFormats1.Append(cellFormat5); // index = 3
            cellFormats1.Append(cellFormat6); // index = 4
            cellFormats1.Append(cellFormat7); // index = 5

The binding ID is an integer number, and the created master is associated with an ascending integer number.
for BorderId = (UInt32Value)1U, which means using border2.

GenerateThemePart1Content()
Excel color theme

GenerateWorksheetPart1Content()
generate content, column style, row style, cell value and style, filter, merge cells

            Cell cell12 = new Cell(){ CellReference = "E5", StyleIndex = (UInt32Value)5U, DataType = CellValues.SharedString };
            CellValue cellValue7 = new CellValue();
            cellValue7.Text = "2";
  • cell12 using index 2 of Shared string content which "HKID no."
  • StyleIndex = (UInt32Value)5U means cellFormat7 which is associated with specified border, alignment

GenerateSpreadsheetPrinterSettingsPart1Content()
Print area settings? (not sure)

GenerateSharedStringTablePart1Content()

  • Shared Text the created shared text could be used by cells in multiple worksheets
            SharedStringTable sharedStringTable1 = new SharedStringTable(){ Count = (UInt32Value)13U, UniqueCount = (UInt32Value)13U };

            SharedStringItem sharedStringItem1 = new SharedStringItem();
            Text text1 = new Text();
            text1.Text = "No.";

            sharedStringItem1.Append(text1);

            SharedStringItem sharedStringItem2 = new SharedStringItem();
            Text text2 = new Text();
            text2.Text = "Name of Patient";

            sharedStringItem2.Append(text2);

            SharedStringItem sharedStringItem3 = new SharedStringItem();
            Text text3 = new Text();
            text3.Text = "HKID no.";

            sharedStringItem3.Append(text3);

//...

SetPackageProperties()

  • Creator
  • Created
  • Modified
  • LastModifiedBy

How to Create Custom Excel

Add, and modify cell, styles in GenerateWorkbookStylesPart1Content()

For table body, create a loop in GenerateWorksheetPart1Content() Create row in loop, add row by something like sheetData1.Append(row18); For the cell not using SharedString, assign the value to Text property

            // Total Amount Reference = "E18",
            Cell cell171 = new Cell() { StyleIndex = (UInt32Value)48U };
            cell171.DataType = CellValues.Number;
            CellValue cellValue47 = new CellValue();
            cellValue47.Text = (sbd.returnValueToString(Convert.ToDecimal(dataList.Sum(x => x.APP_CLAIM_AMT))));
            cell171.Append(cellValue47);
//...
            row17.Append(cell170);
            row17.Append(cell171);
            row17.Append(cell172);
//...

How to Create Custom Word

Free e-Book - Create New Word Document
https://riptutorial.com/Download/openxml.pdf

Clone this wiki locally