Generating spreadsheet from tables using OpenXML

Question

Goal: Generate a spreadsheet with worksheets named off of tables and column names listed on the first row of each worksheet. These will actually be based off classes build from those tables.

Issue: After opening the spreadsheet it claims it found "unreadable content" and asks if I want to recover. If I select yes it does recover the spreadsheet perfectly but the user should not have to do this.

Observation: Using the SDK I am able to see the inner workings of the spreadsheet and I noticed the xml names for the worksheets are the real issue. In the initial downloaded version they are named Sheet1 through Sheet9 but then they go into Sheet1a through Sheet1f. So basically its numbering them with hexidecimals 1-f. After I do a repair on it they correct the names to purely numeric.

Not sure if you can see the image, looks like my work blocks the site :/

Note: I have looked at my code and I do not see anywhere I can specifically name these internal xml sheets so I am not sure what code to share with you but I am posting what section of code is creating these sheets.

Method that builds the spreadsheet:

    public MemoryStream CreatePartsFromAssembly(MemoryStream spreadsheetStrem, Type typeInAssembly)
    {
        using (SpreadsheetDocument document = SpreadsheetDocument.Create(spreadsheetStrem, SpreadsheetDocumentType.Workbook))
        {
            //List<Type> types = null;
            var types = ExcelTypeHelper.GetTableTypes(typeInAssembly);

            ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId" + (types.Count).ToString());
            GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1, types);

            WorkbookPart workbookPart1 = document.AddWorkbookPart();
            GenerateWorkbookPart1Content(workbookPart1, types);

            var sharedStringOffsets = new List<int>();

            foreach (var type in types)
            {
                sharedStringOffsets.Add(ExcelTypeHelper.GetPropertyCount(type));
            }

            //int j = 0;
            int sharedStringOffset = 0;
            int sharedStringSum = sharedStringOffsets.Sum();
            int sharedStringRunningSum = 0;

            for (int i = types.Count; i > 0; i--)
            {
                var type = types[i - 1];
                sharedStringRunningSum += sharedStringOffsets[i - 1];
                sharedStringOffset = sharedStringSum - sharedStringRunningSum;

                WorksheetPart worksheetPart = workbookPart1.AddNewPart<WorksheetPart>("rId" + (i).ToString());
                GenerateWorksheetPartContent(worksheetPart, types[i - 1], sharedStringOffset);

                //j++;
            }

            SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId" + (types.Count + 3).ToString());
            GenerateSharedStringTablePartContent(sharedStringTablePart1, types);

            WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId" + (types.Count + 2).ToString());
            GenerateWorkbookStylesPart1Content(workbookStylesPart1);

            ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId" + (types.Count + 1).ToString());
            GenerateThemePart1Content(themePart1);

            SetPackageProperties1(document);
        }

        return spreadsheetStrem;
    }

Method that builds the worksheet:

    private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, Type type, int sharedStringOffset)
    {
        var propertyNames = ExcelTypeHelper.GetPropertyNames(type);

        string referenceRange = "A1:" + ColumnHeaderFromRowColumn((uint)1, (uint)propertyNames.Count);

        Worksheet worksheet = new Worksheet();
        worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
        SheetDimension sheetDimension = new SheetDimension() { Reference = referenceRange };

        SheetViews sheetViews = new SheetViews();

        SheetView sheetView = new SheetView() { WorkbookViewId = (UInt32Value)0U };
        Selection selection = new Selection() { ActiveCell = "A2", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A2" } };

        sheetView.Append(selection);

        sheetViews.Append(sheetView);
        SheetFormatProperties sheetFormatProperties2 = new SheetFormatProperties() { DefaultRowHeight = 15D };

        SheetData sheetData = new SheetData();

        Row row = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:" + propertyNames.Count } };

        for (int i = 0; i < propertyNames.Count; i++)
        {
            Cell cell = new Cell() { CellReference = ColumnHeaderFromRowColumn((uint)1, (uint)i + 1), DataType = CellValues.SharedString };
            CellValue cellValue = new CellValue();
            cellValue.Text = (i + sharedStringOffset).ToString();   //Shared string reference

            cell.Append(cellValue);
            row.Append(cell);
        }

        sheetData.Append(row);
        PageMargins pageMargins = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

        worksheet.Append(sheetDimension);
        worksheet.Append(sheetViews);
        worksheet.Append(sheetFormatProperties2);
        worksheet.Append(sheetData);
        worksheet.Append(pageMargins);

        worksheetPart.Worksheet = worksheet;
    }

Answer 1

ClosedXML is a good library if you are building a relatively small spreadsheet. However, performance dips after a few hundred rows/columns/tabs. Once you go past that, you're going to want to stay with OpenXML and especially use the OpenXMLWriter class WriteStartElement(), WriteElement() and WriteEndElement() methods. This is known as SAX (Simple API for XML) approach.

Read this MSDN article for more background: SAX vs DOM

Answer 2

So the answer to this question is.. DON'T USE OPENXML!!!!

I found a library that uses OpenXML but turns it all into objects. I rewrote a rather large implementation in less than a day. I was able to generate a spreadsheet from scratch with 20 lines of code. Support on the module is great the owner is fast to respond.

If you are working with OpenXML trash it and get ClosedXML they have great documentation and it is an awesome implementation!

http://closedxml.codeplex.com/

You won't regret it :)

To clarify as well, once I used ClosedXML I was able to generate the workbook without any errors initially. It also made it extremely simple to change cell background color, I was able to change the cell DataType really easy. You honestly cannot go wrong! :)

Answer 3

Can you try without explicitly setting the relationship IDs? Specifically this part:

WorksheetPart worksheetPart = workbookPart1.AddNewPart<WorksheetPart>("rId" + (i).ToString());

Just do:

WorksheetPart worksheetPart = workbookPart1.AddNewPart<WorksheetPart>();

That just means the SDK automatically assign a relationship ID for you. If you really need the ID, then use this to get it:

workbookPart1.GetIdOfPart(worksheetPart);