Java : Add Comment XLS File Apache POI Example

In this blog post, we will see how to insert comments into Excel 97 - 2003 format workbooks, using Apache POI and Java, with an example program. This is in continuation to our last article, that explained about adding comments in XLSX (2007 and above) spreadsheets.You would need Apache POI and Java to try the example provided in this post. We will also see how to apply formatting to the comments like italics, underline comment text, change comment text font in the sample code. The code is tested working, please feel free to customize it to suit to your needs - and post us a comment if you have any questions.

XLS File - Add Comments Using Apache POI - Java Example

The full working Java example is provided below. The code is commented for convenience and you can follow through it easily.

 import java.io.FileOutputStream;
 import java.io.IOException;
 import org.apache.poi.hssf.usermodel.*;
 import org.apache.poi.ss.usermodel.IndexedColors;
 public class xlsCellComments
 {
   public static void main(String[] args)
     throws IOException
   {
         /* Step -1: Create a Excel 97 - 2003 compatible workbook / Add a worksheet */
     HSSFWorkbook xlsComment = new HSSFWorkbook();     
     HSSFCreationHelper richTextFactory = xlsComment.getCreationHelper();     
     HSSFSheet sheet = xlsComment.createSheet();
     /* Step -2: Create some Cells with Data so we can comment on */
     HSSFCell cell1 = sheet.createRow(4).createCell(8);
     cell1.setCellValue("Cell Commenting Through Java POI Example");
     HSSFCell cell2 = sheet.createRow(2).createCell(2);
     cell2.setCellValue("Cell Comments in XLS - Formatting Options");
         /* Step-3: Create Drawing Object to hold comment */
     HSSFPatriarch drawing = sheet.createDrawingPatriarch();     
         /* Step-4: Increase Comment Box Size - two anchor objects for two comments */   
     HSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 7, 8);
         HSSFClientAnchor anchor2 = drawing.createAnchor(0, 0, 0, 0, 0, 2, 6, 8);
         /* Step-5: Create the Comment Object */
     HSSFComment comment1 = drawing.createCellComment(anchor);
         HSSFComment comment2 = drawing.createCellComment(anchor2);
         /* Step-6: Rich Text Format Comments in Excel */
         HSSFRichTextString rtf1 = richTextFactory.createRichTextString("Let us apply some Rich Text Formatting to Comments now");
         /* Tip - to add new line include \n */
     comment1.setString(rtf1);
         /* Define author */
     comment1.setAuthor("Author1");
         /* Add comment to cell */
     cell1.setCellComment(comment1);
         HSSFRichTextString rtf2 = richTextFactory.createRichTextString("Another comment on a different cell");
     HSSFFont commentFormatter = xlsComment.createFont();
         /* Specify a custom font for the comment */
     commentFormatter.setFontName("Arial");
         /* Change Comment style to italics */
     commentFormatter.setItalic(true);
         /* Set Double Underline */
     commentFormatter.setUnderline(HSSFFont.U_DOUBLE);
         /* Set a custom comment color */
     commentFormatter.setColor(IndexedColors.RED.getIndex());
         /* Apply all the formatting options to the rich text string */
     rtf2.applyFont(commentFormatter);
     /* Add comment string */
     comment2.setString(rtf2);
         /* Set author */
     comment2.setAuthor("Author2");
         /* Set the column of the Cell that contains the comment */
     comment2.setColumn(2);
         /* Set the row of the cell that contains the comment */
     comment2.setRow(2);
     /* Write the output to a file */
     String fname = "comments.xls";
     FileOutputStream out = new FileOutputStream(fname);
     xlsComment.write(out);
     out.close();
   }
 }

The output of this program is provided below:

XLS Add Comment- Java POI - Example Output

XLS Insert Comment With Formatting - Java Example Program Output-2

Add Comment Excel Sheet Java POI Example

In this blog post, we will discuss how to add / Insert comments to cells in an Excel (both XLS / XLSX) workbook using Apache POI and Java with an example program. We will also explore the possibilities of using rich text formatting on the comments and discuss the following;

• Using different fonts
• Varying font height / weight
• Changing Font Color
• Setting Author name against the comments
• Adding new line characters inside a comment
• Rich Text Format only specific parts of a comment
• Varying the size of the comments box in POI

