Migration from Aspose.Cells for Java 2.5.4 to 7.0.4

wroeling · January 3, 2012, 8:46am

I'm currently doing a migration from Aspose.Cells for Java 2.5.4 to 7.0.4

There is a huge amount of changes in the API since you switched the codebase to the .NET development. Not all changes are very well documented, but I was able to sort out most things by the (inclomplete!) list that I found here: <a href="https://forum.aspose.com/t/122434</a> There are still quite a few things that puzzle me. Maybe you can help me?I want to apply changes to the style of a single cell (like A12) or to a range of cells (like (A12:B20) without affecting the settings that are already applied. Eg: enlarge the fontsize without affecting the font, the color, the alignment, etc.. How do I do that? How do I lock a cell in 7.0.4?How do I convert this piece of code: if (type == FileFormatType.CSV || type == FileFormatType.TAB_DELIMITED) { workbook.getOpenOptions().setConvertNumericData(false); workbook.getOpenOptions().setEncoding("utf8");
}

Regards,

Wim Roeling

amjad.sahi · January 3, 2012, 11:38am

Hi,

1) Apply font size, color, alignment settings to cell/range of cells. etc.

//…

//Setting the font size

Style style = cell.getStyle();

Font font = style.getFont();

font.setSize(14);

font.setColor(Color.getBlue());

//Setting the horizontal alignment of the text

style.setHorizontalAlignment(TextAlignmentType.CENTER);

cell.setStyle(style)

2) How to lock a cell

See the topic, especially the sub topic: “Protect a few Cells in the Worksheet”:

http://www.aspose.com/documentation/java-components/aspose.cells-for-java/protecting-worksheets.html

3) Code conversion

See a sample code below:

TxtLoadOptions lo = new TxtLoadOptions(LoadFormat.CSV);

lo.setEncoding("UTF-8");

lo.setConvertNumericData(true);

Workbook workbook = new Workbook("myfile.csv", lo);

Hope, this helps.

thank you.

shakeel.faiz · January 3, 2012, 8:33pm

Hi,

For your first question, we think you wanted to apply a style property to a range.

Please follow this code

Java

Range r = cells.createRange("A1", "A3");
style = wb.createStyle();
style.getFont().setSize(20);
StyleFlag f = new StyleFlag();
f.setFontSize(true);
r.applyStyle(style, f);

wroeling · January 4, 2012, 9:17am

This works in a lot of cases, but I still can not get it right for all situations:

For example, to give an existing solid border a red color, I use this:

Cells cells = workbook.getWorksheets().get(0).getCells() ;
cells.CreateRange( "A1" );
Style style = workbook.createStyle();
StyleFlag flag = new StyleFlag();
Border border = style.getBorders().getByBorderType( BorderType.LEFT_BORDER );
border.setColor( Color.fromArgb( 0xFF0000 ));
flag.setLeftBorder( true );
range.applyStyle( style, flag );

But this clears the border first and then tries to color a non-existing border! The only way to get it right is to set the border linestyle and the border color simultaniously. This is not want I want, however. And how do I get the line style of a range? It's always 0 when I do border.getLineStyle()

Regards,

Wim Roeling

amjad.sahi · January 4, 2012, 11:33am

Hi,

When you use workbook.createStyle() method it would instantiate a new Style with default formatting options/settings, so you have to provide all the border formatting details from the scratch. This technique is useful when you are doing formatting from the scratch. I think for your need, you may try to change your code a bit, e.g

Sample code: (Suppose I have a template file which has a left border set on A1 in the first worksheet…)

Workbook workbook = new Workbook(“bordercolor.xlsx”);

Cells cells = workbook.getWorksheets().get(0).getCells() ;

Cell cell = cells.get(“A1”);

Style style = cell.getStyle();

StyleFlag flag = new StyleFlag();

Border border = style.getBorders().getByBorderType(BorderType.LEFT_BORDER);

border.setColor(Color.fromArgb( 0xFF0000 ));

flag.setLeftBorder( true );

cell.setStyle(style, flag);

workbook.save(“outbordercolor1.xlsx”);

Thank you.

wroeling · January 5, 2012, 10:12am

Thanks for your help! I fixed almost all issues now that came up during the migration to version 7.0.4

There are two things remaining:

1. In a previous version of Aspose.cells I could distinguise betweein integer and double values in a cell with CellValueType.INT and CellValueType.Double. In 7.0.4 there's only CellValueType.IS_NUMERIC. Is there a way to tell where it's an integer or not or to find the precision of the value? (The number of digits after the decimal point, such that 123.0000 in NOT considered to be an integer)

2. I did not succeeed to protect a sheet with a password, even after reading the article about protection that you sugested. What am I doing wrong? This is my Java code:

Workbook workbook = getWorkbook();
Worksheet worksheet = workbook.getWorksheets().get( workbook.getWorksheets().getActiveSheetIndex() );
Protection protection = worksheet.getProtection();
protection.setPassword( "secret" );

Regards,

Wim Roeling

amjad.sahi · January 5, 2012, 11:53am

Hi,

1) Well, you may use Cell.getValue() method to obtain the cell value, the possible data types might be Double, Boolean, Integer, null etc. Also, you may use Cell.getDoubleValue() and Cell.getIntValue() methods for your needs.

2) Please add three lines to your code e.g

Protection protection = worksheet.getProtection();

protection.setAllowEditingContent(false);

protection.setAllowEditingObject(false);

protection.setAllowEditingScenario(false);

protection.setPassword( “secret” );

OR add a single line instead:

Workbook workbook = getWorkbook();
Worksheet worksheet = workbook.getWorksheets().get( workbook.getWorksheets().getActiveSheetIndex() );

worksheet.protect(ProtectionType.ALL, "secret", null);

it will work fine.

Thank you.

wroeling · January 6, 2012, 6:54am

I tried both, but both do not work.

1) When I use cell.getValue() every numeric value is returnd as a Double. There is no way to tell if it is a double or an integer. When I do cell.getIntValue() I just get the integer part. This does not tell me if the cell was an integer or not. When I do cell.getDouble() I get a Double value. I can ofcourse check if the fractional part is 0, but that's also the case for a value such as 123.00 which is not an integer but a double whitch happens to have a fractional part of .00

I really cant figure out a way to tell the difference between doubles and integers, like I was able to in earlier versions.

(2) I tried everything you suggested, but there is still no password on my sheet.

shakeel.faiz · January 8, 2012, 8:22pm

Hi,

1)

For the value type of double and integer, in fact even if you input a int value(such as 123) in ms excel manually, ms excel will save it as double too. For those values even by old versions you will get DOUBLE type too.

Only a special range of int values(such as 234567), ms excel save them as int in xls file and old version of aspose.cells will give INTEGER type.

So, we think it makes little sense for users to check the read value type and we now only keep NUMERIC type for them in new versions. If you really need to check the value type of double or integer, we think you have to check them by your own code,

Please see the sample code below:

Java

Object v = cell.getValue();

if(v instanceof Double)

{

Double d = (Double)v;

if(d.intValue() == d.doubleValue())

{

…

}

}

2)
For protection of a sheet, we tested following code:

Java

Workbook wb;

wb = new Workbook();

WorksheetCollection wss = wb.getWorksheets();

Worksheet sheet;

sheet = wss.get(0);

sheet.protect(ProtectionType.ALL, “123”, null);

wb.save(“res.xls”);

When open the generated res.xls in ms excel, we found the sheet has been protected correctly. One has to input the correct password “123” when chose “unprotect sheet”.

If you still get issue, please send us your sample project and we will check it soon.