When working with Excel spreadsheets, you may need to add information that doesn't belong directly in a cell, such as the reason for a price change, an explanation of an unusual value, or a reminder to verify certain data.
Excel comments let you attach these notes to individual cells without changing their values. You can also use Java to add, read, update, or remove them programmatically.
This article covers these four operations with complete Java examples.
1. Add the Maven Dependency
To get started, add an Excel processing library to your Java project. If you're using Maven, include the following repository and dependency in your 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.free</artifactId>
<version>16.3.1</version>
</dependency>
</dependencies>
2. Add Comments to Excel Cells in Java
To add a comment to an Excel cell, use the CellRange.addComment() method. It returns an ExcelComment object, which you can use to set the comment text and adjust its appearance.
For the examples below, we'll use a workbook named sales.xlsx, with the following data on its first worksheet:
| Product | Quantity | Unit Price | Amount |
|---|---|---|---|
| Product A | 20 | 49 | 980 |
| Product B | 15 | 60 | 900 |
The table occupies cells A1:D3. We'll add a note explaining the price in C2 and another reminding us to verify the amount in D3:
import com.spire.xls.*;
public class AddExcelComments {
public static void main(String[] args) {
Workbook workbook = new Workbook();
try {
// Load the Excel file
workbook.loadFromFile("sales.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Add a comment to cell C2
ExcelComment comment1 =
sheet.getCellRange("C2").addComment();
comment1.setText(
"The unit price was updated this month.");
// Set the comment box size
comment1.setWidth(220);
comment1.setHeight(60);
// Keep the comment box hidden by default
comment1.setVisible(false);
// Add a comment to cell D3
ExcelComment comment2 =
sheet.getCellRange("D3").addComment();
comment2.setText(
"Please verify this amount.");
comment2.setAutoSize(true);
// Save the modified workbook
workbook.saveToFile(
"sales-with-comments.xlsx",
ExcelVersion.Version2016);
} finally {
workbook.dispose();
}
}
}
The program creates sales-with-comments.xlsx, with a comment attached to each of the two cells.
Besides setting the text, you can control how a comment appears:
setWidth()andsetHeight()specify the comment box dimensions.setAutoSize(true)adjusts the box size to fit its text.setVisible(false)keeps the comment box from being displayed continuously.
Setting a comment's visibility to false doesn't remove it. The comment remains attached to the cell and can still be viewed in Excel.
3. Read Comments from an Excel Worksheet
You may also need to inspect comments already stored in a workbook, such as when checking notes left during a review.
The Worksheet.getComments() method provides access to the comments collection for a worksheet. You can iterate through it and retrieve each comment's text and location.
The following example reads all comments from the workbook created in the previous section:
import com.spire.xls.*;
public class ReadExcelComments {
public static void main(String[] args) {
Workbook workbook = new Workbook();
try {
// Load the Excel file
workbook.loadFromFile(
"sales-with-comments.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Get the number of comments
int count = sheet.getComments().getCount();
System.out.println("Total comments: " + count);
// Read each comment
for (int i = 0; i < count; i++) {
ExcelComment comment =
sheet.getComments().get(i);
System.out.println("Comment " + (i + 1));
System.out.println("Row: " + comment.getRow());
System.out.println("Column: " + comment.getColumn());
System.out.println("Text: " + comment.getText());
System.out.println("----------------");
}
} finally {
workbook.dispose();
}
}
}
The program prints the number of comments and the row, column, and text of each one.
If you already know which cell contains the comment, there's no need to loop through the entire collection. You can retrieve it directly using getComment():
ExcelComment comment =
sheet.getCellRange("C2").getComment();
if (comment != null) {
System.out.println(comment.getText());
}
The null check matters because not every cell has a comment. It prevents a NullPointerException when the requested cell has none.
4. Edit an Existing Excel Comment
Comments sometimes need to be updated as the information in a spreadsheet changes.
For example, suppose the unit price in C2 has now been verified. We can replace the original comment with "The current unit price has been verified."
There's no need to remove the existing comment and create a new one. Simply retrieve it with getComment() and update its text using setText().
import com.spire.xls.*;
public class EditExcelComment {
public static void main(String[] args) {
Workbook workbook = new Workbook();
try {
// Load the workbook containing comments
workbook.loadFromFile(
"sales-with-comments.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Get the existing comment in C2
ExcelComment comment =
sheet.getCellRange("C2").getComment();
// Update the comment
if (comment != null) {
comment.setText(
"The current unit price has been verified.");
// Resize the comment box to fit the text
comment.setAutoSize(true);
}
// Save the updated workbook
workbook.saveToFile(
"sales-edited-comments.xlsx",
ExcelVersion.Version2016);
} finally {
workbook.dispose();
}
}
}
After running the code, open sales-edited-comments.xlsx to see the updated comment in C2.
Only the comment text and its box sizing are changed. The cell's value remains the same.
You can also modify other comment properties, such as its dimensions, visibility, and text formatting, without recreating the comment.
5. Delete Comments from Excel Cells
Once a note is no longer needed, you can remove it from the worksheet.
Suppose the amount in D3 has been checked, and the reminder to verify it is no longer relevant.
Use getComment() to retrieve the comment, then call remove() to delete it.
import com.spire.xls.*;
public class DeleteExcelComment {
public static void main(String[] args) {
Workbook workbook = new Workbook();
try {
// Load the workbook containing comments
workbook.loadFromFile(
"sales-with-comments.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Get the comment in cell D3
ExcelComment comment =
sheet.getCellRange("D3").getComment();
// Remove the comment if it exists
if (comment != null) {
comment.remove();
}
// Save the modified workbook
workbook.saveToFile(
"sales-removed-comment.xlsx",
ExcelVersion.Version2016);
} finally {
workbook.dispose();
}
}
}
The resulting file, sales-removed-comment.xlsx, no longer contains the comment in D3. The value stored in that cell is preserved, as are the comments in other cells.
If you need to remove comments from several known cells, you can repeat the same operation for each one.
6. A Few Things to Keep in Mind
Traditional Comments vs. Threaded Comments
The examples in this article work with traditional Excel cell comments, which are called Notes in newer versions of Microsoft Excel.
These are different from modern threaded comments, which support replies and @mentions. The APIs shown here should not be assumed to work with threaded comments.
Check Whether a Comment Already Exists
Before adding a comment to a cell, consider whether it already has one.
If your goal is to update an existing note, use getComment() followed by setText() rather than creating another comment.
Save Changes to the Workbook
Adding, editing, or deleting a comment changes the workbook in memory. To write those changes to an Excel file, you must call workbook.saveToFile().
The examples save their results to new files so that the original workbook remains unchanged. If you need to overwrite an existing file, it's worth keeping a backup.
Comments
Post a Comment