基于Easy-poi完成Excel模板导出,多图片,多sheet页

  • 时间:
  • 浏览:
  • 来源:互联网

一、导入需要的依赖

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

		<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>

如果poi-ooxml的版本过低(低于3.7x的版本)会报找不到org.apache.poi.xssf.usermodel.XSSFWorkbook 这个类,切记切记

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(因为我的图片在oss上,所以需要先下载下来)

/**
	 * 下载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);
    }

4、项目结构

在这里插入图片描述

5、模板样式

在这里插入图片描述
在这里插入图片描述

6、导出成品

在这里插入图片描述
搞定

本文链接http://element-ui.cn/article/show-77431.aspx