reaadExcelServlet.java
package com.mvc.upload;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Iterator;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.oreilly.servlet.MultipartRequest;
//*Jagruk app notification feedback
@WebServlet("/readExcel")
public class readExcelServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
ArrayList cellArrayListHolder = new ArrayList ();
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/appdb","root","root");
String Path= "E:/Jagruk app notification feedback";
String absolutepath=getServletContext().getRealPath(Path);
MultipartRequest mr=new MultipartRequest(request,absolutepath);
String filename=mr.getOriginalFileName("filename");
FileInputStream myinput = new FileInputStream(new File(absolutepath+"/"+filename));
XSSFWorkbook workbook = new XSSFWorkbook(myinput);
/*Create a workbook using myfilesystem */
/* Get the first sheet from workbook */
XSSFSheet firstSheet = workbook.getSheetAt(0);
Iterator<row> iterator = firstSheet.iterator();
while (iterator.hasNext())
{
XSSFRow nextRow =(XSSFRow)iterator.next();
ArrayList rowarrylist=new ArrayList();
Iterator<cell> cellIterator = nextRow.cellIterator();
while (cellIterator.hasNext()) {
XSSFCell cell = (XSSFCell)cellIterator.next();
rowarrylist.add(cell);
}
cellArrayListHolder.add(rowarrylist);
}
System.out.println(cellArrayListHolder);
ArrayList rowarrylist=null;
PreparedStatement st=con.prepareStatement("INSERT INTO exportdata VALUES (?,?,?,?,?,?,?,?)");
for(int i=1;i<cellarraylistholder.size();i++)
{=""
="" rowarrylist="(ArrayList)cellArrayListHolder.get(i);
" st.setstring(2,rowarrylist.get(1).tostring());
="" st.setstring(3,rowarrylist.get(2).tostring());
="" st.setstring(4,rowarrylist.get(3).tostring());
="" st.setstring(5,rowarrylist.get(4).tostring());
="" st.setstring(6,rowarrylist.get(5).tostring());
="" st.setstring(7,rowarrylist.get(6).tostring());
="" st.setstring(8,rowarrylist.get(7).tostring());
="" st.setstring(9,rowarrylist.get(8).tostring());
="" st.executeupdate();="" }
="" catch(exception="" e)
="" {
="" e.getmessage();
="" system.out.println(e.getmessage());
="" }
}
exportdatabaase.jsp:
<%@="" page="" language="java" contenttype="text/html; charset=ISO-8859-1" pageencoding="ISO-8859-1" %="">
<title>upload excel data
/*We need to something to iterate through the cell */
What I have tried:
ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());
ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());
ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());
count= ps.executeUpdate();