Creating Excel File Using OpenXML

Introduction

In this example, let’s create a demo console project in Visual Studio by selecting File -> New -> Project. From the project window, select the Console template type name and select a path for the project, as shown below.

Creating Excel File Using OpenXML

After creating the project, create a Model Class and give a name as TestModel, as shown below.

public class TestModel  
 {              
     public int TestId { get; set; }  
     public string TestName { get; set; }  
     public string TestDesc { get; set; }  
     public DateTime TestDate { get; set; }  
 }

Create one more class and give the name as TestModelList.

public class TestModelList  
{  
    public List<TestModel> testData { get; set; }  
}

Let’s add some hard-coded data to this model. For that, write the below code in the main method of Program.cs file.

class Program  
{  
    static void Main(string[] args)  
    {             
          
        TestModelList tmList = new TestModelList();  
        tmList.testData = new List<TestModel>();  
        TestModel tm = new TestModel();  
        tm.TestId = 1;  
        tm.TestName = "Test1";  
        tm.TestDesc = "Tested 1 time";  
        tm.TestDate = DateTime.Now.Date;  
        tmList.testData.Add(tm);  
  
        TestModel tm1 = new TestModel();  
        tm1.TestId = 2;  
        tm1.TestName = "Test2";  
        tm1.TestDesc = "Tested 2 times";  
        tm1.TestDate = DateTime.Now.AddDays(-1);  
        tmList.testData.Add(tm1);  
  
        TestModel tm2 = new TestModel();  
        tm2.TestId = 3;  
        tm2.TestName = "Test3";  
        tm2.TestDesc = "Tested 3 times";  
        tm2.TestDate = DateTime.Now.AddDays(-2);  
        tmList.testData.Add(tm2);  
  
        TestModel tm3 = new TestModel();  
        tm3.TestId = 4;  
        tm3.TestName = "Test4";  
        tm3.TestDesc = "Tested 4 times";  
        tm3.TestDate = DateTime.Now.AddDays(-3);  
        tmList.testData.Add(tm);   
          
    }  
}

Now, we have got a Model ready. So, let’s start writing functions for creating an Excel file using OpenXml. For this, add OpenXml from NuGet Packages by right-clicking the project selecting "Manage NuGet Package" and searching openxml. From the list, select DocumentFormat.OpenXml as shown below and install it.

Creating Excel File Using OpenXML

Next, create functions for creating an Excel package using OpenXml, as shown below.

First, import OpenXml packages as shown below.

using DocumentFormat.OpenXml;  
using DocumentFormat.OpenXml.Packaging;  
using DocumentFormat.OpenXml.Spreadsheet;  
using X14 = DocumentFormat.OpenXml.Office2010.Excel;  
using X15 = DocumentFormat.OpenXml.Office2013.Excel;

Then, add the below code for creating an Excel file into the given path.

public void CreateExcelFile(TestModelList data, string OutPutFileDirectory)  
{  
    var datetime = DateTime.Now.ToString().Replace("/", "_").Replace(":", "_");  
   
    string fileFullname = Path.Combine(OutPutFileDirectory, "Output.xlsx");  
   
    if (File.Exists(fileFullname))  
    {  
        fileFullname = Path.Combine(OutPutFileDirectory, "Output_" + datetime + ".xlsx");  
    }  
   
    using (SpreadsheetDocument package = SpreadsheetDocument.Create(fileFullname, SpreadsheetDocumentType.Workbook))  
    {  
        CreatePartsForExcel(package, data);  
    }  
}

Write functions for creating workbooks and worksheets in Excel.

private void CreatePartsForExcel(SpreadsheetDocument document, TestModelList data)  
{  
    SheetData partSheetData = GenerateSheetdataForDetails(data);  
   
    WorkbookPart workbookPart1 = document.AddWorkbookPart();  
    GenerateWorkbookPartContent(workbookPart1);  
   
    WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId3");  
    GenerateWorkbookStylesPartContent(workbookStylesPart1);  
   
    WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");  
    GenerateWorksheetPartContent(worksheetPart1, partSheetData);  
}

Write functions for creating workbook and worksheet content in Excel, as shown below.

