Click here to Skip to main content
15,867,834 members
Articles / Programming Languages / C# 5.0
Tip/Trick

Fast Excel Import and Export

Rate me:
Please Sign up or sign in to vote.
4.85/5 (23 votes)
15 Oct 2014CPOL2 min read 56.6K   6   53   14
Fast, really fast, import/export from Excel OOXML

Introduction

This tip presents code to access (read & write) data in an Excel sheet without using any external module, by directly accessing the data in the ooxml format. Also a great effort has been made to make it as fast as possible, minimizing the number of memory allocations, and making it as usable as possible.

You can find the most up to date code at https://github.com/jsegarra1971/SejExcelExport.

Background

There are different techniques to read/write data from an Excel sheet, some of them use the database paradigm (DAO, odbc,..), others use the Excel-like-objects path (worksheets, cells, ...), and others use Excel itself (COM, Interop,...). Each of these techniques has a number of libraries, hiding the dirty details, and making their use fairly simple.

These techniques have their pros and cons, but most of them either, require additional software (i.e.: Excel, Microsoft.ACE.OLEDB,...), usually this is not a big problem, as most of this software is open source, free or very cheap, and easily accessible.

Nevertheless and when it comes to do the job, most of these tools are quite "liberal" in their memory consumption. This usually it is not a problem for small sheets (less than 5,000 rows), but it can bring your system to its knees for big sheets (more than 50,000 rows).

Reading a Sheet

Reading a sheet is quite simple, you just need:

C#
ExcelReader myReader=new ExcelReader("this_is_my_excel_file.xlsx");    // Open the file
myReader.Process(OnExcelCell);                                         // Process it

and to get the actual values:

C#
?void OnExcelCell(char Column, int RowNumber, string value)
{
 if (Column=='#') Console.Write("Row: "+RowNumber);
 else Console.Write("Column: "+Column+" Value: "+value);
}  

Currently, only the first sheet and columns up to 'Z' are processed. Nevertheless it is quite simple to overcome this limitations (see ExcelReader.cs).

Writing a Sheet

For writing data into a sheet, we need two things:

  1. The data, which will come as an implementation of IDataReader.
  2. A template, which will come as an Excel file with placeholders for the actual data. These placeholders are just names beginning with and asterik "*". The values from the data source are retrieved by matching this placeholder with the FIELDNAME.

Using this template:

  • ROW1 is fixed, and will be present in the output file
  • ROW2 will hold the fields "name","surname" and "age" of the first record of data
  • ROW3 will hold the fields "name","surname" and "age" of the second record of data

and then repeat until EOF.

ExcelWriter t = new ExcelWriter("Template.xlsx");   // This is the template
MySampleData data = new MySampleData();             // This is the IDataReader
t.Export(data,"output_file.xlsx");                  // Use the template to create the data file

Performance

It is hard to read/write an Excel file faster. Included there is a sample that generates an Excel file with 100,000 rows, and then reads all the 100,000 rows. Both operations done in less than 4 seconds, running on a rather outdated system (Windows 7 virtual machine on a 2009 iMac)

License

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


Written By
Spain Spain
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionHow to write Cell value as Date Pin
Ngo Quang Vu14-Apr-19 19:52
Ngo Quang Vu14-Apr-19 19:52 
QuestionWhy not use System.IO.Packaging.Package? PinPopular
Mario Z26-Sep-15 0:21
professionalMario Z26-Sep-15 0:21 
AnswerWhy not OpenXML SDK? Pin
Hingto20-Oct-15 2:36
Hingto20-Oct-15 2:36 
Suggestionif source code was present as download here! Pin
Member 1083043325-Aug-15 0:33
professionalMember 1083043325-Aug-15 0:33 
GeneralRe: if source code was present as download here! Pin
Jose Segarra21-Sep-15 21:14
Jose Segarra21-Sep-15 21:14 
QuestionIs .Net required? Pin
Richard Algeni Jr18-Oct-14 11:04
Richard Algeni Jr18-Oct-14 11:04 
AnswerRe: Is .Net required? Pin
Jose Segarra19-Oct-14 23:09
Jose Segarra19-Oct-14 23:09 
GeneralRe: Is .Net required? Pin
Richard Algeni Jr20-Oct-14 8:35
Richard Algeni Jr20-Oct-14 8:35 
QuestionTested against EPPlus Pin
William Ivanski17-Oct-14 10:26
professionalWilliam Ivanski17-Oct-14 10:26 
AnswerRe: Tested against EPPlus Pin
Jose Segarra19-Oct-14 23:10
Jose Segarra19-Oct-14 23:10 
Thanks !!
QuestionMessage Closed Pin
15-Oct-14 22:05
Ciprian Beldi15-Oct-14 22:05 
AnswerRe: Another Excel library Pin
Member 1083043325-Aug-15 0:41
professionalMember 1083043325-Aug-15 0:41 
QuestionGreat article Pin
peteSJ15-Oct-14 10:36
peteSJ15-Oct-14 10:36 
AnswerRe: Great article Pin
aarif moh shaikh16-Oct-14 3:01
professionalaarif moh shaikh16-Oct-14 3:01 

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.