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

Free Support Forum - aspose.com

Converting Excel files to PDF - variety of Style and Format Issues

We are interested in using Aspose Cells to generate PDFs of our reports; however, there are a variety of style and formatting errors when converting the .XLSX files to PDF. We currently have licensing for 8.1.0, but I have also run watermarked versions in 8.1.2 to demonstrate that these issues persist through the current version of the product. These are toy examples, but they demonstrate the formatting issues that we are concerned with. The pdf files with just ____.pdf are 8.1.0 and those that are ____X.pdf are 8.1.2


Animal Example:
- Special Characters: in the xlsx file, we see diamond and delta characters in the title above the chart, however in both AnimalExample.pdf and AnimalExampleX.pdf, we see different characters.
- Chart formatting - gridlines: The secondary vertical gridlines in the pdf versions are not there
- Chart formatting - graph lines: the graphlines for the CATS and MICE datapoints aren’t visible in the pdfs. The dots are not connected, making it difficult to read the trend lines.
- #NA - In the charts below, we have #NA in the cells, but when we print the xlsx files directly, those conver to “-” but they stay as “#NA” in the pdf.

Cheese Example:
- Chart: the value numbers occur on the bottom axis of the chart for Creamy, Firm, Spreadable, Supple, Smooth, and Flaky when they are not supposed to. Additionally, Crumbly has other numbers within the column.

Fruit Example
- Title formatting: the titles of the last two columns run into each other in the PDF but not the xlsx document.


All of the pdf files are generated through the following code:
PdfSaveOptions opt = new PdfSaveOptions(SaveFormat.PDF);
opt.setAllColumnsInOnePagePerSheet(true); //makes each worksheet a page in the pdf
String fname = outFile;
FileOutputStream fos = new FileOutputStream(fname);

wb.save(fos, opt);
fos.close();

Hi Kelly,


Thank you for contacting Aspose support.

We have evaluated all of your presented scenarios on our end while using the latest version of Aspose.Cells for Java 8.2.0. We are able to reproduce all mentioned problems except the first one. Please check the attached resultant PDF for “AnimalExample.xlsx”. You will notice that the diamond and delta characters in the chart title have been rendered correctly.

Rest of the problems have been logged in our bug tracking system for further investigation & correction purposes. Ticket details are as follow:

  • CELLSJAVA-40960: Chart formatting - grid lines
  • CELLSJAVA-40961: Chart formatting - graph lines
  • CELLSJAVA-40962: Rendering of #N/A value
  • CELLSJAVA-40963: Unwanted values on the chart bottom axis
  • CELLSJAVA-40964: Changed Chart Axis value
  • CELLSJAVA-40965: Merging of the title cells

Please spare us little time to properly analyze the problems. In the meanwhile, we will keep you posted with updates in this regard.

I am unable to see your attachment (404 error).


Would the symbol rendering have to do with the default font? Is there some way that I would be able to fix this locally?
Hi Kelly,

There could be a problem with the community server so I have uploaded the PDF file on the Dropbox storage.

Regarding the missing symbols on your end with the same spreadsheet, I don't think the issue is related to the fonts. However, please confirm this by using the latest version of Aspose.Cells for .NET 8.2.0 on your end, without making any further changes in your environment. You can get the latest release from the link provided below.
http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/default.aspx

I am using Java for this implementation, not .NET. I have tried the 8.2.0 version of the product and all problems persist. Including the missing symbols.

Hi Kelly,


Sorry for the mistake Please give a try to the latest version of Aspose.Cells for Java 8.2.0.1 against the problem of missing symbols for spreadsheet “AnimalExample.xlsx”. We have tested the build on our side, and the symbols have rendered fine. In case they won’t appear on your side, we will further investigate the matter and log appropriate ticket(s) if required.

Right, I have run that test already. I still have the symbol rendering issues. I’ve attached the resulting pdf

Hi again,


Thank you for your quick response.

Please provide us your environment details such as Operating System version & architecture (32bit/64bit) and JDK version & type. Moreover, if the spreadsheet was created locally, and if it is feasible for you to provide the fonts used in the spreadsheet, please share them in an archive. You can upload the archive to any free file hosting services such as Dropbox or Google Drive, and share the link here.

I am operating on RedHat version 4.1.2-46 64bit. My JDK version is 1.7. I cannot provide the font files, but I have attached a list of the fonts we have available.

Hi Kelly,


Thank you for the information.

We will try to arrange all the fonts provided in your list to simulate your environment on our side. Please spare us little time for it. In the meanwhile, we will keep you posted with updates in this regard.

Hi Kelly,


We have re-evaluated the problem related to the missing symbols on the rendered PDF file against the spreadsheet “AnimalExample.xlsx”. We have confirmed that the problem is related to the missing font (Ariel) as we were unable to find it in your provided list of fonts, nor the PDF shared by you contains this in the embedded font list. However, when we have generated the PDF on our side (on Ubuntu), the PDF has the complete list of embedded fonts, and the symbols have rendered fine.

In order to avoid this situation, please install the Arial TrueType font(s) at your platform. If you just want to confirm our claim, please follow the steps provided below.

  1. Download the aforesaid fonts from this link.
  2. Place them in any accessible directory.
  3. Specify the font location using the statement CellsHelper.setFontDir(location to the fonts) at the start of your application.
  4. Load the spreadsheet in an instance of Workbook & save it back in the PDF format.
  5. Check the PDF properties for embedded font list.

Please note, the provided fonts have been taken from a Windows 7 platform, and shared for testing purposes only. In case you wish to use the same font files in a production environment other than Windows, we strongly recommend you to check the vendor’s licensing information.

Let us know of your feedback.

Hi,

Thanks for using Aspose.Cells for Java.

We have fixed the following issues

