Excel Reader

Please work around the latest code, some issues are already fixed.

I am happy to work with people doing great projects.

Hi,

I've downloaded two versions of your project - one in Aug. 2008 and another in Feb. 2009. On both versions, I get an error in about 1/2 of my files (all created using Excel 2003).

The error occurs in the Record.cs file, in the ReadString() method. Specifically, the error occurs when the code then reads an incorrectly large value for the variable "stringlength" (representing the length of the string to read), or the variable "size," (representing the length of the Asian phonetic settings). This large value creates an exception. When the error occurs, these values are typically in the tens or hundreds of thousands.

Through testing, I've found that the error seems to occur when the end of the stream is reached, and your code switches to a continued record using SwitchToContinuedRecord(). This occurs on line 3 of the ReadString() method. I've been unable to determine the error. But maybe its just because the file is from Excel 2003. Or maybe its because about 30-35 records end up in the "default" case in the Record.Read() method.

Regardless, I want to say thanks for creating your tool. If you're able to figure out the error I described above, please let me know (sunilshenoi@yahoo.com). I can provide sample files that generate the error, if you'd like. Good luck.

Please report issues to: http://code.google.com/p/excellibrary/[^]. You can also attach the excel file with issue.

Hi! Liu, I have add a new Issue in your project, nothing serious, but I think you may separate the WinForms from your project, creating a DLL part for anyone who (me for example) want to use the code...

I think I will made this, anyway for my develop needs, if you like... I can send you what I have done....

Keep the great work! Smile | :)

Exception is raised when there are lot of rows in excel file. is there a work around for large files

Please post, more information, Size of the file, Quantity of columns and rows. Wink | ;)

MasterSectorAllocation.cs

original -->
private void InitializeMasterSectorAllocationTable()
{
this.MasterSectorAllocationTable = new List<int>(NumberOfSecIDs);
SelectSIDs(Document.Header.MasterSectorAllocationTable);
int msid = Document.Header.FirstSectorIDofMasterSectorAllocationTable;
while (msid != SID.EOC)
{
CurrentMSATSector = msid;
int[] SIDs = Document.ReadSectorDataAsIntegers(msid);
SelectSIDs(SIDs);
msid = SIDs[SIDs.Length - 1];
}
}

Fix -->
private void InitializeMasterSectorAllocationTable()
{
this.MasterSectorAllocationTable = new List<int>(NumberOfSecIDs);
SelectSIDs(Document.Header.MasterSectorAllocationTable);
int msid = Document.Header.FirstSectorIDofMasterSectorAllocationTable;

int difCount;
if ((difCount = Document.Header.NumberOfMasterSectors) == 0)
return;

while (difCount > 0)
{
CurrentMSATSector = msid;

int[] SIDs = Document.ReadSectorDataAsIntegers(msid);
for (int i = 0; i < SIDs.Length - 1; i++)
{
int sid = SIDs[i];
if (sid == SID.Free)
return;
MasterSectorAllocationTable.Add(SIDs[i]);
}
msid = SIDs[SIDs.Length - 1];

if ((difCount--) == 0)
MasterSectorAllocationTable.Add(msid);
}
}

CompoundDocument_Write.cs

org -->
internal void WriteStreamData(int startSID, byte[] data)
{
...
if (sid != SID.EOC && prev_sid != SID.EOC)
{
ShortSectorAllocation.LinkSectorID(prev_sid, SID.EOC);
fix -->
internal void WriteStreamData(int startSID, byte[] data)
{
...
if (sid != SID.EOC)
{
SectorAllocation.LinkSectorID(sid, SID.EOC);

modified on Sunday, March 29, 2009 7:43 AM

Hi.
Thank you for the library. Keep doing what you are doing,it's the most complete source in .net.
For the complete api in java you can find the code here.

http://www.docjar.com/projects/jexcelapi_2_6_8-code.html[^]

I added some code to solve the date problem. I'll try to list it bellow:

In WorkbookDecoder.cs i stored the number formats found in excel:

private static List<BOUNDSHEET> DecodeRecords(List<Record> records, out SharedResource sharedResource)
        {
            sharedResource = new SharedResource();
            List<BOUNDSHEET> boundSheets = new List<BOUNDSHEET>();
            foreach (Record record in records)
            {
                record.Decode();
                switch (record.Type)
                {
                    case RecordType.BOUNDSHEET:
                        boundSheets.Add(record as BOUNDSHEET);
                        break;
                    case RecordType.FORMAT:
                       FORMAT frm = record as FORMAT;
                       if (!sharedResource.CellFormats.ContainsKey(frm.FormatIndex)) {
                            sharedResource.CellFormats.Add(frm.FormatIndex, frm);
                        }
                        break;
                    case RecordType.XF:
                        sharedResource.ExtendedFormats.Add(record as XF);
                        break;
                    case RecordType.SST:
                        sharedResource.SharedStringTable = record as SST;
                        break;
                    case RecordType.DATEMODE:
                        DATEMODE dateMode = record as DATEMODE;
                        switch (dateMode.Mode)
                        {
                            case 0:
                                sharedResource.BaseDate = DateTime.Parse("1899-12-31");
                                break;
                            case 1:
                                sharedResource.BaseDate = DateTime.Parse("1904-01-01");
                                break;
                        }
                        break;
                    case RecordType.PALETTE:
                        PALETTE palette = record as PALETTE;
                        int colorIndex = 8;
                        foreach (int color in palette.RGBColours)
                        {
                            sharedResource.ColorPalette[colorIndex] = Color.FromArgb(color);
                            colorIndex++;
                        }
                        break;
                }
            }

In WorksheetDecoder.cs i checked if the records have date or number formats and populated the cells accordingly

private static CellCollection PopulateCells(List<Record> records, SharedResource sharedResource)
        {
            CellCollection cells = new CellCollection();
            cells.SharedResource = sharedResource;
            foreach (Record record in records)
            {
                record.Decode();
                switch (record.Type)
                {
                    //case RecordType.DIMENSIONS:
                    //    DIMENSIONS dimensions = record as DIMENSIONS;
                    //    cells.FirstRowIndex = dimensions.FirstRow;
                    //    cells.FirstColIndex = dimensions.FirstColumn;
                    //    cells.LastRowIndex = dimensions.LastRow-1;
                    //    cells.LastColIndex = dimensions.LastColumn-1;
                    //    break;
                    case RecordType.BOOLERR:
                        BOOLERR boolerr = record as BOOLERR;
                        cells.CreateCell(boolerr.RowIndex, boolerr.ColIndex, boolerr.GetValue(), boolerr.XFIndex);
                        break;
                    case RecordType.LABELSST:
                        LABELSST label = record as LABELSST;
                        cells.CreateCell(label.RowIndex, label.ColIndex, sharedResource.GetStringFromSST(label.SSTIndex), label.XFIndex);
                        break;
                    case RecordType.NUMBER:
                        NUMBER number = record as NUMBER;
                        cells.CreateCell(number.RowIndex, number.ColIndex, number.Value, number.XFIndex);
                        break;
                    case RecordType.RK:
                        RK rk = record as RK;
                        if (sharedResource.IsDate(rk.XFIndex)) {
                            cells.CreateCell(rk.RowIndex, rk.ColIndex, sharedResource.DecodeDateTime((double)Record.DecodeRK(rk.Value)), rk.XFIndex);
                        }
                        else {
                            cells.CreateCell(rk.RowIndex, rk.ColIndex, Record.DecodeRK(rk.Value), rk.XFIndex);
                        }
                        break;
                    case RecordType.MULRK:
                        MULRK mulrk = record as MULRK;
                        int row = mulrk.RowIndex;
                        for (int col = mulrk.FirstColIndex; col <= mulrk.LastColIndex; col++)
                        {
                            int index = col - mulrk.FirstColIndex;
                            object value = Record.DecodeRK(mulrk.RKList[index]);
                            int XFindex = mulrk.XFList[index];
                            if (sharedResource.IsDate(XFindex)) {
                                cells.CreateCell(row, col, sharedResource.DecodeDateTime((double)value), XFindex);
                            }
                            else {
                                cells.CreateCell(row, col, value, XFindex);
                            }
                        }
                        break;
                    case RecordType.FORMULA:
                        FORMULA formula = record as FORMULA;
                        cells.CreateCell(formula.RowIndex, formula.ColIndex, formula.DecodeResult(), formula.XFIndex);
                        break;
                }
            }
            return cells;
        }

Added the class FormatType.cs :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Office.Excel {
    public class FormatType {
        public static int[] DateFormats = new int[20]   {
         0xe,
         0xf,
         0x10,
         0x11,
         0x12,
         0x13,
         0x14,
         0x15,
         0x16,
         0x2d,
         0x2e,
         0x2f,
         0xbb,
         0xb5,
         0xb6,
         0xba,
         0xbb,
         0xbc,
         0xbd,
         0xbe
        };

          //DateFormat.SHORT,
          //DateFormat.MEDIUM,
          //"d-MMM",
          //"MMM-yy",
          //"h:mm a",
          //"h:mm:ss a",
          //"H:mm",
          //"H:mm:ss",
          //"M/d/yy H:mm",
          //"mm:ss",
          //"H:mm:ss",
          //"mm:ss.S";
          //"dd-MMM-yy"
          // '[$-409]h:mm:ss\\ AM/PM' 
          // '[$-409]dddd\\,\\ mmmm\\ dd\\,\\ yyyy' 
          //  dd/mm/yyyy
          // '[$-409]dd\\-mmm\\-yy;@' 
          // 'mmm\\-yyyy' ,"
          // 'mmm\\-yyyy' ,
          // '[$-409]dd\\-mmm\\-yy;@'



        public static int[] NumberFormats = new int[21] {
         0x1,
         0x2,
         0x3,
         0x4,
         0x5,
         0x6,
         0x7,
         0x8,
         0x9,
         0xa,
         0xb,
         0x25,
         0x26,
         0x27,
         0x28,
         0x29,
         0x2a,
         0x2b,
         0x2c,
         0x30,
         0xb3
        };

          //"0//"
          //"0.00//"
          //"#,##0//"
          //"#,##0.00//"
          //"$#,##0;($#,##0)//"
          //"$#,##0;($#,##0)//"
          //"$#,##0.00;($#,##0.00)//"
          //"$#,##0.00;($#,##0.00)//"
          //"0%//"
          //"0.00%//"
          //"0.00E00//"
          //"#,##0;(#,##0)//"
          //"#,##0;(#,##0)//"
          //"#,##0.00;(#,##0.00)//"
          //"#,##0.00;(#,##0.00)//"
          //"#,##0;(#,##0)//"
          //"$#,##0;($#,##0)//"
          //"#,##0.00;(#,##0.00)//"
          //"$#,##0.00;($#,##0.00)//"
          //"##0.0E0//")};
          // '0.0000%'

    }
}

Added IsDate and IsDouble properies on XF.cs and FORMAT.cs:

public partial class XF : Record
	{
		public XF(Record record) : base(record) {    
        }

		public XF()
		{
			this.Type = RecordType.XF;
		}

        private bool isDate = false;
        public bool IsDate {
            get {
                return isDate;
            }
        }


        private bool isDouble = false;
        public bool IsDouble {
            get {
                return isDouble;
            }
        }

		public UInt16 FontIndex;

		public UInt16 FormatIndex;

		public UInt16 CellProtection;

		public Byte Alignment;

		public Byte Rotation;

		public Byte Indent;

		public Byte Attributes;

		public UInt32 LineStyle;

		public UInt32 LineColor;

		public UInt16 Background;

        public override void Decode() {
            MemoryStream stream = new MemoryStream(Data);
            BinaryReader reader = new BinaryReader(stream);
            this.FontIndex = reader.ReadUInt16();
            this.FormatIndex = reader.ReadUInt16();
            this.CellProtection = reader.ReadUInt16();
            this.Alignment = reader.ReadByte();
            this.Rotation = reader.ReadByte();
            this.Indent = reader.ReadByte();
            this.Attributes = reader.ReadByte();
            this.LineStyle = reader.ReadUInt32();
            this.LineColor = reader.ReadUInt32();
            this.Background = reader.ReadUInt16();

            for (int i = 0; i < FormatType.DateFormats.Length && isDate == false; i++) {
                if (FormatIndex == FormatType.DateFormats[i]) {
                    isDate = true;
                }
            }

            for (int i = 0; i < FormatType.NumberFormats.Length && isDouble == false; i++) {
                if (FormatIndex == FormatType.NumberFormats[i]) {
                    isDouble = true;
                }
            }
        }

		public override void Encode()
		{
			MemoryStream stream = new MemoryStream();
			BinaryWriter writer = new BinaryWriter(stream);
			writer.Write(FontIndex);
			writer.Write(FormatIndex);
			writer.Write(CellProtection);
			writer.Write(Alignment);
			writer.Write(Rotation);
			writer.Write(Indent);
			writer.Write(Attributes);
			writer.Write(LineStyle);
			writer.Write(LineColor);
			writer.Write(Background);
			this.Data = stream.ToArray();
			this.Size = (UInt16)Data.Length;
			base.Encode();
		}

	}

public partial class FORMAT : Record
	{
		public FORMAT(Record record) : base(record) { }

		public FORMAT()
		{
			this.Type = RecordType.FORMAT;
		}

        private bool isDate = false;
        public bool IsDate {
            get {
                return isDate;
            }
        }


        private bool isDouble = false;
        public bool IsDouble {
            get {
                return isDouble;
            }
        }

		/// <summary>
		/// Format index used in other records
		/// </summary>
		public UInt16 FormatIndex;

		/// <summary>
		/// Number format string
		/// </summary>
		public String FormatString;

		public override void Decode()
		{
			MemoryStream stream = new MemoryStream(Data);
			BinaryReader reader = new BinaryReader(stream);
			this.FormatIndex = reader.ReadUInt16();
			this.FormatString = this.ReadString(reader, 16);

            for (int i = 0; i < FormatType.DateFormats.Length && IsDate == false; i++) {
                if (FormatIndex == FormatType.DateFormats[i]) {
                    isDate = true;
                }
            }

            for (int i = 0; i < FormatType.NumberFormats.Length && isDouble == false; i++) {
                if (FormatIndex == FormatType.NumberFormats[i]) {
                    isDouble = true;
                }
            }
		}

		public override void Encode()
		{
			MemoryStream stream = new MemoryStream();
			BinaryWriter writer = new BinaryWriter(stream);
			writer.Write(FormatIndex);
			Record.WriteString(writer, FormatString,16);
			this.Data = stream.ToArray();
			this.Size = (UInt16)Data.Length;
			base.Encode();
		}

	}

Also i had to add some helpers on SharedResource.cs

public Hashtable CellFormats = new Hashtable();
public bool IsDate(int pos) {
           XF xfr = (XF)ExtendedFormats[pos];
           if (xfr.IsDate) {
               return true;
           }

           if (CellFormats.ContainsKey(xfr.FormatIndex)) {
               return ((FORMAT)CellFormats[xfr.FormatIndex]).IsDate;
           }

           return false;        }

public DateTime DecodeDateTime(double value) {
           double days = value;
           if (days > 366) days--;
           return BaseDate.AddDays(days);
       }

Now the cell has the value saved with the excel type string, double or date time so i had to change the getters for some properties. So this is the changed code :

public object Value
       {
           get { return _value; }
           set
           {
               if (IsEmpty) throw new Exception("Can not set value to an empty cell.");
               _value = value;
           }
       }

       public string StringValue
       {
           get
           {
               if (_value == null)
               {
                   return String.Empty;
               }
               if(_value is String)
               {
                   return _value.ToString();
               }
               return string.Empty;
           }
       }

       public DateTime DateTimeValue
       {
           get {
               if (_value == null) {
                   return DateTime.MinValue;
               }
               if (_value is DateTime) {
                   return (DateTime)_value;
               }
               else {
                   return DateTime.MinValue;
               }
           }
       }

And now here is how you can read the excel in a DataTable in the most common scenario :

public class NetExcelDataAdapter {
      private DateTime mindate = new DateTime(1900, 1, 1);
      private Workbook workbook = null;

      public NetExcelDataAdapter(string filename) {
          CompoundDocument doc = null;
          MemoryStream streamdata = null;
          try {
              doc = CompoundDocument.Open(filename);
              byte[] bookdata = doc.GetStreamData("Workbook");
              if (bookdata != null) {
                  streamdata = new MemoryStream(bookdata);
                  workbook = WorkbookDecoder.Decode(streamdata);
              }
          }
          catch (Exception ex) {
              throw new Exception(string.Format("Eroare la incarcarea   fisierului {0} \n\r cu exceptia : {1}"
                 , filename
                 , ex.Message));
          }
          finally {
              doc.Close();

              if (streamdata != null) {
                  streamdata.Close();
                  streamdata.Dispose();
              }
          }
      }

      public DataTable GetExcelContent(int index) {
          if (workbook != null && workbook.Worksheets.Count > index + 1) {
              Worksheet sheet = workbook.Worksheets[index];
              return GetExcelContent(sheet);
          }
          return new DataTable();
      }

      public DataTable GetExcelContent(string sheetName) {
          if (workbook != null) {
              foreach (Worksheet sheet in workbook.Worksheets) {
                  if (sheet.Name == sheetName) {
                      return GetExcelContent(sheet);
                  }
              }
          }
          return new DataTable();
      }

      private DataTable GetExcelContent(Worksheet sheet) {
          DataTable result = new DataTable();

          if (sheet.Cells.LastColIndex > 0 && sheet.Cells.LastRowIndex > 1) {
              result = new DataTable(sheet.Name);
              for (int columnno = 0; columnno <= sheet.Cells.LastColIndex; columnno++) {
                  try {
                      DataColumn column = new DataColumn(sheet.Cells[0, columnno].Value.ToString());

                      if (sheet.Cells[1, columnno].Value is double) {
                          column.DataType = typeof(Double);
                      }
                      else if (sheet.Cells[1, columnno].Value is DateTime) {
                          column.DataType = typeof(DateTime);
                      }
                      else {
                          column.DataType = typeof(String);
                      }

                      result.Columns.Add(column);
                  }
                  catch (Exception ex) {
                      throw new Exception();
                  }
              }

              foreach (Pair<Pair<int, int>, Cell> cell in sheet.Cells) {
                  try {
                      if (cell.Left.Left > 0) {
                          if (result.Rows.Count < cell.Left.Left) {
                              result.Rows.Add(result.NewRow());
                          }
                          if (result.Columns[cell.Left.Right].DataType == typeof(DateTime)) {
                              result.Rows[cell.Left.Left - 1][cell.Left.Right] = cell.Right.DateTimeValue;
                          }
                          else {
                              result.Rows[cell.Left.Left - 1][cell.Left.Right] = cell.Right.Value;
                          }
                      }
                  }
                  catch (Exception ex) {
                      throw new Exception();
                  }
              }
          }

          result.AcceptChanges();
          return result;
      }
  }

The only problem that i had with this till now is that it crashes for more than 20000 records with more than 15 columns. I wish you good luck.

This feature is already supported in changes r5 of http://code.google.com/p/excellibrary/source/list[^]

I am happy to work with people doing great projects.

谢谢你~！
很好很不错~！

A Dream About Software。

Are there any plan to move this code into an open source project and host in places like google code?

This will help community a lot by having a repository with full revision history and an issue tracker.

I am interested in helping out. My email is jetcat(at)gmail.com.

That's a good idea. Thanks for your contribution!
I created the project in http://code.google.com/p/excellibrary/[^]

I am happy to work with people doing great projects.

hi Liu Junfeng,

using QiHe.CodeLib;
using QiHe.Office.CompoundDocumentFormat;
using QiHe.Office.Excel;

i use the above dll for upload the excel file.but the file sixe is large(200kb) i get the following error.

The output char buffer is too small to contain the decoded characters, encoding 'Unicode (UTF-8)' fallback

how can i solve it.pls help......

Exception : Exception of type 'System.Web.HttpUnhandledException' was thrown.

Exception Description : System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.ArgumentException:The output char buffer is too small to contain the decoded characters, encoding 'Unicode (UTF-8)' fallback
'System.Text.DecoderReplacementFallback'. Parameter name: chars at System.Text.Encoding.ThrowCharsOverflow() at System.Text.Encoding.ThrowCharsOverflow(DecoderNLS decoder, Boolean nothingDecoded) at System.Text.UTF8Encoding.GetChars(Byte* bytes, Int32 byteCount, Char* chars, Int32 charCount, DecoderNLS baseDecoder) at System.Text.DecoderNLS.GetChars(Byte* bytes, Int32 byteCount, Char* chars, Int32 charCount, Boolean flush) at System.Text.DecoderNLS.GetChars(Byte[] bytes, Int32 byteIndex, Int32 byteCount, Char[] chars, Int32 charIndex, Boolean flush) at System.Text.DecoderNLS.GetChars(Byte[] bytes, Int32 byteIndex, Int32 byteCount, Char[] chars, Int32 charIndex) at System.IO.BinaryReader.InternalReadOneChar() at System.IO.BinaryReader.PeekChar() at QiHe.Office.Excel.Record.ReadString(BinaryReader reader, Int32 lengthbits, BinaryReader& continuedReader)

Hai

how this problem is solved because i am also having same problem.

I am currently testing write functionality using large number of data. Here are my findings so far:

1/ Writing excel file has a performance bottleneck at this method: SharedResource.GetSSTIndex(). If I add a dictionary to replace the linear search, then write speed is near to read speed.

2/ When writing large number of unique string values, EXTSST records doesn't seem to be formatted correctly (may be due to incorrect CONTINUE record implementation in Record.Encode()). I can hack this be limiting string offset to 128 in EXTSST.Encode(). (Excel also does this - according to comments in POI source code.)

3/ When writing large number of cells (something like 30 worksheet with 10000 rows and 50 columns each), I get the following exception:

System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values.
Parameter name: SATSectorIndex

at QiHe.Office.CompoundDocumentFormat.MasterSectorAllocation.GetSATSectorID(Int32 SATSectorIndex) in MasterSectorAllocation.cs: line 74
at QiHe.Office.CompoundDocumentFormat.SectorAllocation.LinkSectorID(Int32 sectorID, Int32 newSectorID) in SectorAllocation.cs: line 33
at QiHe.Office.CompoundDocumentFormat.MasterSectorAllocation.AllocateMSATSector() in MasterSectorAllocation.cs: line 126
at QiHe.Office.CompoundDocumentFormat.MasterSectorAllocation.AllocateSATSector() in MasterSectorAllocation.cs: line 95
at QiHe.Office.CompoundDocumentFormat.MasterSectorAllocation.GetSATSectorID(Int32 SATSectorIndex) in MasterSectorAllocation.cs: line 70
at QiHe.Office.CompoundDocumentFormat.SectorAllocation.LinkSectorID(Int32 sectorID, Int32 newSectorID) in SectorAllocation.cs: line 33
at QiHe.Office.CompoundDocumentFormat.MasterSectorAllocation.AllocateMSATSector() in MasterSectorAllocation.cs: line 126
at QiHe.Office.CompoundDocumentFormat.MasterSectorAllocation.AllocateSATSector() in MasterSectorAllocation.cs: line 95
at QiHe.Office.CompoundDocumentFormat.MasterSectorAllocation.GetSATSectorID(Int32 SATSectorIndex) in MasterSectorAllocation.cs: line 70
at QiHe.Office.CompoundDocumentFormat.SectorAllocation.LinkSectorID(Int32 sectorID, Int32 newSectorID) in SectorAllocation.cs: line 33
...
at QiHe.Office.CompoundDocumentFormat.MasterSectorAllocation.AllocateMSATSector() in MasterSectorAllocation.cs: line 126
at QiHe.Office.CompoundDocumentFormat.MasterSectorAllocation.AllocateSATSector() in MasterSectorAllocation.cs: line 95
at QiHe.Office.CompoundDocumentFormat.MasterSectorAllocation.GetSATSectorID(Int32 SATSectorIndex) in MasterSectorAllocation.cs: line 70
at QiHe.Office.CompoundDocumentFormat.SectorAllocation.LinkSectorID(Int32 sectorID, Int32 newSectorID) in SectorAllocation.cs: line 33
at QiHe.Office.CompoundDocumentFormat.CompoundDocument.WriteStreamData(Int32 startSID, Byte[] data) in CompoundDocument_Write.cs: line 140
at QiHe.Office.CompoundDocumentFormat.CompoundDocument.WriteStreamData(String[] streamPath, Byte[] data) in CompoundDocument_Write.cs: line 121
at QiHe.Office.Excel.Workbook.Save(String file) in Workbook.cs: line 35
at ExcelToolkit._Tests.ReadWriteTest.WriteMultipleCellTest() in ReadWriteTest.cs: line 83

Have you found the solution for this issue? Thanks

I have also added support for cell format.

Here's the modification on current code base:

Index: Office/Excel/SharedResource.cs
===================================================================
--- Office/Excel/SharedResource.cs
+++ Office/Excel/SharedResource.cs
@@ -16,6 +16,8 @@
 
         public List<xf> ExtendedFormats = new List<xf>();
 
+        public CellFormatCollection CellFormats = new CellFormatCollection();
+
         public SharedResource()
         {
         }
Index: Office/Excel/Decode/WorkbookDecoder.cs
===================================================================
--- Office/Excel/Decode/WorkbookDecoder.cs
+++ Office/Excel/Decode/WorkbookDecoder.cs
@@ -87,6 +87,9 @@
                     case RecordType.BOUNDSHEET:
                         boundSheets.Add(record as BOUNDSHEET);
                         break;
+                    case RecordType.FORMAT:
+                        sharedResource.CellFormats.Add(record as FORMAT);
+                        break;
                     case RecordType.XF:
                         sharedResource.ExtendedFormats.Add(record as XF);
                         break;
Index: Office/Excel/Cell.cs
===================================================================
--- Office/Excel/Cell.cs
+++ Office/Excel/Cell.cs
@@ -110,5 +110,21 @@
                 return SharedResource.ColorPalette[BackColorIndex];
             }
         }
+
+        public ushort FormatIndex
+        {
+            get
+            {
+                return SharedResource.ExtendedFormats[XFIndex].FormatIndex;
+            }
+        }
+
+        public CellFormat Format
+        {
+            get
+            {
+                return SharedResource.CellFormats[FormatIndex];
+            }
+        }
     }
 }
</xf></xf>

New classes:

using System.Collections.Generic;

namespace QiHe.Office.Excel
{
    public class CellFormatCollection
    {
        private Dictionary<ushort,> lookupTable;

        public CellFormatCollection()
        {
            lookupTable = new Dictionary<ushort,>();

            // Excel built-in cell format
            // source: 
            //  http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java?view=markup
            //  http://sc.openoffice.org/excelfileformat.pdf
            lookupTable.Add(0, new CellFormat(CellFormatType.General, "General"));
            lookupTable.Add(1, new CellFormat(CellFormatType.Number, "0"));
            lookupTable.Add(2, new CellFormat(CellFormatType.Number, "0.00"));
            lookupTable.Add(3, new CellFormat(CellFormatType.Number, "#,##0"));
            lookupTable.Add(4, new CellFormat(CellFormatType.Number, "#,##0.00"));
            lookupTable.Add(5, new CellFormat(CellFormatType.Currency, "($#,##0_);($#,##0)"));
            lookupTable.Add(6, new CellFormat(CellFormatType.Currency, "($#,##0_);[Red]($#,##0)"));
            lookupTable.Add(7, new CellFormat(CellFormatType.Currency, "($#,##0.00);($#,##0.00)"));
            lookupTable.Add(8, new CellFormat(CellFormatType.Currency, "($#,##0.00_);[Red]($#,##0.00)"));
            lookupTable.Add(9, new CellFormat(CellFormatType.Percentage, "0%"));
            lookupTable.Add(10, new CellFormat(CellFormatType.Percentage, "0.00%"));
            lookupTable.Add(11, new CellFormat(CellFormatType.Scientific, "0.00E+00"));
            lookupTable.Add(12, new CellFormat(CellFormatType.Fraction, "# ?/?"));
            lookupTable.Add(13, new CellFormat(CellFormatType.Fraction, "# ??/??"));
            lookupTable.Add(14, new CellFormat(CellFormatType.Date, "m/d/yy"));
            lookupTable.Add(15, new CellFormat(CellFormatType.Date, "d-mmm-yy"));
            lookupTable.Add(16, new CellFormat(CellFormatType.Date, "d-mmm"));
            lookupTable.Add(17, new CellFormat(CellFormatType.Date, "mmm-yy"));
            lookupTable.Add(18, new CellFormat(CellFormatType.Time, "h:mm AM/PM"));
            lookupTable.Add(19, new CellFormat(CellFormatType.Time, "h:mm:ss AM/PM"));
            lookupTable.Add(20, new CellFormat(CellFormatType.Time, "h:mm"));
            lookupTable.Add(21, new CellFormat(CellFormatType.Time, "h:mm:ss"));
            lookupTable.Add(22, new CellFormat(CellFormatType.DateTime, "m/d/yy h:mm"));
            lookupTable.Add(37, new CellFormat(CellFormatType.Accounting, "(#,##0_);(#,##0)"));
            lookupTable.Add(38, new CellFormat(CellFormatType.Accounting, "(#,##0_);[Red](#,##0)"));
            lookupTable.Add(39, new CellFormat(CellFormatType.Accounting, "(#,##0.00_);(#,##0.00)"));
            lookupTable.Add(40, new CellFormat(CellFormatType.Accounting, "(#,##0.00_);[Red](#,##0.00)"));
            lookupTable.Add(41, new CellFormat(CellFormatType.Currency, "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)"));
            lookupTable.Add(42, new CellFormat(CellFormatType.Currency, "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)"));
            lookupTable.Add(43, new CellFormat(CellFormatType.Currency, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"));
            lookupTable.Add(44, new CellFormat(CellFormatType.Currency, "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"));
            lookupTable.Add(45, new CellFormat(CellFormatType.Time, "mm:ss"));
            lookupTable.Add(46, new CellFormat(CellFormatType.Time, "[h]:mm:ss"));
            lookupTable.Add(47, new CellFormat(CellFormatType.Time, "mm:ss.0"));
            lookupTable.Add(48, new CellFormat(CellFormatType.Scientific, "##0.0E+0"));
            lookupTable.Add(49, new CellFormat(CellFormatType.Text, "@"));
        }

        public void Add(FORMAT record)
        {
            if (record == null)
                return;

            // Built-in cell formula may change due to regional settings
            // therefore, we allow caller to replace built-in cell format
            if (this.lookupTable.ContainsKey(record.FormatIndex))
            {
                CellFormat oldCellFormat = this.lookupTable[record.FormatIndex];
                this.lookupTable[record.FormatIndex] = new CellFormat(oldCellFormat.FormatType, record.FormatString);
            }
            else
            {
                this.lookupTable.Add(record.FormatIndex, new CellFormat(CellFormatType.Custom, record.FormatString));
            }
        }

        public CellFormat this[ushort formatIndex]
        {
            get
            {
                if (this.lookupTable.ContainsKey(formatIndex))
                    return this.lookupTable[formatIndex];
                else
                    throw new KeyNotFoundException("Unable to find specific cell format");
            }
        }
    }
}

namespace QiHe.Office.Excel
{
    public enum CellFormatType
    {
        General,
        Number,
        Currency,
        Accounting,
        Date,
        Time,
        DateTime,
        Percentage,
        Fraction,
        Scientific,
        Text,
        Special,
        Custom
    }

    public class CellFormat
    {
        private CellFormatType formatType;
        private string formatString;

        public CellFormat(CellFormatType type, string fmt)
        {
            formatType = type;
            formatString = fmt;
        }

        public CellFormatType FormatType
        {
            get { return formatType; }
        }

        public string FormatString
        {
            get { return formatString; }
        }
    }

}

Thanks for sharing this great package.

The reason for NaN issue is due to there is shared formula record after formula record.

WorksheetDecoder.cs

        private static List<record> ReadRecords(Stream stream, out MSODRAWING drawingRecord)
        {
            List<record> records = new List<record>();
            drawingRecord = null;
            Record record = Record.Read(stream);
            Record last_record = record;
            Record last_formula_record = null; // jetcat_au: last formula record
            last_record.Decode();
            if (record is BOF && ((BOF)record).StreamType == StreamType.Worksheet)
            {
                while (record.Type != RecordType.EOF)
                {
                    if (record.Type == RecordType.CONTINUE)
                    {
                        last_record.ContinuedRecords.Add(record);
                    }
                    else
                    {
                        switch (record.Type)
                        {
                            case RecordType.STRING:
                                if (last_formula_record is FORMULA) // jetcat_au: use last_formula_record instead
                                {
                                    record.Decode();
                                    (last_formula_record as FORMULA).StringRecord = record as STRING;
                                }
                                break;
                            case RecordType.MSODRAWING:
                                if (drawingRecord == null)
                                {
                                    drawingRecord = record as MSODRAWING;
                                    records.Add(record);
                                }
                                else
                                {
                                    drawingRecord.ContinuedRecords.Add(record);
                                }
                                break;
                            default:
                                records.Add(record);
                                break;
                        }
                        // jetcat_au: see 4.8 Array Formulas and Shared Formulas
                        if (record.Type == RecordType.FORMULA)
                            last_formula_record = record;
                        else if (record.Type != RecordType.SHRFMLA && record.Type != RecordType.ARRAY)
                            last_formula_record = null;

                        last_record = record;
                    }
                    record = Record.Read(stream);
                }
                records.Add(record);
            }
            return records;
        }
</record></record></record>

Thanks for sharing.
Smile | :)

dnpro
"Very bad programmer"

My excel file has dates like: 1/25/2009

But when I look at my value in code it is (cell.StringValue) 39838.

I noticed that the DateTimeValue property is filled out, but then how will I know that this cell is a date? Since all cells have that property.

Thanks!

Tim M

This can be judged from cell's numberformat but the current code doesn's support this. WorkSheetEncoder already supports numberformat. I will add numberformat to decoder in the next version.

I am happy to work with people doing great projects.

I am using:
using Microsoft.Office.Tools.Excel;
but I get the following errors
The event 'Microsoft.Office.Tools.Excel.Workbook.Open' can only appear on the left hand side of += or -=
Cannot apply indexing with [] to an expression of type 'Microsoft.Office.Interop.Excel.Sheets'
Cannot apply indexing with [] to an expression of type 'Microsoft.Office.Interop.Excel.Range'

I tried to download your source code ZIP file, but can't for some reason.

Thanks for your help!

you should verify whether the input file is a xls file or not, you may use the api provided by structured storage.

Regards,
unruledboy_at_gmail_dot_com
http://www.xnlab.com

When I use this program for reading an Excel file with no any protected Workbook, it works fine. Problem raised when I've tried to read an Excel file with protected Workbook(to prevent users to rename/modify Sheet Names), then method ReadBytes(BinaryReader reader, int count) call itself (recursively) until "Stack Overflow" error raised.

Any idea or suggestion?

Shah

modified on Friday, December 5, 2008 11:14 AM

I am not able to read decimal data from the cell by using this library.pls help

Excel Reader

Introduction

What It Can Do?

Using the Code

History

License

Comments and Discussions