DEV Community: E-iceblue Product Family

Java Convert PDF to Excel and Excel to PDF

E-iceblue Product Family — Fri, 14 Jan 2022 09:42:10 +0000

In our daily lives, we use PDFs and Excels in a variety of ways. We know the data in PDFs can't be edited easily, whereas the data in an Excel spreadsheet can be freely edited. Occasionally, we don't want others to change the data; at other times, we need to edit the data in the files. In this article, I am going to show you how to convert PDF document to Excel and Excel to PDF by using Spire.Office for Java from the following four parts.

Convert each PDF page to a single Excel worksheet
Convert a Multi-Page PDF to One Excel Worksheet
Convert a Whole Excel File to PDF
Convert a Specific Worksheet to PDF

Install Spire.Office.jar

Spire.Office for Java supports to operate Word, Excel, PowerPoint and PDF files from Java. If you’re creating a Maven project, you can easily add the jar in your application using the following configurations. For non-Maven projects, download the jar file from this link and add it as a dependency in your application.

<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.office</artifactId>
        <version>4.12.2</version>
    </dependency>
</dependencies>

Convert each PDF page to a single Excel worksheet

The details steps to convert each PDF page to a single Excel worksheet.

Create a PdfDocument object.
Load a sample PDF file using PdfDocument.loadFromFile() method.
Save the PDF file to Excel using PdfDocument.saveToFile() method.

import com.spire.pdf.FileFormat;
import com.spire.pdf.PdfDocument;

public class PdftoExcel {
    public static void main(String[] args) throws Exception {

        //create a PdfDocument instance
        PdfDocument pdf = new PdfDocument();

        //Load a sample PDF file
        pdf.loadFromFile("Sample.pdf");

        //Save to Excel
        pdf.saveToFile("out/ToExcel.xlsx", FileFormat.XLSX);

        }
    }

Convert a Multi-Page PDF to One Excel Worksheet

The details steps to convert a PDF file containing 2 pages to one Excel worksheet.

Create a PdfDocument object.
Load a sample PDF file using PdfDocument.loadFromFile() method.
Set the PDF to XLSX conversion options to render multiple PDF pages on a single worksheet using PdfDocument.getConvertOptions().setPdfToXlsxOptions() method.
Save the PDF file to Excel using PdfDocument.saveToFile() method.

import com.spire.pdf.FileFormat;
import com.spire.pdf.PdfDocument;
import com.spire.pdf.conversion.XlsxLineLayoutOptions;


public class PdftoExcel {
    public static void main(String[] args) throws Exception {

        //create a PdfDocument instance
        PdfDocument pdf = new PdfDocument();
        //Load a sample PDF file
        pdf.loadFromFile("Sample.pdf");

        //Set the PDF to XLSX conversion options: rendering multiple pages on a single worksheet
        pdf.getConvertOptions().setPdfToXlsxOptions(new XlsxLineLayoutOptions(false,true,true));

        //Save to Excel
        pdf.saveToFile("out/ToOneSheet.xlsx", FileFormat.XLSX);

        }
    }

Convert a Whole Excel File to PDF

The details steps to convert an Excel Workbook with two worksheets to a PDF.

Create a Workbook object.
Load a sample Excel document using Workbook.loadFromFile() method.
Set the Excel to PDF conversion options through the methods under the ConverterSetting object, which is returned by Workbook.getConverterSetting() method.
Convert the whole Excel document to PDF using Workbook.saveToFile() method.

import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;

public class ExceltoPDF {
    public static void main(String[] args) throws Exception {

        //Create a Workbook instance and load an Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

        //Save the resulting document to a specified path
        workbook.saveToFile("out/ExcelToPdf.pdf", FileFormat.PDF);
        }
    }

Convert a Specific Worksheet to PDF

The details steps to convert a specific Excel worksheet to a PDF.

Create a Workbook object and load a sample Excel document using Workbook.loadFromFile() method.
Set the Excel to PDF conversion options through the methods under the ConverterSetting object, which is returned by Workbook.getConverterSetting() method.
Get a specific worksheet using Workbook.getWorksheets().get() method.
Convert the worksheet to PDF using Worksheet.saveToPdf() method.

import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ExceltoPDF {
    public static void main(String[] args) throws Exception {

        //Create a Workbook instance and load an Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

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

        //Save the resulting document to a specified path
        worksheet.saveToPdf("out/WorksheetToPdf.pdf");

        }
    }

Convert Excel to CSV and Vice Versa in Java

E-iceblue Product Family — Tue, 11 Jan 2022 04:02:27 +0000

CSV files are delimited text files in which values are separated by commas. They can be used with most spreadsheet programs, such as Microsoft Excel and Google Spreadsheets. Sometimes, you might need to do conversions between Excel and CSV. In this article, I will demonstrate how to convert Excel to CSV and CSV to Excel in Java using Spire.XLS for Java library.

Add Dependencies

To begin with, you need to add needed dependencies for including Spire.XLS for Java library into your Java project.
You can either download the library’s jar from the official website or install it from Maven by adding the following code to your maven-based project’s pom.xml file.

<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.xls</artifactId>    
        <version>4.12.2</version>    
    </dependency>    
</dependencies>

Convert Excel to CSV using Java

You can convert an Excel worksheet to CSV or convert multiple worksheets to separate CSV files as per your requirement.

