The excel zoom factor could help us to display the data on Excel worksheet clearly or completely. This article will demonstrate how to set the zoom factor on Excel work sheet in Java application by Spire.XLS for Java.

import com.spire.xls.*;

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

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

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

        //Set the zoom factor of the sheet to 150
        sheet.setZoom(150);

        //Save the Excel file
        workbook.saveToFile("Zoomfactor.xlsx", ExcelVersion.Version2010);
    }
}

Effective screenshot after setting the zoom factor of the sheet to 150.

This article demonstrates how to create chart with non-contiguous data in Excel using Spire.XLS for Java.

The example Excel file:

import com.spire.xls.*;
import com.spire.xls.charts.ChartSerie;

import java.awt.*;

public class ChartWithNonContiguousData {
    public static void main(String[] args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("NonContiguousData.xlsx");

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

        //Add a clustered column chart to the worksheet
        Chart chart = sheet.getCharts().add(ExcelChartType.ColumnClustered);
        chart.setSeriesDataFromRange(false);
        //Set chart position
        chart.setLeftColumn(1);
        chart.setTopRow(10);
        chart.setRightColumn(10);
        chart.setBottomRow(24);

        //Add a series to the chart
        ChartSerie cs1 = (ChartSerie)chart.getSeries().add();
        //Set series name
        cs1.setName(sheet.getCellRange("B1").getValue());
        //Set category labels for the series using non-contiguous data
        cs1.setCategoryLabels(sheet.getCellRange("A2:A3").addCombinedRange(sheet.getCellRange("A5:A6"))
.addCombinedRange(sheet.getCellRange("A8:A9")));
        //Set values for the series using non-contiguous data
        cs1.setValues(sheet.getCellRange("B2:B3").addCombinedRange(sheet.getCellRange("B5:B6"))
.addCombinedRange(sheet.getCellRange("B8:B9")));
        //Specify the series type
        cs1.setSerieType(ExcelChartType.ColumnClustered);

        //Add a series to the chart
        ChartSerie cs2 = (ChartSerie)chart.getSeries().add();
        //Set series name
        cs2.setName(sheet.getCellRange("C1").getValue());
        //Set category labels for the series using non-contiguous data
        cs2.setCategoryLabels(sheet.getCellRange("A2:A3").addCombinedRange(sheet.getCellRange("A5:A6"))
.addCombinedRange(sheet.getCellRange("A8:A9")));
        //Set values for the series using non-contiguous data
        cs2.setValues(sheet.getCellRange("C2:C3").addCombinedRange(sheet.getCellRange("C5:C6"))
.addCombinedRange(sheet.getCellRange("C8:C9")));
        //Specify the series type
        cs2.setSerieType(ExcelChartType.ColumnClustered);

        //Set chart title
        chart.setChartTitle("Chart");
        chart.getChartTitleArea().getFont().setSize(20);
        chart.getChartTitleArea().setColor(Color.black);
        
        chart.getPrimaryValueAxis().hasMajorGridLines(false);

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

Output:

This article demonstrates how to freeze or unfreeze rows and columns in Excel using Spire.XLS for Java.

Freeze top row

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

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

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

//Freeze top row
sheet.freezePanes(2,1);

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

Freeze fisrt column

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

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

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

//Freeze frist column
sheet.freezePanes(1,2);

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

Freeze few rows and columns

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

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

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

//Freeze the second row and the second column
sheet.freezePanes(3,3);

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

Unfreeze panes

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

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

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

//Unfreeze panes
sheet.removePanes();

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

Excel documents are widely used in many applications, and it is often necessary to customize their appearance to improve their readability. One way to achieve this is by setting a background color or image for the document, which can enhance its visual appeal and give it a more professional look. This article will demonstrate how to set background color and image for Excel in Java using Spire.XLS for Java.

• Set Background Color for Excel in Java
• Set Background Image for 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>16.8.4</version>
    </dependency>
</dependencies>

Set Background Color for Excel in Java

With Spire.XLS for Java, not only can you set the background color for the entire range of cells used in the worksheet, but you can also set it for a specific range of cells within the worksheet. The following are the steps to set background color for Excel.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet from the workbook using Workbook.getWorksheets.get(index) method.
• Use Worksheet.getAllocatedRange().getStyle().setColor() method to set background color for the used cell range or Worksheet.getCellRange().getStyle().setColor() method to set background color for a specified cell range in the worksheet.
• Save the result file using Workbook.saveToFile() method.

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

        import java.awt.*;

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

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Set background color for the used cell range in the worksheet
        sheet.getAllocatedRange().getStyle().setColor(Color.orange);
        //Set background color for a specified cell range in the worksheet
        //sheet.getCellRange("A1:E19").getStyle().setColor(Color.pink);

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

Set Background Image for Excel in Java

Spire.XLS for Java also offers Worksheet.getPageSetup().setBackgoundImage() method for users to set the image background. The following are the steps to achieve this.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet from the workbook using Workbook.getWorksheets.get(index) method.
• Set the image as the background image of the worksheet using Worksheet. getPageSetup().setBackgoundImage() method.
• Save the result file using Workbook.saveToFile() method.

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

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;

public class BackgroundImage {
    public static void main(String[] args) throws IOException {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Load an image
        BufferedImage image = ImageIO.read( new File("background.jpg"));
        //Set the image as the background image of the worksheet
        sheet.getPageSetup().setBackgoundImage(image);

        //Save the file
        workbook.saveToFile("SetBackImage.xlsx", ExcelVersion.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.

Textboxes in Excel are versatile tools that enhance the functionality of your spreadsheets. They allow users to add annotations, labels, or supplementary information, making it easier to convey important messages or insights. Whether you're looking to highlight critical data points, provide detailed explanations, or create visually appealing reports, effectively managing textboxes is essential.

In this article, you will learn how to insert a textbox, extract text from a textbox, and delete a textbox in Excel using Java and Spire.XLS for Java.

• Insert a Textbox to Excel in Java
• Extract Text from a Textbox in Excel in Java
• Delete a Textbox in Excel in Java

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

Insert a Textbox to Excel in Java

A textbox can be inserted into a worksheet using the Worksheet.getTextBoxes().addTextBox() method. This method returns an ITextBoxShape object, which provides various methods like setText(), setHAlignment(), and getFill() for configuring the text and formatting of the textbox.

To add a textbox with personalized text and formatting in Excel, follow these steps:

• Create a Workbook object.
• Load an Excel file from the specified file path.
• Retrieve a specific worksheet from the workbook.
• Insert a textbox at the desired location using Worksheet.getTextBoxes().addTextBox() method.
• Set the textbox text using ITextBoxShape.setText() method.
• Customize the textbox's appearance using other methods available in the ITextBoxShape object.
• Save the workbook as a new Excel file.

import com.spire.xls.*;
import com.spire.xls.core.ITextBoxShape;

import java.awt.*;

public class AddTextbox {

    public static void main(String[] args) {

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

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

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

        // Add a textbox to the specified location
        ITextBoxShape textBoxShape = sheet.getTextBoxes().addTextBox(5, 4, 60, 200);

        // Set text of the textbox
        textBoxShape.setText("This is a text box, with sample text.");

        // Create a font
        ExcelFont font = workbook.createFont();
        font.setFontName("Times New Roman");
        font.setSize(14);
        font.setColor(Color.red);

        // Apply font to the text
        textBoxShape.getRichText().setFont(0, textBoxShape.getText().length() - 1, font);

        // Set horizontal alignment
        textBoxShape.setHAlignment(CommentHAlignType.Left);

        // Set the fill color of the shape
        textBoxShape.getFill().setFillType(ShapeFillType.SolidColor);
        textBoxShape.getFill().setForeColor(Color.LIGHT_GRAY);

        // Save the Excel file
        workbook.saveToFile("output/AddTextBox.xlsx", ExcelVersion.Version2010);

        // Dispose resources
        workbook.dispose();
    }
}

Extract Text from a Textbox in Excel in Java

You can access a specific textbox using the Worksheet.getTextBoxes().get() method. After retrieving it, the text can be accessed with the ITextBox.getText() method.

Here are the steps to extract text from a textbox in Excel:

• Create a Workbook object.
• Load an Excel file from the specified file path.
• Retrieve a specific worksheet from the workbook.
• Access the desired textbox using Worksheet.getTextBoxes().get() method.
• Get the textbox's text using ITextBox.getText() method.

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

public class ExtractTextbox {

    public static void main(String[] args) {

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

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

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

        // Get a specific textbox
        ITextBox textBox = sheet.getTextBoxes().get(0);

        // Get text from the textbox
        String text = textBox.getText();

        // Print out result
        System.out.println(text);
    }
}

Delete a Textbox in Excel in Java

To delete a specific textbox from a worksheet, utilize the ITextBoxes.get().Remove() method. If you want to clear all textboxes, first obtain the count with the Worksheet.getTextBoxes().getCount() method, then loop through the collection to remove each textbox one by one.

Here's how to remove a textbox from Excel:

• Create a Workbook object.
• Load an Excel file from the desired file path.
• Access a specific worksheet within the workbook.
• Retrieve the textbox collection using Worksheet.getTextBoxes() method.
• Delete the targeted textbox using ITextBoxes.get().Remove() method.
• Save the modified workbook to a new Excel file.

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

public class DeleteTextbox {

    public static void main(String[] args) {

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

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

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

        // Get textbox collection from the worksheet
        ITextBoxes textBoxes = sheet.getTextBoxes();

        // Remove a specific textbox
        textBoxes.get(0).remove();

        // Save the updated document to a different Excel file
        workbook.saveToFile("output/DeleteTextbox.xlsx", ExcelVersion.Version2016);

        // Dispose resources
        workbook.dispose();
    }
}

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.

Sometimes we need to merge several related Word documents to make a more complete one. To merge documents with MS Word, you need to manually copy and paste contents or import contents from other documents, which can be tedious. Luckily, Spire.Doc for Java provides 2 easy ways to merge Word documents by programming. This article will show the detailed steps to merge Word documents.

• Merge Documents by Inserting the Entire File
• Merge Documents by Cloning Contents

Install Spire.Doc for Java

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

Merge Documents by Inserting the Entire File

The method Document.insertTextFromFile() provided by Spire.Doc allows merging Word documents by inserting other documents entirely into a document with the inserted contents starting from a new page.

The detailed steps of merging documents by inserting the entire file are as follows:

• Create an object of Document class and load a Word document from disk.
• Insert another Word document entirely to the loaded document using Document.insertTextFromFile() method.
• Save the result document using Document.saveToFile() method.

import com.spire.doc.*;

public class merge {
    public static void main(String[] args) {
        //Create an object of Document and load a Word document from disk
        Document document = new Document("C:/Samples/Sample1.docx");

        //Insert another Word document entirely to the document
        document.insertTextFromFile("C:/Samples/Sample2.docx", FileFormat.Docx_2013);

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

Merge Documents by Cloning Contents

If you want to merge documents without starting a new page, you can clone the contents of other documents to add to the end of a document.

The detailed steps of merging documents by cloning contents are as follows:

• Create two objects of Document and load the two Word documents from disk.
• Loop through the second document to get all the sections using Document.getSections() method, then loop through all the sections to get their child objects using Section.getBod().getChildObjects() method, then get the last section of the first document using Document.getLastSection() method, and then add the child objects to the last section of the first document using Body.getChildObjects().add() method.
• Save the result document using Document.saveToFile() method.

import com.spire.doc.*;

public class mergeDocuments {
    public static void main(String[] args){
        //Create two Document objects and load two Word documents from disk
        Document document1 = new Document("C:/Samples/Sample1.docx");
        Document document2 = new Document("C:/Samples/Sample2.docx");

        //Loop through the second document to get all the sections
        for (Object sectionObj : (Iterable) document2.getSections()) {
            Section sec=(Section)sectionObj;
            //Loop through the sections of the second document to get their child objects
            for (Object docObj :(Iterable ) sec.getBody().getChildObjects()) {
                DocumentObject obj=(DocumentObject)docObj;

                //Get the last section of the first document
                Section lastSection = document1.getLastSection();

                //Add the child objects to the last section of the first document
                Body body = lastSection.getBody();
                body.getChildObjects().add(obj.deepClone());
            }
        }

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

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.