Click here to Skip to main content
14,303,709 members

Export very large data to Excel file

Rate this:
4.87 (15 votes)
Please Sign up or sign in to vote.
4.87 (15 votes)
27 Sep 2013CPOL
This code solves the problem when exporting very large data size to Excel file.

Introduction

This code solves the popular problem when creating a large Excel file with massive amounts of rows.

We faced this problem (and still are) when we tried to export data from a database and the data size was very large. Using any common third party library such as NOPI (http://npoi.codeplex.com), EPPlus (http://epplus.codeplex.com), or even using OpenXML (http://www.codeproject.com/Articles/371203/Creating-basic-Excel-workbook-with-Open-XML), we got stuck at OutofMemoryException exception.

Background

OpenXML is a standard and an open format for Office files. It defines the document structure and the elements for Office documents like .xlsx for Excel. I used here the Northwind database, but you can use your own database. You don't have to install Microsoft Office to create an Excel file with the OpenXML SDK.

Using the code

To be able to run this code, you need to install the OpenXML SDK 2.0 from Download Center. You need also to reference the WindowsBase assembly version 3.0 or 4.0 in this project (WindowsBase.dll). Please note that the WindowsBase assembly version 4.0 comes with .NET framework 4.5.

I developed this console application using Visual Studio 2012.

If you look at the Program class, you will find a method called ExportToOxml which is responsible for the creation of the Excel file.

The cause of the problem is we create the object that represents the Excel file in memory and we start to add cell data to it. Your application's consumed memory will start to increase and increase until it raises the OutOfMemoryException exception.

You will find out that I solved this problem by dividing the data that is coming from the database into groups of rows (it is configurable), and each group will be written to an individual sheet in the Excel workbook. I write the whole Excel object to the disk after adding each sheet to it and free the memory of the sheet data, then reopen the same Excel file again (without loading its data in memory), and add the next group of data in a new sheet, save it to the disk, and the process continues until all the data we have are written to the Excel file.

Please use the variable rowsPerSheet to control the number of rows per sheet that you want to produce. For example, I use 150000 value when I exported 700000 rows. if you are going to export more than Excel file at the same time (as in web sites/services), then choose smaller value of rowsPerSheet.

rowsPerSheet  = 50000; 

You will notice that I did not load my data into the DataTable, as this will consume the application memory, but I used a DataReader which is fantastic in this case.

The project is for demonstration purposes only.

Please read my code comments inside the code, as it explains more about the logic and execution.

Do you have in your mind the question of the cell styles (font, colors, etc.)? I will update the article very soon with it.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
 
namespace ExportToExcel
{
    class Program
    {
        static private int rowsPerSheet = 100;
        static private DataTable ResultsData=new DataTable();
        static void Main(string[] args)
        {
            const string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";
            using (var connection =
                   new SqlConnection(@"Data Source=localhost\SQL2008;" + 
                       @"Initial Catalog=Northwind;Integrated Security=SSPI"))
                {
                    var command =new SqlCommand(queryString, connection);
                    connection.Open();

                    SqlDataReader reader = command.ExecuteReader();
                    
                    int c = 0;
                    bool firstTime = true;

                    //Get the Columns names, types, this will help
                    //when we need to format the cells in the excel sheet.
                    DataTable dtSchema = reader.GetSchemaTable();
                    var listCols = new List<DataColumn>();
                    if (dtSchema != null)
                    {
                        foreach (DataRow drow in dtSchema.Rows)
                        {
                            string columnName = Convert.ToString(drow["ColumnName"]);
                            var column = new DataColumn(columnName, (Type)(drow["DataType"]));
                            column.Unique = (bool)drow["IsUnique"];
                            column.AllowDBNull = (bool)drow["AllowDBNull"];
                            column.AutoIncrement = (bool)drow["IsAutoIncrement"];
                            listCols.Add(column);
                            ResultsData.Columns.Add(column);
                        }
                    }

                    // Call Read before accessing data. 
                    while (reader.Read())
                    {
                        DataRow dataRow = ResultsData.NewRow();
                        for (int i = 0; i < listCols.Count; i++)
                        {
                            dataRow[(listCols[i])] = reader[i];
                        }
                        ResultsData.Rows.Add(dataRow);
                        c++;
                        if (c == rowsPerSheet)
                        {
                            c = 0;
                            ExportToOxml(firstTime);
                            ResultsData.Clear();
                            firstTime = false;
                        }
                    }
                    if (ResultsData.Rows.Count > 0)
                    {
                        ExportToOxml(firstTime);
                        ResultsData.Clear();
                    }
                    // Call Close when done reading.
                    reader.Close();
                }
        }
 
        private static void ExportToOxml(bool firstTime)
        {
            const string fileName = @"C:\MyExcel.xlsx";
 
            //Delete the file if it exists. 
            if (firstTime && File.Exists(fileName))
            {
                File.Delete(fileName);
            }
 
            uint sheetId = 1; //Start at the first sheet in the Excel workbook.
            
            if (firstTime)
            {
                //This is the first time of creating the excel file and the first sheet.
                // Create a spreadsheet document by supplying the filepath.
                // By default, AutoSave = true, Editable = true, and Type = xlsx.
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
                    Create(fileName, SpreadsheetDocumentType.Workbook);
 
                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
 
                // Add a WorksheetPart to the WorkbookPart.
                var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);
 

                var bold1 = new Bold();
                CellFormat cf = new CellFormat();
 

                // Add Sheets to the Workbook.
                Sheets sheets;
                sheets = spreadsheetDocument.WorkbookPart.Workbook.
                    AppendChild<Sheets>(new Sheets());
 
                // Append a new worksheet and associate it with the workbook.
                var sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                        GetIdOfPart(worksheetPart),
                    SheetId = sheetId,
                    Name = "Sheet" + sheetId
                };
                sheets.Append(sheet);
 
                //Add Header Row.
                var headerRow = new Row();
                foreach (DataColumn column in ResultsData.Columns)
                {
                    var cell = new Cell { DataType = CellValues.String, 
                                     CellValue = new CellValue(column.ColumnName) };
                    headerRow.AppendChild(cell);
                }
                sheetData.AppendChild(headerRow);
 
                foreach (DataRow row in ResultsData.Rows)
                {
                    var newRow = new Row();
                    foreach (DataColumn col in ResultsData.Columns)
                    {
                        var cell = new Cell
                        {
                            DataType = CellValues.String,
                            CellValue = new CellValue(row[col].ToString())
                        };
                        newRow.AppendChild(cell);
                    }
 
                    sheetData.AppendChild(newRow);
                }
                workbookpart.Workbook.Save();
 
                spreadsheetDocument.Close();
            }
            else
            {
                // Open the Excel file that we created before, and start to add sheets to it.
                var spreadsheetDocument = SpreadsheetDocument.Open(fileName, true);
 
                var workbookpart = spreadsheetDocument.WorkbookPart;
                if (workbookpart.Workbook == null)
                    workbookpart.Workbook = new Workbook();
 
                var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);
                var sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
 
                if (sheets.Elements<Sheet>().Any())
                {
                    //Set the new sheet id
                    sheetId = sheets.Elements<Sheet>().Max(s => s.SheetId.Value) + 1;
                }
                else
                {
                    sheetId = 1;
                }
 
                // Append a new worksheet and associate it with the workbook.
                var sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                        GetIdOfPart(worksheetPart),
                    SheetId = sheetId,
                    Name = "Sheet" + sheetId
                };
                sheets.Append(sheet);
 
                //Add the header row here.
                var headerRow = new Row();
 
                foreach (DataColumn column in ResultsData.Columns)
                {
                    var cell = new Cell { DataType = CellValues.String, 
                            CellValue = new CellValue(column.ColumnName) };
                    headerRow.AppendChild(cell);
                }
                sheetData.AppendChild(headerRow);
                
                foreach (DataRow row in ResultsData.Rows)
                {
                    var newRow = new Row();
 
                    foreach (DataColumn col in ResultsData.Columns)
                    {
                        var cell = new Cell
                        {
                            DataType = CellValues.String,
                            CellValue = new CellValue(row[col].ToString())
                        };
                        newRow.AppendChild(cell);
                    }
 
                    sheetData.AppendChild(newRow);
                }
 
                workbookpart.Workbook.Save();
 
                // Close the document.
                spreadsheetDocument.Close();
            }
        }
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Hisham Saber
Software Developer (Senior)
Australia Australia
Web/Cloud Consultant, Project Manager, & Scrum Master for 13+ years experience in IT field.

