EasyPOIExcel模板导出，多图片，多sheet页

程序员小英

于 2024-06-13 15:49:51 发布

阅读量350

点赞数 2

文章标签：模版

本文链接：https://blog.csdn.net/2401_83384536/article/details/139656226

版权

原文来源于：程序员恰恰

如有侵权，联系删除

一、导入需要的依赖

1、maven项目需要导入的依赖，其他项目自行去仓库下载依赖

 <dependency>
      <groupId>cn.afterturn</groupId>
       <artifactId>easypoi-base</artifactId>
       <version>4.2.0</version>
</dependency>
        
 <dependency>
     <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.2.0</version>
</dependency>
        
 <dependency>
     <groupId>cn.afterturn</groupId>
     <artifactId>easypoi-annotation</artifactId>
      <version>4.2.0</version>
 </dependency>

2、会出现的问题

<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi-ooxml</artifactId>
 <version>4.1.1</version>
 </dependency>

3、上代码

这里是需要导的包，很多地方贴代码不贴包实在头痛

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;
import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.xykj.common.core.entity.ResponseBean;
import com.xykj.domain.vo.FinishedFlowDto;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Component;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;

（1）、Excel导出

/**
  * Excel导出
  * @return
  */
 public ResponseBean ExcelFlow(FinishedFlowDto finishedFlow, HttpServletResponse response)throws Exception{
  OutputStream out = null;
  /**设置文件的名称*/
  String sworkName = "成品制作流程单";
  String filename = new String(sworkName.getBytes(),"ISO-8859-1");
  URLEncoder.encode(filename,"UTF-8");
  //创建servlet输出流
  response.setHeader("Content-disposition","attachment;filename="+filename+".xls");
  response.setContentType("application/vnd.ms-excel;charset=utf-8");
  out = response.getOutputStream();
  Map<String, Object> map = setExcelInfo(finishedFlow);
  Workbook workbook = getWorkbook(map, "成品制作单", "设计定稿画面");
  workbook.write(out);
  return ResponseBean.success().setData(filename);
 }

（2）、获取Workbook对象

**
  * 获取Workbook对象
  * @param map 需要导出的信息（多sheet）
  * @param sheetName sheet名称
  * @return
  */
 public Workbook getWorkbook(Map<String, Object> map,String... sheetName){
  /**模板所在路径，打开配置多sheet*/
  TemplateExportParams params = new TemplateExportParams("template/finished_flow.xlsx",true,sheetName);
  Workbook workbook = ExcelExportUtil.exportExcel(params,map);
  return workbook;
 }

（3）、设置需要导出的数据

/**
  * 设置需要导出的数据
  * @param finishedFlow
  * @return
  * @throws Exception
  */
 public Map<String, Object> setExcelInfo(FinishedFlowDto finishedFlow)throws Exception{
  /**数据配置*/
  Map<String, Object> map = new HashMap<String, Object>();
  map.put("projectSn",finishedFlow.getProjectSn());
  map.put("area",finishedFlow.getArea());
  map.put("projectName", finishedFlow.getProjectName());
  map.put("item",finishedFlow.getItem());
  map.put("receiveTime", finishedFlow.getReceiveTime());
  map.put("finishedSize", finishedFlow.getFinishedSize());
  map.put("number", finishedFlow.getNumber());
  map.put("willReach", finishedFlow.getWillReach());
  map.put("install", finishedFlow.getInstall());
  map.put("urgent", finishedFlow.getUrgent());
  map.put("requirements", finishedFlow.getRequirements());
  map.put("alterContent", finishedFlow.getClientAlterContent());
  getImage(map,finishedFlow.getClientUrl());
  map.put("stylist", finishedFlow.getStylist());
  map.put("auditor", finishedFlow.getAuditor());
  map.put("generalControl", finishedFlow.getGeneralControl());
  /**设置多个sheet页*/
  Map<String, Object> map11 = new HashMap<String, Object>();
  map11.put("map",map);
  map11.put("map1",getSheet2(finishedFlow));
  return map11;
 }

（4）、第二个sheet页数据

/**
  * 第二个sheet页数据
  * @return
  * @throws Exception
  */
 public Map<String, Object> getSheet2(FinishedFlowDto finishedFlow)throws Exception{
  Map<String, Object> map = new HashMap<>();
  map.put("designUrl",getDesignUrl(map,finishedFlow.getLectureUrl()));
  map.put("stylist",finishedFlow.getStylist());
  map.put("designAlterContent",finishedFlow.getLectureAlterContent());
  return map;
 }

（5）、下载img（因为我的图片在七牛云上，所以需要先下载下来）

/**
  * 下载img
  * @param urlImg
  * @return
  * @throws Exception
  */
 public byte[] getImg(String urlImg)throws Exception{
  URL url = new URL(urlImg);//获取人员照片的地址
  //打开链接
  HttpURLConnection conn = (HttpURLConnection) url.openConnection();
  //设置请求方式为"GET"
  conn.setRequestMethod("GET");
  //超时响应时间为5秒
  conn.setConnectTimeout(5 * 1000);
  //通过输入流获取图片数据
  InputStream inStream = conn.getInputStream();
  //得到图片的二进制数据，以二进制封装得到数据，具有通用性
  ByteArrayOutputStream outStream = new ByteArrayOutputStream();
  //创建一个Buffer字符串
  byte[] buffer = new byte[1024];
  //每次读取的字符串长度，如果为-1，代表全部读取完毕
  int len = 0;
  //使用一个输入流从buffer里把数据读取出来
  while ((len = inStream.read(buffer)) != -1){
   //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
   outStream.write(buffer, 0, len);
  }
  //关闭输入流
  inStream.close();
  //把outStream里的数据写入内存
  return outStream.toByteArray();
 }

（6）、把对应的图片设置到单元格中

/**
  * 把对应的图片设置到单元格中
  * @param map 单元格数据对象
  * @param urlImg 需要传输的图片
  * @return
  * @throws Exception
  */
 public Map<String, Object> getImage(Map<String, Object> map ,String urlImg)throws Exception{
  String[] split = urlImg.split(",");
  int i = 1;
  for (String s : split) {
   ImageEntity image = new ImageEntity();
   image.setData(getImg(s));
   image.setType(ImageEntity.Data);
   image.setHeight(200);
   image.setWidth(150);
   map.put("clientUrl"+i,image);
   i++;
  }
  return map;
 }

（7）、获取定稿图片

/**
  * 获取定稿图片
  * @param map
  * @param urlImg
  * @return
  * @throws Exception
  */
 public Map<String, Object> getDesignUrl(Map<String, Object> map ,String urlImg)throws Exception{
  String[] split = urlImg.split(",");
  int i = 1;
  for (String s : split) {
   ImageEntity image = new ImageEntity();
   image.setData(getImg(s));
   image.setType(ImageEntity.Data);
   image.setHeight(200);
   image.setWidth(150);
   map.put("designUrl"+i,image);
   i++;
  }
  return map;
 }

3、成品制作流程单（控制层）

 /**
     * 成品制作流程单-导出
     * @param response
     * @return
     */
    @ApiImplicitParams({
            @ApiImplicitParam(name = "orderItemsId", value = "订单明细id",required = true)
    })
    @ApiOperation(value = "成品制作流程单-导出",notes = "报表管理")
    @GetMapping("/flowExport")
    public void flowExport(HttpServletResponse response ,Long orderItemsId) throws Exception{
        FinishedFlowDto finishedFlowDto = orderItemsService.selectFinishedFlowInfo(orderItemsId);
        excelUtil.ExcelFlow(finishedFlowDto,response);
    }