Click here to Skip to main content
15,304,157 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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();
Posted
Comments
phil.o 27-Nov-19 5:02am
   
This is not a question. This is just a statement ("I am unable to..."), followed by an unformatted code dump.
Please use the green Improve question widget which appears while hovering your question, and provide a description of the issue: what is this code doing that it should not, or what is it not doing that it should, or what error message does it provide, etc. Please define the issue. "Not working" is not a valid issue description.
Member 14639038 27-Nov-19 6:24am
   
data contained in excel is not exported to mysql database table namely exportdata.
phil.o 27-Nov-19 6:36am
   
Please use the "improve question" widget as suggested and modify your question, so that everyone can pay attention.
Member 14639038 27-Nov-19 6:21am
   
but how i can do it ?????
actually by mistake i forgot to post as a question
ZurdoDev 27-Nov-19 8:55am
   
What is the actual problem? No one is going to take your code and try to run it so you need to be specific on what the issue is.
Member 14639038 6-Dec-19 3:53am
   
actual problem is that : data cannot be read from the excel sheet.
and another problem is : data is not inserted into database from the excel.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900