Missing Excel Closing Tabs

Question

I use OpenXml SAX to create Excel in Visual Studio 2010.  After the Excel file is created, it is missing the closing tabs, like the following.  It has the open tabs.

</sheetData>

  <pageMargins left="0.75" right="0.75" top="1" bottom="1" header="0.5" footer="0.5" />

  <headerFooter alignWithMargins="0" />

  <drawing r:id="rId1" />

 </worksheet>

Here is the code.  Can you please help what is missing?

using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();

                WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
                var excelStylesheet = new ExcelStylesheet();

                excelStylesheet.AddNumberingFormat("MM/dd/yyyy");

                // Cell formats
                excelStylesheet.AddCellFormat("DateTime", (f) =>
                {
                    f.NumberFormatId = excelStylesheet.NumberingFormats["MM/dd/yyyy"];
                    f.ApplyNumberFormat = BooleanValue.FromBoolean(true);
                });

                excelStylesheet.AddCellFormat("Numeric", (f) =>
                {
                    f.NumberFormatId = 2; // 0.00
                    f.ApplyNumberFormat = BooleanValue.FromBoolean(true);
                });

                excelStylesheet.AddCellFormat("Numeric1", (f) =>
                {
                    f.NumberFormatId = 1; // 0
                    f.ApplyNumberFormat = BooleanValue.FromBoolean(true);
                });

                SharedStringTablePart stringTable = workbookPart.AddNewPart<SharedStringTablePart>();

                string XML = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><sst xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main""></sst>";
                AddPartXml(stringTable, XML);

                SharedStringItem sharedStringItem = new SharedStringItem();
                foreach (var field in dataList.FieldList)
                {
                    stringTable.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(field)));
                    stringTable.SharedStringTable.Save();
                }

                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                string relId = workbookPart.GetIdOfPart(worksheetPart);

                Workbook workbook = new Workbook();
                FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
                Worksheet worksheet = new Worksheet();
                SheetData sheetData = new SheetData();

                int rowCount = dataList.Count + 1;
                SheetDimension sheetDimension = new SheetDimension() { Reference = "A1:" + GetExcelColumnName(dataList.FieldList.Length) + rowCount };
                worksheet.Append(sheetDimension);

                sheetData.AppendChild(CreateHeaderRow(dataList.FieldList, excelStylesheet));

                var rowIndex = 0;
                foreach (IUtilityData item in dataList)
                {
                    sheetData.AppendChild(CreateDataRow(rowIndex, dataList.FieldList, item, excelStylesheet));
                    rowIndex++;
                }

                wbsp.Stylesheet = excelStylesheet.Stylesheet;
                wbsp.Stylesheet.Save();

                worksheet.Append(sheetData);

                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                Sheets sheets = new Sheets();
                Sheet sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
                sheets.Append(sheet);

                workbook.Append(fileVersion);
                workbook.Append(sheets);

                document.WorkbookPart.Workbook = workbook;
                document.WorkbookPart.Workbook.Save();
                document.Close();
            }

Answer 1

Hi danyeungw,

Thanks for posting in the MSDN Forum.

It's based on my experience that CreateDataRow is a customized method. Would you please show me that method?

And in my opinion, the code which you have shown me will work fine. I'm wondering whether error exists in the CreateDataRow method.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Thanks Tom,

Here is the CreateDataRow method.

private static OpenXmlElement CreateDataRow(string[] fieldList, IUtilityData item, OpenXmlWriter writer, ExcelStylesheet excelStyleSheet)
        {
            var row = new Row();

            foreach (var field in fieldList)
            {
                var value = item[field];

                if (value == null)
                {
                    value = string.Empty;
                }

                var cell = CreateCell(value, string.Empty, false, excelStyleSheet, field);

                row.AppendChild(cell);
            }

            return row;
        }

Answer 3

Hi danyeungw,

I didn't find you use "writer" parameter in the CreateDataRow method. And I didn't see Worksheet element has been initialized. There has two things need to confirm:

1. Whether Worksheet element has been created. It's based on my experience that might the key for your issue.
2. Whether you close every element via "writer".

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

I have the writer.close.  I am posting the whole class.  Thanks.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

using Ptms.KinderMorgan.ImportUtility.Entity;
using System.Data;

namespace Ptms.KinderMorgan.ImportUtility.IO
{
    internal static class ExcelWriterSax
    {
        public static void Export(DataFile dataFile, IUtilityDataList dataList)
        {
            Export(dataFile.FilePath, dataList);
        }

