jxl实现excel导入导出
一、导出
(1)首先先加入jxl.jar,maven 项目先导入依赖
(2)添加导出按钮或者链接,
(3)js中添加按钮事件
// 导出账单
$("#btn-bill-export").on('click', function () {
var ids = getSeclectIds();
if (ids.length === 0) {
xmtc.failMsg("请先选择一条记录");
return;
} else if (ids.length > 1) {
xmtc.failMsg("请只选择一条记录");
return;
} else {
var flag = 0;
var selectRows = xmtc.getRows(table, $('#dateTable'));
var bill_no = selectRows[0].bill_no;
var bill_name = selectRows[0].bill_name;
//后台导出事件
var url = base + "/fee/csBills/exportHandler?bill_no=" + bill_no + "&type=02" + "&bill_name=" + bill_name;
//ajax进行导出前校验 (xmtc为系统自定义对象,ajaxPost为系统自定义方法,对ajax访问进行封装重写)
xmtc.ajaxPost(base + "/fee/csBills/checkBillsFee", {
bill_no: bill_no,
bill_name: bill_name
}, function (data) {
if (!data.success) {
xmtc.failMsg(data.msg);
} else {
//成功后导出excel
openurl(url)
}
});
return false; //阻止表单跳转。如果需要表单跳转,去掉这段即可。
} });
其中:openurl事件如下:
openurl(url)-------->window.open(encodeURI(encodeURI(url)));
window.open()方法可以查找一个已存在的或者新建的浏览器窗口,这边是访问excel的导出下载记录:http://localhost:8081/huate-tms/fee/csBills/exportHandler?bill_no=BN2019102400002&type=02&bill_name=2019年9月厦门福金物流有限公司(江西省区出库)—厦门华特集团有限公司(龙岗)"
注: window.open([URL], [窗口名称], [参数字符串])
例如:打开http://www.imooc.com网站,大小为300px * 200px,无菜单,无工具栏,无状态栏,有滚动条窗口:
<script type="text/javascript"> window.open(' https://www.imooc.com/','_blank','width=300,height=200,menubar=no,toolbar=no, status=no,scrollbars=yes')</script>
注:encodeURI编码 如果通过form提交的,那就不需要用这个了。但是如果是使用url的方式 ajax提交到后台的,就需要对url进行encodeURI编码,
否则,会导致后台出现各种乱码,不加encodeURI的话,默认浏览器编码格式提交, 这样的话,浏览器不同,传到后台的值也就不同了,)
(4)exportHandler导出函数
public void exportHandler(){
//获取应收账单的所有信息
Map params = this.buildParams();
params.put("bill_no",getPara("bill_no"));
String bill_no = getPara("bill_no");
String type = getPara("type");
String bill_name = getPara("bill_name");
List<Record> records = null;
if("01".equals(type)){
try{
records = new SqlExecutor().find("bills_receive_query",params);
}catch (Exception e){
Result result = new Result();
result.setSuccess(false);
result.fail("应收账单信息转为Excel文档出现异常,导出失败");
renderJson(result);
return;
}
}else if("02".equals(type)){
try{
records = new SqlExecutor().find("bills_pay_query",params);
}catch (Exception e){
Result result = new Result();
result.setSuccess(false);
result.fail("应付账单信息转为Excel文档出现异常,导出失败");
renderJson(result);
return;
}
}
//账单信息转换为excel文档
File tempFile = null;
String fileName = null;
if("01".equals(type)){
fileName = "应收账单.xls";
}else if("02".equals(type)){
fileName = bill_name + ".xls";
}
try {
//生成缓存文件
tempFile = new File("./"+System.currentTimeMillis()+".temp");
//台账信息转为Excel文档
exportExcel(records, tempFile, type, bill_no);
//返回前端下载
renderFile(tempFile,fileName);
} catch (Exception e) {
e.printStackTrace();
//异常信息结果返回
Result result = new Result();
result.setSuccess(false);
result.fail("账单信息转为Excel文档出现异常,导出失败");
renderJson(result);
}
}
(5)exportExcel函数如下,前端调用此函数即可导出excel并下载
/**
* 配件信息转为Excel文档
* @throws IOException
* @throws WriteException
* @throws RowsExceededException
*/
public String exportExcel(List<Record> records, File tempFile, String type, String bill_no)
throws IOException, RowsExceededException, WriteException {
String errorMsg = "";
WritableWorkbook wwb = Workbook.createWorkbook(new FileOutputStream(tempFile));
//创建页签
WritableSheet sheet = null;
if("01".equals(type)){
sheet = wwb.createSheet("应收账单清单", 0);
}else if("02".equals(type)){
sheet = wwb.createSheet("应付账单清单", 0);
}
//-------------打印设置start---------------
//设置打印时页眉页脚、边距
sheet.getSettings().setBottomMargin(0.196d);
sheet.getSettings().setTopMargin(0.196d);
sheet.getSettings().setLeftMargin(0.196d);
sheet.getSettings().setRightMargin(0.196d);
//横向打印
sheet.getSettings().setOrientation(PageOrientation.LANDSCAPE);
//-------------打印设置end---------------
//------------表头start--------------
//设置字体样式
WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 13, WritableFont.BOLD,
false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
// 设置居中
titleFormat.setAlignment(Alignment.CENTRE);
//设置边框
titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
String title = "运输对账单 " + records.get(0).getStr("bill_name");
sheet.addCell(new Label(0, 0, title, titleFormat));//表头账单名称
//合并单元格0到16列
sheet.mergeCells(0, 0, 16, 0);
//设置0行高度
sheet.setRowView(0, 800, false);
//------------表头end--------------
//设置标题行样式
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD,
false, UnderlineStyle.NO_UNDERLINE, Colour.WHITE);
WritableCellFormat wc = new WritableCellFormat(wfont);
// 设置居中
wc.setAlignment(Alignment.CENTRE);
// 设置边框线
wc.setBorder(Border.ALL, BorderLineStyle.THIN);
// 设置单元格的背景颜色
wc.setBackground(jxl.format.Colour.GREEN);
//设置根据内容自动设置列宽
CellView cellView = new CellView();
cellView.setAutosize(true); //设置自动大小
sheet.setColumnView(0, 5);
sheet.setColumnView(1, 10);
sheet.setColumnView(2, 10);
sheet.setColumnView(3, 10);
sheet.setColumnView(4, 30);
sheet.setColumnView(5, 20);
sheet.setColumnView(6, 20);
sheet.setColumnView(7, 20);
//构建页签第一行标题行
sheet.addCell(new Label(0, 1, "序号", wc));
sheet.addCell(new Label(1, 1, "日期", wc));
sheet.addCell(new Label(2, 1, "运输单号", wc));
sheet.addCell(new Label(3, 1, "承运车号", wc));
sheet.addCell(new Label(4, 1, "收货单位", wc));
sheet.addCell(new Label(5, 1, "产品", wc));
sheet.addCell(new Label(6, 1, "发货地点", wc));
sheet.addCell(new Label(7, 1, "收货地点", wc));
//设置行数,从第3行开始,即行下标从2开始
int rowIndex = 2;
//设置sheet字体样式
//设置标题行样式
WritableFont wbodyFont = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD,
false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat bodyFormat = new WritableCellFormat(wbodyFont);
bodyFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
//设置每行的数据值
for (Record record : records) {
sheet.addCell(new Number(0, rowIndex, rowIndex - 1, bodyFormat));//序号
sheet.addCell(new Label(1, rowIndex, getString(record.getStr("departure_time")), bodyFormat));//日期
sheet.addCell(new Label(2, rowIndex, getString(record.getStr("hand_no")), bodyFormat));//手工号
sheet.addCell(new Label(3, rowIndex, getString(record.getStr("vehicle_no")), bodyFormat));//承运车号
sheet.addCell(new Label(4, rowIndex, getString(record.getStr("consignee_name")), bodyFormat));//收货单位
sheet.addCell(new Label(5, rowIndex, getString(record.getStr("cargo_name")), bodyFormat));//产品
sheet.addCell(new Label(6, rowIndex, getString(record.getStr("shipper_address_name")), bodyFormat));//发货地点
sheet.addCell(new Label(7, rowIndex, getString(record.getStr("consignee_address_name")), bodyFormat));//收货地点
//设置行数
rowIndex++;
}
// 把创建的内容写入到输出流中,并关闭输出流
wwb.write();
wwb.close();
return errorMsg;
}
总结:
(1)在相应界面先创建导出按钮或连接,对导出事件进行处理,进行导出前校验
(2)使用Workbook.createWorkbook(new FileOutputStream(tempfile))创建工作簿[ tempfile缓存路径]得到WritableWorkbook
(3)然后createSheet创建sheet页, 对表头,body进行字体样式的设定,然后使用sheet.addCell创建每列
(4) 循环将每行数据进行遍历加入sheet中 最后把创建的内容写入到输出流中,并关闭输出流
wwb.write();
wwb.close();