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;
    }