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.


27 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. 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

  1. Hi! Is this code work for any extension of excel file? For Example xls or xlsx

  1. You have provided a nice article, Thank you very much for this one. And I hope this will be useful for many people. And I am waiting for your next post keep on updating these kinds of knowledgeable things
    Java Training in Chennai
    Java Training in Coimbatore
    Java Training in Bangalore

  1. Post is very useful. Thank you, this useful information.

    Start your journey with Best SAP Course and get hands-on Experience with 100% Placement assistance from experts Trainers @Softgen Infotech Located in BTM Layout Bangalore. Expert Trainers with 8+ Years of experience, Free Demo Classes Conducted.

  1. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal.keep sharing!!

    android training in chennai

    android online training in chennai

    android training in bangalore

    android training in hyderabad

    android Training in coimbatore

    android training

    android online training

  1. Irrespective of rankings, this will help in huge traffic generation, on your website, over time and, in turn,will steadily increase the number of potential customers for your products and services.

    web designing training in chennai

    web designing training in velachery

    digital marketing training in chennai

    digital marketing training in velachery

    rpa training in chennai

    rpa training in velachery

    tally training in chennai

    tally training in velachery

Post a Comment