-->
Skip to main content

Create and Read Excel File in java [2021]

Create and Read Excel File in java [2021]

On a Regular Basis, Developers need to Create and Read excel file in java either they have pre-existing files from other communication channels to read or they need to create one from data fetched from the database.

Today we will see 
  1. how to create excel file in Java 
  2. how to read excel file in Java
using the Apache POI library. 

As java doesn't have support for reading and creating excel files so we need to use a third-party library to generate one which is provided by Apache as Apache POI.

Create and Read Excel File in Java
Contents

What is Apache POI?

Apache POI(Poor Obfuscation Implementation) is a third-party library used to read and write XLS, XLSX file formats. It has interfaces and classes that support these operations we just need to use them appropriately.

POI has two implementations one for HSSF(Horrible SpreadSheet Format) and another for XSSF(XML SpreadSheet Format). 

HSSF is used for working with excel 2003 or earlier versions and XSSF is used for working with excel 2007 and later versions.

In this tutorial, we will use the XSSF implementation and generate an XLSX format file.

Adding Apache POI Dependency

<dependency>  
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>4.1.2</version>
</dependency>
We can get the latest dependency version from mvn repository.

Creating a Student Pojo

In this example, we are going to save the Student list in studentinfo.xlsx file thus creating Student Pojo which will have all the information that will be displayed in excel file rows and cells.
 public class Student {
  private int rollNo;
  private String firstName;
  private String lastName;
  private String subject;

  public Student(int rollNo, String firstName, String lastName, String subject) {
	super();
	this.rollNo = rollNo;
	this.firstName = firstName;
	this.lastName = lastName;
	this.subject = subject;
  }

  public int getRollNo() {
	return rollNo;
  }

  public String getFirstName() {
	return firstName;
  }

  public String getLastName() {
        return lastName;
  }

  public String getSubject() {
	return subject;
  }

 }

Creating the Excel File

With Apache POI Library, To create an excel file we need to create an instance of WorkBook which is an interface, it has two implementation classes one for xls (HSSFWorkbook) and another for xlsx format (XSSFWorkbook). 

In our example, we will create XSSFWorkbook instance which can be created as below
 Workbook workbook = new XSSFWorkbook();
 Sheet sheet = workbook.createSheet("Studentinfo");
After creating Workbook instance we created Sheet object which will create a sheet in excel file with name Studentinfo.

Now we generate header row which we will take from static array columns in our case, in live scenarios we can take it from other files or databases wherever we have stored them.
 //Create a Header Row
 Row headerRow = sheet.createRow(0);
 for (int i = 0; i < columns.length; i++) {
     Cell cell = headerRow.createCell(i);
     cell.setCellValue(columns[i]);
 }
Row class creates a single row using a sheet object and the Cell class creates a single cell by using a row instance. 

Now we will generate other rows that we will fetch from ArrayList of studentsinfo object.
 // Create Other rows and cells with contacts data
 int rowNum = 1;

 for (Student student : studentInfo) {
     Row row = sheet.createRow(rowNum++);
     row.createCell(0).setCellValue(student.getRollNo());
     row.createCell(1).setCellValue(student.getFirstName());
     row.createCell(2).setCellValue(student.getLastName());
     row.createCell(3).setCellValue(student.getSubject());
 }
studentinfo is an ArrayList where we have stored student's data.

Generating the Excel File 

Finally, we will write the workbook object to xlsx file 
 // Write the output to a file
 try (FileOutputStream fileOut = new FileOutputStream("studentinfo.xlsx")) {
	workbook.write(fileOut);
 } catch (FileNotFoundException e) {
	e.printStackTrace();
 } catch (IOException e) {
	e.printStackTrace();
 }
This above code will generate studentinfo.xlsx in the project location path where you put your project with data populated in it.

Reading Excel File in Java

To read excel file in Java we will first create an instance of FileInputStream and then get an instance of XSSFWorkbook from it.
 //Read the input from file
 File file = new File("studentinfo.xlsx");
 FileInputStream fis = new FileInputStream(file);
 XSSFWorkbook wb = new XSSFWorkbook(fis);
Now we will get Sheet class object from the workbook 
 XSSFSheet sheet = wb.getSheetAt(0);
As we got the sheet and we are fetching the first sheet at index 0 from file now we will iterate rows and columns.

To read column we have to match column type whether its error, numeric, formula, boolean, or string type.
 Iterator<Row> itr = sheet.iterator();
 while (itr.hasNext()) {
   Row row = itr.next();
   Iterator<Cell> cellIterator = row.cellIterator();
   while (cellIterator.hasNext()) {
	Cell cell = cellIterator.next();
	switch (cell.getCellType()) {
		case STRING: // field that represents string cell type
		System.out.print(cell.getStringCellValue() + "\t\t\t");
		break;
		case NUMERIC: // field that represents number cell type
		System.out.print(cell.getNumericCellValue() + "\t\t\t");
		break;
		case FORMULA: // field that represents formula cell type
		System.out.print(cell.getCellFormula() + "\t\t\t");
		break;
        	case BOOLEAN: // field that represents boolean cell type
		System.out.print(cell.getBooleanCellValue() + "\t\t\t");
		break;
		case ERROR: // field that represents error cell type
		System.out.print(cell.getErrorCellValue() + "\t\t\t");
		break;
		default:
	}
   }
  System.out.println("");
 }

Complete Source Code of ExcelUtil Class.


Excel Write Output

Create Excel File in Java

Excel Read Output

Read Excel File in Java
how to read excel file in java using eclipse

Conclusion

In this tutorial, we have covered how to Create and Read excel file in Java using Apache POI library.

Thanks for reading this tutorial so far. If you like this tutorial then please share it with your friends and colleagues. If you have any questions, doubts, suggestions, or feedback then please drop a comment and I'll try to answer your question.

Happy Learning!!!

Labels:

Comments

Post a Comment

If you have any doubts, Please let me know.