Some borders are missing while converting excel to pdf

[Dasgupta]

1. We are observing some borders are missing while converting excel to pdf.

2.

Code: Select all

public static String convertExcelToPdf(File inputExcelFile) throws IOException {

      // Set license key
      com.spire.license.LicenseProvider.setLicenseKey(LICENSE_KEY);

      logger.info("inputExcelFile created: " + inputExcelFile.getPath());

      File outputPdfFile = File.createTempFile("outputPdf", "." + "pdf");

      Workbook workbook = new Workbook();

      workbook.loadFromFile(inputExcelFile.getAbsolutePath());

      // Set worksheets to fit to page when converting
      workbook.getConverterSetting().setSheetFitToPage(true);

      // Get the first worksheet.
      Worksheet sheet = workbook.getWorksheets().get(0);

      // Get the PageSetup object of the first worksheet.
      PageSetup pageSetup = sheet.getPageSetup();

      // Set the page margins of bottom, left, right and top.
      pageSetup.setBottomMargin(2);
      pageSetup.setLeftMargin(1);
      pageSetup.setRightMargin(1);
      pageSetup.setTopMargin(3);

      // Set the margins of header and footer.
      pageSetup.setHeaderMarginInch(2);
      pageSetup.setFooterMarginInch(2);

      // Save the resulting document to a specified path
      workbook.saveToFile(outputPdfFile.getAbsolutePath());

      logger.info("outputPdfFile.getAbsolutePath() " + outputPdfFile.getAbsolutePath());

      return changePageSizeToA4(addPageNumberToPDF(outputPdfFile.getAbsolutePath()));
   }

3. Manifest-Version: 1.0
Extension-Name: spire.office
Implementation-Title: spire.office for java
Implementation-Version: 5.4.2
Implementation-Vendor: E-iceblue Co., Ltd.
Implementation-Vendor-Id: com.spire
Implementation-URL: https://www.e-iceblue.com


4. Application Type: Spingboot JDK 1.8

[Ula.wang]

Hi,

Thank you for your inquiry.
I tested the excel file and code you provided through Spire.Office for Java 5.4.2 and encountered the same problem as you. However, this issue has been fixed in the latest commercial version Spire.Office for Java 8.10.2 . Please download the latest commercial version through the following link and retest again.If you have any issue just feel free to contact us.

Download Link:https://www.e-iceblue.com/Download/office-for-java.html

Sincerely
Ula
E-iceblue support team

[Dasgupta]

Hello,

Thank you. With given version border issue is resolved, But size and alignment of sheet 2 & 3 from input excel is not good in output PDF.

Can you please guide here.

Thanks and regards,
Umesh Asodekar

[Ula.wang]

Hi,

Thank you for your more inquiry.
I downloaded the file you provided and opened the input excel through Microsoft Excel 365 , but I couldn't find sheet2&3 from your input excel. Please check if the input file you provided is correct. If your conversion code is different from the one provided earlier, please provide a conversion code that can reproduce your problem. Thank you in advance.

Sincerely,
Ula
E-iceblue support team

[Dasgupta]

Hello,

PFA,

Thanks and regards,
Umesh

[Ula.wang]

Hi,

Thank you for your inquiry.
I tested the excel document you provided and the code you provided for the first time, but I didn’t reproduce your problem with the latest version of Spire.Office for JAVA . My system environment is Win10, and I have attached a screenshot of my generation effect here. Please provide me with your system environment and code that can reproduce your problem to help us do further investigation, you can attach here. Thank you in advance.
Here is a screenshot of my test screen:

2023-11-09 180447.png (76.33 KiB) Viewed 11872 times

Sincerely,
Ula
E-iceblue support team

[Dasgupta]

Hello,

Please find requested details below.

OS Details:

OS Name: Microsoft Windows Server 2019 Standard
OS Version: 10.0.17763 N/A Build 17763
System Type: x64-based PC

Code:

Code: Select all

@PostMapping("/exceltopdf")
   public ResponseEntity<Resource> exceltopdf(@RequestParam("file") MultipartFile multipartFile,
         HttpServletRequest request) throws FileNotFoundException, IOException {
      logger.info("inside exceltopdf");

      File inputExcelFile = File.createTempFile("inputExcel", "." + "xls");
      try (FileOutputStream outputStream = new FileOutputStream(inputExcelFile)) {
         IOUtils.copy(multipartFile.getInputStream(), outputStream);
      }

      Resource outputResource = null;
      try {
         outputResource = fileStorageService.loadFileAsResource(convertExcelToPdf(inputExcelFile));
      } catch (Exception e) {
         logger.error(e.toString());
      }

      return ResponseEntity.ok().contentType(MediaType.parseMediaType("application/pdf"))
            .header(HttpHeaders.CONTENT_DISPOSITION,
                  "attachment; filename=\"" + outputResource.getFilename() + "\"")
            .body(outputResource);
   }

