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.
|