Read, Write Excel With Java - POI
![Read, Write Excel With Java - POI Read, Write Excel With Java - POI](/static/images/java.png)
Read And Write Excel With Java - POI Example explains step by step details of reading and writing excel with Apache POI.
Consider an application where you need to manipulate Microsoft office formats, in that case you can use Apache POI
Apache POI project consists of APIs that is used for manipulating various Microsoft office formats, this includes pure java libraries for writing and reading files in Microsoft Office formats, such as Excel, Word and PowerPoint presentations.
Apache POI uses event based API's, this will reduce the memory foot print drastically.
You can see the below example, which is demonstrating Reading & Writing Excel With Apache POI
pom.xml
You need to have following dependencies
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency>
Required Libraries
Write Excel With Java - POI
import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.Calendar; import java.util.Date; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; 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.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.FillPatternType; public class PoiWriteExcelFile { public static void main(String[] args) { try { FileOutputStream fileOut = new FileOutputStream("test.xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet("Worksheet"); HSSFRow row = worksheet.createRow((short) 0); HSSFCell cellA1 = row.createCell(0); cellA1.setCellValue("Hello"); HSSFCellStyle styleOfCell = workbook.createCellStyle(); styleOfCell.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex()); styleOfCell.setFillPattern(FillPatternType.BIG_SPOTS); cellA1.setCellStyle(styleOfCell); HSSFCell cellB1 = row.createCell(1); cellB1.setCellValue("World"); styleOfCell = workbook.createCellStyle(); styleOfCell.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex()); styleOfCell.setFillPattern(FillPatternType.BIG_SPOTS); cellB1.setCellStyle(styleOfCell); HSSFCell cellC1 = row.createCell(2); cellC1.setCellValue("Happy"); styleOfCell = workbook.createCellStyle(); styleOfCell.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex()); styleOfCell.setFillPattern(FillPatternType.BIG_SPOTS); cellC1.setCellStyle(styleOfCell); HSSFCell cellD1 = row.createCell(3); cellD1.setCellValue(new Date()); styleOfCell = workbook.createCellStyle(); styleOfCell.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); styleOfCell.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex()); styleOfCell.setFillPattern(FillPatternType.BIG_SPOTS); cellD1.setCellStyle(styleOfCell); row = worksheet.createRow(1); row.createCell(0).setCellValue(Calendar.getInstance().getTime().toString()); row.createCell(1).setCellValue("a string"); row.createCell(2).setCellValue("true"); row.createCell(3).setCellType(CellType.ERROR); workbook.write(fileOut); workbook.close(); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } }
Output
![Read And Write Excel With Java - POI Read And Write Excel With Java - POI](/static/images/poi-write-excel.png)
Read Excel With Java - POI
import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; 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; public class PoiReadExcelFile { public static void main(String[] args) { try { FileInputStream iStream = new FileInputStream("test.xls"); HSSFWorkbook workbook = new HSSFWorkbook(iStream); HSSFSheet worksheet = workbook.getSheet("Worksheet"); // get first row HSSFRow row = worksheet.getRow(0); HSSFCell cellA1 = row.getCell(0); System.out.println("A1 " + cellA1.getStringCellValue()); HSSFCell cellB1 = row.getCell(1); System.out.println("B1 " + cellB1.getStringCellValue()); HSSFCell cellC1 = row.getCell(2); System.out.println("C1 " + cellC1.getStringCellValue()); HSSFCell cellD1 = row.getCell(3); System.out.println("D1 " + cellD1.getDateCellValue()); // get next row row = worksheet.getRow(1); HSSFCell cellA2 = row.getCell(0); System.out.println("A2 " + cellA2.getStringCellValue()); HSSFCell cellB2 = row.getCell(1); System.out.println("B2 " + cellB2.getStringCellValue()); HSSFCell cellC2 = row.getCell(2); System.out.println("C2 " + cellC2.getStringCellValue()); HSSFCell cellC3 = row.getCell(3); System.out.println("D2 " + cellC3.getErrorCellValue()); workbook.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } }
Output
A1 Hello B1 World C1 Happy D1 Sat Dec 31 08:55:00 IST 2011 A2 Sat Dec 31 08:55:00 IST 2011 B2 a string C2 true D2 15
2 Responses to "Read, Write Excel With Java - POI"