Export to Excel using Open XML. But Last cell not filling up

I used this code snippet to export data to Excel using Open XML.

The code was in VB.Net so I converted it to C#. It's working fine with only one (annoying) glitch. The last cell (Z5) is blank. When I used the VB.Net version it's filling all the cells. I manually compared the C# vs the VB.Net code bu开发者_如何学运维t it's functionally similar byte by byte. But still it's not able to fill up the last cell. Any idea why?

C# version of the code is below for your reference:

protected void Export()
{
    Response.ClearHeaders();
    Response.ClearContent();
    Response.Clear();
    Response.Buffer = true;
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
    //"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" '"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" '"application/vnd.ms-excel"
    Response.AddHeader("content-disposition", "attachment; filename=Test.xlsx");
    Response.Charset = "";
    this.EnableViewState = false;
    MemoryStream ms = new MemoryStream();
    SpreadsheetDocument objSpreadsheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
    WorkbookPart objWorkbookPart = objSpreadsheet.AddWorkbookPart();
    objWorkbookPart.Workbook = new Workbook();
    WorksheetPart objSheetPart = objWorkbookPart.AddNewPart<WorksheetPart>();
    objSheetPart.Worksheet = new Worksheet(new SheetData());
    Sheets objSheets = objSpreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    Sheet objSheet = new Sheet();
    objSheet.Id = objSpreadsheet.WorkbookPart.GetIdOfPart(objSheetPart);
    objSheet.SheetId = 1;
    objSheet.Name = "mySheet";
    objSheets.Append(objSheet);

    for (int intRow = (int)('A'); intRow <= (int)('Z'); intRow++)
    {
        for (uint intCol = 1; intCol <= 5; intCol++)
        {
            Cell objCell = InsertCellInWorksheet(Convert.ToString((char)intRow), intCol, objSheetPart);
            objCell.CellValue = new CellValue("This was a test: " + Convert.ToString((char)intRow) + intCol.ToString());
            objCell.DataType = new EnumValue<CellValues>(CellValues.String);
            objSpreadsheet.WorkbookPart.Workbook.Save();
        }
    }


    objSpreadsheet.WorkbookPart.Workbook.Save();
    objSpreadsheet.Close();
    ms.WriteTo(Response.OutputStream);
    Response.Flush();
    Response.End();
}

private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
    Worksheet worksheet = worksheetPart.Worksheet;
    var 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();
    }
    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
    Cell refCell = row.Elements<Cell>().FirstOrDefault(cell => string.Compare(cell.CellReference.Value, cellReference, true) > 0);

    var newCell = new Cell { CellReference = cellReference };
    row.InsertBefore(newCell, refCell);

    worksheet.Save();
    return newCell;
}

Issue is resolved. I made a small change to the code to make it work. Instead of saving the sheet inside InsertCellInWorksheet method. I am saving the sheet outside the for loops. Here is the working version of the code.

protected void Export()
{
    Response.ClearHeaders();
    Response.ClearContent();
    Response.Clear();
    Response.Buffer = true;
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
    //"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" '"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" '"application/vnd.ms-excel"
    Response.AddHeader("content-disposition", "attachment; filename=Test.xlsx");
    Response.Charset = "";
    this.EnableViewState = false;
    MemoryStream ms = new MemoryStream();
    SpreadsheetDocument objSpreadsheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
    WorkbookPart objWorkbookPart = objSpreadsheet.AddWorkbookPart();
    objWorkbookPart.Workbook = new Workbook();
    WorksheetPart objSheetPart = objWorkbookPart.AddNewPart<WorksheetPart>();
    objSheetPart.Worksheet = new Worksheet(new SheetData());
    Sheets objSheets = objSpreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    Sheet objSheet = new Sheet();
    objSheet.Id = objSpreadsheet.WorkbookPart.GetIdOfPart(objSheetPart);
    objSheet.SheetId = 1;
    objSheet.Name = "mySheet";
    objSheets.Append(objSheet);

    for (int intRow = (int)('A'); intRow <= (int)('Z'); intRow++)
    {
        for (uint intCol = 1; intCol <= 5; intCol++)
        {
            Cell objCell = InsertCellInWorksheet(Convert.ToString((char)intRow), intCol, objSheetPart);
            objCell.CellValue = new CellValue("This was a test: " + Convert.ToString((char)intRow) + intCol.ToString());
            objCell.DataType = new EnumValue<CellValues>(CellValues.String);
        }
    }
    objSheetPart.Worksheet.Save();
    objSpreadsheet.WorkbookPart.Workbook.Save();
    objSpreadsheet.Close();
    ms.WriteTo(Response.OutputStream);
    Response.Flush();
    Response.End();
}

private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
    Worksheet worksheet = worksheetPart.Worksheet;
    var 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();
    }
    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
    Cell refCell = row.Elements<Cell>().FirstOrDefault(cell => string.Compare(cell.CellReference.Value, cellReference, true) > 0);

    var newCell = new Cell { CellReference = cellReference };
    row.InsertBefore(newCell, refCell);

    //worksheet.Save();
    return newCell;
}

Not sure how it worked in VB.Net :-)

The loop through the rows and columns that you have doesn't look right. It looks like you are looping from row = 65 (i.e. (int)('A') to row = 65 + 26 (i.e. (int)('Z')) and then col = 1 to 3. Maybe you should loop from col = 1 to 5.
I think you possibly need to have a look at the xml you are dealing with and make sure you are moving through the document (rows and columns) correctly. If you weren't aware of it all office 2007 and later documents are openXml document. They are basically zip files that contain xml. If you take your spreadsheet and change the extension from .xlsx to .zip you will be able to open the document as a zip file and browse the contents. I haven't worked with openXml for over a year, but if my memory serves me correctly you will find the xml representing the sheets in the sheets folder.