自定义EXCEL导出功能

 /** * @Description 导出凭证分录的excel文件 * @param glDetail * @param request * @param response * @param redirectAttributes * @return  * @Date2017年5月4日 上午10:33:22 * @AuthorFANFYK */@SuppressWarnings("unused")@RequestMapping(value = "detailEx", method=RequestMethod.POST)    public String detailExport(GlDetail glDetail, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes){        List<Map<String,Object>> dataList = new  ArrayList<Map<String,Object>>();        Page<GlDetail> page = new Page<GlDetail>(request, response,"ajaxPage");        List<GlDetail> searchList = new  ArrayList<GlDetail>();        try {searchList = glDetailService.findAjaxList(page, glDetail);} catch (IllegalAccessException e1) {e1.printStackTrace();} catch (InvocationTargetException e1) {e1.printStackTrace();}GlVoucher glVoucher = glVoucherService.get(glDetail.getPkVoucher());// 设置单元格样式设置统一的单元格大小HSSFWorkbook wb = new HSSFWorkbook(); // --->创建了一个excel文件Sheet sheet = wb.createSheet("新的工作表"); // --->创建了一个工作簿sheet.setColumnWidth((short) 3, 30 * 250); // --->设置单元格宽度，因为一个单元格宽度定了那么下面多有的单元格高度都确定sheet.setColumnWidth((short) 4, 40 * 400); // --->第一个参数是指哪个单元格，第二个参数是单元格的宽度sheet.setColumnWidth((short) 5, 30 * 250);sheet.setColumnWidth((short) 6, 30 * 250);sheet.setColumnWidth((short) 7, 30 * 250);sheet.setColumnWidth((short) 8, 30 * 250);sheet.setDefaultRowHeight((short) 600); // ---->有得时候你想设置统一单元格的高度，就用这个方法// 样式1，设置内容对齐方式以及边框线的粗细CellStyle style1 = wb.createCellStyle(); // 样式对象style1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直style1.setAlignment(CellStyle.ALIGN_CENTER);// 水平style1.setBorderTop(CellStyle.BORDER_THIN);// 上边线style1.setBorderLeft(CellStyle.BORDER_THIN);// 左边线style1.setBorderBottom(CellStyle.BORDER_THIN);// 底边线style1.setBorderRight(CellStyle.BORDER_THIN);// 右边线// 样式2CellStyle style2 = wb.createCellStyle(); // 样式对象style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直style2.setAlignment(CellStyle.ALIGN_CENTER);// 水平// 设置标题字体格式Font font = wb.createFont();// 设置字体样式font.setFontHeightInPoints((short) 20); // --->设置字体大小font.setFontName("Courier New"); // --->设置字体，是什么类型例如：宋体// 设置单个表格内容的行数（5行）int table = (int) Math.ceil(searchList.size() / 5)+1;// 循环绘制表格for (int j = 0; j < table; j++) {// 表格第一行(记账凭证行)Row row1 = sheet.createRow(0+12*j); // --->创建一行// CellRangeAddress有4个参数：起始行号，终止行号， 起始列号，终止列号sheet.addMergedRegion(new CellRangeAddress(0+12*j,  0+12*j, (short) 3,(short) 7));row1.setHeightInPoints(25);Cell cell1 = row1.createCell((short) 3); // --->创建一个单元格cell1.setCellStyle(style2); // 设置样式(对齐方式无边框)cell1.setCellValue(glVoucher.getPkVouchertype().getVouchtypename());// 表格第二行Row row2 = sheet.createRow(1+12*j);sheet.addMergedRegion(new CellRangeAddress(1+12*j, 1+12*j, (short) 3,(short) 7));Cell cell2_1 = row2.createCell((short) 3);cell2_1.setCellValue(glVoucher.getPrepareddate());cell2_1.setCellStyle(style2);Cell cell2_2 = row2.createCell((short) 8);cell2_2.setCellValue("来源系统：" + glVoucher.getPkSystemName());cell2_2.setCellStyle(style2);// 表格第三行Row row3 = sheet.createRow(2+12*j);sheet.addMergedRegion(new CellRangeAddress(2+12*j,  2+12*j, (short) 3,(short) 4));Cell cell3_1 = row3.createCell((short) 3);cell3_1.setCellValue(glVoucher.getPkGlorgbook().getGlorgbookname());cell3_1.setCellStyle(style2);DecimalFormat df=new DecimalFormat("0000");Cell cell3_2 = row3.createCell((short) 8);cell3_2.setCellValue("第" + df.format(glVoucher.getNo()) + "号凭证 -"+df.format(j+1)+"/"+df.format(table));cell3_2.setCellStyle(style2);// 表格第四行sheet.addMergedRegion(new CellRangeAddress(3+12*j,  3+12*j, (short) 0,(short) 2));Row row4 = sheet.createRow(3+12*j);row4.setHeightInPoints((short) 30);Cell cell4_1 = row4.createCell((short) 3);cell4_1.setCellStyle(style1);cell4_1.setCellValue("摘要");Cell cell4_2 = row4.createCell((short) 4);cell4_2.setCellStyle(style1);cell4_2.setCellValue("会计科目");Cell cell4_3 = row4.createCell((short) 5);cell4_3.setCellStyle(style1);cell4_3.setCellValue("单价");Cell cell4_4 = row4.createCell((short) 6);cell4_4.setCellStyle(style1);cell4_4.setCellValue("数量");Cell cell4_5 = row4.createCell((short) 7);cell4_5.setCellStyle(style1);cell4_5.setCellValue("借方本币");Cell cell4_6 = row4.createCell((short) 8);cell4_6.setCellStyle(style1);cell4_6.setCellValue("贷方本币");int endrow = searchList.size();if(searchList.size()>(5*j+5)){endrow = 5*j+5;}// 钱币金额格式化NumberFormat numberFormat = new DecimalFormat("#,###.00");for (int i = 0+5*j; i < endrow; i++) {// 表格第n行sheet.addMergedRegion(new CellRangeAddress(4 + i+7*j,  4 + i+7*j,(short) 0, (short) 2));Row row = sheet.createRow(4 + i+7*j);row.setHeightInPoints((short) 30);GlDetail detail = searchList.get(i);Cell row_1 = row.createCell((short) 3);row_1.setCellStyle(style1);row_1.setCellValue(StringUtils.isNotBlank(detail.getExplanation()) ? detail.getExplanation() : "");Cell row_2 = row.createCell((short) 4);row_2.setCellStyle(style1);row_2.setCellValue(!StringUtils.isNull(detail.getPkAccsubj()) && StringUtils.isNotBlank(detail.getPkAccsubj().getDispname()) ? detail.getPkAccsubj().getDispname() : "" + " " + (StringUtils.isNotBlank(detail.getValueName()) ? detail.getValueName() : ""));Cell row_3 = row.createCell((short) 5);row_3.setCellStyle(style1);if (!StringUtils.isNull(detail.getPrice()) && detail.getPrice() != 0) {row_3.setCellValue(detail.getPrice());}Cell row_4 = row.createCell((short) 6);row_4.setCellStyle(style1);if (detail.getDirection().equals("D")) {if (!StringUtils.isNull(detail.getDebitquantity()) && detail.getDebitquantity() != 0) {row_4.setCellValue(detail.getDebitquantity());}} else {if (!StringUtils.isNull(detail.getCreditquantity()) && detail.getCreditquantity() != 0) {row_4.setCellValue(detail.getCreditquantity());}}Cell row_5 = row.createCell((short) 7);row_5.setCellStyle(style1);if(!StringUtils.isNull(detail.getLocaldebitamount()) && detail.getLocaldebitamount()!=0){row_5.setCellValue(numberFormat.format(detail.getLocaldebitamount()));}Cell row_6 = row.createCell((short) 8);row_6.setCellStyle(style1);if(!StringUtils.isNull(detail.getLocalcreditamount()) && detail.getLocalcreditamount()!=0){row_6.setCellValue(numberFormat.format(detail.getLocalcreditamount()));}}if(searchList.size()<(5*j+5)){for (int k = searchList.size(); k < 5*j+5; k++) {// 表格第n行sheet.addMergedRegion(new CellRangeAddress(4 + 7*j + k,  4 + 7*j + k,(short) 0, (short) 2));Row row = sheet.createRow(4 + 7*j + k);row.setHeightInPoints((short) 30);Cell row_1 = row.createCell((short) 3);row_1.setCellStyle(style1);Cell row_2 = row.createCell((short) 4);row_2.setCellStyle(style1);Cell row_3 = row.createCell((short) 5);row_3.setCellStyle(style1);Cell row_4 = row.createCell((short) 6);row_4.setCellStyle(style1);Cell row_5 = row.createCell((short) 7);row_5.setCellStyle(style1);Cell row_6 = row.createCell((short) 8);row_6.setCellStyle(style1);}}Row row10 = sheet.createRow(9+12*j);row10.setHeightInPoints((short) 30);sheet.addMergedRegion(new CellRangeAddress(9+12*j,  9+12*j,(short) 4, (short) 6));Cell cell10_1 = row10.createCell((short) 3);cell10_1.setCellStyle(style1);cell10_1.setCellValue("附单据：0                       张");Cell cell10_3 = row10.createCell((short) 4);cell10_3.setCellStyle(style1);Cell cell10_4 = row10.createCell((short) 5);cell10_4.setCellStyle(style1);Cell cell10_5 = row10.createCell((short) 6);cell10_5.setCellStyle(style1);Cell cell10_6 = row10.createCell((short) 7);cell10_6.setCellStyle(style1);Cell cell10_7 = row10.createCell((short) 8);cell10_7.setCellStyle(style1);if(j == table-1){cell10_3.setCellValue("合计："+MoneyUtils.change(glVoucher.getTotaldebit()));cell10_6.setCellValue(numberFormat.format(glVoucher.getTotaldebit()));cell10_7.setCellValue(numberFormat.format(glVoucher.getTotalcredit()));}else{cell10_3.setCellValue("合计：");}Row row11 = sheet.createRow(10+12*j);row4.setHeightInPoints((short) 25);Cell cell11_1 = row11.createCell((short) 3);cell11_1.setCellStyle(style2);if (glVoucher.getPkManager() != null) {cell11_1.setCellValue("记账：" + glVoucher.getPkManager().getName());} else {cell11_1.setCellValue("记账：");}Cell cell11_2 = row11.createCell((short) 4);cell11_2.setCellStyle(style2);if (glVoucher.getPkChecked() != null) {cell11_2.setCellValue("审核：" + glVoucher.getPkChecked().getName());} else {cell11_2.setCellValue("审核：");}Cell cell11_3 = row11.createCell((short) 5);cell11_3.setCellStyle(style2);if (glVoucher.getPkCasher() != null) {cell11_3.setCellValue("出纳：" + glVoucher.getPkCasher().getName());} else {cell11_3.setCellValue("出纳：");}Cell cell11_4 = row11.createCell((short) 6);cell11_4.setCellStyle(style2);if (glVoucher.getPkPrepared() != null) {cell11_4.setCellValue("制单：" + glVoucher.getPkPrepared().getName());} else {cell11_4.setCellValue("制单：");}}String fileName = "凭证"+DateUtils.getDate("yyyyMMddHHmmss")+".xls";FileOutputStream fileOut = null;try {        response.setContentType("application/octet-stream; charset=utf-8");        response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));        wb.write(response.getOutputStream());        response.getOutputStream().flush();        response.getOutputStream().close();        return null;} catch (Exception e) {e.printStackTrace();} finally {if (fileOut != null) {try {fileOut.close();} catch (IOException e) {e.printStackTrace();}}}return null;}