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:
Workbook part (required part)
One or more worksheets
Charts
Tables
Custom XML
You can download Open XML SDK from Microsoft site --
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");
}
Function to create shared string part for the cell.
privatestaticint
InsertSharedStringItem(string
text, SharedStringTablePart
shareStringPart)
{
// If the part does not contain
a SharedStringTable, create one.
if
(shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = newSharedStringTable();
}
int
i = 0;
// Iterate through all the
items in the SharedStringTable. If the text already exists, return its index.
foreach
(SharedStringItem
item in
shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if
(item.InnerText == text)
{
return
i;
}
i++;
}
// The text does not exist in
the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(newSharedStringItem(new
DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return
i;
}
Function to get the cell object.
private static Cell
InsertCellInWorksheet(string
columnName, uint
rowIndex, WorksheetPart
worksheetPart)
{
Worksheet
worksheet = worksheetPart.Worksheet;
SheetData
sheetData = worksheet.GetFirstChild<SheetData>();
string
cellReference = columnName + rowIndex;
// If the worksheet does not
contain a row with the specified row index, insert one.
Row
row;
if
(sheetData.Elements<Row>().Where(r
=> r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<Row>().Where(r
=> r.RowIndex == rowIndex).First();
}
else
{
row = new Row()
{ RowIndex = rowIndex };
sheetData.Append(row);
}
// If there is not a cell with
the specified column name, insert one.
if
(row.Elements<Cell>().Where(c
=> c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return
row.Elements<Cell>().Where(c
=> c.CellReference.Value == cellReference).First();
}
else
{
// Cells must be in sequential
order according to CellReference. Determine where to insert the new cell.
Cell
refCell = null;
//foreach (Cell cell in
row.Elements<Cell>())
//{
// if
(string.Compare(cell.CellReference.Value, cellReference, true) > 0)
// {
// refCell = cell;
// break;
// }
//}
Cell
newCell = new Cell()
{ CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return
newCell;
}
}
Note: - This solution will work only with Office 2007 onwards.