We will write a single Java program that explains how to do this all in one go. The Java code is commented well enough for you to understand how adding comments work in POI. Follow the code given below:

Excel (XLSX) - Add Comments - Java POI Example Program

 import java.io.FileOutputStream;
 import java.io.IOException;
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 import org.apache.poi.xssf.usermodel.*;
 import org.apache.poi.ss.usermodel.IndexedColors;
 public class InsertCellComments
 {
   public static void main(String[] args)
     throws IOException
   {
         /* Start with a workbook object to write comments into */
     XSSFWorkbook xlsx_comments = new XSSFWorkbook();
     /* Now add a worksheet into the workbook */
     XSSFCreationHelper richTextFactory = xlsx_comments.getCreationHelper();     
     XSSFSheet sheet = xlsx_comments.createSheet();
     /* Create a CELL with some value so we can comment on */
     XSSFCell cell1 = sheet.createRow(3).createCell(5);
     cell1.setCellValue("Some value here");
     
         /* Create Drawing Object to hold comment */
     XSSFDrawing drawing = sheet.createDrawingPatriarch();     
         /* Let us draw a big comment box to hold lots  of comment data */
     XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
     /* Create a comment object */
     XSSFComment comment1 = drawing.createCellComment(anchor);
         /* Create some comment text as Rich Text String */
     XSSFRichTextString rtf1 = richTextFactory.createRichTextString("I'm going to type a big comment in this box and see how it appears. And some newline characters also \n\n new line \n newline");
         /* You can also insert newline character in comments using \n */
     comment1.setString(rtf1);
         /* Define the author for the comments,you can set each comment to have a different author */
     comment1.setAuthor("Thinktibits");
         /* Stamp the comment to the cell */
     cell1.setCellComment(comment1);
         /* Let us now define another comment and explore some more options */
         /* Create another Cell */
     XSSFCell cell2 = sheet.createRow(2).createCell(2);
     cell2.setCellValue("Some more value");
     XSSFClientAnchor anchor2 = drawing.createAnchor(0, 0, 0, 0, 0, 2, 6, 8);
     XSSFComment comment2 = drawing.createCellComment(anchor2);
     XSSFRichTextString rtf2 = richTextFactory.createRichTextString("Formatting options for comments in Apache POI");
     XSSFFont commentFormatter = xlsx_comments.createFont();
         /* Specify a custom font for comment text in Apache POI */
     commentFormatter.setFontName("Verdana");
         /* Specify a custom font height */
     commentFormatter.setFontHeightInPoints((short)14);
         /* Specify bold weight of the font */
     commentFormatter.setBoldweight((short)700);
         /* Set a custom comment color */
     commentFormatter.setColor(IndexedColors.BLUE.getIndex());
         /* Apply all the formatting options to the rich text string */
     rtf2.applyFont(commentFormatter);
     /* Add comment string */
     comment2.setString(rtf2);
         /* Set author */
     comment2.setAuthor("Apache POI");
         /* Set the column of the Cell that contains the comment */
     comment2.setColumn(2);
         /* Set the row of the cell that contains the comment */
     comment2.setRow(2);
     /* Write the output to a file */
     String fname = "comments.xlsx";
     FileOutputStream out = new FileOutputStream(fname);
     xlsx_comments.write(out);
     out.close();
   }
 }

The code above creates an Excel spreadsheet in XLSX format, with two cells with comments included in it. This can be seen from the output screenshots below:

Comment 1- XLSX Comment Apache POI Example 

Formatting Options in Comments - Apache POI Example

In the next post, we will see how to add comments to XLS format versions with suitable examples.

Apache POI XLSX Line Chart Java Example Program

In some of our blog posts earlier, we discussed how to generate charts in Excel workbook using Apache POI and JFreechart library. With POI 3.10, it is now possible to create charts directly in POI and in this post, let us discuss how to create a simple line chart in Apache POI with examples. The benefit of creating charts directly in POI are that we are not dependent on external libraries like JFreechart and we can also edit our charts on the fly if required. In the JFreechart approach, we had to embed the chart as a picture object into Excel that is no longer the case. So, download POI 3.10 and have all the JAR files required in your class path and we are ready to start with the basic Java example.

