In this article, let us look at how to write data into an excel sheet using Java.
Any programming language must be able to handle files. Java has built-in methods for generating, reading, updating, and deleting files. The File class, which is included in the java.io package, provides several methods. Java employs the stream class to perform file operations.
CSV files are useful for doing operations in excel sheets with JAVA since they can be easily utilized with Microsoft Excel, Google spreadsheets, and practically all other spreadsheets available.
Here we use the apache poi library to write the data into an excel sheet. To use it we must just add the dependency from the maven repository in the pom.xml file.
Pre-requisites:
We required the following software on your computers to work with these.
- Check to see if your machine has Java; if not, obtain the most recent Java SDK version from here.
- Create a Maven project.
- Add apache poi dependency.
Write Data into Excel Sheet using Java
Here we use STS(Spring Tool Suite) to write data into an excel sheet.
You can either use MyEclipse or STS(SpringToolSuite) for this. Here I am using STS.
STS:
Spring Tool Suite (STS) is a Java IDE designed specifically for the development of Spring-based enterprise applications. It is simpler, quicker, and more convenient. Most notably, it is built on the Eclipse IDE. STS is a free, open-source software platform powered by VMware. Spring Tools 4 represents the next generation of Spring tooling for your preferred development environment. It was largely redesigned from the bottom up to give world-class support for developing Spring-based enterprise apps, regardless of whether you choose Eclipse, Visual Studio Code, or Theia IDE.
Note:
If you are using MyEclipse then create a maven project.
STS directly imports all the classes from maven if we just add dependencies.
Step#1: Open STS(SpringToolSuite) and create a spring stater project say WriteDataintoExcel in a package com.sheetstips.
Step#2: Add the spring web dependency and click on Finish. Now the project is created successfully.
Step#3: Open the pom.xml file and add the following apache poi dependency from the maven repository.
https://mvnrepository.com/
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.12</version> </dependency> <dependency> <groupId>org.apache.poi</ groupId> <artifactId>poi-ooxml</ artifactId> <version>3.12</version> </dependency>
Step#4: Create a new class say WritingDataIntoExcel in the src/main/java folder with the same package com.sheetstips.
Step#5: Write the below code in the above WritingDataIntoExcel class
Approach:
- Import all the required libraries using the import keyword
- Press ctri+shft+o to import directly.
- Create a class say WritingDataIntoExcel
- Create a new XSSFWorkbook object using the new keyword
- Create a new worksheet in the above workbook object by passing the sheet name as an argument to the createSheet() function(Sheet1 by default)
- Store it in another variable
- Create a row object
- This data needs to be written (Object[])
- Create an object of the TreeMap using the new keyword
- Add the data to the above object row-wise using the put() function.
- Take a variable and initialize the row id value as 0.
- Write the data into the Excel sheet.
- Iterate in the keys of the treeMap using the for loop
- Creating a row in the spreadsheet using the createRow() function and increment the rowId value by 1.
- Get all the details of the key from the studentData Tree map using the get() function and store this as an object
- Take a variable and initiaize the cell id value as 0.
- Iterate in the above object values using the for Loop.
- Create a cell using the createCell() function and increment the cellId value by 1.
- Set the value of the cell using the setCellValue() function by converting the object values to string.
- Create an object for the FileOutputStream by passing the output excel file path as an argument to it to place the output file in that location.
- Write the data which is added in the above steps i.e write the outputFile to the workbook Object using the write() function.
- Close the above-opened output file using the close() function.
- The Exit of the Program
Below is the implementation:
package com.sheetstips;
//Import all the required libraries using the import keyword
//Press ctri+shft+o to import directly
import java.io.File;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
//Create a class say WritingDataIntoExcel
public class WritingDataIntoExcel {
// The exceptions can be caught here
public static void main(String[] args) throws Exception {
// Create a new XSSFWorkbook object using the new keyword
XSSFWorkbook workbookObj = new XSSFWorkbook();
// Create a new worksheet in the above workbook object by passing
// the sheet name as an argument to the createSheet() function(Sheet1 by
// default)
// Store it in another variable
XSSFSheet worksheet = workbookObj.createSheet("Website Details");
// Create a row object
XSSFRow excelrow;
// This data needs to be written (Object[])
// Create an object of the TreeMap using the new keyword
Map<String, Object[]> websiteDetails = new TreeMap<String, Object[]>();
// Add the data to the above object row-wise using the put() fuction
websiteDetails.put("1", new Object[] { "EmployeeId", "Employee Name", "Employee Salary" });
websiteDetails.put("2", new Object[] { "2122", "John", "50000" });
websiteDetails.put("3", new Object[] { "2123", "Mary", "85000" });
websiteDetails.put("4", new Object[] { "2124", "Alex", "60000" });
websiteDetails.put("5", new Object[] { "2125", "Nick", "25000" });
websiteDetails.put("6", new Object[] { "2126", "Isha", "65000" });
//
Set<String> keyId = websiteDetails.keySet();
// Take a variable and initiaize the row id value as 0
int rowId = 0;
// Write the data into the Excel sheet
// Iterate in the keys of the treeMap using the for loop
for (String key : keyId) {
// Creating a row in the spreadsheet using the createRow() function and
// increment the rowId value by 1.
excelrow = worksheet.createRow(rowId++);
// Get all the details of the key from the studentData Tree map using the get()
// function and store this as an object
Object[] objectArr = websiteDetails.get(key);
// Take a variable and initiaize the cell id value as 0
int cellId = 0;
//Iterate in the above object values using the for Loop
for (Object obj : objectArr) {
// Create a cell using the createCell() function and increment the
// cellId value by 1
Cell excelcell = excelrow.createCell(cellId++);
// Set the value of the cell using the setCellValue() function
// by converting the object values to string
excelcell.setCellValue((String) obj);
}
}
// Create an object for the FileOutputStream by passing the output excel file
// path as an argument to it to place the output file in that location
FileOutputStream outputfile = new FileOutputStream(new File("OutputExcelFile.xlsx"));
// Write the data which is added in the above steps
// i.e write the outputFile to the workbook Object using the write() function
workbookObj.write(outputfile);
// Close the above opened outputfile using the close() function
outputfile.close();
}
}
Step#6: Now run the code by Right-clicking on the project and click on run –> Run as spring boot App
Output:
