（说明，原文转载链接出自：）
实现思路：
1.由于前端通过echarts生成图形报表，所以后台没必要通过再弄一个插件生成一次图表;
2.将echarts生成的图片获取base64编码，将编码内容post参数传入后台;
3.后台接收到图片参数进行解码，生成本地图片;
4.利用poi创建的HSSFPatriarch对象.createPicture()方法将图片写入excel单元格中。
导出方法代码：
/**
*导出综合得分统计
* @param fileName
* @param request
* @param response
* @param headInfo
* @param dataList
* @param evaluationId
* @throws DeException
*/
public void exportCompositeScore(String fileName, HttpServletRequest request, HttpServletResponse response,
String[] headInfo,List<Map<String,Object>> dataList,int evaluationId,String imgUrl,String message) throws DeException {
try {
Evaluation evaluation = evaluationDao.getEvaluationInfo(evaluationId);
int completeNum = evaluatorUserDao.getEvalStatusNum(evaluationId,Status.COMPLETED.name());//获取答题结束的数量
int totalNum = evaluatorUserDao.getEvalTotalNum(evaluationId);//获取测评人总数
HSSFWorkbook book = new HSSFWorkbook();// 创建Excel文件
HSSFSheet sheet = book.createSheet(fileName); // 创建一个工作薄
// 设置标题样式
HSSFCellStyle tileStyle = book.createCellStyle();
tileStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平布局：居中
tileStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
tileStyle.setWrapText(true);
HSSFFont nameRowFont = book.createFont();
nameRowFont.setFontName("微软雅黑");
nameRowFont.setFontHeightInPoints((short) 8);// 设置字体大小
nameRowFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
tileStyle.setFont(nameRowFont);
/*创建问卷标题行*/
CellStyle wrapTextStyle = book.createCellStyle(); //创建自动换行样式
wrapTextStyle.setWrapText(true); //设置换行
HSSFRow row0 = sheet.createRow(0);
row0.setHeight((short) 500);// 设置行高
HSSFCell nameCell = null;
for(int i = 0; i < headInfo.length; i++){
nameCell = row0.createCell(i);
nameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
if(i==0){
nameCell.setCellStyle(wrapTextStyle);
nameCell.setCellValue(new HSSFRichTextString(evaluation.getEvaluation_subject()));//问卷标题
}
}
CellRangeAddress nameCellRange = new CellRangeAddress(0, 0, 0, headInfo.length-1);//标题合并单元格
sheet.addMergedRegion(nameCellRange);
/*创建问卷说明行*/
HSSFRow row1 = sheet.createRow(1);
row1.setHeight((short) 500);// 设置行高
String desc = "测评人员："+totalNum+"人"+" 已完成："+completeNum+"人";
for(int i = 0; i < headInfo.length; i++){
HSSFCell cell = row1.createCell(i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if(i==0){
cell.setCellValue(new HSSFRichTextString(desc));
}
}
CellRangeAddress summaryCellRange = new CellRangeAddress(1, 1, 0, headInfo.length-1);//说明合并单元格
sheet.addMergedRegion(summaryCellRange);
//加权提示行
HSSFRow row2 = sheet.createRow(2);
HSSFCell messageCell = null;
for(int i = 0; i < headInfo.length; i++){
messageCell = row2.createCell(i);
messageCell.setCellType(HSSFCell.CELL_TYPE_STRING);
if(i==0){
messageCell.setCellStyle(wrapTextStyle);
messageCell.setCellValue(message);//问卷标题
}
}
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, headInfo.length-1));//合并单元格；起始行，结束行，起始列，结束列
if(StringUtils.isEmpty(message)){
row2.setZeroHeight(true);//隐藏行
}
// EXCEL第四行表格标题
HSSFRow row3 = sheet.createRow(3);
HSSFCell cell = null;
row3.setHeight((short) 400);// 设置行高
for (int i = 0; i < headInfo.length; i++) {
/*设置列宽度*/
if(i==1){
sheet.setColumnWidth(i, 20 * 256);
}else if(i>1){
sheet.setColumnWidth(i, 30 * 256);
}else{
sheet.setColumnWidth(i, 12 * 256);
}
cell = row3.createCell(i);
cell.setCellValue(headInfo[i]);
cell.setCellStyle(tileStyle);
}
// EXCEL正文数据
Map<String,Object> answerObj = null;
HSSFRow dataRow = null;
Cell dataCell = null;
for (int j = 0; j < dataList.size(); j++) {
answerObj = dataList.get(j);//每一行的记录
dataRow = sheet.createRow(j+4);
dataCell = dataRow.createCell(0);//工号
dataCell.setCellValue(answerObj.get("employee_id")+"");
dataCell = dataRow.createCell(1);//姓名
dataCell.setCellValue(answerObj.get("user_name")+"");
dataCell = dataRow.createCell(2);//部门
dataCell.setCellValue(answerObj.get("department")+"");
dataCell = dataRow.createCell(3);//个人平均分
dataCell.setCellValue(Tools.formatDouble(Double.parseDouble(answerObj.get("personal_score")+"")));
dataCell = dataRow.createCell(4);//测评平均分
dataCell.setCellValue(Tools.formatDouble(Double.parseDouble(answerObj.get("avg_score")+"")));
dataCell = dataRow.createCell(5);//分差值
dataCell.setCellValue(Tools.formatDouble(Double.parseDouble(answerObj.get("difference_val")+"")));
}
/*生成图表*/
if(StringUtils.isNotEmpty(imgUrl)) {
String[] imgUrlArr = imgUrl.split("base64,");//拆分base64编码后部分
org.bouncycastle.util.encoders.Base64 decode = new org.bouncycastle.util.encoders.Base64();
byte[] buffer = decode.decode(imgUrlArr[1]);
String picPath = request.getRealPath("")+ "/"+ UUID.randomUUID().toString() +".png";
File file = new File(picPath);//图片文件
//生成图片
OutputStream out = new FileOutputStream(file);//图片输出流
out.write(buffer);
out.flush();//清空流
out.close();//关闭流
ByteArrayOutputStream outStream = new ByteArrayOutputStream(); // 将图片写入流中
BufferedImage bufferImg = ImageIO.read(new File(picPath));
ImageIO.write(bufferImg, "PNG", outStream); // 利用HSSFPatriarch将图片写入EXCEL
HSSFPatriarch patri = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) 0, dataList.size() + 5, (short) 6, dataList.size() + 35);
patri.createPicture(anchor, book.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
if(file.exists()){
file.delete();//删除图片
}
}
//输出excel文件名
fileName = Tools.processFileName(request, fileName);// 不同浏览器文件名乱码解决
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+fileName);
book.write(os);
os.flush();
os.close();
} catch (Exception e) {
logger.error("tid={} | 导出综合得分表出现异常", ContextClient.getTid(), e);
throw new ServiceException(ServiceExceptionConstant.SYSTEM_EXCEPTION);
}finally{
}
}方法说明：
HSSFClientAnchor用于创建一个新的端锚，并设置锚的左下和右下坐标,用于图片插入，画线等操作。
HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
dx1 dy1 起始单元格中的x,y坐标.
dx2 dy2 结束单元格中的x,y坐标
col1,row1 指定起始的单元格，下标从0开始
col2,row2 指定结束的单元格 ，下标从0开始
来张图更容易说明:
最后，看下生成的excel效果：
总结一下：
1.优点：开发快捷，前后端图片显示样式一致
2.缺点：不适合多图片，base64编码参数太长，请求参数太大，只能使用post方式。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
