Aspose Generated File can not be proccessed in SSIS

Aspose.Cells Product Family
1

Hi All,

I am facing a problem when we are processing aspose generated file in SSIS.

Issue : The SSIS Pacakge throws an Execption when a Blank value is stored in the Cells which format to grey color and also locked . We are unable to find what type of data is that cells. The package throw Execption in the below if Condition

If row.Item("Lower_Range_Target").ToString <> "" Or (Not TypeOf row.Item("Lower_Range_Target") Is System.DBNull) Then

dblLowerRangeTarget = row.Item("Lower_Range_Target")

End If

Kindly let me know if you have any solutions to this.

2

Hi,

We will get back to you for your issue.

Thank you.

3

Hi,

Well, I think you may try to change your code as follows if it works:

If (Not TypeOf row.Item("Lower_Range_Target") Is System.DBNull) And row.Item("Lower_Range_Target").ToString <> "" Then

dblLowerRangeTarget = row.Item("Lower_Range_Target")

End If

I think you should first test whether the object is null, then further check if the value is an empty string value.

Thank you.

4

Hi ,

I have changed the code and checked but it not working. can you tell me one thing when we are locking the cell and also graying out the cells.whats happening there ????

With Regards,

Naveen T

5

Hi,

We do not add any additional attribute/process while locking the cells or graying out the cells.
Could you open the file in MS Excel and save it, then check whether SSIS can load the saved file fine.

Thank you.

6

Hi ,

When we are use Aspose 3.7 we were using same package to process the file that it was working. We have update Aspose 4.5 we are getting this issue. kindly let me know how to solve this issue.

Thank you.

7

Hi,

Could you conduct a test i.e.., try you open the Aspose.Cells generated file in MS Excel manually and then re-save it, now check whether SSIS can load the file fine or not.

We will further try to explore your issue.

Thank you.

8

Hi,

We can open the Excel file in the MS Excel and its working issue and the package throw the same exception.

thank you,

9
Sorry for this problem. However, we cannot reproduce the problem in our places.
Can you send me two Excel files, one is created with Aspose.Cells 3.7 and another is created with Aspose.Cells 4.5? Both files contain same data. Thus we can check the difference to make sure what causes this problem.
We will check it ASAP. Thank you.
10

We got the files created with Aspose.Cells v3.7 and Aspose.Cells v4.5. Could you tell me which cell caused this problem? Thank you.

And please try to change your code to:

If (Not row.Item("Lower_Range_Target") is Nothing) Or row.Item("Lower_Range_Target").ToString <> "" Or (Not TypeOf row.Item("Lower_Range_Target") Is System.DBNull) Then

dblLowerRangeTarget = row.Item("Lower_Range_Target")

End If

If it still doesn't work, could you post your whole error message here? Thank you.

11

Hi Laurence

As per your suggestion I have made the changes in the Code. But I am get the same exception below are details.

First Try :

I have replaced the Existing code by the code which you have given. Then It has throw the same Exception “Input string was not in a correct format.Couldn’t store <> in Lower_Range_Target Column. Expected type is Double.”.

Second Try :

I have replaced the Existing code by the code which you have given but while assigning the value to the variable I have covert the Value to Double. Then also It has throw the same Exception “Input string was not in a correct format.Couldn’t store <> in Lower_Range_Target Column. Expected type is Double.”.

Code Snippets :

If (Not row.Item("Lower_Range_Target") Is Nothing) Or row.Item("Lower_Range_Target").ToString <> "" Or (Not TypeOf row.Item("Lower_Range_Target") Is System.DBNull) Then

dblLowerRangeTarget = Convert.ToDouble(row.Item("Lower_Range_Target"))

End If

As per the Exception the Error occur in the Lower_Range_Target Column

Please let me know in case you need information.

With Regards,

Naveen T

12

Hi Naveen,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.


After checking the two files, we think we have found the issue. We found that the type of the Cell "T2" is String and the value of the Cell "T2" is "".

Please check whether there is Cell.PutValue("") in your code. In old version 3.7, we will process "" string value as null value, when you call Cell.PutValue method. But we found out that MS Excel can use "" as the string value of the cell.

Please replace Cell.PutValue("") with Cell.PutValue(null) in your code and check whether the input value is "" before calling Cell.PutValue() method.

Thank You & Best Regards,