(CELLSJAVA-40962 - Aspose.Cells renders the #N/A value differently than MS Excel)
(CELLSJAVA-40965 - Cells run into each other in the PDF)

Please download and try this fix: Aspose.Cells for Java v8.2.0.2 and let us know your feedback.

Thank you.


I have tested the changes and I no longer have the #NA issue. However, I still have the cells merging together in the title tabs - the fruit example pdf still looks the same where the column titles with black backgrounds look the same. Additionally, I have tried your fix for the special characters (adding the ariel font set path) and my issues still persist.

This is the screenshot of my fonts

Hi Kelly,


Thank you for your feedback.

We have tested the case CELLSJAVA-40965 against the latest version of Aspose.Cells for Java 8.2.0.2 on Windows 7 & Ubuntu 12 LTS 64-bit. Unfortunately, we are unable to replicate the issue for the sample “FruitExample.xlsx”. Please check the attached resultant PDF file for your reference. If the result is different on your end, the probable reason is again the missing fonts that are also required to correctly calculate the cell width & height. We have also tested the problem of missing symbols on Ubuntu environment and we are unable to replicate this issue after placing the font (Arial) and pointing the API to use those fonts. In order to further investigate the issues, please provide us the resultant PDF files generated with Aspose.Cells for Java 8.2.0.2 on your end as well as the source code snippet to set the font directory.

Please note, we are unable to get the exact version of Red Hat Linux that you are currently using, so we are testing the cases on Ubuntu for now. We will be keep searching for the exact RHEL version for thorough investigation.

I was mistaken about the CELLSJAVA-40965 issue, my pdf looks the same as yours.


I have attached the resulting PDF for the Animal Example that displays the missing symbols.

This is my full program to generate the pdfs. allFiles.txt has the names of the existing xlsx files and the font files are located in a folder within the project folders. I am running RHEL 5


import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.FileReader;
import com.aspose.cells.*;


public class AsposeTesting {

AsposeTesting(){
}
public static void main(String args[]) throws Exception{
AsposeTesting at = new AsposeTesting();
at.processFiles(“allFiles.txt”);
}
private Workbook openWB(String inName) throws Exception{
Workbook wb = new Workbook(inName+".xlsx");
System.out.println(“Opened “+inName+”.xlsx”);
return wb;
}
private void processFiles(String allF) throws Exception{
String inName;
BufferedReader br = new BufferedReader(new FileReader(allF));
while((inName=br.readLine())!=null){
saveAsPDF(openWB(inName), inName);
}
br.close();
}
private void saveAsPDF(Workbook wb, String outFile) throws Exception{
PdfSaveOptions opt = new PdfSaveOptions(SaveFormat.PDF);
CellsHelper.setFontDir("/ariel.zip_FILES/");
System.out.println("\t\t fonts in: “+CellsHelper.getFontDir());
opt.setAllColumnsInOnePagePerSheet(true); //makes each worksheet a page in the pdf
String fname = outFile+”.pdf";
FileOutputStream fos = new FileOutputStream(fname);

wb.save(fos, opt);
System.out.println("\tSaved “+outFile+”.pdf");
fos.close();
}
}

Hi Kelly,


It’s good to know that we may now close the CELLSJAVA-40965.

Regarding the missing symbols problem, I would like to confirm that the folder “/ariel.zip_FILES/” from your source code contains the .ttf files. Please make sure that conversion takes place only after setting the required .ttf file as follow.

Java

java.io.File f = new java.io.File(fontLocation + “arial.ttf”);
if(f.exists())
{
ArrayList fonts = new ArrayList();
fonts.add(fontLocation + “arial.ttf”);
CellsHelper.setFontFiles(fonts);

Workbook book = new Workbook(input);
book.save(outputPdf, SaveFormat.PDF);
}

Let us know your feedback on this.

Hi Kelly,


We are wondering if you are able to resolve the problem with code snippet provided in the previous post. Please keep us posted with updates so we may close this issue as well.

I was not. I am not sure how to demonstrate my problems to you since I have followed all the steps you have sent me, but the issue still persists.

Hi Kelly,


First of all, thank you for your continues support and help in sorting out this problem, We really appreciate your input.

We are sorry to know that the presented solution didn’t work, however, the behavior is strange to us. If the fonts are available on the specified location the PDF file should embed the correct fonts while rendering all the symbols correctly. Please note, only arial.ttf file is required to render the symbols in question. The previously provided code snippet first makes sure the availability of the arial.ttf file on the machine then converts the spreadsheet to PDF. If you are still not getting the desired results then you must either have the Arial Unicode font installed on your Linux distribution and pointing the application towards it as per Aspose.Cells’ default behavior to first check the system’s font directory.

Another important note, when Aspose.Cells API does not find the appropriate fonts or any font, it simulates the Arial font for rendering purposes. In this case, the Arial font isn’t embedded in the PDF, and the PDF properties will look like as attached proprieties.png. This is the indication that the specified path either does not exist or it does not contain the required fonts.

Please try the following code that restricts the API to ignore the system fonts and render the text using only the fonts available on specified location.

Java

System.setProperty(“Aspose.Cells.FontDirExc”, “C:/Users/Babar Raza/Desktop/ariel/”);
Workbook book = new Workbook(“D:/temp/AnimalExample.xlsx”);
book.save(“D:/temp/output.pdf”, SaveFormat.PDF);

The above code does not require setting the fonts location using CellsHelper.setFontDir method. However, you have to extract the ariel.zip on your machine and pass the path to the setProperty method as second parameter. It's best to extract the TTFs in some folder that is accessible to the application/user. My suggestion is to place them in the Downloads folder.

Following results are required for further investigation.
  1. PDF generated with this code.
  2. PDF generated with code recently provided.