Cell style and value are getting lost while reading from excel file

Aspose.Cells Product Family
1

Style Formatting getting lost while reading from file. I created the file and set some styles (font and alignment) to the cell. but when I tries to read back those styles from cell again by reopening the workbook, I could see the lost of formatting information. Note : I could see the formatting in Excel file.

Below is the code.

import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.Color;
import com.aspose.cells.Comment;
import com.aspose.cells.Font;
import com.aspose.cells.Style;
import com.aspose.cells.TextAlignmentType;
import com.aspose.cells.ThemeColor;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.aspose.cells.WorksheetCollection;

public class CellStyleDemo {


public static void readFormatting() throws Exception{

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

WorksheetCollection sheets = workbook.getWorksheets();

Worksheet worksheet = sheets.get(sheets.getActiveSheetIndex());

Cells cells = worksheet.getCells();

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

System.out.println(cell.getValue());

Style style = cell.getStyle();
System.out.println(style.getVerticalAlignment());
System.out.println(style.getHorizontalAlignment());
Font font = style.getFont();

System.out.println(font.getColor());
System.out.println(font.isBold()); //this gives false
System.out.println(font.getSize());//this returns 10
System.out.println(style.getBackgroundColor());

System.out.println(“done”);

}

public static void main(String arg[]){

try {
Workbook workbook = new Workbook();

WorksheetCollection sheets = workbook.getWorksheets();

Worksheet worksheet = sheets.get(sheets.getActiveSheetIndex());

Cells cells = worksheet.getCells();

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

cell.setValue(“comment testing”);

Style style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.CENTER);
style.setHorizontalAlignment(TextAlignmentType.RIGHT);
Font font = style.getFont();

font.setColor(Color.getGreen());
font.setBold(true);
font.setSize(18);
style.setBackgroundColor(Color.getBlack());

cell.setStyle(style);

workbook.save(“cellstyles.xlsx”);
System.out.println(“done”);

System.out.println(“reading back”);
readFormatting();

System.out.println(“done”);


}catch(Exception e){
e.printStackTrace();
}
}

}

Here is output :
null // this is value of the cell. don’t know why it is null
0 //Vertical alignment
5 //horizontal alignment
com.aspose.cells.Color@ff000000 //color
false //isBold
10 //size
com.aspose.cells.Color@0 //background color , I don’t see the background color in excel as well.


I am not sure what I am doing wrong with aspose. I am evaluating the aspose against POI and trying some use cases. Any help would be appriciated.

Regards,
Azhar

2

Hi Azhar,

Thank you for using Aspose products, and welcome to Aspose.Cells support forum.

We have evaluated your presented scenario while using the latest version of Aspose.Cells for Java 7.6.1, and we are unable to replicate the said problems with latest release. We recommend you to download the v7.6.1, and give it a try at your end. Hopefully, you will not face the formatting issues with latest build. In case you face any difficulties, please feel free to write back.

3

Thanks for your prompt reply.
I am already using latest version v7.6.1 and I am still facing the issue. For safer side I again downloded the latest version from the link you mentioned but that didn’t helped either.

This is my environment :
java version “1.7.0_45”
OpenJDK Runtime Environment (fedora-2.4.3.0.fc19-x86_64 u45-b15)

I know this should not matter but just in case if it helps you to resolve the issue.

Regards,
Azhar

4

Hi,


Well, I know the culprit line of code that is causing your issue. See the line of code below:
Worksheet worksheet = sheets.get(sheets.getActiveSheetIndex());

Well, as you do not set the license in your code (before using other Aspose.Cells APIs), so the above line would always return the Evaluation watermark sheet as an active sheet and not your first sheet “Sheet1” in the Excel file. So you have two things to sort out your issue at the moment, please perform any one.

1) Use and purchase Aspose.Cells for JAVA license and set it in your codes before using another other APIs set, see the document for your reference:
http://www.aspose.com/docs/display/cellsjava/Licensing

2) Change the line of code (in your JAVA program where you pasted), i.e…,
Worksheet worksheet = sheets.get(sheets.getActiveSheetIndex());
to:
Worksheet worksheet = sheets.get(0);
it will work fine for you.

Let us know if you have any other issue or queries.


Thank you.

5

Perfect. This worked. appriciate your prompt help.

Regards,
Azhar

6

Hi,


Good to know that it sorts out your issue now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.