Upload and parse excel file - JSP

1. Download and add the necessary libraries to the classpath.

• poi-3.9.jar
• poi-ooxml-3.9.jar
• commons-fileupload-1.3.jar
• poi-ooxml-schemas-3.9.jar
• dom4j-1.6.1.jar

2. You should add to the .jsp file the following codes:

 <form action="/XXX/TestServlet" method="post" name="testForm" id="testForm" enctype="multipart/form-data" >

<input type="file" name="mptest" >

<input type="submit" value="Yükle">

</form>

3.TestServlet.java:

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
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.ss.usermodel.WorkbookFactory;

public class TestServlet extends HttpServlet {

    public TestServlet() {
    }

    @Override
    public void init() throws ServletException {
        super.init();
    }

    protected void doGet(HttpServletRequest request,
        HttpServletResponse response) throws ServletException, IOException {
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        try {
           if(ServletFileUpload.isMultipartContent(request)) {
             try {

                List<FileItem> multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);

                if(multiparts != null && multiparts.size() > 0) {
                     FileItem item = multiparts.get(0);

                    // control file mimetype
                    if(item.getContentType().equals("application/vnd.ms-excel") || item.getContentType().equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {

                          if(!item.isFormField()) {
                               InputStream in = item.getInputStream();
                               Workbook wb = WorkbookFactory.create(in);

                               Sheet mySheet = wb.getSheetAt(0);// get first  sheet
                               Iterator<Row> rowIter = mySheet.rowIterator();// get row list

                              while(rowIter.hasNext()) {
                                   List<String> cellList = new ArrayList<String>();
        
                                   Row row = rowIter.next();
                                   Iterator<Cell> cellIterator = ((Row) row).cellIterator();// get cell list

                                   // get all cell as String
                                  while(cellIterator.hasNext()) {
                                      Cell cell = cellIterator.next();
     
                                      switch(cell.getCellType()) {
                                          case Cell.CELL_TYPE_BOOLEAN:
                                              cellList.add(Boolean.toString(cell.getBooleanCellValue()));
                                              break;
        
                                          case Cell.CELL_TYPE_NUMERIC:
                                               cellList.add(String.valueOf(cell.getNumericCellValue()));
                                               break;
        
                                         case Cell.CELL_TYPE_STRING:
                                                cellList.add(cell.getStringCellValue());
                                                break;
        
                                        case Cell.CELL_TYPE_BLANK:
                                              break;
                                    }
     
                               }
                              // do your job for the 'row' and 'cellList'
                           }
                        }
       
                    } else {
                     // the file is not an excel file
                  }
               }
      
            } catch(Exception e) {
              // upload error
            }
  
        } else {
           // wrong file error message
        }
     
     } catch(Exception e) {
        // exception
      }
 
  }

}