locked
'System.OutOfMemoryException' for documentformat.open xml RRS feed

  • Question

  • User1132714760 posted

    Hi

    I have 1000000 plus records in my datatable.

    I need create excel using those records in datatable.

    I am using documentformat.openXMl.

    I am getting "System.OutOfMemoryException" error.

    here is my code

    static void Main(string[] args)
            {
               String sFile = "C:\\Users\\xyz\\Documents\\test2.xlsx";
                if (File.Exists(sFile))
                {
                    File.Delete(sFile);
                }
                DataTable dt = Select * from EMP;//get data from DB table
               

                using (SpreadsheetDocument spreedDoc = SpreadsheetDocument.Create(sFile,
           DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
                {
                    WorkbookPart wbPart = spreedDoc.WorkbookPart;
                    if (wbPart == null)
                    {
                        wbPart = spreedDoc.AddWorkbookPart();
                        wbPart.Workbook = new Workbook();
                    }
                    WorksheetPart worksheetPart = wbPart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet();
                    WorkbookStylesPart stylePart = wbPart.AddNewPart<WorkbookStylesPart>();
                    stylePart.Stylesheet = GenerateStylesheet();
                    stylePart.Stylesheet.Save();
                    Columns columns = new Columns(
                            new Column // Id column
                            {
                                Min = 1,
                                Max = 3,
                                Width = 24,
                                CustomWidth = true
                            },
                            new Column // Name and Birthday columns
                            {
                                Min = 4,
                                Max = 4,
                                Width = 15,
                                CustomWidth = true
                            },
                            new Column // Salary column
                            {
                                Min = 5,
                                Max = 6,
                                Width = 8,
                                CustomWidth = true
                            });

                    worksheetPart.Worksheet.AppendChild(columns);
                    Sheets sheets = wbPart.Workbook.AppendChild(new Sheets());

                    Sheet sheet = new Sheet() { Id = wbPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Exception" };

                    sheets.Append(sheet);
                    wbPart.Workbook.Save();
                    SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());

                    var workingSheet = ((WorksheetPart)wbPart.GetPartById(sheet.Id)).Worksheet;              
                    Int32 rowindex = 1;
                    Row row = null;
                    for (int i=0;i<3;i++)
                    {
                       row = new Row();
                        row.RowIndex = (UInt32)rowindex;
                        if (rowindex == 1)
                        {
                            row.Append(ConstructCell("STRUCT",CellValues.String,3));
                        }
                        else if (rowindex == 2)
                        {
                            row.Append(ConstructCell("testtest", CellValues.String,4));
                        }
                        else if (rowindex == 3)
                        {
                            row.Append(ConstructCell("Run Date:", CellValues.String, 5) , ConstructCell("testdate", CellValues.String,0));
                        }
                        sheetData.AppendChild(row);
                        rowindex++;
                    }
                    rowindex = 5;
                    foreach (DataRow emp in dt.Rows) //app dt.Rows.count=1000000
                    {
                        row = new Row();
                        row.RowIndex = (UInt32)rowindex;

                        if (rowindex == 5) //Header 
                        {
                           row.Append(ConstructCell("Name", CellValues.String, 2),ConstructCell("Email", CellValues.String, 2));
                       }
                       else
                     {
                            row.Append(ConstructCell(emp[0].ToString(), CellValues.String,1),
                                ConstructCell(emp[1].ToString(), CellValues.String, 1),
                                ConstructCell(emp[2].ToString(), CellValues.String, 1),
                                ConstructCell(emp[3].ToString(), CellValues.String, 1),
                                ConstructCell(emp[4].ToString(), CellValues.Number,1),
                                ConstructCell(emp[5].ToString(), CellValues.String, 1),

                                 ConstructCell(emp[6].ToString(), CellValues.String, 1),
                                ConstructCell(emp[7].ToString(), CellValues.String, 1),
                                ConstructCell(emp[8].ToString(), CellValues.String, 1),
                                ConstructCell(emp[9].ToString(), CellValues.Number,1),
                                ConstructCell(emp[10].ToString(), CellValues.String, 1),

                                ConstructCell(emp[11].ToString(), CellValues.String, 1)

                      );
                       }

                       sheetData.AppendChild(row);
                       rowindex++;
                   }             

                        sheetData.AppendChild(row);                    
                        Console.WriteLine(j);

                        rowindex++;

                    }

                    
                    MergeCells mergeCells = new MergeCells();
                   
                    mergeCells.Append(new MergeCell() { Reference = new StringValue("A1:C1") });
                    mergeCells.Append(new MergeCell() { Reference = new StringValue("A2:C2") });               
                    worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First());              
                    worksheetPart.Worksheet.Save();
                }
            }
          
            private static Cell ConstructCell(string text,CellValues dataType,uint styleIndex)
            {
                return new Cell()
                {
                    CellValue = new CellValue(text),
                    DataType = new EnumValue<CellValues>(dataType),               
                    StyleIndex = styleIndex
                };

            }      
            private static Stylesheet GenerateStylesheet()
            {
                Stylesheet styleSheet = null;

                Fonts fonts = new Fonts(
                    new Font( // Index 0 - default
                        new FontSize() { Val = 10 },
                        new FontName() { Val = "Arial" }
                    ),
                    new Font( // Index 1 - header
                        new FontSize() { Val = 10 },
                        new Bold(),                  
                        new FontName() { Val = "Arial" }
                    ),
                    new Font( // Index 2 - header
                        new FontSize() { Val = 12 },
                        new Bold(),
                        new FontName() { Val = "Arial" }),
                     new Font( // Index 3 - header
                        new FontSize() { Val = 14 },
                        new Bold(),
                        new FontName() { Val = "Arial" },
                        new Underline() { Val = UnderlineValues.Single }),
                     new Font( // Index 4 - header
                        new FontSize() { Val = 10 },
                        new Bold(),
                        new FontName() { Val = "Verdana" },
                        new Color() { Rgb = "FFFFFF" })
                    );

                Fills fills = new Fills(
                         new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
                         new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
                         new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "477344" } })
                { PatternType = PatternValues.Solid }) // Index 2 - header


                    );

                Borders borders = new Borders(
                        new Border(), // index 0 default
                        new Border( // index 1 black border
                            new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new DiagonalBorder())
                    );

                CellFormats cellFormats = new CellFormats(
                        new CellFormat (),// default
                        new CellFormat { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // body
                        new CellFormat { FontId = 4, FillId = 2, BorderId = 1, ApplyFill = true,Alignment=new Alignment { Horizontal = HorizontalAlignmentValues.Center } }, // header
                        new CellFormat { FontId = 3, FillId = 0, BorderId = 0},
                        new CellFormat { FontId = 2, FillId = 0, BorderId = 0 },
                        new CellFormat { FontId = 1, FillId = 0, BorderId = 0 }
                    );          
                styleSheet = new Stylesheet(fonts, fills, borders, cellFormats);

                return styleSheet;
            }

    Wednesday, January 16, 2019 4:06 AM

Answers