The following are the steps to convert an Excel worksheet to CSV:

Create an instance of Workbook class.
Load an Excel file using Workbook.loadFromFile() method.
Get the desired worksheet by its index using Workbook.getWorksheets().get(index) method.
Save the worksheet as CSV using XlsWorksheet.saveToFile(String fileName, String separator, Charset encoding) method. You may also choose one of the other two overloaded methods below:

a. saveToFile(String fileName, String separator)

b. saveToFile(String fileName, String separator, boolean retainHiddenData)

import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

import java.nio.charset.Charset;

public class ConvertAWorksheetToCsv {
    public static void main(String []args){
        //Create an instance of Workbook class
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

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

        //Save the worksheet as CSV
        sheet.saveToFile("ExcelToCSV.csv", ",", Charset.forName("UTF-8"));
    }
}

The above code saves only the first worksheet in the Excel file as CSV. If you want to save multiple worksheets in the Excel file as separate CSV files, use the following code.

import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

import java.nio.charset.Charset;

public class ConvertMultipleWorksheetsToCsv {
    public static void main(String []args) {
        //Create an instance of Workbook class
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

        //Loop through the worksheets in the Excel file
        for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
            Worksheet sheet = workbook.getWorksheets().get(i);
            //Save each worksheet as a separate CSV
            sheet.saveToFile("Output/ExcelToCSV_" + i + ".csv", ",", Charset.forName("UTF-8"));
        }
    }
}

Convert CSV to Excel using Java

The following are the steps to convert a CSV to Excel:

Create an instance of Workbook class.
Load a CSV file using Workbook.loadFromFile(String fileName, String separator, int row, int column) method.
Loop through the worksheets in the CSV file.
Within the loop, access the used range of the current worksheet using Worksheet.getAllocatedRange() method. Then set ignore error option using XlsRange.setIgnoreErrorOptions() method. After that, autofit columns and rows using XlsRange.autoFitColumns() and XlsRange.autoFitRows() method.
Save the CSV to Excel using Workbook.saveToFile(string fileName, ExcelVersion version) method.

import com.spire.xls.*;

import java.util.EnumSet;

public class ConvertCsvToExcel {
    public static void main(String []args) {
        //Create an instance of Workbook class
        Workbook workbook = new Workbook();

        //Load a CSV file
        workbook.loadFromFile("ExcelToCSV.csv", ",", 1, 1);

        //Loop through the worksheets in the CSV file
        for (int i = 0; i < workbook.getWorksheets().getCount(); i++)
        {
            Worksheet sheet = workbook.getWorksheets().get(i);
            //Access the used range in each worksheet
            CellRange usedRange = sheet.getAllocatedRange();
            //Ignore errors when saving numbers in the used range with text
            usedRange.setIgnoreErrorOptions(EnumSet.of(IgnoreErrorType.NumberAsText));
            //Autofit columns and rows
            usedRange.autoFitColumns();
            usedRange.autoFitRows();
        }

        //Save the result file
        workbook.saveToFile("CSVToExcel.xlsx", ExcelVersion.Version2013);
    }
}

See More

Product Page | Documentation | Forum |

How to Increase or Decrease Margins of an Existing PDF in Java

E-iceblue Product Family — Thu, 06 Jan 2022 12:14:19 +0000

Margins refer to the blank area around a PDF page. In certain cases, you may need to adjust the margins of your PDF documents. For example, before merging multiple PDF documents into a single PDF, you may want to change the margins so that all documents have the same page size. This article will show you how to increase or decrease margins of an existing PDF document using Spire.PDF for Java.

Installing Spire.Pdf.jar

If you use Maven, you can easily import the Spire.Pdf.jar in your application by adding the following code to your project’s pom.xml file. For non-Maven projects, download the jar file from this link and manually add it as a dependency in your application.

<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.pdf</artifactId>
        <verson>4.11.2</version>
    </dependency>
</dependencies>

Example 1. Increase Margins of an Existing PDF

To enlarge the margin of a PDF document, the basic thought is to create a new PDF that has bigger page size and then draw the original page on the big page at a proper position. The following code snippet will show you how to turn ideas into reality step by step.

import com.spire.pdf.FileFormat;
import com.spire.pdf.PdfDocument;
import com.spire.pdf.PdfPageBase;
import com.spire.pdf.graphics.PdfMargins;
import com.spire.pdf.graphics.PdfTemplate;

import java.awt.*;
import java.awt.geom.Dimension2D;
import java.awt.geom.Point2D;

public class IncreaseMargins {

