因为之后的功能模块涉及excel导入导出，之前也没有接触过，所以今天下午看了下，做了个小例子，方便以后用,导入导出都以XLS，这里只有部分关键代码
导出:
//excel导出
public void exportExcel(){
try {
SimpleDateFormat sf=new SimpleDateFormat("YYYYMMDDHH");
String d=sf.format(new Date());
String name="车辆"+d+".xls";
String str="";
List<VehicleOperate> vehicleOperates=vehicleOperateService.getObjOperates(str);
HttpServletResponse response=ServletActionContext.getResponse();//>?
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//解决中文乱码问题
response.addHeader("Content-Disposition","attachment; filename=\""+ new String(name.getBytes(),"utf-8")+ "\"");
OutputStream os=response.getOutputStream();
WritableWorkbook book=Workbook.createWorkbook(os);
WritableSheet sheet=book.createSheet("1", 0);
//样式一
WritableFont font1=new WritableFont(WritableFont.TIMES,16,WritableFont.BOLD);
//格式1
WritableCellFormat format1=new WritableCellFormat(font1);
format1.setAlignment(jxl.format.Alignment.LEFT);//水平居左
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中
format1.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);//边框设置
format1.setWrap(true);//自动换行
//格式2
WritableCellFormat format2=new WritableCellFormat();
format2.setAlignment(jxl.format.Alignment.CENTRE);//水平居中
format2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中
format2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//边框设置
//格式3
WritableCellFormat format3=new WritableCellFormat();
format3.setAlignment(jxl.format.Alignment.CENTRE);//水平居中
format3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中
format3.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//边框设置
format3.setBackground(jxl.format.Colour.ICE_BLUE);//设置背景色
//单元格合并
sheet.mergeCells(0,0,34,0);
sheet.setRowView(0, 500);//设置行高
//设置列宽
sheet.setColumnView(1, 40);sheet.setColumnView(2, 20);sheet.setColumnView(3, 20);sheet.setColumnView(4, 20);
sheet.setColumnView(5, 20);sheet.setColumnView(6, 20);sheet.setColumnView(7, 20);
//设置excel的每列标题
sheet.addCell(new Label(0,0,"车辆",format1));
sheet.addCell(new Label(0,1,"序号",format3));
sheet.addCell(new Label(1,1,"车牌号",format3));
sheet.addCell(new Label(2,1,"驾驶员",format3));
System.out.println(vehicleOperates.size());
//单元格中添加数据
if(vehicleOperates!=null&&vehicleOperates.size()>0){
for(int i=0;i<vehicleOperates.size();i++){
Map<String, Object>map=(Map<String, Object>)vehicleOperates.get(i);
System.out.println(map.get("PlateNo"));
sheet.addCell(new Label(0,2+i,String.valueOf((i+1)),format2));
sheet.addCell(new Label(1,2+i,map.get("PlateNo")==null?"":map.get("PlateNo").toString(),format2));
sheet.addCell(new Label(2,2+i,map.get("Driver")==null?"":map.get("Driver").toString(),format2));
System.out.println(map.get("Driver")==null?"":map.get("Driver").toString());
}
}
book.write();
book.close();
os.close();
response.flushBuffer();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
导入
jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ include file="/../common/common-js.jsp"%>
<%@ include file="/../common/common-taglibs.jsp"%>
<%@ include file="/../common/common-css.jsp"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://"
+ request.getServerName() + ":" + request.getServerPort()
+ path + "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'reflectCase.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<style type="text/css">
a {text-decoration: none;}
a:hover {text-decoration: underline; color: #FF9933;}
td {vertical-align: middle;}
.hide {display: none;}
<textarea {font-size: 12; height: 50px;}
</style>
<script>
function uploadCompreUseFile() {
var s = document.getElementsByName("upload")[0].value;
s = s.slice(s.length - 3, s.length);
if (s != "xls") {
alert("请使用正确的格式文件导入。");
return false;
}
if (document.getElementsByName("upload")[0].value == "") {
alert("请选择上传的文件");
document.getElementsByName("upload")[0].focus();
return false;
} else {
document.forms[0].action = "/GIS/VehicleOperate/importExcel.do";
document.forms[0].submit();
}
}
</script>
</head>
<body>
<form action="" method="post" enctype="multipart/form-data">
<table style="border-collapse: collapse;">
<tr class="biaoge_title">
<td style="text-align:left" bgcolor="#D8E4F2">
<input type="button" value="导入" class="buttonAli" title="" name="buttonupdate" οnclick="uploadCompreUseFile()">
<input type="button" value="返回" class="buttonAli" title="" name="button4" onClick="back()" >
</td>
</tr>
</table>
<br />
<table style="border-collapse: collapse;">
<tr>
<td width="24%" nowrap bgcolor="#D8E4F2">车辆信息</td>
<td width="76%" colspan="3" class="pt12-black"><label><input type="file" name="upload" id="upload" /> </label></td>
</tr>
</table>
<br/>
<s:if test="errorInfos.size()>0"><font color="red" size="5" style="margin-left: 15px">导入文件中:</font><br></s:if>
<s:iterator value="errorInfos" id="#error" status="status">
<tr><font color="red" size="4" style="margin-left: 17px"><s:property value="#error"/><br></font></tr>
</s:iterator>
</form>
</body>
</html>
后台方法
//excel导入
//这个方法会对应一个页面，可以选择导入的内容
public String importExcel(){
try {
//private List<VehicleOperate> importList=new ArrayList<VehicleOperate>();
//private File upload
//注意加上get set方法
importList = readExcel(upload);
if (importList == null) {
this.setErrorInfos(this.getErrorInfos());
return "import";
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return "importList";
}
//private List<String> errorInfos = new ArrayList<String>();
//private String errorInfo;
//注意get set 方法
//这个方法返回LIST，在页面上展示
private List<VehicleOperate> readExcel(File file) throws Exception {
errorInfos.clear();
List<VehicleOperate> resultList = new ArrayList<VehicleOperate>();
Workbook workbook = null;
String checkResult = null;
try {
workbook = Workbook.getWorkbook(file);
Sheet sheet = workbook.getSheet(0);
//String title = sheet.getCell(0, 0).getContents().trim();
int row = sheet.getRows();
for (int i = 2; i < row; i++) {
if (sheet.getCell(0, i).getContents().trim().length() == 0
&& sheet.getCell(1, i).getContents().trim().length() == 0
&& sheet.getCell(2, i).getContents().trim().length() == 0) {
break;
}
VehicleOperate truck = new VehicleOperate();
if (sheet.getCell(1, i).getContents().trim() != null && sheet.getCell(2, i).getContents().trim() != "") {
if (CheckItem.checkString(sheet.getCell(1, i).getContents().trim(), 16) == null) {
truck.setPlateNo(sheet.getCell(1, i).getContents().trim() == null ? "" : sheet.getCell(1, i).getContents().trim());
} else {
checkResult = CheckItem.checkString(sheet.getCell(1, i).getContents().trim(), 16);
setErrorInfo(editErrorInfo(i, "车牌号", checkResult));
errorInfos.add(getErrorInfo());
}
if (CheckItem.checkString(sheet.getCell(2, i).getContents().trim(), 100) == null) {
truck.setDriver(sheet.getCell(2, i).getContents().trim() == null ? "" : (sheet.getCell(2, i).getContents().trim()));
} else {
checkResult = CheckItem.checkString(sheet.getCell(2, i).getContents().trim(), 100);
setErrorInfo(editErrorInfo(i, "驾驶员", checkResult));
errorInfos.add(getErrorInfo());
}
//truck.setDelflag("1");
resultList.add(truck);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
workbook.close();
}
if(errorInfos.size()==0){
return resultList;
}else{
return null;
}
}
//得到在展示页面之后就好做了，根据勾选的内容插到数据库表中去就完了
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ taglib prefix="s" uri="/struts-tags" %>
<%@ include file="../../../../common/common-js.jsp" %>
<%@ include file="../../../../common/common-taglibs.jsp"%>
<%@ include file="../../../../common/common-css.jsp"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'menuForm.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
</head>
<script type="text/javascript">
function save() {
var a=document.getElementById("PlateNo").value;
alert(a);
}
function back() {
window.location.href='${ctx}/machinejobcar/searchDefaultList.do';
}
</script>
<body>
<s:hidden id="errorInfo" name="errorInfo"></s:hidden>
<form method="post">
<table border="1" width="90%" style="BORDER-COLLAPSE: collapse; width: 90% " borderColor=#3E9FFF cellSpacing=0 cellPadding=3 id="table1">
<tr class="biaoge_title" style="background-color:#D7E7F7;">
<td>
<input type="button" value="保存" οnclick="save();">
<input type="button" value="返回" οnclick="back()">
</td>
</tr>
</table>
<br/>
<table border="1" width="90%" style="BORDER-COLLAPSE: collapse; width: 90% " borderColor=#3E9FFF cellSpacing=0 cellPadding=3 >
<tr align="center" style="font-weight:bold;background-color:#D7E7F7;">
<td style="text-align: center">选择</td>
<td style="text-align: center">序号</td>
<td style="text-align: center">车牌号</td>
<td style="text-align: center">驾驶员</td>
</tr>
<s:iterator value="importList" id="importList" status="status">
<tr <s:if test="#status.even">bgcolor="#D8E4F2"</s:if>>
<td bgcolor="white" class="pt12-black" style="text-align: center" height="30" ><input type="checkbox" value="<s:property value="%{delflag}"/>" name="selectedList[<s:property value="#status.index"/>].delflag" value="1" checked="checked"/></td>
<td bgcolor="white" class="pt12-black" style="text-align: center"><input type="hidden" value="<s:property value="%{areaid}"/>" name="selectedList[<s:property value="#status.index"/>].areaid"/><s:property value="#status.count"/></td>
<td bgcolor="white" class="pt12-black" style="text-align: center"><input type="hidden" id="PlateNo" value="<s:property value="%{PlateNo}"/>" name="selectedList[<s:property value="#status.index"/>].PlateNo"/><s:property value="%{PlateNo}"/></td>
<td bgcolor="white" class="pt12-black" style="text-align: center"><input type="hidden" value="<s:property value="%{Driver}"/>" name="selectedList[<s:property value="#status.index"/>].Driver"/><s:property value="%{Driver}"/></td>
</tr>
</s:iterator>
</table>
</form>
<script type="text/javascript">
var errorInfo = $("#errorInfo").val();
if(errorInfo!=null&&errorInfo.trim().length>0){
alert(errorInfo);
}
</script>
</body>
</html>
3150
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