Java Example - Create Line Chart - Apache POI 

 import java.io.FileOutputStream;
 import org.apache.poi.ss.usermodel.*;
 import org.apache.poi.ss.usermodel.charts.*;
 import org.apache.poi.ss.util.CellRangeAddress;
 import org.apache.poi.xssf.usermodel.*;
 import org.apache.poi.xssf.usermodel.charts.*;
 /* Line Chart Example in Apache POI */
 public class LineChart
 {
   public static void main(String[] args)
     throws Exception
   {
                         /* Create a Workbook object that will hold the final chart */
                        XSSFWorkbook my_workbook = new XSSFWorkbook();
                         /* Create a worksheet object for the line chart. This worksheet will contain the chart */
                        XSSFSheet my_worksheet = my_workbook.createSheet("LineChart_Example");
                        
                        /* Let us now create some test data for the chart */
                        /* Later we can see how to get this test data from a CSV File or SQL Table */
                        /* We use a 4 Row chart input with 5 columns each */
                        for (int rowIndex = 0; rowIndex < 4; rowIndex++)
                {
                        /* Add a row that contains the chart data */
                        XSSFRow my_row = my_worksheet.createRow((short)rowIndex);
                        for (int colIndex = 0; colIndex < 5; colIndex++)
                {
                        /* Define column values for the row that is created */
                        XSSFCell cell = my_row.createCell((short)colIndex);
                        cell.setCellValue(colIndex * (rowIndex + 1));
                }
                }               
                        /* At the end of this step, we have a worksheet with test data, that we want to write into a chart */
                        /* Create a drawing canvas on the worksheet */
                        XSSFDrawing xlsx_drawing = my_worksheet.createDrawingPatriarch();
                        /* Define anchor points in the worksheet to position the chart */
                        XSSFClientAnchor anchor = xlsx_drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
                        /* Create the chart object based on the anchor point */
                        XSSFChart my_line_chart = xlsx_drawing.createChart(anchor);
                        /* Define legends for the line chart and set the position of the legend */
                        XSSFChartLegend legend = my_line_chart.getOrCreateLegend();
                        legend.setPosition(LegendPosition.BOTTOM);     
                        /* Create data for the chart */
                        LineChartData data = my_line_chart.getChartDataFactory().createLineChartData();     
                        /* Define chart AXIS */
                        ChartAxis bottomAxis = my_line_chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
                        ValueAxis leftAxis = my_line_chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
                        leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);     
                        /* Define Data sources for the chart */
                        /* Set the right cell range that contain values for the chart */
                        /* Pass the worksheet and cell range address as inputs */
                        /* Cell Range Address is defined as First row, last row, first column, last column */
                        ChartDataSource<Number> xs = DataSources.fromNumericCellRange(my_worksheet, new CellRangeAddress(0, 0, 0, 4));
                        ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(my_worksheet, new CellRangeAddress(1, 1, 0, 4));
                        ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(my_worksheet, new CellRangeAddress(2, 2, 0, 4));
                        ChartDataSource<Number> ys3 = DataSources.fromNumericCellRange(my_worksheet, new CellRangeAddress(3, 3, 0, 4));
                        /* Add chart data sources as data to the chart */
                        data.addSerie(xs, ys1);
                        data.addSerie(xs, ys2);
                        data.addSerie(xs, ys3);
                        /* Plot the chart with the inputs from data and chart axis */
                        my_line_chart.plot(data, new ChartAxis[] { bottomAxis, leftAxis });
                        /* Finally define FileOutputStream and write chart information */               
                        FileOutputStream fileOut = new FileOutputStream("xlsx-line-chart.xlsx");
                        my_workbook.write(fileOut);
                        fileOut.close();
   }
 }

Note that this example is for XLSX format, and you can easily change this to support the XLS format is required. Just change the class file names suitable to point to XLS format and you are done.

A sample output of this program is provided below:

POI-3.10- Java Line Chart Example Program - Output

Try your chart with this new approach and tell us how it works.

Convert CSV to PDF– Java Program Example

In this series, we are providing examples for converting a CSV file to PDF using Java. In this part of the series, we will provide a standalone program in Java that will do the following:

• Read input CSV file
• Create PDF document
• Populate a PDF table from the contents of the CSV file
• Writes PDF file to output

Each of these steps is described briefly now. The full Java program is provided at the end of this post.

Read Input CSV File

