Excel Reader

• Download source files - 279.64 KB

Introduction

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

• Compound Document file format
• Excel file format
• Microsoft Office 97 Drawing File Format

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

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