        public static void Export(string fileName, IUtilityDataList dataList)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart;
                OpenXmlWriter writer;

                workbookPart = document.AddWorkbookPart();

                WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
                var excelStyleSheet = new ExcelStylesheet();

                excelStyleSheet.AddNumberingFormat("MM/dd/yyyy");

                // Cell formats
                excelStyleSheet.AddCellFormat("DateTime", (f) =>
                {
                    f.NumberFormatId = excelStyleSheet.NumberingFormats["MM/dd/yyyy"];
                    f.ApplyNumberFormat = BooleanValue.FromBoolean(true);
                });

                excelStyleSheet.AddCellFormat("Numeric", (f) =>
                {
                    f.NumberFormatId = 2; // 0.00
                    f.ApplyNumberFormat = BooleanValue.FromBoolean(true);
                });

                excelStyleSheet.AddCellFormat("Numeric1", (f) =>
                {
                    f.NumberFormatId = 1; // 0
                    f.ApplyNumberFormat = BooleanValue.FromBoolean(true);
                });

                LoadStringTable();

                var workbook = new Workbook();
                workbookPart.Workbook = workbook;

                var fileVersion = new FileVersion() { ApplicationName = "Microsoft Office Excel" };
                workbook.Append(fileVersion);

                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                var relationshipId = workbookPart.GetIdOfPart(worksheetPart);
                var sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relationshipId };

                var sheets = new Sheets();
                sheets.Append(sheet);
                workbook.Append(sheets);

                SharedStringItem sharedStringItem = new SharedStringItem();
                SharedStringTablePart stringTable = workbookPart.AddNewPart<SharedStringTablePart>();

                //foreach (var field in dataList.FieldList)
                //{
                //    stringTable.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(field)));
                //    stringTable.SharedStringTable.Save();
                //}

                writer = OpenXmlWriter.Create(worksheetPart);

                writer.WriteStartElement(new Worksheet());
                writer.WriteStartElement(new SheetData());

                writer.WriteElement(CreateHeaderRow(dataList, excelStyleSheet));

                foreach (IUtilityData item in dataList)
                {
                    writer.WriteElement(CreateDataRow(dataList.FieldList, item, writer, excelStyleSheet));
                }

                writer.WriteEndElement();      // SheetData
                writer.WriteEndElement();      // Worksheet

                writer.Close();

                wbsp.Stylesheet = excelStyleSheet.Stylesheet;
                wbsp.Stylesheet.Save();

                document.WorkbookPart.Workbook = workbook;
                document.WorkbookPart.Workbook.Save();
                document.Close();
            }
        }

        private static OpenXmlElement CreateHeaderRow(IUtilityDataList dataList, ExcelStylesheet excelStyleSheet)
        {
            var row = new Row();

            foreach (var value in dataList.FieldList)
            {
                var cell = CreateCell(value, string.Empty, true, excelStyleSheet, "");

                row.AppendChild(cell);
            }

            return row;
        }

        private static OpenXmlElement CreateDataRow(string[] fieldList, IUtilityData item, OpenXmlWriter writer, ExcelStylesheet excelStyleSheet)
        {
            var row = new Row();

            foreach (var field in fieldList)
            {
                var value = item[field];

                if (value == null)
                {
                    value = string.Empty;
                }

                var cell = CreateCell(value, string.Empty, false, excelStyleSheet, field);

                row.AppendChild(cell);
            }

            return row;
        }

        private static Cell CreateCell(object value, string formatString, bool isHeader, ExcelStylesheet excelStyleSheet, string field)
        {
            if (value == null)
            {
                value = string.Empty;
            }

            Type type = value.GetType();
            Cell cell = new Cell();

            if (type == typeof(String) || type == typeof(string) || type == typeof(Boolean) || type == typeof(bool))
            {
                var text = new DocumentFormat.OpenXml.Spreadsheet.Text { Text = value.ToString() };

                InlineString inlineString = new InlineString();
                inlineString.Append(text);

                cell.DataType = CellValues.InlineString;
                cell.Append(inlineString);

            }
            else if (type == typeof(DateTime))
            {
                double dateValue = ((DateTime)value).ToOADate();

                if (dateValue > 0)
                {
                    cell.DataType = CellValues.String;
                    string dDate;
                    dDate = ((DateTime)value).ToString("M/d/yyyy");
                    if ((DateTime)value != DateTime.MinValue)
                    {
                        cell.CellValue = new CellValue((String)dDate.ToString());
                    }
                }
                else
                {
                    cell.DataType = CellValues.Number;

                    if ((DateTime)value != DateTime.MinValue)
                    {
                        cell.CellValue = new CellValue(dateValue.ToString());
                        cell.StyleIndex = excelStyleSheet.CellFormats["DateTime"];
                    }
                }
            }
            else
            {
                cell.DataType = CellValues.Number;
                cell.CellValue = new CellValue(value.ToString());
                cell.StyleIndex = field == "R_Status" ? excelStyleSheet.CellFormats["Numeric1"] : excelStyleSheet.CellFormats["Numeric"];
            }

            return cell;
        }


        private static void LoadStringTable()
        {
        }
    }
}

