Add, Read and Delete Comments in Excel in Java

#java #excel #comments

A comment is a rich text note which we often use to add tips or additional information to a particular Excel cell. Free Spire.XLS for Java library provides Java developers the ability to add and manipulate comments in Excel files within Java applications. In this article, we will introduce how to add, read and delete comments in Excel using Free Spire.XLS for Java library.

Installation

First of all, you need to download spire.xls.jar and add it to your project as dependency. If you use maven, you need to add the following dependencies to your pom.xml file.

<repositories>
        <repository>
            <id>com.e-iceblue</id>
            <name>e-iceblue</name>
            <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
        </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>2.2.0</version>
    </dependency>
</dependencies>

Add Comments

The following example shows how to add a comment to an Excel file and format individual characters in the comment text with different colors using free Spire.XLS for Java.

import com.spire.xls.*;

public class AddComments {
    public static void main(String[] args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Set worksheet name
        sheet.setName("Comments");

        //Add text to cell [1,1]
        CellRange range = sheet.getCellRange(1,1);
        range.setText("Working with comment:");
        //Add text to cell [5,1]
        CellRange range1 = sheet.getCellRange(5, 1);
        range1.setText("Comment");
        //Add comment to cell [5,1]
        range1.getComment().setText("It's a comment.\nmulti-line,\nvisible and\nformatted.");
        //Show comment
        range1.getComment().setVisible(true);
        //Set comment hight
        range1.getComment().setHeight(100);
        //Create fonts and set font color
        ExcelFont fontBlue = workbook.createFont();
        fontBlue.setKnownColor(ExcelColors.LightBlue);
        ExcelFont fontGreen = workbook.createFont();
        fontGreen.setKnownColor(ExcelColors.LightGreen);
        //Set fonts for individual characters within the comment text
        range1.getComment().getRichText().setFont(0, 4, fontGreen);
        range1.getComment().getRichText().setFont(5, 6, fontBlue);
        range1.getComment().getRichText().setFont(7, 15, fontGreen);

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

Read Comments

Free Spire.XLS for Java supports reading all of the comments as well as reading a particular comment associated with a specified cell in an Excel worksheet.

import com.spire.xls.*;

public class ReadComments {
    public static void main(String[] args){
        //Load Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("AddComments.xlsx");
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Print out all of the comments in the worksheet
        for(int i = 0; i < sheet.getComments().getCount(); i ++){
            String comment = sheet.getComments().get(i).getText();
            System.out.println(comment);
        }

        //Print out comment associated with a specified cell
        //System.out.println(sheet.getCellRange(5,1).getComment().getText());
    }
}

Delete Comments

We can either delete all of the comments or delete a particular comment associated with a specified cell in an Excel worksheet.

import com.spire.xls.*;

public class DeleteComments {
    public static void main(String[] args){
        //Load Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("AddComments.xlsx");
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Delete all of the comments
        for(int i = 0; i < sheet.getComments().getCount(); i ++){
            sheet.getComments().get(i).remove();
        }

        //Delete comment associated with a specified cell
        sheet.getCellRange(5,1).getComment().remove();

        workbook.saveToFile("DeleteComments.xlsx", ExcelVersion.Version2013);
    }
}