poi导出Excel（分行单元格颜色设置，字体设置，合并单元格，插入图片）

这是一个日报导出功能的代码;图片是用JfreeChars生成好的，话不多少 看代码

public String excelExport(HttpServletRequest request,HttpServletResponse response,CarSaledailyDTO carSaledailyDTO){
        //获取需要导出的报表数据
 　　　　List<List<List>> list = carSaledailyFacade.excelData(carSaledailyDTO,request);
        
        HSSFWorkbook workbook = new HSSFWorkbook();
        
        HSSFSheet sheet = workbook.createSheet("销售日报");
        CellRangeAddress region=new CellRangeAddress(0, 1, 0, 30);
        sheet.addMergedRegion(region);
        HSSFRow row=sheet.createRow(0);
        //合并列
        HSSFCell cell=row.createCell(0);
        
        HSSFFont f  = workbook.createFont();
        f.setFontName("黑体");
        f.setFontHeightInPoints((short) 22);//字号      
        f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗      

        HSSFCellStyle style = workbook.createCellStyle();     
        style.setFont(f);      
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
        cell.setCellValue("日报");
        cell.setCellStyle(style);
        
        int r =2;
        for(int i = 0;i<list.size();i++){
        r=createRow(list.get(i), sheet,r,workbook);
        }    

        
        //cell.setCellStyle(style);
        //HSSFSheet sheet1 = workbook.createSheet("全国每日提报量和成交合同量");
        FileInputStream stream;
        String path = request.getSession().getServletContext().getRealPath("/");
        System.out.println(path);
        try {
            stream = new FileInputStream(path+"images\\pic\\line.png");
            byte[] bytes=new byte[(int)stream.getChannel().size()];
            stream.read(bytes);//读取图片到二进制数组
            int pictureIdx = workbook.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)0, 33, (short)20, 60);
            HSSFPicture pict = patriarch.createPicture(anchor, pictureIdx);
            //pict.resize();//自动调节图片大小,图片位置信息可能丢失
        } catch (Exception e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        
         ByteArrayOutputStream os = new ByteArrayOutputStream();

            try {
                workbook.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }

            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            try {
                // 设置response参数，可以打开下载页面
                response.reset();
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                //response.setHeader("Content-Disposition", "attachment;filename=" + new String(".xls").getBytes(), "iso-8859-1"));
                String filename = "SaleDailyReport"+carSaledailyDTO.getSearchDate()+".xls";
                response.setHeader("Content-Disposition", "attachment;filename= "+ URLEncoder.encode(filename, "UTF-8"));
                ServletOutputStream out = response.getOutputStream();

                BufferedInputStream bis = null;
                BufferedOutputStream bos = null;

                bis = new BufferedInputStream(is);
                bos = new BufferedOutputStream(out);

                byte[] buff = new byte[2048];
                int bytesRead;

                // Simple read/write loop.
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
                }
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
                
            } catch (final IOException e) {
                e.printStackTrace();
            } 
            
        return "导出成功！";
    }

上面这个方法是koala框架里的基于spring mvc的一个controller，完全可以忽略参数*DTO;

    public Integer createRow(List<List> list,HSSFSheet sheet,int r,HSSFWorkbook workbook){
        
        for(int k = 0;k<list.size();k++){
             HSSFCellStyle style = workbook.createCellStyle();
             //设置边框样式
             style.setBorderTop(HSSFCellStyle.BORDER_THIN);
             style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
             style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
             style.setBorderRight(HSSFCellStyle.BORDER_THIN);
             //设置边框颜色
             style.setTopBorderColor(HSSFColor.BLACK.index);
             style.setBottomBorderColor(HSSFColor.BLACK.index);
             style.setLeftBorderColor(HSSFColor.BLACK.index);
             style.setRightBorderColor(HSSFColor.BLACK.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            if(r==2){
                style.setFillForegroundColor(HSSFColor.YELLOW.index);
            }else if(r==7||r==15||r==23||r==31){
                style.setFillForegroundColor(HSSFColor.RED.index);
            }else if(r==9){
                style.setFillForegroundColor(HSSFColor.GOLD.index);
            }else if(r==17){
                style.setFillForegroundColor(HSSFColor.TAN.index);
            }else if(r==25){
                style.setFillForegroundColor(HSSFColor.SEA_GREEN.index);
            }else{
                style.setFillForegroundColor(HSSFColor.WHITE.index);
            }

            HSSFRow row = sheet.createRow(r);
        
            if(list.get(k)!=null){
            for(int j =0;j<list.get(k).size();j++){
                
                HSSFCell cell = row.createCell(j);
                
                cell.setCellValue((list.get(k).get(j))+"");
                
                cell.setCellStyle(style);
            }
            
            }
            r+=1;
            
        }
        if(r==8||r==16||r==24){
            HSSFRow row1 = sheet.createRow(r);
            for(int j =0;j<list.get(0).size();j++){
                HSSFCellStyle style = workbook.createCellStyle();
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
                HSSFCell cell = row1.createCell(j);
                cell.setCellValue("");
                cell.setCellStyle(style);
            }
            }
        
        r+=1;
        System.out.println(r);
        
        return r;
    }

除去Excel的标题‘日报’部分 和 图片导入；其余数据写入及单元格样式设计实在createRow方法里面实现的；

需要注意的是：

1.我们在页面导出excel文档时，都会涉及到一个导出路径选择的问题，在第一段代码中设置response参数之后就可以实现；发送到出请求时在直接在页面发送超链接请求，如：

2.使用浏览器导出excel的文件名如果是中文，可能会出现乱码问题（这里火狐浏览器比较特殊，所以为了不出现乱码，建议统一使用英文名）

response.setHeader("Content-Disposition", "attachment;filename=" + new String(".xls").getBytes(), "UTF-8"));// 火狐浏览器

response.setHeader("Content-Disposition", "attachment;filename= "+ URLEncoder.encode(filename, "UTF-8"));//其他