none
Export 1lac records to xlsm(Macro-enabled) using OpenXML SAX method RRS feed

  • Question

  • Hello Team,

    We have macro enabled file provided by customer. We have append the data to the existing sheet.I tried with approaches provided in the below link.

    The file is getting corrupted, not able to open. Could someone help me with suggestions

    As shown in the below screenshot, i need to populate the data from fourth row.

    https://blogs.msdn.microsoft.com/brian_jones/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk/

    https://www.codeproject.com/Articles/877791/How-to-Create-Large-Excel-File-using-Openxml


    Friday, June 23, 2017 3:44 PM

Answers

All replies

  • Hello,

    What code do you use now?

    If you open the file, what error would you get? If you repair the file in Excel, what information would you receive?

    If you open the file in Open XML SDK Tool and check validation, would you get any error?

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 26, 2017 1:27 AM
    Moderator
  • Hello,

    Below is the code snippet. Basically, there is sheet called "Log" in the client provided workbook with columns.

    I need to just write data into rows. Attached the vlidation error i get in tool.

      public static byte[] PopulateExcelData(IList<Entity> data)
            {
                string path = @"report.xlsm";
                int noOfColumns = LogColumns.Count;
                int noOfrows = data.Count;

                byte[] returnArray;
                byte[] byteArray = File.ReadAllBytes(path);
                using (var stream = new MemoryStream())
                {
                    stream.Write(byteArray, 0, (int)byteArray.Length);
                    using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(stream, true))
                    {
                        myDoc.ChangeDocumentType(SpreadsheetDocumentType.MacroEnabledWorkbook);
                        WorkbookPart workbookPart = myDoc.WorkbookPart;
                        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                        string origninalSheetId = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Log").SingleOrDefault().Id;

                        WorksheetPart replacementPart = workbookPart.AddNewPart<WorksheetPart>();
                        string replacementPartId = workbookPart.GetIdOfPart(replacementPart);

                        OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
                        OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);

                        uint rowIdex = 3;
                        Row r = new Row();

                        while (reader.Read())
                        {
                            if (reader.ElementType == typeof(SheetData))
                            {
                                if (reader.IsEndElement)
                                    continue;
                                writer.WriteStartElement(new SheetData());

                                for (int row = 0; row < noOfrows; row++)
                                {
                                    var newRow = new Row() { RowIndex = ++rowIdex };
                                    writer.WriteStartElement(newRow);
                                    for (int col = 0; col < noOfColumns; col++)
                                    {

                                        writer.WriteStartElement(new Cell());
                                        var val = data[row].GetValue<object>(LogColumns[col]) as string ?? string.Empty;
                                        writer.WriteElement(new CellValue(ValidateValue(val)));
                                        writer.WriteEndElement();
                                    }
                                    writer.WriteEndElement();
                                }

                                writer.WriteEndElement();
                            }
                            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(origninalSheetId)).First();
                        sheet.Id.Value = replacementPartId;
                        workbookPart.DeletePart(worksheetPart);
                    }

                    returnArray = stream.ToArray();
                }

                return returnArray;
            }

    Monday, June 26, 2017 2:56 PM
  • Hello,

    The code could insert cells into rows but old row elements would be omitted, then old rows would be appended into the worksheetpart, so you would get the validation error "unknownelement". I could reproduce this error and it would not break the workbook. However, old rows are removed using the code above.

    To insert text into specific cells or rows, i would suggest you follow How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    For the error about the relationship, what object do you insert into the sheet? According to the error message, you need to check the relationship between these objects and the new worksheet part you insert. In my opinion, if you insert text referring to the link above, you would not get the error because you dont need to insert a new worksheetpart and you could refer to the old worksheetpart and append new cells.

    Regards,

    Celeste 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 27, 2017 10:34 AM
    Moderator
  • Thanks for you time.

    I believe the link you shared is based on  DOM approach, i have used this approach earlier, it use to take 4 sec to save 15k records, thats where my colleague suggested me SAX approach. 

    I was looking for a quick start for updating the existing xlsm file using SAX.

    Tuesday, June 27, 2017 2:00 PM
  • Hello,

    I understand but SAX approach is not suitable for updating existing file.  

    You may check the similar thread:

    https://stackoverflow.com/questions/14264022/openxml-sax-method-adding-row-to-existing-tab

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by soni_7 Sunday, December 24, 2017 11:20 AM
    Wednesday, June 28, 2017 5:21 AM
    Moderator