前言
提示:以下是本篇文章正文内容,下面案例可供参考
一、搭建springboot环境
二、使用步骤
1.引入autopoi依赖
一般只需要添加aoto-poi依赖即可,下面其他依赖是我个人演示需要用到的
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<!-- 添加mybatis依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
<!-- 添加mysql驱动-->
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- autopoi依赖-->
<dependency>
<groupId>org.jeecgframework</groupId>
<artifactId>autopoi-web</artifactId>
<version>1.2.2</version>
<exclusions>
<exclusion>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- lombok依赖-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
<build>
<!--让idea编译src/main/java目录下的xml文件-->
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
2.编写实体类
代码如下(示例):
@Data
public class Province implements Serializable {
/*
* @Excel:
* name:指excel报表中的标题
* width:指这一列单元格占多少宽度
* 一般只设置name属性即可
*
* */
private Integer id;
@Excel(name="省份名称",width = 15)
private String name;
@Excel(name = "省份简称",width = 15)
private String jiancheng;
@Excel(name = "省会城市",width = 15)
private String shenghui;
}
例如:这是我用到的excel文件
3.编写controller导入功能
从前端页面提交过来一个excel文件file即可
@RequestMapping("/toimport")//跳转到impotExcel.html界面
public String toimport(){
return "importExcel";
}
@RequestMapping("/importExcel")
public String importExcel(@RequestParam("file") MultipartFile file, Model model) {
List<Province> provinces = null;
try {
ImportParams params = new ImportParams();
//表格标题所在行,计数从0开始
params.setTitleRows(1);
//head头部所在行,计数从0开始
params.setHeadRows(1);
//表格sheet数量
params.setSheetNum(1);
InputStream inputStream = null;
inputStream = file.getInputStream();
provinces = ExcelImportUtil.importExcel(inputStream, Province.class, params);
//把导入的信息输出到控制台
for(Province pro:provinces) {
System.out.println(pro);
}
} catch (Exception e) {
model.addAttribute("msg","导入失败");
e.printStackTrace();
}
model.addAttribute("msg","导入成功");
return "importExcel";
}
编写一个html页面 ,我这里是importExcel.html
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<center>
<form method="post" action="/importExcel" enctype="multipart/form-data">
请选择Excel文件:<input type="file" name="file"><br/><br/>
<input type="submit" value="提交">
</form>
<span th:text="${msg}"></span>
</center>
</body>
</html>
访问toimport,跳转到importExcel.html界面
查看控制台,可以看到导入的结果
可以看到和excel文件里的内容一致
4.编写controller导出功能
启动springboot,访问export即可自动生成excel文件
@RequestMapping("/export")
public ModelAndView excel(HttpServletResponse response) throws IOException{
//这是我从数据库查询到list集合
List<Province> provinces = mapper.selectAllPro();
//导出
ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
//导出文件名称
mv.addObject(NormalExcelConstants.FILE_NAME,"省份信息汇总");
//注解对象Class
mv.addObject(NormalExcelConstants.CLASS,Province.class);
//必须添加,否则会报空指针异常
mv.addObject(NormalExcelConstants.PARAMS,new ExportParams("省份信息","省份"));
//自定义表格参数
//导出数据列表
mv.addObject(NormalExcelConstants.DATA_LIST,provinces);
return mv;
}
数据库表如下
访问export,自动生成excel报表,保存到本地即可
总结
最后附上autopoi教程链接:http://doc.jeecg.com/2044224点击我即可跳转