POI API

Read Excel file in Java using POI API

Mallik2 Comments

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Iterator;
public class ReadExcelFileUsingPOI{
    public static void main(String[] args) throws Exception {
        String filename = "D:\\test.xls";
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filename);
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            HSSFSheet sheet = workbook.getSheetAt(0);
            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator cells = row.cellIterator();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    int type = cell.getCellType();
                    if (type == HSSFCell.CELL_TYPE_STRING) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                                + cell.getColumnIndex() + "] = STRING; Value = "
                                + cell.getRichStringCellValue().toString());
                    } else if (type == HSSFCell.CELL_TYPE_NUMERIC) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                                + cell.getColumnIndex() + "] = NUMERIC; Value = "
                                + cell.getNumericCellValue());
                    } else if (type == HSSFCell.CELL_TYPE_BOOLEAN) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                                + cell.getColumnIndex() + "] = BOOLEAN; Value = "
                                + cell.getBooleanCellValue());
                    } else if (type == HSSFCell.CELL_TYPE_BLANK) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                                + cell.getColumnIndex() + "] = BLANK CELL");
                    }
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                fis.close();
            }
        }
    }
}

How to Create Excel file in Java using POI API

Mallik1 Comment


import java.io.FileOutputStream;


import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public class CreateExcelFile {

public static final String FILE_NAME = “D:\\test.xls”;


 public void createExcelFile(String filename) {

try {

HSSFWorkbook hwb = new HSSFWorkbook();

HSSFSheet sheet = hwb.createSheet(“Sheet1”);

 

HSSFRow rowhead = sheet.createRow((short) 0);

rowhead.createCell((short) 0).setCellValue(“S.No.”);

rowhead.createCell((short) 1).setCellValue(“Emp ID”);

rowhead.createCell((short) 2).setCellValue(“EMP Name”);

rowhead.createCell((short) 3).setCellValue(“Dept”);

 

rowhead = sheet.createRow((short) 1);

rowhead.createCell((short) 0).setCellValue(“1”);

rowhead.createCell((short) 1).setCellValue(“EMP1”);

rowhead.createCell((short) 2).setCellValue(“Mallik”);

rowhead.createCell((short) 3).setCellValue(“Java”);


FileOutputStream fileOut = new FileOutputStream(filename);

hwb.write(fileOut);

fileOut.close();

System.out.println(“Your excel file has been generated!”);


 } catch (Exception ex) {

System.out.println(ex);

ex.printStackTrace();

}

}

public static void main(String[] args) {

CreateExcelFile excelFile = new CreateExcelFile();

excelFile.createExcelFile(FILE_NAME);

}

}