Issue in creating image of data range

Aspose.Cells Product Family
1
My use-case is to get the image of certain specified range.
Fro that, I am trying to copy that range from one workbook to to another and using SheetRender to get the desired the image. Following is the code snippet.

Range sourceRange = worksheet.getCells().createRange("A1:B4")

Workbook targetWorkbook = new Workbook(FileFormatType.XLSX);
Worksheet targetWs = targetWorkbook.getWorksheets().get(0);
Range targetRange = targetWs.getCells().createRange(0, 0, sourceRange.getRowCount(), sourceRange.getColumnCount());

PasteOptions options = new PasteOptions();
options.setPasteType(PasteType.ALL);
targetRange.copy(sourceRange, options);

Worksheet sheet = targetWorkbook.getWorksheets().get(0);

ImageOrPrintOptions imageOrPrintOptions = new ImageOrPrintOptions();
imgOptionsForTable.setImageFormat(resolvedImageFormat);
imgOptionsForTable.setHorizontalResolution(300);
imgOptionsForTable.setVerticalResolution(300);
imgOptionsForTable.setOnePagePerSheet(true);
imgOptionsForTable.setOnlyArea(true);

SheetRender sr = new SheetRender(sheet, imageOrPrintOptions);
sr.toImage(0, "C:/temp/abc.png");

I am able to get the image, however the image looses row sizes and does not render properly. Attached is the generated image and excel file for your reference. Kindly suggest what is going wrong.

Secondly, also suggest if there is some other better way to achieve this.
2

Hi,


Thanks for the template file and sample code.

I observed the issue as you mentioned by using the following sample code, I even tried setting the printable area for the sheet before taking image but in vain. The output image is not rendered fine with data. I am using our latest version/fix: Aspose.Cells for Java v8.1.0.x.
e.g
Sample code:

