java 导出blob图片到excel

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