How to Read and Write Excel file in Java

Reading and Writing files in java is a very basic thing that every programmer needs to know. In this article, we will focus on how to read and write Excel and CSV files in java. Following pointers will be covered in this article,

• Introduction
• What is a CSV file?
• How to write a CSV file in Java?
• How to read CSV file in Java?
• What is an Excel file?
• How to write Excel file in Java?
• How to read Excel file in Java?

Moving on with this article on Read and write Excel and CSV file in Java

Introduction

Let’s talk about the reading and writing file in JAVA. Let’s take a simple scenario where we want to write a file in JAVA. Suppose we have a simple text file and we want to write some data into that text file so how can a Java program write this data into the file.
1. First, we open the text file in our Java program in writing mode because we want to write some data into the file.
2. Now we have our file object, after that, we append some string in the file object.
3. Finally, we flush the changes into the file and close the file.

Now let’s talk about how we can read the file in JAVA suppose we have a text file that contains some data and we want to read this data.

1. Firstly we need to open the file in the reading mode and pass that data to a file object.
2. Next in need to read the data from the file line by line for this we create a loop and read the data line by line from the file.
3. After completing the reading data from the file we need to close the file.

Moving on with this article on Read and write Excel and CSV file in Java

What is the CSV file?

Now let’s talk about the CSV file, well CSV file is a normal file but contains the data with comma-separated values this comma-separated values. CSV file stores the table data into a simple text file. In our example, we created a CSVReadWrite.java file to read and write the file.

Moving on with this article on Read and write Excel and CSV file in Java

How to write a CSV file in Java?

Let’s take a simple example where we write a CSV file. For this, we use openCSV library. First, we load the file path into the CSVWriter and then set the column and then push the data both are comma-separated. The writeNext method is used to push the data to the file.

Moving on with this article on Read and write Excel and CSV file in Java

How to read CSV file in Java?

Let’s talk about writing a CSF file in java. In our example, we created readingCSVFile method to read the CSV file. We use FileReader object to load the file and BudfferedReader class to read the file. Then we start reading the file line by line readFilerow. Then we split the data by comma and save it into the array and finally print the data by index.

CSVReadWrite.java

