twitter
rss

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.


17 comments:

  1. not working this code except xlsx format

  1. Thanks! it worked

  1. HI ,can you please give steps to excel export example using spring mvc

  1. Hi Sir, can you please give steps to excel export example using spring mvc and if Possible can you an explanation about @Configurable

  1. can you share demo example for that example

  1. my excel having 10 digit mobile number while getting that number i am getting below out put i used this code row.getCell(8)
    9.949376104E9
    if i used this code row.getCell((int)5).getNumericCellValue() like this out put 9999999999.0 can you give code for get 10 digit mobile number as it is

  1. the code is not visible can anyone tel me how to get 100 % view

  1. what about service layer inteface and implementaion and dao layer

  1. can u upload service layer code and DAO layer coder

  1. Hi,
    I guess the while loop condition should be <= , if < its just reads the header.

  1. Dai pythyam unaku theriyuma illaya???

  1. Amazing Article, thank you!. I am very glad to read your informative & practical blog. Kindly keep updating your blog.
    Java Developer is a wonderful career for IT students.To start Dream Career to become a Java developer learn from
    Java Training in Chennai
    . or learn thru Java Online Training from India .

  1. Excellant post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    Best Devops online Training
    Online DevOps Certification Course - Gangboard

  1. Excellant post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    Selenium training in Chennai
    Selenium training in Bangalore
    Selenium training in Pune
    Selenium Online training

  1. I have picked cheery a lot of useful clothes outdated of this amazing blog. I’d love to return greater than and over again. Thanks! 
    python Training in Pune
    python Training in Chennai
    python Training in Bangalore

  1. I found your blog while searching for the updates, I am happy to be here. Very useful content and also easily understandable providing.. Believe me I did wrote an post about tutorials for beginners with reference of your blog. 
    microsoft azure training in bangalore
    rpa training in bangalore
    best rpa training in bangalore
    rpa online training

Post a Comment