none
how to make openxml insert spreadsheet rows most efficiently

    Question

  • I use OpenXML v 2.8.1.0 to create an excel report for a web site. Spreadsheet has 22 columns. Takes 30 seconds to render 15,000 rows, but that's too long for a web page. Can I do this more efficiently?

    Code snippet is below (left out parts not germane to the issue). Suspect I'm inefficient in all that looping through each cell to set its cellreference, and then setting each row's cellreference as well.

    I left out code where I open the spreadsheet I am populating - it contains headers and a sample detail row, which has all formatting I want but all values are blank.

    In code I clone that row, set values and cellreference for each cell, set rowreference for the row, insert the row, then get the next row of data from the database and repeat.

    Or is 500 rows per second about as fast as OpenXML is capable of?

    Thanks.

    public class MyReport
    {
        public byte[] Get(List<dbRow> dbRows)
        {
            // dbRows is the result of "Select col0, col1, ... col21 from dbTable"

            // first 6 rows are the report headers
            // row 7 is the blank detail row.
            Row templateDetailRow = sheetData.Elements<Row>().Where(r => r.RowIndex == 7).First();
            // start inserting data rows at row number 8
            uint currentRowPointer = sheetData.Elements<Row>().LastOrDefault().RowIndex + 1; // start adding rows after the current last row
            Row currentBottomRow = sheetData.Elements<Row>().LastOrDefault(); // initialize to last row before we start adding rows

            foreach (dbRow r in dbRows)
            {
                Row newRow = (Row)templateDetailRow.Clone();
                List<Cell> cells = newRow.Elements<Cell>().OrderBy(c => c.CellReference.Value).ToList();
                SetCellsInRow(cells, r, currentRowPointer);
                newRow.RowIndex = currentRowPointer;
                InsertRow(sheetData, newRow);
                currentRowPointer++;
            }

        }
        private void SetCellsInRow(List<Cell> cells, dbRow source, uint rowIndex)
        {
            SetCell(cells[0], source.string0, rowIndex);
            SetCell(cells[1], source.int1, rowIndex);
            SetCell(cells[2], source.string2, rowIndex);
            SetCell(cells[3], source.string3, rowIndex);
            SetCell(cells[4], source.string4, rowIndex);
            SetCell(cells[5], String.Format("{0:MM/dd/yyyy}", source.date5), rowIndex);
            SetCell(cells[6], source.string6, rowIndex);
            SetCell(cells[7], source.int7, rowIndex);
            SetCell(cells[8], source.string8, rowIndex);
            SetCell(cells[9], source.string9, rowIndex);
            SetCell(cells[10], source.date10.ToString("MM/dd/yyyy"), rowIndex);
            SetCell(cells[11], String.Format("{0:MM/dd/yyyy}", source.date11), rowIndex);
            SetCell(cells[12], String.Format("{0:MM/dd/yyyy}", source.date12), rowIndex);
            SetCell(cells[13], source.string13, rowIndex);
            SetCell(cells[14], String.Format("{0:MM/dd/yyyy}", source.date14), rowIndex);
            SetCell(cells[15], source.int15 ?? 0, rowIndex);
            SetCell(cells[16], String.Format("{0:MM/dd/yyyy}", source.date16), rowIndex);
            SetCell(cells[17], source.string17, rowIndex);
            SetCell(cells[18], source.string18, rowIndex);
            SetCell(cells[19], source.int19, rowIndex);
            SetCell(cells[20], source.string20, rowIndex);
            SetCell(cells[21], source.string21, rowIndex);
        }

        private void SetCell(Cell theCell, string val, uint rowIndex)
        {   // a NON-SHARED string is going in to the cell
            theCell.CellValue = new CellValue(val);
            theCell.DataType = new EnumValue<CellValues>(CellValues.String);
            theCell.CellReference = Regex.Replace(theCell.CellReference, @"\d+", rowIndex.ToString());
        }

        private void SetCell(Cell theCell, int val, uint rowIndex)
        {    // an int is going in to the cell
            theCell.CellValue = new CellValue(val.ToString());
            theCell.DataType = new EnumValue<CellValues>(CellValues.Number);
            theCell.CellReference = Regex.Replace(theCell.CellReference, @"\d+", rowIndex.ToString());
        }

        private static void InsertRow(SheetData sheetData, Row row)
        {
            sheetData.InsertAfter(row, currentBottomRow);
            currentBottomRow = row;
        }
    }


    mercredi 11 juillet 2018 18:49

Toutes les réponses

  • Hello D-Who,

    Does the dbRow a custom class? Could you get your into a dataset?  Eburgos has ever share a good solution to export a dataset to a workbook here.

    I tried to use a datset with a data table which contains 22 fields and 16K rows data to test the code. It spent about 3 seconds to finish. I would suggest you try to refer to the code and adjust it for your need.

    private void ExportDataSet(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 headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
    
                        List<String> columns = new List<string>();
                        foreach (System.Data.DataColumn column in table.Columns)
                        {
                            columns.Add(column.ColumnName);
    
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                            headerRow.AppendChild(cell);
                        }
    
    
                        sheetData.AppendChild(headerRow);
    
                        foreach (System.Data.DataRow dsrow in table.Rows)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                            foreach (String col in columns)
                            {
                                DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                                newRow.AppendChild(cell);
                            }
    
                            sheetData.AppendChild(newRow);
                        }
    
                    }
                }
            }
    string destination = @"C:\Users\terryx\Desktop\TestFolder\1.xlsx";
                DateTime StartTime = DateTime.Now;
                ExportDataSet(dataSet, destination);
                DateTime EndTime = DateTime.Now;
                MessageBox.Show("Spent Time" + (EndTime-StartTime).TotalSeconds + " second");

    Best Regards,

    Terry


    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.

    jeudi 12 juillet 2018 09:05
    Modérateur