easyExcel文件上传与下载

最新推荐文章于 2024-05-22 11:01:27 发布

a806451906

最新推荐文章于 2024-05-22 11:01:27 发布

阅读量964

点赞数

本文链接：https://blog.csdn.net/a806451906/article/details/128376549

版权

1.导入POM依赖

        <!-- commons-io 文件流依赖-->
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.7</version>
        </dependency>
        <!-- commons-fileupload -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.4</version>
        </dependency>

       <!-- easyexcel依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

2.模板文件

上面是导出模板，如果导入得话工资下面得要换成{.salary}，否则报错，也可以设置实体类为字符串然后截取工资部分。

3.实体类

package com.ahead.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.Date;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.*;

/**
 * <p>
 *
 * 创建employee类,用于构建向Excel表格中写数据的类型;
 * @ExcelProperty:这个注解是EasyExcel提供,用于生成Excel表格头
 * @author 荣
 * @since 2022-12-18
 */

@ContentRowHeight(30) //设置内容高度
@HeadRowHeight(50)    //设置标题高度
@ColumnWidth(25)      //设置列宽
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("employee")
@ApiModel(value = "Employee对象", description = "")
public class Employee implements Serializable {

    private static final long serialVersionUID = 1L;

    // 字符串的内容的背景设置成天蓝(可以改后面数字)
    @ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
    @ExcelProperty("用户编号")
    @ApiModelProperty("用户编号")
    @TableId(value = "userId", type = IdType.AUTO)
    private Integer userId;

    @ExcelProperty("姓名")
    @ApiModelProperty("姓名")
    @TableField("userName")
    private String userName;

    @ExcelProperty("性别")
    @ApiModelProperty("性别")
    @TableField("gender")
    private String gender;

//    设置浮点数导出格式
    @NumberFormat("#.##")
    @ExcelProperty("工资")
    @ApiModelProperty("工资")
    @TableField("salary")
    private Double salary;


//    设置日期导出格式
    @DateTimeFormat("yyyy年MM月dd日 HH时mm分ss秒")
    @ExcelProperty("入职日期")
    @ApiModelProperty("入职日期")
    @TableField("hireDate")
    private Date hireDate;


}

4.前端页面

1.文件上传(此处用得是layui得上传页面)

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="UTF-8">
    <title>zyupload上传实例</title>
    <link rel="stylesheet" th:href="@{/layuimini-v2/lib/layui-v2.6.3/css/layui.css}" media="all">
    <link rel="stylesheet" th:href="@{/layuimini-v2/lib/jq-module/zyupload/zyupload-1.0.0.min.css}" media="all">
    <link rel="stylesheet" th:href="@{/layuimini-v2/css/public.css}" media="all">
</head>
<body>
<!--<div class="layuimini-container">-->
<div class="layuimini-main">


        <div class="layui-upload-drag" id="test10">
            <i class="layui-icon"></i>
            <p>点击上传，或将文件拖拽到此处</p>
            <div class="layui-hide" id="uploadDemoView">
                <hr>
                <img src="" alt="上传成功后渲染" style="max-width: 196px">
            </div>
        </div>

</div>

    <div>
    <form th:action="@{/ahead/employee/fileDownload}" method="get">
        <input type="submit" value="文件下载">
    </form>

</div>

<script th:src="@{/layuimini-v2/lib/jquery-3.4.1/jquery-3.4.1.min.js}" charset="utf-8"></script>
<script th:src="@{/layuimini-v2/lib/layui-v2.6.3/layui.js}" charset="utf-8"></script>
<script th:src="@{/layuimini-v2/lib/jq-module/zyupload/zyupload-1.0.0.min.js}" charset="utf-8"></script>
<script type="text/javascript">

    layui.use(['upload', 'element', 'layer'], function(){
        var $ = layui.jquery
            ,upload = layui.upload
            ,element = layui.element
            ,layer = layui.layer;


        //拖拽上传
        upload.render({
            elem: '#test10'
            ,url: '../ahead/employee/fileUpload' //此处用的是第三方的 http 请求演示，实际使用时改成您自己的上传接口即可。
            ,accept: 'file' //普通文件
            ,done: function(res){
                layer.msg('上传成功');
                console.log(res);
            }
        });

</script>

</body>
</html>

5.模板文件上传(Controller)

    @RequestMapping("/fileUpload")
    @ResponseBody
    public DataFormat fileUpload(MultipartFile file) throws FileUploadException, IOException {

        InputStream inputStream = file.getInputStream();

        ArrayList<Employee> list = new ArrayList<>();
        DataFormat dataFormat = new DataFormat();
        EasyExcel.read(inputStream, Employee.class, new ReadListener<Employee>() {

            @Override
            public void invoke(Employee employee, AnalysisContext analysisContext) {
                Employee employee1 = new Employee();
//可以根据字段唯一性先查询下数据库字段是否重复
                employee1.setUserName(employee.getUserName());
                employee1.setGender(employee.getGender());
                employee1.setSalary(employee.getSalary());
                employee1.setHireDate(employee.getHireDate());
                list.add(employee1);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                boolean b = employeeService.saveBatch(list);
                if (b == true){
                    dataFormat.setMsg("上传成功！");
                }else {
                    dataFormat.setMsg("上传失败！");
                }
            }
        }).sheet().headRowNumber(3).doRead();

        return dataFormat;
}

6.文件下载(Controller)

    @RequestMapping("/fileDownload")
    @ResponseBody
    public void fileDownload(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {

//            设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            //获取模板文件的文件流
            InputStream inputStream = getClass()
                .getClassLoader().getResourceAsStream("Template/userTemplate2.xlsx");

            //查询数据
            List<Employee> list = employeeService.list(null);

            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), Employee.class)
                    .withTemplate(inputStream)
                    .autoCloseStream(Boolean.FALSE)
                    .sheet(0)
                    .doFill(list);
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = MapUtils.newHashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }


    }