private void GenerateWorkbookPartContent(WorkbookPart workbookPart1)  
{  
    Workbook workbook1 = new Workbook();  
    Sheets sheets1 = new Sheets();  
    Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };  
    sheets1.Append(sheet1);  
    workbook1.Append(sheets1);  
    workbookPart1.Workbook = workbook1;  
}  
  
private void GenerateWorksheetPartContent(WorksheetPart worksheetPart1, SheetData sheetData1)  
{  
    Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };  
    worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");  
    worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");  
    worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");  
    SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1" };  
   
    SheetViews sheetViews1 = new SheetViews();  
   
    SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };  
    Selection selection1 = new Selection() { ActiveCell = "A1", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1" } };  
   
    sheetView1.Append(selection1);  
   
    sheetViews1.Append(sheetView1);  
    SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };  
   
    PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };  
    worksheet1.Append(sheetDimension1);  
    worksheet1.Append(sheetViews1);  
    worksheet1.Append(sheetFormatProperties1);  
    worksheet1.Append(sheetData1);  
    worksheet1.Append(pageMargins1);  
    worksheetPart1.Worksheet = worksheet1;  
}

Write code for workbook styles by giving your own font size, color, font name, border properties, cell style formats, etc. as shown below.

private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)  
{  
    Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };  
    stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");  
    stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");  
   
    Fonts fonts1 = new Fonts() { Count = (UInt32Value)2U, KnownFonts = true };  
   
    Font font1 = new Font();  
    FontSize fontSize1 = new FontSize() { Val = 11D };  
    Color color1 = new Color() { Theme = (UInt32Value)1U };  
    FontName fontName1 = new FontName() { Val = "Calibri" };  
    FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };  
    FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };  
   
    font1.Append(fontSize1);  
    font1.Append(color1);  
    font1.Append(fontName1);  
    font1.Append(fontFamilyNumbering1);  
    font1.Append(fontScheme1);  
   
    Font font2 = new Font();  
    Bold bold1 = new Bold();  
    FontSize fontSize2 = new FontSize() { Val = 11D };  
    Color color2 = new Color() { Theme = (UInt32Value)1U };  
    FontName fontName2 = new FontName() { Val = "Calibri" };  
    FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };  
    FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };  
   
    font2.Append(bold1);  
    font2.Append(fontSize2);  
    font2.Append(color2);  
    font2.Append(fontName2);  
    font2.Append(fontFamilyNumbering2);  
    font2.Append(fontScheme2);  
   
    fonts1.Append(font1);  
    fonts1.Append(font2);  
   
    Fills fills1 = new Fills() { Count = (UInt32Value)2U };  
   
    Fill fill1 = new Fill();  
    PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };  
   
    fill1.Append(patternFill1);  
   
    Fill fill2 = new Fill();  
    PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };  
   
    fill2.Append(patternFill2);  
   
    fills1.Append(fill1);  
    fills1.Append(fill2);  
   
    Borders borders1 = new Borders() { Count = (UInt32Value)2U };  
   
    Border border1 = new Border();  
    LeftBorder leftBorder1 = new LeftBorder();  
    RightBorder rightBorder1 = new RightBorder();  
    TopBorder topBorder1 = new TopBorder();  
    BottomBorder bottomBorder1 = new BottomBorder();  
    DiagonalBorder diagonalBorder1 = new DiagonalBorder();  
   
    border1.Append(leftBorder1);  
    border1.Append(rightBorder1);  
    border1.Append(topBorder1);  
    border1.Append(bottomBorder1);  
    border1.Append(diagonalBorder1);  
   
    Border border2 = new Border();  
   
    LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin };  
    Color color3 = new Color() { Indexed = (UInt32Value)64U };  
   
    leftBorder2.Append(color3);  
   
    RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };  
    Color color4 = new Color() { Indexed = (UInt32Value)64U };  
   
    rightBorder2.Append(color4);  
   
    TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin };  
    Color color5 = new Color() { Indexed = (UInt32Value)64U };  
   
    topBorder2.Append(color5);  
   
    BottomBorder bottomBorder2 = new BottomBorder() { Style = BorderStyleValues.Thin };  
    Color color6 = new Color() { Indexed = (UInt32Value)64U };  
   
    bottomBorder2.Append(color6);  
    DiagonalBorder diagonalBorder2 = new DiagonalBorder();  
   
    border2.Append(leftBorder2);  
    border2.Append(rightBorder2);  
    border2.Append(topBorder2);  
    border2.Append(bottomBorder2);  
    border2.Append(diagonalBorder2);  
   
    borders1.Append(border1);  
    borders1.Append(border2);  
   
    CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };  
    CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };  
   
    cellStyleFormats1.Append(cellFormat1);  
   
    CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)3U };  
    CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };  
    CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyBorder = true };  
           CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyBorder = true };  
   
    cellFormats1.Append(cellFormat2);  
    cellFormats1.Append(cellFormat3);  
    cellFormats1.Append(cellFormat4);  
   
    CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };  
    CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };  
   
    cellStyles1.Append(cellStyle1);  
    DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };  
   TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };  
   
    StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();  
   
    StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };  
    stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");  
           X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };  
   
    stylesheetExtension1.Append(slicerStyles1);  
   
    StylesheetExtension stylesheetExtension2 = new StylesheetExtension() { Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };  
    stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");  
    X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() { DefaultTimelineStyle = "TimeSlicerStyleLight1" };  
   
    stylesheetExtension2.Append(timelineStyles1);  
   
    stylesheetExtensionList1.Append(stylesheetExtension1);  
    stylesheetExtensionList1.Append(stylesheetExtension2);  
   
    stylesheet1.Append(fonts1);  
    stylesheet1.Append(fills1);  
    stylesheet1.Append(borders1);  
    stylesheet1.Append(cellStyleFormats1);  
    stylesheet1.Append(cellFormats1);  
    stylesheet1.Append(cellStyles1);  
    stylesheet1.Append(differentialFormats1);  
    stylesheet1.Append(tableStyles1);  
    stylesheet1.Append(stylesheetExtensionList1);  
   
    workbookStylesPart1.Stylesheet = stylesheet1;  
}

