//点击导出按钮
function exportBtn(){
// 把表单中的查询条件转为json对象
var serachJson = $('#serach_from').serializeJson();
window.location.href = "/nursing/exportExcel?query="+serachJson;
}
// 工具方法,可以将指定的表单中的输入项目序列号为json数据
$.fn.serializeJson = function() {
var serializeObj = {};
var array = this.serializeArray();
$(array).each(
function() {
if (serializeObj[this.name]) {
if ($.isArray(serializeObj[this.name])) {
serializeObj[this.name].push(this.value);
} else {
serializeObj[this.name] = [
serializeObj[this.name], this.value ];
}
} else {
serializeObj[this.name] = this.value;
}
});
return serializeObj;
};
controller层调用实现导出的方法
/**
* 导出 按年份月份统计养老院使用设备报表
*/
@RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
public String exportExcel(NsExcelQuery query,HttpServletResponse response) {
String filename = "养老院使用设备.xls";
String title = "养老院使用设备";
List<NursingUseVo> vos = nursingService.queryExExcel(query);
try {
ExcelBase.exportExcel(title,filename,vos,response);
} catch (IOException e) {
e.printStackTrace();
logger.error("导出"+filename+"失败");
}
return "/admin/nsList";
}
@SuppressWarnings("resource")
public static void exportExcel(String title, String filename, List<NursingUseVo> data,
HttpServletResponse response) throws IOException {
OutputStream out = response.getOutputStream();// 取得输出流
BufferedOutputStream bos = new BufferedOutputStream(out);//缓冲流
response.reset();// 清空输出流
response.setContentType("application/ms-excel;charset=GB2312");
response.setHeader("Content-disposition",
"attachment; filename=" + new String(filename.getBytes("GB2312"), "ISO8859-1") + ".xls");
String[] str= {"","","1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月","合计"};
// 声明一个工作薄
try {
HSSFWorkbook workbook = new HSSFWorkbook();
// 首先检查数据看是否是正确的
if (data == null || data.size() == 0 || title == null || out == null) {
throw new Exception("传入的数据不对!");
}
// 创建一个sheet页
HSSFSheet sheet = workbook.createSheet("养老院安装设备表格");
// 设置表格默认列宽度为12个字节
sheet.setDefaultColumnWidth((short) 13);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, str.length - 1));
HSSFRow titleRow = sheet.createRow(0);
HSSFCell titleCell = titleRow.createCell(0);
titleCell.setCellValue(title);
titleCell.setCellStyle(setTitleStyle(workbook, workbook.createCellStyle()));
// 生成一个单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 创建标题行
HSSFRow headRow = sheet.createRow(1);
// 设置这些样式
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.WHITE.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
// 设置列头
HSSFCell cell0 = headRow.createCell(0);
cell0.setCellStyle(cellStyle);
cell0.setCellValue(new HSSFRichTextString("养老院名称"));
HSSFCell cell1 = headRow.createCell(1);
cell1.setCellStyle(cellStyle);
cell1.setCellValue(new HSSFRichTextString("设备名称"));
HSSFCell cell2 = headRow.createCell(2);
cell2.setCellStyle(cellStyle);
cell2.setCellValue(new HSSFRichTextString("使用次数统计"));
cell2.setCellStyle(cellStyle);
HSSFRow headRow2 = sheet.createRow(2);
//循环设置月份表头
for(int i=2;i<str.length;i++){
HSSFCell cell = headRow2.createCell(i);
cell.setCellValue(new HSSFRichTextString(str[i]));
// 把字体应用到当前的样式
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 14));
for (NursingUseVo vo : data) {
HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
dataRow.createCell(0).setCellValue(vo.getNs_name());
dataRow.createCell(1).setCellValue(vo.getDevices_name());
Map<Object, Integer> map = vo.getMap();
if(map.get(MonthVo.January)!=null){
dataRow.createCell(2).setCellValue(map.get(MonthVo.January));
}
if(map.get(MonthVo.February)!=null){
dataRow.createCell(3).setCellValue(map.get(MonthVo.February));
}
if(map.get(MonthVo.March)!=null){
dataRow.createCell(4).setCellValue(map.get(MonthVo.March));
}
if(map.get(MonthVo.April)!=null){
dataRow.createCell(5).setCellValue(map.get(MonthVo.April));
}
if(map.get(MonthVo.May)!=null){
dataRow.createCell(6).setCellValue(map.get(MonthVo.May));
}
if(map.get(MonthVo.June)!=null){
dataRow.createCell(7).setCellValue(map.get(MonthVo.June));
}
if(map.get(MonthVo.July)!=null){
dataRow.createCell(8).setCellValue(map.get(MonthVo.July));
}
if(map.get(MonthVo.August)!=null){
dataRow.createCell(9).setCellValue(map.get(MonthVo.August));
}
if(map.get(MonthVo.September)!=null){
dataRow.createCell(10).setCellValue(map.get(MonthVo.September));
}
if(map.get(MonthVo.October)!=null){
dataRow.createCell(11).setCellValue(map.get(MonthVo.October));
}
if(map.get(MonthVo.November)!=null){
dataRow.createCell(12).setCellValue(map.get(MonthVo.November));
}
if(map.get(MonthVo.December)!=null){
dataRow.createCell(13).setCellValue(map.get(MonthVo.December));
}
if(vo.getSum()!=null){
dataRow.createCell(14).setCellValue(vo.getSum());
}
}
workbook.write(bos);
//使用缓冲区的时候要注意刷新
bos.flush();
}catch(Exception e){
e.printStackTrace();
}finally{
if (out != null)
out.close();
if (bos != null)
//关闭缓冲区的对象,实际上是关闭与它关联的流对象最好放在finally执行
bos.close();
}
}