none
Open XML - Generated Excel Files are Corrupted RRS feed

  • Question

  • I am trying to export a large amount of data to Excel using Open XML SDK 2.5.  However I am not able to open larger files that are generated.  When I try to open the Excel files that are generated, I get a prompt that the file needs repairing.   

    The code I am using is from the following link (The helper class I am using is unmodified from the article)- https://www.codeproject.com/Articles/877791/How-to-Create-Large-Excel-File-using-Openxml   

    Here is the function I in my WPF code that I am calling the helper class from-

                                                     

            public static void LargeExportTest(List<ExcelData> arrExport, string filename, int sheetnumber, List<String> map)
            {
                try
                {
                    int batchCount = 0;
                    using (var spreadSheet = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
                    {
                        // create the workbook
                        var workbookPart = spreadSheet.AddWorkbookPart();

                        var openXmlExportHelper = new OpenXmlWriterHelper();
                        //openXmlExportHelper.SaveCustomStylesheet(workbookPart);


                        var workbook = workbookPart.Workbook = new Workbook();
                        var sheets = workbook.AppendChild<Sheets>(new Sheets());

                        // create worksheet 1
                        var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                        var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
                        sheets.Append(sheet);

                        using (var writer = OpenXmlWriter.Create(worksheetPart))
                        {

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

                                for (int rowNum = 2; rowNum <= 4 - 1; rowNum++)
                            {

                                int u = Convert.ToInt32(rowNum.ToString());

                                writer.WriteStartElement(new Row() { RowIndex = Convert.ToUInt32(u) + 6 });

                                //insert test case name in first column
                                openXmlExportHelper.WriteCellValueSax(writer, arrExport[u].Name.ToString() == null ? "  x  "  : stripNonValidXMLCharacters(arrExport[u].Name.ToString()), CellValues.String);
                                int NextPosition = 0;
                                for (int col = 0; col < arrExport[u].ColumnData.Count; col++)
                                {
                                    if (!((col + 1) >= arrExport[u].ColumnData.Count - 1))
                                    {
                                        NextPosition = (arrExport[u].ColumnData[col + 1].ColumnNumber - arrExport[u].ColumnData[col].ColumnNumber);

                                    }
                                    else
                                    {
                                        NextPosition = arrExport[u].ColumnData[col].ColumnNumber - 1;
                                    }

                                    for (int x = 0; x < NextPosition; x++)
                                    {

                                        openXmlExportHelper.WriteCellValueSax(writer, "    Y    ", CellValues.String);


                                    }


                                        openXmlExportHelper.WriteCellValueSax(writer, arrExport[u].ColumnData[col].ColumnData.ToString() == null ? String.Empty : stripNonValidXMLCharacters(arrExport[u].ColumnData[col].ColumnData.ToString()), CellValues.String);

                                        Console.WriteLine(arrExport[u].ColumnData[col].ColumnData.ToString());
                                    Console.WriteLine(string.Format("Length: {0} , col is {1}, u is: {2}", arrExport[u].ColumnData[col].ColumnData.Length, col.ToString(), u.ToString()));
                                }
                                arrExport[u].ColumnData.Clear();  //reduce memory

                                writer.WriteEndElement(); //end of Row
                                batchCount++;
                            }

                            writer.WriteEndElement(); //end of SheetData
                            writer.WriteEndElement(); //end of worksheet
                            writer.Close();

                        }
                        //create the share string part using sax like approach too
                        openXmlExportHelper.CreateShareStringPart(workbookPart);

                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

    I am also including part of the sheet1.xml from one of the generated files-

    <?xml version="1.0" encoding="utf-8"?><x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><x:sheetData><x:row r="8"><x:c t="str"><x:v>Rep1Req_Scr_5d694939-74a7-4f46-bc3f-36a3f9b15c42</x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>'1</x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>AAA</x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>XXX</x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>Rory Bradley</x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>'999999999</x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>SSN</x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>'999</x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>'000</x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c><x:c t="str"><x:v>    Y    </x:v></x:c></x:row></x:sheetData></x:worksheet>


    Friday, June 28, 2019 1:47 PM