Click here to Skip to main content
Click here to Skip to main content

Excel Reader

, 20 Feb 2009
Rate this:
Please Sign up or sign in to vote.
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:
    Stream fileStream = File.OpenRead(file);
    Workbook book = new Workbook();
    book.Open(fileStream);
    Worksheet sheet = book.Worksheets[0];
  2. Read cell:
    int row = 1;
    int col = 0;
    string ID = sheet.Cells[row, col].StringValue;
    
    Picture pic = sheet.ExtractPicture(row, col);
  3. Create workbook:
    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:
     // 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)

Share

About the Author

Liu Junfeng
Software Developer (Senior) Beyondsoft SH
China China
No Biography provided

Comments and Discussions

 
QuestionTks for thread! Pinprofessionalvietnam-travel-vietnam-tour25-Mar-14 20:40 
NewsMessage Automatically Removed PinmemberJamie Calops16-Oct-13 22:51 
Questionformatting Pinmembermbowles20129-Jan-13 5:41 
QuestionData amount related problem PinmemberLlarian3-Sep-12 3:45 
GeneralMy vote of 5 Pinmembertomercagan26-Aug-12 1:03 
QuestionHow can i dump a worksheet to a DataTable? Pinmembernavakiran19-Aug-12 1:18 
GeneralMy vote of 5 Pinmembercdwilliams11-Jul-12 9:24 
SuggestionC# Read Excel and Show in WPF DataGrid PinmemberMember 471378514-Jun-12 4:49 
QuestionError writing to excel when using japanese characters Pinmemberprincektd13-Jun-12 20:29 
QuestionFix for Reading Excel with Macro Issue? Pinmembergsanchezbiz27-Mar-12 21:12 
QuestionImages count [modified] Pinmemberafxii15-Jan-12 2:37 
QuestionCol count? PinmemberOkan Kocyigit22-Dec-11 2:11 
QuestionCharting/graphs Pinmemberforge33_se31-Aug-11 15:11 
QuestionMerge cells PinmemberplextoR24-Jul-11 0:44 
GeneralMy vote of 3 PinmemberReelix13-Apr-11 0:55 
GeneralMy vote of 5 Pinmemberrajesh_chan28-Mar-11 22:36 
GeneralClass reference Pinmembersenguptaamlan8-Mar-11 1:53 
AnswerRe: Class reference PinmemberDrkSpyder26-Dec-11 20:50 
GeneralRe: Class reference PinmemberNick Merchant1-May-12 10:43 
GeneralMy vote of 5 Pinmembercareced1-Mar-11 7:36 
GeneralMy vote of 5 Pinmembersneff28-Feb-11 2:51 
GeneralCompoundDocument should implement IDisopsable Pinmembersneff28-Feb-11 2:30 
GeneralRe: CompoundDocument should implement IDisopsable PinmemberLiu Junfeng31-Mar-11 6:07 
GeneralRe: CompoundDocument should implement IDisopsable Pinmembersneff31-Mar-11 10:20 
GeneralMy vote of 5 Pinmemberahsan sarfraz3-Feb-11 1:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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 | Mobile
Web04 | 2.8.140821.2 | Last Updated 20 Feb 2009
Article Copyright 2006 by Liu Junfeng
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid