<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <% 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 'importList.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"> <link rel="stylesheet" type="text/css" href="css/common.css" /> <script type="text/javascript" src="../js/jquery-easyui-1.2.6/jquery-1.7.2.min.js"></script> <link rel="stylesheet" type="text/css" href="../js/jquery-easyui-1.2.6/themes/default/easyui.css" /> <link rel="stylesheet" type="text/css" href="../js/jquery-easyui-1.2.6/themes/icon.css" /> <script type="text/javascript" src="../js/jquery-easyui-1.2.6/jquery.easyui.min.js"></script> <script type="text/javascript" src="../js/jquery-easyui-1.2.6/locale/easyui-lang-zh_CN.js"></script> <script type="text/javascript" src="../js/commons.js"></script> <link rel="stylesheet" type="text/css" href="css/uploadify.css" /> <script type="text/javascript" src="../js/swfobject.js"></script> <script type="text/javascript" src="../js/jquery.uploadify.v2.1.0.min.js"></script> <script type="text/javascript"> $(function(){ //文件上传uploadify $('#fileInput').uploadify({ 'uploader': 'js/swf/uploadify.swf', 'script': 'importdata-upload', //指定服务端处理类的入口 'scriptData' :{'templateId':$('#templates').combobox('getValue')}, //参数 'folder': 'default', 'fileDataName': 'fileInput', //和input的name属性值保持一致就好，Struts2就能处理了 'queueID': 'fileQueue', 'auto': false,//是否选取文件后自动上传 'multi': true,//是否支持多文件上传 'fileDesc' : 'Excel文档', 'fileExt' : '*.xls', 'sizeLimit' : 1024*1024,//最大1M 'simUploadLimit' : 1,//每次最大上传文件数 'buttonImg': 'images/preview.png',//按钮上的图片 'buttonCursor':'hand',//鼠标悬停在按钮时的样子 'buttonText': ' ',//按钮上的文字 'onComplete': function (event, queueID, fileObj, response, data) { //alert(response); var result = eval("(" +response + ")" ); var status = result.status; var msg = result.message; if(status.trim() =="ok" || status.trim()=="OK"){ $.messager.alert("提示信息",msg); $('#importDialog').dialog('close'); $('#t_importdata').datagrid('load',null);//刷新表格数据 }else{ $.messager.alert("提示信息",msg); } } }); /** * 初始化数据表格 */ $('#t_importdata').datagrid({ //idField:'id' ,//有这个属性的时候getSelections可能出问题 title:'数据列表' , fit:true , height:450 , url:'importdata-list' , fitColumns:true , striped: true , //隔行变色特性 loadMsg: '数据正在加载,请耐心的等待...' , rownumbers:true , frozenColumns:[[ //冻结列特性 ,不要与fitColumns 特性一起使用 { field:'ck' , width:50 , checkbox: true } ]], columns:[[ { field:'importid' , title:'主表id' , width:100 , hidden:true },{ field:'importDataType' , title:'数据类型' , width:100 , sortable : true },{ field:'importDate' , title:'导入时间' , width:100 , sortable : true },{ field:'importStatus' , title:'导入标志' , width:100 , formatter:function(value , record , index){ if(value == 1){ return '导入成功' ; } else if( value == 0){ return '导入失败' ; } } },{ field:'handleDate' , title:'处理时间' , width:100 },{ field:'handleStatus' , title:'处理标志' , width:100 , formatter:function(value , record , index){ if(value == 1){ return '已处理' ; } else if( value == 0){ return '未处理' ; } } } ]] , pagination: true , pageSize: 10 , pageList:[5,10,15,20,50], //从这里开始加功能按钮实现导入模板 //添加toolbar,定义导入模板和明细查看按钮 toolbar:[ { text:'删除', iconCls:'icon-cancel', handler:function(){ var rows=$("#t_importdata").datagrid("getSelections"); if(rows.length==0){ $.messager.alert("提示信息","请选择要删除的行"); return; } var importids=""; for(var i=0;i<rows.length;i++){ if(importids=="" || importids==null){ importids = rows[i].importid; }else{ importids = importids +","+ rows[i].importid; } } $.messager.confirm("提示","确定删除么?",function (res) { if(res){ $.ajax({ url:'importdata-deleteByID', type:'POST', dataType:'json', data:{importids:importids}, success:function(data){ $('#t_importdata').datagrid('load',null);//刷新表格数据 } }); } }); } }, { text:'导入模板', iconCls:'icon-add', handler:function(){ $('#templateDialog').dialog('open'); //点击导入模板打开dialog } }, { text:'导入明细查看', iconCls:'icon-edit', handler:function(){ //动态创建datagrid $('#divDataGrid').html('<table id="tatalTb"></table>'); var arr = $('#t_importdata').datagrid('getSelections'); if(arr.length != 1){ $.messager.alert('提示信息','只能选择一条记录进行查看！'); return; }else{ $('#importDetailsDialog').dialog('open'); $.ajax({ url:'importdata-columns', type:'POST', dataType:'json', data:{templateId:arr[0].importDataType}, success:function(data){ $('#tatalTb').datagrid({ url: 'importdata-columndatas', fitColumns: true, idField:"appId", columns:data, queryParams:{importDataId:arr[0].importid}, toolbar:[ { text:'确认导入', iconCls:'icon-add', handler:function(){ //只有未处理并且没有错误代码的数据允许确认导入 var detailDatas = $('#tatalTb').datagrid('getData'); for(var i = 0;i<detailDatas.length;i++){ var cgbz = detailDatas.rows[i].cgbz; var hcode = detailDatas.rows[i].hcode; if(cgbz!="未处理" || (hcode.indexOf("#000")>=0)){ $.messager.alert("提示信息","已处理或数据有误，请核对后再重新导入！"); return; } } $.ajax({ url:'importdata-doimport', type:"POST", dataType:'json', data:{importDataId:arr[0].importid}, success:function(data){ var status = data.status; var message = data.message; if(status == "ok"){ $.messager.alert("提示信息",message); $('#tatalTb').datagrid('reload',null); }else{ $.messager.alert("提示信息",message); } } }); } },{ text:'返回', iconCls:'icon-back', handler:function(){ $('#importDetailsDialog').dialog('close'); } } ] }); } }); } } } ] }); $('#okBtn').click(function(){ //获取选择模板ID var str = $('#templates').combobox('getValue');//获取当前选中的值 if(str==null || str==""){ $.messager.alert("提示信息","请选择模板!"); return; } $('#templateDialog').dialog('close'); $('#importDialog').dialog('open'); var val = "download?templateId=" + str;//超链接的形式发送下载请求 $('#downloadTemplate').attr('href',val); }); $('#uploadBtn').click(function(){ var val = $('#templates').combobox('getValue'); $('#fileInput').uploadifySettings('scriptData',{'templateId':val}); $('#fileInput').uploadifyUpload(); }); }); </script> </head> <body> <div id="lay" class="easyui-layout" style="width: 100%;height:100%" > <div region="center" > <table id="t_importdata"></table> </div> </div> <div id="templateDialog" title="选择模板" modal=true draggable=false class="easyui-dialog" closed=true style="width: 350px;height:220px"> <form action=""> <table> <tr> <td>选择模板：</td> <td> <input id="templates" name="templates" class="easyui-combobox" panelHeight="auto" url="importdata-templates" valueField="templateId" textField="templateName" value="" /> <a id="okBtn" class="easyui-linkbutton">确定</a> </td> </tr> </table> </form> </div> <div id="importDialog" title="导入Excel" modal=true draggable=false class="easyui-dialog" closed=true style="width:350px;height:220px;"> <form id="importForm" action="importdata-upload" method="post"> <table> <tr> <td>下载模板:</td> <td> <a id = "downloadTemplate" >点击下载模板</a> </td> </tr> <tr> <td>浏览(上传):</td> <td> <input id="fileInput" name="fileInput" type="file" /> </td> </tr> <tr> <td colspan="2"> <%--进度条--%> <div id="fileQueue"></div><a id="uploadBtn" class="easyui-linkbutton" >导入</a> </td> </tr> </table> </form> </div> <div id="importDetailsDialog" title="导入明细查看" modal=true draggable=false class="easyui-dialog" closed=true style="width:800px;height:500px;"> <div id="divDataGrid"></div> </div> </body> </html>
上面就是导入操作的界面。涉及到uploadify,easyUI等。
在看看对应的action:
package com.huangteng.demo.action; import com.alibaba.fastjson.JSON; import com.huangteng.demo.model.ColumnInfo; import com.huangteng.demo.model.ImportData; import com.huangteng.demo.model.ImportDataDetail; import com.huangteng.demo.model.Template; import com.huangteng.demo.service.ImportDataService; import com.opensymphony.xwork2.ActionSupport; import net.sf.json.JSONArray; import org.apache.commons.beanutils.BeanUtils; import org.apache.commons.io.FileUtils; import org.apache.commons.lang3.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.struts2.ServletActionContext; import org.jdom.Document; import org.jdom.Element; import org.jdom.input.SAXBuilder; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; /** * Created by ht on 2016/8/8. */ public class importDataAction extends ActionSupport{ private ImportDataService importDataService = new ImportDataService(); private List<ImportData> importDataList = new ArrayList<ImportData>(); //分页属性 private int page; private int rows; private String sort; private String order; //模板id private String templateId; //输入文件 private File fileInput; private String importids; public String getImportids() { return importids; } public void setImportids(String importids) { this.importids = importids; } public String getImportDataId() { return importDataId; } public void setImportDataId(String importDataId) { this.importDataId = importDataId; } private String importDataId; public File getFileInput() { return fileInput; } public void setFileInput(File fileInput) { this.fileInput = fileInput; } public String getTemplateId() { return templateId; } public void setTemplateId(String templateId) { this.templateId = templateId; } public String getOrder() { return order; } public void setOrder(String order) { this.order = order; } public ImportDataService getImportDataService() { return importDataService; } public void setImportDataService(ImportDataService importDataService) { this.importDataService = importDataService; } public List<ImportData> getImportDataList() { return importDataList; } public void setImportDataList(List<ImportData> importDataList) { this.importDataList = importDataList; } public int getPage() { return page; } public void setPage(int page) { this.page = page; } public int getRows() { return rows; } public void setRows(int rows) { this.rows = rows; } public String getSort() { return sort; } public void setSort(String sort) { this.sort = sort; } public String execute(){ return SUCCESS; } //获取数据导入的数据列表 public void list(){ HttpServletResponse response = ServletActionContext.getResponse(); response.setContentType("text/html;charset=utf-8"); importDataList = importDataService.list(page,rows,sort,order); String json="{"+"\"total\":"+importDataList.size()+","+"\"rows\":"+ JSON.toJSONString(importDataList)+"}"; //System.out.println(json); try { response.getWriter().write(json); } catch (IOException e) { e.printStackTrace(); } } //获取导入模板，并打印在导入模板选择的下拉选 public void templates(){ HttpServletResponse response = ServletActionContext.getResponse(); response.setContentType("text/html;charset=utf-8"); List<Template> list = new ArrayList<Template>(); Template t = new Template(); //模板文件在web/template/student.xml t.setTemplateId("student"); t.setTemplateName("student"); list.add(t); try { response.getWriter().write(JSON.toJSONString(list)); } catch (IOException e) { e.printStackTrace(); } } /** * 上传模板 * 涉及xml文件解析，文件上传等知识点 * Created by ht on 2016/8/9. */ public void upload(){ //获取response HttpServletResponse response = ServletActionContext.getResponse(); response.setContentType("text/html;charset=utf-8"); //获取时间并转字符串 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); String dateNow = sdf.format(new Date()); //保存主表信息 ImportData importData = new ImportData(); importData.setImportid(String.valueOf(System.currentTimeMillis())); importData.setImportDataType(templateId); importData.setImportDate(dateNow); importData.setImportStatus("1");//导入成功 importData.setHandleDate(null); importData.setHandleStatus("0");//未处理 importDataService.saveImportData(importData); //解析模板 try{ //1 获取模板文件 String path = ServletActionContext.getServletContext().getRealPath("/template"); path = path +"\\"+templateId+".xml"; File file = new File(path); //2 POI登场 读取Excel文件 HSSFWorkbook wb = new HSSFWorkbook(FileUtils.openInputStream(fileInput)); HSSFSheet sheet = wb.getSheetAt(0); //3 解析xml模板文件 用SaxBuilder SAXBuilder builder = new SAXBuilder(); Document parse = builder.build(file); Element root = parse.getRootElement();//获取到根元素 Element tbody = root.getChild("tbody"); Element tr = tbody.getChild("tr"); List<Element> children = tr.getChildren("td"); //获取开始行和开始列 int firstRow = tr.getAttribute("firstrow").getIntValue(); int firstCol = tr.getAttribute("firstcol").getIntValue(); //获取Excel最后一行行号 int lastRowNum = sheet.getLastRowNum(); //循环每一行，处理数据 for(int i=firstRow;i<=lastRowNum;i++){ //初始化明细数据 ImportDataDetail importDataDetail = new ImportDataDetail(); importDataDetail.setImportid(importData.getImportid()); importDataDetail.setCgbz("0");//未处理 //读取某行 HSSFRow row = sheet.getRow(i); //对该行的非空判断 if(isEmptyRow(row)){ continue; //跳过接下来的步骤 } int lastCellNum = row.getLastCellNum();//获取一行最后的列号 //行非空，对所有单元格取值 for(int j=firstCol;j<lastCellNum;j++){ Element td = children.get(j-firstCol); HSSFCell cell = row.getCell(j); //如果单元格为空，跳过 if(cell == null){ continue; } //获取单元格值 String value = getCellValue(cell,td); //导入实体明细赋值 if(StringUtils.isNotBlank(value)){ //当value中有异常信息时，获取截取","后的数组，把错误编号和错误提示信息 //分别存入对应的字段中 //BeanUtils.setProperty提供三个参数，第一是JavaBean对象，第二个是要操作的属性名，第三个为要设置的具体的值 if(value.indexOf("#000")>0){ String[] info = value.split(","); importDataDetail.setHcode(info[0]); importDataDetail.setMsg(info[1]); BeanUtils.setProperty(importDataDetail,"col"+j,info[2]); }else{ BeanUtils.setProperty(importDataDetail,"col"+j,value); } } } importDataService.saveImportDataDetail(importDataDetail); } String str = "{\"status\":\"OK\",\"message\":\"导入数据成功!\"}"; response.getWriter().write(str); }catch (Exception e){ String str = "{\"status\":\"noOK\",\"message\":\"导入数据失败!\"}"; try { response.getWriter().write(str); } catch (IOException e1) { e1.printStackTrace(); } e.printStackTrace(); } } /** * 非空判断 */ public Boolean isEmptyRow(HSSFRow row){ boolean flag = true; for(int i=0;i<row.getLastCellNum();i++){ HSSFCell cell =row.getCell(i); if(cell != null){ if(StringUtils.isNotBlank(cell.toString())){ return false; } } } return flag; } /** * 获取单元格的值 */ public String getCellValue(HSSFCell cell,Element td){ //首先获取单元格的位置 int i = cell.getRowIndex()+1; int j = cell.getColumnIndex()+1; String returnValue = ""; try{ //获取模板文件对单元格格式的限制 String type = td.getAttribute("type").getValue(); boolean isNullAble = td.getAttribute("isnullable").getBooleanValue(); int maxlength = 9999; if(td.getAttribute("maxlength")!=null){ maxlength = td.getAttribute("maxlength").getIntValue(); } //根据单元格格式取值 String value = null; switch (cell.getCellType()){ case HSSFCell.CELL_TYPE_STRING:{ value = cell.getStringCellValue(); break; } case HSSFCell.CELL_TYPE_NUMERIC:{ //判断是不是日期 if("data,datatime".indexOf(type)>=0){ Date date = cell.getDateCellValue(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); value = sdf.format(date); }else{ value = String.valueOf(cell.getNumericCellValue()); } break; } }; //非空、长度校验 if(!isNullAble && StringUtils.isBlank(value)){ returnValue = "#0001,第" + i + "行第" +j +"列不能为空！"+"," + value; }else if(StringUtils.isNotBlank(value) && (value.length()>maxlength)){ returnValue = "#0002,第" + i + "行第" +j +"列长度超过最大长度！"+"," + value; }else{ returnValue = value; } }catch (Exception e){ e.printStackTrace(); } return returnValue; } /** * 动态获取表头信息 */ public void columns(){ HttpServletResponse response = ServletActionContext.getResponse(); response.setContentType("text/html;charset=utf-8"); //获取表头信息 List<ColumnInfo> list = getColumns(); //转换json对象返回 String json ="["+ JSON.toJSONString(list) + "]"; try { response.getWriter().write(json); } catch (IOException e) { e.printStackTrace(); } } /** * 动态获取表头 * @return */ private List<ColumnInfo> getColumns() { List<ColumnInfo> list = new ArrayList<ColumnInfo>(); //获取模板文件 String path = ServletActionContext.getServletContext().getRealPath("/template"); path = path + "\\" + templateId + ".xml"; File file = new File(path); //解析模板文件 SAXBuilder builder = new SAXBuilder(); try { Document parse = builder.build(file); Element root = parse.getRootElement(); Element thead = root.getChild("thead"); Element tr = thead.getChild("tr"); List<Element> children = tr.getChildren(); ColumnInfo c = null; //添加处理标志、失败代码，失败说明 c = createColumnInfo("cgbz","处理标志",120,"center"); list.add(c); c = createColumnInfo("hcode","失败代码",120,"center"); list.add(c); c = createColumnInfo("msg","失败说明",120,"center"); list.add(c); for (int i = 0; i < children.size(); i++) { Element th = children.get(i); String value = th.getAttribute("value").getValue(); c = createColumnInfo("col"+i,value,120,"center"); list.add(c); } } catch (Exception e) { e.printStackTrace(); } return list; } /** * 创建column对象 */ private ColumnInfo createColumnInfo(String fieldId, String title, int width, String align) { ColumnInfo c = new ColumnInfo(); c.setField(fieldId); c.setTitle(title); c.setWidth(width); c.setAlign(align); return c; } /** * 获取明细数据 */ public void columndatas(){ HttpServletResponse response = ServletActionContext.getResponse(); response.setContentType("text/html;charset=utf-8"); //获取明细数据 List<ImportDataDetail> list = importDataService.getImportDataDetailsByMainId(importDataId); String json = "{\"total\":"+list.size()+", \"rows\":"+JSON.toJSONString(list)+"}"; try { response.getWriter().write(json); } catch (IOException e) { e.printStackTrace(); } } /** * 确认导入 */ public void doimport(){ HttpServletResponse response = ServletActionContext.getResponse(); response.setContentType("text/html;charset=utf-8"); //将导入的明细数据已到student表中 importDataService.saveStudents(importDataId); //修改主表、明细表处理标志及时间 SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd"); String dateNow = sf.format(new Date()); importDataService.updImportDataStatus(dateNow, importDataId); importDataService.updImportDataDetailStatus(importDataId); String str = "{\"status\":\"ok\",\"message\":\"确认成功！\"}"; try { response.getWriter().write(str); } catch (IOException e) { e.printStackTrace(); } } /** * * 删除 */ public void deleteByID(){ del(importids); } public void del(String importids){ if(importids.indexOf(",")>0){ String[] it=importids.split(","); for(int i=0;i<it.length;i++){ importDataService.del(it[i]); } }else{ importDataService.del(importids); } } }
看看service:
package com.huangteng.demo.service; import com.huangteng.demo.model.ImportData; import com.huangteng.demo.model.ImportDataDetail; import com.huangteng.demo.utils.DB; import com.mysql.jdbc.ConnectionFeatureNotAvailableException; import org.apache.commons.lang3.StringUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; /** * Created by ht on 2016/8/9. */ public class ImportDataService { //从数据库查询导入项目信息 public List<ImportData> list(int currentPage, int pageSize,String sort,String order){ Connection conn = DB.creatConn(); String sql = "select * from t_importdata where 1=1"; if(StringUtils.isNotBlank(sort)){ sql+="order by"+sort; } if(StringUtils.isNotBlank(order)){ sql+=" "+order; } sql+=" limit "+(currentPage-1)*pageSize+","+pageSize; //这里的limit前边一定记得加空格哦 PreparedStatement ps = DB.prepare(conn,sql); List<ImportData> importDatas = new ArrayList<ImportData>(); try { ResultSet rs = ps.executeQuery(); ImportData i = null; while(rs.next()){ i = new ImportData(); i.setImportid(rs.getString("importid")); i.setImportDataType(rs.getString("importdatatype")); i.setImportDate(rs.getString("importdate")); i.setImportStatus(rs.getString("importstatus")); i.setHandleDate(rs.getString("handledate")); i.setHandleStatus(rs.getString("handlestatus")); importDatas.add(i); } } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(conn); DB.close(ps); } return importDatas; } //保存操作，参数是ImportData对象 public void saveImportData(ImportData i){ Connection conn= DB.creatConn(); String sql = "insert into t_importdata(importid,importdatatype,importdate,importstatus,handledate,handlestatus)" + "values(?,?,?,?,?,?)"; PreparedStatement ps = DB.prepare(conn,sql); try { ps.setString(1, i.getImportid()); ps.setString(2, i.getImportDataType()); ps.setString(3, i.getImportDate()); ps.setString(4, i.getImportStatus()); ps.setString(5, i.getHandleDate()); ps.setString(6, i.getHandleStatus()); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { DB.close(conn); DB.close(ps); } } //保存操作 public void saveImportDataDetail(ImportDataDetail i){ Connection conn = DB.creatConn(); String sql = "insert into t_importdatadetail(importid,cgbz,hcode,msg,col0,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10) " + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement ps = DB.prepare(conn, sql); try { ps.setString(1, i.getImportid()); ps.setString(2, i.getCgbz()); ps.setString(3, i.getHcode()); ps.setString(4, i.getMsg()); ps.setString(5, i.getCol0()); ps.setString(6, i.getCol1()); ps.setString(7, i.getCol2()); ps.setString(8, i.getCol3()); ps.setString(9, i.getCol4()); ps.setString(10, i.getCol5()); ps.setString(11, i.getCol6()); ps.setString(12, i.getCol7()); ps.setString(13, i.getCol8()); ps.setString(14, i.getCol9()); ps.setString(15, i.getCol10()); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { DB.close(conn); DB.close(ps); } } //根据id查询导入数据信息 public List<ImportDataDetail> getImportDataDetailsByMainId(String importDataId) { Connection conn = DB.creatConn(); String sql = "select * from t_importdatadetail where importid = '" + importDataId + "'"; PreparedStatement ps = DB.prepare(conn, sql); List<ImportDataDetail> importDataDetails = new ArrayList<ImportDataDetail>(); try { ResultSet rs = ps.executeQuery(); ImportDataDetail i = null; while(rs.next()) { i = new ImportDataDetail(); i.setImportDetailId(rs.getInt("importdetailid")); i.setImportid(rs.getString("importid")); i.setCgbz("1".equals(rs.getString("cgbz"))?"已处理":"未处理"); i.setHcode(rs.getString("hcode")); i.setMsg(rs.getString("msg")); i.setCol0(rs.getString("col0")); i.setCol1(rs.getString("col1")); i.setCol2(rs.getString("col2")); i.setCol3(rs.getString("col3")); i.setCol4(rs.getString("col4")); i.setCol5(rs.getString("col5")); i.setCol6(rs.getString("col6")); i.setCol7(rs.getString("col7")); i.setCol8(rs.getString("col8")); i.setCol9(rs.getString("col9")); i.setCol10(rs.getString("col10")); importDataDetails.add(i); } } catch (SQLException e) { e.printStackTrace(); } DB.close(ps); DB.close(conn); return importDataDetails; } //保存学生信息 public void saveStudents(String importId) { Connection conn = DB.creatConn(); String id = Long.toString(System.currentTimeMillis()); String sql = "insert into t_student(stunum,stuname,stuage,stusex,stubirthday,stuhobby) " + "select col0,col1,col2,col3,col4,col5 from t_importdatadetail " + " where importid = '" + importId + "'"; PreparedStatement ps = DB.prepare(conn, sql); try { ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } DB.close(ps); DB.close(conn); } public void updImportDataStatus(String handleDate,String importId) { Connection conn = DB.creatConn(); String id = Long.toString(System.currentTimeMillis()); String sql = "update t_importdata set handledate =?,handlestatus='1' where importid = ?"; PreparedStatement ps = DB.prepare(conn, sql); try { ps.setString(1,handleDate); ps.setString(2,importId); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } DB.close(ps); DB.close(conn); } public void updImportDataDetailStatus(String importId) { Connection conn = DB.creatConn(); String id = Long.toString(System.currentTimeMillis()); String sql = "update t_importdatadetail set cgbz ='1',hcode='',msg='导入数据成功!' where importid = ?"; PreparedStatement ps = DB.prepare(conn, sql); try { ps.setString(1,importId); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } DB.close(ps); DB.close(conn); } public void del(String importids){ Connection conn = DB.creatConn(); String sql="delete from t_importdata where importid=?"; PreparedStatement ps = DB.prepare(conn, sql); try { ps.setString(1,importids); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } DB.close(ps); DB.close(conn); } }
实现下载的action:
package com.huangteng.demo.action; import com.opensymphony.xwork2.ActionSupport; import org.apache.commons.io.FileUtils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.struts2.ServletActionContext; import org.jdom.Attribute; import org.jdom.Document; import org.jdom.Element; import org.jdom.input.SAXBuilder; import java.io.*; import java.net.URLEncoder; import java.util.List; /** * Created by ht on 2016/8/9. */ public class FileDownloadAction extends ActionSupport { private String templateId; private String templateName; public String getTemplateId() { return templateId; } public void setTemplateId(String templateId) { this.templateId = templateId; } public String getTemplateName() { return templateName; } public void setTemplateName(String templateName) { this.templateName = templateName; } @Override public String execute() throws Exception { return SUCCESS; } /** * 获取输入流 * @return * @throws IOException */ public InputStream getInputStream() throws IOException { //创建模板 createTemplate(); String path = ServletActionContext.getServletContext().getRealPath("/template"); String filepath = path +"\\" + templateName + ".xls"; File file = new File(filepath); return FileUtils.openInputStream(file); } /** * 获取文件名 */ public String getDownloadFileName(){ String downloadFileName = ""; String filename = templateName + ".xls"; try { downloadFileName = URLEncoder.encode(filename,"UTF-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } return downloadFileName; } /** * 创建模板 */ private void createTemplate() { String path = ServletActionContext.getServletContext().getRealPath("/template"); File file = new File(path,templateId+".xml"); SAXBuilder builder = new SAXBuilder(); try{ //获取根元素 Document parse = builder.build(file); Element root = parse.getRootElement(); //POI登场 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet0"); //获取名字 templateName = root.getAttribute("name").getValue(); int rownum = 0; int column = 0; //1 一个一个来解析，第一个是studen.xml中的colgroup Element colgroup = root.getChild("colgroup"); setColumnWidth(sheet,colgroup); //2 第二个是studen.xml中的title Element title = root.getChild("title"); List<Element> trs = title.getChildren("tr"); for(int i=0;i<trs.size();i++){ Element tr = trs.get(i); List<Element> tds = tr.getChildren("td"); HSSFRow row = sheet.createRow(rownum); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); for(column=0;column<tds.size();column++){ Element td = tds.get(column); HSSFCell cell = row.createCell(column); //获取标签属性 Attribute rowSpan = td.getAttribute("rowspan"); Attribute colSpan = td.getAttribute("colspan"); Attribute value = td.getAttribute("value"); if(value!=null){ String val = value.getValue(); cell.setCellValue(val); int rspan = rowSpan.getIntValue() - 1; int cspan = colSpan.getIntValue() -1; //设置字体 HSSFFont font = wb.createFont(); font.setFontName("宋体"); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short)12); cellStyle.setFont(font); cell.setCellStyle(cellStyle); //合并单元格 sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan)); } } rownum++; } //3 thead部分的解析 Element thead = root.getChild("thead"); trs = thead.getChildren("tr"); for (int i = 0; i < trs.size(); i++) { Element tr = trs.get(i); HSSFRow row = sheet.createRow(rownum); List<Element> ths = tr.getChildren("th"); for(column = 0;column < ths.size();column++){ Element th = ths.get(column); Attribute valueAttr = th.getAttribute("value"); HSSFCell cell = row.createCell(column); if(valueAttr != null){ String value =valueAttr.getValue(); cell.setCellValue(value); } } rownum++; } //4 tboby部分 Element tbody = root.getChild("tbody"); Element tr = tbody.getChild("tr"); int repeat = tr.getAttribute("repeat").getIntValue(); List<Element> tds = tr.getChildren("td"); for (int i = 0; i < repeat; i++) { HSSFRow row = sheet.createRow(rownum); for(column =0 ;column < tds.size();column++){ Element td = tds.get(column); HSSFCell cell = row.createCell(column); setType(wb,cell,td); } rownum++; } //输出Excel模板 File tempFile = new File(path, templateName + ".xls"); tempFile.delete(); tempFile.createNewFile(); FileOutputStream stream = FileUtils.openOutputStream(tempFile); wb.write(stream); stream.close(); }catch (Exception e){ e.printStackTrace(); } } /** * 处理column组 * 设置宽度，算法来于百度 * @param sheet * @param colgroup */ private static void setColumnWidth(HSSFSheet sheet,Element colgroup){ List<Element> cols= colgroup.getChildren("col"); for(int i=0;i<cols.size();i++){ Element col = cols.get(i); //获取属性 Attribute width = col.getAttribute("width"); String unit = width.getValue().replaceAll("[0-9,\\.]", "");//取出单位 String value = width.getValue().replaceAll(unit, "");//取出数字 int v=0; if(StringUtils.isBlank(unit) || "px".endsWith(unit)){ v = Math.round(Float.parseFloat(value) * 37F); }else if ("em".endsWith(unit)){ v = Math.round(Float.parseFloat(value) * 267.5F); } sheet.setColumnWidth(i, v); } } /** * 格式设置 * @param wb * @param cell * @param td */ private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td){ //获取type Attribute typeAttr = td.getAttribute("type"); String type = typeAttr.getValue(); //样式 HSSFDataFormat format = wb.createDataFormat(); HSSFCellStyle cellStyle = wb.createCellStyle(); //判断type类型 if("NUMERIC".equalsIgnoreCase(type)){ cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); Attribute formatAttr = td.getAttribute("format"); String formatValue = formatAttr.getValue(); formatValue = StringUtils.isNotBlank(formatValue)? formatValue : "#,##0.00"; cellStyle.setDataFormat(format.getFormat(formatValue)); }else if("STRING".equalsIgnoreCase(type)){ cell.setCellValue(""); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cellStyle.setDataFormat(format.getFormat("@")); }else if("DATE".equalsIgnoreCase(type)){ cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cellStyle.setDataFormat(format.getFormat("yyyy-m-d")); }else if("ENUM".equalsIgnoreCase(type)){ //四个参数是起始行、列，终止行、列 CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex()); Attribute enumAttr = td.getAttribute("format"); String enumValue = enumAttr.getValue(); //生成下拉菜单 DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(",")); HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint); wb.getSheetAt(0).addValidationData(dataValidation); } cell.setCellStyle(cellStyle); } }
前面的章节讲学生信息导出来为Excel文件的时候，少说了一个写的工具类，不然大家看了会疑惑
package com.huangteng.demo.utils; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import java.lang.reflect.Method; import java.util.List; /** * Created by ht on 2016/8/8. * 导出为Excel文件的工具类 */ public class ExportUtils { //导出header public static void outputHeaders(String[] headersInfo, HSSFSheet sheet){ HSSFRow row = sheet.createRow(0); for(int i =0;i<headersInfo.length;i++){ sheet.setColumnWidth(i,4000);//设置列宽样式 row.createCell(i).setCellValue(headersInfo[i]); } } //导出主体内容 public static void outputColumns(String[] headersInfo, List columnsInfo,HSSFSheet sheet,int rowIndex ){ HSSFRow row ; //循环导出数据 for (int i = 0; i < columnsInfo.size(); i++) { row = sheet.createRow(rowIndex+i); Object obj = columnsInfo.get(i); //每一列 for (int j = 0; j < headersInfo.length; j++) { Object value = getFieldValueByName(headersInfo[j],obj); row.createCell(j).setCellValue(value.toString()); } } } /** * 利用反射，通过get/set方法来获取每一column对应的值 * @param fieldName * @param obj * @return */ private static Object getFieldValueByName(String fieldName, Object obj) { String firstLetter = fieldName.substring(0,1).toUpperCase(); String getter = "get" +firstLetter + fieldName.substring(1);//拼接get方法的名称 try { Method method = obj.getClass().getMethod(getter, new Class[]{}); Object value = method.invoke(obj, new Object[]{}); return value; } catch (Exception e) { e.printStackTrace(); System.out.println("反射异常"); return null; } } }
现在的struts2配置文件是：
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.3//EN" "http://struts.apache.org/dtds/struts-2.3.dtd"> <struts> <package name="default" namespace="/" extends="struts-default"> <!--学生信息--> <action name="student-*" class="com.huangteng.demo.action.StudentAction" method="{1}"> <result>/jsp/studentList.jsp</result> </action> <!--数据导入--> <action name="importdata-*" class="com.huangteng.demo.action.importDataAction" method="{1}"> <result>/jsp/importList.jsp</result> </action> <!--主界面--> <action name="main" class="com.huangteng.demo.action.MainAction"> <result>/jsp/layout.jsp</result> </action> <!--下载--> <action name="download" class="com.huangteng.demo.action.FileDownloadAction"> <result name="success" type="stream"> <param name="bufferSize">8192</param> <param name="contentType">application/octet-stream</param> <param name="inputName">inputStream</param> <param name="contentDisposition">attachment;filename="${downloadFileName}</param> </result> </action> </package> </struts>
后边的代码实在是太多，一个一个总结的话时间太紧了。只能这样一股脑丢出来了，抱歉。
4785
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
