Writing to a worksheet

In this post under Apache Excel, I will with example how to write to a worksheet.

For our example, we will use a text file “Input1.txt” to read the data to be written to worksheet.

The “Input1.txt” file contains comma separated values as shown below


Below is the complete code for your reference.

Main Class

1  package package4;
3  import java.io.BufferedReader;
4  import java.io.File;
5  import java.io.FileOutputStream;
6  import java.io.FileReader;
7  import java.io.IOException;
9  import org.apache.poi.xssf.usermodel.XSSFCell;
10 import org.apache.poi.xssf.usermodel.XSSFRow;
11 import org.apache.poi.xssf.usermodel.XSSFSheet;
12 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
14 public class Example4 {
15     public static void main(String[] args) throws IOException {
16         File outputFile = new File("Example3.xlsx");
17         File inputFile = new File("Input1.txt");
18         try(XSSFWorkbook workbook = new XSSFWorkbook();
19             FileOutputStream fileOutputStream = new FileOutputStream(outputFile);
20             FileReader fileReader = new FileReader(inputFile);
21             BufferedReader bufferedReader = new BufferedReader(fileReader)) {
22             XSSFSheet xssfSheet = workbook.createSheet("Page1");
23             String line = bufferedReader.readLine();
24             int rowNumber = 0;
25             while(line != null) {
26                 XSSFRow xssfRow = xssfSheet.createRow(rowNumber);
27                 String[] columns = line.split(",");
28                 for(int j = 0; j < columns.length; j++) {
29                     XSSFCell xssfCell = xssfRow.createCell(j);
30                     xssfCell.setCellValue(columns[j]);
31                 }
32                 line = bufferedReader.readLine();
33                 rowNumber++;
34             }
35             workbook.write(fileOutputStream);
36         }
37     }
38 }

In the above code, at line 16, we create a File object for a new file “Example3.xlsx”. It will be an excel file that we will create.

At line 17, we create a File object for existing file “Input1.txt”. It will be input file from which we will read the comma separated values.

In try with resources block, we create the following resources
1) XSSFWorkbook
2) FileOutputStream
3) BufferedReader

At line 22, in XSSFWorkbook instance, we create a sheet by name “Page1” by calling “createSheet” method.

We read the input file line by line in a while loop.

At line 26, for each line in the input file, we create a row by calling “createRow” available on “XSSFSheet” instance.

Each line has string data separated by comma. We split the string data into columns at line 27.

For each column in a row, we create a cell by calling “createCell” on “XSSFRow” instance and set its value. Refer to line 29 and 30.

At the end we write the entire data to excel file by calling “write” method on “XSSFWorkbook” instance. Refer to line 35.

In this way we can write data to an excel sheet.

Leave a Reply