In this step, we will use opencsv-2.3.jar file, and read the input CSV file data into the program memory. The program assumes the CSV file has two columns, even though you can make that dynamic depending on your needs. A code fragment that reads a CSV file is given below:

                /* Step -1 : Read input CSV file in Java */
                String inputCSVFile = "csv_to_pdf.csv";
                CSVReader reader = new CSVReader(new FileReader(inputCSVFile));

Create PDF document

We will use itextpdf-5.3.4.jar and create a Document object, and attach it to a PdfWriter object at this step. This will create a PDF file for us and we can start writing the contents of the CSV file to the PDF following this. We will also create iText table objects that maps to a physical PDF table and will hold CSV file data. The code fragment is provided below:

                /* Step-2: Initialize PDF documents - logical objects */
                Document my_pdf_data = new Document();
                PdfWriter.getInstance(my_pdf_data, new FileOutputStream("converted_PDF_File.pdf"));
                my_pdf_data.open();            
                PdfPTable my_first_table = new PdfPTable(2);
                PdfPCell table_cell;

Populate PDF table from CSV data

In this step, we will loop through the CSV file line by line and dump the contents of this file into a PDF table. We will use Pdfptable object to capture CSV file data and attach this table to the PDF document created earlier.

                /* Step -3: Loop through CSV file and populate data to PDF table */
                while ((nextLine = reader.readNext()) != null) {
                        lnNum++;        
                        table_cell=new PdfPCell(new Phrase(nextLine[0]));
                        my_first_table.addCell(table_cell);
                        table_cell=new PdfPCell(new Phrase(nextLine[1]));
                        my_first_table.addCell(table_cell);                                             
                }

Write PDF file to output

Once all the lines in the CSV file are read, we close the PDF document and the write the output to a file. The CSV file opened for reading can also be closed at this point. We also attach the logical PDF table to Document object at this step. This ensures the table is written to PDF.

                /* Step -4: Attach table to PDF and close the document */
                my_pdf_data.add(my_first_table);                       
                my_pdf_data.close();            

CSV to PDF – Complete Java Program

The complete Java program that converts a CSV file to a table in PDF document is provided below:

import java.io.FileOutputStream;
import java.io.*;
import au.com.bytecode.opencsv.CSVReader;
import java.io.FileReader;
import java.util.*;
import com.itextpdf.text.*;
import com.itextpdf.text.pdf.*;
public class csv2pdf {  
        public static void main(String[] args) throws Exception{
                
                /* Step -1 : Read input CSV file in Java */
                String inputCSVFile = "csv_to_pdf.csv";
                CSVReader reader = new CSVReader(new FileReader(inputCSVFile));
                /* Variables to loop through the CSV File */
                String [] nextLine; /* for every line in the file */            
                int lnNum = 0; /* line number */
                /* Step-2: Initialize PDF documents - logical objects */
                Document my_pdf_data = new Document();
                PdfWriter.getInstance(my_pdf_data, new FileOutputStream("converted_PDF_File.pdf"));
                my_pdf_data.open();            
                PdfPTable my_first_table = new PdfPTable(2);
                PdfPCell table_cell;
                /* Step -3: Loop through CSV file and populate data to PDF table */
                while ((nextLine = reader.readNext()) != null) {
                        lnNum++;        
                        table_cell=new PdfPCell(new Phrase(nextLine[0]));
                        my_first_table.addCell(table_cell);
                        table_cell=new PdfPCell(new Phrase(nextLine[1]));
                        my_first_table.addCell(table_cell);                                             
                }
                /* Step -4: Attach table to PDF and close the document */
                my_pdf_data.add(my_first_table);                       
                my_pdf_data.close();            
        }
}

CSV to PDF – Output Snapshot

A snapshot of the output created by the program above is given below:

CSV to PDF - Java Program Output - Example

That completes the standalone Java program example for the tutorial. In the next part, we will examine how to create a servlet for this conversion. Related links to this series are available below:

This series: Convert CSV to PDF using Java
Keywords: Convert CSV to PDF, Java Example Program, OpenCSV, iText, Java Servlet Example
All Parts:
1. Convert CSV to PDF– Standard Java Program Example
2. CSV to PDF Servlet Conversion – HTML Form Design
3. CSV to PDF Converter – Full Java Servlet Program