EXTJS+SSH(JXL实现导入导出功能)
前端(创建导入导出按钮):
js部分:
/*
* 导入
*/
importItem : function() {
Ext.QuickTips.init();
Ext.onReady(function() {
var form = new Ext.form.FormPanel({
baseCls : 'x-plain',
labelWidth : 80,
url : cfg.cxt +'/eplantrain/manage/upload/excel.sj',
fileUpload : true,
defaultType : 'textfield',
items : [ {
xtype: 'fileuploadfield',
name: 'file',
fieldLabel: '   文件名',
labelWidth: 50,
msgTarget: 'side',
allowBlank: false,
anchor: '90%',
buttonText: '选择Excel文件'
},{
xtype: 'label',
text: '(请选择Excel导入,注意录入的字段格式)',
style:{
marginLeft:'10px'
}
} ]
});
var win = new Ext.Window({
title : '上传Excel',
width : 400,
height : 130,
layout : 'fit',
plain : true,
bodyStyle : 'padding:5px;',
buttonAlign : 'center',
items : form,
buttons : [ {
text : '导入',
handler : function() {
if (form.form.isValid()) {
Ext.MessageBox.show({
title : '请等待',
msg : '导入中...',
progressText : '',
width : 300,
progress : true,
closable : false,
animEl : 'loding'
});
form.getForm().submit({
success : function(form, action) {
listView.queryResItem();
Ext.Msg.alert("提示",action.result.msg);
win.hide();
},
failure : function(form, action) {
Ext.Msg.alert('警告',"Excel导入失败!");
win.hide();
}
});
}
}
}, {
text : '关闭',
handler : function() {
win.hide();
}
} ]
});
win.show();
});
},
/*
导出文件(这里根据选择id进行批量导出)
*/
exportFile: function () {
var sm = grid.getSelectionModel();
if (sm.getCount() == 0) {
Ext.Mytip.msg('提示', '您尚未选择要导出的数据!');
return;
}
var ids = [];
sm.each(function(r) {
ids.push(r.data.id);
});
// Ext.Msg.confirm('请确认',
// '<span style="color:red"><b>提示:</b>确定导出吗?</span>',
// function(btn,text) {
// if (btn == 'yes') {
//
// Ext.Ajax.request({
// url :cfg.cxt + "/eplantrain/manage/exports.sj",
//
// success : function(response) {
//
// //window.location.href = cfg.cxt + "/eplantrain/manage/exports.sj";
// var rt = Ext.decode(response.responseText);
// alert(111+response.responseText.success==true+rt);
// if (rt.success) {
// listView.queryResItem();
// Ext.Mytip.msg('提示', '导出成功!');
// } else {
// Ext.Mytip.msg('提示', rt.msg);
// }
// },
// failure : safetys.ajaxError,
// params : {
// "ids" : ids.join(',')
// }
// });
// }
// });
window.location.href = cfg.cxt + "/eplantrain/manage/exports.sj?ids="+ids;
},
后台代码(这里应把代码提取到业务层,控制层直接调用方法即可,也可抽取成工具类调用,这里不多加累述):
//导出Excel****************************************
@SuppressWarnings("deprecation")
@RequestMapping("/exports")
@ResponseBody
public Msg exportFile(HttpServletResponse response,String ids) throws Exception {
Assert.isTrue(StringUtils.isNotBlank(ids), "参数错误!");
// 1.文件下载响应头
response.setCharacterEncoding("UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + new String(("导出Excel名").getBytes("gb2312"), "ISO-8859-1") + ".xls");
//response.setHeader("Content-Disposition", "attachment;filename=" + new String((edTemplate.getTemplateName() + "导入模板").getBytes(), "ISO-8859-1") + ".xls");
// 2.响应到浏览器
WritableWorkbook workbook = Workbook.createWorkbook(response.getOutputStream());
// 创建工作簿sheet
WritableSheet sheet = workbook.createSheet("工作簿名", 0);
//设置字体;
WritableFont font2 = new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLUE);
WritableCellFormat cellFormat1 = new WritableCellFormat(font2);
//设置背景颜色;
cellFormat1.setBackground(Colour.PINK);
//设置边框;
cellFormat1.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
//设置自动换行;
cellFormat1.setWrap(true);
//设置文字居中对齐方式;
cellFormat1.setAlignment(Alignment.CENTRE);
//设置垂直居中;
cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
// 3.设置column名(标题目录,根据自己数据库表字段命名)
String[] ts ={"法规或文件名称","制定机关","文号","发布时间","法规分类","层级","最近修订时间","状态","法规或文件电子档"};
Label label=null;
for(int i=0;i<ts.length;i++){
//x,y,第一行的列名
label=new Label(i,0,ts[i],cellFormat1);
//7:添加单元格
sheet.addCell(label);
}
// 4.把数据填充到工作簿中 调用service调用查询数据库
List<ZZcfg> list = zzcfgService.findz(ids);
try{
for (int i = 0, j = 1; i < list.size(); i++, j++) {
ZZcfg z = list.get(i);
//System.out.println(z);
//设置列宽
sheet.setColumnView(i, 16);
//重新设置部分列宽
sheet.setColumnView(3, 14);
sheet.setColumnView(6, 14);
sheet.setColumnView(7, 14);
//设置行高
sheet.setRowView(i, 350);
//设置字体的attribute
WritableFont font1=new WritableFont(WritableFont.createFont("楷体 _GB2312"), 12, WritableFont.NO_BOLD);
WritableCellFormat format1=new WritableCellFormat(font1);
sheet.addCell(new Label(0, j, z.getzName(),format1));
sheet.addCell(new Label(1, j, z.getForOrg(),format1));
sheet.addCell(new Label(2, j, z.gettNumber(),format1));
String applydate=z.getReleTime().toLocaleString().substring(0,9);
sheet.addCell(new Label(3, j, applydate,format1));
sheet.addCell(new Label(4, j, z.getFgfl(),format1));
sheet.addCell(new Label(5, j, z.getOrderNo(),format1));
String applydateq=z.getLastTime().toLocaleString().substring(0,9);
sheet.addCell(new Label(6, j, applydateq,format1));
sheet.addCell(new Label(7, j, z.getzStatus().toString(),format1));
sheet.addCell(new Label(8, j, z.getTextField(),format1));
}
}catch (Exception e){
e.printStackTrace();
}
// 5.写入数据
workbook.write();
// 6.关闭资源
workbook.close();
return new Msg();
}
//导入Excel****************************************
@RequestMapping("upload/excel")
public void importExcelPapaers(HttpServletRequest request, HttpServletResponse response, @RequestParam MultipartFile file) throws IOException,
JSONException {
JSONObject jo = new JSONObject();
response.setContentType("text/html;charset=utf-8");
if (file.isEmpty()) {
jo.put("success", true);
jo.put("msg", "导入失败,文件不能为空!");
response.getWriter().print(jo.toString());
} else {
if ("application/vnd.ms-excel".equals(file.getContentType().toString())) {
//创建一个集合
List<ZZcfg> list = new ArrayList<ZZcfg>();
// 获取Excel对象
Workbook book;
try {
book = Workbook.getWorkbook(file.getInputStream());
// 获取Excel第一个选项卡对象
Sheet sheet = book.getSheet(0);
// 遍历选项卡,第一行是表头,所以索引数-1
for (int i = 0; i < sheet.getRows() - 1; i++) {
ZZcfg zz = new ZZcfg();
// 获取第i+1行单元格对象
zz.setzName(sheet.getCell(0,i+1).getContents());
zz.setForOrg(sheet.getCell(1,i+1).getContents());
zz.settNumber(sheet.getCell(2,i+1).getContents());
//时间格式的转换
try {
zz.setReleTime(new SimpleDateFormat("yyyy-MM-dd").parse(sheet.getCell(3,i+1).getContents()));
zz.setLastTime(new SimpleDateFormat("yyyy-MM-dd"). parse(sheet.getCell(6,i+1).getContents()));
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
zz.setFgfl(sheet.getCell(4,i+1).getContents());
zz.setOrderNo(sheet.getCell(5,i+1).getContents());
zz.setzStatus(sheet.getCell(7,i+1).getContents());
zz.setTextField(sheet.getCell(8,i+1).getContents());
list.add(zz);
}
for (int i = 0; i < list.size(); i++) {
zcfgService.save(list.get(i));
}
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
jo.put("success", true);
jo.put("msg", "导入成功!");
response.getWriter().print(jo.toString());
} else {
jo.put("success", true);
jo.put("msg", "导入失败!");
response.getWriter().print(jo.toString());
}
}
}
Service就不写了,直接写实现类
ServiceImpl:
//新增
@Override
public ZZcfg save(ZZcfg entity) {
this.merge(entity);
return entity;
}
@Override
public List<ZZcfg> findz(String ids) {
ArrayList<ZZcfg> l = new ArrayList<ZZcfg>();
if(ids.contains(",")){
String[] tIds = StringUtils.split(ids,",");
for (int i = 0; i < tIds.length; i++) {
String id = StringUtils.trim(tIds[i]);
ZZcfg pc = this.get(Long.valueOf(id));
l.add(pc);
}
}else{
ZZcfg ac = this.get(Long.parseLong(ids));
l.add(ac);
}
for (int i = 0; i < l.size(); i++) {
String a = l.get(i).getzName();
}
return l;
}
end