Write a function for generating workbook content, as shown below.

private void GenerateWorkbookPartContent(WorkbookPart workbookPart1)  
{  
    Workbook workbook1 = new Workbook();  
    Sheets sheets1 = new Sheets();  
    Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };  
    sheets1.Append(sheet1);  
    workbook1.Append(sheets1);  
    workbookPart1.Workbook = workbook1;  
}

Write the below functions to add data into Excel.

private SheetData GenerateSheetdataForDetails(TestModelList data)  
{  
    SheetData sheetData1 = new SheetData();  
    sheetData1.Append(CreateHeaderRowForExcel());  
  
    foreach (TestModel testmodel in data.testData)  
    {  
        Row partsRows = GenerateRowForChildPartDetail(testmodel);  
        sheetData1.Append(partsRows);  
    }  
    return sheetData1;  
}

The below function is created for creating Header rows in Excel.

private Row CreateHeaderRowForExcel()  
{  
    Row workRow = new Row();  
    workRow.Append(CreateCell("Test Id", 2U));  
    workRow.Append(CreateCell("Test Name", 2U));   
    workRow.Append(CreateCell("Test Description", 2U));  
    workRow.Append(CreateCell("Test Date", 2U));                   
    return workRow;  
}

The below function is used for generating child rows.

private Row GenerateRowForChildPartDetail(TestModel testmodel)  
{  
    Row tRow = new Row();  
    tRow.Append(CreateCell(testmodel.TestId.ToString()));  
    tRow.Append(CreateCell(testmodel.TestName));  
    tRow.Append(CreateCell(testmodel.TestDesc));  
    tRow.Append(CreateCell(testmodel.TestDate.ToShortDateString()));  
     
    return tRow;  
}

The below function is used for creating cells by passing only cell data, and it adds a default style.

private Cell CreateCell(string text)  
{  
    Cell cell = new Cell();  
    cell.StyleIndex = 1U;  
    cell.DataType = ResolveCellDataTypeOnValue(text);  
    cell.CellValue = new CellValue(text);  
    return cell;  
}

