Problems with cells formated as Date in Aspose.Excel

Aspose.Cells Product Family
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.

10

@mariush,
Aspose.Cells is the latest product that has replaced Aspose.Excel and is no more under active development now. It is much advanced and contains support for the advanced features in different versions of MS Excel. You can format data in a variety of ways including date format as demonstrated in the following sample code:

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();

// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];

// Adding the current system date to "A1" cell
worksheet.Cells["A1"].PutValue(DateTime.Now);

// Getting the style of A1 cell
Style style = worksheet.Cells["A1"].GetStyle();

// Setting the custom display format to show date as "d-mmm-yy"
style.Custom = "d-mmm-yy";

// Applying the style to A1 cell
worksheet.Cells["A1"].SetStyle(style);

// Adding a numeric value to "A2" cell
worksheet.Cells["A2"].PutValue(20);

// Getting the style of A2 cell
style = worksheet.Cells["A2"].GetStyle();

// Setting the custom display format to show value as percentage
style.Custom = "0.0%";

// Applying the style to A2 cell
worksheet.Cells["A2"].SetStyle(style);

// Adding a numeric value to "A3" cell
worksheet.Cells["A3"].PutValue(2546);

// Getting the style of A3 cell
style = worksheet.Cells["A3"].GetStyle();

// Setting the custom display format to show value as currency
style.Custom = "£#,##0;[Red]$-#,##0";

// Applying the style to A3 cell
worksheet.Cells["A3"].SetStyle(style);

// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);

Here are the articles that can be referred to for formatting cells in a worksheet :
Data Formatting
Format Worksheet Cells in a Workbook
Modify an Existing Style

The latest free trial version can be downloaded here:
Aspose.Cells for .NET (Latest Version)

Download a complete runnable solution here to test a variety of features of this product.