Excel to pdf conversion issues

hello i am having following issues

1. Overall View of Pdf:

The overall view of the excel pdf
converted document is very different from the real document as the columns are
shifted to the next page.

2.<span style=“font:7.0pt “Times New Roman””>
Scaling View not According to Excel:

<!–[if gte mso 9]>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val=“Cambria Math”/>
<m:brkBin m:val=“before”/>
<m:brkBinSub m:val="–"/>
<m:smallFrac m:val=“off”/>
<m:dispDef/>
<m:lMargin m:val=“0”/>
<m:rMargin m:val=“0”/>
<m:defJc m:val=“centerGroup”/>
<m:wrapIndent m:val=“1440”/>
<m:intLim m:val=“subSup”/>
<m:naryLim m:val=“undOvr”/>
</m:mathPr></w:WordDocument>
<![endif]–><!–[if gte mso 10]>

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}

<![endif]–>

The Scaling view of the final pdf document is different from that of the original excel document. The example is that if I enter 400 zoom for the excel to pdf conversion of a document and also view the same document in Microsoft excel at 400% zoom, then both the outputs are different.

code

private static Workbook setScalingWorkbook(Workbook wb, String szoom)
{
if (!szoom.equals(""))
{
int zoom = Integer.parseInt(szoom);

// Accessing all the worksheets of the workbook
for (int i = 0 ; i < wb.getWorksheets().getCount() ; i++)
{
Worksheet worksheet = wb.getWorksheets().get(i);
// Change the zoom factor
worksheet.getPageSetup().setZoom(zoom);
}
}

return wb;
}

3.<span style=“font:7.0pt “Times New Roman””>
Data is being Lost with Paper Size Combination:

The data of the excel sheets is
lost when different combinations of paper size and orientation are applied. The
excel sheet appears to be cropped from a side.

<!–[if gte mso 9]>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val=“Cambria Math”/>
<m:brkBin m:val=“before”/>
<m:brkBinSub m:val="–"/>
<m:smallFrac m:val=“off”/>
<m:dispDef/>
<m:lMargin m:val=“0”/>
<m:rMargin m:val=“0”/>
<m:defJc m:val=“centerGroup”/>
<m:wrapIndent m:val=“1440”/>
<m:intLim m:val=“subSup”/>
<m:naryLim m:val=“undOvr”/>
</m:mathPr></w:WordDocument>
<![endif]–><!–[if gte mso 10]>

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}

<![endif]–>

SOme sample document are
the following pdf documtn shows my excel to pdf conversion with altering the orientation and paper size
and then the image shows the alteration of the above same attributes to the original excel file…
You can clearly see the difference of the data lost


Hi Hafsa,


Thank you for contacting Aspose support.

I am afraid, we also require your input sample spreadsheets to properly evaluate your presented scenarios on our end. Before you move forward to provide the above requested, please give a try to the latest version of Aspose.Cells for Java (Latest Version) on your end to see if it makes any difference. Please feed us back with your results as well as newly generated PDF files along with their corresponding input spreadsheets.

Hey there:
The problem is still there after using the latest version of aspose cells
When i take an excel file, change its orientation to landscape and paper size from letter to pdf and then save it to pdf (everything through aspose), the result is different from the desire result.
to help you understand the issue, i have posted files
The test.xlsx , is my original excel file. Then the desired.pdf is my desired result and the output.pdf is the output through aspose. In the excel original view.PNG image you can clearly see how different is the desired look comparing with the output.pdf file. Some columns are shifted to the other page which is a drawback.

Code used:
For excel to pdf conversion:


if (ext == “xlsx”)
{
// For the xlsx type documents
LoadOptions loadOptions = new LoadOptions(FileFormatType.XLSX);

// Taking the input file stream from connection and forming Workbook Object with the specific load options
wb=new com.aspose.cells.Workbook(conn.getInputStream(), loadOptions);
}
else
{
// Taking the input file stream from connection and forming Workbook Object
wb=new com.aspose.cells.Workbook(conn.getInputStream());
}

if (check.equals(“Y”))
{
// Applying the Attribute Changes
wb = ChangeAttributesWorkBook(wb,printJO);

}

// Initializing a new OutputStream for saving the changed file
ByteArrayOutputStream dstStream = new ByteArrayOutputStream();

// Saving the changed file in PDf Format
wb.save(dstStream, com.aspose.cells.SaveFormat.PDF);

For orientation:
private static Workbook setOrientationWorkbook(Workbook wb, String orient)
{

// Accessing all the worksheets of the workbook
for (int i = 0 ; i < wb.getWorksheets().getCount() ; i++)
{
Worksheet worksheet = wb.getWorksheets().get(i);

if (orient.equalsIgnoreCase(“Portrait”))
{
worksheet.getPageSetup().setOrientation(PageOrientationType.PORTRAIT);
}
if (orient.equalsIgnoreCase(“Landscape”))
{
worksheet.getPageSetup().setOrientation(PageOrientationType.LANDSCAPE);
}
}

return wb;
}


