|
Is there any way to determine if column has been set to Hidden?
|
|
|
|
|
Downloaded this package, looks like it could be what I needed. Created VS2008 version of project, added signing, made all warnings errors. Corrected a couple of warnings in code (search for "ABr" in solution). Started on XML documentation path but too many public entities (977 
I have posted the VS2008 package on my google site at http://sites.google.com/site/softwareabnet/updated-excelreader-by-liu-junfeng[^]
|
|
|
|
|
|
Hey guy,
First, thanks for the tremendous effort in the lib. I've been working thru making active changes in the codebase (e.g. ColumnWidths from internal to public) because I'm using your lib as a lib (rather than an integral part of my app, so internal doesn't apply). I'll be working thru more bits and pieces (like the semantics of setting column / cell attributes) and when I'm done I'll post up changes on your site. I'll also post up the reasons for the changes, etc.
Thanks for the link, and keep up the great work.
Andy
|
|
|
|
|
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.
|
|
|
|
|
|
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!
|
|
|
|
|
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. 
|
|
|
|
|
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.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
};
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
};
}
}
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;
}
}
public UInt16 FormatIndex;
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.
|
|
|
|
|
|
谢谢你~！
很好很不错~！
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.
|
|
|
|
|
|
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,>();
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;
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;
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)
{
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;
}
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.
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.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