    public static void main(String[] args) {

        //Load the original PDF document
        PdfDocument originalPdf = new PdfDocument("C:\\Users\\Administrator\\Desktop\\Table.pdf");

        //Get first page
        PdfPageBase firstPage = originalPdf.getPages().get(0);

        //Create a new PdfDocument object
        PdfDocument newPdf = new PdfDocument();

        //Set increasing value of the page margin
        PdfMargins margins = newPdf.getPageSettings().getMargins();
        margins.setTop(40);
        margins.setBottom(40);
        margins.setLeft(40);
        margins.setRight(40);

        //Set the page size of the new PDF document
        Dimension2D dimension2D = new Dimension();
        dimension2D.setSize(firstPage.getSize().getWidth() + margins.getLeft()+margins.getRight(), firstPage.getSize().getHeight() + margins.getTop()+margins.getBottom());
        newPdf.getPageSettings().setSize(dimension2D);

        //Loop through the pages in the original document
        for (int i = 0; i < originalPdf.getPages().getCount(); i++) {

            //Create template based on the source page
            PdfTemplate template = originalPdf.getPages().get(i).createTemplate();

            //Add a page to the new PDF
            PdfPageBase page = newPdf.getPages().add(dimension2D);

            //Draw template on the page
            template.draw(page.getCanvas(), new Point2D.Float(0, 0));
        }

        //Save the new document to file
        newPdf.saveToFile("output/IncreaseMargins.pdf", FileFormat.PDF);
    }
}

Example 2. Decrease Margins of an Existing PDF

Likewise, the way to decrease the margins of a PDF is to create a new PDF that has smaller page size and then draw the source page on the small page at a specified coordinate. It’s worth mentioning that the decreasing value on the left, right, top or bottom direction should be less than the existing margins. Otherwise, the content area might be cut off while decreasing the margins.

import com.spire.pdf.FileFormat;
import com.spire.pdf.PdfDocument;
import com.spire.pdf.PdfPageBase;
import com.spire.pdf.graphics.PdfMargins;
import com.spire.pdf.graphics.PdfTemplate;

import java.awt.*;
import java.awt.geom.Dimension2D;
import java.awt.geom.Point2D;

public class DecreaseMargins {
    public static void main(String[] args) {

        //Load the original PDF document
        PdfDocument originalPdf = new PdfDocument("C:\\Users\\Administrator\\Desktop\\Table.pdf");

        //Get the first page
        PdfPageBase firstPage = originalPdf.getPages().get(0);

        //Create a new PdfDocument object
        PdfDocument newPdf = new PdfDocument();

        //Set decreasing value
        double left = 20;
        double right = 20;
        double top = 20;
        double bottom = 20;

        //Set the page size of the new PDF document
        Dimension2D dimension2D = new Dimension();
        dimension2D.setSize(originalPdf.getPages().get(0).getSize().getWidth() - left - right , originalPdf.getPages().get(0).getSize().getHeight() - top -bottom);

        //Loop through the pages in the original document
        for (int i = 0; i < originalPdf.getPages().getCount(); i++) {

            //Create template based on the source page
            PdfTemplate template = originalPdf.getPages().get(i).createTemplate();

            //Add a page to the new PDF
            PdfPageBase page = newPdf.getPages().add(dimension2D, new PdfMargins(0));

            //Draw template on the page
            template.draw(page.getCanvas(), new Point2D.Float(-20, -20));
        }

        //Save the new document to file
        newPdf.saveToFile("output/DecreaseMargins.pdf", FileFormat.PDF);
    }
}

Java Hide and Show Rows or Columns in Excel

E-iceblue Product Family — Fri, 31 Dec 2021 02:22:58 +0000

With the help of Spire.XLS for Java, we can easily hide and show the rows or columns in the Excel worksheets programmatically. In this article, we will demonstrate how to hide and show rows or columns in Excel in Java applications from the following four parts.

Hide Excel Rows and Columns
Show Hidden Rows and Columns in Excel
Hide Multiple Rows and Columns
Show All Hidden Rows and Columns

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<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.xls</artifactId>
        <version>4.12.2</version>
    </dependency>
</dependencies>

Hide Rows and Columns

Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
Get a specified worksheet using Workbook.getWorksheets().get() method.
Hide the column using Worksheet.hideColumn(int columnIndex)method.
Hide the row using worksheet.hideRow(int rowIndex) method. Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;


public class HideRowsColumns {
    public static void main(String[] args) throws Exception {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("Sample.xlsx ");

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

        //Hide the third column
        sheet.hideColumn(3);

        //Hide the third row
        sheet.hideRow(3);

        //Save the document
        wb.saveToFile("HideRowsColumns.xlsx", ExcelVersion.Version2016);
    }
}

Show Hidden Rows and Columns

Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
Get a specified worksheet using Workbook.getWorksheets().get() method.
Show the column using Worksheet.showColumn(int columnIndex)method.
Show the row using worksheet.showRow(int rowIndex) method.
Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;


public class ShowRowsColumns {
    public static void main(String[] args) throws Exception {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("HideRowsColumns.xlsx ");

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

        //Unhide the third column
        sheet.showColumn(3);

        //Unhide the third row
        sheet.showRow(3);

        //Save the document
        wb.saveToFile("ShowRowsColumns.xlsx", ExcelVersion.Version2016);
    }
}

Hide Multiple Rows and Columns

Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
Get a specified worksheet using Workbook.getWorksheets().get() method.
Hide multiple columns using Worksheet.hideColumns(int columnIndex, int columnCount)method.
Hide multiple rows using worksheet.hideRows(int rowIndex, int rowCount) method.
Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;


public class HideMultiRowsColumns {
    public static void main(String[] args) throws Exception {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("Sample.xlsx ");

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

        //Hide multiple columns
        sheet.hideColumns(2,2);

        //Hide multiple rows
        sheet.hideRows(3,3);

        //Save the document
        wb.saveToFile("HideMultiRowsColumns.xlsx", ExcelVersion.Version2016);
    }
}

Show Multiple Rows and Columns

Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
Get a specified worksheet using Workbook.getWorksheets().get() method.
Loop through the rows and find the hidden rows using Worksheet.getRowIsHide() method.
Show all hidden rows using Worksheet.showRow(i) method.
Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;


public class ShowMultiRowsColumns {
    public static void main(String[] args) throws Exception {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("HideMultiRowsColumns.xlsx");

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

        //Traverse all the rows 
        for (int i = 1; i <= sheet.getLastRow(); i++) {

                //detect if the row is hidden
                if (sheet.getRowIsHide(i)) {

                    //Show the hidden rows
                    sheet.showRow(i);
                }
            }

        //Traverse the columns and show all the hidden columns
        for (int j = 1; j <= sheet.getLastColumn(); j++) {
            if (sheet.getColumnIsHide(j)) {
                sheet.showColumn(j);
            }

            //Save the document
            wb.saveToFile("ShowMultiRowsColumns.xlsx", ExcelVersion.Version2016);
        }
    }
}

Java Delete Blank Rows and Columns in Excel

E-iceblue Product Family — Tue, 28 Dec 2021 08:53:27 +0000

In this article, we will demonstrate how to delete blank rows and columns in Excel in Java applications by using Spire.XLS for Java. We can detect if the rows and columns is blank or not using the method Worksheet.getRows().isBlank() and Worksheet.getColumns().isBlank(). After that we can easily remove rows and columns from Excel worksheets programmatically using the method Worksheet.deleteRow(int) and Worksheet.deleteColumn(int).

Install Spire.XLS for Java

<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.xls</artifactId>
        <version>4.12.2</version>
    </dependency>
</dependencies>

Delete Blank Rows in Excel using Java

The following are the steps to remove the blank rows in Excel:

Create a Workbook object and load the sample Excel document using Workbook.loadFromFile() method.
Get the desired worksheet using Workbook.getWorksheets().get() method.
Traverse the rows and determine if the rows is blank using Worksheet.getRows().isBlank() method.
Delete the blank rows using Worksheet.deleteRow(int) method.
Save the document to another file using Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;


public class RemoveBlankRows {
    public static void main(String[] args) throws Exception {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("Sample.xlsx ");

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

        //Loop through the rows
        for (int i = sheet.getLastRow(); i >= 1; i--)
        {
            //Detect if a row is blank
            if (sheet.getRows()[i-1].isBlank())
            {
                //Remove blank row
                sheet.deleteRow(i);
            }
        }


        //Save the document
        wb.saveToFile("DeleteBlankRowsAndColumns.xlsx", ExcelVersion.Version2016);


    }
}

Delete Blank Columns in Excel using Java

The following are the steps to remove the blank columns in Excel.

Create a Workbook object and load the sample Excel document using Workbook.loadFromFile() method.
Get the desired worksheet using Workbook.getWorksheets().get() method.
Traverse the columns and determine if the columns is blank using Worksheet.getColumns().isBlank() method.
Delete the blank rows using Worksheet.deleteColumn(int) method.
Save the document to another file using Workbook.saveToFile() method.


import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;


public class RemoveBlankColumns {
    public static void main(String[] args) throws Exception {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("Sample01.xlsx ");

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

        //Loop through the columns
        for (int i = sheet.getLastColumn(); i >= 1; i--)
        {
            //Detect if a column is blank
            if (sheet.getColumns()[i-1].isBlank())
            {
                //Remove blank column
                sheet.deleteColumn(i);
            }
        }

        //Save the document
        wb.saveToFile("DeleteBlankColumns.xlsx", ExcelVersion.Version2016);
    }
}

Group or Ungroup Rows and Columns in Excel in Java

E-iceblue Product Family — Wed, 22 Dec 2021 07:42:38 +0000

In Microsoft Excel, rows and columns can be grouped or ungrouped to analyze and summarize data. In this article, we will demonstrate how to group or ungroup rows and columns in Excel in Java using Spire.XLS for Java library.

Contents Summary:

Group Rows and Columns
Add Subtotals to a Range
Ungroup Rows and Columns

Add Dependencies

<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.xls</artifactId>    
        <version>4.12.2</version>    
    </dependency>    
</dependencies>

Group Rows and Columns

You can group rows and columns by using the groupByRows and groupByColumns methods of XlsWorksheet class. Both of the methods accept the following parameters:

int: the first row/column index to be grouped.
int: the last row/column index to be grouped.
boolean: indicates whether to show/hide the grouped data.

