使用springboot和easypoi进行的数据导出的小案例
在这个案例中使用的有springboot和easypoi进行数据导出到excel中
yml文件是这样的:
server: port: 8080 spring: datasource: url: jdbc:mysql://localhost:3306/testuser?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=UTC username: root password: root mybatis: mapper-locations: classpath*:mapper/*.xml mybatis-plus: mapper-locations: classpath:/mapper/*Mapper.xml log: #修改这个路径就可以直接使用在不同的地方进行显示logback path: G:/idjavacode/industry4/doc level: INFO
引入的依赖有一下几个
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!-- 文件上传组件 -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<!--阿里巴巴 fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.30</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-annotation</artifactId>
<version>3.0.6</version>
</dependency>
</dependencies>
这是大致的数据结构,没有写dao层,直接自己模拟的数据将数据进行的导出.
utils工具类是:
import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.ImportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.IOException; import java.net.URLEncoder; import java.util.List; import java.util.Map; import java.util.NoSuchElementException; /** * @Auther:qingmu * @Description:脚踏实地,只为出人头地 * @Date:Created in 16:24 2019/7/17 */ public class ExtUtils { public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) { ExportParams exportParams = new ExportParams(title, sheetName); exportParams.setCreateHeadRows(isCreateHeader); defaultExport(list, pojoClass, fileName, response, exportParams); } public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) { defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName)); } public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) { defaultExport(list, fileName, response); } private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) { Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list); if (workbook != null) ; downLoadExcel(fileName, response, workbook); } private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) { try { response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); workbook.write(response.getOutputStream()); } catch (IOException e) { // throw new NormalException(e.getMessage()); } } private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) { Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF); if (workbook != null) ; downLoadExcel(fileName, response, workbook); } public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) { if (StringUtils.isBlank(filePath)) { return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); List<T> list = null; try { list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params); } catch (NoSuchElementException e) { // throw new NormalException("模板不能为空"); } catch (Exception e) { e.printStackTrace(); // throw new NormalException(e.getMessage()); } return list; } public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) { if (file == null) { return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); List<T> list = null; try { list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params); } catch (NoSuchElementException e) { // throw new NormalException("excel文件不能为空"); } catch (Exception e) { // throw new NormalException(e.getMessage()); System.out.println(e.getMessage()); } return list; } }
实体类:
import cn.afterturn.easypoi.excel.annotation.Excel; import com.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import lombok.Data; import java.io.Serializable; /** * @Auther:qingmu * @Description:脚踏实地,只为出人头地 * @Date:Created in 16:34 2019/7/17 */ @Data @TableName("user") public class User implements Serializable { private static final long serialVersionUID = 1L; @Excel(name = "id", width = 15) @TableId("id") private int id; @Excel(name = "姓名", orderNum = "0", width = 30) @TableField("name") private String name; @TableField("age") @Excel(name = "年龄", width = 30) private int age; }
service层是:
import com.nums.poi.pojo.User; import org.springframework.stereotype.Service; import java.util.ArrayList; import java.util.List; /** * @Auther:qingmu * @Description:脚踏实地,只为出人头地 * @Date:Created in 17:22 2019/7/17 */ @Service public class UserService { public List<User> findAll(){ List<User> listAll = new ArrayList(); List<User> users = new ArrayList<>(); User user = new User(); user.setId(10); user.setName("张三"); user.setAge(25); User user1 = new User(); user1.setId(11); user1.setName("张三"); user1.setAge(26); User user2 = new User(); user2.setId(12); user2.setName("张三"); user2.setAge(27); users.add(user); users.add(user1); users.add(user2); listAll.addAll(users); return listAll; } }
controller层是:
import com.nums.poi.pojo.User; import com.nums.poi.service.UserService; import com.nums.poi.utils.ExtUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletResponse; import java.util.List; /** * @Auther:qingmu * @Description:脚踏实地,只为出人头地 * @Date:Created in 17:31 2019/7/17 */ @RestController public class ExcelController { @Autowired private UserService userService; @RequestMapping("/execl") public void export(HttpServletResponse response){ List<User> all = userService.findAll(); ExtUtils.exportExcel(all,"easypoi导出功能","导出sheet1",User.class,"测试user.xls",response); } }
最后启动springboot,然后通过端口号进行访问.
版权声明：本文为qingmuchuanqi48原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。