Migration question(s) from 2.2.1 to 8.0.1

Aspose.Cells Product Family
1

Hi,

I’m currently migrating our code from Aspose.Cells 2.2.1 to version 8.0.1 and I have a couple of questions:

1. FileFormatType.EXCEL2007 is not available… EXCEL_2007_XLSX is deprecated… So I guess I should use: XLSX?
2. AutoFilter.setRange(CellArea cellArea) is not available anymore… The How to Migrate to Aspose.Cells 7.0.0 or Higher|Documentation documentation doesn’t list this… how do I go about and change the code so it does the same in 8.0.1 ? Is there a CellArea to range String converter or something?
3. cell.getStyle().getNumberFormatType() == NumberFormatType.NUMBER; does not seem to be supported anymore? How do I work around this in 8.0.1? cell.getNumberCategoryType() == NumberCategoryType.NUMBER; ?

Kind regards,
Jeroen van der Grift

2

Hi,


1) Your understanding is correct, you will use FileFormatType.XLSX constant.

2) Well, in the new versions, you may try to use:
e.g
Sample code:

String areaRange = “B2:F14”;
autoFilter.setRange(areaRange);

//OR you may try to use other overload, i.e.,
public void setRange(int row, int startColumn, int endColumn)
Sets the range to which the specified AutoFilter applies.
Parameters:
row - Row index.
startColumn - Start column index.
endColumn - End column Index.

3) You may try to use:
e.g
Sample code:

cell.getNumberCategoryType() == NumberCategoryType.NUMBER

Hope, this helps a bit.

Thank you.
3

Regarding 2:

So if old code says:

sheet.getAutoFilter().setRange(CellArea.createCellArea(0, 0, rowCount, columnCount - 1));

I do this now as follows:

String from = CellsHelper.cellIndexToName(0,0);
String to = CellsHelper.cellIndexToName(rowCount, columnCount - 1);
sheet.getAutoFilter().setRange(from + “:” + to);

is that correct?

4

Another question:

The code does cell.setValue("", false); … 8.0.1 does not have the boolean parameter.
Can I just remove it or what did that boolean do exactly in 2.2.1? Do I need to do something extra in 8.0.1 to mimic that behaviour?

5

Hi,


Regarding 2, your code looks correct, I think you may use rowCount-1 accordingly.

For your other question, you may try to use cell.putValue("", false) overloaded method.

Thank you.
6

Hi… I seem to have detected a discrepancy when upgrade from 2.2.1 to 8.0.1 that I can’t explain…

There is a test which compares the getStringValue of a Cell with the expected String value “€ 5,01”… when I inspect the Cell it is listed like this: Aspose.Cells.Cell [ B6; ValueType : IsNumeric; Value : € 5,01 ; Formula:=SUM(B2:B4) ]

The test fails because there is a space character postfixed to the output of the getStringValue call (as you see above in between the 1 and the ;).

Is this possibly a regression/bug in 8.0.1?

7

Hi,


Could you provide us your template Excel file (containing the currency value) and sample code with v8.0.1.x here, we will check your issue soon.

Thank you.
8


Oke… I have attached two output files generated with aspose… filename should make clear which version was used…

The unit test code used to read the attached files… is (more or less) the same for both versions:

Workbook wbActualResult = new Workbook(filePath);
Worksheet sheet = wbActualResult.getWorksheets().get(“data”);
wbActualResult.calculateFormula();
Cells actualCells = sheet.getCells();

The assert from the unit test:
Assert.assertEquals(“€ 5,01”, actualCells.get(5, 1).getStringValue());

On 2.2.1 getStringValue() returns the expected string… on 8.0.1 "€ 5,01 " is returned (space character postfixed).

9

Hi,


Thanks for the template file and sample code.

After an initial test, I observed the issue as you mentioned by using your sample code with your template file. A space character is added to the getStringValue output. I used the following sample code with both your files with v8.0.1.3 and got the same results.
e.g
Sample code:

Workbook wbActualResult = new Workbook(“aspose_801.xls”);
Worksheet sheet = wbActualResult.getWorksheets().get(“data”);
wbActualResult.calculateFormula();
Cells actualCells = sheet.getCells();

System.out.println(“€ 5,01:” + actualCells.get(5, 1).getStringValue() + “:end”);

I have logged a ticket with an id “CELLSJAVA-40811” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.




10
Hi,

We have evaluated your issue further. Well, it is not a bug in the product. In fact it is an enhancement included from v8.0.0 for formatting numeric values according to the users' reasonable requirement. Please see the release note of this official version at http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/entry534643.aspx (the section "Cell.StringValue is changed for special formatting pattern with '*' and '_'"). In your provided template file, the formatting pattern is "€" #,##0.00_-, the '_' at the tail(end) denotes one space should be appended for the formatted result. Also you can confirm the result is correct by copying this cell to a text editor, you can find the extra space at the tail of the cell content.

Hope, you understand now.

Thank you.
11

Ah ok… thanks… I’ll check the release notes and write some javadoc this is changed but correct behavior!