pom文件引入maven依赖实现低代码量导出excel

2023-11-26 08:55:22

        今天逛CSDN，发现有一位大神将他写的一个Excel工具包开源了，之前我也有写过一篇excel的导出文章，与这相比代码量实在是太多太冗余了。赶紧下下来实现一番。

感谢大神的贡献：公司的报表工具太难用，我三天撸了个Excel工具，运营小姐姐直呼太好用了，现已开源！！（建议收藏）_冰河的专栏-CSDN博客

下载完源码后，导入idea中编译i使用maven工具install，在本地maven仓库生成对应maven依赖包，用来给项目引入。

导出Excel到本地：

        普通方式和基于注解方式都需要引入以下的maven依赖

<!--        普通方式导出excel-->
        <dependency>
            <groupId>io.mykit.excel</groupId>
            <artifactId>mykit-excel-common</artifactId>
            <version>1.0.0-SNAPSHOT</version>
        </dependency>

        创建一个实体类Student用于普通导出测试,Person、Teacher用于基于注解导出测试:

package com.example.demo.utils;


import lombok.Data;

/**
 * @author zhangqianwei
 * @date 2021/9/7 17:25
 */
@Data
public class Student {
    private int id;
    private String name;
    private int age;
    private String sex;

    public Student() {
    }

    public Student(int id, String name, int age, String sex) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
    }
    
}
package com.example.demo.utils;

import io.mykit.excel.annotation.ExcelColumn;
import lombok.Data;

/**
 * @author zhangqianwei
 * @date 2021/9/11 13:42
 */
@Data
public class Person {
    @ExcelColumn(isExport = true,title = "编号",sort = 1)
    private int id;
    @ExcelColumn(isExport = true,title = "姓名",sort = 2)
    private String name;
    @ExcelColumn(isExport = true,title = "年龄",sort = 4)
    private int age;

    public Person(int id, String name,int age){
        this.id = id;
        this.name = name;
        this.age = age;
    }
}

package com.example.demo.utils;


import lombok.Data;

/**
 * @author zhangqianwei
 * @date 2021/9/7 17:25
 */
@Data
public class Student {
    private int id;
    private String name;
    private int age;
    private String sex;

    public Student() {
    }

    public Student(int id, String name, int age, String sex) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
    }

}

创建测试类excel测试这两种方法:

package com.example.demo.utils;

import io.mykit.excel.utils.excel.AnnotationExcelExportUtils;
import io.mykit.excel.utils.excel.BaseExcelExportUtils;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zhangqianwei
 * @date 2021/9/11 10:42
 */
public class excel {
    public static void main(String[] args) throws Exception{
        //普通方式导出Excel
        normalExcel();
        //注解方式导出Excel
        ExcelByZJ();
    }

    /**
     * 普通方式导出Excel
     * @throws FileNotFoundException
     */
    public static void normalExcel() throws FileNotFoundException {
        //添加测试数据
        List<Student> list = new ArrayList<Student>();
        list.add(new Student(1,"张三",18,"男"));
        list.add(new Student(2,"李四",17,"男"));
        list.add(new Student(3,"王五",18,"女"));
        //使用Excel导出工具初始化JavaBean
        BaseExcelExportUtils<Student> utils = new BaseExcelExportUtils<Student>();
        //指定导出的Excel每一列的名称
        String[] columnNames = { "ID", "姓名","年龄","性别" };
        utils.exportExcel("用户导出", columnNames, list, new FileOutputStream("E:/普通导出.xls"), BaseExcelExportUtils.EXCEL_FILE_2003);
    }

    /**
     * 使用注解方式导出Excel
     * @throws FileNotFoundException
     */
    public static void ExcelByZJ() throws FileNotFoundException {
        //添加测试数据
        List<Teacher> list = new ArrayList<Teacher>();
        list.add(new Teacher(1,"张三",6,"幼儿园","大班"));
        list.add(new Teacher(2,"李四",10,"小学","数学"));
        list.add(new Teacher(3,"王五",14,"初中","语文"));
        //使用Excel导出工具初始化JavaBean
        AnnotationExcelExportUtils<Teacher> utils = new AnnotationExcelExportUtils<Teacher>();
        utils.exportExcel("用户导出", list, new FileOutputStream("E:/注解导出.xls"), Teacher.class, AnnotationExcelExportUtils.EXCEL_FILE_2003);
    }

}

