How to append data in Excel using Java

How to append data in Excel using Java

by

Java is a well-known programming language that is used to perform various tasks. However, a limited number of people can use it together with Excel. Java is compatible with Excel and thus can be used to make any changes on an Excel document. To the new users, it may be a bit challenging and confusing due to the code lines associated with the Java language. Don't be stressed anymore. This article got you covered.

Let us now discuss how to use java on your Excel document.

Creating new sheets in Excel using data

when using Java code lines on Excel, you need to have a third-party application. In our case. We will use the Apache POI. Apache POI allows the Excel user to read and write files in Microsoft formats.

Steps to create a new file using java are;

1. Download and install the Apache Poi software on your device. Then, launch it to get started.

2. Once you've installed the software, go ahead and write java code to create a new sheet.

Below is an example of java code that can be used to create and add data to the new sheet.

package net.codejava.excel;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

/**

* This program illustrates how to update an existing Microsoft Excel document.

* Create a new sheet.

*

* @author www.codejava.net

*

*/

public class ExcelFileUpdateExample4 {

public static void main(String[] args) {

String excelFilePath = "JavaBooks.xls";

try {

FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

Workbook workbook = WorkbookFactory.create(inputStream);

Sheet newSheet = workbook.createSheet("Comments");

Object[][] bookComments = {

{"new sheet ", "opened"},

{"click here ", "done"},

{"2021 ", "year"},

};

int rowCount = 0;

for (Object[] aBook : bookComments) {

Row row = newSheet.createRow(++rowCount);

int columnCount = 0;

for (Object field : aBook) {

Cell cell = row.createCell(++columnCount);

if (field instanceof String) {

cell.setCellValue((String) field);

} else if (field instanceof Integer) {

cell.setCellValue((Integer) field);

}

}

}

FileOutputStream outputStream = new FileOutputStream("JavaBooks.xls");

workbook.write(outputStream);

workbook.close();

outputStream.close();

} catch (IOException | EncryptedDocumentException

| InvalidFormatException ex) {

ex.printStackTrace();

}

}

}

3. After you're done, a new sheet will be created. Go ahead and open the Excel document created.

4. Updating specific cells in Excel

Similarly, you can update any cell using the Java code lines.

For example, let write a java program to update a cell at row{1} and column{2}.

Sheet sheet = workbook.getSheetAt(1);

Cell cell2Update = sheet.getRow(1).getCell(2);

cell2Update.setCellValue(49);

Updating the Entire sheet in Excels

Steps to be followed;

1. Firstly, you need to have an Excel sheet that you'll update.

2. Then types these java code lines on your Apache POI.

package net.codejava.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

/**

 * This program illustrates how to update an existing Microsoft Excel document.

 * Append new rows to an existing sheet.

 *

 * @author www.codejava.net

 *

 */

public class ExcelFileUpdateExample1 {

    public static void main(String[] args) {

        String excelFilePath = "JavaBooks.xls";

        try {

            FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

            Workbook workbook = WorkbookFactory.create(inputStream);

            Sheet sheet = workbook.getSheetAt(0);

            Object[][] bookData = {

                    {"Updated ", "Java"},

                                {"Entire sheet ", "code"},

                                {" done", "done"},

            };

            int rowCount = sheet.getLastRowNum();

            for (Object[] aBook : bookData) {

                Row row = sheet.createRow(++rowCount);

                int columnCount = 0;

                Cell cell = row.createCell(columnCount);

                cell.setCellValue(rowCount);

                for (Object field : aBook) {

                    cell = row.createCell(++columnCount);

                    if (field instanceof String) {

                        cell.setCellValue((String) field);

                    } else if (field instanceof Integer) {

                        cell.setCellValue((Integer) field);

                    }

                }

            }

            inputStream.close();

            FileOutputStream outputStream = new FileOutputStream("JavaBooks.xls");

            workbook.write(outputStream);

            workbook.close();

            outputStream.close();

        } catch (IOException | EncryptedDocumentException

                | InvalidFormatException ex) {

            ex.printStackTrace();

        }

    }

}