Excel 2010 in Aspose.Cells 5.3.x and 5.2.x and 6.0.0.0

DanielWEltman · July 12, 2011, 3:15pm

I am opening the file with load options:

Dim lo
= New LoadOptions()

Dim ldo = New LoadDataOption()

ldo.SheetIndexes = New
Integer() {0}

ldo.ImportFormula = False

lo.LoadDataOnly = True

lo.LoadDataOptions = ldo

Dim workbook = New Workbook(filePath, lo)

And on that
last line I get:

Object reference not set to an instance of the object, at ٱ.♕.♅(XmlTextReader ൬) at ٱ.♕.♜(XmlTextReader ก) at ٱ.䱜.☂() at ٱ.䱜.࣫() at ٱ.䱜.䱝(Workbook ෙ, Stream ԁ, LoadDataOption ੘) at
Aspose.Cells.Workbook.Ӿ(Stream ԁ, LoadOptions Ԁ) at Aspose.Cells.Workbook.Ӿ(String
ӿ, LoadOptions Ԁ) at Aspose.Cells.Workbook…ctor(String file, LoadOptions
loadOptions)

I am able to open 2007 excel files, just not 2010. I can open 2010
files if I remove all the load options,. but I need those load options there!
Attached is a sample file. Thanks Daniel

This message was posted using Email2Forum by Amjad Sahi. (attachment)

amjad.sahi · July 12, 2011, 3:23pm

Hi,

I have tested your file with your code using latest version/fix v 6.0.0.4 (attached), it works fine. Please try the attached version.

Thank you.

DanielWEltman · July 12, 2011, 4:46pm

Thank you -- this is now working.

Daniel

DanielWEltman · July 18, 2011, 2:26pm

Hi,

In using these load options;

Dim lo = New LoadOptions()

Dim ldo = New LoadDataOption()

ldo.SheetIndexes = New Integer() {0}

ldo.ImportFormula = False

lo.LoadDataOnly = True

lo.LoadDataOptions = ldo

Dim workbook = New Workbook(filePath, lo)

in version 6.0.0.4, I think the format is still getting through.

I entered into a cell 8/25 in excel, and then I highlighted it . Though the cell was displaying "25-Aug" The actual cell value was 8/25/2011, and the format was set to custom. I then changed the actual cell value to 8/25/2009. The cell still displayed "25-Aug", but the actual cell value is no 8/25/2009. and then used my .net code using aspose to import this.

it was imported to the DB as "25-Aug" though I would expect 8/25/2009 since i am using LoadDataOnly=true and that should NOT read formatting.

It seems to me that this is a bug in the loaddataonly option still using the custom formatting to read the cells value instead of the cells actual value.

Any help or advice would be appreciated.

Thanks

Daniel

DanielWEltman · July 18, 2011, 2:33pm

By the way, just to confirm what I think is the problem, when I manually change the format of that cell to "date" instead of custom, it reads the date fine.

amjad.sahi · July 18, 2011, 3:32pm

Hi,

I am not sure about your issue. Please give us more details. Kindly create a sample console application, zip it and post it here with all the files. We will check it soon.

Thank you.

DanielWEltman · July 18, 2011, 4:00pm

Hi Amjad,

Before I spend the time it will take to do that, can you just try this?

Try and open the attached excel file with the load options as I describe:

Dim lo = New LoadOptions()

Dim ldo = New LoadDataOption()

ldo.SheetIndexes = New Integer() {0}

ldo.ImportFormula = False

lo.LoadDataOnly = True

lo.LoadDataOptions = ldo

Dim workbook = New Workbook(filePath, lo)

See if aspose reads the cell V2 as '25-Aug' (which is formatted) or as '8/25/2009' (which is the actual value of the cell.

Thanks

Daniel

DanielWEltman · July 18, 2011, 4:01pm

PS I would expect Aspose to read V2 as ‘8/25/2009’ since I have loaddataonly set to true, which should ignore formatting. However, for me, aspose is reading it as ‘25-Aug’ which is the formatted way the value appears in the cell.

amjad.sahi · July 18, 2011, 4:32pm

Hi,

Please use Cell.DateTimeValue or Cell.Value attribute for your need. See the following sample code.

Sample code:

Dim lo = New LoadOptions()

Dim ldo = New LoadDataOption()

ldo.SheetIndexes = New Integer() { 0 }

ldo.ImportFormula = False

lo.LoadDataOnly = True

lo.LoadDataOptions = ldo

Dim workbook = New Workbook(“e:\test2\200908_Monthly_LoanTESTEST.xls”, lo)

Dim dtVal As String = workbook.Worksheets(0).Cells(“V2”).DateTimeValue.ToString() '8/25/2009

Dim stVal As String = workbook.Worksheets(0).Cells(“V2”).StringValue '25-Aug

Dim val As String = workbook.Worksheets(0).Cells(“V2”).Value.ToString() '8/25/2009

Thank you.

DanielWEltman · July 18, 2011, 4:58pm

Hi Amjad, I am glad you could reproduce now. My point is that this is a bug -- the cell's real value is 8/25/2009, not '25-Aug'. That is simply the display because of the formatting. Since I am using LoadDataOnly = true, Aspose should ignore formatting and use 8/25/2009. I should not have to use any properites or anything to get that. In fact, I cannot, since I am doing a straight bulk copy to the sql db with the raw data from aspose.

Is there anyway you guys could fix this bug?

Thanks so much

Daniel

amjad.sahi · July 18, 2011, 9:07pm

Hi,

Well for your knowledge, currently, when you set LoadDataOnly to “true”, we still will load styles/formatting. I think you may control on how to export DataTime to DB by yourself for your need.

Thank you.

DanielWEltman · July 19, 2011, 10:36am

Hi Amjad. I did not know that. How do you load without formatting?

Thanks

Daniel

amjad.sahi · July 19, 2011, 2:39pm

Hi,

Well, I think you may try to do it manually by getting the un-formatted datetime values using your own code. E.g you may try to use: Cell.DateTimeValue or Cell.Value attributes.

Thank you.

DanielWEltman · July 19, 2011, 3:22pm

Thanks.

Daniel