- pom文件依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>1.1.2-beta5</version> </dependency> - ExporExcelController 文件中
@RequestMapping("/exce") @Controller public class ExporExcelController { //植入Service @Resource(name = "idemoService") private DemoService demoService; /* *直接输出数据到前台 */ @RequestMapping("/export") public String ExporExcel(HttpServletResponse response) throws Exception { //throws IOException { ExcelWriter writer = null; OutputStream outputStream = response.getOutputStream(); try { //添加响应头信息 response.setHeader("Content-disposition", "attachment; filename=" + "catagory.xls"); response.setContentType("application/msexcel;charset=UTF-8");//设置类型 response.setHeader("Pragma", "No-cache");//设置头 response.setHeader("Cache-Control", "no-cache");//设置头 response.setDateHeader("Expires", 0);//设置日期头 //实例化 ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLS, true); //实例化表单 Sheet sheet = new Sheet(1, 0, Catagory.class); sheet.setSheetName("测试"); //获取数据 List<Catagory> catagoryList = demoService.findAllToExcel(); //System.out.println(catagoryList.size()); //输出 writer.write(catagoryList, sheet); writer.finish(); outputStream.flush(); } catch (IOException e) { e.printStackTrace(); } finally { try { response.getOutputStream().close(); } catch (IOException e) { e.printStackTrace(); } } return null; } /* * */ @RequestMapping("/import") @ResponseBody public void importExcel(@RequestParam("file") MultipartFile file) throws IOException { InputStream inputStream = file.getInputStream(); //实例化实现了AnalysisEventListener接口的类 ExcelListener listener = new ExcelListener(); //传入参数 ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener); //读取信息 excelReader.read(new Sheet(1, 1, Catagory.class)); //获取数据 List<Object> list = listener.getDatas(); List<Catagory> catagoryList = new ArrayList<Catagory>(); Catagory catagory = new Catagory(); //转换数据类型,并插入到数据库 for (int i = 0; i < list.size(); i++) { catagory = (Catagory) list.get(i); System.out.println(catagory); demoService.addForExcel(catagory); } } /* *在服务器上生成excel */ @RequestMapping("/exportserver") @ResponseBody public String writeBySimple() { /* System.out.println(System.getProperty("user.dir")); String path =System.getProperty("user.dir")+"\Index"; //所创建文件目录 */ String path = System.getProperty("user.dir")+"\target\classes\static"; String filePath = "tmpexcel"; File f = new File(path+"\"+filePath); if(!f.exists()) { f.mkdirs(); //创建目录 } String fileName = "测试.xlsx"; List<List<Object>> data = new ArrayList<>(); data.add(Arrays.asList("111", "222", "333")); data.add(Arrays.asList("111", "222", "333")); data.add(Arrays.asList("111", "222", "333")); List<String> head = Arrays.asList("表头1", "表头2", "表头3"); ExcelUtil.writeBySimple(path+"\"+filePath+"\"+fileName, data, head); return filePath+"\"+fileName; //return null; } } -
DeamDao中
@Component("DemoEmDAO") public interface DemoDAO { //excel查询所有 @Select("select id,name,sex,age from em") public List<Catagory> findAllToExcel() throws Exception; //添加 public int addForExcel(Catagory model); } - Model中
@EqualsAndHashCode(callSuper = true) @Data public class Catagory extends BaseRowModel { @ExcelProperty(value = "id", index = 0) private Integer id; @ExcelProperty(value = "姓名", index = 1) private String name; @ExcelProperty(value = "性别", index = 2) private String sex; @ExcelProperty(value = "年龄", index = 3) private Integer age; } - DemoService中
public interface DemoService { //excel查询所有 public List<Catagory> findAllToExcel() throws Exception; //添加 public int addForExcel(Catagory model); } - DemoServiceImpl中
@Service("idemoService") public class DemoServiceImpl implements DemoService { @Resource(name = "DemoEmDAO") private DemoDAO dao; @Override public List<Catagory> findAllToExcel() throws Exception { return dao.findAllToExcel(); } @Override public int addForExcel(Catagory model) { dao.addForExcel(model); return 0; } } - ExcelListener中
public class ExcelListener extends AnalysisEventListener { //可以通过实例获取该值 private List<Object> datas = new ArrayList<Object>(); public void invoke(Object o, AnalysisContext analysisContext) { datas.add(o);//数据存储到list，供批量处理，或后续自己业务逻辑处理。 doSomething(o);//根据自己业务做处理 } private void doSomething(Object object) { //1、入库调用接口 } public List<Object> getDatas() { return datas; } public void setDatas(List<Object> datas) { this.datas = datas; } public void doAfterAllAnalysed(AnalysisContext analysisContext) { // datas.clear();//解析结束销毁不用的资源 } } - ExcleUtil中
@Slf4j public class ExcelUtil { // private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class); private static Sheet initSheet; static { initSheet = new Sheet(1, 0); initSheet.setSheetName("sheet"); //设置自适应宽度 initSheet.setAutoWidth(Boolean.TRUE); } /** * 读取少于1000行数据 * @param filePath 文件绝对路径 * @return */ public static List<Object> readLessThan1000Row(String filePath){ return readLessThan1000RowBySheet(filePath,null); } /** * 读小于1000行数据, 带样式 * filePath 文件绝对路径 * initSheet ： * sheetNo: sheet页码，默认为1 * headLineMun: 从第几行开始读取数据，默认为0, 表示从第一行开始读取 * clazz: 返回数据List<Object> 中Object的类名 */ public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet){ if(!StringUtils.hasText(filePath)){ return null; } Logger log = LoggerFactory.getLogger(ExcelUtil.class); sheet = sheet != null ? sheet : initSheet; InputStream fileStream = null; try { fileStream = new FileInputStream(filePath); return EasyExcelFactory.read(fileStream, sheet); } catch (FileNotFoundException e) { log.info("找不到文件或文件路径错误, 文件：{}", filePath); }finally { try { if(fileStream != null){ fileStream.close(); } } catch (IOException e) { log.info("excel文件读取失败, 失败原因：{}", e); } } return null; } /** * 读大于1000行数据 * @param filePath 文件觉得路径 * @return */ public static List<Object> readMoreThan1000Row(String filePath){ return readMoreThan1000RowBySheet(filePath,null); } /** * 读大于1000行数据, 带样式 * @param filePath 文件觉得路径 * @return */ public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet){ if(!StringUtils.hasText(filePath)){ return null; } Logger log = LoggerFactory.getLogger(ExcelUtil.class); sheet = sheet != null ? sheet : initSheet; InputStream fileStream = null; try { fileStream = new FileInputStream(filePath); ExcelListener excelListener = new ExcelListener(); EasyExcelFactory.readBySax(fileStream, sheet, excelListener); return excelListener.getDatas(); } catch (FileNotFoundException e) { log.error("找不到文件或文件路径错误, 文件：{}", filePath); }finally { try { if(fileStream != null){ fileStream.close(); } } catch (IOException e) { log.error("excel文件读取失败, 失败原因：{}", e); } } return null; } /** * 生成excle * @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx * @param data 数据源 * @param head 表头 */ public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head){ writeSimpleBySheet(filePath,data,head,null); } /** * 生成excle * @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx * @param data 数据源 * @param sheet excle页面样式 * @param head 表头 */ public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet){ sheet = (sheet != null) ? sheet : initSheet; if(head != null){ List<List<String>> list = new ArrayList<>(); head.forEach(h -> list.add(Collections.singletonList(h))); sheet.setHead(list); } OutputStream outputStream = null; ExcelWriter writer = null; try { outputStream = new FileOutputStream(filePath); writer = EasyExcelFactory.getWriter(outputStream); writer.write1(data,sheet); } catch (FileNotFoundException e) { // log.error("找不到文件或文件路径错误, 文件：{}", filePath); System.out.println("找不到文件或文件路径错误, 文件：{}"+ filePath); }finally { try { if(writer != null){ writer.finish(); } if(outputStream != null){ outputStream.close(); } } catch (IOException e) { // log.error("excel文件导出失败, 失败原因：{}", e); System.out.println("excel文件导出失败, 失败原因：{}"); } } } /** * 生成excle * @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx * @param data 数据源 */ public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data){ writeWithTemplateAndSheet(filePath,data,null); } /** * 生成excle * @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx * @param data 数据源 * @param sheet excle页面样式 */ public static void writeWithTemplateAndSheet(String filePath, List<? extends BaseRowModel> data, Sheet sheet){ if(CollectionUtils.isEmpty(data)){ return; } sheet = (sheet != null) ? sheet : initSheet; sheet.setClazz(data.get(0).getClass()); OutputStream outputStream = null; ExcelWriter writer = null; try { outputStream = new FileOutputStream(filePath); writer = EasyExcelFactory.getWriter(outputStream); writer.write(data,sheet); } catch (FileNotFoundException e) { // log.error("找不到文件或文件路径错误, 文件：{}", filePath); System.out.println("找不到文件或文件路径错误, 文件：{}"+ filePath); }finally { try { if(writer != null){ writer.finish(); } if(outputStream != null){ outputStream.close(); } } catch (IOException e) { // log.error("excel文件导出失败, 失败原因：{}", e); System.out.println("excel文件导出失败, 失败原因：{}"); } } } /** * 生成多Sheet的excle * @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx * @param multipleSheelPropetys */ public static void writeWithMultipleSheel(String filePath,List<MultipleSheelPropety> multipleSheelPropetys){ if(CollectionUtils.isEmpty(multipleSheelPropetys)){ return; } OutputStream outputStream = null; ExcelWriter writer = null; try { outputStream = new FileOutputStream(filePath); writer = EasyExcelFactory.getWriter(outputStream); for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) { Sheet sheet = multipleSheelPropety.getSheet() != null ? multipleSheelPropety.getSheet() : initSheet; if(!CollectionUtils.isEmpty(multipleSheelPropety.getData())){ sheet.setClazz(multipleSheelPropety.getData().get(0).getClass()); } writer.write(multipleSheelPropety.getData(), sheet); } } catch (FileNotFoundException e) { // log.error("找不到文件或文件路径错误, 文件：{}", filePath); System.out.println("找不到文件或文件路径错误, 文件：{}"+ filePath); }finally { try { if(writer != null){ writer.finish(); } if(outputStream != null){ outputStream.close(); } } catch (IOException e) { // log.error("excel文件导出失败, 失败原因：{}", e); System.out.println("excel文件导出失败, 失败原因：{}"); } } } } - JsonDateSerializer中
public class JsonDateSerializer extends JsonSerializer<Date> { private SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); @Override public void serialize(Date date, JsonGenerator gen, SerializerProvider provider) throws IOException, JsonProcessingException { String value = dateFormat.format(date); gen.writeString(value); } } - MultipleSheelPropety中
@Data public class MultipleSheelPropety { private List<? extends BaseRowModel> data; //可以通过实例获取该值 // private List<Object> data = new ArrayList<Object>(); private Sheet sheet; public List<? extends BaseRowModel> getData() { return data; } public void setDatas(List<? extends BaseRowModel> data) { this.data = data; } public Sheet getSheet() { return sheet; } public void setSheet(Sheet sheet) { this.sheet = sheet; } } - DemoMapper.xml中
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.exceldemo.demo.mapper.DemoDAO" > <resultMap id="BaseResultMap" type="com.exceldemo.demo.entity.Em" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="name" property="name" jdbcType="VARCHAR" /> <result column="sex" property="sex" jdbcType="TIMESTAMP" /> <result column="age" property="age" jdbcType="INTEGER" /> <result column="createDate" property="createDate" jdbcType="TIMESTAMP" /> </resultMap> <!--01.查询所有记录--> <select id="findAll" resultType="com.exceldemo.demo.entity.Em"> select * from em </select> <!--excel导入--> <insert id="addForExcel"> insert into em(name,sex,age,createDate) values(#{name},#{sex},#{age},now()) </insert> </mapper> - html文件
<!DOCTYPE html> <html lang="zh-CN" xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org"> <head> <meta charset="utf-8" /> <title>excel导入导出</title> <script type="text/javascript" th:src="@{/js/jquery-3.4.1.min.js}"></script> </head> <body> <!--表单提交--> <form action="/exce/import" method="post" enctype="multipart/form-data"> <input type="file" name="file" id="file"/> <input type="submit" value="表单提交"> </form> <!--通过js提交--> <input type="file" name="file" id="file2"/> <input name="btnSubmit" type="button" value="JS导入" onclick="fnImpotExcel()"/> <br> <input name="btnSubmit" type="button" value="导出excel1(直接输出excel数据)" onclick="window.open('/exce/export')"/> <input name="btnSubmit" type="button" value="导出excel2（服务器上生成excel文件，返回文件地址）" onclick="fnExportExcel('/exce/exportserver')"/> </body> </html> <script> function fnImpotExcel(){ var blob = document.getElementById('file2').files[0]; var xhr = new XMLHttpRequest(); xhr.onreadystatechange = function() { if(xhr.readyState == 4) { if(xhr.responseText) { alert(xhr.responseText); // if(slices == 0) { // alert(2) // } } } }; //构造form数据 var fd = new FormData(); fd.append("file", blob); fd.append("name", blob.name); xhr.open("POST", "/exce/import", false); //设置二进制文边界件头 //xhr.setRequestHeader("X_Requested_With", location.href.split("/")[3].replace(/[^a-z]+/g, '$')); xhr.send(fd); /* $.ajax({ url: "/exce/import", type: "post", data: {field:document.getElementById('file2').files[0]}, success: function (data) {alert(data); console.log(data); // data = decodeURIComponent(decodeURIComponent (data)).split(","); // $.each(a,function (k,v) { // $("[data-id="+a[k]+"]").append(decodeURIComponent(decodeURIComponent (data[k]))); // }) } }) */ } //导出excel function fnExportExcel(url){ $.ajax({ url: url, type: "post", success: function (data) { if(data!="") window.open("/"+data); } }) } </script> - SQL文件
DROP TABLE IF EXISTS `em`; CREATE TABLE `em` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `sex` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `createDate` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of em -- ---------------------------- INSERT INTO `em` VALUES ('3', '张西', '女', '63', '2019-11-07 23:02:28'); INSERT INTO `em` VALUES ('4', '兰六', '男', '7', '2019-11-07 23:02:28'); INSERT INTO `em` VALUES ('5', '兰六11', '男', '11', '2019-11-07 23:05:28');