jsp页面导入excel文件的步骤及配置
2024-09-27 10:32:20
上传使用flash插件
需要jquery.uploadify.min.js，uploadify.css，poi-ooxml-3.8-20120326.jar等
jsp页面：
<%@include file="/uploadDeclare.jsp"%>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
.uploadify {
float: left;
margin: 10px;
}
</style>
<script type="text/javascript">
//导入功能
$(function() {
$("#uploadFile").uploadify({
buttonClass : '',
fileSizeLimit : '51200KB',
uploader : '${basePath}core/upload!uploadFile.action', // 服务器端处理地址
swf : '${basePath}js/uploadify/uploadify.swf', // 上传使用的 Flash
buttonText : "导入",
buttonCursor : 'hand',
fileObjName : 'uploadify',// 上传参数名称 后台action里面的属性uploadify
fileTypeExts : "*.xls;*.xlsx", // 扩展名
fileTypeDesc : "请选择 文件格式",
removeTimeout : , // 文件说明
auto : true, // 选择之后，自动开始上传
multi : false, // 是否支持同时上传多个文件
queueSizeLimit : , // 允许多文件上传的时候，同时上传文件的个数
queueID : 'queueID', onUploadSuccess : function(file, data, response) {
var data = jQuery.parseJSON(data);
if (data.error == ) {
ldDialog.alert(data.message);
} else {
$.ajax({
url : "${basePath}uploadDocumentAction/uploadDocument!importManyOverallPlan.action",
type : "post",
dataType : "json",
data : {
filePath : data.url,
fileName : data.newFileName,
proId : "${proId}",
},
async : false,
success : function(r) {
if (r.code == 'success') {
ldDialog.tips("导入成功！");
document.pagerForm.submit();
} else {
ldDialog.tips("导入失败！");
}
}
});
}
}
});
});
</script>
</head> <td>
<input type="button" id="uploadFile" class="ldBtnGray" value="导入" />
<a href="${basePath}/upload/template/template13.xlsx" download="template13.xlsx">
<span style='float: right; margin-right: 20px; margin-top: 10px; font-size: 18px; color: #2891d2;'>模板下载</span>
</a>
</td>
uploadDeclare.jsp
<%@page language="java" contentType="text/html; charset=utf-8"%>
<base
href="${pageContext.request.scheme}://${pageContext.request.serverName}:${pageContext.request.serverPort}${pageContext.request.contextPath}/" />
<LINK href="${basePath}plugins/uploadify/uploadify.css" type="text/css" rel="stylesheet" />
<script language="javascript" type="text/javascript">
//防止客户端缓存文件，造成uploadify.js不更新，而引起的“喔唷，崩溃啦”
document.write ("<script type='text/javascript' " + "src='${basePath}plugins/uploadify/jquery.uploadify.min.js?" + new Date () + "'><\/script>");
</script>
<style>
.uploadify-box {
width: 130px;
margin: 0px;
margin-top: 10px;
}
</style>
效果页面：
excel文件放在eclipse-workspace\report\WebRoot\upload\template\template13.xlsx目录下，都有的字段：
jsp页面 url : "${basePath}uploadDocumentAction/uploadDocument!importManyOverallPlan.action"对应的类，uploadDocumentAction
其中base类DesigndrawOverallPlanPo中书协的属性和属性的get(),set()方法，可自行编写
/**
* 批量导入
* @param modelMap
* @param request
* @return
* @throws ParseException
*/
@ResponseBody
@RequestMapping("uploadDocument!importManyOverallPlan.action")
public JSONObject importManyOverallPlan(ModelMap modelMap, HttpServletRequest request) throws ParseException {
String filePath = this.getStringParameter("filePath");
String fileName = this.getStringParameter("fileName");
String proId = this.getStringParameter("proId");
final JSONObject result = new JSONObject();
final UploadDocument uploadDocument = this.parameterToPoPrefix(UploadDocument.class);
ProApprovalPo proApproval = this.proApprovalBaseService.queryProApprovalById(proId);
User user = this.getSessionUser();
String proName = "";
boolean success = false;
String code = ""; if(user != null && proApproval != null) {
String abPath = request.getSession().getServletContext().getRealPath("/");
filePath = abPath + filePath;
List<DesigndrawOverallPlanPo> designdrawOverallPlans = new ReadInfoTableExcel().getExcelToOverallPlan(filePath);
proName = proApproval.getProName();
for(int i=;i<designdrawOverallPlans.size();i++) {
DesigndrawOverallPlanPo designdrawOverallPlan = designdrawOverallPlans.get(i);
//首先校验excel文件中的项目id和项目名称是否正确
//还有计划开始时间要小于等于计划结束时间
//且时间格式2018-01-02要正确
//计划开始时间和计划结束时间是date类型与导入时类型（java.long.String）不匹配，
//所以现在导入时，计划开始时间值:reserved1，计划结束时间：reserved2
//.trim():取消空格
if(StringUtils.isEmpty(designdrawOverallPlan.getProId()) || !proId.equals(designdrawOverallPlan.getProId().trim())) {
continue;
}else if(StringUtils.isEmpty(designdrawOverallPlan.getProName()) || !proName.equals(designdrawOverallPlan.getProName().trim())) {
continue;
}else if(StringUtils.isNotEmpty(designdrawOverallPlan.getReserved1()) && StringUtils.isNotEmpty(designdrawOverallPlan.getReserved2())) {
java.text.SimpleDateFormat format = new java.text.SimpleDateFormat("yyyy-MM-dd");
String startAcTime = designdrawOverallPlan.getReserved1().trim();
String endAcTime = designdrawOverallPlan.getReserved2().trim();
//计算持续时长(天):计划结束时间-计划开始时间
if(validationTimeFormat(startAcTime) && validationTimeFormat(endAcTime)) {
int day = (int)((format.parse(endAcTime).getTime() - format.parse(startAcTime).getTime())/( * * * )) + ;
if(day<) {
continue;
}else {
designdrawOverallPlan.setStartAcTime(format.parse(startAcTime));
designdrawOverallPlan.setEndAcTime(format.parse(endAcTime));
designdrawOverallPlan.setContinueDate(day);
}
}
}
designdrawOverallPlan.setReserved1(null);
designdrawOverallPlan.setReserved2(null);
designdrawOverallPlan.setProId(designdrawOverallPlan.getProId().trim());
designdrawOverallPlan.setProName(designdrawOverallPlan.getProName().trim());
designdrawOverallPlan.setBaseInfo(user);
designdrawOverallPlanBaseService.insertDesigndrawOverallPlan(designdrawOverallPlan);
success = true;
}
}
result.put("code", code);
return result;
}
/**
* 校验时间格式为2018-01-02
* @param time
* @return
*/
public boolean validationTimeFormat(String time) {
boolean bool = false;
Pattern pattern = Pattern.compile("[0-9]*");
if(time != null && time.length() == ) {
//校验前四位是否为数字
if(pattern.matcher(time.substring(, )).matches()) {
//校验第五位是-
if("-".indexOf(time.substring(, ))!=-) {
//校验第6,7位是否为数字
if(pattern.matcher(time.substring(,)).matches()) {
//校验第8位是否为-
if("-".indexOf(time.substring(, ))!=-) {
//校验第9,10位是否为数字
if(pattern.matcher(time.substring(, )).matches()) {
bool = true;
}
}
}
}
} }
return bool;
}
方法：ReadInfoTableExcel().getExcelToOverallPlan(filePath);
/**
* 获取excel里的信息
* @param filepath
* @param filetype
* @param uuid
* @return
*/
public List<DesigndrawOverallPlanPo> getExcelToOverallPlan(String filePath) {
//这儿导入使用两个方法原因：
//excel版本有03版本和07版本的区别，文件后缀名分别为.xls和.xlsx。它们对应的POI中的Workbook也是不同的，
//分别是HSSFWorkbook和XSSFWorkbook；对于不同版本的EXCEL文档要使用不同的工具类
try{
return addExcelToOverallPlanXSSF1(filePath);
}catch(Exception e){
try{
return addExcelToOverallPlanXSSF2(filePath);
}catch (Exception e1){
return null;
}
}
}
方法：addExcelToOverallPlanXSSF1 和 addExcelToOverallPlanXSSF2，这儿利用到了反射的方法使属性和字段值一一对应起来。
/**
* 批量导入 方法1
* @param filePath
* @return
* @throws IOException
*/
public List<DesigndrawOverallPlanPo> addExcelToOverallPlanXSSF1(String filePath) throws IOException{
List<ZuobiaoInfo> zuobiaoInfos = new ArrayList<>();
List<DesigndrawOverallPlanPo> designdrawOverallPlans = new ArrayList<DesigndrawOverallPlanPo>();
XSSFWorkbook swb = new XSSFWorkbook(filePath);
XSSFSheet sheet = swb.getSheetAt();
zuobiaoInfos = new TableinfoTest().getOverallPlan();
//获取excel文件中的行数
int rowsNumber = this.getnumber(filePath);
for(int i=;i<rowsNumber;i++) {
boolean flag = true;
DesigndrawOverallPlanPo designdrawOverallPlan = new DesigndrawOverallPlanPo();
for(int j=;j<zuobiaoInfos.size();j++) {
Class c = designdrawOverallPlan.getClass();
Class[] cargs = new Class[];
int cellnumber = zuobiaoInfos.get(j).getZongzuobiao();
try {
Object realArgs = this.getXSSFSheetExcelvalue(i+, cellnumber, sheet);
if(realArgs!=null) {
cargs[] = realArgs.getClass();
String method = zuobiaoInfos.get(j).getMethod();
Method m = c.getMethod(method, cargs);
Object[] inArgs = new Object[];
inArgs[] = realArgs;
m.invoke(designdrawOverallPlan, inArgs);
}
} catch (Exception e) {
e.printStackTrace();
flag = false;
break;
}
}
if(flag) {
designdrawOverallPlans.add(designdrawOverallPlan);
}
}
return designdrawOverallPlans;
}
/**
* 批量导入 方法2
* @param filePath
* @return
* @throws IOException
*/
public List<DesigndrawOverallPlanPo> addExcelToOverallPlanXSSF2(String filePath) throws IOException{
List<ZuobiaoInfo> zuobiaoInfos = new ArrayList<>();
List<DesigndrawOverallPlanPo> designdrawOverallPlans = new ArrayList<DesigndrawOverallPlanPo>();
HSSFWorkbook xwb = new HSSFWorkbook(new FileInputStream(filePath));
HSSFSheet sheet= xwb.getSheetAt();
zuobiaoInfos = new TableinfoTest().getOverallPlan();
//获取excel文件中的行数
int rowsNumber = this.getnumber(filePath);
for(int i=;i<rowsNumber;i++) {
boolean flag = true;
DesigndrawOverallPlanPo designdrawOverallPlan = new DesigndrawOverallPlanPo();
for(int j=;j<zuobiaoInfos.size();j++) {
Class c = designdrawOverallPlan.getClass();
Class[] cargs = new Class[];
int cellnumber = zuobiaoInfos.get(j).getZongzuobiao();
try {
Object realArgs=this.getHSSFSheetExcelvalue(i+, cellnumber, sheet);
if(realArgs!=null) {
cargs[] = realArgs.getClass();
String method = zuobiaoInfos.get(j).getMethod();
if(!realArgs.toString().equals("")){
Method m = c.getMethod(method, cargs);
Object[] inArgs = new Object[];
inArgs[] = realArgs;
m.invoke(designdrawOverallPlan, inArgs);
}else{
flag = false;
break;
}
}
} catch (Exception e) {
e.printStackTrace();
flag = false;
break;
}
}
if(flag) {
designdrawOverallPlans.add(designdrawOverallPlan);
}
}
return designdrawOverallPlans;
}
方法：this.getnumber(filePath);
public int getnumber(String filepath) {
try{
XSSFWorkbook xwb = new XSSFWorkbook(filepath);
XSSFSheet sheet = xwb.getSheetAt();
int allsheetnumber=sheet.getLastRowNum();
System.out.println(allsheetnumber);
return allsheetnumber;
}catch (Exception e){
e.printStackTrace();
try {
HSSFWorkbook xwb1 = new HSSFWorkbook(new FileInputStream(filepath));
HSSFSheet sheet1= xwb1.getSheetAt();
int allsheetnumber1=sheet1.getLastRowNum();
return allsheetnumber1;
}catch (Exception e1){
e1.printStackTrace();
return ;
}
}
}
方法：this.getHSSFSheetExcelvalue(i+1, cellnumber, sheet);
public String getHSSFSheetExcelvalue(int rownumber,int cellnumber,HSSFSheet sheet){
String str=null;
if(sheet.getRow(rownumber)!=null){
if(sheet.getRow(rownumber).getCell(cellnumber)!=null){
str=sheet.getRow(rownumber).getCell(cellnumber).toString();
}
}
return str;
}
方法：TableinfoTest().getOverallPlan()，其中setProId等是base类DesigndrawOverallPlanPo中属性proId的set()方法。
public class TableinfoTest {
List<ZuobiaoInfo> overallPlan = new ArrayList<ZuobiaoInfo>
(Arrays.asList(
new ZuobiaoInfo("setProId", "项目编号", ),
new ZuobiaoInfo("setProName", "项目名称", ),
new ZuobiaoInfo("setWorkContent", "工作内容", ),
new ZuobiaoInfo("setReserved1", "计划开始时间", ),
new ZuobiaoInfo("setReserved2", "计划结束时间", ),
new ZuobiaoInfo("setRemarks", "备注", )
));
public List<ZuobiaoInfo> getOverallPlan() {
return overallPlan;
}
public void setOverallPlan(List<ZuobiaoInfo> overallPlan) {
this.overallPlan = overallPlan;
}
}
类：ZuobiaoInfo
package com.landicorp.pilot.action.readexcel;
public class ZuobiaoInfo {
public String Method;//方法名
public String name;
public int zongzuobiao;//纵坐标
public ZuobiaoInfo(){
}
public ZuobiaoInfo(String Method, String name ,int zongzuobiao){
this.Method=Method;
this .zongzuobiao=zongzuobiao;
this .name=name;
}
public int getZongzuobiao() {
return zongzuobiao;
}
public void setZongzuobiao(int zongzuobiao) {
this.zongzuobiao = zongzuobiao;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getMethod() {
return Method;
}
public void setMethod(String method) {
Method = method;
}
}
最新文章
- Topshelf 学习 跨平台
- 关于Nginx的一些优化(突破十万并发)
- DPI
- text-align:justify的使用
- jquery 新闻滚动效果
- 用Access作为后台数据库支撑，书写一个用C#写入记录的案例
- DDP和DDU什么区别
- CAN总线
- Applet 数字签名技术全然攻略
- C++ multimap容器访问同一键值元素的不同方法
- druid参数配置说明
- Web Animations API (JS动画利器)
- Entity Framework 查漏补缺 （二）
- 使用Jsoup实现java爬虫（非原创）
- Java 浅拷贝和深拷贝
- ThetaSome_ThetaAll子查询
- DevExpress数据源 BindingList<FilesList> 与BindSource 使用区别
- Java中的Html解析：使用jsoup
- Linux CentOS 6.5 下 vsftpd ftp服务器搭建
- L2-031 深入虎穴(BFS)