How to Set Data into Excel sheet using Apache POI?

How to Set Data into Excelsheet?

The below example program explains how to write / set data in spreadsheet without any formatting such as fonts etc.

• To Create blank workbook :

XSSFWorkbook workbook = new XSSFWorkbook();

• To Create blank workbook :

XSSFSheet spreadsheet = workbook.createSheet("JavaByKiran");

• To Create a row object :

XSSFRow row;

• This data needs to write (Object[])

Map<String, Object[]> jbkemp = new TreeMap<String, Object[]>();
    jbkemp.put("1", new Object[] { "EMP ID", "EMP NAME", "Address" });

    jbkemp.put("2", new Object[] { "1", "JavaByKiran", "Pune" });

    jbkemp.put("3", new Object[] { "2", "JavaByKiran", "Pune" });

    jbkemp.put("4", new Object[] { "3", "JavaByKiran", "Pune" });

    jbkemp.put("5", new Object[] { "4", "JavaByKiran", "Pune" });

    jbkemp.put("6", new Object[] { "5", "JavaByKiran", "Pune" });

• Iterate over data and write to sheet :

Set<String> keyid = jbkemp.keySet();
    int rowid = 0;
    for (String key : keyid) {
	row = spreadsheet.createRow(rowid++);
	Object[] objectArr = jbkemp.get(key);
	int cellid = 0;
        for (Object obj : objectArr) {
            Cell cell = row.createCell(cellid++);
            cell.setCellValue((String) obj);
        }
    }

• Write the workbook in file system :

FileOutputStream out = new FileOutputStream(
		       new File("F://JBK_Tutorials//JbkSetDataIntoExcelSheet//src//main//java//JbkEmployee.xlsx"));
workbook.write(out);

• Please find the below working example program :

package com.javabykiran.setdataintoexcelsheet;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
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;

public class JbkSetDataIntoExcelSheet {
    public static void main(String[] args) throws Exception {

	// Create blank workbook
	XSSFWorkbook workbook = new XSSFWorkbook();

	// Create a blank sheet
	XSSFSheet spreadsheet = workbook.createSheet("JavaByKiran");

	// Create row object
	XSSFRow row;

	// This data needs to write (Object[])
	Map<String, Object[]> jbkemp = new TreeMap<String, Object[]>();
	jbkemp.put("1", new Object[] { "EMP ID", "EMP NAME", "Address" });

	jbkemp.put("2", new Object[] { "1", "JavaByKiran", "Pune" });

	jbkemp.put("3", new Object[] { "2", "JavaByKiran", "Pune" });

	jbkemp.put("4", new Object[] { "3", "JavaByKiran", "Pune" });

	jbkemp.put("5", new Object[] { "4", "JavaByKiran", "Pune" });

	jbkemp.put("6", new Object[] { "5", "JavaByKiran", "Pune" });

	// Iterate over data and write to sheet
	Set<String> keyid = jbkemp.keySet();
	int rowid = 0;

        for (String key : keyid) {
            row = spreadsheet.createRow(rowid++);
            Object[] objectArr = jbkemp.get(key);
            int cellid = 0;
            for (Object obj : objectArr) {
		Cell cell = row.createCell(cellid++);
                cell.setCellValue((String) obj);
            }
	}
	// Write the workbook in file system
	FileOutputStream out = new FileOutputStream(
			       new File("F://JBK_Tutorials//JbkSetDataIntoExcelSheet//src//main//java//JbkEmployee.xlsx"));

        workbook.write(out);
	out.close();
	System.out.println("Data written successfully");
    }
}

• Output in Excel file :