For Paper Size

private static Workbook setPaperSizeWorkbook(Workbook wb, String ps)
{

// Accessing all the worksheets of the workbook
for (int i = 0 ; i < wb.getWorksheets().getCount() ; i++)
{
Worksheet worksheet = wb.getWorksheets().get(i);
if (ps.equalsIgnoreCase(“Letter”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_LETTER);
}
if (ps.equalsIgnoreCase(“Legal”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_LEGAL);
}
if (ps.equalsIgnoreCase(“Ledger”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_LEDGER);
}
if (ps.equalsIgnoreCase(“Executive”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_EXECUTIVE);
}
if (ps.equalsIgnoreCase(“B3”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_B_3);
}
if (ps.equalsIgnoreCase(“B4”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_B_4);
}
if (ps.equalsIgnoreCase(“B5”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_B_5);
}
if (ps.equalsIgnoreCase(“A3”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_A_3);
}
if (ps.equalsIgnoreCase(“A2”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_A_2);
}
if (ps.equalsIgnoreCase(“A4”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_A_4);
}
if (ps.equalsIgnoreCase(“A5”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_A_5);
}
if (ps.equalsIgnoreCase(“EnvelopeDL”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_ENVELOPE_DL);
}
if (ps.equalsIgnoreCase(“Quarto”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_QUARTO);
}
if (ps.equalsIgnoreCase(“Statement”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_STATEMENT);
}
if (ps.equalsIgnoreCase(“Tabloid”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_TABLOID);
}
if (ps.equalsIgnoreCase(“Paper10x14”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_10_X_14);
}
if (ps.equalsIgnoreCase(“Paper11x17”))
{
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_11_X_17);
}
}

return wb;
}

Hi Hafsa,


Thank you for providing more details and sample files.

I believe you are performing the tests on a non-windows platform where required fonts are not present. Please note, Aspose.Cells APIs require the TTF (TrueType Fonts) to be available on the platform where spreadsheet to PDF conversion has to take place. The required fonts are the ones that are used in the spreadsheet to format text as well as the default font of the spreadsheet. The API requires these fonts for the following reasons.

  • Calculate the cell height and width
  • Render the text

If you check my attached snapshots for the two pages of your generated PDF files, you will notice that Aspose.Cells generated PDF file has larger text and column widths as compared to the desired results. This behavior is caused due to the reason that Aspose.Cells for Java API is unable to find the Calibri font in your environment where you are performing the conversion.

I would suggest you to please install or copy the Calibri TTFs along with other fonts (if used in the spreadsheet) in your environment and point the Aspose.Cells for Java API to the aforesaid TTF files using the CellsHelper.setFontDir method at the start of your application. Moreover, please execute your tests while using the latest version of Aspose.Cells for Java (Latest Version).

Please go through these documentation links for your reference.

I am running the service on windows platform and i do have all the fonts in C:/Windows/Fonts
i have checked…
I will update you after explicitly setting the Font library for cells

My issue is not resolved.
I have explicitly set the options as

"PaperSize" : “EnvelopeDL”,
“Orientation” : “Landscape”
“Watermark” : “testing 123”
“Range” : "1"
for my file. The range selects only the first workbook.
I have attached the sample files as the original excel sheet by name test.xls
The desired result without the Range and Watermark (which play no role in the look) option in the actual output.pdf then the aspose output with range in the aspose output.pdf.
If you will see then you can notice some huge huge differences.
The columns are shifted to the next page. And even if i am using the windows platform, the font is changed and bold.
Now one other problem is that the watermark is not appearing problem. Please Please guide me early, waiting. my watermark code is given as

private static Workbook setWatermarkWorkbook(Workbook wb, String watermarkText)
{
// Accessing all the worksheets of the workbook
for (int i = 0 ; i < wb.getWorksheets().getCount() ; i++)
{
Worksheet worksheet = wb.getWorksheets().get(i);
com.aspose.cells.Shape wordart = worksheet.getShapes().addTextEffect(MsoPresetTextEffect.TEXT_EFFECT_1, watermarkText, “Arial Black”, 50, false, true, 18, 8, 1, 1, 130, 800);
// To set the wordart shape according to a watermark look
wordart = WatermarkShapeSet(wordart);
}
return wb;
}

/************************************************************************
*
* Function Name : WatermarkShapeSet()
* Description : It changes the watermark related shaped accordingly to give
* it the required look of a watermark
*
*************************************************************************/
private static com.aspose.cells.Shape WatermarkShapeSet(com.aspose.cells.Shape wordart )
{
MsoFillFormat wordArtFormat = wordart.getFillFormat();
wordArtFormat.setForeColor(com.aspose.cells.Color.getDarkRed());
wordArtFormat.setTransparency(1);
MsoLineFormat lineFormat = wordart.getLineFormat();
lineFormat.setVisible(false);
return wordart;
}

