How Create Excel File Through Open XML Package (OOXML)

In this article I will explain how we can create Excel File through 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:

Workbook part (required part)

One or more worksheets

Charts

Tables

Custom XML

You can download Open XML SDK from Microsoft site --

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0HYPERLINK "http://www.microsoft.com/downloads/en/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en"&HYPERLINK "http://www.microsoft.com/downloads/en/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en"displaylang=en

Creating Excel File

To create excel file through OOXML we need to add following references in our application.

DocumentFormat.OpenXml and

WindowBase

To create new excel file first we need to create object of spread sheet document

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.

Please download complete code for creating Excel File through OOXML.

Thanks for reading..