none
root element is missing RRS feed

  • Question

  • Hello,

    I am trying to implement the example found here.

    I get "root element is missing" on the line commented below.

    What am I doing wrong?  Thanks very much for the help.

    Sam

    //  Export List<> to excel
    public static void ExportToExcel(IList<PresentablePosition> list, string filePath)
            {
                // Create a document
                using (SpreadsheetDocument doc = OpenXmlUtilities.CreateSpreadsheetDocument(filePath))
                {
                    WriteRandomValues(doc, 10, 10);
                    return;
    		// remaining code omitted
    
    
    
    // Create spreadsheet document
    public static SpreadsheetDocument CreateSpreadsheetDocument(string filePath)
            {
                // Create a new spreadsheet
                SpreadsheetDocument doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook);
    
                // Add a WorkbookPart
                WorkbookPart workbookPart = doc.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();
    
                // Add a Shared String Table
                SharedStringTablePart sharedStringTablePart =  workbookPart.AddNewPart<SharedStringTablePart>();
                sharedStringTablePart.SharedStringTable = new SharedStringTable();
    
                // Add a WorksheetPart
                WorksheetPart workSheetPart = workbookPart.AddNewPart<WorksheetPart>();
                workSheetPart.Worksheet = new Worksheet(new SheetData());
    
                // Add sheets to the Workbook
                Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    
                // Append a new worksheet
                Sheet sheet = new Sheet { Id = doc.WorkbookPart.GetIdOfPart(workSheetPart), SheetId = 1, Name = "Sheet1" };
                
                sheets.Append(sheet);
    
                return doc;
            }
    
    
    // Write random values
    protected static void WriteRandomValues(SpreadsheetDocument myDoc, int numRows, int numCols)
            {
                WorkbookPart workbookPart = myDoc.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                
                string originalSheetId = workbookPart.GetIdOfPart(worksheetPart);
                WorksheetPart replacementPart = workbookPart.AddNewPart<WorksheetPart>();
                string replacementPartId = workbookPart.GetIdOfPart(replacementPart);
                
                OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); //Root element is missing error here
                OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);
    
                Row r = new Row();
                Cell c = new Cell();
                CellFormula f = new CellFormula();
                f.CalculateCell = true;
                f.Text = "RAND()";
                c.Append(f);
                CellValue v = new CellValue();
                c.Append(v);
    
                while (reader.Read())
                {
                    if (reader.ElementType == typeof(SheetData))
                    {
                        if (reader.IsEndElement)
                            continue;
    
                        writer.WriteStartElement(new SheetData());
    
                        for (int row = 0; row < numRows; row++)
                        {
                            writer.WriteStartElement(r);
    
                            for (int col = 0; col < numCols; col++)
                            {
                                writer.WriteElement(c);
                            }
                            writer.WriteEndElement(); //row
                        }
                        writer.WriteEndElement(); // sheetdata
                    }
                    else
                    {
                        if (reader.IsStartElement)
                            writer.WriteStartElement(reader);
                        else if (reader.IsEndElement)
                            writer.WriteEndElement();
                    }
                }
                reader.Close();
                writer.Close();
                Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Id.Value.Equals(originalSheetId)).First();
                sheet.Id.Value = replacementPartId;
                workbookPart.DeletePart(worksheetPart);
                myDoc.WorkbookPart.Workbook.Save();
            
            }
        }
    
    

     

     



    • Edited by SWheaties Monday, October 24, 2011 8:10 PM
    Monday, October 24, 2011 8:07 PM

Answers

All replies

  • Hi SWheaties,

    Thank you for posting.

    I tested on my side by using the sample code, however it works well on my side. I guess worksheetPart in your code may be null, try to debug to see it. You can also try to run the sample to see whether it works well on your side.

    Hope this can help you and just feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, October 26, 2011 9:09 AM
  • Hi Bruce,  Thank you for your reply.

    Worksheet part is not null:

    // Add a WorksheetPart

     

    WorksheetPart workSheetPart = workbookPart.AddNewPart<WorksheetPart

    >();

    workSheetPart.Worksheet =

    new Worksheet(new SheetData());

    I can place a breakpoint on the offending line and observe workSheetPart is not null.

    The example shows how to add rows to an existing file.  I need to create a new file.  Thanks for your help.

     
    Wednesday, October 26, 2011 11:06 PM
  • I found it.  I wont ask why there are methods for saving a work book as well as each sheet.

     

    public void ExportToExcel(IList<PresentablePosition> list, string filePath) 
    { 
    // Create a document 
    using (SpreadsheetDocument doc = OpenXmlUtilities.CreateSpreadsheetDocument(filePath)) 
    { 
    doc.WorkbookPart.WorksheetParts.First().Worksheet.Save(); // Save before writing 
    WriteRandomValues(doc, 10, 10);

     

     




    • Edited by SWheaties Thursday, October 27, 2011 12:44 AM
    • Proposed as answer by Bruce Song Friday, October 28, 2011 6:30 AM
    • Marked as answer by Bruce Song Wednesday, November 2, 2011 6:46 AM
    Thursday, October 27, 2011 12:40 AM
  • Hi SWheaties,

    Glad to hear that you resolved the problem and thank you for sharing the workaround with us. I guess after you created the spreadsheet, it just in the memory and we need to save the data in the DOM tree back to the part. Please refer to this article:

    http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.worksheet.save.aspx

    And I found an article also call save before writing data to it:

    http://cdonner.com/introduction-to-microsofts-open-xml-format-sdk-20-with-a-focus-on-excel-documents.htm

    Hope this can give you some hint.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Bruce Song Wednesday, November 2, 2011 6:46 AM
    Thursday, October 27, 2011 6:35 AM