how to extract data from excel (apache poi) to put it in mysql table using jsp? [ SOLVED]
- by Nihad KH
I want to extract data from excel sheet to insert it into a mysql table using jsp, so far i've done this and its printing data into the outpout(using apache poi),what should i add to this code ?
Output :
Name Age Adress
Mark 35 New york,AA
Elise 22 India,bb
Charlotte 45 France,cc
Readexcel.jsp :
<%@page import="java.sql.Statement"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.util.Date"%>
<%@page import="org.apache.poi.ss.usermodel.Cell"%>
<%@page import="org.apache.poi.ss.usermodel.Row"%>
<%@page import="org.apache.poi.xssf.usermodel.XSSFSheet"%>
<%@page import="org.apache.poi.xssf.usermodel.XSSFWorkbook"%>
<%@page import="java.io.File"%>
<%@page import="org.apache.commons.io.FilenameUtils"%>
<%@page import="org.apache.commons.fileupload.FileItem"%>
<%@page import="java.util.Iterator"%>
<%@page import="java.util.List"%>
<%@page import="org.apache.commons.fileupload.servlet.ServletFileUpload"%>
<%@page import="org.apache.commons.fileupload.disk.DiskFileItemFactory"%>
<%@page import="org.apache.commons.fileupload.FileItemFactory"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>PRINT DATA FROM EXCEL FILE</title>
</head>
<body>
<%
try{
boolean ismultipart=ServletFileUpload.isMultipartContent(request);
if(!ismultipart){
}else{
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
List items = null;
try{
items = upload.parseRequest(request);
}catch(Exception e){
}
Iterator itr = items.iterator();
while(itr.hasNext()){
FileItem item = (FileItem)itr.next();
if(item.isFormField()){
}else{
String itemname = item.getName();
if((itemname==null || itemname.equals(""))){
continue;
}
String filename = FilenameUtils.getName(itemname);
File f = checkExist(filename);
item.write(f);
try{
XSSFWorkbook workbook = new XSSFWorkbook(item.getInputStream());
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()){
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:
out.print(cell.getNumericCellValue() + "t");
break;
case Cell.CELL_TYPE_STRING:
out.print(cell.getStringCellValue() + "t");
break;}
}
out.println("");
}
}catch (Exception e){
e.printStackTrace();
}
}
}
}
}catch(Exception e){
}
finally {
out.close();
}
%>
<%!
private File checkExist(String fileName){
String saveFile = "D:/upload/";
File f = new File(saveFile+"/"+fileName);
if(f.exists()){
StringBuffer sb = new StringBuffer(fileName);
sb.insert(sb.lastIndexOf("."),"-"+new Date().getTime());
f = new File(saveFile+"/"+sb.toString());
}
return f;
}
%>
</body>
</html>
I've created a table in my database named EXCELDATA with the header of the excel sheet :
ExcelData (Name varchar(50),age int,adress varchar(50));
what should i add to this code to get the data from the excel sheet to the mysql table ??
