Spire.XLS for Java provides the getStyle() method and setStyle() method under the IXLSRange interface to get or set the style of a specific cell range. To copy formatting from one cell to another, get the style first and then apply it to another cell.

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

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

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

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

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

        //Get the number of rows used
        int rowCount = sheet.getRows().length;
        
        //Loop through the rows
        for (int i = 1; i < rowCount + 1; i++)
        {
            //Copy the formatting from a certain cell to another
            sheet.getRange().get(String.format("C%d",i)).setStyle(sheet.getRange().get(String.format("A%d",i)).getStyle());
        }

        //Save the result to file
        workbook.saveToFile("output/CopyFormatting.xlsx", ExcelVersion.Version2016);
    }
}

Spire.XLS for Java provides you with the ability to shrink text to fit in a cell by using the setShrinkToFit method of the CellStyleObject class. The setShrinkToFit method accepts the following parameter:

boolean: specify whether to shrink text to fit in a cell.

The following example shows how to shrink text to fit in a cell in Excel using Spire.XLS for Java.

import com.spire.xls.*;

public class ShrinkTextToFitInACell {
    public static void main(String []args) throws Exception {
        //Create a workbook instance
        Workbook workbook = new Workbook();

        //Load the Excel file
        workbook.loadFromFile("Sample.xlsx");

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

        //Get the cell range to shrink text
        CellRange cell = sheet.getRange().get("B2:B3");

        //Enable “shrink to fit”
        cell.getCellStyle().setShrinkToFit(true);

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

The input Excel:

The output Excel:

In the process of manipulating Excel worksheets, sometimes you may encounter the situation where the text in a cell is so long that some of it is hidden. At this time, it’s recommended to wrap the extra-long text into multiple lines so you can see it all. This article will demonstrate how to programmatically wrap or unwrap text in Excel cells using Spire.XLS for 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>14.4.1</version>
    </dependency>
</dependencies>
    

Wrap or Unwrap Text in Excel cells

Spire.XLS for Java supports wrapping or unwrapping text in Excel cells using the setWrapText() method provided by the IStyle interface. Below are detailed steps for your reference.

• Create a Workbook instance.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specific worksheet of the document using Workbook.getWorksheets().get() method.
• Get a specific cell of the worksheet using Worksheet.getRange().get() method.
• Get the style of the specified cell using XlsRange.getStyle() method and set whether the text is wrapped or not using setWrapText() method provided by IStyle interface.
• 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 WrapOrUnwrapText {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\sample.xlsx");

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

        //Wrap text in the cell "D8"
        sheet.getRange().get("D8").getStyle().setWrapText(true);

        //Unwrap text in the cell "D6"
        sheet.getRange().get("D6").getStyle().setWrapText(false);

        //Save the document to another file
        workbook.saveToFile("output/WrapOrUnwrapText.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.

Copying worksheet involves duplicating an existing worksheet within the same workbook or across different workbooks. This valuable feature enables developers to create an exact replica of the original worksheet effortlessly, including its structure, formatting, data, formulas, charts, and other objects without any mistake. It proves especially beneficial when dealing with extensive data files, as it significantly reduces time and effort required for backing up files and creating templates. In this article, we will introduce how to copy worksheets in Excel using Spire.XLS for Java. With this method, all the cell formats in the original Excel worksheets will be completely remained.

• Copy Worksheets between Workbooks
• Copy Worksheets within Workbooks

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

Copy Worksheets between Workbooks

Spire.XLS for Java library allows you copy worksheets from one workbook to another file easily by using Worksheet.copyFrom() method. The following are detailed steps.

• Create a new Workbook object.
• Load the source Excel file from disk using Workbook.loadFromFile() method.
• Get the first worksheet of the source file by using Workbook.getWorksheets().get() method.
• Create an another Workbook object.
• Load the target file from disk using Workbook.loadFromFile() method.
• Add a new sheet to the target file using Workbook.getWorksheets().add() method.
• Copy the first worksheet of the source file to the new added sheet of the target file through Worksheet.copyFrom() method.
• Finally, specify the output path and save the target file using Workbook.saveToFile() method.

import com.spire.xls.*;

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

        //Create a Workbook
        Workbook sourceWorkbook = new Workbook();

        //Load the source Excel file from disk
        sourceWorkbook.loadFromFile("sample1.xlsx");

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

        //Create a another Workbook
        Workbook targetWorkbook = new Workbook();

        //Load the target Excel file from disk
        targetWorkbook.loadFromFile("sample2.xlsx");

        //Add a new worksheet
        Worksheet targetWorksheet = targetWorkbook.getWorksheets().add("added");

        //Copy the first worksheet of sample1 to the new added sheet of sample2
        targetWorksheet.copyFrom(srcWorksheet);

        //String for output file
        String outputFile = "output/CopyWorksheet.xlsx";

        //Save the result file
        targetWorkbook.saveToFile(outputFile, ExcelVersion.Version2013);
        sourceWorkbook.dispose();
        targetWorkbook.dispose();
    }
}

Copy Worksheets within Workbooks

You can also copy a worksheet within the same workbook by adding a new worksheet to this workbook and then copying the desired sheet to the new one. The following are the steps to duplicate worksheets within an Excel workbook.

• Create a new Workbook object.
• Load the source Excel file from disk using Workbook.loadFromFile() method.
• Get the first worksheet by using Workbook.getWorksheets().get() method and add a new sheet called "MySheet" using Workbook.getWorksheets().add() method.
• Copy the first worksheet to the second one through Worksheet.copyFrom() method;
• Finally, specify the output path and save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

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

        //Load the sample file from disk
        workbook.loadFromFile("sample1.xlsx");

        //Get the first sheet and add a new worksheet to this file
        Worksheet sheet = workbook.getWorksheets().get(0);
        Worksheet sheet1 = workbook.getWorksheets().add("MySheet");

        //Copy the first worksheet to the second one
        sheet1.copyFrom(sheet);

        //String for output file
        String result = "output/CopySheetWithinWorkbook.xlsx";

        //Save to file
        workbook.saveToFile(result, ExcelVersion.Version2013);
        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.