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