//Uploading a excel file and parsing it on server side using apache file upload and poi api
Step 1 :- Download following jar files commons-io-1.4.jar,commons-fileupload-1.2.1.jar,poi-3.8-20120326.jar from apache website
Step 2 :- Create a dynamic web project and place the above jars in the lib folder
Step 3 :- Create a jsp which consits ui to upload a file
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><%@page
language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<html>
<head>
<title>parseXlSheet</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
</head>
<body>
<form action="DemoParseServlet" method="post" enctype="multipart/form-data">
<input type="file" name="xlFile">
</br>
</br>
<input type="submit" value="Insert to db">
</form>
</body>
</html>
Step 4 :- Create a servlet which will take the xl file parse it and get required content
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
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.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
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.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
* Servlet implementation class DemoParseServlet
*/
@WebServlet("/DemoParseServlet")
public class DemoParseServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public DemoParseServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
System.out.println("entering in doget");
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
if (isMultipart) {
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
try {
List items = upload.parseRequest(request);
Iterator iterator = items.iterator();
while (iterator.hasNext()) {
FileItem item = (FileItem) iterator.next();
if (!item.isFormField()) {
String fileName = item.getName();
System.out.println("filename:- " + fileName);
Workbook wb = new HSSFWorkbook(item.getInputStream());
Sheet sheet = wb.getSheetAt(0);
for (Iterator<Row> rit = sheet.rowIterator(); rit
.hasNext();) {
Row row = rit.next();
for (Iterator<Cell> cit = row.cellIterator(); cit
.hasNext();) {
Cell cell = cit.next();
System.out.println("cell value :- "+cell.toString());//printing content inside each row
// do something here
}
}
}
}
} catch (FileUploadException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
Step 5 :-Make changes in xml so that our required jsp will load first then Clean build the project and run it
Step 1 :- Download following jar files commons-io-1.4.jar,commons-fileupload-1.2.1.jar,poi-3.8-20120326.jar from apache website
Step 2 :- Create a dynamic web project and place the above jars in the lib folder
Step 3 :- Create a jsp which consits ui to upload a file
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><%@page
language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<html>
<head>
<title>parseXlSheet</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
</head>
<body>
<form action="DemoParseServlet" method="post" enctype="multipart/form-data">
<input type="file" name="xlFile">
</br>
</br>
<input type="submit" value="Insert to db">
</form>
</body>
</html>
Step 4 :- Create a servlet which will take the xl file parse it and get required content
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
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.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
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.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
* Servlet implementation class DemoParseServlet
*/
@WebServlet("/DemoParseServlet")
public class DemoParseServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public DemoParseServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
System.out.println("entering in doget");
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
if (isMultipart) {
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
try {
List items = upload.parseRequest(request);
Iterator iterator = items.iterator();
while (iterator.hasNext()) {
FileItem item = (FileItem) iterator.next();
if (!item.isFormField()) {
String fileName = item.getName();
System.out.println("filename:- " + fileName);
Workbook wb = new HSSFWorkbook(item.getInputStream());
Sheet sheet = wb.getSheetAt(0);
for (Iterator<Row> rit = sheet.rowIterator(); rit
.hasNext();) {
Row row = rit.next();
for (Iterator<Cell> cit = row.cellIterator(); cit
.hasNext();) {
Cell cell = cit.next();
System.out.println("cell value :- "+cell.toString());//printing content inside each row
// do something here
}
}
}
}
} catch (FileUploadException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
Step 5 :-Make changes in xml so that our required jsp will load first then Clean build the project and run it
No comments:
Post a Comment