POI导出excel

POI导出excel

在项目中，我们经常会遇到需要把数据库的数据导出到excel或word中，在这里我记录下，我使用POI导出excel,并且在导出excel中包含图片。
代码如下:

		//查询条件
        Map<String,Object> map = new HashMap<>();
        //获取数据库的数据
        List<Map<String,Object>> listData =enterpriseTaskMapper.selectBywhere(map);
        //导出第一行标题
        String[] title = {"安全问题", "环保问题", "其他问题", "问题分类", "检查内容", "检查人", "选择企业", "检查时间", "问题附件","处理时间","处理结果","处理结果附件"};
        //导出第一行对应在list中字段
        String[] value = {"security", "environment", "other", "problem_class_name", "measures", "check_user", "affiliated_dept_name", "check_time", "attachment","dispose_time","dispose_result","dispose_result_attachment"};
		//实列po
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFSheet sheet = hwb.createSheet();
        sheet.setDefaultColumnWidth(20);
        //针对有图片的列，我把宽度设置稍微宽点。
        sheet.setColumnWidth(8,100 * 256);
        //针对有图片的列，我把宽度设置稍微宽点。
        sheet.setColumnWidth(11,100 * 256);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = hwb.createCellStyle();
        style.setWrapText(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFCell cell;
        for (int i = 0; i < title.length; i++) {
            row.setHeightInPoints(30);
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        if(listData.size() >0){
            for (int j=0;j<listData.size();j++){
                row = sheet.createRow(j+1);
                for(int m =0; m<value.length;m++){
                    cell = row.createCell(m);
                    //判断是否返回为空
                    if( value[m] == "attachment" || value[m] == "dispose_result_attachment"){
                        String fileRealPath = null;
                        String attachName  = listData.get(j).get(value[m]) == null ? "":listData.get(j).get(value[m]).toString();
                        if(!"".equals(attachName) ){
                             String[] files =  attachName.split(",");
                             int h = files.length;
                             row.setHeightInPoints(h*100);
                             for (int n =0;n<files.length;n++){
                                    String tmpFileName = files[0];
                                     fileRealPath = attachmentService.getRealPath(tmpFileName);
                                     File file = new File(fileRealPath);
                                     ByteArrayOutputStream byteArrayOut = null;
                                     try {
                                         BufferedImage bufferedImage = ImageIO.read(file);
                                         byteArrayOut = new ByteArrayOutputStream();
                                         ImageIO.write(bufferedImage, "jpg", byteArrayOut);
                                     }catch (IOException e) {
                                         e.printStackTrace();
                                     }
                                     byte[] data = byteArrayOut.toByteArray();
                                     HSSFPatriarch drawingPatriarch = sheet.createDrawingPatriarch();
                                     HSSFClientAnchor anchor = new HSSFClientAnchor(0, n*(255/h), 723, (n+1)*(255/h), (short)m, j+1, (short) m, j+1);
                                     anchor.setAnchorType(2);
                                     drawingPatriarch.createPicture(anchor, hwb.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG));
                             }

                        }
                    }else{
                        cell.setCellValue(listData.get(j).get(value[m]) == null ? "":listData.get(j).get(value[m]).toString());
                    }
                    cell.setCellStyle(style);
                }
            }
        }
        String filename = "检查情况.xls";
        try {
            filename = new String(filename.getBytes("UTF-8"),"ISO8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/octet-stream;charset=ISO8859-1");
        response.setHeader("Content-Disposition", "attachment;filename="+filename );
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            hwb.write(os);
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }