<%@ 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>
后边的代码实在是太多，一个一个总结的话时间太紧了。只能这样一股脑丢出来了，抱歉。