|
I took a peek at the dll using ildasm and am of the opinion that the functionality you're looking for isn't there... at least not yet.
|
|
|
|
|
Hi there, a great effort. I'd like to point out a few issues I've found with the Encoder. I'd be very grateful if someone could post a solution, particularly to (1).
1) When writing large Decimal values they don't seem to be encoded correctly. E.g. I pass in 5690000 as Decimal through the encoder - when opening up the spreadsheet in Excel, it reports the value written as -5047418.24.
2) The "EncodeCell" method in WorkSheetEncoder.cs does not contain support for the C# "short" data type. Therefore when passing anything as Int16/Short you get an "Invalid cell value".
Thanks
|
|
|
|
|
Fixed now.
I am happy to work with people doing great projects.
|
|
|
|
|
Congratulations!! Very good work!
|
|
|
|
|
How I can get background color in cell? I'm getting only ColorIndex but don't know how translate it to RGB.
|
|
|
|
|
谢谢你,很好很棒。
A Dream About Software。
|
|
|
|
|
谢谢!
I am happy to work with people doing great projects.
|
|
|
|
|
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.
|
|
|
|
|