Microsoft Certified Solution Provider, Scrum Master Certified, PMP.

Comments and Discussions

 
QuestionIs this possible to write all data in the same Sheet without raise the Execption? Pin
Member 1136319210-Sep-19 1:53
memberMember 1136319210-Sep-19 1:53 
Praiseawesome Pin
VanTamsir19-Jun-19 20:27
memberVanTamsir19-Jun-19 20:27 
QuestionMy Vote Of 5 Pin
aldirasta4-Nov-18 22:42
memberaldirasta4-Nov-18 22:42 
QuestionImpressive results with 700K+ rows Pin
francis.Net200311-Aug-17 21:12
memberfrancis.Net200311-Aug-17 21:12 
AnswerRe: Impressive results with 700K+ rows Pin
Member 1091956215-Nov-17 19:37
memberMember 1091956215-Nov-17 19:37 
QuestionHow to format Date column? Pin
Member 122775205-Mar-17 23:19
memberMember 122775205-Mar-17 23:19 
Questioni want to add one more sheet in the same sheet Pin
Member 1269379022-Aug-16 5:49
memberMember 1269379022-Aug-16 5:49 
Praisesweet Pin
Member 122628164-Jul-16 14:42
memberMember 122628164-Jul-16 14:42 
SuggestionWon't export binary data Pin
Nime Cloud10-Feb-16 20:28
memberNime Cloud10-Feb-16 20:28 
Questionwrite data in the same sheet? Pin
Gian C Moraes30-Sep-15 9:56
memberGian C Moraes30-Sep-15 9:56 
QuestionAdd comments and Style in cell Pin
amitgsc23-Jul-15 8:09
memberamitgsc23-Jul-15 8:09 
QuestionQuery on writing Column names in Excel Sheet Pin
meeram392-Jul-15 20:50
professionalmeeram392-Jul-15 20:50 
Questionbut it is not working in ASP.net Pin
Member 328855219-Jun-15 4:05
memberMember 328855219-Jun-15 4:05 
SuggestionMy C#/VB.Net "Export to Excel" library Pin
Michael Gledhill10-Jun-15 23:38
memberMichael Gledhill10-Jun-15 23:38 
Questioni have more then 25 mb file possible to generate excel on website Pin
Member 113402421-Mar-15 23:29
memberMember 113402421-Mar-15 23:29 
AnswerRe: i have more then 25 mb file possible to generate excel on website Pin
Hisham Saber2-Mar-15 15:44
memberHisham Saber2-Mar-15 15:44 
GeneralRe: i have more then 25 mb file possible to generate excel on website Pin
Member 1046588619-Apr-19 1:07
memberMember 1046588619-Apr-19 1:07 
QuestionExport Data into existing excel Pin
jaswants5-Feb-15 0:50
memberjaswants5-Feb-15 0:50 
AnswerRe: Export Data into existing excel Pin
Hisham Saber5-Feb-15 23:20
memberHisham Saber5-Feb-15 23:20 
GeneralRe: Export Data into existing excel Pin
jaswants6-Feb-15 1:49
memberjaswants6-Feb-15 1:49 
AnswerRe: Export Data into existing excel Pin
Hisham Saber8-Feb-15 13:20
memberHisham Saber8-Feb-15 13:20 
GeneralRe: Export Data into existing excel Pin
Hisham Saber8-Feb-15 13:31
memberHisham Saber8-Feb-15 13:31 
GeneralRe: Export Data into existing excel Pin
jaswants8-Feb-15 19:31
memberjaswants8-Feb-15 19:31 
GeneralRe: Export Data into existing excel Pin
Hisham Saber9-Feb-15 12:36
memberHisham Saber9-Feb-15 12:36 
GeneralRe: Export Data into existing excel Pin
jaswants9-Feb-15 18:17
memberjaswants9-Feb-15 18:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 26 Sep 2013

Stats

214.8K views
7.3K downloads
41 bookmarked