Menu
I want to read an excel file in JSP,for this I first uploaded the file in a folder in the :D partition named uploads using a web application project,and I tried to read the excel uploaded file with an another java projet.The both codes are working.Here it is the code of uploading in a specific folder via web application project(JSP and SERVLET):
Libraries
index.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<title>Upload File</title>
</head>
<body>
<form action="UploadFile" method="post" enctype="multipart/form-data">
Select File : <input type="file" name="filetoupload">
<br/>
<input type="submit" value="Upload File">
</form>
</body>
</html>
UploadServlet.java(Servlet)
import java.io.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import javax.servlet.ServletException;
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.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.io.FilenameUtils;
public class UploadFile extends HttpServlet{
String saveFile="D:/upload/";
protected void processRequest(...)throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
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);
}
}
}
}catch(Exception e){
}
finally {
out.close();
}
}
private File checkExist(String fileName) {
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;
}
@Override
protected void doGet(...)throws ServletException, IOException {
processRequest(request, response);
}
@Override
protected void doPost(...)throws ServletException, IOException {
processRequest(request, response);
}
@Override
public String getServletInfo() {
return "Short description";
}
}
Then I create a new JAVA PROJECT (SWING) and try the code of reading an EXCEL File via POI,It worked as well,here its is the code:
Libraries
JavaApplication.java
import java.io.*;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class JavaApplication{
public static void main(String[] args){
try{
FileInputStream file;
file = new FileInputStream(new File("D:\\upload\\total.xlsx"));
//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()){
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()){
Cell cell = cellIterator.next();
//Check the cell type and format accordingly
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "t");
break;
}
}
System.out.println("");
}
file.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
The question is how to mix these two codes to can UPLOAD the file then print the data from EXCEL to a table in JSP ???? help me i'm stuck for over a month on this procedure
943
Apache POI XSSF implementation should be used for Excel 2007 OOXML (. xlsx) file format. Apache POI HSSF and XSSF API provides mechanisms to read, write or modify excel spreadsheets. Apache POI also provides SXSSF API that is an extension of XSSF to work with very large excel sheets.
after item.write(f); add this
InputStream inputStream= new ByteArrayInputStream(IOUtils.toByteArray(new FileInputStream(f)));
Workbook wb = WorkbookFactory.create(inputStream);
Sheet mySheet = wb.getSheetAt(0);
Iterator<Row> rowIter = mySheet.rowIterator();
rowIter.next();
continue your code from here.
174
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<!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>
<%
//HSSFCell cell=new HSSFCell();
//cell.
%>
<form action="uploadExcelsb123.jsp" method="post" enctype="multipart/form-data">
Name: <input type="file" name="excel"><br>
<input type="submit"" name="Upload">
</form>
</body>
</html>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="org.apache.commons.io.IOUtils"%>
<%@ page
import="org.apache.commons.fileupload.servlet.ServletFileUpload"%>
<%@ page import="org.apache.commons.fileupload.disk.DiskFileItemFactory"%>
<%@ page import="org.apache.commons.fileupload.*"%>
<%@ page import="com.api.dao.MyDataConnect"%>
<%@ page import="java.util.*,java.io.*"%>
<%@ page import="java.util.Iterator"%>
<%@ page import="java.util.List"%>
<%@ page import="java.util.Map"%>
<%@ page import="java.io.File"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@page import="com.api.dao.MyDataConnect;"%><html>
<head>
<title>Bulk Upload Page</title>
</head>
<body>
<%
Map<String,String> mp=new HashMap<String,String>();
try {
String ImageFile = "";
String itemName = "";
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 (FileUploadException e) {
e.getMessage();
}
Connection conn = new MyDataConnect().giveConnection();
//Date d = new Date();
FileItem item = (FileItem) items.get(0);
//InputStream is=item.getInputStream();
HSSFWorkbook workbook = new HSSFWorkbook(item.getInputStream());
String ipaddress=request.getRemoteAddr();
HSSFSheet sh = (HSSFSheet) workbook.getSheet("Sheet1");
Iterator<HSSFRow> rowIterator = sh.rowIterator();
%>
<table border="4">
<%
int rowcount=0;
System.out.println("xdgdsf");
while(rowIterator.hasNext()) {
String user_id="";
String name="",email="";
String age="";
HSSFRow row = rowIterator.next();
Iterator<HSSFCell> cellIterator = row.cellIterator();
%><tr> <%
int colcount=0;
while(cellIterator.hasNext()) {
HSSFCell cell = cellIterator.next();
%><td><%
String value="";
int no=0;
switch(cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t\t");
if((int) cell.getNumericCellValue()==0){
no=0;
}else{
no=(int) cell.getNumericCellValue();
}
String column=mp.get(colcount+"");
if(column.trim().equals("user_id")){
out.print(cell.getNumericCellValue() + "\t\t");
user_id=no+"";
}else if(column.trim().equals("age")){
out.print(cell.getNumericCellValue() + "\t\t");
age=no+"";
}
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue().toString() + "\t\t");
if(cell.getStringCellValue().toString()==null || cell.getStringCellValue().toString()=="" || cell.getStringCellValue().toString().trim().length()==0 ){
value="NA";
}else{
value=cell.getStringCellValue().toString();
}
if(rowcount!=0){
column=mp.get(colcount+"");
// System.out.println(mp);
if(column.trim().equals("name")){
out.print(cell.getStringCellValue().toString() + "\t\t");
name=value;
}else if(column.trim().equals("email")){
out.print(cell.getStringCellValue().toString() + "\t\t");
email=value;
}else if(column.trim().equals("user_id")){
out.print(cell.getNumericCellValue() + "\t\t");
user_id=no+"";
}else if(column.trim().equals("age")){
out.print(cell.getNumericCellValue() + "\t\t");
age=no+"";
}
}
break;
}
if(rowcount==0){
mp.put(colcount+"",cell.getStringCellValue().toString().trim());
}
colcount+=1;
%></td><%
}
System.out.println("\n");
rowcount+=1;
%></tr><%
if(rowcount!=0){
String query="insert into bulk_upload(user_id,name,email,age) values('"+user_id+"','"+name+"','"+email+"','"+age+"')";
System.out.println(query);
PreparedStatement ptmt=conn.prepareStatement(query);
int i=ptmt.executeUpdate();
if(i==1){
System.out.println("Updated --------- ");
}else{
System.out.println("Not Updated --------- ");
}
ptmt.close();
}
conn.close();
%></table><%
}
}
}catch(Exception e){
e.printStackTrace();
System.out.println(e);
}
%>
</body>
</html>
user_id and Age Should Be Integer remaining should be String
abouve Code is for java 1.5 accepts only xls file not xlsx, libraries are
commons-fileupload-1.3.jar mysql-connector-java-5.1.18-bin.jar org.apache.commons.io.jar poi-2.5.1-final-20040804.jar
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
