Problems with cells formated as Date in Aspose.Excel


#1

Hi,

I have been using Aspose.Excel to test it’s features for a possible future use, but I have encountered some issues:
1. Can’t get date from excel files created with microsoft excel and cells formated as date (other than a few formats that are not very much used and custom formats), or from excel created from aspose and opened in my program.
2. MaxRow and MaxColumn return No. rows and No. columns are value - 1. I suppose is because you start counting from 0 and return the index, wich is right but wrong Smile.
Here is my code:

static void Main(string[] args)
{
MemoryStream stream = new MemoryStream();
Excel temp = new Excel();

Cells tcells = temp.Worksheets[0].Cells;
Cell tcell = tcells[0,0];
tcell.PutValue(DateTime.Now);
tcell.Style.Number = 14;
//
tcell = tcells[0,1];
tcell.PutValue(DateTime.Now);
tcell.Style.Number = 15;
//
tcell = tcells[0,2];
tcell.PutValue(DateTime.Now);
tcell.Style.Number = 16;
//
tcell = tcells[0,3];
tcell.PutValue(DateTime.Now);
tcell.Style.Number = 17;
//
temp.Save(“c:\svd.xls”);

Excel excel = new Excel();

excel.Open(“c:\svd.xls”);


Console.WriteLine("Num Rows: " + excel.Worksheets[0].Cells.MaxRow);
Console.WriteLine("Num Columns: " + excel.Worksheets[0].Cells.MaxColumn);

Cells cells = excel.Worksheets[0].Cells;
//
for(int j = 0; j<=cells.MaxRow; j++)
{
for(int i=0; i<=cells.MaxColumn; i++)
{
Cell cell = cells[j, i];
Console.WriteLine (“Value: " + cell.Value);
Console.WriteLine (” IsStyle?: "+ cell.IsStyleSet);
Console.WriteLine (“Formula:”+ cell.Formula);
Console.WriteLine ("Custom: "+ cell.Style.Custom);
Console.WriteLine (“Type”+ cell.Type );


//Console.WriteLine ("Date: "+ cell.DateTimeValue);
Console.WriteLine ("String: "+ cell.StringValue);

//Console.WriteLine ("Int: "+ cell.IntValue);
//Console.WriteLine (“Float: “+ cell.FloatValue);
//Console.WriteLine (“Double: “+ cell.DoubleValue);

Console.WriteLine(”=========================================”);

}
Console.WriteLine(”\nNexRow\nPress Enter…”);
Console.ReadLine();
}
//
/


//Console.ReadLine();
}
}
Here are the date format tested:

The following standard DateTime formats from MSExcel work fine:

1. eg. March 14, 1998

2. eg. 14-Mar-1998

  1. custom defined date formats (eg. Mm-dd-yyyy, mm.dd.yyyy, mmddyyyy)


The following standard DateTime formats from MSExcel don’t work:

  1. eg. 3/14/98

  2. eg. 03/14/98

  3. eg. 14-Mar-98

  4. eg. 3/14/98 1:30 PM

  5. eg. 3/14/1998


Thanks

#2

MaxRow and MaxColumn return the max row and column index. They are zero based.

About the date-time format, because date-time formats in MS Excel and .NET are different, we will convert those formats. Custom date-time formats are not supported well and we are enhancing it. Your DateTime formats listed above will be supported in next release.


#3

So for now I can’t retrive any date values from MSExcel files. When is due the next version?


#4

The new hotfix will be release at the start of next week.


#5

The new hotfix will be released at the start of next week.


#6

Please download v2.7.5 and have a try.


#7

Thanks for the fast response.
The fix works fine in .NET. But i use aspose in VB 6.0 and i get different stuff from the stuff i get in C#, so is a litlle bit tricky. But ,if in VB 6.0 for every standard DateTime formated cell the Type is 5, and for custom DateTime cell the Type is numeric and cell.style.custom will give me the format (eg. mm/dd/yy) it will do for me for now.


#8

Are those date formats in VB6.0 same as before? If yes, I think it’s a version problem. Please check if the dll in your GAC is the latest version.


#9

Yes, it was a versioning problem. I hadn’t corectly registered the dll.
Thanks again.