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]()