代码如下,导出表格里无图片显示,图片路径正确。
@RequestMapping(value = "/exp/expScholarshipApply", method = RequestMethod.GET)
public String exptest(HttpServletResponse response, HttpServletRequest request) throws IOException {
//导出文件名
String filename ="奖学金列表"+Util.getStrDateForYYYYMMDDHHMMSS(new Date())+".xlsx";
String sheetName = "奖学金信息";
//测试数据
List<ScholarshipApplyBean> list = new ArrayList<ScholarshipApplyBean>();
//String path=request.getSession().getServletContext().getRealPath("/");
ScholarshipApplyBean bean = new ScholarshipApplyBean();
bean.setApplierName("张三爸");
bean.setApplyFormOwner("张三");
bean.setArtConsulter("申美丽");
bean.setCourseName("课5");
bean.setCurators("吴XX");
bean.setEmail("zhangsan@google.com.cn");
bean.setFillFormDate("2016-10-25");
bean.setFormDate("2016-10-25");
bean.setFormSeqNo("14066241");
bean.setGuardianName("张三爸");
bean.setPhoneNo("17059595959");
bean.setStudentName("张三");
bean.setApplyAmtLevel(2);
ScholarshipApplyBean bean2 = new ScholarshipApplyBean();
bean2.setApplierName("李四爸");
bean2.setApplyFormOwner("李四");
bean2.setArtConsulter("申美丽");
bean2.setCourseName("课5");
bean2.setCurators("吴XX");
bean2.setEmail("zhangsan@google.com.cn");
bean2.setFillFormDate("2016-10-25");
bean2.setFormDate("2016-10-25");
bean2.setFormSeqNo("14066241");
bean2.setGuardianName("李四爸");
bean2.setPhoneNo("17059595959");
bean2.setStudentName("李四");
bean2.setApplyAmtLevel(2);
list.add(bean);
list.add(bean2);
String classParam[]=null;
String headers[]=null;
headers =new String[]{ "图片","列1", "列2","列3","列4"};
classParam = new String[]{"picSrc", "applierName","applyFormOwner","artConsulter","email"};
response.setContentType("APPLICATION/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + new String(filename.getBytes("gbk"), "iso-8859-1"));
ServletOutputStream outputStream = response.getOutputStream();
this.export11(sheetName, list, classParam, headers, outputStream);
outputStream.flush();
outputStream.close();
return null;
}
public void export11(String sheetName, List<?> dataList, String[] classParam, String[] headers, ServletOutputStream outputStream) throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
CellStyle style = this.getCellStyle(wb);
CellStyle style2 = this.getCellStyle2(wb);
CellStyle style3 = this.getCellStyle2(wb);
CellStyle style4 = this.getCellStyle2(wb);
Sheet sheet = wb.createSheet(sheetName);
XSSFDataFormat df = wb.createDataFormat();
//标题列
Row rowHeader = sheet.createRow(0);
//标题列填充
for (int i = 0; i < headers.length; i++) {
Cell cellHeader = rowHeader.createCell(i);
cellHeader.setCellStyle(style);
cellHeader.setCellValue(headers[i]);
sheet.setColumnWidth(i, 5000); //顺便设置列宽
}
//测试要插入的图片地址
String imageAdress="F://image2.png";
XSSFDrawing drawing = (XSSFDrawing) sheet.createDrawingPatriarch();
//内容填充
for(int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i+1);
row.setHeight((short) 2500); //设置行高
for(int j = 0; j < classParam.length; j++) {
Object obj = ClassUtils.invokeGet(dataList.get(i), classParam[j]);
Cell cell = row.createCell(j);
this.formatValue(row,obj,cell,style2,style3,style4,df);
}
//首列都插入图片
testDrawImg(wb,drawing,imageAdress,0,i+1);
}
wb.write(outputStream);
outputStream.close();
}
public void testDrawImg(XSSFWorkbook wb , XSSFDrawing drawing,String imgSrc,int col,int row) throws FileNotFoundException, IOException{
int pictureIdx = wb.addPicture(IOUtils.toByteArray(new FileInputStream(imgSrc)), Workbook.PICTURE_TYPE_PNG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0,0,5000,2500,(short) col, row, (short)col, row);
XSSFPicture pict= drawing.createPicture(anchor, pictureIdx);
pict.resize(0.5);
}
private void formatValue(Row row, Object obj, Cell cell, CellStyle style2,CellStyle style3,CellStyle style4,XSSFDataFormat df) {
Boolean isInteger=false;//data是否为整数
if (obj != null ) {
if(!(obj instanceof String)){
//判断data是否为整数(小数部分是否为0)
isInteger=obj.toString().matches("^[-\\+]?[\\d]*$");
if (isInteger) {
style3.setDataFormat(df.getFormat("#,##0"));//数据格式只显示整数
cell.setCellStyle(style3);
}else{
style4.setDataFormat(df.getFormat("#,##0.00"));//保留两位小数点
cell.setCellStyle(style4);
}
cell.setCellValue(Double.parseDouble(obj.toString()));
}else{
style2.setDataFormat(df.getFormat("@")); //数据格式为文本
cell.setCellStyle(style2);
cell.setCellValue(obj+"");
}
}else{
style2.setDataFormat(df.getFormat("@"));//数据格式为文本
cell.setCellStyle(style2);
cell.setCellValue("");
}
}
private CellStyle getCellStyle(XSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font font = wb.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
private CellStyle getCellStyle2(XSSFWorkbook wb) {
CellStyle style2 = wb.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font font2 = wb.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
style2.setFont(font2);
return style2;
}