springboot + 若依 ruoyi + easypoi excel的导入导出（带图片）
一、官方文档
gitee地址官方文档二、快速开始
1、导入
引入依赖<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>4.2.0</version></dependency>
前端代码
<!--上传按钮--><button type="button" class="btn btn-success" id="upload" shiro:hasPermission="exhibitionserver:exhibitor:add"><i class="fa fa-plus"></i> 导入展商</button><!--js代码,若依使用的是bootstrap上传，如果和我一样用了layui，需要自己加入layui的包--><script>layui.use('upload', function () {var upload = layui.upload;//指定允许上传的文件类型upload.render({elem: '#upload', url: prefix + '/import' //改成您自己的上传接口, accept: 'file' //普通文件, exts: 'xls|xlsx', done: function (res) {layer.msg(res.message);}});});</script>
实体类
package com.jsiec.exhibitionserver.pojo.excel;import cn.afterturn.easypoi.excel.annotation.Excel;import lombok.Data;import java.io.Serializable;@Datapublic class SExhExhibitorImportExcel implements Serializable {/*** 展商名称（全称）*/@Excel(name = "展商名称")private String czsName;/*** logo，150px*150px*/@Excel(name = "公司LOGO",type = 2)private String czsLogo;/*** 简介*/@Excel(name = "简介")private String czsIntro;/*** 展位号*/@Excel(name = "展位号")private String czsPosition;/*** 公司地址*/@Excel(name = "公司地址")private String czsAddress;/*** 展馆号*/@Excel(name = "展馆号")private String czsNo;private static final long serialVersionUID = 1L;}
后端接口
//controller@PostMapping("import")@ResponseBodypublic AjaxResult save(MultipartFile file) throws Exception {excelService.importExhibitors(file);return AjaxResult.success();}
//servicepublic interface ExcelService {void importExhibitors(MultipartFile exhibitors);void exportExhibitors(HttpServletResponse response);}
//serviceImpl@Overridepublic void importExhibitors(MultipartFile exhibitors) {try {ImportParams params = new ImportParams();params.setTitleRows(1);params.setHeadRows(1);List<SExhExhibitorImportExcel> result = ExcelImportUtil.importExcel(exhibitors.getInputStream(),SExhExhibitorImportExcel.class, params);//将图片上传到服务器将地址保存result.forEach(exhibitor->{if(StringUtil.isNotEmpty(exhibitor.getCzsLogo())){try {File file = new File(exhibitor.getCzsLogo());FileInputStream fileInputStream = new FileInputStream(file);/***这里需要把file转化为MultipartFile，使用了MockMultipartFile方法，需要加入依赖<dependency><groupId>org.springframework</groupId><artifactId>spring-test</artifactId><version>5.3.9</version></dependency>**/MultipartFile multipartFile = new MockMultipartFile("file", file.getName(), "text/plain", IOUtils.toByteArray(fileInputStream));//调用自己的上传接口String imgUrl = fileService.uploadFile(multipartFile,"exhibitor");exhibitor.setCzsLogo(imgUrl);//todo 把中转类的值赋给需要存储的对象} catch (Exception e) {e.printStackTrace();}}});//todo 在这里把需要存储的对象放入库}catch (Exception e){log.error("导入展商模板出错");e.printStackTrace();}}
2、导出
依赖前端代码参考导入的依赖
<!--html--><a class="btn btn-warning" onclick="exportexhibitor()"><i class="fa fa-download"></i> 展商模板</a><!--script-->function exportexhibitor() {location.href = prefix+'/export';}
实体类
package com.jsiec.exhibitionserver.pojo.excel;import cn.afterturn.easypoi.excel.annotation.Excel;import com.jsiec.exhibitionserver.generator.domain.SExhExhibitor;import mon.config.RuoYiConfig;import lombok.Data;import java.io.Serializable;@Datapublic class SExhExhibitorExcel implements Serializable {/*** 默认上传的地址*/private static String defaultBaseDir = RuoYiConfig.getProfile();/*** 展商名称（全称）*/@Excel(name = "展商名称", width = 30)private String czsName;/*** logo，150px*150px*/@Excel(name = "公司LOGO", type = 2 ,width = 40)private String czsLogo;/*** 简介*/@Excel(name = "简介", width = 150)private String czsIntro;/*** 展位号*/@Excel(name = "展位号", width = 30)private String czsPosition;/*** 公司地址*/@Excel(name = "公司地址", width = 30)private String czsAddress;/*** 展馆号*/@Excel(name = "展馆号", width = 30)private String czsNo;private static final long serialVersionUID = 1L;public SExhExhibitorExcel(SExhExhibitor sExhExhibitor){this.czsName = sExhExhibitor.getCzsName();this.czsLogo = defaultBaseDir + sExhExhibitor.getCzsLogo().replace("/profile","");this.czsIntro = sExhExhibitor.getCzsIntro();this.czsPosition = sExhExhibitor.getCzsPosition();this.czsAddress = sExhExhibitor.getCzsAddress();this.czsNo = sExhExhibitor.getCzsNo();}}
后端代码
//controller@GetMapping("export")@ResponseBodypublic void export(HttpServletResponse response){excelService.exportExhibitors(response);}
//servicepackage com.jsiec.exhibitionserver.generator.service;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;/*** @author Pekah* @create /2/15 16:28*/public interface ExcelService {void importExhibitors(MultipartFile exhibitors);void exportExhibitors(HttpServletResponse response);}
//serviceImpl@Overridepublic void exportExhibitors(HttpServletResponse response) {try {//从数据库查询到数据List<SExhExhibitor> users = sExhExhibitorMapper.selectList(null);//设置信息头，告诉浏览器内容为excel类型response.setHeader("content-Type", "application/vnd.ms-excel");//设置下载名称response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("展商模板.xlsx", StandardCharsets.UTF_8.name()));//字节流输出ServletOutputStream out = response.getOutputStream();//设置excel参数ExportParams params = new ExportParams();//设置sheet名名称params.setSheetName("展商列表");params.setTitle("展商信息");//转成对应的类型;要不然会报错，虽然也可以导出成功List<SExhExhibitorExcel> exportUsers = ToSExhExhibitorExcel(users);//导入excelWorkbook workbook = ExcelExportUtil.exportExcel(params, SExhExhibitorExcel.class, exportUsers);//写入workbook.write(out);} catch (Exception e) {log.error("导出展商模板出错");e.printStackTrace();}}
三、图片的导出相关
1、导出的两种方式
在实体列@Excel注解上，type =2表示该字段类型为图片,imageType=1 (默认可以不填),表示从file读取,字段类型是个字符串类型 可以用相对路径也可以用绝对路径,绝对路径优先依次获取.
type =2表示该字段类型为图片,imageType=2 ,表示从数据库或者已经读取完毕,字段类型是个字节数组 直接使用 同时,image 类型的cell最好设置好宽和高,会百分百缩放到cell那么大,不是原尺寸,这里注意下
所以，导出有两种方式，我在第二部分只展示了第一种方式，写入绝对路径。第二种方式也有多种实现方式
第一种方式，直接获取图片流赋值给图片属性，对应的实体类中的String也要换成byte[ ]
/*** 输出指定文件的byte数组* * @param filePath 文件路径* @param os 输出流* @return*/public static void writeBytes(String filePath, OutputStream os) throws IOException{FileInputStream fis = null;try{File file = new File(filePath);if (!file.exists()){throw new FileNotFoundException(filePath);}fis = new FileInputStream(file);byte[] b = new byte[1024];int length;while ((length = fis.read(b)) > 0){os.write(b, 0, length);}}catch (IOException e){throw e;}finally{IOUtils.close(os);IOUtils.close(fis);}}
第二种方式，直接根据图片的url下载为图片流
String pictureUrl = "图片的url";//建立图片连接URL url = new URL(pictureUrl);HttpURLConnection connection = (HttpURLConnection)url.openConnection();//设置请求方式connection.setRequestMethod("GET");//设置超时时间connection.setConnectTimeout(10*1000);//输入流InputStream stream = connection.getInputStream();byte[] res = new byte[stream.available()];//记得关闭流，不然消耗资源stream.close();return res;
四、遇到的问题
1、图片导出失败
我遇到的图片导出失败是由于依赖的版本问题，使用4.4.0导出的是空白，换成4.2.0后就成功导出了
2、excel导入的对象反射的值都是null
ImportParams params = new ImportParams();params.setTitleRows(1);params.setHeadRows(1);
标题和头数量设置错误会导致反射的对象都是null，或者由于@Excel注解的name不一致
