java 导出blob图片到excel

实现功能，导出当前页面显示员工的图片，核心代码已给出，仅供参考， 如需转载请注明出处http://www.cnblogs.com/wangjianguang/p/7852060.html 

我这里一页最多将50张图片导出到excel中，花费时间大概2秒钟，导出图片做限制，主要也是为了性能考虑，

我暂时没有好的办法，因为我们一个页面最多展示50条数据，如果用户想导出100条数据，那只能点击下一页，然后点击导出。。。要是大神路过，欢迎指导，感谢！

 

@SuppressWarnings("deprecation")
    @RequestMapping("export-employeeImage")
    public void exportEmployeeImage(ModelMap model, TrainerQuery  query,
            HttpServletRequest request,HSSFWorkbook workbook, HttpServletResponse response)
            throws NoSuchFieldException, SecurityException,
            IllegalStateException, ServletException, IOException,
            WriteException, SQLException {
            response.reset();
            
            response.setContentType("APPLICATION/vnd.ms-excel;charset=UTF-8");
            // 注意，如果去掉下面一行代码中的attachment; 那么也会使IE自动打开文件。
            response.setHeader(
                    "Content-Disposition",
                    "attachment; filename="
                            + java.net.URLEncoder.encode(
                                    "employeePhoto"+DateUtil.getExportDate()+".xls", "UTF-8"));
            OutputStream os = response.getOutputStream();
            String logoRealPathDir =request.getSession().getServletContext().getRealPath("//modile//employee-photo.xlsx");   //定义的模板，模板规则每行显示5个，显示6行，上下行间隔11个空格
            InputStream input_document = new FileInputStream(new File(logoRealPathDir));
            XSSFWorkbook input_work = new XSSFWorkbook(input_document);
            //-------------------begin导出图片-----------------------------------
            XSSFSheet inpub_sheet = input_work.getSheetAt(0);
            
            // 设置样式
            XSSFCellStyle textStyle10 = input_work.createCellStyle();
            // 设置垂直居中
            textStyle10.setAlignment(HorizontalAlignment.CENTER);
            textStyle10.setVerticalAlignment((short) 1);
            // 设置细边框
            textStyle10.setBorderBottom(BorderStyle.THIN);
            textStyle10.setBorderRight(BorderStyle.THIN);
            textStyle10.setWrapText(true);
            XSSFFont fontText2 = input_work.createFont();
            // 字体号码
            fontText2.setFontHeightInPoints((short) 11);
            // 字体名称
            fontText2.setFontName("微软雅黑");
            textStyle10.setFont(fontText2);
            //为了防止速度过慢，我这里设置了只能下载当前页的数据图片，可根据实际需要调整
            query.setCurrentPage(query.getTxtcurrentPage());
            query.setPageSize(query.getTxtpageSize());
            query.setTotalItem(query.getTotal());
            List<Map> picture = trainerService.getLecturersPhotos(query); //查询数据库获取图片，可根据实际需要修改
            XSSFRow input_row = null;
            int rowNum=11;
            int colNum=0;
            int row1=2;
            int row2=10;
            for (int i = 0; i < picture.size(); i++) {
                Map map = (Map) picture.get(i);
//                if(rowNum%11==0){
                    input_row = inpub_sheet.getRow(rowNum);  //获取excel行
//                }
                String trainCode = map.get("TRAINERCODE").toString();  //读取数据
                String name = map.get("NAME").toString();
                input_row.getCell(colNum).setCellStyle(textStyle10);  //设置单元格格式
                input_row.getCell(colNum).setCellValue(trainCode+"-"+name);  //往单元格中填充内容
                BLOB blob = (BLOB) map.get("AVATAR");
                BufferedInputStream in =null;
                InputStream inStream = blob.getBinaryStream();  //得到流对象
                long nLen = blob.length();
                int nSize = (int) nLen;  
                byte[] data = new byte[nSize];
                inStream.read(data);
                data=ImageHepler.ChangeImgSize(data,130,160);  //将读取数据流转换成图片，并设置大小
                inStream = new ByteArrayInputStream(data);
                try {
                    in = new BufferedInputStream(inStream,1024);
                } catch (Exception e1) {
                    e1.printStackTrace();
                }
                int pictureIdx =input_work.addPicture(in, input_work.PICTURE_TYPE_JPEG);//向excel中插入图片
                XSSFDrawing drawing = inpub_sheet.createDrawingPatriarch();  //创建绘图对象
               //   XSSFClientAnchor的参数说明：    
                 //   参数   说明    
                 //  dx1  第1个单元格中x轴的偏移量     
                 //  dy1  第1个单元格中y轴的偏移量     
                 //  dx2     第2个单元格中x轴的偏移量     
                 //  dy2  第2个单元格中y轴的偏移量    
                 //  col1 第1个单元格的列号     
                 //  row1  第1个单元格的行号     
                 //  col2 第2个单元格的列号     
                 //  row2 第2个单元格的行号     
                XSSFClientAnchor anchor= new XSSFClientAnchor(1, 1, 1, 1,(short) colNum, row1, (short) colNum+1, row2);//定位图片的位置
                XSSFPicture pict = drawing.createPicture(anchor, pictureIdx); 
                response.setContentType("image/jpeg, image/jpg, image/png, image/gif");
                pict.resize();
                colNum=colNum+3;
                if(colNum==15){
                    colNum=0;
                    rowNum=rowNum+13;
                    row1=row1+13;
                    row2=row2+13;
                }
            }
        try {
            input_work.write(os);
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

package common.image.util;


import java.awt.geom.AffineTransform;
import java.awt.image.AffineTransformOp;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;

import java.io.IOException;


import javax.imageio.ImageIO;

public class ImageHepler
{
 
  /**
     * 图片转换，将读取图片放大或缩小
     * 
     * */
    public static  byte[] ChangeImgSize(byte[] data, int nw, int nh){     
        byte[] newdata = null;     
        try{      
             BufferedImage bis = ImageIO.read(new ByteArrayInputStream(data));     
                int w = bis.getWidth();     
                int h = bis.getHeight();     
                double sx = (double) nw / w;     
                double sy = (double) nh / h;     
                AffineTransform transform = new AffineTransform();     
                transform.setToScale(sx, sy);     
                AffineTransformOp ato = new AffineTransformOp(transform, null);     
                //原始颜色     
                BufferedImage bid = new BufferedImage(nw, nh, BufferedImage.TYPE_3BYTE_BGR);     
                ato.filter(bis, bid);     
                //转换成byte字节     
                ByteArrayOutputStream baos = new ByteArrayOutputStream();     
                ImageIO.write(bid, "jpeg", baos);     
                newdata = baos.toByteArray();     
        }catch(IOException e){      
             e.printStackTrace();      
        }      
        return newdata;     
    }    
}