Java 实现导出excel表

import java.util.List;import javax.annotation.Resource;import javax.servlet.http.HttpServletRequest;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional;/**     * 导出     * 使用方法生成excle模板样式       */    @RequestMapping(value = "/export", method = RequestMethod.GET, produces = "application/json")    public void aud001Export(HttpServletRequest request,HttpServletResponse response,Aud001 audItem){        try {            //请求参数            String queryString = request.getQueryString();            if (StringUtils.isNotBlank(queryString)) {                String[] stringArray = queryString.split("&");                for (String string : stringArray) {                    String[] aa = string.split("=");                    if(aa.length>1){//                      String  last = aa[1];//                      String a = last.replaceAll("%", "\\\\%");                        if(aa[0].equals("aitemkbn1")){                            audItem.setAitemkbn1(audItem.getAitemkbn1().replaceAll("%", "\\\\%"));                        }else if(aa[0].equals("policyInformationCode")){                            audItem.setPolicyInformationCode(audItem.getPolicyInformationCode().replaceAll("%", "\\\\%"));                        }else if(aa[0].equals("auditItems")){                            audItem.setAuditItems(audItem.getAuditItems().replaceAll("%", "\\\\%"));                        }                    }                }            }            HSSFWorkbook workbook = aud001Excel.createExcel(request,audItem);              SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); // 定义文件名格式              //定义excle名称 ISO-8859-1防止名称乱码              String name = "导出_审计项"+format.format(new Date()) + ".xls";            String codedFilename = "";               String agent = request.getHeader("USER-AGENT");               if (null != agent && -1 != agent.indexOf("MSIE") || null != agent                         && -1 != agent.indexOf("Trident") || null != agent && -1 != agent.indexOf("Edge")) {// ie浏览器及Edge浏览器                     String namea = java.net.URLEncoder.encode(name, "UTF-8");                     codedFilename = namea;                 } else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,Chrome等浏览器                     codedFilename = new String(name.getBytes("UTF-8"), "iso-8859-1");                 }            response.setContentType("application/vnd.ms-excel");              response.addHeader("Content-Disposition", "attachment;filename=" + codedFilename);              workbook.write(response.getOutputStream());        } catch (Exception e) {            e.printStackTrace();        }    }/**      *   Excel 导出信息      * @param cusList      * @param request      * @return      */    public HSSFWorkbook createExcel( HttpServletRequest request,Aud001 auditItems) {        List<Aud001> aud001s = aud001Mapper.getAll(auditItems);        // 创建一个webbook，对应一个excel文件          HSSFWorkbook workbook = new HSSFWorkbook();          // 在webbook中添加一个sheet,对应excel文件中的sheet          HSSFSheet sheet = workbook.createSheet("审计项");          // 设置列宽          sheet.setColumnWidth(0, 35 * 100);          sheet.setColumnWidth(1, 35 * 100);          sheet.setColumnWidth(2, 35 * 100);          sheet.setColumnWidth(3, 35 * 100);          sheet.setColumnWidth(4, 35 * 100);          sheet.setColumnWidth(5, 35 * 100);  //      sheet.setColumnWidth(6, 35 * 100);  //      sheet.setColumnWidth(7, 35 * 100);  //      sheet.setColumnWidth(8, 35 * 100);          // 在sheet中添加表头第0行          HSSFRow row = sheet.createRow(0);          // 创建单元格，并设置表头，设置表头居中          HSSFCellStyle style = workbook.createCellStyle();          // 创建一个居中格式          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);         // 带边框          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);          // 生成一个字体          HSSFFont font = workbook.createFont();          // 字体增粗          font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);          // 字体大小          font.setFontHeightInPoints((short) 12);          // 把字体应用到当前的样式          style.setFont(font);          // 单独设置整列居中或居左          HSSFCellStyle style1 = workbook.createCellStyle();          style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);          HSSFCellStyle style2 = workbook.createCellStyle();          style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);          HSSFCellStyle style3 = workbook.createCellStyle();          style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);          HSSFFont hssfFont = workbook.createFont();          hssfFont.setColor(HSSFFont.COLOR_RED);          hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);          style3.setFont(hssfFont);          HSSFCellStyle style4 = workbook.createCellStyle();          style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);          HSSFFont hssfFont1 = workbook.createFont();          hssfFont1.setColor(HSSFFont.COLOR_NORMAL);          hssfFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        style4.setFont(hssfFont1);          HSSFCell cell;        cell = row.createCell(0);          cell.setCellValue("审计类别");          cell.setCellStyle(style);          cell = row.createCell(1);          cell.setCellValue("审计编号");          cell.setCellStyle(style);          cell = row.createCell(2);          cell.setCellValue("审计项");          cell.setCellStyle(style);           cell = row.createCell(3);          cell.setCellValue("审计程序");          cell.setCellStyle(style);         cell = row.createCell(4);          cell.setCellValue("审计依据");          cell.setCellStyle(style);          cell = row.createCell(5);          cell.setCellValue("审计项状态");          cell.setCellStyle(style);          for (int i = 0; i < aud001s.size(); i++) {            row = sheet.createRow(i + 1);              Aud001 aud001=aud001s.get(i);            // 创建单元格，并设置值              // 编号列居左              HSSFCell c1 = row.createCell(0);              c1.setCellStyle(style1);              c1.setCellValue(aud001.getAitemkbn1());  //审计类别            HSSFCell c2 = row.createCell(1);              c2.setCellStyle(style1);              c2.setCellValue(aud001.getPolicyInformationCode());  //审计编号            HSSFCell c3 = row.createCell(2);              c3.setCellStyle(style1);              c3.setCellValue(aud001.getAuditItems());  //审计项            HSSFCell c4 = row.createCell(3);              c4.setCellStyle(style1);              c4.setCellValue(aud001.getAuditProcedure());  //审计程序            HSSFCell c5 = row.createCell(4);              c5.setCellStyle(style1);              c5.setCellValue(aud001.getAuditPolicy());  //审计依据            HSSFCell c6 = row.createCell(5);              c6.setCellStyle(style1);              if("0".equals(aud001.getAitemstatus())){                c6.setCellValue("起草");  //审计项状态            }else if("1".equals(aud001.getAitemstatus())){                c6.setCellValue("启用");  //审计项状态            }else if("2".equals(aud001.getAitemstatus())){                c6.setCellValue("停用");  //审计项状态            }        }          return workbook;      }/**      *       * @param cell      *            一个单元格的对象      * @return 返回该单元格相应的类型的值      */    public static Object getRightTypeCell(Cell cell) {        Object object = null;        // 把数字当成String来读，避免出现1读成1.0的情况        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {            cell.setCellType(Cell.CELL_TYPE_STRING);        }        switch (cell.getCellType()) {        case Cell.CELL_TYPE_STRING: {            object = cell.getStringCellValue();            break;        }        case Cell.CELL_TYPE_NUMERIC: {            cell.setCellType(Cell.CELL_TYPE_NUMERIC);            object = cell.getNumericCellValue();            break;        }        case Cell.CELL_TYPE_FORMULA: {            cell.setCellType(Cell.CELL_TYPE_NUMERIC);            object = cell.getNumericCellValue();            break;        }        case Cell.CELL_TYPE_BLANK: {            cell.setCellType(Cell.CELL_TYPE_BLANK);            object = cell.getStringCellValue();            break;        }        }        return object;    }