public static String convertExcelToPdf(File inputExcelFile) throws IOException {

      // Set license key
      com.spire.license.LicenseProvider.setLicenseKey(LICENSE_KEY);

      logger.info("inputExcelFile created: " + inputExcelFile.getPath());

      File outputPdfFile = File.createTempFile("outputPdf", "." + "pdf");

      Workbook workbook = new Workbook();

      workbook.loadFromFile(inputExcelFile.getAbsolutePath());

      // Set worksheets to fit to page when converting
      workbook.getConverterSetting().setSheetFitToPage(true);

      // Get the first worksheet.
      //Worksheet sheet = workbook.getWorksheets().get(0);

      WorksheetsCollection sheetsCollection = workbook.getWorksheets();
      
      for (int i = 0; i < sheetsCollection.getCount(); i++) {
         Worksheet sheet = sheetsCollection.get(i);
         
         // Get the PageSetup object of the first worksheet.
         PageSetup pageSetup = sheet.getPageSetup();

         // Set the page margins of bottom, left, right and top.
         pageSetup.setBottomMargin(2);
         pageSetup.setLeftMargin(1);
         pageSetup.setRightMargin(1);
         pageSetup.setTopMargin(3);

         // Set the margins of header and footer.
         pageSetup.setHeaderMarginInch(2);
         pageSetup.setFooterMarginInch(2);
   
      }

      // Save the resulting document to a specified path
      workbook.saveToFile(outputPdfFile.getAbsolutePath());

      logger.info("outputPdfFile.getAbsolutePath() " + outputPdfFile.getAbsolutePath());

      return changePageSizeToA4(addPageNumberToPDF(outputPdfFile.getAbsolutePath()));
   }



[Ula.wang]

Hi,

Thank you for your inquiry.
I have reproduced your question here. You want to convert excel file into an A4 sized pdf file while ensuring that the content isn’t deformed. The latest version of Spire.Xls for Java 13.11.0 can meet your requirements, you can refer to the following code:

Code: Select all

// Create a new Workbook object
Workbook workbook = new Workbook();

// Load the workbook from a file
workbook.loadFromFile("data/WSS 06th NOV Chapterwise.xlsx");

// Set the sheet to fit to page size
workbook.getConverterSetting().setSheetFitToPage(true);

// Get the first worksheet in the workbook
Worksheet sheet = workbook.getWorksheets().get(0);

// Get the PageSetup object for the worksheet
PageSetup pageSetup = sheet.getPageSetup();

// Set the bottom margin of the page setup
pageSetup.setBottomMargin(2);

// Set the left margin of the page setup
pageSetup.setLeftMargin(1);

// Set the right margin of the page setup
pageSetup.setRightMargin(1);

// Set the top margin of the page setup
pageSetup.setTopMargin(3);

// Set the header margin of the page setup in inches
pageSetup.setHeaderMarginInch(2);

// Set the footer margin of the page setup in inches
pageSetup.setFooterMarginInch(2);

// Save the workbook as a PDF file with version 2016
workbook.saveToFile("output1/1.pdf", FileFormat.Version2016);

but currently, the latest version of Spire.Office for Java isn't yet able to achieve this effect, and the next version of Spire.Office for Java is expected will implement this feature. Once Spire.Office for Java has implemented this feature, and I will notify you immediately.We deeply apologize for the inconvenience caused.
You can download Spire.Xls for Java 13.11.0 through this link:https://www.e-iceblue.com/Download/xls-for-java.html

Sincerely,
Ula
E-iceblue support team

[Dasgupta]

Hello,

How soon we can expect latest version of Spire Office which will have the said fix, As the fix for this issue is on priority for us.
Please let us know.

Thanks and regards,
Umesh Asodekar

[Ula.wang]

Hi,

Thank you for your feedback.
The latest version of Spire.Office for Java will be released in approximately two weeks. If it is successfully released, I will inform you in time. We deeply apologize for the inconvenience caused.

