关闭

excel导入导出

标签: java excelexceljava导入 导出
163人阅读 评论(0) 收藏 举报

因为之后的功能模块涉及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" onclick="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" />&nbsp;&nbsp;</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="保存" onclick="save();">
<input type="button" value="返回" onclick="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>



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1202次
    • 积分:83
    • 等级:
    • 排名:千里之外
    • 原创:7篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条