参照博文echart图导出到pdf,将前台传到后台的二十四位字节编码生成图片,然后将图片导出到excel中。
public static void exportEchartsImg(HttpServletResponse response,List<String> pngName,String fileName,String path,Map<String, Object> map,String staticServer){
XSSFWorkbook book = new XSSFWorkbook();// 创建Excel文件
XSSFSheet sheet = book.createSheet(fileName); // 创建一个工作薄
BufferedImage bufferImg = null;
try{
XSSFDrawing patri = sheet.createDrawingPatriarch();//一个sheet只能一个
int rowBegin = 2;//起始行
int rowEnd = 20;//终止行
int rowBefore = 0;//起始列
int rowAfter = 3;//终止列
for(int i=0;i<pngName.size();i++){
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();//一定要重新定义,不然输出始终是第一张
bufferImg = ImageIO.read(new File(path+pngName.get(i)));
ImageIO.write(bufferImg,"png",outputStream);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,(short) rowBefore, rowBegin, (short) rowAfter, rowEnd);
patri.createPicture(anchor, book.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
rowBefore = rowBefore+3;
rowAfter = rowAfter+3;
//rowBegin = (i+1)*15+2;
// rowEnd = rowBegin + 15;
}
CellStyle cellDataStyle = book.createCellStyle();
org.apache.poi.ss.usermodel.Font fontData = book.createFont();
fontData.setFontHeightInPoints((short) 10);
fontData.setFontName("宋体");
cellDataStyle.setFont(fontData);
cellDataStyle.setBorderBottom((short) 1);
cellDataStyle.setBorderLeft((short) 1);
cellDataStyle.setBorderRight((short) 1);
cellDataStyle.setBorderTop((short) 1);
cellDataStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellDataStyle.setFillForegroundColor(HSSFColor.WHITE.index);
cellDataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 创建一个DataFormat对象
DataFormat format = book.createDataFormat();
// 这样才能真正的控制单元格格式,@就是指文本型
cellDataStyle.setDataFormat(format.getFormat("@"));
//写数据内容
String[] ymlx = getValue(map,"ymlx");
Row lxRow = sheet.createRow(21);
Cell cell = lxRow.createCell(0);
cell.setCellValue("年度");
cell.setCellStyle(cellDataStyle);
for(int i=0;i<ymlx.length;i++){
Cell cellLx = lxRow.createCell(i+1);
cellLx.setCellValue(ymlx[i]);
cellLx.setCellStyle(cellDataStyle);
}
String[] year = getValue(map,"year");
String[] imgArr = {"blue.png","red.png","green.png"};
for(int i=0;i<year.length;i++){
Row dataRow = sheet.createRow(22+i);
*//*ByteArrayOutputStream outputStream = new ByteArrayOutputStream();//一定要重新定义,不然输出始终是第一张
bufferImg = ImageIO.read(new File(path+"/"+imgArr[i]));
ImageIO.write(bufferImg,"png",outputStream);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,(short) 0, (short)(23+i), (short) 1, (short) (23+i));
patri.createPicture(anchor, book.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));*//*
String[] dataArr ={"0"};
Cell cellYear = dataRow.createCell(0);
cellYear.setCellValue(year[i]);
cellYear.setCellStyle(cellDataStyle);
if(i==0){
dataArr = getValue(map,"data1");
}else if(i==1){
dataArr = getValue(map,"data2");
}else if(i==2){
dataArr = getValue(map,"data3");
}
for(int j=0;j<dataArr.length;j++){
Cell cellData = dataRow.createCell(j+1);
cellData.setCellValue(dataArr[j]);
cellData.setCellStyle(cellDataStyle);
}
}
OutputStream os = response.getOutputStream();// 取得输出流
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-disposition", "attachment; filename=" + new String(fileName.getBytes("GBK"), "ISO-8859-1") + ".xlsx");
System.setProperty("org.apache.poi.util.POILogger", "org.apache.poi.util.POILogger");
book.write(os);
os.flush();
os.close();
}catch (IOException e){
e.printStackTrace();
}
}