今天逛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();
}
扫一扫
3078
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
