6,010 13
Top 1% !
Pearl of Wisdom
Popularity: 3131st place

Published on:


How to Work with Excel Files Using Apache POI

This code will work as a utility to read and write excel files using Apache POI Library. 3.13
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
import; import; 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 org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelUtils { private static HSSFSheet ExcelWSheet; private static HSSFWorkbook ExcelWBook; private static HSSFCell Cell; private static HSSFRow Row; private static String filePath; public static void setExcelFile(String Path,String SheetName) throws Exception { try { // Open the Excel file filePath=Path; FileInputStream ExcelFile = new FileInputStream(Path); // Access the required test data sheet ExcelWBook = new HSSFWorkbook(ExcelFile); ExcelWSheet = ExcelWBook.getSheet(SheetName); } catch (Exception e){ throw (e); } } //This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num public static String getCellData(int RowNum, int ColNum) throws Exception{ try{ Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum); String CellData = Cell.getStringCellValue(); return CellData; }catch (Exception e){ return""; } } //This method is to write in the Excel cell, Row num and Col num are the parameters public static void setCellData(String Result, int RowNum, int ColNum) throws Exception { try{ Row = ExcelWSheet.getRow(RowNum); Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL); if (Cell == null) { Cell = Row.createCell(ColNum); Cell.setCellValue(Result); } else { Cell.setCellValue(Result); } // Constant variables Test Data path and Test Data file name FileOutputStream fileOut = new FileOutputStream(filePath); ExcelWBook.write(fileOut); fileOut.flush(); fileOut.close(); }catch(Exception e){ throw (e); } } }
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus