What is Apache POI?
Apache POI (Poor Obfuscation Implementation) is a Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). Apache POI is your java Excel solution which will let you to read and write MS Word, MS Excel and MS PowerPoint files using java.
In this article let’s see how to read data from an uploaded file. Apache poi API has the following implementations for achieving this. Horrible Spreadsheet Format (HSSF) is the pure Java implementation of the Excel (97-2007) file format of POI.
HSSFWorkbook – This is the first object constructed used to write/read an excel file. This represents the entire Excel File.
HSSFSheet - This class is used to create new spreadsheet which is called by HSSFWorkbook. This represents a single sheet in an Excel Document.
HSSFRow - This class represents the rows of a spreadsheet.
HSSFCell - This class represents the cell in a row of a spreadsheet.
Structure of Excel Document to be uploaded and read :
Spring 3 controller Code Snippet to Read Data from the uploaded document :
@RequestMapping(value = "/processExcel", method = RequestMethod.POST)
public String processExcel(
@RequestParam("excelfile") MultipartFile excelfile) {
try {
int i = 0;
//Creates a workbook object from the uploaded excelfile
HSSFWorkbook workbook = new HSSFWorkbook(excelfile.getInputStream());
//Creates a worksheet object representing the first sheet
HSSFSheet worksheet = workbook.getSheetAt(0);
//Reads the data in excel file until last row is encountered
while (i < worksheet.getLastRowNum()) {
//Creates an object for the Candidate Model
Candidate candidate=new Candidate();
//Creates an object representing a single row in excel
HSSFRow row = worksheet.getRow(i++);
//Sets the Read data to the model class
candidate.setCandidateId((int)row.getCell(0).getNumericCellValue());
candidate.setName(row.getCell(1).getStringCellValue());
candidate.setAddress(row.getCell(2).getStringCellValue());
candidate.setEmailId(row.getCell(3).getStringCellValue());
candidate.setPinCode((int)row.getCell(4).getNumericCellValue());
candidate.setAboutCandidate(row.getCell(5).getStringCellValue());
//Sends the model object to service layer for validation,
//data processing and then to persist
iCandidateService.saveCandidate(candidate);
}
} catch (Exception e) {
e.printStackTrace();
}
return "uploadSuccess";
}
If you are using maven, add this dependency to pom.xml
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.7</version> </dependency>
That’s all folks. If you have any doubts please feel free to contact me and provide your suggestions through comments.
February 25, 2013 at 1:46 AM
not working this code except xlsx format