OpenXML library save excel file

Question

I have the following code and its not saving the values to cell and also in file. It shows value in cell.cellvalue field but its not writing it to excel. I have no idea how to save the file. I used OpenXml-SDK and I am writing datatable values to each cell/row of created spreadsheet document.

 using (SpreadsheetDocument ssd=SpreadsheetDocument.Open(Server.MapPath(@"\ExcelPackageTemplate.xlsx"),true))
        {
            WorkbookPart wbPart = ssd.WorkbookPart;
            WorksheetPart worksheetPart = wbPart.WorksheetParts.First();

            SheetData sheetdata = worksheetPart.Worksheet.GetFirstChild<SheetData>();
            string[] headerColumns = new string[] { dt.Columns[0].ColumnName, dt.Columns[1].ColumnName,dt.Columns[2].ColumnName };
            DocumentFormat.OpenXml.Spreadsheet.Row r = new DocumentFormat.OpenXml.Spreadsheet.Row();
            int RowIndexer = 1;
            //int colInd=0;
            r.RowIndex = (UInt32)RowIndexer;

            string test = ColumnName(RowIndexer);

            foreach (DataColumn dc in dt.Columns)
            {

                DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                cell.CellReference = test+RowIndexer;
                cell.DataType = CellValues.InlineString;
                cell.InlineString = new InlineString(new Text(dc.ColumnName.ToString()));
                DocumentFormat.OpenXml.Spreadsheet.CellValue value = new DocumentFormat.OpenXml.Spreadsheet.CellValue();

                r.AppendChild(cell);

                // colInd++;
            }

            //r.RowIndex = (UInt32)RowIndexer;
            RowIndexer = 2;
            foreach (DataRow dr in dt.Rows)
            {
                DocumentFormat.OpenXml.Spreadsheet.Row Row = new DocumentFormat.OpenXml.Spreadsheet.Row();
                string Index = ColumnName(RowIndexer);
                Row.RowIndex = (UInt32)RowIndexer;
                foreach (object value in dr.ItemArray)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.DataType = CellValues.InlineString;
                    cell.InlineString =  new InlineString(new Text(value.ToString()));
                    cell.CellReference = Index+RowIndexer;
                   // cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value.ToString());
                    Row.AppendChild(cell);

                }
                RowIndexer++;

            }

            worksheetPart.Worksheet.Save();
            wbPart.Workbook.Save();
            ssd.Close();

score 6 · Accepted Answer · edited May 23 '17 at 11:46

Try this:

using (SpreadsheetDocument ssd = SpreadsheetDocument.Open(Server.MapPath(@"\ExcelPackageTemplate.xlsx"), true))
{
    WorkbookPart wbPart = ssd.WorkbookPart;
    WorksheetPart worksheetPart = wbPart.WorksheetParts.First();

    SheetData sheetdata = worksheetPart.Worksheet.GetFirstChild<SheetData>();
    string[] headerColumns = new string[] { dt.Columns[0].ColumnName, dt.Columns[1].ColumnName, dt.Columns[2].ColumnName };
    DocumentFormat.OpenXml.Spreadsheet.Row r = new DocumentFormat.OpenXml.Spreadsheet.Row();
    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
    int RowIndexer = 1;
    int ColumnIndexer = 1;

    r.RowIndex = (UInt32)RowIndexer;
    foreach (DataColumn dc in dt.Columns)
    {
        cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
        cell.CellReference = ColumnName(ColumnIndexer) + RowIndexer;
        cell.DataType = CellValues.InlineString;
        cell.InlineString = new InlineString(new Text(dc.ColumnName.ToString()));
        // consider using cell.CellValue. Then you don't need to use InlineString.
        // Because it seems you're not using any rich text so you're just bloating up
        // the XML.

        r.AppendChild(cell);

        ColumnIndexer++;
    }
    // here's the missing part you needed
    sheetdata.Append(r);

    RowIndexer = 2;
    foreach (DataRow dr in dt.Rows)
    {
        r = new DocumentFormat.OpenXml.Spreadsheet.Row();
        r.RowIndex = (UInt32)RowIndexer;
        // this follows the same starting column index as your column header.
        // I'm assuming you start with column 1. Change as you see fit.
        ColumnIndexer = 1;
        foreach (object value in dr.ItemArray)
        {
            cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
            // I moved it here so it's consistent with the above part
            // Also, the original code was using the row index to calculate
            // the column name, which is weird.
            cell.CellReference = ColumnName(ColumnIndexer) + RowIndexer;
            cell.DataType = CellValues.InlineString;
            cell.InlineString = new InlineString(new Text(value.ToString()));

            r.AppendChild(cell);
            ColumnIndexer++;
        }
        RowIndexer++;

        // missing part
        sheetdata.Append(r);
    }

    worksheetPart.Worksheet.Save();
    wbPart.Workbook.Save();
    ssd.Close();
}

Some comments:

The ColumnName() function is from here.
I'm assuming you wanted the column headers in a row, and data in subsequent rows (because the original code had the row index used for calculating the column name).
Cleaned up some parts of the code so it's easier to read and that the column header and data row parts are more consistent in writing style.
I suggest that you consider using CellValue instead of InlineString. Based on your code, you're importing a DataTable, and you don't seem to need rich text so InlineString's are a little overkill and might make the resulting file larger (bloated XML). Use one or the other, and remember to set DataType accordingly.
Also, the code only works for completely empty SheetData.

score 2 · Answer 2 · edited May 23 '17 at 12:08

2

There are some possible issues here:

You create and add new Rows and Cells: it presumes the worksheet is totally blank when you open it (ie: no rows or cells with the same index/address).

Since you set the CellType to InlineString, you need to set the Cell.InlineString and not Cell.CellValue

cell.DataType = CellValues.InlineString;
cell.InlineString = new InlineString(New Text(value.TsString()));

Also, there is something wrong in your code concerning usage of CellReference. These parts of code have no sense:
```
cell.CellReference = dc.ColumnName.ToString();
cell.CellReference = value.ToString();
```
Cell Address should be something like this "A1". You have to review your code to set the correct address. You already have the rowIndex. You need to get the column name. This Translate a column index into an Excel Column Name can help.

edited May 23 '17 at 12:08

Community

1
1

answered Jun 19 '13 at 16:55

Chris

8,527
10
34
51

@user1848739 I have updated my answer since I realize that there is others possibles issues. – Chris Jun 19 '13 at 19:22
I tried your solution and its showing me the same result. It has blank sheet. How can I save the data in excel sheet? – rach Jun 19 '13 at 19:39
@user1848739 It's impossible to say if you don't show the modified code with the correct cellAddress. Also, I don't see code that saves the workbook (it saves the worksheet only) – Chris Jun 19 '13 at 19:51
I edited my code. Please see it and test is the function from the link you provided to get excel columname. But the first time loop is getting A1 and it remains same. for the second column it should have b1...and so on.. – rach Jun 19 '13 at 19:56
@user1848739 OK I have not tested this one, I have my own that works. But from what I see in your code, this is normal... `string test = ColumnName(RowIndex);` should be something like this `string test = ColumnName(colIndex);` For each row you add (in first part in column headers and then for DataRows, you have to reset the colndex to 1 and increment it for each column (ie in `foreach (DataColumn dc in dt.Columns)` and `foreach (object value in dr.ItemArray)`). – Chris Jun 19 '13 at 20:29

OpenXML library save excel file

2 Answers2

Linked