EasyExcel导出数据到多个sheet并实现页面下载

第一、Java解析Excel工具EasyExcel
Java 程序员在项目上一般会经常遇到解析数据、生成Excel的需求，比较流行的就是Apache poi框架了，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。esayExcel在大数据量的时候是一行一行的解析，不同于POI的一次性解析，这样避免了内存的溢出。
第二、EasyExcel 解决了什么
1.传统 Excel 框架，如 Apache poi、jxl 都存在内存溢出的问题；
2.传统 excel 开源框架使用复杂、繁琐;
3.EasyExcel 底层还是使用的 poi, 但是做了很多优化，比如修复了并发情况下的一些 bug。
第三、EasyExcel导出数据到多个sheet
1.添加依赖

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.0.5</version>
</dependency>

2.学生实体类

import com.alibaba.excel.annotation.ExcelProperty;
/**
 * 学生实体类
 * @author shixiangcheng
 * 2020-01-17
 */
public class Student {
	@ExcelProperty({"学号"})
	private String no;
	@ExcelProperty({"姓名"})
	private String name;
	@ExcelProperty({"生日"})
	private String birthday;
	public Student(String no, String name, String birthday) {
		super();
		this.no = no;
		this.name = name;
		this.birthday = birthday;
	}
	public String getNo() {
		return no;
	}
	public void setNo(String no) {
		this.no = no;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getBirthday() {
		return birthday;
	}
	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}
}

3.测试类

import java.util.ArrayList;
import java.util.List;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
/**
 * 测试类
 * @author shixiangcheng
 * 2020-01-17
 */
public class Test {
	public static void main(String [] args){
		String fileName="D:/报表.xlsx";
		ExcelWriter excelWriter = EasyExcel.write(fileName).build();
    	List<Student> studentList=new ArrayList<Student>();
    	Student student=new Student("1","张三","2000-01-01");
    	studentList.add(student);
    	 //这里 需要指定写用哪个class去写
    	WriteSheet writeSheet = EasyExcel.writerSheet(0, "学生信息1").head(Student.class).build();
    	excelWriter.write(studentList, writeSheet);
    	writeSheet = EasyExcel.writerSheet(1, "学生信息2").head(Student.class).build();
    	excelWriter.write(studentList, writeSheet);
        //千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
	}
}

4.运行结果

5.从页面导出，编写controller代码

import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
/**
 * 导出报表
 * @author shixiangcheng
 * 2020-01-17
 */
@Controller
public class ExportExcelController {
	@RequestMapping("/exportExcel")
	public void exportExcel(HttpServletResponse response) throws Exception{
		String fileName="报表";
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
		ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
    	List<Student> studentList=new ArrayList<Student>();
    	Student student=new Student("1","张三","2000-01-01");
    	studentList.add(student);
    	//这里 需要指定写用哪个class去写
    	WriteSheet writeSheet = EasyExcel.writerSheet(0, "学生信息1").head(Student.class).build();
    	excelWriter.write(studentList, writeSheet);
    	writeSheet = EasyExcel.writerSheet(1, "学生信息2").head(Student.class).build();
    	excelWriter.write(studentList, writeSheet);
        //千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
	}
}

6.启动服务，从浏览器访问，验证下载的报表内容

欢迎大家积极留言交流学习心得，点赞的人最美丽，谢谢