We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Incorrect generated report

Aspose.Cells Product Family
#1

Hi,


We try use aspose-cells-8.7.1 for Java, for converting xlsx file to pdf.

Before this, we use unoconv for ubuntu. Result of converting from aspose looks not good.

val workbook = new Workbook(“report.xlsx”) 
workbook.save(“report.pdf”, SaveFormat.PDF)
this is exmaple of code what we use.
In zip file I added example of converting report.xlsx by unoconv and aspose.

What we do wrong, is it possible such kind of action with aspose ?

Thank you.
#2

Hi Yaroslav,


Thank you for sharing the samples.

I have evaluated the presented scenario on my side and I am able to notice two worth mentioning points as follow.

  • Your provided spreadsheet Report.xlsx seems to be corrupted as Excel application cannot open it without repair. On repair, Excel removed most of the contents from the spreadsheet. Being corrupted, Aspose.Cells APIs should not load it in an instance of Workbook, however, latest release 8.7.1 can load and convert it to PDF. However, the results may not be legit due to the fact discussed above. Moreover, we were not able to see the contents of the spreadsheet as well as perform the conversion via Excel application to compare it with Aspose.Cells generated PDF. Please provide us another sample that could open in Excel application.
  • I suspect you are missing the required fonts (seems to be Arial & Calibri font families are required) in your development environment. Due to this reason, Aspose.Cells APIs are not able to correctly calculate the glyph dimensions and consequently the text is being trimmed to some extent. Again, we were not able to thoroughly investigate the matter due to the reason mentioned above. Please note, Aspose.Cells APIs require the fonts to be available in the environment to get best results. You can place the TTF files of Arial & Calibri font families in any location (accessible to your user & application) and set the font directory at the start of application using CellsHelper.setFontDir method. Please review the detailed articles on this subject.

Please check the attachment for the PDF generated in Windows environment with required fonts available. Please check the PDF properties for Fonts tab, you will notice that Calibri & Arial fonts are embedded in it. Please also check the attached snapshots for the PDF properties of your provided PDF files generated with Aspose. Moreover, a snapshot is attached to show how Excel complains about invalid contents in your provided spreadsheet and asks to repair it.
#3
Hi Babar,

Thank you for quick answer.

In attachement I added report, that could be opened in Windows.

We generate xlsx in Ubuntu, through apache poi api (https://poi.apache.org/) and than convert to pdf using unoconv (https://github.com/dagwieers/unoconv).

My main question, why report not align to whole width of page when we try to do this through aspose api ? When we do this in unoconv result pdf looks good.

Thank you.
#4

Hi again,


Thank you for sharing the sample again.

Please note, Aspose.Cells APIs follow Excel’s guidelines and specification for creating, manipulating and converting spreadsheets. When you use Excel application to convert the given spreadsheet to PDF, the resultant file matches exactly with Aspose.Cells generated PDF therefore the results are correct as per Excel standards. Coming back to your original question, your current API (unoconv) is somehow automatically stretching the contents horizontally (check image in Excel & resultant PDF) which is not possible with Aspose.Cells APIs as per my testing. In normal scenarios, we can call Worksheet.autoFitColumns to expand the column widths so that the contents fit automatically but this solution cannot work in this scenario because you have merged cells spanning over multiple columns. In my opinion, you can solve it in following two ways.

  • While generating the spreadsheet, set the columns widths to accommodate full contents. This has to be accomplished on your own as you are not using Aspose.Cells APIs to generate the spreadsheet.
  • Expand the problematic columns manually in Excel or using Aspose.Cells APIs before converting the spreadsheet to PDF. At the end of this post, I have shared a code snippet that expands the columns C & AL.

That said, when you are rendering the spreadsheet on a non-Windows platform such as Ubuntu, you need to place the fonts (TTFs of Arial & Calibri font families) and set the font’s location in code. I hope you have already gone through the articles shared in my previous response in order to understand why Aspose.Cells APIs require TrueType fonts for rendering purposes and how can you direct the API to pick the fonts from a particular disc location.

Java

CellsHelper.setFontDir(directoryPathToTTFs);

Workbook book = new Workbook(inFilePath);
Worksheet sheet = book.getWorksheets().get(“Лист1”);
sheet.getCells().getColumns().get(CellsHelper.columnNameToIndex(“C”)).setWidth(5);
sheet.getCells().getColumns().get(CellsHelper.columnNameToIndex(“AL”)).setWidth(5);
book.save(outFilePath);
#5
Hi Babar,

Thank you for help.

Yes, setting folder with fonts solve one our problem :)

