easyUI+HSSFWorkbook导出Excel表格
1、自定义分页工具条,在分页条上新增导出按钮
var pager = $('#tableID').datagrid('getPager');
pager.pagination({
buttons:[{
text:'导出本页',
iconCls:'icon-save',
handler:function(){
}
},{
text:'导出详单',
iconCls:'icon-print',
handler:function(){
}
}]
});
页面效果如下图:
2、easyUI自带的导出Excel,能导出table当前页面的数据
1)导入datagrid-export.js
2)调用方法
buttons:[{
text:'导出本页',
iconCls:'icon-save',
handler:function(){
var rows = $("#tableID").datagrid("getRows");
$('#querySheet').datagrid('toExcel', {
filename:'表格名称.xls',
rows:rows,
worksheet:'工单清单'
});
}
}]
3、导出工单所有清单,datagrid-export.js,只能导出本页数据,要导出所有清单,需把请求参数传到后台通过Java代码生成并导出,具体实现分两种:
1)请求参数较少时,可通过get方式获取,window.location.href="/exportRiskSheetToExcel.do?params=xxx"
2)请求参数过多时,需要通过表单提交的方式导出Excel
前端js代码如下:
buttons:[{
text:'导出本页',
iconCls:'icon-save',
handler:function(){
var params = $("#form").serializeObject();
var form = document.createElement("form");
form.style.display = 'none';
form.action = '/exportRiskSheetToExcel.do';
form.method = "post";
document.body.appendChild(form);
for(var key in params){
var input = document.createElement("input");
input.type = "hidden";
input.name = key;
input.value = params[key];
form.appendChild(input);
}
form.method = "post";
form.submit();
form.remove();
}
}]
后端代码如下:
一、controller层获取到请求参数,前端传的值,封装到一个实体bean里面,调用数据接口获取到数据详单,再传到工单类生成HSSFWorkbook并返回,然后在通过浏览器输出Excel
@RequestMapping(value="/exportRiskSheetToExcel.do",method=RequestMethod.POST)
public void exportRiskSheetToExcel(HttpServletRequest request,HttpServletResponse response, SheetEntity entity) {
List<Map<String, Object>> list = new ArrayList<>();
String curTime = new SimpleDateFormat("HHmmss").format(new Date());
ServletOutputStream out=null;
//表格标题
String[] head = new String[]{"工单号","标题","申请时间","结束时间"};
//表格列columns
String[] columns = new String[]{"SHEET_NO","TITLE","CREATE_DATE","END_TIME"};
try {
list = dubboService.invoke("SheetService", "queryAllSheetList", new Object[] {entity});
String filename="工单清单"+curTime+".xls";
response.reset();// 清空输出流
response.setCharacterEncoding("utf-8");
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
// 设置下载的文件名
response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
response.flushBuffer();
out = response.getOutputStream();
//调用工具类生成HSSFWorkbook 并返回
HSSFWorkbook excelExt = ExportToExcelUtil.exportExcelExt(list,columns, head);
excelExt.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
二,HSSFWorkbook工具类
public class ExportToExcelUtil {
public static HSSFWorkbook exportExcelExt(List<Map<String, Object>> dataList, String[] columns,String[] head) {
// 第一步,创建一个workbook,对应一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet hssfSheet = workbook.createSheet("sheet1");
hssfSheet.setDefaultColumnWidth((short) 20);//设置默认宽度
// 第三步,在sheet中添加表头第0行
HSSFRow row = hssfSheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle hssfCellStyleContent = workbook.createCellStyle();
hssfCellStyleContent.setAlignment(HorizontalAlignment.LEFT); // 水平靠左
hssfCellStyleContent.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
hssfCellStyleContent.setWrapText(true);//自动换行
hssfCellStyleContent.setBorderLeft(BorderStyle.MEDIUM);// 左边框
hssfCellStyleContent.setBorderTop(BorderStyle.MEDIUM);// 上边框
hssfCellStyleContent.setBorderRight(BorderStyle.MEDIUM);// 右边框
hssfCellStyleContent.setBorderBottom(BorderStyle.MEDIUM); // 下边框
HSSFCell hssfCell = null;
// 设置表头
for (int i = 0; i < head.length; i++) {
hssfCell = row.createCell(i);// 列索引从0开始
String name = head[i];
hssfCell.setCellValue(name);// 列名
hssfCell.setCellStyle(hssfCellStyleContent);
// hssfSheet.autoSizeColumn(i);
}
// 填充数据
for (int i = 0; i < dataList.size(); i++) {
row = hssfSheet.createRow(i + 1);
Map<String, Object> map = dataList.get(i);
for (int y = 0; y < columns.length; y++) {// 记录对应字段
String key = columns[y];
String value = map.get(key) + "";
HSSFCell cell = row.createCell(y);
cell.setCellStyle(hssfCellStyleContent);
cell.setCellValue(value);
}
}
return workbook;
}
}