Click here to Skip to main content
15,860,972 members
Articles / Programming Languages / C#

Excel Reader

Rate me:
Please Sign up or sign in to vote.
4.92/5 (109 votes)
20 Feb 2009LGPL32 min read 1.1M   25.8K   357   173
Create, read and modify Excel *.xls files in pure C# without COM interop
Sample Image - ExcelReader.png

Introduction

This library is built based on the following documents, thanks to their authors:

Record structures in BIFF8/BIFF8X format are considered.

Now this project is hosted on Google Code.
You can get the latest code, report issues and submit improvements from there. 

What It Can Do?

  • It can read worksheets in a workbook and read cells in a worksheet.
  • It can read cell content (text, number, datetime, or error) and cell format (font, alignment, linestyle, background, etc.).
  • It can read pictures in the file, get information of image size, position, data, and format.
  • It can create a workbook and save to file.

Using the Code

  1. Open file:
    C#
    Stream fileStream = File.OpenRead(file);
    Workbook book = new Workbook();
    book.Open(fileStream);
    Worksheet sheet = book.Worksheets[0];
  2. Read cell:
    C#
    int row = 1;
    int col = 0;
    string ID = sheet.Cells[row, col].StringValue;
    
    Picture pic = sheet.ExtractPicture(row, col);
  3. Create workbook:
    C#
    string file = "C:\\newdoc.xls";
    Workbook workbook = new Workbook();
    Worksheet worksheet = new Worksheet("First Sheet");
    worksheet.Cells[0, 1] = new Cell(1);
    worksheet.Cells[2, 0] = new Cell(2.8);
    worksheet.Cells[3, 3] = new Cell((decimal)3.45);
    worksheet.Cells[2, 2] = new Cell("Text string");
    worksheet.Cells[2, 4] = new Cell("Second string");
    worksheet.Cells[4, 0] = new Cell(32764.5, "#,###.00");
    worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY\-MM\-DD");
    worksheet.Cells.ColumnWidth[0, 1] = 3000;
    workbook.Worksheets.Add(worksheet);
    workbook.Save(file);
  4. Traverse worksheet:
    C#
    // traverse cells
    foreach (Pair<Pair<int, int>, Cell> cell in sheet.Cells)
    {
        dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
    }
    
    // traverse rows by Index
    for (int rowIndex = sheet.Cells.FirstRowIndex;
       rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
    {
        Row row = sheet.Cells.GetRow(rowIndex);
        for (int colIndex = row.FirstColIndex;
       colIndex <= row.LastColIndex; colIndex++)
        {
            Cell cell = row.GetCell(colIndex);
        }
    }
    

History

  • 2007-5-17
    • Displayed each Sheet in separate tabpage
    • Fixed some bugs in FORMULA.cs and Form1.cs
  • 2007-5-26
    • Decoded FORMULAR result
  • 2008-1-23
    • Changed default Encoding from ASCII to UTF8 in Record.cs ln97 (suggested by ragundo)
    • Return EmptyCell instead of null for non existing cells (requested by amrlafi)
    • Bug fix in Record.cs ln83 and ln133 (proposed by dhirshjr and ilogpasig)
    • Tried to fix bug in CompoundDocument.cs (found by stevenbright)
  • 2008-9-15
    • Create, Open and Modify CompoundDocument
  • 2008-9-22
    • Save modified CompoundDocument
  • 2008-10-26
    • Encoded workbook and worksheet to BIFF8 records
    • Fixed a bug that compressed Unicode string wrongly decoded by UTF8
  • 2008-11-10
    • Fixed XF format so that the created *.xls file can be opened by Microsoft Excel 2003
  • 2008-11-16
    • Assigned number format for number and date time values
    • Set column widths
  • 2009-1-3
    • Updated project source code and article text
    • Many bug fixes
    • Added support to create and modify *.xls files
  • 2009-01-20
    • Fix for getting 'NaN' when reading string value of a formula cell
    • Start a Google Code project
  • 2009-01-23
    • Support for decode cell format and encode predefined cell format
    • Changed namespaces and directory structure
    • Added some test cases
  • 2009-02-12
    • Initial implementation of encode images

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)


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

Comments and Discussions

 
QuestionRead Excel macro Pin
subodhan19-Oct-20 23:20
subodhan19-Oct-20 23:20 
GeneralMy vote of 3 Pin
Member 1225157526-Apr-16 0:05
Member 1225157526-Apr-16 0:05 
Questionexcel文件转成pdf Convert excel to pdf Pin
ming_775519-Mar-15 16:34
ming_775519-Mar-15 16:34 
Questionformatting Pin
mbowles20129-Jan-13 5:41
mbowles20129-Jan-13 5:41 
QuestionData amount related problem Pin
Llarian3-Sep-12 3:45
Llarian3-Sep-12 3:45 
GeneralMy vote of 5 Pin
tomercagan26-Aug-12 1:03
tomercagan26-Aug-12 1:03 
QuestionHow can i dump a worksheet to a DataTable? Pin
navakiran19-Aug-12 1:18
navakiran19-Aug-12 1:18 
GeneralMy vote of 5 Pin
cdwilliams11-Jul-12 9:24
cdwilliams11-Jul-12 9:24 
QuestionError writing to excel when using japanese characters Pin
princektd13-Jun-12 20:29
princektd13-Jun-12 20:29 
QuestionFix for Reading Excel with Macro Issue? Pin
gsanchezbiz27-Mar-12 21:12
gsanchezbiz27-Mar-12 21:12 
QuestionImages count Pin
afxii15-Jan-12 2:37
afxii15-Jan-12 2:37 
QuestionCol count? Pin
Okan Kocyigit22-Dec-11 2:11
Okan Kocyigit22-Dec-11 2:11 
QuestionCharting/graphs Pin
forge33_se31-Aug-11 15:11
forge33_se31-Aug-11 15:11 
QuestionMerge cells Pin
plextoR24-Jul-11 0:44
plextoR24-Jul-11 0:44 
GeneralMy vote of 3 Pin
Reelix13-Apr-11 0:55
Reelix13-Apr-11 0:55 
GeneralMy vote of 5 Pin
rajesh_chan28-Mar-11 22:36
rajesh_chan28-Mar-11 22:36 
GeneralClass reference Pin
senguptaamlan8-Mar-11 1:53
senguptaamlan8-Mar-11 1:53 
AnswerRe: Class reference Pin
DrkSpyder26-Dec-11 20:50
DrkSpyder26-Dec-11 20:50 
GeneralRe: Class reference Pin
Nick Merchant1-May-12 10:43
Nick Merchant1-May-12 10:43 
GeneralMy vote of 5 Pin
wpx71-Mar-11 7:36
wpx71-Mar-11 7:36 
GeneralMy vote of 5 Pin
sneff28-Feb-11 2:51
sneff28-Feb-11 2:51 
GeneralCompoundDocument should implement IDisopsable Pin
sneff28-Feb-11 2:30
sneff28-Feb-11 2:30 
GeneralRe: CompoundDocument should implement IDisopsable Pin
Liu Junfeng31-Mar-11 6:07
Liu Junfeng31-Mar-11 6:07 
GeneralRe: CompoundDocument should implement IDisopsable Pin
sneff31-Mar-11 10:20
sneff31-Mar-11 10:20 
GeneralMy vote of 5 Pin
ahsan sarfraz3-Feb-11 1:07
ahsan sarfraz3-Feb-11 1:07 

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.