When you are creating a report by referencing data from multiple Excel files, you may find that the process is quite time-consuming and may also cause confusion or lead to errors as you need to switch between different opened files. In such a case, combining these separate Excel files into a single Excel workbook is a great option to simplify your work. This article will demonstrate how to merge multiple Excel files into one using Spire.XLS for Java.

• Merge Multiple Excel Workbooks into One in Java
• Merge Multiple Excel Worksheets into One in Java

Install Spire.XLS for Java

First, 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>15.11.3</version>
    </dependency>
</dependencies>

Merge Multiple Excel Workbooks into One in Java

With Spire.XLS for Java, you can merge data from different Excel files into different worksheets of one Excel Workbook. The following are the steps to merge multiple Excel workbooks into one.

• Specify the input Excel files that need to be merged.
• Initialize a Workbook object to create a new Excel workbook, and then clear all default worksheets in the workbook using Workbook.getWorksheets().clear() method.
• Initialize another temporary Workbook object.
• Loop through all input Excel files, and load the current workbook into the temporary Workbook object using Workbook.loadFromFile() method.
• loop through the worksheets in the current workbook, and then copy each worksheet from the current workbook to the new workbook using Workbook.getWorksheets().addCopy() method.
• Save the new workbook to file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class MergeExcels {
    public static void main(String[] args){
        //Specify the input Excel files
        String[] inputFiles = new String[]{"Budget Summary.xlsx", "Income.xlsx", "Expenses.xlsx"};

        //Initialize a new Workbook object
        Workbook newBook = new Workbook();

        //Clear the default worksheets
        newBook.getWorksheets().clear();

        //Initialize another temporary Workbook object
        Workbook tempBook = new Workbook();

        //Loop through all input Excel files
        for (String file : inputFiles)
        {
            //Load the current workbook
            tempBook.loadFromFile(file);
            //Loop through the worksheets in the current workbook
            for (Worksheet sheet : (Iterable) tempBook.getWorksheets())
            {
                //Copy each worksheet from the current workbook to the new workbook
                newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
            }
        }

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

The input Excel files:

The merged Excel workbook:

Merge Multiple Excel Worksheets into One in Java

An Excel workbook can contain multiple worksheets, and there are times you may also need to merge these worksheets into a single worksheet. The following are the steps to merge multiple Excel worksheets in the same workbook into one worksheet.

• Initialize a Workbook object and load an Excel file using Workbook.loadFromFile() method.
• Get two worksheets that need to be merged using Workbook.getWorksheets().get(int Index) method. Note that the sheet index is zero-based.
• Get the used range of the second worksheet using Worksheet.getAllocatedRange() method.
• Specify the destination range in the first worksheet using Worksheet.getCellRange(int row, int column) method. Note that the row and column indexes are 1-based.
• Copy the used range of the second worksheet to the destination range in the first worksheet using CellRange.copy(CellRange destRange) method.
• Remove the second worksheet using Worksheet.remove() method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class MergeExcelWorksheets {
    public static void main(String[] args){
        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile("input.xlsx");

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

        //Get the second worksheet
        Worksheet sheet2 = workbook.getWorksheets().get(1);

        //Get the used range in the second worksheet
        CellRange sourceRange = sheet2.getAllocatedRange();

        //Specify the destination range in the first worksheet
        CellRange destRange = sheet1.getCellRange(sheet1.getLastRow() + 1, 1);

        //Copy the used range of the second worksheet to the destination range in the first worksheet
        sourceRange.copy(destRange);

        //Remove the second worksheet
        sheet2.remove();

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

The input Excel worksheets:

The merged Excel worksheets:

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

This article demonstrates how to create a nested group in a worksheet using Spire.XLS for Java.

import com.spire.xls.*;

import java.awt.*;

public class CreateNestedGroup {

    public static void main(String[] args) {

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

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

        //Create a cell style
        CellStyle style = workbook.getStyles().addStyle("style");
        style.getFont().setColor(Color.blue);
        style.getFont().isBold(true);

        //Write data to cells
        sheet.get("A1").setValue("Project plan for project X");
        sheet.get("A1").setCellStyleName(style.getName());
        sheet.get("A3").setValue("Set up");
        sheet.get("A3").setCellStyleName(style.getName());
        sheet.get("A4").setValue("Task 1");
        sheet.get("A5").setValue("Task 2");
        sheet.getCellRange("A4:A5").borderAround(LineStyleType.Thin);
        sheet.getCellRange("A4:A5").borderInside(LineStyleType.Thin);
        sheet.get("A7").setValue("Launch");
        sheet.get("A7").setCellStyleName(style.getName());
        sheet.get("A8").setValue("Task 1");
        sheet.get("A9").setValue("Task 2");
        sheet.getCellRange("A8:A9").borderAround(LineStyleType.Thin);
        sheet.getCellRange("A8:A9").borderInside(LineStyleType.Thin);

        //Pass false to isSummaryRowBelow method , which indicates the summary rows appear above detail rows
        sheet.getPageSetup().isSummaryRowBelow(false);

        //Group the rows using groupByRows method
        sheet.groupByRows(2,9,false);
        sheet.groupByRows(4,5,false);
        sheet.groupByRows(8,9,false);

        //Save to file
        workbook.saveToFile("NestedGroup.xlsx", ExcelVersion.Version2016);
    }
}

While sharing your spreadsheets with others, you may do not want the receiver to alter the content or may want them to change only specific content and leave the rest unchanged. To protect your worksheet from being edited by other people, Excel offers a protection feature. In this article, you will learn how to programmatically protect and unprotect a workbook or a worksheet in Java by using Spire.XLS for Java.

• Password Protect an Entire Workbook
• Protect a Worksheet with a Specific Protection Type
• Allow Users to Edit Ranges in a Protected Worksheet
• Lock Specific Cells in a Worksheet
• Unprotect a Password Protected Worksheet
• Remove or Reset Password of an Encrypted Workbook

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

Password Protect an Entire Workbook in Java

By encrypting an Excel document with a password, you ensure that only you and authorized individuals can read or edit it. The following are the steps to password protect a workbook using Spire.XLS for Java.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Protect the workbook using a password using Workbook.protect() method.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

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

public class PasswordProtectWorkbook {

    public static void main(String[] args) {

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

        //Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Protect workbook with a password
        workbook.protect("psd-123");

        //Save the workbook to another Excel file
        workbook.saveToFile("output/Encrypted.xlsx", ExcelVersion.Version2016);
    }
}

Protect a Worksheet with a Specific Protection Type in Java

If you wish to grant people permission to read your Excel document but restrict the types of modifications they are allowed to make on a worksheet, you can protect the worksheet with a specific protection type. The table below lists a variety of pre-defined protection types under the SheetProtectionType enumeration.

The following are the steps to protect a worksheet with a specific protection type using Spire.XLS for Java.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get(index) method.
• Protect the worksheet with a protection type using Worksheet.protect(String password, EnumSet.of <SheetProtectionType> options) method.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

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

import java.util.EnumSet;

public class ProtectWorksheet {

    public static void main(String[] args) {

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

        //Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get a specific worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Protect the worksheet with the permission password and the specific protect type
        worksheet.protect("psd-permission", EnumSet.of(SheetProtectionType.None));

        //Save the workbook to another Excel file
        workbook.saveToFile("output/ProtectWorksheet.xlsx", ExcelVersion.Version2016);
    }
}

Allow Users to Edit Ranges in a Protected Worksheet in Java

In certain cases, you may need to allow users to be able to edit selected ranges in a protected worksheet. The following steps demonstrate how to.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get(index) method.
• Specify editable cell ranges using Worksheet.addAllowEditRange() method.
• Protect the worksheet with a protection type using Worksheet.protect(String password, EnumSet.of <SheetProtectionType> options) method.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

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

import java.util.EnumSet;

public class AllowEditRanges {

    public static void main(String[] args) {

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

        //Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

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

        //Add ranges that allow editing
        sheet.addAllowEditRange("Range One", sheet.getRange().get("A5:A6"));
        sheet.addAllowEditRange("Range Two", sheet.getRange().get("A8:B11"));

        //Protect the worksheet with a password and a protection type
        sheet.protect("psd-permission", EnumSet.of(SheetProtectionType.All));

        //Save the workbook to another Excel file
        workbook.saveToFile("output/AllowEditRange.xlsx", ExcelVersion.Version2016);
    }
}

Unprotect a Password Protected Worksheet in Java

To remove the protection of a password-protected worksheet, invoke the Worksheet.unprotect() method and pass in the original password as a parameter. The detailed steps are as follows.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get(index) method.
• Remove the protection using Worksheet.unprotect(String password) method.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

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

public class UnprotectWorksheet {

    public static void main(String[] args) {

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

        //Load an Excel file containing protected worksheet
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\ProtectedWorksheet.xlsx");

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

        //Unprotect the worksheet using the specified password
        sheet.unprotect("psd-permission");

        //Save the workbook to another Excel file
        workbook.saveToFile("output/UnprotectWorksheet.xlsx", ExcelVersion.Version2016);
    }
}

Remove or Reset Password of an Encrypted Workbook in Java

To remove or reset password of an encrypted workbook, you can use the Workbook.unprotect() method and the Workbook.protect() method, respectively. The following steps show you how to load an encrypted Excel document and delete or change the password of it.

• Create a Workbook object.
• Specify the open password using Workbook.setOpenPassword() method.
• Load the encrypted Excel file using Workbook.loadFromFile() method.
• Remove the encryption using Workbook.unprotect() method. Or change the password using Workbook.protect() method.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

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

public class RemoveOrResetPassword {

    public static void main(String[] args) {

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

        //Specify the open password
        workbook.setOpenPassword("psd-123");

        //Load an encrypted Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Encrypted.xlsx");

        //Unprotect workbook
        workbook.unProtect();

        //Reset password
        //workbook.protect("newpassword");

        //Save the workbook to another Excel file
        workbook.saveToFile("output/Unprotect.xlsx", ExcelVersion.Version2016);
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.