This post was most recently updated on June 14th, 2019
Read and write excel file in java
Pre-requisite:Download latest apache poi library.
Add below mentions jar files in build path from downloaded compressed file.
Class which contains read and write function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 |
import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.Iterator; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelReadWrite { public Workbook getWorkbook(String filePathStr) { Workbook Xlworkbook = null; File fpXsl = new File(filePathStr); String fExt = filePathStr.substring(filePathStr.lastIndexOf('.') + 1, filePathStr.length()).toUpperCase(); try { if (fExt.matches("XLSX")) { Xlworkbook = new XSSFWorkbook(new FileInputStream(fpXsl)); } else if (fExt.matches("XLS")) { Xlworkbook = new HSSFWorkbook(new FileInputStream(fpXsl)); } else { System.out.println("sysos"); } } catch (IOException e) { e.printStackTrace(); } return Xlworkbook; } public Workbook createWorkbook(String filePathStr) { Workbook Xlworkbook = null; String fExt = filePathStr.substring(filePathStr.lastIndexOf('.') + 1, filePathStr.length()).toUpperCase(); if (fExt.matches("XLSX")) { Xlworkbook = new XSSFWorkbook(); } else if (fExt.matches("XLS")) { Xlworkbook = new HSSFWorkbook(); } else { System.out.println("File format not supported"); } return Xlworkbook; } public Sheet getSheetByName(Workbook XlWorkbook, String sheetName) { return XlWorkbook.getSheet(sheetName); } public Sheet getSheetByNumber(Workbook XlWorkbook, int sheetNumber) { return XlWorkbook.getSheetAt(sheetNumber); } public void readExcel(String excelFilePath, String sheetName) { Workbook workbook = getWorkbook(excelFilePath); Sheet sheet = getSheetByName(workbook, sheetName); Iterator<Row> rows = sheet.iterator(); while (rows.hasNext()) { Row row = rows.next(); Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { Cell cell = cells.next(); System.out.println(cell.getAddress().toString()+" : "+cell.getStringCellValue()); } } } public void writeExcelSheet(String excelFilePath, String sheetName, int rowNum, int cellNum, String cellData) throws IOException { Workbook xlWorkbook = getWorkbook(excelFilePath); Sheet sheet = getSheetByName(xlWorkbook, sheetName); Workbook xlworkbook2Write = createWorkbook(excelFilePath); Sheet sheet2Write = xlworkbook2Write.createSheet(sheetName); Row row2Write = null; Cell cell2Write = null; int startRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); if (rowNum < startRow) { row2Write = sheet2Write.createRow(rowNum); cell2Write = row2Write.createCell(cellNum); cell2Write.setCellValue(cellData); } for (int i = startRow; i <= lastRow; i++) { Row row = sheet.getRow(i); row2Write = sheet2Write.createRow(i); int startCell = 0; int lastCell = 0; try { startCell = row.getFirstCellNum(); lastCell = row.getLastCellNum(); for (int j = startCell; j < lastCell; j++) { Cell cell = row.getCell(j); cell2Write = row2Write.createCell(j); cell2Write.setCellValue(cell.getStringCellValue()); if (rowNum == i && j == cellNum) { cell2Write.setCellValue(cellData); } } } catch (Exception e) { if (rowNum == i) { cell2Write = row2Write.createCell(cellNum); cell2Write.setCellValue(cellData); } } } if (rowNum > lastRow) { row2Write = sheet2Write.createRow(rowNum); cell2Write = row2Write.createCell(cellNum); cell2Write.setCellValue(cellData); } FileOutputStream fos = new FileOutputStream(excelFilePath); xlworkbook2Write.write(fos); fos.flush(); fos.close(); } } |
Read and write functions from ExcelReadWrite class used.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
import java.io.IOException; public class ExcelMain { public static void main(String[] args) throws IOException { ExcelReadWrite readWrite=new ExcelReadWrite(); System.out.println("Before changes in D3"); //Read excel data readWrite.readExcel("D:\\Tools\\TestData.xlsx", "sheet"); //Change value of D3, initially it was FAIL and new value will be Pass int rowNum=2; //third row int colNum=3; //4th column (D) readWrite.writeExcelSheet("D:\\Tools\\TestData.xlsx", "sheet", rowNum, colNum, "Pass"); //Read excel, now D3 will become Pass System.out.println("After changes in D3"); readWrite.readExcel("D:\\Tools\\TestData.xlsx", "sheet"); } } |
Code Execution Result:
Changes in excel file before and after code execution.
 |
 |
|---|
Hi, I found on the net Jspreadsheet.
It’s Java API for Microsoft Excel from Independentsoft company.
There are a number of use cases on the site and the API itself is well documented and tehnical support.
Of course it has a price, but it’s worth it all, it’s a Java API, Android, .NET, and more.