DocumentFormat.Openxml add datatable to worksheet without looping into rows

Question

Is there any way to insert datatable directly into the worksheet without looping the DataRows. I have a very large datatable with more than 200k rows. so looping through each row is taking time while performing export the excel sheet. Can somebody suggest a way below is the code snippet am using

 foreach (DataRow dr in dt.Rows)
            {

                ++rowIndex;

                writer.WriteStartElement(new Row { RowIndex = rowIndex });

                for (int colInx = 0; colInx < numberOfColumns; colInx++)
                {
                    cellValue = dr.ItemArray[colInx].ToString();
                    cellValue = ReplaceHexadecimalSymbols(cellValue);
                    cellReference = excelColumnNames[colInx] + rowIndex.ToString();

                    // Create cell with data
                    if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
                    {
                        //  For numeric cells without any decimal places.
                        //  If this numeric value is NULL, then don't write anything to the Excel file.
                        cellFloatValue = 0;
                        bool bIncludeDecimalPlaces = IsFloatColumn[colInx];
                        if (double.TryParse(cellValue, out cellFloatValue))
                        {
                            cellValue = cellFloatValue.ToString(CultureInfo.InvariantCulture);
                            AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces, writer);
                        }
                    }
                    else if (IsDateColumn[colInx])
                    {
                        //  For date values, we save the value to Excel as a number, but need to set the cell's style to format
                        //  it as either a date or a date-time.
                        DateTime dateValue;
                        if (DateTime.TryParse(cellValue, out dateValue))
                        {
                            AppendDateCell(cellReference, dateValue, writer);
                        }
                        else
                        {
                            //  This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank.
                            AppendTextCell(cellReference, cellValue, writer);
                        }
                    }
                    else
                    {
                        //  For text cells, just write the input data straight out to the Excel file.
                        AppendTextCell(cellReference, cellValue, writer);
                    }
                }
                writer.WriteEndElement(); //  End of Row
            }

Answer 1

I had to do the same thing . I needed to pass the data table and have it create the spread sheet. This worked for me.

http://lateral8.com/articles/2010/3/5/openxml-sdk-20-export-a-datatable-to-excel.aspx

using System.Data;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public void ExportDataTable(
     DataTable table,
     string exportFile)
    {
        //create the empty spreadsheet template and save the file
        //using the class generated by the Productivity tool
        ExcelDocument excelDocument = new ExcelDocument();
        excelDocument.CreatePackage(exportFile);

        //populate the data into the spreadsheet
        using (SpreadsheetDocument spreadsheet =
            SpreadsheetDocument.Open(exportFile, true))
        {
            WorkbookPart workbook = spreadsheet.WorkbookPart;
            //create a reference to Sheet1
            WorksheetPart worksheet = workbook.WorksheetParts.Last();
            SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>();

            //add column names to the first row
            Row header = new Row();
            header.RowIndex = (UInt32)1;

            foreach (DataColumn column in table.Columns)
            {
                Cell headerCell = createTextCell(
                    table.Columns.IndexOf(column) + 1,
                    1,
                    column.ColumnName);

                header.AppendChild(headerCell);
            }
            data.AppendChild(header);

            //loop through each data row
            DataRow contentRow;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                contentRow = table.Rows[i];
                data.AppendChild(createContentRow(contentRow, i + 2));
            }
        }
    }

Helper methods:

    #region WorkBook Methods

    /// <summary>
    /// Gets the Excel column name based on a supplied index number.
    /// </summary>
    /// <returns>1 = A, 2 = B... 27 = AA, etc.</returns>
    private string getColumnName(int columnIndex)
    {
        int dividend = columnIndex;
        string columnName = String.Empty;
        int modifier;

        while (dividend > 0)
        {
            modifier = (dividend - 1) % 26;
            columnName =
                Convert.ToChar(65 + modifier).ToString() + columnName;
            dividend = (int)((dividend - modifier) / 26);
        }

        return columnName;
    }

    private Cell createTextCell(
        int columnIndex,
        int rowIndex,
        object cellValue)
    {
        Cell cell = new Cell();

        cell.DataType = CellValues.InlineString;
        cell.CellReference = getColumnName(columnIndex) + rowIndex;

        InlineString inlineString = new InlineString();
        Text t = new Text();

        t.Text = cellValue.ToString();
        inlineString.AppendChild(t);
        cell.AppendChild(inlineString);

        return cell;
    }

    private Row createContentRow(
        DataRow dataRow,
        int rowIndex)
    {
        Row row = new Row

        {
            RowIndex = (UInt32)rowIndex
        };

        for (int i = 0; i < dataRow.Table.Columns.Count; i++)
        {
            Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
            row.AppendChild(dataCell);
        }
        return row;
    }

    #endregion