How Create Excel File Through Open XML Package (OOXML)

Introduction

Open XML is an open standard for creating word-processing documents, presentations, and spreadsheets. Through OOXML we can create office applications on different platform. Open XML is designed for existing word-processing documents, presentations, and spreadsheets that are encoded in binary formats defined by Microsoft Office applications.

An Open XML file is stored in a ZIP archive for packaging and compression.

Spreadsheet workbooks are described by using SpreadsheetML markup. Workbook packages can contain:

You can download Open XML SDK from Microsoft site --

DocumentFormat.OpenXml and

SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath,SpreadsheetDocumentType.Workbook);

filepath stores the local path where excel file will be created.

Now we need to add workbook spreadsheetDocument.

WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();

workbookpart.Workbook = newWorkbook();

Add a WorksheetPart to the WorkbookPart. Worksheet part represents the worksheet in the workbook.

WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();

worksheetPart.Worksheet = newWorksheet(newSheetData());

Append a new worksheet and associate it with the workbook.

Sheet sheet = newSheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };

sheets.Append(sheet);

Following functions will explain how can we create excel file.

Function for creating Spreadsheet.

public staticvoid CreateSpreadsheetWorkbook(string filepath, DataTable dt)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
FileInfo f = newFileInfo(filepath);
if(f.Exists)
f.Delete();

SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath,SpreadsheetDocumentType.Workbook);

// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = newWorkbook();

// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = newWorksheet(newSheetData());

// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(newSheets());

// Append a new worksheet and associate it with the workbook.
Sheet sheet = newSheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
sheets.Append(sheet);
string cl = "";
uint row = 2;
int index;
Cell cell;
foreach (DataRow dr in dt.Rows)
{
for (int idx = 0; idx < dt.Columns.Count; idx++)
{
if (idx >= 26)
cl = "A" + Convert.ToString(Convert.ToChar(65 + idx - 26));
else
cl = Convert.ToString(Convert.ToChar(65 + idx));
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
if (row == 2)
{
index = InsertSharedStringItem(dt.Columns[idx].ColumnName, shareStringPart);
cell = InsertCellInWorksheet(cl, row - 1, worksheetPart);
cell.CellValue = newCellValue(index.ToString());
cell.DataType = newEnumValue<CellValues>(CellValues.SharedString);
}

// Insert the text into the SharedStringTablePart.
index = InsertSharedStringItem(Convert.ToString(dr[idx]), shareStringPart);
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = newCellValue(index.ToString());
cell.DataType = newEnumValue<CellValues>(CellValues.SharedString);
}
row++;
}

//InsertCellInWorksheet("A", 1, worksheetPart);
workbookpart.Workbook.Save();

// Close the document.
spreadsheetDocument.Close();
//InsertText(@"c:\MyXL3.xlx", "Hello");
}