excel导出工具类

 /**
  *response 响应体
  *List<List<String>> excelData 表头和数据
  *String sheetName sheet名称
  *String fileName  文件名称
  *int columnWidth  表格宽度
  *String title     头部信息
  *int colNum       头部宽度
  */
 private void exportExcel(HttpServletResponse response, List<List<String>> excelData, String sheetName, String fileName, int columnWidth, String title, int colNum) throws Exception {

        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 10);//设置字体大小
        font.setBold(true);
        titleStyle.setFont(font);



        HSSFCellStyle contentStyle = workbook.createCellStyle();
        contentStyle.setBorderBottom(BorderStyle.THIN); //下边框
        contentStyle.setBorderLeft(BorderStyle.THIN);//左边框
        contentStyle.setBorderTop(BorderStyle.THIN);//上边框
        contentStyle.setBorderRight(BorderStyle.THIN);//右边框
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
//        contentStyle.setWrapText(true);
        HSSFFont contentFont = workbook.createFont();
        contentFont.setFontName("黑体");
        contentFont.setFontHeightInPoints((short) 10);//设置字体大小
        contentFont.setBold(false);
        contentStyle.setFont(contentFont);

        //通过的颜色
        HSSFCellStyle passStyle = workbook.createCellStyle();
        passStyle.setBorderBottom(BorderStyle.THIN); //下边框
        passStyle.setBorderLeft(BorderStyle.THIN);//左边框
        passStyle.setBorderTop(BorderStyle.THIN);//上边框
        passStyle.setBorderRight(BorderStyle.THIN);//右边框
        passStyle.setAlignment(HorizontalAlignment.CENTER);
        HSSFFont passFont = workbook.createFont();
        passFont.setFontName("黑体");
        passFont.setColor(HSSFColor.BRIGHT_GREEN.index);
        passFont.setFontHeightInPoints((short) 10);//设置字体大小
        passFont.setBold(false);
        passStyle.setFont(passFont);


        //未通过的颜色
        HSSFCellStyle unPassStyle = workbook.createCellStyle();
        unPassStyle.setBorderBottom(BorderStyle.THIN); //下边框
        unPassStyle.setBorderLeft(BorderStyle.THIN);//左边框
        unPassStyle.setBorderTop(BorderStyle.THIN);//上边框
        unPassStyle.setBorderRight(BorderStyle.THIN);//右边框
        unPassStyle.setAlignment(HorizontalAlignment.CENTER);
        unPassStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        unPassStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFFont unPassFont = workbook.createFont();
        unPassFont.setFontName("黑体");
        unPassFont.setColor(HSSFColor.RED.index);
        unPassFont.setFontHeightInPoints((short) 10);//设置字体大小
        unPassFont.setBold(false);
        unPassStyle.setFont(unPassFont);

        HSSFCellStyle singleStyle = workbook.createCellStyle();
        singleStyle.setAlignment(HorizontalAlignment.LEFT);
        singleStyle.setFont(contentFont);

        //生成一个表格，设置表格名称
        HSSFSheet sheet = workbook.createSheet(sheetName);

        CellRangeAddress cellRangeTitle = new CellRangeAddress(0,0,0,colNum-1);
        sheet.addMergedRegion(cellRangeTitle);
        if(title!=null){
            HSSFRow rowheader = sheet.createRow(0);
            HSSFCell cellheader = rowheader.createCell(0);
            cellheader.setCellStyle(titleStyle);
            cellheader.setCellValue(title);
            //合并单元格后的边框设置
            RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeTitle, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeTitle, sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeTitle, sheet);
            RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeTitle, sheet);
        }

        //设置表格列宽度
        sheet.setDefaultColumnWidth(columnWidth);

        //写入List<List<String>>中的数据
        int rowIndex = 1;
        for(List<String> data : excelData){

            if(data.size()==1){
                CellRangeAddress cellRangeData = new CellRangeAddress(rowIndex,rowIndex,0,colNum-1);
                sheet.addMergedRegion(cellRangeData);
                HSSFRow dataheader = sheet.createRow(rowIndex);
                HSSFCell cellDataheader = dataheader.createCell(0);
                cellDataheader.setCellStyle(singleStyle);
                cellDataheader.setCellValue(data.get(0));
                //合并单元格后的边框设置
                RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeData, sheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeData, sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeData, sheet);
                RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeData, sheet);
                rowIndex++;
            }else{
                //创建一个row行，然后自增1
                HSSFRow row = sheet.createRow(rowIndex++);

                //遍历添加本行数据
                for (int i = 0; i < data.size(); i++) {
                    //创建一个单元格
                    HSSFCell cell = row.createCell(i);
                    cell.setCellStyle(contentStyle);
                    //创建一个内容对象
                    HSSFRichTextString text = new HSSFRichTextString(data.get(i));
                    //将内容对象的文字内容写入到单元格中
                    if(text.toString().equals("已导入")){
                        text.applyFont(passFont);
                    }else if(text.toString().contains("此数据")){
                        cell.setCellStyle(unPassStyle);
                        text.applyFont(unPassFont);
                    }else{
                        cell.setCellStyle(contentStyle);
                    }
                    cell.setCellValue(text);
                }

            }
        }

        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream;charset=utf-8");

        //设置导出Excel的名称
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8"));
        response.setCharacterEncoding("UTF-8");
        //刷新缓冲
        response.flushBuffer();

        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        workbook.write(response.getOutputStream());

        //关闭workbook
        workbook.close();

    }