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,
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();
}
workbook.save(byteoutpdf, FileFormatType.PDF);
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,
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,