Workbook workbook = new Workbook(“f:\files\temp.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(0);

Range sourceRange = worksheet.getCells().createRange(“A1:B4”);

Workbook targetWorkbook = new Workbook(FileFormatType.XLSX);
Worksheet targetWs = targetWorkbook.getWorksheets().get(0);
Range targetRange = targetWs.getCells().createRange(0, 0, sourceRange.getRowCount(), sourceRange.getColumnCount());

PasteOptions options = new PasteOptions();
options.setPasteType(PasteType.ALL);
targetRange.copy(sourceRange, options);

targetWorkbook.save(“f:\files\out1.xlsx”); //The file is fine with copied range.


Worksheet sheet = targetWorkbook.getWorksheets().get(0);

sheet.getPageSetup().setPrintArea(“A1:” + CellsHelper.cellIndexToName(sheet.getCells().getMaxDataRow()+1,sheet.getCells().getMaxColumn()+1).toString());

ImageOrPrintOptions imageOrPrintOptions = new ImageOrPrintOptions();
imageOrPrintOptions.setImageFormat(ImageFormat.getPng());
imageOrPrintOptions.setHorizontalResolution(300);
imageOrPrintOptions.setVerticalResolution(300);
imageOrPrintOptions.setOnePagePerSheet(true);
imageOrPrintOptions.setOnlyArea(true);



SheetRender sr = new SheetRender(sheet, imageOrPrintOptions);
sr.toImage(0, “f:/files/out1.png”);

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

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

Thank you.
3

Hi,


Thanks for logging a ticket for this.

It seems that in addition to row/column sizes there many other issues in this.

In other example even formatting like fonts, background color etc… all are lost.

I have attached the sample xls file and output image generated for the range B3:G9 for your reference.

It would be helpful for me if you suggest some other foolproof workaround to achieve the same. So that I do not loose any styles like row/column size, fonts, background & text colors etc… while generating image output of the selected data.

Thanks for your help.


4

Hi,


I have tested your scenario a bit with your template file.
Well, when you simply select the range cells in the source range (of source Workbook sheet) and copy (CTRL +C) it and paste (CTRL +V) into a new Workbook sheet cells manually in MS Excel, you will see that data is copied without much formattings (e.g fonts with attributes, foreground/ background color, row/column widths etc.) as the formatting are lost a bit. Aspose.Cells works the same way as MS Excel does. Aspose.Cells follows MS Excel standards, so I am afraid, there is no better way to cope with it and certain formatting/ styles are lost in b/w workbooks.

Thank you.
5
Thanks for the response.

Just to let you know that the behavior of manual copy (CTRL +C) and paste (CTRL +V) into a new Workbook sheet cells is not the same when done through the ASPOSE.

I understand there are some cases in which a bit of information is lost when copy pasting data manually in Excel. But the behavior in those scenarios is not the same when done through ASPOSE which becomes very difficult to convince the customers and end-users.

For example you can see the attached xlsx file. Manually copy pasting the data to other sheet only looses the column sizes but not the actual styling info like background color, font type and size etc.. But when I do the same thing from ASPOSE the result is different (resultant png also attached for reference).

Hence there seems to be something either I am missing in my code (which I already shared to you) or something in ASPOSE implementation which the development team can figure out well.

Thanks for your help and quick responses. Looking forward to an appropriate solution.



6

Hi,


Thanks for providing further details.

Well, I have further evaluated your issue. I observed the issue as you mentioned. When copying ranges in b/w worksheets in the same Workbook, it does not copy formattings (fonts attributes, background/foreground theme colors, etc.) properly in the generated Excel file. I have used the following sample code with your template file.
e.g
Sample code:

Workbook workbook = new Workbook(“f:\files\CopySample.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(0);

Range sourceRange = worksheet.getCells().createRange(“B3:G9”);

Worksheet worksheet1 = workbook.getWorksheets().get(workbook.getWorksheets().add());
Range targetRange = worksheet1.getCells().createRange(“B3:G9”);

targetRange.copy(sourceRange);

workbook.save(“f:\files\out1.xlsx”);

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

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

Thank you.

7

Hi,


We have evaluated your issue “CELLSJAVA-40882” further.
Please try our latest version/fix: aspose-cells-8.1.1.jar (http://www.aspose.com/community/files/72/java-components/aspose.cells-for-java/entry557150.aspx).

Well, it works fine actually with our latest version/fix: v8.1.1. I was giving wrong sheet index for the source sheet. Please try our latest version with the following sample code (see the line of code in bold):
e.g
Sample code:

Workbook workbook = new Workbook(“f:\files\CopySample.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(1);

Range sourceRange = worksheet.getCells().createRange(“B3:G9”);

Worksheet worksheet1 = workbook.getWorksheets().get(workbook.getWorksheets().add());
Range targetRange = worksheet1.getCells().createRange(“B3:G9”);

targetRange.copy(sourceRange);

workbook.save(“f:\files\out1.xlsx”); //Check Sheet5 and you will see the range is properly copied with its formattings.

Let us know if you still find this issue with v8.1.1.

Thank you.


8

Hi,

Please try our latest version/fix: Aspose.Cells for Java v8.1.1.3

We have fixed your issue “CELLSJAVA-40879” now. You should autofitRows in the sheet before rendering (see the red line in the following code segment):

e.g.

Sample code:

Workbook workbook = new Workbook("f:\\files\\temp.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);

Range sourceRange = worksheet.getCells().createRange("A1:B4");

Workbook targetWorkbook = new Workbook(FileFormatType.XLSX);
Worksheet targetWs = targetWorkbook.getWorksheets().get(0);
Range targetRange = targetWs.getCells().createRange(0, 0, sourceRange.getRowCount(), sourceRange.getColumnCount());

PasteOptions options = new PasteOptions();
options.setPasteType(PasteType.ALL);
targetRange.copy(sourceRange, options);

targetWorkbook.save("f:\\files\\out1.xlsx"); //The file is fine with copied range.

Worksheet sheet = targetWorkbook.getWorksheets().get(0);

sheet.getPageSetup().setPrintArea("A1:" + CellsHelper.cellIndexToName(sheet.getCells().getMaxDataRow()+1,sheet.getCells().getMaxColumn()+1).toString());

ImageOrPrintOptions imageOrPrintOptions = new ImageOrPrintOptions();
imageOrPrintOptions.setImageFormat(ImageFormat.getPng());
imageOrPrintOptions.setHorizontalResolution(300);
imageOrPrintOptions.setVerticalResolution(300);
imageOrPrintOptions.setOnePagePerSheet(true);
imageOrPrintOptions.setOnlyArea(true);

sheet.autoFitRows();
SheetRender sr = new SheetRender(sheet, imageOrPrintOptions);
sr.toImage(0, "f:/files/out1.png");

Thank you.

9

The issues you have found earlier (filed as CELLSJAVA-40879) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
10

Thanks for the solution.


As suggested sheet.autoFitRows(); works fine as per its behavior. However, it automatically increases or decreases the rowsize to fit the content.

Is there a way where I want to have exact rowsize what was there in the source excel?

Thanks


11

Hi Neer,


Thank you for the confirmation on the suggested solution.

The autoFitRows method is designed to increase/decrease the row height according to the length/size of the contents in the cells of a particular row. Unfortunately, the API does not offer any means to retrieve the previous row height (after applying the autoFitRows procedure). However, you may use the Row.getHeight method to retrieve & store the height of a row into a variable before calling the autoFitRows method.

Hope this helps a bit.