|
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.
|
|
|
|
|
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
|
|
|
|
|
Hi,
This Excel reader is not able to read Excel 2007 files(.xlsx).I will be happy if anyone can tell me how to read the .xlsx file using this feature(QiHe). Is there any update available for this?
Thanks!!
|
|
|
|
|
Hi to all,
I am using this code to read the office 2007 excel(.xlsx) file but it throws exception unknown file format.There is any update available of this project for office 2007.
Thanks,
Amar
|
|
|
|
|
Hi
I'm trying to write a small app to scan directories and find documents with embeded links or VBA code. I've tried using office interop but find alot of issues with it. This approach looks like just the job, just one small problem; When I click on a node in the tree (VBA or OLE info for example) the text that is displayed in the 'TextView' is not readable, it looks like an encoding issue but I can't where to change it. Has anyone come across this issue and managed to fix it?
Thanks
D
|
|
|
|
|
I don't think this has come up yet, but the program seems to be treating xls spreadsheets made by OpenOffice oddly. In particular, it's truncating numbers at the decimal randomly. Try opening http://sinbios.org/test.xls first in a spreadsheet application, and then in the supplied program, and you'll see that many of the numbers are truncated. Note that these numbers were inputted by hand, not using formulae. Perhaps they're being mistakenly cast into int somewhere?
|
|
|
|
|
Found the issue, turns out DecodeRK() in Record.cs returned num as an int if isFloat is false, and obviously the isFloat test is returning false for some values that should be true. Since for me it doesn't matter if integers are returned as doubles, I just changed the type of num from int to double even when isFloat is false.
|
|
|
|
|
Hi Liu Junfeng,
Absolutely this is a useful to get rid of Com-Ecel, but i have few issues in using this lib, those are, reading cell-value and cell-comment using named-cell. can you help me out in this regard....
I think mostly the answer is, you are not supporting this feature......but any ho...let me know any possibility..
Thanks for the great library.....
Mukthesh
|
|
|
|
|
Hi Liu,
After downloading your code and turning it into DLLs, I tried it out. It works very well with a single workbook but not with the next 180 of them. This is the situation: I have a SharePoint library with both .xls and .xlsx, a total of 181. They get downloaded into memory, including their streams, and I iterate through them leaving the xlsx out. As the program goes to each workbook and your code checks the header, the check fails. The bytes starting from the second workbook don't match the ones specified in your code. Now, the problem it that it works for the first file regardless of what file is the first. Do you have any ideas?
Thanks,
humble.apprentice
|
|
|
|
|
I facing the same issue, any solution for this ?
|
|
|
|
|
Firstly .xlsx file is not supported. If the file is .xls and still get "File header not recognized." message, maybe it is other versions of Compound Document. You can try comment this line
if (!doc.CheckHeader()) return null;
I am happy to work with people doing great projects.
|
|
|
|
|
Hi,
Yes, I know that xlsx is not supported, the 180 docs I mention where only xls. Any ideas?
Thanks,
humble.apprentice
|
|
|
|
|