package com.excelcsv;
import com.opencsv.CSVWriter;
import java.io.*;
public class CSVReadWrite {
String filePath;
CSVWriter file;
CSVReadWrite(String filePath){
this.filePath = filePath;
}
// writing csv file function
public void writingCSVFile(){
try {
file = new CSVWriter(new FileWriter(new File(filePath)));
String[] colName = { "Student ID", "Student Name", "Student Email" };
file.writeNext(colName);
String[] data = {"001", "Frank", "frank@znx.com"};
String[] data1 = {"002", "Mark", "mark@znx.com"};
String[] data2 = {"003", "Martin", "martin@znx.com"};
file.writeNext(data);
file.writeNext(data1);
file.writeNext(data2);
file.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// reading csv file
public void readingCSVFile(){
try {
BufferedReader readFile = new BufferedReader(new FileReader(filePath));
String readFilerow;
while ((readFilerow = readFile.readLine()) != null) {
String[] data = readFilerow.split(",");
System.out.println(data[0]+"|"+data[1]+"|"+data[2]);
}
readFile.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}

Moving on with this article on Read and write Excel and CSV file in Java

What is an Excel file?

Before talking about the excel file let’s first talk about Microsoft Excel, well Microsoft Excel is a software program that used in a spreadsheet to read, write and calculate data from the table. When we store a Microsoft Excel file that file calls the excel file that has the extension .xls(older) or .xlsx (newer).

Moving on with this article on Read and write Excel and CSV file in Java

How to write Excel file in Java?

Now let’s discuss the file writing in excel, well writing the file in xls format we use JExcel API because the excel file is the specific file that open and edits with Microsoft Excel software. we need to create a WritableWorkbook object and set the file path in Workbook.createWorkbook method. Next, we call the method createSheet to create a new sheet and set the label. Finally, we need to add cell for this we call the method addCell and pass the label object with data we want to put in the excel file. In the last, we call the excelSheet.write a method to write the data to the excel file.

Moving on with this article on Read and write Excel and CSV file in Java

How to read Excel file in Java?

In the final phase of reading excel file, we first set the file path in the Workbook.getWorkbook next we create a sheet object and get the sheet.
Next, we create a cell object and get a cell form the sheet object. Now we want to print the data so we call the getContents method on cell object this will return the data from the specific cell.

ExcelReadWrite.java

package com.excelcsv;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import java.io.File;
import java.io.IOException;
public class ExcelReadWrite {
String filePath;
ExcelReadWrite(String filePath){
this.filePath = filePath;
}
// writing excel file
public void writeExcelFile(){
WritableWorkbook excelSheet = null;
try {
excelSheet = Workbook.createWorkbook(new File(filePath));
WritableSheet excelFile = myFirstWbook.createSheet("Sheet 1", 0);
Label label = new Label(0, 0, "Test Count");
excelFile.addCell(label);
Number number = new Number(0, 1, 1);
excelFile.addCell(number);
label = new Label(1, 0, "Result");
excelFile.addCell(label);
label = new Label(1, 1, "Passed");
excelFile.addCell(label);
number = new Number(0, 2, 2);
excelFile.addCell(number);
label = new Label(1, 2, "Passed 2");
excelFile.addCell(label);
excelSheet.write();
}catch (Exception e){
e.printStackTrace();
}
}
// reading excel file
public void readExcelFile(){
try {
excelSheet = Workbook.getWorkbook(new File(filePath));
Sheet sheet = workbook.getSheet(0);
Cell cell1 = sheet.getCell(0, 0);
System.out.print(cell1.getContents() + ":");
Cell cell2 = sheet.getCell(0, 1);
System.out.println(cell2.getContents());
Cell cell3 = sheet.getCell(1, 0);
System.out.print(cell3.getContents() + ":");
Cell cell4 = sheet.getCell(1, 1);
System.out.println(cell4.getContents());
System.out.print(cell1.getContents() + ":");
cell2 = sheet.getCell(0, 2);
System.out.println(cell2.getContents());
System.out.print(cell3.getContents() + ":");
cell4 = sheet.getCell(1, 2);
System.out.println(cell4.getContents());
} catch (IOException e) {
e.printStackTrace();
}
}
}

For handling the exception handling we write bothe classes code between the try-catch block.
The Main.java main java file that contains the main method here we call both classes, then we create a csvObj to handle read and write CSV file and create another object execObj to read and write excel file. This file handles the main functionality of our program.

Main.java

package com.excelcsv;
public class Main {
public static void main(String[] args) {
/**
* reading and writing CSV file here
*/
// creating CSVReadWrite class object
CSVReadWrite csvObj = new CSVReadWrite(System.getProperty("user.dir")+"output_csv.csv");
// writing CSV file
csvObj.writingCSVFile();
// read csv file
csvObj.readingCSVFile();
/**
* reading and writing Excel file here
*/
// creating ExcelReadWrite object
ExcelReadWrite excObj = new ExcelReadWrite(System.getProperty("user.dir")+"output_excel.xls");
// writing excel file
excObj.writeExcelFile();
// reading excel file
excObj.readExcelFile();
}
}

With this, we come to an end of this Read and Write Excel and CSV File in Java article.

Check out the Java Certification Course by Edureka, a trusted online learning company with a network of more than 250,000 satisfied learners spread across the globe. Edureka’s Java J2EE and SOA training and certification course is designed for students and professionals who want to be a Java Developer. The course is designed to give you a head start into Java programming and train you for both core and advanced Java concepts along with various Java frameworks like Hibernate & Spring.

Got a question for us? Please mention it in the comments section of this “Read and Write Excel and CSV File in Java” blog and we will get back to you as soon as possible.