Sincerely,
Ula
E-iceblue support team

[Ula.wang]

Hi ,

Thanks for your patience!
Glad to inform you we just released Spire.Office for JAVA 8.11.3, which contains the fix of issue SPIREXLS-4906.
Please note that in the latest commercial version, the code circled in red in the following image must be placed before the page settings and the order cannot be reversed.

A4B72A7A-4E72-442b-8CA8-37E844824516.png (96.09 KiB) Viewed 11532 times

I put the complete code below for your reference：

Code: Select all

 
        // Create a new Workbook object
        Workbook workbook = new Workbook();

        // Load the workbook from an Excel file
        workbook.loadFromFile("data/WSS 06th NOV Chapterwise.xlsx");

        // Set the sheet fit to page setting for all sheets in the workbook
        workbook.getConverterSetting().setSheetFitToPage(true);

        // Set the sheet fit to page retain paper size setting for all sheets in the workbook
        workbook.getConverterSetting().setSheetFitToPageRetainPaperSize(true);

        // Get the collection of worksheets in the workbook
        WorksheetsCollection sheetsCollection = workbook.getWorksheets();

        // Iterate through each worksheet in the collection
        for (int i = 0; i < sheetsCollection.getCount(); i++) {
            // Get the current worksheet
            Worksheet sheet = sheetsCollection.get(i);

            // Get the page setup for the current worksheet
            PageSetup pageSetup = sheet.getPageSetup();

            // Set the paper size to A4
            pageSetup.setPaperSize(PaperSizeType.PaperA4);

            // Set the bottom margin to 2 inches
            pageSetup.setBottomMargin(2);

            // Set the left margin to 1 inch
            pageSetup.setLeftMargin(1);

            // Set the right margin to 1 inch
            pageSetup.setRightMargin(1);

            // Set the top margin to 3 inches
            pageSetup.setTopMargin(3);

            // Set the header margin to 2 inches
            pageSetup.setHeaderMarginInch(2);

            // Set the footer margin to 2 inches
            pageSetup.setFooterMarginInch(2);
        }
        // Save the workbook as a PDF file with the specified output path
        workbook.saveToFile("output3/3.pdf", FileFormat.PDF);

Please download the new version from the link below to test.

Download link:https://www.e-iceblue.com/Download/office-for-java.html

Sincerely,
Ula
E-iceblue support team

[Dasgupta]

Hello Team,

I tried above solution but it didnt worked for me. Please let me know what input do you need from my side.

Thanks and regards,
Umesh Asodekar

[William.Zhang]

Hello,

Thanks for your feedback.
I converted the Excel file("WSS 06th NOV Chapterwise.xlsx") you previously provided using the latest Spire.Office for Java Version:9.2.0, and the generated PDF has no formatting issues. Have you changed your input file? If so, please provide us with the latest documentation. If not, please upgrade to the latest version and refer to the code below for testing. If it still doesn't work, please provide us with your result file. Thank you in advance.

Code: Select all

// Create a new Workbook object
Workbook workbook = new Workbook();
// Load the workbook from an Excel file
workbook.loadFromFile("WSS 06th NOV Chapterwise.xlsx");
// Set the sheet fit to page setting for all sheets in the workbook
workbook.getConverterSetting().setSheetFitToPage(true);
// Set the sheet fit to page retain paper size setting for all sheets in the workbook
workbook.getConverterSetting().setSheetFitToPageRetainPaperSize(true);
// Save the workbook as a PDF file with the specified output path
workbook.saveToFile("res.pdf", FileFormat.PDF);

Sincerely,
William
E-iceblue support team

[Dasgupta]

Hello,

When I'm trying to use 9.2.0 version of spire office, I'm getting errors in imports.
PFA for screenshots and input file.

Thanks and regards,
Umesh Asodekar

[William.Zhang]

Hello,

Thanks for your feedback.
Kindly note that in the latest Spire.Office 9.2.0, various components (e.g Spire.Xls, Spire.Doc) have been separated from Spire.Office. If you want to use Spire.Xls, Spire.Doc, and Spire.Pdf simultaneously, please refer to the following way for importing. If you have any other questions, please feel free to write back.

Code: Select all

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.doc</artifactId>
        <version>12.3.1</version>
    </dependency>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.0</version>
    </dependency>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.pdf</artifactId>
        <version>10.3.1</version>
    </dependency>
</dependencies>


Sincerely,
William
E-iceblue support team