Free Support Forum - aspose.com

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

<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>I am opening the file with load options:

<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>Dim lo
= New LoadOptions()
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>Dim ldo = New LoadDataOption()
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>ldo.SheetIndexes = New
Integer() {0}
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>ldo.ImportFormula = False
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>lo.LoadDataOnly = True
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>lo.LoadDataOptions = ldo
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>Dim workbook = New Workbook(filePath, lo)
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>And on that
last line I get:
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>Object reference not set to an instance of the object, at ٱ.<span style=“font-size:12.0pt;font-family:“MS Mincho”;mso-bidi-font-family:“MS Mincho”;
mso-ansi-language:EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>♕<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>.<span style=“font-size:12.0pt;font-family:
“MS Mincho”;mso-bidi-font-family:“MS Mincho”;mso-ansi-language:EN-US;
mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>♅<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>(XmlTextReader <span style=“font-size:12.0pt;
font-family:“Kartika”,“serif”;mso-fareast-font-family:“Times New Roman”;
mso-ansi-language:EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>൬<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>) at ٱ.<span style=“font-size:12.0pt;
font-family:“MS Mincho”;mso-bidi-font-family:“MS Mincho”;mso-ansi-language:
EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>♕<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>.<span style=“font-size:12.0pt;font-family:
“MS Mincho”;mso-bidi-font-family:“MS Mincho”;mso-ansi-language:EN-US;
mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>♜<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>(XmlTextReader <span style=“font-size:12.0pt;
font-family:“Angsana New”,“serif”;mso-fareast-font-family:“Times New Roman”;
mso-ansi-language:EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>ก<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>) at ٱ.<span style=“font-size:12.0pt;
font-family:“PMingLiU”,“serif”;mso-bidi-font-family:PMingLiU;mso-ansi-language:
EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>䱜<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>.<span style=“font-size:12.0pt;font-family:
“MS Mincho”;mso-bidi-font-family:“MS Mincho”;mso-ansi-language:EN-US;
mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>☂<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>() at ٱ.<span style=“font-size:12.0pt;
font-family:“PMingLiU”,“serif”;mso-bidi-font-family:PMingLiU;mso-ansi-language:
EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>䱜<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>.࣫() at ٱ.<span style=“font-size:12.0pt;
font-family:“PMingLiU”,“serif”;mso-bidi-font-family:PMingLiU;mso-ansi-language:
EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>䱜<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>.<span style=“font-size:12.0pt;font-family:
“PMingLiU”,“serif”;mso-bidi-font-family:PMingLiU;mso-ansi-language:EN-US;
mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>䱝<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>(Workbook <span style=“font-size:12.0pt;
font-family:“Iskoola Pota”,“sans-serif”;mso-fareast-font-family:“Times New Roman”;
mso-ansi-language:EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>ෙ<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>, Stream ԁ, LoadDataOption ੘) at
Aspose.Cells.Workbook.Ӿ(Stream ԁ, LoadOptions Ԁ) at Aspose.Cells.Workbook.Ӿ(String
ӿ, LoadOptions Ԁ) at Aspose.Cells.Workbook…ctor(String file, LoadOptions
loadOptions)
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;mso-fareast-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:EN-US;
mso-bidi-language:AR-SA”>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)

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.

Thank you -- this is now working.

Daniel

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

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.

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.

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

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.

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.

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

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.

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

Thanks

Daniel

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.

Thanks.

Daniel