Web方式下载Excel：

        普通下载与基于注解下载都需要引入以下maven依赖：

        <!--		添加本地依赖允许Web方式导出Excel-->
        <dependency>
            <groupId>io.mykit.excel</groupId>
            <artifactId>mykit-excel-servlet</artifactId>
            <version>1.0.0-SNAPSHOT</version>
        </dependency>

创建接口类TestController测试调用：

package com.example.webdemo.Controller;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.example.webdemo.Entity.Student;
import com.example.webdemo.Entity.Teacher;
import com.example.webdemo.Entity.User;
import io.mykit.excel.servlet.ExportExcelWrapper;
import io.mykit.excel.utils.excel.BaseExcelExportUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zhangqianwei
 * @date 2021/9/11 15:05
 */
@RequestMapping("/test")
@Controller
public class TestController {

    /**
     * Web方式普通导出Excel
     *
     * @param request
     * @param response
     * @throws Exception
     */
    @RequestMapping(value = "/getExcel")
    public static void ExcelByWeb(HttpServletRequest request, HttpServletResponse response) throws Exception {
        //添加测试数据
        List<Student> list = new ArrayList<Student>();
        list.add(new Student(1, "张三", 18, "男"));
        list.add(new Student(2, "李四", 17, "男"));
        list.add(new Student(3, "王五", 18, "女"));

        String[] columnNames = {"ID", "姓名", "年龄", "性别"};
        String fileName = "Web方式的普通Excel导出";
        ExportExcelWrapper<Student> util = new ExportExcelWrapper<Student>();
        util.exportExcel(fileName, fileName, columnNames, list, response, BaseExcelExportUtils.EXCEL_FILE_2003);
    }

    /**
     * 基于注解的Web方式导出Excel
     *
     * @param request
     * @param response
     * @throws Exception
     */
    @RequestMapping(value = "/getExcelByZJ")
    public static void ExcelByWebAndZJ(HttpServletRequest request, HttpServletResponse response) throws Exception {
        //添加测试数据
        List<Teacher> list = new ArrayList<Teacher>();
        list.add(new Teacher(1, "张三", 6, "幼儿园", "大班"));
        list.add(new Teacher(2, "李四", 10, "小学", "数学"));
        list.add(new Teacher(3, "王五", 14, "初中", "语文"));

        String fileName = "Web方式基于注解的Excel导出";
        ExportExcelWrapper<Teacher> wrapper = new ExportExcelWrapper<Teacher>();
        wrapper.annotationExportExcel(fileName, fileName, list, Teacher.class, response, ExportExcelWrapper.EXCEL_FILE_2003);
    }

   

}

其中有一个接口使用了阿里巴巴的easyExcel开源工具包也可以大大的简化代码量;

easyExcel需要引入的maven依赖：

        <!--        引入阿里巴巴的easyExcel导出Excel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beta5</version>
        </dependency>
        <dependency>

创建一个JavaBean User用作easyExcel测试使用：

package com.example.webdemo.Entity;

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

/**
 * @author zhangqianwei
 * @date 2021/9/11 15:58
 */
//使用easyExcel JavaBean使用相对应的注解需要继承BaseRowModel类
@Data
public class User extends BaseRowModel {
    @ExcelProperty(value = {"主键ID"}, index = 0)
    private int id;
    @ExcelProperty(value = {"用户姓名"}, index = 1)
    private String name;
    @ExcelProperty(value = {"用户年龄"}, index = 2)
    private int age;

    public User(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }
}

        在刚刚的测试接口类TestController中测试如下接口即可实现使用easyExcel导出Excel：

 /**
     * 使用easyExcel导出Excel
     * @param response
     * @throws IOException
     */
    @RequestMapping("/exportExcel")
    public void exportByEasyExcel(HttpServletResponse response) throws IOException {
        //添加测试数据
        List<User> list = new ArrayList<User>();
        list.add(new User(1, "张三", 18));
        list.add(new User(2, "李四", 17));
        list.add(new User(3, "王五", 18));

        ServletOutputStream out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
        String fileName = "测试exportExcel";
        Sheet sheet = new Sheet(1, 0,User.class);
        //设置自适应宽度
        sheet.setAutoWidth(Boolean.TRUE);
        // 第一个 sheet 名称
        sheet.setSheetName("第一个sheet");
        writer.write(list, sheet);
        //通知浏览器以附件的形式下载处理，设置返回头要注意文件名有中文
        response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xlsx");
        writer.finish();
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        out.flush();
    }

上一篇：maven项目建立pom.xml报无法解析org.apache.maven.plugins:maven-resources-plugin


下一篇：python-如何逃避实际命名的BeautifulSoup ISO标记中的父属性？