The below function is used for creating a cell by passing cell data and cell style.

private Cell CreateCell(string text, uint styleIndex)  
{  
    Cell cell = new Cell();  
    cell.StyleIndex = styleIndex;  
    cell.DataType = ResolveCellDataTypeOnValue(text);  
    cell.CellValue = new CellValue(text);  
    return cell;  
}

The below function is created for resolving the data type of numeric value in a cell.

private EnumValue<CellValues> ResolveCellDataTypeOnValue(string text)  
{  
    int intVal;  
    double doubleVal;  
    if (int.TryParse(text, out intVal) || double.TryParse(text, out doubleVal))  
    {  
        return CellValues.Number;  
    }  
    else  
    {  
        return CellValues.String;  
    }  
}

Now, let’s call the main function for generating an Excel file into the main method by passing our model into it.

static void Main(string[] args)  
 {  
  
     TestModelList tmList = new TestModelList();  
     tmList.testData = new List<TestModel>();  
     TestModel tm = new TestModel();  
     tm.TestId = 1;  
     tm.TestName = "Test1";  
     tm.TestDesc = "Tested 1 time";  
     tm.TestDate = DateTime.Now.Date;  
     tmList.testData.Add(tm);  
  
     TestModel tm1 = new TestModel();  
     tm1.TestId = 2;  
     tm1.TestName = "Test2";  
     tm1.TestDesc = "Tested 2 times";  
     tm1.TestDate = DateTime.Now.AddDays(-1);  
     tmList.testData.Add(tm1);  
  
     TestModel tm2 = new TestModel();  
     tm2.TestId = 3;  
     tm2.TestName = "Test3";  
     tm2.TestDesc = "Tested 3 times";  
     tm2.TestDate = DateTime.Now.AddDays(-2);  
     tmList.testData.Add(tm2);  
  
     TestModel tm3 = new TestModel();  
     tm3.TestId = 4;  
     tm3.TestName = "Test4";  
     tm3.TestDesc = "Tested 4 times";  
     tm3.TestDate = DateTime.Now.AddDays(-3);  
     tmList.testData.Add(tm);  
  
     Program p = new ExelConvertDemo.Program();  
     p.CreateTaktExcelFile(tmList, "d:\\");  
 }

The output of the Excel file would be similar to the below image.

Creating Excel File Using OpenXML

7 Comments

Join the conversation! Your thoughts help the community grow.

Peten Nemtom2y ago
Sir! Thank you very much for this example!
vairam Tamilpandian3y ago
Shall we use this code in mvc application?
Eddie Willcox4y ago
Great tutorial. Thank you. Do you perhaps have a solution to add images in specific cells using the GenerateRowForChildPartDetail method?
J S4y ago
Awesome work, with some refactoring I have functional excel creation in the Blazor.
Victor Garzon5y ago
Genial, funciona a la perfecci?n!
Hassan Faghihi5y ago
I changed this: ```private readonly Regex _digitOnlyRegex = new Regex("^\\-?\\d+$", RegexOptions.Compiled); private EnumValue<CellValues> ResolveCellDataTypeOnValue(string text) { if (text.StartsWith("+")) //PhoneNumber return CellValues.String; if (_digitOnlyRegex.IsMatch(text)) return CellValues.Number; //if (int.TryParse(text, out _) || double.TryParse(text, out _)) // return CellValues.Number; return CellValues.String; }``` as things like phonenumber (+xxxxxxxxxxx) could be parsed to a number, but then the excel would generated errors... you may also wish to uncomment some of other changes that I did (but redo it) in case of needs: ```private Cell CreateCell(string text) { Cell cell = new Cell(); cell.StyleIndex = 1U; cell.DataType = ResolveCellDataTypeOnValue(text); //if (cell.DataType == CellValues.Number) cell.CellValue = new CellValue(text); //else //{ //cell.DataType = CellValues.InlineString; //cell.InlineString = new InlineString(new Text(text)); //} return cell; }```
Girish Mahida6y ago
Hi, I was trying to create excel with multiple sheets, but ending up in creating corrupted file or file without any formatting. Could you guide me in this?