Click here to Skip to main content
12,251,889 members (61,561 online)
Click here to Skip to main content
Add your own
alternative version

Stats

84.3K views
4K downloads
34 bookmarked
Posted

Export very large data to Excel file

, 27 Sep 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

You may also be interested in...

Comments and Discussions

 
SuggestionWon't export binary data Pin
Nimea10-Feb-16 20:28
memberNimea10-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
membermeeram392-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 
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 
GeneralRe: Export Data into existing excel Pin
Hisham Saber10-Feb-15 17:34
memberHisham Saber10-Feb-15 17:34 
BugRe: Export Data into existing excel Pin
jaswants11-Feb-15 0:37
memberjaswants11-Feb-15 0:37 
GeneralRe: Export Data into existing excel Pin
Hisham Saber22-Feb-15 11:28
memberHisham Saber22-Feb-15 11:28 
QuestionPlease help me Pin
Member 1068544229-Dec-14 23:04
memberMember 1068544229-Dec-14 23:04 
AnswerRe: Please help me Pin
Garth J Lancaster29-Dec-14 23:23
memberGarth J Lancaster29-Dec-14 23:23 
AnswerRe: Please help me Pin
Hisham Saber3-Jan-15 17:34
memberHisham Saber3-Jan-15 17:34 
Questionexport data to excell Pin
hikon20-Oct-14 8:20
memberhikon20-Oct-14 8:20 
AnswerRe: export data to excell Pin
Hisham Saber17-Dec-14 12:12
memberHisham Saber17-Dec-14 12:12 
QuestionExcellent work Pin
Buddhadeva Das25-May-14 3:29
memberBuddhadeva Das25-May-14 3:29 
AnswerRe: Excellent work Pin
Hisham Saber25-May-14 20:34
memberHisham Saber25-May-14 20:34 
GeneralRe: Excellent work Pin
Buddhadeva Das27-May-14 6:29
memberBuddhadeva Das27-May-14 6:29 
GeneralRe: Excellent work Pin
Hisham Saber3-Jun-14 18:14
memberHisham Saber3-Jun-14 18:14 
QuestionExport very large data to excel Pin
Member 1069606926-Mar-14 8:15
memberMember 1069606926-Mar-14 8:15 
AnswerRe: Export very large data to excel Pin
Hisham Saber26-Mar-14 16:12
memberHisham Saber26-Mar-14 16:12 
GeneralMy vote of 2 Pin
Empiric27-Sep-13 9:55
memberEmpiric27-Sep-13 9:55 
GeneralRe: My vote of 2 Pin
Hisham Saber27-Sep-13 10:11
memberHisham Saber27-Sep-13 10:11 
GeneralRe: My vote of 2 Pin
Empiric27-Sep-13 10:15
memberEmpiric27-Sep-13 10:15 
GeneralRe: My vote of 2 Pin
Hisham Saber27-Sep-13 11:20
memberHisham Saber27-Sep-13 11:20 
GeneralMy vote of 4 Pin
stefanveliki27-Sep-13 3:51
memberstefanveliki27-Sep-13 3:51 
GeneralRe: My vote of 4 Pin
Hisham Saber27-Sep-13 10:04
memberHisham Saber27-Sep-13 10:04 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 27 Sep 2013
Article Copyright 2013 by Hisham Saber
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid