This article demonstrates how to insert arrays, including one-dimensional and two-dimensional arrays, into Excel cells using Spire.XLS for Java.

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

public class InsertArray {

    public static void main(String[] args) {

        //Create a Workbook instance 
        Workbook wb = new Workbook();

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

        //Define a one-dimensional array 
        String[] oneDimensionalArray = new String[]{"Apple", "Pear", "Grape", "Banana"};

        // Write the array to the worksheet from the specified cell (true means vertically insert)
        sheet.insertArray(oneDimensionalArray, 1, 1, true);

        //Define a two-dimensional array 
        String[][] twoDimensionalArray = new String[][]{
                {"Name", "Age", "Sex", "Dept."},
                {"John", "25", "Male", "Development"},
                {"Albert", "24", "Male", "Support"},
                {"Amy", "26", "Female", "Sales"}
        };

        //Write the array to the worksheet from the specified cell
        sheet.insertArray(twoDimensionalArray, 1, 3);

        //Save the file 
        wb.saveToFile("InsertArrays.xlsx", ExcelVersion.Version2016);
    }
}

Merging cells in Excel refers to combining two or more adjacent cells into one large cell that spans multiple rows or columns. This is useful for creating titles or labels that need to be centered over a range of cell. In this article, you will learn how to programmatically merge or unmerge cells in an Excel document using Spire.XLS for Java.

• Merge Cells in Excel in Java
• Unmerge Cells in Excel 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 Cells in Excel in Java

The detailed steps are as follows.

• Create a Workbook instance.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Get a specified range using Worksheet.getRange().get() method.
• Merge cells in the specified range using XlsRange.merge() method.
• Set the horizontal alignment of merged cells to Center using XlsRange.getCellStyle().setHorizontalAlignment() method.
• Set the vertical alignment of merged cells to Center using XlsRange.getCellStyle().setVerticalAlignment() method.
• Save the result document using Workbook.saveToFile() method.

import com.spire.xls.*;

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

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

        //Load a sample Excel document
        workbook.loadFromFile("input.xlsx");

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

        //Merge cells by range
        sheet.getRange().get("A2:A4").merge();
        sheet.getRange().get("A5:A7").merge();

        //Set the horizontal alignment of merged cells to Center
        sheet.getRange().get("A2").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        sheet.getRange().get("A5").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);

        //Set the vertical alignment of merged cells to Center
        sheet.getRange().get("A2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getRange().get("A5").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);


        //Save the result document
        workbook.saveToFile("MergeCells.xlsx", FileFormat.Version2013);
    }
}

Unmerge Cells in Excel in Java

The detailed steps are as follows.

• Create a Workbook instance.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Get a specified range using Worksheet.getRange().get() method.
• Unmerge cells in the specified range using XlsRange.unMerge() method.
• Save the result document using Workbook.saveToFile() method.

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

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

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

        //Load a sample Excel document
        workbook.loadFromFile("MergeCells.xlsx");

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

        //Unmerge cells by range
        sheet.getRange().get("A2:A4").unMerge();

        //Save the result document
        workbook.saveToFile("UnMergeCells.xlsx", FileFormat.Version2013);
    }
}

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.

Using PDF as a format for sending documents ensures that no formatting changes will occur to the original document. Exporting Excel to PDF is a common practice in many cases. This article introduces how to convert a whole Excel document or a specific worksheet to PDF using Spire.XLS for Java.

• Convert a Whole Excel File to PDF
• Convert a Specific Worksheet to PDF

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>

Convert a Whole Excel File to PDF

The following are the steps to convert a whole Excel document to 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 ConvertExcelToPdf {

    public static void main(String[] args) {

        //Create a Workbook instance and load an Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx");

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

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

Convert a Specific Worksheet to PDF

The following are the steps to convert a specific worksheet to 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.
• Get a specific worksheet using Workbook.getWorksheets().get() method.
• Convert the worksheet to PDF using Worksheet.saveToPdf() method.

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

public class ConvertWorksheetToPdf {

    public static void main(String[] args) {

        //Create a Workbook instance and load an Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx");

        //Set worksheets to fit to width when converting
        workbook.getConverterSetting().setSheetFitToWidth(true);

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

        //Convert to PDF and save the resulting document to a specified path
        worksheet.saveToPdf("output/WorksheetToPdf.pdf");
    }
}

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.