CODEWITHSUNDEEP
jspapache-poiservlet-3.0
Mavrick
Mavrick

Reputation: 505

Compare specific row or column of xls sheet using java and Apache POI

My jsp file is (to upload the excel file) :

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
    <form method="post" action="UploadAction" enctype="multipart/form-data">
    Upload the *.xls file: <input type="file" name="file" value="file"> 
    <br><br>
    <input type="submit" value="Submit">
</body>
</html>

My Servlet code is ( to process the excel file) 

import javax.servlet.ServletException;
import javax.servlet.http.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.File;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.Iterator;


import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;

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;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class UploadAction extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    doPost(request, response);
}

public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException
{
    boolean isMultipart = ServletFileUpload.isMultipartContent(request);
    PrintWriter out = response.getWriter();
    Connection conn = null;
    PreparedStatement pstmt = null;
    String query = "INSERT INTO mydoct(ID) VALUES (?)";
    if(isMultipart) 
    {
        try
        {
            FileItemFactory factory = new DiskFileItemFactory();
            ServletFileUpload upload = new ServletFileUpload(factory);
            List items = upload.parseRequest(request);
            Iterator iter = items.iterator();
            while (iter.hasNext()) 
            {
                FileItem item = (FileItem) iter.next();
                if(!item.isFormField()) 
                {
                    String fileName = item.getName();
                    String root = getServletContext().getRealPath("/");
                    File path = new File(root + "/MyDoctors");
                    if (!path.exists()) {
                        boolean status = path.mkdirs();
                    }
                    File uploadedFile = new File(path + "/" + fileName);
                    String fpath = uploadedFile.getAbsolutePath();
                    //out.println(fpath);
                    int mid = fileName.lastIndexOf(".");
                    String ext = fileName.substring(mid+1,fileName.length());
                    //out.println(ext);
                    if((ext.toLowerCase().equals("xls"))||(ext.toLowerCase().equals("xlsx")))
                    {
                        item.write(uploadedFile);

                        try {
                            Class.forName("com.mysql.jdbc.Driver");
                            String url = "jdbc:mysql://localhost:3306/test";
                            conn = DriverManager.getConnection(url,"root","root");
                            pstmt = conn.prepareStatement(query);


                            FileInputStream file = new FileInputStream(new File(fpath));
                            HSSFWorkbook wb = new HSSFWorkbook(file);

                            //Get first sheet from the workbook
                            HSSFSheet sheet = wb.getSheetAt(0);

                            //Find the total number of rows.
                            int rows = sheet.getPhysicalNumberOfRows();
                            out.println(rows);

                            //Iterate through each rows from first sheet
                            Iterator<Row> rowIterator = sheet.iterator();

                            //while(rowIterator.hasNext()) {
                            for(int i = 1; i<=rows; i++){
                            Row row = rowIterator.next();

                            //find the total no columns in a particular raw.
                            HSSFRow rcol = sheet.getRow(i);
                            int cells = 0;
                            if (row != null) {
                            cells = row.getPhysicalNumberOfCells();
                            //out.println(cells);
                            }
                            //For each row, iterate through each columns
                            Iterator<Cell> cellIterator = row.cellIterator();
                            //while(cellIterator.hasNext()) {
                             for(int j = 0; j<cells; j++){            
                             Cell cell = cellIterator.next();





                            switch(cell.getCellType()) {
                                case Cell.CELL_TYPE_BOOLEAN:
                                     out.print(cell.getBooleanCellValue() + "\t\t");
                                    break;
                                case Cell.CELL_TYPE_NUMERIC:
                                    out.print(cell.getNumericCellValue() + "\t\t");
                                    break;
                                case Cell.CELL_TYPE_STRING:
                                     out.print(cell.getStringCellValue() + "\t\t");
                                     break;
                                 } 


                               } 
                             }
                                file.close();
                                FileOutputStream fos = new FileOutputStream(new File(fpath));
                                wb.write(fos);
                                out.close();

                            } catch (FileNotFoundException e) {
                                e.printStackTrace();
                            } catch (IOException e) {
                                e.printStackTrace();
                            }
                    }
                }else {
                    out.println("invalid file");
                }
            }
        }catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }
}
}

I want to read the 2nd and 3rd columns of 5th, 6th and 7th rows. Please help me..........

Upvotes: 0

Views: 7393

Answers (1)

Gagravarr
Gagravarr

Reputation: 48326

As clearly shown in the Apache POI Documentation, there are two broad ways to access rows and cells. One is to iterate over all of them (well suited to cases when you want everything), the other is to fetch rows and cells by index (works well when you want specific cells)

You've said you want the 2nd and 3rd columns of rows 5, 6 and 7. That's very easy:

 Sheet s = workbook.getSheetAt(0);

 // Rows and Cells are 0 based not 1 based
 Row r5 = s.getRow(4);
 Row r6 = s.getRow(5);
 Row r7 = s.getRow(6);
 if (r5 == null || r6 == null || r7 == null) {
    throw new IllegalArgumentException("At least one required row is empty");
 }

 Cell c5_2 = r5.getCell(1);
 Cell c5_3 = r5.getCell(2);
 Cell c6_2 = r6.getCell(1);
 Cell c6_3 = r6.getCell(2);
 Cell c7_2 = r7.getCell(1);
 Cell c7_3 = r7.getCell(2);

 // Now use them

 // eg
 for (Cell cell : new Cell[] { c5_2, c5_3, c6_2, c6_3 } ) {
     switch(cell.getCellType()) {
         case Cell.CELL_TYPE_BOOLEAN:
             out.print(cell.getBooleanCellValue() + "\t\t");
             break;
         case Cell.CELL_TYPE_NUMERIC:
             out.print(cell.getNumericCellValue() + "\t\t");
             break;
         case Cell.CELL_TYPE_STRING:
             out.print(cell.getStringCellValue() + "\t\t");
             break;
      }      
  }

Upvotes: 2

Related Questions