springboot + 若依 ruoyi + easypoi excel的导入导出(带图片)
一、官方文档
二、快速开始
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;
@Data
public 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")
@ResponseBody
public AjaxResult save(MultipartFile file) throws Exception {
excelService.importExhibitors(file);
return AjaxResult.success();
}
//service
public interface ExcelService {
void importExhibitors(MultipartFile exhibitors);
void exportExhibitors(HttpServletResponse response);
}
//serviceImpl
@Override
public 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 com.ruoyi.common.config.RuoYiConfig;
import lombok.Data;
import java.io.Serializable;
@Data
public 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")
@ResponseBody
public void export(HttpServletResponse response)
{
excelService.exportExhibitors(response);
}
//service
package com.jsiec.exhibitionserver.generator.service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
/**
* @author Pekah
* @create 2023/2/15 16:28
*/
public interface ExcelService {
void importExhibitors(MultipartFile exhibitors);
void exportExhibitors(HttpServletResponse response);
}
//serviceImpl
@Override
public 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);
//导入excel
Workbook 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不一致