OpenXML large dataset to excel very slow

Question

I am using OpenXml to convert large data to excel and i am using the following Export to Excel class

http://www.codeproject.com/Articles/692092/A-free-Export-to-Excel-Csharp-class-using-OpenXML

Below is the code in my class.

  public static bool CreateExcelDocumentAsStream(DataSet ds, string filename, System.Web.HttpResponse Response)
    {
        try
        {
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
            {
                WriteExcelFile(ds, document);
            }
            stream.Flush();
            stream.Position = 0;

            Response.ClearContent();
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";

            //  NOTE: If you get an "HttpCacheability does not exist" error on the following line, make sure you have
            //  manually added System.Web to this project's References.

            Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);
            Response.AddHeader("content-disposition", "attachment; filename=" + filename);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            byte[] data1 = new byte[stream.Length];
            stream.Read(data1, 0, data1.Length);
            stream.Close();
            Response.BinaryWrite(data1);
            Response.Flush();

            //  Feb2015: Needed to replace "Response.End();" with the following 3 lines, to make sure the Excel was fully written to the Response
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.SuppressContent = true;
            System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();

            return true;
        }
        catch (Exception ex)
        {
            Trace.WriteLine("Failed, exception thrown: " + ex.Message);
            return false;
        }
    }

    /// <summary>
    /// Create an Excel file, and write it to a file.
    /// </summary>
    /// <param name="ds">DataSet containing the data to be written to the Excel.</param>
    /// <param name="excelFilename">Name of file to be written.</param>
    /// <returns>True if successful, false if something went wrong.</returns>
    public static bool CreateExcelDocument(DataSet ds, string excelFilename)
    {
        try
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook))
            {
                WriteExcelFile(ds, document);

            }
            Trace.WriteLine("Successfully created: " + excelFilename);
            return true;
        }
        catch (Exception ex)
        {
            Trace.WriteLine("Failed, exception thrown: " + ex.Message);
            return false;
        }
    }


    private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
    {
        //  Create the Excel file contents.  This function is used when creating an Excel file either writing 
        //  to a file, or writing to a MemoryStream.
        spreadsheet.AddWorkbookPart();
        spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

        //  My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
        spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

        //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
        WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
        Stylesheet stylesheet = new Stylesheet();
        workbookStylesPart.Stylesheet = stylesheet;


        //  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
        uint worksheetNumber = 1;
        Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
        foreach (DataTable dt in ds.Tables)
        {
            //  For each worksheet you want to create
            string worksheetName = dt.TableName;

            //  Create worksheet part, and add it to the sheets collection in workbook
            WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
            Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName };

            // If you want to define the Column Widths for a Worksheet, you need to do this *before* appending the SheetData
            // http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/1d93eca8-2949-4d12-8dd9-15cc24128b10/

            sheets.Append(sheet);

            //  Append this worksheet's data to our Workbook, using OpenXmlWriter, to prevent memory problems
            WriteDataTableToExcelWorksheet(dt, newWorksheetPart);

            worksheetNumber++;
        }


        spreadsheet.WorkbookPart.Workbook.Save();



    }

    private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
    {
        OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
        writer.WriteStartElement(new Worksheet());
        writer.WriteStartElement(new SheetData());

        string cellValue = "";

        //  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
        //
        //  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
        //  cells of data, we'll know if to write Text values or Numeric cell values.
        int numberOfColumns = dt.Columns.Count;
        bool[] IsNumericColumn = new bool[numberOfColumns];
        bool[] IsDateColumn = new bool[numberOfColumns];

        string[] excelColumnNames = new string[numberOfColumns];
        for (int n = 0; n < numberOfColumns; n++)
            excelColumnNames[n] = GetExcelColumnName(n);

        //
        //  Create the Header row in our Excel Worksheet
        //
        uint rowIndex = 1;

        writer.WriteStartElement(new Row { RowIndex = rowIndex });
        for (int colInx = 0; colInx < numberOfColumns; colInx++)
        {
            DataColumn col = dt.Columns[colInx];
            AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
            IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
            IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
        }
        writer.WriteEndElement();   //  End of header "Row"

        //
        //  Now, step through each row of data in our DataTable...
        //
        double cellNumericValue = 0;
        foreach (DataRow dr in dt.Rows)
        {
            // ...create a new row, and append a set of this row's data to it.
            ++rowIndex;

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

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

                // Create cell with data
                if (IsNumericColumn[colInx])
                {
                    //  For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
                    //  If this numeric value is NULL, then don't write anything to the Excel file.
                    cellNumericValue = 0;
                    if (double.TryParse(cellValue, out cellNumericValue))
                    {
                        cellValue = cellNumericValue.ToString();
                        AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
                    }
                }
                else if (IsDateColumn[colInx])
                {
                    //  This is a date value.
                    DateTime dtValue;
                    string strValue = "";
                    if (DateTime.TryParse(cellValue, out dtValue))
                        strValue = dtValue.ToShortDateString();
                    AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer);
                }
                else
                {
                    //  For text cells, just write the input data straight out to the Excel file.
                    AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
                }
            }
            writer.WriteEndElement(); //  End of Row
        }
        writer.WriteEndElement(); //  End of SheetData
        writer.WriteEndElement(); //  End of worksheet

        writer.Close();
    }

    private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
    {
        //  Add a new Excel Cell to our Row 
        writer.WriteElement(new Cell
        {
            CellValue = new CellValue(cellStringValue),
            CellReference = cellReference,
            DataType = CellValues.String
        });
    }

    private static void AppendNumericCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
    {
        //  Add a new Excel Cell to our Row 
        writer.WriteElement(new Cell
        {
            CellValue = new CellValue(cellStringValue),
            CellReference = cellReference,
            DataType = CellValues.Number
        });
    }

    private static string ReplaceHexadecimalSymbols(string txt)
    {
        string r = "[\x00-\x08\x0B\x0C\x0E-\x1F\x26]";
        return Regex.Replace(txt, r, "", RegexOptions.Compiled);
    }

I am calling the method like this

            CreateExcelFile.CreateExcelDocumentAsStream(ds, ExportToExcel.FileName, response);

This is working fine but it has performance issue and its very slow. My excel data has 49K rows and 100 columns.

Is there way to improve performance?

Any suggestions would be appreciated .

Thanks in advance

Answer 1

Try closedXML and notice the difference.

https://www.codeproject.com/Tips/1107815/Export-an-Excel-File-using-ClosedXML