Answer 5

Hi danyeungw,

What's ExcelStylesheet is? It isn't the member of Document.OpenXml.Spreadsheet namespace. Please clarify it.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 6

It adds the cell format to the worksheet.  It seems it works.  Does it cause the error? Thanks.

Answer 7

Hi danyeungw,

I think I must involve some experts into this for better support. There might be some time delay, appreciate for your patience.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 8

Hi Tom,

Do you have update on the solution?  Thank you.

Danyeung

Answer 9

Ok. I made some changes and the error messge went away when I opened the Excel file.  However, the closing tabs are still missing. 

public static void Export(string fileName, IUtilityDataList dataList)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart;
                OpenXmlWriter writer;
                workbookPart = document.AddWorkbookPart();
                WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
                var excelStyleSheet = new ExcelStylesheet();
                excelStyleSheet.AddNumberingFormat("MM/dd/yyyy");
                // Cell formats
                excelStyleSheet.AddCellFormat("DateTime", (f) =>
                {
                    f.NumberFormatId = excelStyleSheet.NumberingFormats["MM/dd/yyyy"];
                    f.ApplyNumberFormat = BooleanValue.FromBoolean(true);
                });
                excelStyleSheet.AddCellFormat("Numeric", (f) =>
                {
                    f.NumberFormatId = 2; // 0.00
                    f.ApplyNumberFormat = BooleanValue.FromBoolean(true);
                });
                excelStyleSheet.AddCellFormat("Numeric1", (f) =>
                {
                    f.NumberFormatId = 1; // 0
                    f.ApplyNumberFormat = BooleanValue.FromBoolean(true);
                });
                SharedStringTablePart stringTable = workbookPart.AddNewPart<SharedStringTablePart>();
                string XML = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><sst xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main""></sst>";
                AddPartXml(stringTable, XML);
                var workbook = new Workbook();
                workbookPart.Workbook = workbook;
                var fileVersion = new FileVersion() { ApplicationName = "Microsoft Office Excel" };
                workbook.Append(fileVersion);
                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                var relationshipId = workbookPart.GetIdOfPart(worksheetPart);
                var sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relationshipId };
                var sheets = new Sheets();
                sheets.Append(sheet);
                workbook.Append(sheets);
                writer = OpenXmlWriter.Create(worksheetPart);
                writer.WriteStartElement(new Worksheet());
                writer.WriteStartElement(new SheetData());
                writer.WriteElement(CreateHeaderRow(dataList, excelStyleSheet));
                foreach (IUtilityData item in dataList)
                {
                    writer.WriteElement(CreateDataRow(dataList.FieldList, item, writer, excelStyleSheet));
                }
                writer.WriteEndElement();      // SheetData
                writer.WriteEndElement();      // Worksheet
                writer.Close();
                wbsp.Stylesheet = excelStyleSheet.Stylesheet;
                wbsp.Stylesheet.Save();
                //document.WorkbookPart.Workbook = workbook;
                document.WorkbookPart.Workbook.Save();
                document.Close();
            }

private static void AddPartXml(OpenXmlPart part, string xml)
        {
            using (Stream stream = part.GetStream())
            {
                byte[] buffer = (new UTF8Encoding()).GetBytes(xml);
                stream.Write(buffer, 0, buffer.Length);
            }
        }

Answer 10

I guess I'm unclear what you are referring to when you say "closing tabs".

I was able to use the OpenXML SDK in Visual Studio 2010 to create a basic workbook that contains a single sheet and write that out to a folder on my machine. When I open this workbook I don't see anything missing.

Could you clarify what you mean when you say "closing tabs"?

Will Buffington
Microsoft Excel Support