Hi Hafsa,

Thank you for writing back.

First of all, the recently shared Aspose generated PDF looks better than one you have shared previously. Moreover, this PDF has the correct set of fonts embedded in it. You may check this by opening the PDF in Adobe Reader, check the File->Properties, and view the Fonts tab for both Aspose generated PDF files.

Anyway, I have simplified your scenario as provided in the below code snippet. Also attached is the resultant PDF file. Could you please check it and list down the problems you see?

Java

Workbook book = new Workbook(“D:/temp/test.xlsx”);
for (int i = 0 ; i < book.getWorksheets().getCount() ; i++)
{
Worksheet worksheet = book.getWorksheets().get(i);

if(worksheet.getIndex() == 0)
{
//Set Watermark
Shape wordart = worksheet.getShapes().addTextEffect(MsoPresetTextEffect.TEXT_EFFECT_1, “testing 123”, “Arial Black”, 50, false, true, 18, 8, 1, 1, 130, 800);
MsoFillFormat wordArtFormat = wordart.getFillFormat();
wordArtFormat.setForeColor(com.aspose.cells.Color.getDarkRed());
wordArtFormat.setTransparency(0);
MsoLineFormat lineFormat = wordart.getLineFormat();
lineFormat.setVisible(false);

//Set Borders
worksheet.getPageSetup().setBottomMargin(0);
worksheet.getPageSetup().setTopMargin(0);
worksheet.getPageSetup().setLeftMargin(0);
worksheet.getPageSetup().setRightMargin(0);

//worksheet.getPageSetup().setPrintArea("");

worksheet.getPageSetup().setOrientation(PageOrientationType.LANDSCAPE);
worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_ENVELOPE_DL);
}
else
{
worksheet.setVisible(false);
}
}
book.save(“D:/output.pdf”, SaveFormat.PDF);

Please note, you are still using the older version of Aspose.Cells for Java 8.3.1.2. Please upgrade to the latest version v8.3.1.6 (link shared in my previous response).

Hi again,


Adding more to my previous response. Please share the PDF generated with above code from your end, regardless of the fact it suffices your requirement. Moreover, we also need your environment details such as follow.

  • Operating system version
  • Operating system architecture (x86/x64)
  • Operating system service pack (if any)
  • JDK version

Windows 7
x64 bit operating system
JDK 1.6

Hi Hafsa,


Thank you for the environment details. Please note, I have performed all my tests on the same platform with same JDK revision.

Please feed us back with the PDF results that I have attached in my previous response. In case the results are not up to your requirements then please share the list of discrepancies so we can either log them for correction purposes or suggest workaround for the situation.

I have tried your code but again, and after putting all the borders to zero, the pdf resemblence to the desired result has increased.
The actual output pdf file as well my now aspose output are attached.
The problem i see, is that the aspose output pdf still appears zoomed in and bold with respect to the original output.
And please give me some code for generating a very appropriate transparent grey watermark on all output sheets of excel to resulting pdf document. Thanks

Hi Hafsa,


I have saved the PDF from your provided spreadsheet (test.xlsx) using the MS Excel application, and have compared the two PDF files side by side. I can see minor difference in the text density of Aspose.Cells’ generated PDF as compared to MS Excel’s generated PDF. The text in Aspose.Cells generated PDF seems to be little bolder but not zoomed-in. You may check the comparison from the attached snapshot. I am not sure if this can be improved further however I have logged this incident for the product team’s review under the ticket CELLSJAVA-41180.

Regarding the watermarking, please confirm how do you wish to render the watermark text. If you wish to add the watermark on every page of the rendered PDF file then the smart art containing the watermark text would be added multiple times on the same worksheet whereas the smart art location and quantity will be dynamic based on the number of PDF pages to be generated. Please confirm the requirement so I may work to find a suitable solution for this requirement.

Well thankyou for the comparison file…
And yes let me know if the conversion is furthur improved.
More i need the watermark on each and every page of the geenrated excel to pdf final file…
No matter what paper size or orienation combination i add.
Thank You

Hi Hafsa,

We have evaluated the case logged earlier as CELLSJAVA-41180. I am afraid, we cannot further improve the Aspose.Cells results to match the MS Excel’s generated PDF. Reason being. MS Excel performs scaling of the textual contents according to some unknown factor while rendering the spreadsheets in PDF format. We currently do not have the internal knowledge of this behavior therefore we cannot do the same as of MS Excel. Moreover, we are curious what application are you using to render the spreadsheets in PDF format because your sample does not seem to be created by MS Excel application.

Regarding the watermarking feature, it is best you use the Aspose.Pdf for Java to stamp all the pages of Aspose.Cells generated PDF file with text or image of your choice. Please check the referenced articles and let us know if this suits your requirements. In the meanwhile, we will try to come up with a generic solution that could work for all paper sizes while using Aspose.Cells for Java API only.