记录一个写了一天半的问题,前段js创建一个iframe,由里面的额form来使用jsp上传Excel同时将Excel保存到数据库
下面贴出了整个jsp,有些类涉及到了项目所以删除了
<%@ page contentType="text/html;charset=UTF-8"%>
<%@ page import="java.util.*,java.io.*"%>
<%@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="org.apache.poi.poifs.filesystem.POIFSFileSystem"%>
<%@page import="org.springframework.web.multipart.commons.CommonsMultipartFile" %>
<%@page import="org.apache.commons.io.FileUtils" %>
<%@page import="org.apache.poi.ss.usermodel.Cell" %>
<%@page import="org.apache.poi.xssf.usermodel.XSSFCell" %>
<%@page import="org.apache.poi.xssf.usermodel.XSSFRow" %>
<%@page import="org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@page import="org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<html>
<head>
<title>uploadFile</title>
</head>
<body>
<%
FileStoreService fss = ServiceFactory.getFileStoreSerivce();
String serverPath = request.getRealPath("") + "/upload";
String curTime = DateUtils.curDateTimeStr14();
//最大文件大小
long maxSize = 40000000;
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
upload.setHeaderEncoding("UTF-8");
List items = upload.parseRequest(request);
Iterator itr = items.iterator();
while (itr.hasNext()) {
FileItem item = (FileItem) itr.next();
//由FileItem 装换为 File
String fileName = item.getName();
if(fileName == null){
continue;
}
String fileExt = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();
String newFileName = curTime + "_" +new Random().nextInt(1000) + "." + fileExt;
File file = new File(serverPath, newFileName);
//创建文件
try{
file.createNewFile();
} catch(Exception e){
e.printStackTrace();
} finally{
}
//FileItem 类写入file
item.write(file);
//创建Excel,读取文件内容
XSSFWorkbook workbook = new XSSFWorkbook(FileUtils.openInputStream(file));
//获取第一个工作表
XSSFSheet sheet = workbook.getSheet("Sheet1");
//获取sheet中第一行行号
int firstRowNum = sheet.getFirstRowNum();
//获取sheet中最后一行行号
int lastRowNum = sheet.getLastRowNum();
try {
//循环插入数据
for(int i=firstRowNum+1;i<=lastRowNum;i++){
XSSFRow row = sheet.getRow(i);
Map insertMap = new HashMap();
XSSFCell ITEMID = row.getCell(0); //物料代码
if(ITEMID!=null){
ITEMID.setCellType(Cell.CELL_TYPE_STRING);
insertMap.put("ITEMID",(ITEMID.getStringCellValue()));
}
XSSFCell ITEMNAME = row.getCell(1); //物料名称
if(ITEMNAME!=null){
ITEMNAME.setCellType(Cell.CELL_TYPE_STRING);
insertMap.put("ITEMNAME",(ITEMNAME.getStringCellValue()));
}
XSSFCell DATATIME = row.getCell(2); //日期 格式为 yyyy-MM-dd
if(DATATIME!=null){
DATATIME.setCellType(Cell.CELL_TYPE_STRING);
insertMap.put("DATATIME",(DATATIME.getStringCellValue()));
}
XSSFCell DATAVAL = row.getCell(3); //价格
if(DATAVAL!=null){
DATAVAL.setCellType(Cell.CELL_TYPE_STRING);
insertMap.put("DATAVAL",(DATAVAL.getStringCellValue()));
}
XSSFCell COMPANYNAME = row.getCell(4); //公司别
if(COMPANYNAME!=null){
COMPANYNAME.setCellType(Cell.CELL_TYPE_STRING);
insertMap.put("COMPANYNAME",(COMPANYNAME.getStringCellValue()));
}
insertMap.put("REC_CREATOR",UserUtils.getUserLoginName());
insertMap.put("REC_CREATOR_CNAME", UserUtils.getUserLoginName());
insertMap.put("REC_CREATE_TIME",curTime);
System.out.println(insertMap);
dao.insert("PRLY2011.insert",insertMap);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
file.delete();
}
}
%>
</body>
</html>
上传的Excel的内容为
eclipse开发,jdk1.6,很老,老得不能再老,很烦
其中jsp中部分方法不能用可以评论留言给我