none
Not able to implement Open XML SAX type approach in c# RRS feed

  • Question

  • My requirement is to export huge records with rows equal to 5 lakhs. I was using OpenXml approach like this :


     public void ExportCheck(DataSet ds, string destination)
            {
                using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
                {                        
                    var workbookPart = workbook.AddWorkbookPart();

                    workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                    workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
                   
                    foreach (System.Data.DataTable table in ds.Tables)
                    {
                        var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                        var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                        
                        sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);                  
                        
                        DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                        string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
                        
                        uint sheetId = 1;
                        if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                        {
                            sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                        }

                        DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                        sheets.Append(sheet);               

                        DocumentFormat.OpenXml.Spreadsheet.Row emptyRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                         DocumentFormat.OpenXml.Spreadsheet.Row colRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        
                        List<DataColumn> columns = new List<DataColumn>();
                        
                        foreach (System.Data.DataColumn column in table.Columns)
                        {
                            columns.Add(column);
                            
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("<b>"+ column.ColumnName + "</b>");                       

                            colRow.AppendChild(cell);                        
                        }

                        sheetData.AppendChild(colRow);
                        string valueN;
                        foreach (System.Data.DataRow dsrow in table.Rows)
                        {
                            
                            DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                            foreach (DataColumn col in columns)
                            {
                                DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;

                                if (col.DataType.Equals(typeof(byte[])) == true)
                                {
                                     valueN = ConvertByteArraytoInteger(dsrow[col.ColumnName]).ToString();
                                    
                                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(valueN);
                                }
                                else
                                {
                                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                                }
                                newRow.AppendChild(cell);
                            }

                            sheetData.AppendChild(newRow);
                        }
                   
                     }
                }
            }

            

    But now i have requirements in which data to be exported can be up to 10 lakh records with substantial number of columns. So i had to implement OpenXml Sax type approach as DOM approach is bit slow and memory consuming in case of large data.

    I couldn't find any helpful link which shows me how to use that approach and convert data table/data set to excel. In most link they are using existing spreadsheet to create new excel. Can anyone provide changes to be made to above code to implement SAX approach or any link regarding the same.

    Any help is deeply appreciated
    Thanks

    Tuesday, March 10, 2015 1:00 PM

Answers

  • Hi JB9952,

    Thanks for posting in MSDN forum.

    Based on the descirption, you were write large data into spreadsheet using Open XML SDK. And you want to find some helpul link about writing data in SAX instead of DOM.

    Here is a blog about this topic for your reference:

    Writing Large Excel Files with the Open XML SDK

    Hope it is hlepful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 11, 2015 5:50 AM
    Moderator
  • Hi Jb9952,

    >>He is exporting the data to a Output.xlsx file but where he is taking data I am not able to understand. In my case source is a data Table and destination is Excel Workbook. <<

    There is no data source in the code sample provied in above link. It is writing random data into the workbook. However there is no difference write large data into workbook about the data source caming from.

    >>One more thing I have doubt is till how many records can DOM approach of OpenXML can serve the purpose and SAX approach can be avoided<<

    Based on my understanding, it depneds on the enviroment of the computer and Office version.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 16, 2015 12:15 PM
    Moderator

All replies

  • Hi JB9952,

    Thanks for posting in MSDN forum.

    Based on the descirption, you were write large data into spreadsheet using Open XML SDK. And you want to find some helpul link about writing data in SAX instead of DOM.

    Here is a blog about this topic for your reference:

    Writing Large Excel Files with the Open XML SDK

    Hope it is hlepful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 11, 2015 5:50 AM
    Moderator
  • Thank you so much for replying.

    I watched that video..Only doubt is in the code part what is the source of data for him.He is exporting the data to a Output.xlsx file but where he is taking data I am not able to understand. In my case source is a data Table and destination is Excel Workbook. 

    One more thing I have doubt is till how many records can DOM approach of OpenXML can serve the purpose and SAX approach can be avoided...

    Hope you can help.

    Thanks

    Wednesday, March 11, 2015 6:18 AM
  • Hi Jb9952,

    >>He is exporting the data to a Output.xlsx file but where he is taking data I am not able to understand. In my case source is a data Table and destination is Excel Workbook. <<

    There is no data source in the code sample provied in above link. It is writing random data into the workbook. However there is no difference write large data into workbook about the data source caming from.

    >>One more thing I have doubt is till how many records can DOM approach of OpenXML can serve the purpose and SAX approach can be avoided<<

    Based on my understanding, it depneds on the enviroment of the computer and Office version.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 16, 2015 12:15 PM
    Moderator
  • The blog topic cannot be found. Anywhere else when i can check the solution for using the SAX approach.

    Tuesday, April 9, 2019 7:00 PM