Now we try approach with stretching input templates or changing width of column during process of report generation and will test result doc with aspose pdf convert.

Will update post with our result.

Thank you for help.
#6

Hi again,


It is good to know that you are able to resolve one of the issue by setting the font directory. Please try other solution as well and feel free to contact us back in case you need our further assistance with Aspose.Cells APIs.
#7
Hi again.

We now at final track to use aspose in our application. But before will made final decision we should cover few issues, which stayed for now.

All templates we now prepared at Windows and than filled out at our service (hosted at Linux, code written at scala/java) and than converted through aspose.cells to pdf:

val workbook = new Workbook("source.xlsx")
workbook.save("report.pdf", SaveFormat.PDF)

As result pdf we found such problems:

1) Our images at top compressed by width, but at xlsx looks good

2) At text block with "...ddddddddddddd..." if we added addtional '\n' between "ddd" and "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" all text block will collapsed and only one line will be displayed at result pdf

3) Text block with "...aaa...". Not all data displayed at result pdf ("vvv" present at the end of text block). We want to see at result pdf all text with this big comment. And additional question, could we ask aspose to shift down this text block with pink header to next page with all other bottom blocks in right order?

At attachment you could found source xlsx file and result pdf.

Thank you.
#8

Hi Yaroslav,


Thank you for writing back.

First of all, you are using an older release (8.7.1) for evaluation. You should be using latest releases for all tests. Please note, the latest version of Aspose.Cells for Java is 8.9.0.7 (attached) and I have used this version to perform tests on the recently shared scenarios. Here are my findings for your reference.

  1. I am afraid, I am not able to observe the problem of image being compressed horizontally. Please note, I have verified this point by enabling the Grid Lines in the PageSetup of the worksheet before taking the Print Preview in Excel as well as rendering the PDF with Aspose.Cells for Java API. Please check attached resultant PDF files generated against the latest build of the API as well as the snapshot showing the comparison of the Excel Print Preview and PDF view.
  2. Please note, as per default behaviour, the Aspose.Cells for Java API renders the spreadsheet to PDF format as they are displayed in the Excel’s Print Preview. If you compare the result of Aspose.Cells against the Excel (SaveAs PDF) you will notice both are identical. As far as the contents of the row 21 are concerned, they are being rendered according to the row height. If some contents are hidden due to the height of the row, those contents will either render clipped or do not render at all in the PDF. However, you can dynamically change the height of the rows using Aspose.Cells APIs or even call autoFitRow method on a specified row. Please check the resultant PDF 8.9.0.7-modified-autofitrows.pdf which was generated against the modified spreadsheet where I have manually injected a new line break between “ddd” & “xxx” contents. You can observe that the resultant PDF is showing all contents without any clipping.
  3. This is the Excel’s limitation that a row height cannot exceed 409 points. If you check the height of the row 40 (containing “aaa”) you will notice that it has already reached its limit therefore you cannot further increase the row height to accommodate anything else than what has already been displayed in the aforementioned row.

Please check the following piece of code and give it a try on your end. In case you have any further questions or concerns, please feel free to contact us back.

Java

Workbook book = new Workbook(dir + “report.xlsx”);
book.getWorksheets().get(0).autoFitRow(20); //AutoFit row 21
book.getWorksheets().get(0).autoFitRow(39); //AutoFit row 40
book.save(dir + CellsHelper.getVersion() + “-autofitrows.pdf”);
#9

Hi Babar,


Thank you for detail answer. We will try cover all cases based on your comments. If we will have any additional questions we will ask you.
#10

Hi Yaroslav,


Sure, please feel free to contact us back in case you need any assistance with Aspose APIs.
#11

Hi Babar,


I have one question.

At new version of aspose lib function <span style=“font-family: “DejaVu Sans Mono”; font-size: 9pt; background-color: rgb(228, 228, 255);”>CellsHelper<span style=“font-family: “DejaVu Sans Mono”; font-size: 9pt; background-color: rgb(255, 255, 255);”>.<span style=“font-family: “DejaVu Sans Mono”; font-size: 9pt; font-style: italic;”>setFontDir<span style=“font-family: “DejaVu Sans Mono”; font-size: 9pt; background-color: rgb(255, 255, 255);”>(<span style=“font-family: “DejaVu Sans Mono”; font-size: 9pt; color: rgb(0, 128, 0); font-weight: bold;”>“fonts/”<span style=“font-family: “DejaVu Sans Mono”; font-size: 9pt; background-color: rgb(255, 255, 255);”>) marked as deprecated. What we should use instead of it ?
<span style=“font-family: “DejaVu Sans Mono”; font-size: 9pt; background-color: rgb(255, 255, 255);”>
<span style=“font-family: “DejaVu Sans Mono”; font-size: 9pt; background-color: rgb(255, 255, 255);”>Thank you.
#12

Hi,


You will use FontConfigs.setFontFolder method in in newer versions. See the document for your complete reference on configuring fonts in rendering spreadsheets:
http://www.aspose.com/docs/display/cellsjava/Configuring+Fonts+for+Rendering+Spreadsheets

Thank you.
#13

Hi,


Thank you, that’s work!

Also, from time to time, we began received such warn in log:

[warn] s.a.X.XToolkit - Exception on Toolkit thread
java.lang.ArrayIndexOutOfBoundsException: 85
at sun.awt.X11.XEvent.getFieldsAsString(XEvent.java:86)
at sun.awt.X11.XWrapperBase.toString(XWrapperBase.java:37)
at sun.awt.X11.XEvent.toString(XEvent.java:8)
at java.lang.String.valueOf(String.java:2994)
at sun.util.logging.PlatformLogger$JavaLoggerProxy.doLog(PlatformLogger.java:626)

All work good, but this warning worrying us.

Thank you.
#14

Hi Yaroslav,

Thank you for writing back.

Could you please try the recently shared scenario by setting the JVM option java.awt.headless=true & let us know your feedback?

#15

Hi Babar,


Sorry for big delay.

We found that this problem happened only in test environment, on production all ok. So, for now we close this problem (will monitor during production work).

I have another question.

We now bought license for aspose cells, use it, all work fine. But I am only confused with one thing, that each before calling such kind off code

val workbook = new Workbook(tempExcelPath.toString)
workbook.save(resFilePath, SaveFormat.PDF)

I need call this code

val license = new com.aspose.cells.License()
val licenseStream = new ByteArrayInputStream(licenseByte)
license.setLicense(licenseStream)

Seems like this not influence on performance (setting license not take huge amount of time). But I want to ask question, is this only one standard approach of setting aspose license before each call of workbook.save (or any other operation with aspose) ? Is this useful in production environment ?

Thank you for help.
#16

Hi again,


Sure, please keep us posted with updates in this regard.

Regarding your recent concerns, please note that you need to set the license only once per application life cycle therefore you do not need to set the license every time you need to call Workbook.save method, however, just to be sure that the license has been properly set, you can inquire by using the Workbook.isLicensed API anytime in your code. Please check the detailed article on licensing from here.
#17

Thank you, I will try and provide feedback.