Java: Read or Delete Document Properties from Excel
Document properties in Excel are important pieces of metadata that provide additional information about a workbook. If you are managing multiple Excel workbooks and want to keep track of information like author, title, and other relevant metadata, you can read their document properties to quickly gather this information. Besides, in certain situations, you may need to delete document properties from Excel. For instance, if sensitive data is inadvertently stored in document properties, you may need to delete these document properties before sharing the workbook to ensure data security and confidentiality. This article will show you how to read or delete document properties from Excel in Java using Spire.XLS for Java library.
- Read Standard and Custom Document Properties from Excel in Java
- Delete Standard and Custom Document Properties from 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>14.11.0</version>
</dependency>
</dependencies>
Read Standard and Custom Document Properties from Excel in Java
Standard document properties are pre-built properties included in every Excel file. These properties can include information such as the author, title, subject, keywords, and other details about the file. Custom document properties in Excel are user-defined, meaning that users can create them according to their specific needs. The value of custom document properties can be assigned as text, date time, numeric values, or simply a yes or no.
The following steps demonstrate how to read standard document properties and custom document properties of an Excel file using Spire.XLS for Java:
- Initialize an instance of the Workbook class.
- Load an Excel file using the Workbook.loadFromFile() method.
- Initialize an instance of the StringBuilder class for storing the standard and custom document properties.
- Get the collection of all standard document properties of the file using the Workbook.getDocumentProperties() method.
- Get specific standard document properties using the corresponding methods under the BuiltInDocumentProperties class.
- Append the standard document properties to the StringBuilder instance.
- Get the collection of all custom document properties of the file using the Workbook.getCustomDocumentProperties() method.
- Iterate through the collection.
- Get the name and value of each custom document property using the IDocumentProperty.getName() and IDocumentProperty.getValue() methods and append them to the StringBuilder instance.
- Write the content of the StringBuilder instance into a text file.
- Java
import com.spire.xls.Workbook;
import com.spire.xls.collections.BuiltInDocumentProperties;
import com.spire.xls.core.ICustomDocumentProperties;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
public class ReadStandardDocumentProperties {
public static void main(String[] args) throws IOException {
//Initialize an instance of the Workbook class.
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("Sample.xlsx");
//Initialize an instance of the StringBuilder instance
StringBuilder sb = new StringBuilder();
//Get the collection of all standard document properties
BuiltInDocumentProperties standardProperties = workbook.getDocumentProperties();
//Get specific standard document properties
String title = standardProperties.getTitle();
String subject = standardProperties.getSubject();
String author = standardProperties.getAuthor();
String keywords = standardProperties.getKeywords();
String manager = standardProperties.getManager();
String company = standardProperties.getCompany();
String category = standardProperties.getCategory();
String comments = standardProperties.getComments();
//Append the standard document properties to the StringBuilder instance
sb.append("Standard Document properties:"
+"\r\nTitle: " + title
+ "\r\nSubject: " + subject
+ "\r\nAuthor: " + author
+ "\r\nKeywords: "+ keywords
+ "\r\nManager: " + manager.toString()
+ "\r\nCompany: " + company.toString()
+ "\r\nCategory: " + category.toString()
+ "\r\nComments: " + comments.toString()
);
sb.append("\r\n\nCustom Document Properties:");
//Get the collection of all custom document properties
ICustomDocumentProperties customProperties = workbook.getCustomDocumentProperties();
//Iterate through the collection
for(int i =0; i < customProperties.getCount(); i++)
{
//Append the name and value of each custom document property to the StringBuilder instance
sb.append("\r\n" + customProperties.get(i).getName() + ": " + customProperties.get(i).getValue());
}
//Write the content of the StringBuilder instance into a text file
String output = "ReadDocumentProperties.txt";
FileWriter fw = new FileWriter(output, true);
BufferedWriter bw = new BufferedWriter(fw);
bw.append(sb);
bw.close();
fw.close();
workbook.dispose();
}
}
Delete Standard and Custom Document Properties from Excel in Java
You can easily delete standard document properties from an Excel file by setting their values as empty. For custom document properties, you can use the ICustomDocumentProperties.remove() method to delete them.
The following steps demonstrate how to delete standard and custom document properties from an Excel file using Spire.XLS for Java:
- Initialize an instance of the Workbook class.
- Load an Excel file using the Workbook.loadFromFile() method.
- Get the collection of all standard document properties of the file using the Workbook.getDocumentProperties() method.
- Set the values of specific standard document properties as empty using the corresponding methods under the BuiltInDocumentProperties class.
- Get the collection of all custom document properties of the file using the Workbook.getCustomDocumentProperties() method.
- Iterate through the collection.
- Delete each custom document property from the collection using the ICustomDocumentProperties.remove() method.
- Save the result file using the Workbook.saveToFile() method.
- Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.collections.BuiltInDocumentProperties;
import com.spire.xls.core.ICustomDocumentProperties;
public class DeleteDocumentProperties {
public static void main(String[] args) {
//Initialize an instance of the Workbook class.
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("Sample.xlsx");
//Get the collection of all standard document properties
BuiltInDocumentProperties standardProperties = workbook.getDocumentProperties();
//Set the value of each standard document property as empty
standardProperties.setTitle("");
standardProperties.setSubject("");
standardProperties.setAuthor("");
standardProperties.setManager("");
standardProperties.setCompany("");
standardProperties.setCategory("");
standardProperties.setKeywords("");
standardProperties.setComments("");
//Get the collection of all custom document properties
ICustomDocumentProperties customProperties = workbook.getCustomDocumentProperties();
//Iterate through the collection
for(int i = customProperties.getCount() - 1; i >= 0; i--)
{
//Delete each custom document property from the collection by its name
customProperties.remove(customProperties.get(i).getName());
}
//Save the result file
workbook.saveToFile("DeleteDocumentProperties.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.
Java: Write Data to Excel Worksheets
Excel has the ability to store and organize large amounts of data into orderly spreadsheets quickly. But it is time-consuming if we manually enter data into cells one after another. Automating the creation of Excel files by programming can save us a lot of time and energy. This article introduces how to write data into Excel worksheets in Java by using Spire.XLS for Java.
- Write Text or Number Values to Specific Cells
- Write Arrays to a Worksheet
- Write a DataTable to a Worksheet
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>14.11.0</version>
</dependency>
</dependencies>
Write Text or Number Values to Specific Cells
A certain cell in a worksheet can be accessed by Worksheet.get(int row, int column) method. Then, you can add a text value or a number value to the cell using the IXLSRange.setValue() or IXLSRange.setNumberValue() method. The following are the detailed steps.
- Create a Workbook object.
- Get the first worksheet using Workbook.getWorksheets().get() method.
- Get a specific cell using Workhseet.get() method.
- Add a text value or a number value to the cell using IXLSRange.setValue() or IXLSRange.setNumberValue() method.
- Save the workbook to an Excel file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class WriteToCells {
public static void main(String[] args) {
//Create a Workbook object
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
//Write data to specific cells
worksheet.get(1,1).setValue("Name");
worksheet.get(1,2).setValue("Age");
worksheet.get(1,3).setValue("Department");
worksheet.get(1,4).setValue("Hiredate");
worksheet.get(2,1).setValue("Hazel");
worksheet.get(2,2).setNumberValue(29);
worksheet.get(2,3).setValue("Marketing");
worksheet.get(2,4).setValue("2019-07-01");
worksheet.get(3,1).setValue("Tina");
worksheet.get(3,2).setNumberValue(31);
worksheet.get(3,3).setValue("Technical Support");
worksheet.get(3,4).setValue("2015-04-27");
//Auto fit column widths
worksheet.getAllocatedRange().autoFitColumns();
//Apply a style to the first row
CellStyle style = workbook.getStyles().addStyle("newStyle");
style.getFont().isBold(true);
worksheet.getRange().get(1,1,1,4).setStyle(style);
//Save to an Excel file
workbook.saveToFile("output/WriteToCells.xlsx", ExcelVersion.Version2016);
}
}
Write Arrays to a Worksheet
Spire.XLS for Java provides the Worksheet.insertArrary() method, allowing programmers to write one-dimensional array or two-dimensional array into the specified cell range of a worksheet. The steps to write arrays to a worksheet are as follows:
- Create a Workbook object.
- Get the first worksheet using Workbook.getWorksheets().get() method.
- Create a one-dimensional array and a two-dimensional array.
- Insert the arrays to worksheet using Worksheet.insertArray() method.
- Save the workbook to an Excel file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class WriteArrayToWorksheet {
public static void main(String[] args) {
//Create a Workbook instance
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
//Create a one-dimensional array
String[] oneDimensionalArray = new String[]{"January", "February", "March", "April","May", "June"};
//Write the array to the first row of the worksheet
worksheet.insertArray(oneDimensionalArray, 1, 1, false);
//Create a two-dimensional array
String[][] twoDimensionalArray = new String[][]{
{"Name", "Age", "Sex", "Dept.", "Tel."},
{"John", "25", "Male", "Development","654214"},
{"Albert", "24", "Male", "Support","624847"},
{"Amy", "26", "Female", "Sales","624758"}
};
//Write the array to the worksheet starting from the cell A3
worksheet.insertArray(twoDimensionalArray, 3, 1);
//Auto fit column width in the located range
worksheet.getAllocatedRange().autoFitColumns();
//Apply a style to the first and the third row
CellStyle style = workbook.getStyles().addStyle("newStyle");
style.getFont().isBold(true);
worksheet.getRange().get(1,1,1,6).setStyle(style);
worksheet.getRange().get(3,1,3,6).setStyle(style);
//Save to an Excel file
workbook.saveToFile("output/WriteArrays.xlsx", ExcelVersion.Version2016);
}
}
Write a DataTable to a Worksheet
To import data from a DataTable to a worksheet, use the Worksheet.insertDataTable() method. The following are the detailed steps.
- Create a Workbook object.
- Get the first worksheet using Workbook.getWorksheets().get() method.
- Create a DataTable, and write the DataTable to the worksheet at the specified location using Worksheet.insertDataTable() method.
- Save the workbook to an Excel file using Workbook.saveToFile() method.
- Java
import com.spire.data.table.DataRow;
import com.spire.data.table.DataTable;
import com.spire.xls.*;
public class WriteDataTableToWorksheet {
public static void main(String[] args) throws Exception {
//Create a Workbook instance
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
//Create a DataTable object
DataTable dataTable = new DataTable();
dataTable.getColumns().add("SKU", Integer.class);
dataTable.getColumns().add("NAME", String.class);
dataTable.getColumns().add("PRICE", String.class);
//Create rows and add data
DataRow dr = dataTable.newRow();
dr.setInt(0, 512900512);
dr.setString(1,"Wireless Mouse M200");
dr.setString(2,"$85");
dataTable.getRows().add(dr);
dr = dataTable.newRow();
dr.setInt(0,512900637);
dr.setString(1,"B100 Cored Mouse ");
dr.setString(2,"$99");
dataTable.getRows().add(dr);
dr = dataTable.newRow();
dr.setInt(0,512901829);
dr.setString(1,"Gaming Mouse");
dr.setString(2,"$125");
dataTable.getRows().add(dr);
dr = dataTable.newRow();
dr.setInt(0,512900386);
dr.setString(1,"ZM Optical Mouse");
dr.setString(2,"$89");
dataTable.getRows().add(dr);
//Write datatable to the worksheet
worksheet.insertDataTable(dataTable,true,1,1,true);
//Auto fit column width in the located range
worksheet.getAllocatedRange().autoFitColumns();
//Apply a style to the first row
CellStyle style = workbook.getStyles().addStyle("newStyle");
style.getFont().isBold(true);
worksheet.getRange().get(1,1,1,3).setStyle(style);
//Save to an Excel file
workbook.saveToFile("output/WriteDataTable.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.
Java: Accept or Reject Tracked Changes in Excel
An Excel document with Track Changes turned on will let you know what changes have been made to the document since the author has saved it. If you have the full authority over the document, you can accept or reject each revision. This article covers how to accept or reject all tracked changes at once 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.11.0</version>
</dependency>
</dependencies>
Accept Tracked Changes in a Workbook
To determine whether a workbook has tracked changes, use Workbook.hasTrackedChanegs() method. If yes, you can accept all changes at once using Workbook.acceptAllTrackedchanges() method. The following are the steps to accept tracked changes in an Excel workbook.
- Create a Workbook object.
- Load the sample Excel document using Workbook.loadFromFile() method.
- Determine if the workbook has tracked changes by Workbook.hasTrackedChanegs() method.
- Accept tracked changes using Workbook.acceptAllTrackedChanges() method.
- Save the document to another file using Workbook.saveToFile() method.
- Java
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
public class AcceptTrackedChanges {
public static void main(String[] args) {
//Create a Workbook object
Workbook wb = new Workbook();
//Load the sample Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\Employees.xlsx");
//Determine if the workbook has tracked changes
if (wb.hasTrackedChanges())
{
//Accept tracked changes in the workbook
wb.acceptAllTrackedChanges();
}
//Save to file
wb.saveToFile("output/AcceptChanges.xlsx", FileFormat.Version2013);
}
}
Reject Tracked Changes in a Workbook
If the tracked changes have been proven to exist in a workbook, you can reject them using Workbook.rejectAllTrackedChanges() method. The following are the steps to achieve this.
- Create a Workbook object.
- Load the sample Excel document using Workbook.loadFromFile() method.
- Determine if the workbook has tracked changes by Workbook.hasTrackedChanegs() method.
- Reject all tracked changes using Workbook.rejectAllTrackedChanges() method.
- Save the document to another file using Workbook.saveToFile() method.
- Java
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
public class RejectTrackedChanges {
public static void main(String[] args) {
//Create a Workbook object
Workbook wb = new Workbook();
//Load the sample Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\Employees.xlsx");
//Determine if the workbook has tracked changes
if (wb.hasTrackedChanges())
{
//Reject tracked changes in the workbook
wb.rejectAllTrackedChanges();
}
//Save to file
wb.saveToFile("output/RejectChanges.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.
Java: Split a Worksheet into Several Excel Files
Splitting a worksheet can be beneficial when you have a large amount of data and want to organize it into separate files for easier management and sharing. By using this approach, you can organize and distribute your data in a more organized and structured manner. In this tutorial, we will demonstrate how to split a worksheet into multiple Excel documents by 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.11.0</version>
</dependency>
</dependencies>
Split a Worksheet into Several Excel Files
Spire.XLS for Java provides powerful features that enable us to achieve this task efficiently. The specific steps are as follows.
- Create a Workbook object.
- Load a sample Excel file using Workbook.loadFromFile() method.
- Get the specific sheet using Workbook.getWorksheets().get() method.
- Get the header row and cell ranges using Worksheet.getCellRange() method.
- Create a new workbook and copy the header row and range 1 to the new workbook using Worksheet.copy(CellRange sourceRange, Worksheet worksheet, int destRow, int destColumn, boolean copyStyle, boolean updateRerence) method.
- Copy the column width from the original workbook to the new workbook using Workbook.getWorksheets().get(0).setColumnWidth() method.
- Save the new workbook to an Excel file using Workbook.saveToFile() method.
- Repeat the above operation to copy the header row and range 2 to another new workbook, and save it to another Excel file.
- Java
import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class SplitWorksheet {
public static void main(String[] args) {
//Create a Workbook object to load the original Excel document
Workbook bookOriginal = new Workbook();
bookOriginal.loadFromFile("C:\\Users\\Administrator\\Desktop\\Emplyees.xlsx");
//Get the first worksheet
Worksheet sheet = bookOriginal.getWorksheets().get(0);
//Get the header row
CellRange headerRow = sheet.getCellRange(1, 1, 1, 5);
//Get two cell ranges
CellRange range1 = sheet.getCellRange(2, 1, 6, 5);
CellRange range2 = sheet.getCellRange(7, 1, 11, 5);
//Create a new workbook
Workbook newBook1 = new Workbook();
//Copy the header row and range 1 to the new workbook
sheet.copy(headerRow, newBook1.getWorksheets().get(0), 1, 1, true, false);
sheet.copy(range1, newBook1.getWorksheets().get(0), 2, 1, true, false);
//Copy the column width from the original workbook to the new workbook
for (int i = 0; i < sheet.getLastColumn(); i++) {
newBook1.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
}
//Save the new workbook to an Excel file
newBook1.saveToFile("Sales.xlsx", ExcelVersion.Version2016);
//Create another new workbook
Workbook newBook2 = new Workbook();
//Copy the header row and range 2 to the new workbook
sheet.copy(headerRow, newBook2.getWorksheets().get(0), 1, 1, true, false);
sheet.copy(range2, newBook2.getWorksheets().get(0), 2, 1, true, false);
//Copy the column width from the original workbook to another new workbook
for (int i = 0; i < sheet.getLastColumn(); i++) {
newBook2.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
}
//Save it to another new Excel file
newBook2.saveToFile("Technicians.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.
Java set Excel print page margins
This article demonstrates how to set Excel page margins before printing the Excel worksheets in Java applications. By using Spire.XLS for Java, we could set top margin, bottom margin, left margin, right margin, header margin, and footer margin. Please note that the unit for margin is inch on Spire.XLS for Java while On Microsoft Excel, it is cm (1 inch=2.54 cm).
import com.spire.xls.*;
public class setMargins {
public static void main(String[] args) {
String outputFile="output/setMarginsOfExcel.xlsx";
//Load the sample document from file
Workbook workbook = new Workbook();
workbook.loadFromFile("Sample.xlsx";);
//Get the first worksheet.
Worksheet sheet = workbook.getWorksheets().get(0);
//Get the PageSetup object of the first worksheet.
PageSetup pageSetup = sheet.getPageSetup();
//Set the page margins of bottom, left, right and top.
pageSetup.setBottomMargin(2);
pageSetup.setLeftMargin(1);
pageSetup.setRightMargin(1);
pageSetup.setTopMargin(3);
//Set the margins of header and footer.
pageSetup.setHeaderMarginInch(2);
pageSetup.setFooterMarginInch(2);
//Save to file.
workbook.saveToFile(outputFile, ExcelVersion.Version2013);
}
}
Output:
Java: Add Watermarks to Excel Worksheets
Branding your documents or indicating confidentiality can be easily achieved by adding a watermark to your Excel spreadsheet. Although Excel does not have a built-in feature for watermark insertion, there are alternative methods to achieve the desired effect.
One approach is to insert an image into the header or footer of your Excel worksheet, which can create a watermark-like appearance. Alternatively, setting an image as the background of your spreadsheet can also mimic the watermark effect.
This article demonstrates how to add header or background image watermarks to Excel in Java 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.11.0</version>
</dependency>
</dependencies>
Header Image Watermark vs. Background Image Watermark
Header Image Watermark
Pros:
- Ensures the watermark remains intact on the final printed document.
Cons:
- Invisible under "Normal" view mode in Excel, becoming visible only in "Page Layout" or "Page Break Preview" views.
- Requires precise adjustment of white margins, especially on the top and left sides of the image, for central placement.
Background Image Watermark
Pros:
- Provides a uniform watermark across the entire worksheet area.
Cons:
- Does not persist on the printed sheet, hence will not appear in the final printed output.
Add a Header Image Watermark to Excel in Java
Spire.XLS for Java offers the PageSetup class, providing control over various settings that impact the appearance and layout of a printed worksheet. This class includes the setCenterHeader() and setCenterHeaderImage() methods, which allow for the addition of an image to the worksheet header's center section.
Below are the steps to add a watermark to Excel using a header image in Java.
- Create a Workbook object.
- Load an Excel document from a give file path.
- Load an image using ImageIO.read() method.
- Get a specific worksheet from the workbook.
- Add an image field to the header center by passing "&G" as the parameter of PageSetup.setCenterHeader() method.
- Apply the image to the header center using PageSetup.setCenterHeaderImage() method.
- Save the workbook to a different Excel file.
- Java
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 AddWatermarkToExcelUsingHeaderImage {
public static void main(String[] args) throws IOException {
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel document
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Input3.xlsx");
// Load an image file
BufferedImage image = ImageIO.read( new File("C:\\Users\\Administrator\\Desktop\\confidential_3.jpg"));
// Loop through all worksheets in the file
for (int i = 0; i < workbook.getWorksheets().getCount(); i++)
{
// Get a specific worksheet
Worksheet worksheet = workbook.getWorksheets().get(i);
// Add an image field to the header center
worksheet.getPageSetup().setCenterHeader("&G");
// Add the image to the header center
worksheet.getPageSetup().setCenterHeaderImage(image);
}
// Save the result file
workbook.saveToFile("AddWatermark.xlsx", ExcelVersion.Version2016);
// Dispose resources
workbook.dispose();
}
}
Add a Background Image Watermark to Excel in Java
The PageSetup class includes a method called setBackgroundImage(), enabling you to designate an image as the background for a worksheet.
Here are the steps to add a watermark to Excel using a background image in Java.
- Create a Workbook object.
- Load an Excel document from a give file path.
- Load an image using ImageIO.read() method.
- Get a specific worksheet from the workbook.
- Apply the image to the worksheet as the background using PageSetup.setBackgroundImage() method.
- Save the workbook to a different Excel file.
- Java
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 AddWatermarkToExcelUsingBackgroundImage {
public static void main(String[] args) throws IOException {
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel document
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");
// Load an image file
BufferedImage image = ImageIO.read( new File("C:\\Users\\Administrator\\Desktop\\sample.png"));
// Loop through all worksheets in the file
for (int i = 0; i < workbook.getWorksheets().getCount(); i++)
{
// Get a specific worksheet
Worksheet worksheet = workbook.getWorksheets().get(i);
// Set the image as the background of the worksheet
worksheet.getPageSetup().setBackgoundImage(image);
}
// Save the result file
workbook.saveToFile("AddWatermark.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.
Java: Add or Delete Page Breaks in Excel
Page breaks in Excel are dividers that separate a large worksheet into individual pages for printing. In this article, you will learn how to add or delete page breaks in Excel in Java using Spire.XLS for Java library.
- Add Page Breaks to Excel in Java
- Delete a Specific Page Break from Excel in Java
- Delete All Page Breaks from 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>14.11.0</version>
</dependency>
</dependencies>
Add Page Breaks to Excel in Java
Using Spire.XLS for Java, you can add horizontal and vertical page breaks to an Excel worksheet. Below are the steps to do so:
- Create an instance of Workbook class.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the desired worksheet by its index using Workbook.getWorksheets().get() method.
- Specify the cells where you want to add page breaks to using Worksheet.getRange().get() method.
- Add horizontal and vertical page breaks to the cells using Worksheet.getHPageBreaks().add() and Worksheet.getVPageBreaks().add() methods.
- Set the sheet view mode to ViewMode.Preview using Worksheet.setViewMode() method.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class AddPageBreaks {
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);
//Specify the cells where you want to add page breaks to
CellRange cell1 = sheet.getRange().get("A10");
CellRange cell2 = sheet.getRange().get("F1");
//Add a horizontal page break
sheet.getHPageBreaks().add(cell1);
//Add a vertical page break
sheet.getVPageBreaks().add(cell2);
//Set view mode to Preview in order to view the page breaks
sheet.setViewMode(ViewMode.Preview);
//Save the result file
workbook.saveToFile("AddPageBreaks.xlsx", ExcelVersion.Version2013);
}
}
Delete a Specific Page Break from Excel in Java
The following are the steps to delete a specific page break from an Excel worksheet:
- Create an instance of Workbook class.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the desired worksheet by its index using Workbook.getWorksheets().get() method.
- Delete a specific horizontal or vertical page break from the worksheet by its index using Worksheet.getHPageBreaks().removeAt() or Worksheet.getVPageBreaks().removeAt() method.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class DeleteASpecificPageBreak {
public static void main(String []args) {
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("AddPageBreaks.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Delete the first horizontal page break
sheet.getHPageBreaks().removeAt(0);
//Delete the first vertical page break
sheet.getVPageBreaks().removeAt(0);
//Save the result file
workbook.saveToFile("DeleteASpecificPageBreaks.xlsx", ExcelVersion.Version2013);
}
}
Delete All Page Breaks from Excel in Java
The following are the steps to delete all the page breaks from an Excel worksheet:
- Create an instance of Workbook class.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the desired worksheet by its index using Workbook.getWorksheets().get() method.
- Delete all the horizontal and vertical page breaks from the worksheet using Worksheet.getHPageBreaks().clear() and Worksheet.getVPageBreaks().clear() methods.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class DeleteAllPageBreaks {
public static void main(String []args) {
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("AddPageBreaks.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Delete all horizontal page breaks
sheet.getHPageBreaks().clear();
//Delete all vertical page breaks
sheet.getVPageBreaks().clear();
//Save the result file
workbook.saveToFile("DeleteAllPageBreaks.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.
Java: Set Background Color and Image for Excel in Java
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.
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>14.11.0</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.
- Java
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.
- Java
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.
Java: Add Document Properties to Excel
Adding document properties to an Excel file is a simple and convenient way to provide additional context and information about the file. Document properties can be either standard or custom. Standard document properties, such as author, title, and subject, offer basic information about the file and make it easier to locate and identify. Custom document properties allow users to add specific details about the file, such as project name, client name, or department owner, providing relevant information and context to the data presented in the file. In this article, we will demonstrate how to add standard document properties and custom document properties to an Excel file in Java using Spire.XLS for Java library.
- Add Standard Document Properties to an Excel File in Java
- Add Custom Document Properties to an Excel File 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>14.11.0</version>
</dependency>
</dependencies>
Add Standard Document Properties to an Excel File in Java
Standard document properties are pre-defined by Microsoft Excel and include fields such as Title, Subject, Author, Keywords, and Comments. The following steps demonstrate how to add standard document properties to an Excel file in Java using Spire.XLS for Java:
- Initialize an instance of the Workbook class.
- Load an Excel file using the Workbook.loadFromFile(String fileName) method.
- Add standard document properties, such as title, subject and author to the file using the Workbook.getDocumentProperties().setTitle(String value), Workbook.getDocumentProperties().setSubject(String value), Workbook.getDocumentProperties().setAuthor(String value) methods.
- Save the result file using the Workbook.saveToFile(String fileName, ExcelVersion version) method.
- Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
public class AddStandardDocumentProperties {
public static void main(String[] args) {
//Initialize an instance of the Workbook class
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("Input.xlsx");
//Add standard document properties to the file
workbook.getDocumentProperties().setTitle("Add Document Properties");
workbook.getDocumentProperties().setSubject("Spire.XLS for Java Demo");
workbook.getDocumentProperties().setAuthor("Shaun");
workbook.getDocumentProperties().setManager("Bill");
workbook.getDocumentProperties().setCompany("E-iceblue");
workbook.getDocumentProperties().setCategory("Spire.XLS for Java");
workbook.getDocumentProperties().setKeywords("Excel Document Properties");
//Save the result file
workbook.saveToFile("AddStandardDocumentProperties.xlsx", ExcelVersion.Version2016);
workbook.dispose();
}
}
Add Custom Document Properties to an Excel File in Java
Custom document properties are user-defined and can be tailored to suit specific needs or requirements. The data type of the custom document properties can be Yes or No, Text, Number, and Date. The following steps demonstrate how to add custom document properties to an Excel file in Java using Spire.XLS for Java:
- Initialize an instance of the Workbook class.
- Load an Excel file using the Workbook.loadFromFile(String fileName) method.
- Add a custom document property of "Yes or No" type to the file using the Workbook.getCustomDocumentProperties().add(String var1, boolean var2) method.
- Add a custom document property of "Text" type to the file using the Workbook.getCustomDocumentProperties().add(String var1, String var2) method.
- Add a custom document property of "Number" type to the file using the Workbook.getCustomDocumentProperties().add(String var1, int var2) method.
- Add a custom document property of "Date" type to the file using the Workbook.getCustomDocumentProperties().add(String var1, Date var2) method.
- Save the result file using the Workbook.saveToFile(String fileName, ExcelVersion version) method.
- Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import java.util.Date;
public class AddCustomDocumentProperties {
public static void main(String[] args) {
//Initialize an instance of the Workbook class
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("Input.xlsx");
//Add a “yes or no” custom document property
workbook.getCustomDocumentProperties().add("Revised", true);
//Add a “text” custom document property
workbook.getCustomDocumentProperties().add("Client Name", "E-iceblue");
//Add a “number” custom document property
workbook.getCustomDocumentProperties().add("Phone number", 81705109);
//Add a “date” custom document property
workbook.getCustomDocumentProperties().add("Revision date", new Date());
//Save the result file
workbook.saveToFile("AddCustomDocumentProperties.xlsx", 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.
Java: Merge Excel Files into One
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.
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>14.11.0</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.
- Java
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.
- Java
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.