EasyExcel的使用

 在项目开发中经常要实现excel的导入导出功能，本文简单讲解一下使用easyexcel实现导入导出功能，代码有不严谨的地方还请自行调整。

一、在pom.xml中主要添加如下依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.2-beta5</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.2</version>
</dependency>

二、废话不多说，直接贴代码：

1.项目结构如下：

2.pom.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.exceldemo</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>exceldemo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </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>
        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.18</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.1</version>
        </dependency>
        <!--mybatis-->

        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beta5</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.2</version>
        </dependency>
        <!--End easyexcel-->
        <!--thymeleaf  新的模板引擎，比jsp要出色-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!--jdbc-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.2</version>
            </plugin>
        </plugins>
    </build>

</project>

3. ExporExcelController

package com.exceldemo.demo.controller;

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.exceldemo.demo.pojo.Catagory;
import com.exceldemo.demo.service.DemoService;
import com.exceldemo.demo.uitl.ExcelListener;
import com.exceldemo.demo.uitl.ExcelUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author ：qds
 * @date ：Created in 2019/10/29 21:34
 * @description：excel导入导出
 * @modified By：
 * @version: 1.0$
 */
@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;
    }
}

4. DemoDAO

package com.exceldemo.demo.mapper;

import com.exceldemo.demo.pojo.Catagory;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import java.util.List;

@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);
}

5. Category

package com.exceldemo.demo.pojo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.Date;

/**
 * @author ：qds
 * @date ：Created in 2019/10/29 22:05
 * @description：
 * @modified By：
 * @version: 1.0$
 */
@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;

}

6. DemoService

package com.exceldemo.demo.service;

import com.exceldemo.demo.pojo.Catagory;

import java.util.List;

public interface DemoService {

    //excel查询所有
    public List<Catagory> findAllToExcel() throws Exception;
    //添加
    public int addForExcel(Catagory model);
}

7. DemoServiceImpl

package com.exceldemo.demo.service.impl;

import com.exceldemo.demo.mapper.DemoDAO;
//import com.exceldemo.demo.mapper.EmMapper;
import com.exceldemo.demo.pojo.Catagory;
import com.exceldemo.demo.service.DemoService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author ：qds
 * @date ：Created in 2019/11/6 23:08
 * @description：
 * @modified By：
 * @version: $
 */
@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;
    }
}

8. ExcelListener

package com.exceldemo.demo.uitl;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

/**
 * @author ：qds
 * @date ：Created in 2019/10/29 22:01
 * @description：excel操作
 * @modified By：
 * @version: 1.0$
 */
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();//解析结束销毁不用的资源
    }
}

9. ExcelUtil

package com.exceldemo.demo.uitl;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @author ：qds
 * @date ：Created in 2019/10/30 21:08
 * @description：操作excel
 * @modified By：
 * @version: 1.0$
 */
@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文件导出失败, 失败原因：{}");
            }
        }

    }

}

10. JsonDateSerializer

package com.exceldemo.demo.uitl;

import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonSerializer;
import com.fasterxml.jackson.databind.SerializerProvider;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

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);
    }
}

11. MultipleSheelPropety

package com.exceldemo.demo.uitl;

import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import lombok.Data;

import java.util.List;

/**
 * @author ：qds
 * @date ：Created in 2019/10/30 22:27
 * @description：excel用
 * @modified By：
 * @version: 1.0$
 */
@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;
    }
}

12. 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>

13.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>

谷歌浏览器中显示：

14.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');

好了，效果自行测试吧。

扫二维码关注枯客集合页，也可访问：http://www.kkjhy.com