Java (482)
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.
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.3.2</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.
- Java
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.
- Java
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.
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.3.2</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.
- Java
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.
- Java
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.
Excel is a versatile tool extensively utilized for data management and analysis. There are occasions when you may require locating specific data within an Excel file and replacing it with updated values. In this article, we will explore how to find and replace data in Excel in Java using Spire.XLS for Java.
- Find and Replace Data in a Worksheet in Excel
- Find and Replace Data in a Specific Cell Range in Excel
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.3.2</version>
</dependency>
</dependencies>
Find and Replace Data in a Worksheet in Excel
The Worksheet.findAllString() method provided by Spire.XLS for Java can help you find the cells containing specific text in Excel documents. Once found, you can conveniently replace these values with new ones using the CellRange.setText() method. The steps are as follows:
- Create an instance of Workbook class.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the desired worksheet using Workbook.getWorksheets().get() method.
- Find the specific value in the worksheet using Worksheet.findAllString() method and replace the value of the cell with another value using CellRange.setText() method.
- Set a background for the cell so you can easily find the updated cells using CellRange. getStyle().setColor() method.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import java.awt.Color;
public class ReplaceData {
public static void main(String[] args) {
// Initialize an instance of the Workbook class
Workbook workbook = new Workbook();
// Load an Excel file
workbook.loadFromFile("Test.xlsx");
// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Find the cells with the specific string value "Total" in the worksheet
CellRange[] cells = worksheet.findAllString("Total", true, true);
// Iterate through the found cells
for (CellRange cell : cells) {
// Replace the value of the cell with another value
cell.setText("Sum");
// Set a background color for the cell
cell.getStyle().setColor(Color.YELLOW);
}
// Save the result file to a specific location
workbook.saveToFile("ReplaceDataInWorksheet.xlsx", ExcelVersion.Version2016);
workbook.dispose();
}
}
Find and Replace Data in a Specific Cell Range in Excel
To replace data within a specific range of cells, you can utilize the CellRange.findAllString() method to locate cells within the range that contain the desired values. Then, use the CellRange.setText() method to replace the cell value with a new value. The detailed steps are as follows:
- Create an instance of Workbook class.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the desired worksheet using Workbook.getWorksheets().get() method.
- Get a specific cell range using Worksheet.getCellRange() method.
- Find the cells with the specific value in the cell range using CellRange.findAllString() method.
- Iterate through the found cells
- Replace the value of the cell with another value using CellRange setText() method.
- Set a background for the cell so you can easily find the updated cells using the CellRange. getStyle().setColor() method.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import java.awt.Color;
public class ReplaceDataInCellRange {
public static void main(String[] args) {
// Initialize an instance of the Workbook class
Workbook workbook = new Workbook();
// Load an Excel file
workbook.loadFromFile("Test.xlsx");
// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Get a specific cell range
CellRange range = worksheet.getCellRange("A1:C12");
// Find the cells with the specific value "Total" in the cell range
CellRange[] cells = range.findAllString("Total", true, true);
// Iterate through the found cells
for (CellRange cell : cells) {
// Replace the value of the cell with another value
cell.setText("Sum");
// Set a background color for the cell
cell.getStyle().setColor(Color.YELLOW);
}
// Save the result file to a specific location
workbook.saveToFile("ReplaceDataInCellRange.xlsx", ExcelVersion.Version2016);
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.
