Hi,
I’m using Aspose Cells to modify an Excel document. After that I save the document as PDF using
workbook.save(byteoutpdf, FileFormatType.PDF);
but the problem is that my custom format defined for numbers on my excel document are not kept when transforming to Excel, the standard “,” separator appears instead.
Can you help me?
Thanks
Hi,
Thank you for writing to us. We’ll be glad to assist you in regard, but for that we need your Input (Excel) and Output (PDF) files along with the piece of code you are using to modify the existing file.
Hi,
here is the code, you can find attached the 2 files (input and output -> the excel file is only a part of the pdf file)
public static byte[] prefillTemplate(Long documentType, byte[] template,
EcfFiscalyear fiscalYear, Long year) throws ECFeDocsException{
ByteArrayOutputStream byteout = new ByteArrayOutputStream();
try{
VEcfCompany company = fiscalYear.getCompany();
DateTimeFormatter fmt = DateTimeFormat.forPattern(“dd/MM/yyyy”);
String yearEnd = “31/12/”;
if (company.getYearEnd() != null) {
yearEnd = company.getYearEnd();
}
int day = Integer.parseInt(UtilString.substring(yearEnd,0, yearEnd.indexOf("/")));
int month = Integer.parseInt(UtilString.substring(yearEnd, yearEnd.indexOf("/")+1));
LocalDate date = new LocalDate(year.intValue(),month,day);
String endDate = fmt.print(date);
date = date.minusYears(1);
date =date.plusDays(1);
String startDate = fmt.print(date);
Workbook workbook = new Workbook();
workbook.open(new ByteArrayInputStream(template));
//Données des comptes annuels
EcfFiscalyearVersion version =
EJBLoader.getFiscalYearManager().getFinalVersionByFiscalYearId(fiscalYear.getFiscalyearId());
Map<String, EcfSectionAmount> resultsAmount =
EJBLoader.getFiscalYearManager().getSectionAmountsByVersion(version.getVersionId());
//Données sauvegardée depuis modèle
Map<String, Double> amounts =
EJBLoader.getTaxReturnManager().getAmountsByFiscalYearAndDocType(fiscalYear.getFiscalyearId(),
EcfDocumentType.TYPE_TAXRETURN_MODEL500);
// On préremplit le template
Worksheets sheets = workbook.getWorksheets();
String fiscalNumber = UtilString.removeSpaces(company.getFiscalNumber());
NamedRange[] ranges = sheets.getNamedRanges();
for (int i=0; i<ranges.length; i++) {
String cellname = ranges[i].getText();
if(cellname.equals(ITaxReturnVariables.ADMIN_OFFICE)){
//Admin Office
ranges[i].getCell(0,0).setValue(company.getAdminOffice());
}
for(int j=0; j<11;j++){
if(cellname.equals(ITaxReturnVariables.FISCALNUMBER+"_"+(j+1))){
//Fiscal Number
ranges[i].getCell(0,0).setValue(Character.toString(fiscalNumber.charAt(j)));
}
}
if(cellname.equals(ITaxReturnVariables.CPY_NAME) || cellname.equals(ITaxReturnVariables.CPY_NAME1) ){
//Company name
ranges[i].getCell(0,0).setValue(company.getCompanyName());
}
if(cellname.equals(ITaxReturnVariables.CPY_ADDR)){
//Company street
ranges[i].getCell(0,0).setValue(company.getStreet()+" “+company.getPostalCode()+” “+company.getCity());
}
if(cellname.equals(ITaxReturnVariables.CPY_STREET)){
//Company street
ranges[i].getCell(0,0).setValue(company.getStreet());
}
if(cellname.equals(ITaxReturnVariables.CPY_CITY)){
//Company city
ranges[i].getCell(0,0).setValue(company.getPostalCode()+” "+company.getCity());
}
if(cellname.equals(ITaxReturnVariables.FISCALYEAR_START)){
//startDate
ranges[i].getCell(0,0).setValue(startDate);
}
if(cellname.equals(ITaxReturnVariables.FISCALYEAR_END)){
//closingDate
ranges[i].getCell(0,0).setValue(endDate);
}
if(cellname.equals(ITaxReturnVariables.YEAR)){
//year
ranges[i].getCell(0,0).setValue(year);
}
if(documentType.equals(EcfDocumentType.TYPE_TAXRETURN_MODEL500)){
if(cellname.equals(ITaxReturnVariables.RESULT)){
if(version.getResultPandL()!=null && version.getResultPandL()!=0){
ranges[i].getCell(0,0).setValue(version.getResultPandL()<0?(-version.getResultPandL()):version.getResultPandL().doubleValue());
}
}if(cellname.equals(ITaxReturnVariables.SECTION_JETON)){
EcfSectionAmount jeton = resultsAmount.get(ITaxReturnVariables.SECTION_JETON);
if(jeton!=null && jeton.getSectionAmount()!=null && jeton.getSectionAmount()!=0){
ranges[i].getCell(0,0).setValue(jeton.getSectionAmount());
}
}if(cellname.equals(ITaxReturnVariables.SECTION_AMENDE)){
EcfSectionAmount amende = resultsAmount.get(ITaxReturnVariables.SECTION_AMENDE);
if(amende!=null && amende.getSectionAmount()!=null && amende.getSectionAmount()!=0){
ranges[i].getCell(0,0).setValue(amende.getSectionAmount());
}
}
if(cellname.equals(ITaxReturnVariables.SECTION_IMPOT_COLLECT)){
EcfSectionAmount impotc = resultsAmount.get(ITaxReturnVariables.SECTION_IMPOT_COLLECT);
if(impotc!=null && impotc.getSectionAmount()!=null && impotc.getSectionAmount()!=0){
ranges[i].getCell(0,0).setValue(impotc.getSectionAmount());
}
}
if(cellname.equals(ITaxReturnVariables.SECTION_IMPOT_CAP)){
EcfSectionAmount impotp = resultsAmount.get(ITaxReturnVariables.SECTION_IMPOT_CAP);
if(impotp!=null && impotp.getSectionAmount()!=null && impotp.getSectionAmount()!=0){
ranges[i].getCell(0,0).setValue(impotp.getSectionAmount());
}
}
if(cellname.equals(ITaxReturnVariables.SECTION_IMPOT_FORTUNE)){
EcfSectionAmount impotF = resultsAmount.get(ITaxReturnVariables.SECTION_IMPOT_FORTUNE);
if(impotF!=null && impotF.getSectionAmount()!=null && impotF.getSectionAmount()>0){
ranges[i].getCell(0,0).setValue(impotF.getSectionAmount());
}
}
if(cellname.equals(ITaxReturnVariables.SECTION_IMPOT_COM)){
EcfSectionAmount impotCo = resultsAmount.get(ITaxReturnVariables.SECTION_IMPOT_COM);
if(impotCo!=null &&
impotCo.getSectionAmount()!=null &&
impotCo.getSectionAmount()>0){
ranges[i].getCell(0,0).setValue(impotCo.getSectionAmount());
}
}
if(cellname.equals(ITaxReturnVariables.SECTION_INTERET_RETARD)){
EcfSectionAmount interet = resultsAmount.get(ITaxReturnVariables.SECTION_INTERET_RETARD);
if(interet!=null &&
interet.getSectionAmount()!=null &&
interet.getSectionAmount()>0){
ranges[i].getCell(0,0).setValue(interet.getSectionAmount());
}
}
if(cellname.equals(ITaxReturnVariables.SECTION_DON)){
EcfSectionAmount don = resultsAmount.get(ITaxReturnVariables.SECTION_DON);
if(don!=null && don.getSectionAmount()!=null && don.getSectionAmount()>0){
ranges[i].getCell(0,0).setValue(don.getSectionAmount());
}
}
if(cellname.equals(ITaxReturnVariables.SECTION_DIVIDENDE)){
EcfSectionAmount dividende = resultsAmount.get(ITaxReturnVariables.SECTION_DIVIDENDE);
if(dividende!=null &&
dividende.getSectionAmount()!=null &&
dividende.getSectionAmount()>0){
ranges[i].getCell(0,0).setValue(dividende.getSectionAmount());
}
}
//Données sauvergardées depuis le modèle
if(cellname.equals(ITaxReturnVariables.BENEFICE_TO_REPORT)){
if(amounts.containsKey(ITaxReturnVariables.BENEFICE_TO_REPORT)){
ranges[i].getCell(0,0).setValue(amounts.get(ITaxReturnVariables.BENEFICE_TO_REPORT));
}
}if(cellname.equals(ITaxReturnVariables.BENEF_EXPL)){
if(amounts.containsKey(ITaxReturnVariables.BENEF_EXPL)){
ranges[i].getCell(0,0).setValue(amounts.get(ITaxReturnVariables.BENEF_EXPL));
}
}if(cellname.equals(ITaxReturnVariables.BONIF_1)){
if(amounts.containsKey(ITaxReturnVariables.BONIF_1)){
ranges[i].getCell(0,0).setValue(amounts.get(ITaxReturnVariables.BONIF_1));
}
}if(cellname.equals(ITaxReturnVariables.BONIF_2)){
if(amounts.containsKey(ITaxReturnVariables.BONIF_2)){
ranges[i].getCell(0,0).setValue(amounts.get(ITaxReturnVariables.BONIF_2));
}
}if(cellname.equals(ITaxReturnVariables.BONIF_3)){
if(amounts.containsKey(ITaxReturnVariables.BONIF_3)){
ranges[i].getCell(0,0).setValue(amounts.get(ITaxReturnVariables.BONIF_3));
}
}if(cellname.equals(ITaxReturnVariables.BONIF_4)){
if(amounts.containsKey(ITaxReturnVariables.BONIF_4)){
ranges[i].getCell(0,0).setValue(amounts.get(ITaxReturnVariables.BONIF_4));
}
}if(cellname.equals(ITaxReturnVariables.BONIF_5)){
if(amounts.containsKey(ITaxReturnVariables.BONIF_5)){
ranges[i].getCell(0,0).setValue(amounts.get(ITaxReturnVariables.BONIF_5));
}
}if(cellname.equals(ITaxReturnVariables.BONIF_6)){
if(amounts.containsKey(ITaxReturnVariables.BONIF_6)){
ranges[i].getCell(0,0).setValue(amounts.get(ITaxReturnVariables.BONIF_6));
}
}
}
}
if(documentType.equals(EcfDocumentType.TYPE_TAXRETURN_MODEL500)){
fillLosses(sheets, company);
}
workbook.calculateFormula();
protectSheets(workbook);
workbook.save(byteout);
byteout.close();
}catch (Exception e) {
e.printStackTrace();
throw new ECFeDocsException(e);
}
return byteout.toByteArray();
}
then i do
public byte[] excelToPdf(byte[] excelDoc){
ByteArrayOutputStream byteoutpdf = new ByteArrayOutputStream();
Workbook workbook = new Workbook();
try {
workbook.open(new ByteArrayInputStream(excelDoc));
workbook.calculateFormula();
workbook.save(byteoutpdf, FileFormatType.PDF);
byteoutpdf.close();
} catch (IOException e) {
e.printStackTrace();
}
return byteoutpdf.toByteArray();
}
Hi,
Thanks for your source code and input / output files. We are looking into your said issue and we need some more information to evaluate it.
I'm using Aspose Cells to modify an Excel document. After that I save the document as PDF using
workbook.save(byteoutpdf, FileFormatType.PDF);
workbook.save(byteoutpdf, FileFormatType.PDF);
I looked into your source code, you are not setting any custom formatting in your provided code. That means your input file already has that formatting and you are only updating the cells values. I was unable to check Cell Formatting Option and Conditional Formatting Rules cause your input file is protected. Please provide password to Un-Protect the XLSX file.
but the problem is that my custom format defined for numbers on my excel document are not kept when transforming to Excel [Did you mean PDF?], the standard "," separator appears instead.
Can you point out the Cell in your XLSX file that doesn't match with PDF?
Moreover, you can try our latest/fix version Aspose.Cells JAVA v2.5.2.12 and post us your feedback.
Hi,
Of course, sorry the sheets are protected with ‘pwcecfprotect’. Indeed I am not setting any formatting, I just keep the formatting defined in the template file.
You can check the cell Z61 wich is filled with number 49664867487.
In the Excel file it is formatted as 49 664 867 487
In tehd PDF it’s formatted as 49,664,867,487 (see page 5)
Thanks.
Hi,
We have investigated your reported issue and found out that Cell Z61 in your input file [model500.xlsx] is already set to cell formatting as shown in your sent PDF file. Please see snapshot [Z61CustomFormat.PNG]. Through a simplest version of your source code, we converted the XLSX file to PDF and the results are as expected [the cell under discussion rendered in PDF as 49,664,867,487]. Moreover, we manually changed the cell formatting for Z61 to represent data as number. Again, the result is as expected, see [OutputNumberFormat.pdf (Page 2)].
Attached is an archive containing both PDF files and snapshots of cell formatting tabs displaying the current selected options for Z61.
JAVA Sample Code:
InputStream inputStream = new FileInputStream(“c:\model500-2.xlsx”);
Workbook workbookA = new Workbook();
workbookA.open(inputStream);
inputStream.close();
workbookA.calculateFormula();
OutputStream outputStream = new FileOutputStream(“C:\OutputNumberFormat.pdf”);
workbookA.save(outputStream, FileFormatType.PDF);
OK I’m sorry but I had the Excel options set as space used for separator (corporate parameter) so I suppose I have to change to cell format instead.
Thanks
Hi,
We have released another fix version of Aspose.Cells JAVA v2.5.2.14. You may test your scenario and share your feedback.