OpenXML (SAX Method) - Adding row to existing tab

Question

I am trying to create an Excel document using OpenXML (SAX method). When my method is called I want to check to see if a tab has already been created for a given key. If it is I would like to just append a row to the bottom of that tab. If the tab hasn't been created for a given key I create a new tab like;

      part = wbPart.AddNewPart<WorksheetPart>();

                        string worksheetName = row.Key[i].ToString();

                        Sheet sheet = new Sheet() { Id = document.WorkbookPart.GetIdOfPart(part), SheetId = sheetNumber, Name = worksheetName };
                        sheets.Append(sheet);

                        writer = OpenXmlWriter.Create(part);
                        writer.WriteStartElement(new Worksheet());
                        writer.WriteStartElement(new SheetData());

                        currentrow = 1;

                        string header = Header + "\t" + wrapper.GetHeaderString(3, 2, -1); //need to fix
                        WriteDataToExcel(header, currentrow, 0, writer);
                        currentrow++;


                        writer.WriteEndElement();
                        writer.WriteEndElement();
                        writer.Close();

If the a tab as already been created I recall sheet using the following code;

private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
    {
        IEnumerable<Sheet> sheets =
           document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
           Elements<Sheet>().Where(s => s.Name == sheetName);

        if (sheets.Count() == 0)
        {
            // The specified worksheet does not exist.

            return null;
        }

        string relationshipId = sheets.First().Id.Value;
        WorksheetPart worksheetPart = (WorksheetPart)
             document.WorkbookPart.GetPartById(relationshipId);
        return worksheetPart;

    }

When the correct Worksheet part is returned I try and add the new row by pointing my OpenXmlWriter to the correct part then adding the row;

   part = GetWorksheetPartByName(document, row.Key[i].ToString());

                        writer = OpenXmlWriter.Create(part);
                        writer.WriteStartElement(part.Worksheet);
                        writer.WriteStartElement(part.Worksheet.GetFirstChild<SheetData>());
                        SheetData sheetData = part.Worksheet.GetFirstChild<SheetData>();
                        Row lastRow = sheetData.Elements<Row>().LastOrDefault();

The code runs however I always end up with just one row (the initial one I added when first creating the tab). No subsequent rows show up in the spreadsheet.

I will be adding a lot of rows (50,000+) and would prefer not to have to create a new file and copy the information over each time.

This related SO question may help - http://stackoverflow.com/q/6665307/53614 (you seem to be missing the `InsertAfter` part) — barrowc, Jan 10 '13 at 23:08

score 4 · Accepted Answer · answered Jan 21 '13 at 12:10

4

From my experience, using the SAX method to write (ie, with OpenXmlWriter) works best for new things (parts, worksheets, whatnot). When you use OpenXmlWriter.Create(), that's like overwriting the original existing data for the part (WorksheetPart in this case). Even though in effect, it's not. It's complicated.

As far as my experiments went, if there's existing data, you can't edit data using OpenXmlWriter. Not even if you use the Save() function or close the OpenXmlWriter correctly. For some reason, the SDK will ignore your efforts. Hence the original one row that you added.

If you're writing 50,000 rows, it's best to do so all at one go. Then the SAX method will be useful. Besides, if you're writing one row (at a time?), the speed benefits of using SAX versus the DOM method is negligible.

answered Jan 21 '13 at 12:10

Vincent Tan

3,058
22
21

I am writing one row at time. I keep looking and like you said I think the speed benefits weren't really there so now I'm looking at a DOM approach. Thanks. – JMorg Feb 01 '13 at 20:55
@Vincent : Very helpful answer for me. I am struggling with similar kind of issue can you please help. I am writing Excel file with SAX method. In that I want some header cells to be merged. Searched a lot but merging of cells is possible only through DOM method. So for now I am Writing complete excel in SAX, opening it again in DOM method and after merging I am saving the same. With lesser records it works fine but it gives out of memory exception when record counts is high. Is there any way where I can achieve the functionality? Please respond to http://stackoverflow.com/q/29845898/3103066 – Kiran Ramchandra Parab Apr 29 '15 at 08:08

score 0 · Answer 2 · answered Jan 17 '19 at 04:05

According to this site work with exist Excel with OpenXMLWriter : OpenXMLWriter can only operate a new Worksheet instead of an existing document. So I'm afraid you cannot insert values into particular cells of existing spreadsheet using OpenXMLWriter.

You could read all data in your exist Excel file , then seems you need to add rows(50,000+) I recommend use openxmlwriter to write old and new data to a new Excel file at once. If you use DOM approach it might cause memory problem after you append a lot of rows(50,000+).

OpenXML (SAX Method) - Adding row to existing tab

2 Answers2