Note: You need to make sure that each column of the data that you want to group has a label in the first row (e.g., Region), contains similar facts in each column, and the range that you want to group has no blank rows or columns.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class GroupRowsAndColumns {
    public static void main(String []args){

        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Report.xlsx");

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

        //Group specific rows
        sheet.groupByRows(2, 6, false);
       //Group specific columns
        sheet.groupByColumns(1, 3, false);

        //Save the result file
        workbook.saveToFile("GroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Output:

Add Subtotals to a Range

You can insert subtotals and totals to quickly calculate rows of related data in a range by using the subtotal method of XlsWorksheet class. The method accepts the following parameters:

IXLSRange: the specific cell range.
int: the column index (zero-based) on which you wish to group data.
int[]: the list of column indexes (zero-based) on which the subtotals should be calculated.
SubtotalTypes: the function that you want to use to calculate the subtotals.
boolean: Indicates whether to replace existing subtotals.
boolean: Indicates whether to insert page breaks between groups.
boolean: Indicates whether to add summary rows below each group of detail rows.

import com.spire.xls.*;

public class AddSubtotalsToRange {
    public static void main(String []args){

        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Report.xlsx");

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

        //Specify the range that you wish to subtotal
        CellRange range = sheet.getCellRange("A2:C11");

        //Add subtotal to the range
        sheet.subtotal(range, 0, new int[] { 2 }, SubtotalTypes.Sum, true, false, true);

        //Save the result file
        workbook.saveToFile("AddSubtotal.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Output:

Ungroup Rows and Columns

To ungroup rows and columns, you need to use the ungroupByRows and ungroupByColumns methods of XlsWorksheet class. Both of the methods accept the following parameters:

int: the first row/column index to be ungrouped.
int: the last row/column index to be ungrouped.

import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class UngroupRowsAndColumns {
    public static void main(String []args){

        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("GroupRowsAndColumns.xlsx");

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

        //Ungroup specific rows
        sheet.ungroupByRows(2, 6);
       //Ungroup specific columns
        sheet.ungroupByColumns(1, 3);

        //Save the result file
        workbook.saveToFile("UnGroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Output:

How to Indent a Paragraph in Word in Java

E-iceblue Product Family — Wed, 22 Dec 2021 04:00:25 +0000

In this article, we will demonstrate how to indent paragraphs in Word in Java applications using Spire.Doc for Java. Spire.Doc for Java offers ParagraphFormat.setLeftIndent(float value) method to set the paragraph left indents and ParagraphFormat.setRightIndent(float value) for right indents. The ParagraphFormat.setFirstLineIndent(float value) method can be used to set both hanging and first line indents. Negative value for this property is for the hanging indent and positive value for the first line indent of the paragraph.

Install Spire.Doc for Java

First of all, you're required to add the Spire.Doc.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<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>4.12.7</version>
    </dependency>
</dependencies>

Using the code

The following are the steps to set paragraph indents in Word:

Create a Document instance.
Load a Word document using Document.loadFromFile() method.
Get the desired section by its index using Document.getSections.get() method.
Get the desired paragraph by index using Section.getParagraphs.get() method.
Get the ParagraphFormat object using Paragraph.getFormat() method.
Call the methods to set paragraph indents with the object.
Save the result document using Document.saveToFile() method.


import com.spire.doc.Document;
import com.spire.doc.FileFormat;
import com.spire.doc.Section;
import com.spire.doc.documents.Paragraph;
import com.spire.doc.formatting.ParagraphFormat;

public class Test {
    public static void main(String[] args) throws Exception {
        //Create a Document instance
        Document document= new Document();
        //Load a Word document
        document.loadFromFile("Sample.docx");

        //Get the first section
        Section section = document.getSections().get(0);

        //Get the first paragraph and set left indent, right indent
        Paragraph para = section.getParagraphs().get(0);
        ParagraphFormat format = para.getFormat();
        format.setLeftIndent(30);
        format.setRightIndent(50);

        //Get the second paragraph and set first line indent
        para = section.getParagraphs().get(1);
        format = para.getFormat();
        format.setFirstLineIndent(30);

        //Get the third paragraph and set hanging indent
        para = section.getParagraphs().get(2);
        format = para.getFormat();
        format.setFirstLineIndent(-30);

        //Save the result document
        document.saveToFile("SetParagraphIndents.docx", FileFormat.Docx_2013);
    }
}

Replace Fonts in PDF in Java

E-iceblue Product Family — Fri, 17 Dec 2021 09:02:11 +0000

One of the key factors in making successful documents is the use of appropriate fonts. For example, we usually apply different fonts for headings and paragraphs in order to achieve differentiation and a visually pleasing result. When you get a PDF document in which the fonts are not properly used, you may want to change the fonts being used.

In this article, I am going to introduce how to programmatically replace the used fonts in a PDF document by using Spire.PDF for Java.

Installing Spire.Pdf.jar

<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.pdf</artifactId>
        <verson>4.11.2</version>
    </dependency>
</dependencies>

Example 1. Replace All Fonts with Another Font

Spire.PDF for Java offers PdfDocument.getUsedFonts() to get the collection of fonts being used in the document. Loop through the fonts in the collection, and replace each of them with another font using PdfUsedFont.replace() method.

import com.spire.pdf.PdfDocument;
import com.spire.pdf.graphics.PdfFont;
import com.spire.pdf.graphics.PdfFontFamily;
import com.spire.pdf.graphics.PdfFontStyle;
import com.spire.pdf.graphics.fonts.PdfUsedFont;

public class ReplaceAllFonts {

    public static void main(String[] args) throws Exception {

        //Create a PdfDocument object
        PdfDocument doc = new PdfDocument();

        //Load a sample PDF file
        doc.loadFromFile("C:\\Users\\Administrator\\Desktop\\Fonts.pdf");

        //Get all used fonts in the document
        PdfUsedFont[] fonts = doc.getUsedFonts();

        //Create a font 
        PdfFont newfont = new PdfFont(PdfFontFamily.Times_Roman, 12f, PdfFontStyle.Italic);

        //Travers all fonts used in the document
        for (PdfUsedFont font: fonts
             ) {

            //Replace the current font with a new font
            font.replace(newfont);

        }

        //Save the changes to a different PDF file
        doc.saveToFile("output/ReplaceAllFonts.pdf");
    }
}

Example 2. Replace a Specific Font with a Different Font

After a certain font is obtained from the document, you can get the font name by PdfUsedFont.getName() method. If the font is exactly what you want to replace, then you can replace it with a different font.

import com.spire.pdf.PdfDocument;
import com.spire.pdf.graphics.PdfFont;
import com.spire.pdf.graphics.PdfFontFamily;
import com.spire.pdf.graphics.PdfFontStyle;
import com.spire.pdf.graphics.fonts.PdfUsedFont;

public class ReplaceSpecificFontWithNewFont {

    public static void main(String[] args) throws Exception {

        //Create a PdfDocument object
        PdfDocument doc = new PdfDocument();

        //Load a sample PDF file
        doc.loadFromFile("C:\\Users\\Administrator\\Desktop\\Fonts.pdf");

        //Get all used fonts in the document
        PdfUsedFont[] fonts = doc.getUsedFonts();

        //Create a font
        PdfFont newfont = new PdfFont(PdfFontFamily.Times_Roman, 12f, PdfFontStyle.Italic);

        //Travers all fonts used in the document
        for (PdfUsedFont font: fonts
        ) {

            //Determine if a font is "Calibri"
            if(font.getName().equals("Calibri")){

                //Replace the font "Calibri" with a new font
                font.replace(newfont);
            }

        }

        //Save the changes to a different PDF file
        doc.saveToFile("output/ReplaceSpecificFont.pdf");
    }
}

Copy Contents from One Word Document to Another in Java

E-iceblue Product Family — Fri, 10 Dec 2021 09:54:13 +0000

In some cases, you might need to copy partial or all contents from one Word document to another. In this article, I will demonstrate how to copy contents from one Word document to another, along with how to create a copy of a Word document programmatically in Java.

Contents Summary:

Copy Partial Contents from One Word document to Another
Copy All Contents from One Word Document to Another
Create a Copy of a Word document

Add Dependencies

In order to copy content from Word document, I will be using Free Spire.Doc for Java which is a free, multifunctional and easy-to-use API.
You can either download the jar of the library from the official website or install it from maven by adding the following code to your maven-based project:

<repositories>    
    <repository>    
        <id>com.e-iceblue</id>    
        <name>e-iceblue</name>    
        <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>    
    </repository>    
</repositories>    
<dependencies>    
    <dependency>    
        <groupId> e-iceblue </groupId>    
        <artifactId>spire.doc.free</artifactId>    
        <version>3.9.0</version>    
    </dependency>    
</dependencies>

Copy Partial Contents from One Word document to Another

You can copy partial contents, for example, a section or a paragraph from one Word document to another.

To copy a section from one document to another, you can use the Section.deepClone() method and the Document.getSections().add() method.

import com.spire.doc.Document;
import com.spire.doc.FileFormat;
import com.spire.doc.Section;
import com.spire.doc.documents.SectionBreakType;

public class CopySection {
    public static void main(String []args){

        //Load the source document
        Document sourceDoc = new Document();
        sourceDoc.loadFromFile("source.docx");

        //Get the first section
        Section sourceSection = sourceDoc.getSections().get(0);

        //If you want to add the section to the same page instead of a new page in the destination document, you need to set the section break type as no break.
        sourceSection.setBreakCode(SectionBreakType.No_Break);

        //Load the destination document
        Document destDoc = new Document();
        destDoc.loadFromFile("destination.docx");

        //Copy the first section from the source document to the destination document
        Section copySection = sourceSection.deepClone();
        destDoc.getSections().add(copySection);

        //Save the result document
        destDoc.saveToFile("CopyASection.docx", FileFormat.Docx_2013);
    }
}

If you need to copy a paragraph, you can use the Paragraph.deepClone() and the Section.getParagraphs().add() method.

import com.spire.doc.Document;
import com.spire.doc.FileFormat;
import com.spire.doc.Section;
import com.spire.doc.documents.Paragraph;
import com.spire.doc.interfaces.IParagraph;

public class CopyParagraph {
    public static void main(String []args){

        //Load the source document
        Document sourceDoc = new Document();
        sourceDoc.loadFromFile("source.docx");

        //Get the first section
        Section section1 = sourceDoc.getSections().get(0);
        //Get the first paragraph
        Paragraph para1 = section1.getParagraphs().get(0);

        //Load the destination document
        Document destDoc = new Document();
        destDoc.loadFromFile("destination.docx");

        //Get the last section of the destination document
        Section section2 = destDoc.getLastSection();

        //Copy the first paragraph from the source document to the destination document
        DocumentObject object = para1.deepClone();
        section2.getParagraphs().add((IParagraph) object);

        //Save the result document
        destDoc.saveToFile("CopyAParagraph.docx", FileFormat.Docx_2013);
    }
}

Copy All Contents from One Word Document to Another

You can use the Document.importContent() method to copy all contents from one Word document to another as shown in the following example.

import com.spire.doc.Document;
import com.spire.doc.FileFormat;

public class CopyAllContents {
    public static void main(String []args){
        //Load the source document
        Document sourceDoc = new Document();
        sourceDoc.loadFromFile("source.docx");

        //Load the destination document
        Document destDoc = new Document();
        destDoc.loadFromFile("destination.docx");

        //Copy contents from the source document to the destination document
        destDoc.importContent(sourceDoc, true);

        //Save the result document
        destDoc.saveToFile("CopyAll.docx", FileFormat.Docx_2010);
    }
}

Create a Copy of a Word document

To create a copy of a Word document, you can call the Document.deepClone() method.

import com.spire.doc.Document;
import com.spire.doc.FileFormat;

public class CloneADocument {
    public static void main(String []args){

        //Create a Document instance
        Document document = new Document();

        //Load a Word document
        document.loadFromFile("source.docx");

        //Clone the word document
        Document newDoc = document.deepClone();

        //Save the result file
        newDoc.saveToFile("Clone.docx", FileFormat.Docx_2013);
    }
}

Remove Blank Pages from PDF in Java

E-iceblue Product Family — Wed, 08 Dec 2021 07:07:02 +0000

Introduction

In some circumstances, you may receive a PDF document that contains empty or blank pages inside it. Before you redeliver it to your colleagues or clients, you’d better delete all blank pages in the PDF. In this article, you will learn how to programmatically remove blank pages from PDF by using Spire.PDF for Java.

Prerequisite Knowledge

There are two kinds of blank pages: the first is the completely empty page which has nothing inside; the second is the page that looks blank but actually contains spaces or blank images. Spire.PDF offers the PdfPageBase.isBlank() method to detect whether a page is absolutely blank (empty) or not. For the pages that meet the second situation, we convert them into images and determine if the converted images are blank. If yes, these PDF pages are also considered blank.

What you need to pay special attention to is that the converted images have watermark, which will affect the judgment results. Therefore, a temporary license that removes the evaluation message is required in this scenario. Free free to request one from sales@e-iceblue.com, and obtain the license key from it. The license key will be applied in your program this way.

com.spire.license.LicenseProvider.setLicenseKey("your license key");

Once you get the blanks pages from a document, you can remove them one by one using PdfDocument.getPages().removeAt(int pageIndex) method.

Installing Spire.Pdf.jar

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId> e-iceblue</groupId>
        <artifactId>spire.pdf</artifactId>
        <verson>4.11.2</version>
    </dependency>
</dependencies>

Using the code

The following are the steps to remove blank pages from a PDF document.

Apply your temporary license by license key in your Java program.
Create a PdfDocument object, and load a sample PDF file using PdfDocument.loadFromFile() method.
Traverse all the pages in the document.
Judge if a certain page is absolutely blank using PdfPageBase.isBlank() method. If not, convert the page into an image and judge if the image is blank using the custom method isImageBlank().
After a blank page is found, use PdfDocument.getPages().removeAt() method to delete it.
Save the changes to another file using PdfDocument.saveToFile() method.

import com.spire.pdf.*;
import java.awt.*;
import java.awt.image.*;
import static com.spire.pdf.graphics.PdfImageType.Bitmap;

public class RemoveBlankPages {

    public static void main(String[] args) {

        //Register the license key
        com.spire.license.LicenseProvider.setLicenseKey("your license key");

        //Create a PdfDocument object
        PdfDocument pdf = new PdfDocument();

        //Load a sample PDF file
        pdf.loadFromFile("C:\\Users\\Administrator\\Desktop\\blank.pdf");

        //Traverse all the pages
        for (int i = pdf.getPages().getCount() - 1; i >= 0; i--) {

            //Determine if a page is absolutely blank (empty)
            if (pdf.getPages().get(i).isBlank()) {

                //Remove the blank page
                pdf.getPages().removeAt(i);
            }
            else{

                //Convert the PDF page to image
                BufferedImage image = pdf.saveAsImage(i, Bitmap);

                //Determine whether the image is blank or not
                if (isImageBlank(image)) {

                    //Delete the corresponding PDF page if the image is blank
                    pdf.getPages().removeAt(i);
                }
            }

            //Save the changes to another PDF file
            pdf.saveToFile("output/RemoveBlankPages.pdf");
        }
    }

    //Determine if an image is blank
    public static boolean isImageBlank (BufferedImage image)
    {
        for (int i = 0; i < image.getWidth(); i++) {
            for (int j = 0; j < image.getHeight(); j++) {
                int pixel = image.getRGB(i, j);
                Color c = new Color(pixel);
                if (c.getRed() < 240 || c.getGreen() < 240 || c.getBlue() < 240) {
                    return false;
                }
            }
        }
        return true;
    }
}

Output

Check if an Excel File is Digitally Signed and Verify Digital Signatures in Excel in Java

E-iceblue Product Family — Thu, 02 Dec 2021 09:36:00 +0000

Introduction

When working with Excel files, developers may encounter situations where they need to separate signed files from non-signed files, so that they can send the signed ones to a method to perform further manipulations, for example, verifying the signatures or reading other signature properties like the time of signing and the certificates used to create the signatures.

For such cases, this article will demonstrate how to check if an Excel file is digitally signed and verify digital signatures in Excel in Java using Spire.XLS for Java library.

Add Dependencies

To begin with, you need to add needed dependencies for including Spire.XLS for Java library into your Java project. You can either download the library’s jar from the official website or install it from Maven by adding the following code to your maven-based project’s pom.xml file.

<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.xls</artifactId>    
        <version>4.11.3</version>    
    </dependency>    
</dependencies>

Check and Verify Digital Signatures

The following are the main steps to check and verify digital signatures in an Excel file:

Create a Workbook instance.
Load an Excel file using Workbook.loadFromFile() method.
Check if the file is digitally signed using Workbook.isDigitallySigned() method.
If the file is signed, then get the signatures using Workbook.getDigitalSignatures() method.
Loop through the signatures.
Verify if each signature is valid or not using IDigitalSignature.isValid() method. Then get the certificate of the signature using IDigitalSignature.getX509Certificate() method, after that verify the certificate using X509Certificate.checkValidity() method.

The following is a basic code sample:

import com.spire.xls.Workbook;
import com.spire.xls.core.interfaces.IDigitalSignature;
import com.spire.xls.core.interfaces.IDigitalSignatures;

import java.security.cert.CertificateExpiredException;
import java.security.cert.CertificateNotYetValidException;
import java.security.cert.X509Certificate;

public class CheckAndVerifySignature {
    public static void main(String []args){

        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Signature.xlsx");

        //Check if the file is digitally signed
        boolean isSigned = workbook.isDigitallySigned();
        System.out.println("Is the file digitally signed? " + isSigned);

        if(isSigned) {
            //Get the digital signatures
            IDigitalSignatures signatures = workbook.getDigitalSignatures();

            //Traverse through the signatures
            for (IDigitalSignature signature : (Iterable<IDigitalSignature>) signatures) {
                //Verify the current signature
                boolean signatureIsValid = signature.isValid();

                //Get the certificate of the signature
                X509Certificate certificate = signature.getX509Certificate();

                //Verify the certificate
                boolean certificateIsValid = true;
                try {
                    certificate.checkValidity();
                } catch (CertificateExpiredException e) {
                    certificateIsValid = false;
                } catch (CertificateNotYetValidException e) {
                    certificateIsValid = false;
                }

                System.out.println("Is the signature valid? " + signatureIsValid);
                System.out.println("Is the certificate valid? " + certificateIsValid);
            }
        }

        workbook.dispose();
    }
}

Output:

Extract Tables from a PDF Document in Java

E-iceblue Product Family — Fri, 26 Nov 2021 07:03:59 +0000

Tables are one of the most common elements in PDF documents, such as electronic invoices, financial reports, or whatever PDF documents that contain tabular data. Developers may encounter the situations where they need to extract data out of PDF tables and do further analysis. In this article, I am going to introduce how to programmatically extract data from tables in PDF files in seconds using Spire.PDF for Java.

Installing Spire.Pdf.jar

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId> e-iceblue</groupId>
        <artifactId>spire.pdf</artifactId>
        <verson>4.11.2</version>
    </dependency>
</dependencies>

Using the code

Spire.PDF offers the PdfTableExtractor.extractTable() method to extract tables from a specific page. Below are the main steps to extract tables from a whole PDF document.

Load a sample PDF document while initializing the PdfDocument object.
Loop through the pages in the document, and get the table collection from a specific page using extractTable() method.
Loop through the rows and columns of a certain table, and get the value of a specific cell using PdfTable.getText(int rowIndex, int columnIndex) method.
Write the extracted data in a TXT file.

import com.spire.pdf.PdfDocument;
import com.spire.pdf.utilities.PdfTable;
import com.spire.pdf.utilities.PdfTableExtractor;

import java.io.FileWriter;
import java.io.IOException;

public class ExtractTablesFromPdf {

    public static void main(String[] args) throws IOException {

        //Load a sample PDF document
        PdfDocument pdf = new PdfDocument("C:\\Users\\Administrator\\Desktop\\Table.pdf");

        //Create a StringBuilder instance
        StringBuilder builder = new StringBuilder();

        //Create a PdfTableExtractor instance
        PdfTableExtractor extractor = new PdfTableExtractor(pdf);

        //Declare variables
        PdfTable[] pdfTables = null;
        int tableNumber = 1;

        //Loop through the pages
        for (int pageIndex = 0; pageIndex < pdf.getPages().getCount(); pageIndex++) {

            //Extract tables from the current page
            pdfTables = extractor.extractTable(pageIndex);

            //If any tables are found
            if (pdfTables != null && pdfTables.length > 0) {

                //Loop through the tables in the array
                for (PdfTable table : pdfTables) {

                    builder.append("Table " + tableNumber);
                    builder.append("\r\n");

                    //Loop through the rows in the current table
                    for (int i = 0; i < table.getRowCount(); i++) {

                        //Loop through the columns in the current table
                        for (int j = 0; j < table.getColumnCount(); j++) {

                            //Extract data from the current table cell
                            String text = table.getText(i, j);

                            //Append the text to the string builder
                            builder.append(text + " ");
                        }
                        builder.append("\r\n");
                    }
                    builder.append("\r\n");
                    tableNumber += 1;
                }
            }
        }

        //Write data into a .txt document
        FileWriter fw = new FileWriter("output/ExtractTables.txt");
        fw.write(builder.toString());
        fw.flush();
        fw.close();
    }
}