![]()
![]()
1、建立web工程
引入jar包：
commons-fileupload.jar
commons-lang-1.0.1.jar
jspsmartupload.jar
poi-2.5-final-20040302.jar
servlet-api.jar
servlet.jar
具体Demo示例,详见360云盘“经典详例Demo”
View Code
<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>xsy_poi_importExcel</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>upload.jsp</welcome-file>
</welcome-file-list>
</web-app>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!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=UTF-8">
<title>上传Excel</title>
</head>
<body>
<form action="./submitUp.jsp" method="post" name="fm" target=fraSubmit ENCTYPE="multipart/form-data">
<table>
<tr>
<td class = title>
上传Excel文件地址：<font color="#FF0000">*</font>
</td>
<td class=common>
<input class=common type="file" name=FileName color="#FFFFFF">
</td>
</tr>
<tr>
<input type="submit" value="上传"/>
</tr>
</table>
</form>
</body>
</html>
<%@page import="com.xsy.excel_upload.DailyBankImportBL"%>
<%//程序名称：submitUp.jsp
//程序功能：执行银行对账单批量导入
//创建日期：2015-06-17
//创建人 ： xushuyi
//更新记录： 更新人 更新日期 更新原因/内容
%>
<%@page contentType="text/html;charset=gb2312" %>
<%@page import="java.io.*"%>
<%@page import="java.util.*"%>
<%@page import="com.jspsmart.upload.*"%>
<jsp:useBean id="mySmartUpload" scope="page" class="com.jspsmart.upload.SmartUpload" />
<%
String content = "";
//上传文件
mySmartUpload.initialize(pageContext);
mySmartUpload.setTotalMaxFileSize(10485760);//10M
try{
mySmartUpload.upload();
}catch(Exception ex){
ex.printStackTrace();
}
String path = application.getRealPath("");
String ToPath = "/yd_work/DaliyBankImport/";//获取导入文件需要保存的路径
String filePath ="",excelName = "";//声明路径 导入文件名
try{
com.jspsmart.upload.File myFile = mySmartUpload.getFiles().getFile(0);
filePath = path+ToPath;
filePath = filePath.replace('\\','/');
excelName = "DaliyBank_0000000000.xls";
System.out.println("银行对账单批量导入文件保存路径："
+ filePath + excelName);
//将上传的文件保存到指定目录下 /yd_work/DaliyBankImport/
myFile.saveAs(filePath+excelName,
myFile.SAVEAS_PHYSICAL);
mySmartUpload = null;
}catch(Exception e){
e.printStackTrace();
}
List<String> list = new ArrayList<String>();
list.add(filePath);
list.add(excelName);
DailyBankImportBL dbBankImportBL = new DailyBankImportBL();
try{
if(!dbBankImportBL.submitData(list)){
content = "失败";
}else{
content = "成功";
}
}catch(Exception e){
content = "银行账单导入失败,请检查导入文件!";
e.printStackTrace();
}
%>
<html>
<script language="JavaScript">
var message = "<%=content%>";
alert(message);
</script>
</html>
package com.xsy.excel_upload;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class DailyBankImportBL{
/**
* 文件上传到服务器的路径
*/
private String tfilePath;
/**
* 文件保存在服务器的名字
*/
private String texcelName;
/**
* 上传文件的列数
*/
private static final int COLUMN = 10;
//解析Excel文件
private InputStream is = null;
private POIFSFileSystem fs = null;
private HSSFWorkbook wb = null;
private HSSFSheet sheet = null;
private HSSFRow row = null;
private HSSFCell cell = null;
/**
* 接收数据
* 进行银行账单批量导入业务逻辑处理
* @param vData
* @return
*/
public boolean submitData(List<String> list){
tfilePath = list.get(0);
texcelName = list.get(1);
return excelToSSRS();
}
/**
* 将上传的Excel文件进行解析并转化为SSRS容器
* @return
*/
private boolean excelToSSRS() {
String tempStr = null;
try {
File file = new File(tfilePath+texcelName);
is = new FileInputStream(file);
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
sheet = wb.getSheetAt(0);
System.out.println("sheet最后一行："+sheet.getLastRowNum());
for (int i = 5; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
if (row != null) {
for (short j = 0; j < COLUMN; j++) {
cell = row.getCell(j);
if (cell!=null) {
/**
* cell.getCellType() 获取格式类型
* HSSFCell.CELL_TYPE_NUMERIC 判断为数值 0
* HSSFCell.CELL_TYPE_STRING 判断为字符串 1
*/
if ((j==7||j==8||j==9)
&& (cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC))
tempStr = String.valueOf(cell.getNumericCellValue());
else
tempStr = cell.getStringCellValue();
} else {
tempStr = "";
}
System.out.println((!StringUtils.isEmpty(tempStr)
&& tempStr.equals("--")) ? "" : tempStr);
}
}
}
is.close();
file.delete();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
}
浙公网安备 33010602011771号