Creating Excel File Using OpenXML

In this article I will explain how to create an Excel file using C# without any third party paid dll files or Microsoft Interop services, but OpenXml dll, which is free and available in a nuget package.

In this example, let’s create a demo console project in Visual Studio by selecting File -> New -> Project. From the project window, select Console template and type name and select 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.

  1. public class TestModel  
  2.  {              
  3.      public int TestId { getset; }  
  4.      public string TestName { getset; }  
  5.      public string TestDesc { getset; }  
  6.      public DateTime TestDate { getset; }  
  7.  }  

Create one more class and give name as TestModelList.

  1. public class TestModelList  
  2. {  
  3.     public List<TestModel> testData { getset; }  
  4. }  

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

  1. class Program  
  2. {  
  3.     static void Main(string[] args)  
  4.     {             
  5.           
  6.         TestModelList tmList = new TestModelList();  
  7.         tmList.testData = new List<TestModel>();  
  8.         TestModel tm = new TestModel();  
  9.         tm.TestId = 1;  
  10.         tm.TestName = "Test1";  
  11.         tm.TestDesc = "Tested 1 time";  
  12.         tm.TestDate = DateTime.Now.Date;  
  13.         tmList.testData.Add(tm);  
  14.   
  15.         TestModel tm1 = new TestModel();  
  16.         tm1.TestId = 2;  
  17.         tm1.TestName = "Test2";  
  18.         tm1.TestDesc = "Tested 2 times";  
  19.         tm1.TestDate = DateTime.Now.AddDays(-1);  
  20.         tmList.testData.Add(tm1);  
  21.   
  22.         TestModel tm2 = new TestModel();  
  23.         tm2.TestId = 3;  
  24.         tm2.TestName = "Test3";  
  25.         tm2.TestDesc = "Tested 3 times";  
  26.         tm2.TestDate = DateTime.Now.AddDays(-2);  
  27.         tmList.testData.Add(tm2);  
  28.   
  29.         TestModel tm3 = new TestModel();  
  30.         tm3.TestId = 4;  
  31.         tm3.TestName = "Test4";  
  32.         tm3.TestDesc = "Tested 4 times";  
  33.         tm3.TestDate = DateTime.Now.AddDays(-3);  
  34.         tmList.testData.Add(tm);   
  35.           
  36.     }  
  37. }   

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 and selecting "Manage NuGet Package" and search 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.
    1. using DocumentFormat.OpenXml;  
    2. using DocumentFormat.OpenXml.Packaging;  
    3. using DocumentFormat.OpenXml.Spreadsheet;  
    4. using X14 = DocumentFormat.OpenXml.Office2010.Excel;  
    5. using X15 = DocumentFormat.OpenXml.Office2013.Excel;  
    Then, add the below code for creating an Excel file into given path.
    1. public void CreateExcelFile(TestModelList data, string OutPutFileDirectory)  
    2. {  
    3.     var datetime = DateTime.Now.ToString().Replace("/""_").Replace(":""_");  
    4.    
    5.     string fileFullname = Path.Combine(OutPutFileDirectory, "Output.xlsx");  
    6.    
    7.     if (File.Exists(fileFullname))  
    8.     {  
    9.         fileFullname = Path.Combine(OutPutFileDirectory, "Output_" + datetime + ".xlsx");  
    10.     }  
    11.    
    12.     using (SpreadsheetDocument package = SpreadsheetDocument.Create(fileFullname, SpreadsheetDocumentType.Workbook))  
    13.     {  
    14.         CreatePartsForExcel(package, data);  
    15.     }  
    16. }  
  • Write functions for creating workbook and worksheet into Excel.
    1. private void CreatePartsForExcel(SpreadsheetDocument document, TestModelList data)  
    2. {  
    3.     SheetData partSheetData = GenerateSheetdataForDetails(data);  
    4.    
    5.     WorkbookPart workbookPart1 = document.AddWorkbookPart();  
    6.     GenerateWorkbookPartContent(workbookPart1);  
    7.    
    8.     WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId3");  
    9.     GenerateWorkbookStylesPartContent(workbookStylesPart1);  
    10.    
    11.     WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");  
    12.     GenerateWorksheetPartContent(worksheetPart1, partSheetData);  
    13. }  
  • Write functions for creating workbook and work sheet content in Excel, as shown below.
    1. private void GenerateWorkbookPartContent(WorkbookPart workbookPart1)  
    2. {  
    3.     Workbook workbook1 = new Workbook();  
    4.     Sheets sheets1 = new Sheets();  
    5.     Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };  
    6.     sheets1.Append(sheet1);  
    7.     workbook1.Append(sheets1);  
    8.     workbookPart1.Workbook = workbook1;  
    9. }  
    10.   
    11. private void GenerateWorksheetPartContent(WorksheetPart worksheetPart1, SheetData sheetData1)  
    12. {  
    13.     Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };  
    14.     worksheet1.AddNamespaceDeclaration("r""http://schemas.openxmlformats.org/officeDocument/2006/relationships");  
    15.     worksheet1.AddNamespaceDeclaration("mc""http://schemas.openxmlformats.org/markup-compatibility/2006");  
    16.     worksheet1.AddNamespaceDeclaration("x14ac""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");  
    17.     SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1" };  
    18.    
    19.     SheetViews sheetViews1 = new SheetViews();  
    20.    
    21.     SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };  
    22.     Selection selection1 = new Selection() { ActiveCell = "A1", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1" } };  
    23.    
    24.     sheetView1.Append(selection1);  
    25.    
    26.     sheetViews1.Append(sheetView1);  
    27.     SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };  
    28.    
    29.     PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };  
    30.     worksheet1.Append(sheetDimension1);  
    31.     worksheet1.Append(sheetViews1);  
    32.     worksheet1.Append(sheetFormatProperties1);  
    33.     worksheet1.Append(sheetData1);  
    34.     worksheet1.Append(pageMargins1);  
    35.     worksheetPart1.Worksheet = worksheet1;  
    36. }  
  • Write code for workbook styles by giving your own font size, color, font name, border properties, cell style formats etc. as shown below.
    1. private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)  
    2. {  
    3.     Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };  
    4.     stylesheet1.AddNamespaceDeclaration("mc""http://schemas.openxmlformats.org/markup-compatibility/2006");  
    5.     stylesheet1.AddNamespaceDeclaration("x14ac""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");  
    6.    
    7.     Fonts fonts1 = new Fonts() { Count = (UInt32Value)2U, KnownFonts = true };  
    8.    
    9.     Font font1 = new Font();  
    10.     FontSize fontSize1 = new FontSize() { Val = 11D };  
    11.     Color color1 = new Color() { Theme = (UInt32Value)1U };  
    12.     FontName fontName1 = new FontName() { Val = "Calibri" };  
    13.     FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };  
    14.     FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };  
    15.    
    16.     font1.Append(fontSize1);  
    17.     font1.Append(color1);  
    18.     font1.Append(fontName1);  
    19.     font1.Append(fontFamilyNumbering1);  
    20.     font1.Append(fontScheme1);  
    21.    
    22.     Font font2 = new Font();  
    23.     Bold bold1 = new Bold();  
    24.     FontSize fontSize2 = new FontSize() { Val = 11D };  
    25.     Color color2 = new Color() { Theme = (UInt32Value)1U };  
    26.     FontName fontName2 = new FontName() { Val = "Calibri" };  
    27.     FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };  
    28.     FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };  
    29.    
    30.     font2.Append(bold1);  
    31.     font2.Append(fontSize2);  
    32.     font2.Append(color2);  
    33.     font2.Append(fontName2);  
    34.     font2.Append(fontFamilyNumbering2);  
    35.     font2.Append(fontScheme2);  
    36.    
    37.     fonts1.Append(font1);  
    38.     fonts1.Append(font2);  
    39.    
    40.     Fills fills1 = new Fills() { Count = (UInt32Value)2U };  
    41.    
    42.     Fill fill1 = new Fill();  
    43.     PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };  
    44.    
    45.     fill1.Append(patternFill1);  
    46.    
    47.     Fill fill2 = new Fill();  
    48.     PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };  
    49.    
    50.     fill2.Append(patternFill2);  
    51.    
    52.     fills1.Append(fill1);  
    53.     fills1.Append(fill2);  
    54.    
    55.     Borders borders1 = new Borders() { Count = (UInt32Value)2U };  
    56.    
    57.     Border border1 = new Border();  
    58.     LeftBorder leftBorder1 = new LeftBorder();  
    59.     RightBorder rightBorder1 = new RightBorder();  
    60.     TopBorder topBorder1 = new TopBorder();  
    61.     BottomBorder bottomBorder1 = new BottomBorder();  
    62.     DiagonalBorder diagonalBorder1 = new DiagonalBorder();  
    63.    
    64.     border1.Append(leftBorder1);  
    65.     border1.Append(rightBorder1);  
    66.     border1.Append(topBorder1);  
    67.     border1.Append(bottomBorder1);  
    68.     border1.Append(diagonalBorder1);  
    69.    
    70.     Border border2 = new Border();  
    71.    
    72.     LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin };  
    73.     Color color3 = new Color() { Indexed = (UInt32Value)64U };  
    74.    
    75.     leftBorder2.Append(color3);  
    76.    
    77.     RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };  
    78.     Color color4 = new Color() { Indexed = (UInt32Value)64U };  
    79.    
    80.     rightBorder2.Append(color4);  
    81.    
    82.     TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin };  
    83.     Color color5 = new Color() { Indexed = (UInt32Value)64U };  
    84.    
    85.     topBorder2.Append(color5);  
    86.    
    87.     BottomBorder bottomBorder2 = new BottomBorder() { Style = BorderStyleValues.Thin };  
    88.     Color color6 = new Color() { Indexed = (UInt32Value)64U };  
    89.    
    90.     bottomBorder2.Append(color6);  
    91.     DiagonalBorder diagonalBorder2 = new DiagonalBorder();  
    92.    
    93.     border2.Append(leftBorder2);  
    94.     border2.Append(rightBorder2);  
    95.     border2.Append(topBorder2);  
    96.     border2.Append(bottomBorder2);  
    97.     border2.Append(diagonalBorder2);  
    98.    
    99.     borders1.Append(border1);  
    100.     borders1.Append(border2);  
    101.    
    102.     CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };  
    103.     CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };  
    104.    
    105.     cellStyleFormats1.Append(cellFormat1);  
    106.    
    107.     CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)3U };  
    108.     CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };  
    109.     CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyBorder = true };  
    110.            CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyBorder = true };  
    111.    
    112.     cellFormats1.Append(cellFormat2);  
    113.     cellFormats1.Append(cellFormat3);  
    114.     cellFormats1.Append(cellFormat4);  
    115.    
    116.     CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };  
    117.     CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };  
    118.    
    119.     cellStyles1.Append(cellStyle1);  
    120.     DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };  
    121.    TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };  
    122.    
    123.     StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();  
    124.    
    125.     StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };  
    126.     stylesheetExtension1.AddNamespaceDeclaration("x14""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");  
    127.            X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };  
    128.    
    129.     stylesheetExtension1.Append(slicerStyles1);  
    130.    
    131.     StylesheetExtension stylesheetExtension2 = new StylesheetExtension() { Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };  
    132.     stylesheetExtension2.AddNamespaceDeclaration("x15""http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");  
    133.     X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() { DefaultTimelineStyle = "TimeSlicerStyleLight1" };  
    134.    
    135.     stylesheetExtension2.Append(timelineStyles1);  
    136.    
    137.     stylesheetExtensionList1.Append(stylesheetExtension1);  
    138.     stylesheetExtensionList1.Append(stylesheetExtension2);  
    139.    
    140.     stylesheet1.Append(fonts1);  
    141.     stylesheet1.Append(fills1);  
    142.     stylesheet1.Append(borders1);  
    143.     stylesheet1.Append(cellStyleFormats1);  
    144.     stylesheet1.Append(cellFormats1);  
    145.     stylesheet1.Append(cellStyles1);  
    146.     stylesheet1.Append(differentialFormats1);  
    147.     stylesheet1.Append(tableStyles1);  
    148.     stylesheet1.Append(stylesheetExtensionList1);  
    149.    
    150.     workbookStylesPart1.Stylesheet = stylesheet1;  
    151. }  
  • Write a function for generating workbook content, as shown below.
    1. private void GenerateWorkbookPartContent(WorkbookPart workbookPart1)  
    2. {  
    3.     Workbook workbook1 = new Workbook();  
    4.     Sheets sheets1 = new Sheets();  
    5.     Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };  
    6.     sheets1.Append(sheet1);  
    7.     workbook1.Append(sheets1);  
    8.     workbookPart1.Workbook = workbook1;  
    9. }  
  • Write the below functions to add data into Excel.
    1. private SheetData GenerateSheetdataForDetails(TestModelList data)  
    2. {  
    3.     SheetData sheetData1 = new SheetData();  
    4.     sheetData1.Append(CreateHeaderRowForExcel());  
    5.   
    6.     foreach (TestModel testmodel in data.testData)  
    7.     {  
    8.         Row partsRows = GenerateRowForChildPartDetail(testmodel);  
    9.         sheetData1.Append(partsRows);  
    10.     }  
    11.     return sheetData1;  
    12. }  
    The below function is created for creating Header rows in Excel.
    1. private Row CreateHeaderRowForExcel()  
    2. {  
    3.     Row workRow = new Row();  
    4.     workRow.Append(CreateCell("Test Id", 2U));  
    5.     workRow.Append(CreateCell("Test Name", 2U));   
    6.     workRow.Append(CreateCell("Test Description", 2U));  
    7.     workRow.Append(CreateCell("Test Date", 2U));                   
    8.     return workRow;  
    Below function is used for generating child rows.
    1. private Row GenerateRowForChildPartDetail(TestModel testmodel)  
    2. {  
    3.     Row tRow = new Row();  
    4.     tRow.Append(CreateCell(testmodel.TestId.ToString()));  
    5.     tRow.Append(CreateCell(testmodel.TestName));  
    6.     tRow.Append(CreateCell(testmodel.TestDesc));  
    7.     tRow.Append(CreateCell(testmodel.TestDate.ToShortDateString()));  
    8.      
    9.     return tRow;  
    10. }  
    Below function is used for creating cell by passing only cell data and it adds default style.
    1. private Cell CreateCell(string text)  
    2. {  
    3.     Cell cell = new Cell();  
    4.     cell.StyleIndex = 1U;  
    5.     cell.DataType = ResolveCellDataTypeOnValue(text);  
    6.     cell.CellValue = new CellValue(text);  
    7.     return cell;  
    8. }  
    Below function is used for creating a cell by passing cell data and cell style.
    1. private Cell CreateCell(string text, uint styleIndex)  
    2. {  
    3.     Cell cell = new Cell();  
    4.     cell.StyleIndex = styleIndex;  
    5.     cell.DataType = ResolveCellDataTypeOnValue(text);  
    6.     cell.CellValue = new CellValue(text);  
    7.     return cell;  
    8. }  
    Below function is created for resolving the data type of numeric value in a cell.
    1. private EnumValue<CellValues> ResolveCellDataTypeOnValue(string text)  
    2. {  
    3.     int intVal;  
    4.     double doubleVal;  
    5.     if (int.TryParse(text, out intVal) || double.TryParse(text, out doubleVal))  
    6.     {  
    7.         return CellValues.Number;  
    8.     }  
    9.     else  
    10.     {  
    11.         return CellValues.String;  
    12.     }  
    13. }  
Now, let’s call the main function for generating Excel file into main method with passing our model into it.
  1. static void Main(string[] args)  
  2.  {  
  3.   
  4.      TestModelList tmList = new TestModelList();  
  5.      tmList.testData = new List<TestModel>();  
  6.      TestModel tm = new TestModel();  
  7.      tm.TestId = 1;  
  8.      tm.TestName = "Test1";  
  9.      tm.TestDesc = "Tested 1 time";  
  10.      tm.TestDate = DateTime.Now.Date;  
  11.      tmList.testData.Add(tm);  
  12.   
  13.      TestModel tm1 = new TestModel();  
  14.      tm1.TestId = 2;  
  15.      tm1.TestName = "Test2";  
  16.      tm1.TestDesc = "Tested 2 times";  
  17.      tm1.TestDate = DateTime.Now.AddDays(-1);  
  18.      tmList.testData.Add(tm1);  
  19.   
  20.      TestModel tm2 = new TestModel();  
  21.      tm2.TestId = 3;  
  22.      tm2.TestName = "Test3";  
  23.      tm2.TestDesc = "Tested 3 times";  
  24.      tm2.TestDate = DateTime.Now.AddDays(-2);  
  25.      tmList.testData.Add(tm2);  
  26.   
  27.      TestModel tm3 = new TestModel();  
  28.      tm3.TestId = 4;  
  29.      tm3.TestName = "Test4";  
  30.      tm3.TestDesc = "Tested 4 times";  
  31.      tm3.TestDate = DateTime.Now.AddDays(-3);  
  32.      tmList.testData.Add(tm);  
  33.   
  34.      Program p = new ExelConvertDemo.Program();  
  35.      p.CreateTaktExcelFile(tmList, "d:\\");  
  36.  }  

Output of the Excel file would be similar to the below image.

Creating Excel File Using OpenXML