使用阿里EasyExcel导出大数据量的Excel文件

添加依赖

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beat1</version>
</dependency>

最新的依赖见： https://mvnrepository.com/artifact/com.alibaba/easyexcel

工具类编写

public class EasyExcelUtil {

/**
 * Excel文件导出
 * @param response
 * @param fileName：导出Excel文件名称
 * @param sheetName：Excel的Sheet名称
 * @param headList：Excel标题名称
 * @param bodyList：Excel内容
 * @throws Exception
 */
public static void writeExcel(HttpServletResponse response, String fileName, String sheetName,
		List<List<String>> headList, List<List<Object>> bodyList) throws Exception {
	if (null == fileName) {
		throw new Exception("文件名称不能为空！");
	}
	if (null == sheetName) {
		throw new Exception("Excel的Sheet名称不能为空！");
	}
	if (null == headList) {
		throw new Exception("文件标题不能为空！");
	}

	ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
	Sheet sheet1 = new Sheet(1, 1);
	sheet1.setSheetName(sheetName);
	sheet1.setTableStyle(createDefaultTableStyle());
	sheet1.setHead(headList);
	// 设置自适应宽度
	sheet1.setAutoWidth(Boolean.TRUE);
	if (null != bodyList) {
		writer.write1(bodyList, sheet1);
	}
	writer.finish();
}

/**
 * 导出文件名称定义
 * 
 * @param fileName:文件名称
 * @param response
 *            HttpServletResponse
 * @return
 * @throws Exception
 */
public static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
	try {
		fileName = URLEncoder.encode(fileName, "UTF-8");
		response.setContentType("application/vnd.ms-excel");
		response.setCharacterEncoding("utf8");
		response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
		response.setHeader("Pragma", "public");
		response.setHeader("Cache-Control", "no-store");
		response.addHeader("Cache-Control", "max-age=0");
		return response.getOutputStream();
	} catch (IOException e) {
		throw new Exception("导出excel表格失败!", e);
	}
}

/**
 * 设置默认的导出样式
 * 
 * @return
 */
public static TableStyle createDefaultTableStyle() {
	TableStyle tableStyle = new TableStyle();
	Font headFont = new Font();
	headFont.setBold(true);
	headFont.setFontHeightInPoints((short) 12);
	headFont.setFontName("微软雅黑");
	tableStyle.setTableHeadFont(headFont);
	tableStyle.setTableHeadBackGroundColor(IndexedColors.WHITE);

	Font contentFont = new Font();
	contentFont.setBold(false);
	contentFont.setFontHeightInPoints((short) 11);
	contentFont.setFontName("微软雅黑");
	tableStyle.setTableContentFont(contentFont);
	tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
	return tableStyle;
}

}

controller代码编写

@RequestMapping(value="/exportexcel", method = RequestMethod.GET)
public void exportexcel(HttpServletRequest request,
HttpServletResponse response) throws Exception{

	EasyExcelUtil.writeExcel(response, "easy文件名称", "sheet名称",
			DataUtil.createTestListStringHead2(), 
			DataUtil.createTestListObject());
}

DataUtil 类为EasyExcel提供的测试代码

public class DataUtil {

public static List<List<Object>> createTestListObject() {
    List<List<Object>> object = new ArrayList<List<Object>>();
    for (int i = 0; i < 300000; i++) {
        List<Object> da = new ArrayList<Object>();
        da.add("字符串"+i);
        da.add(Long.valueOf(187837834l+i));
        da.add(Integer.valueOf(2233+i));
        da.add(Double.valueOf(2233.00+i));
        da.add(Float.valueOf(2233.0f+i));
        da.add(new Date());
        da.add(new BigDecimal("3434343433554545"+i));
        da.add(Short.valueOf((short)i));
        object.add(da);
    }
    return object;
}

public static List<List<String>> createTestListStringHead(){
    //写sheet3  模型上没有注解，表头数据动态传入
    List<List<String>> head = new ArrayList<List<String>>();
    List<String> headCoulumn1 = new ArrayList<String>();
    List<String> headCoulumn2 = new ArrayList<String>();
    List<String> headCoulumn3 = new ArrayList<String>();
    List<String> headCoulumn4 = new ArrayList<String>();
    List<String> headCoulumn5 = new ArrayList<String>();

    headCoulumn1.add("第一列");headCoulumn1.add("第一列");headCoulumn1.add("第一列");
    headCoulumn2.add("第一列");headCoulumn2.add("第一列");headCoulumn2.add("第一列");

    headCoulumn3.add("第二列");headCoulumn3.add("第二列");headCoulumn3.add("第二列");
    headCoulumn4.add("第三列");headCoulumn4.add("第三列2");headCoulumn4.add("第三列2");
    headCoulumn5.add("第一列");headCoulumn5.add("第3列");headCoulumn5.add("第4列");

    head.add(headCoulumn1);
    head.add(headCoulumn2);
    head.add(headCoulumn3);
    head.add(headCoulumn4);
    head.add(headCoulumn5);
    return head;
}

public static List<List<String>> createTestListStringHead2(){
    //写sheet3  模型上没有注解，表头数据动态传入
    List<List<String>> head = new ArrayList<List<String>>();
    List<String> headCoulumn1 = new ArrayList<String>();
    List<String> headCoulumn2 = new ArrayList<String>();
    List<String> headCoulumn3 = new ArrayList<String>();
    List<String> headCoulumn4 = new ArrayList<String>();
    List<String> headCoulumn5 = new ArrayList<String>();

    headCoulumn1.add("第一列1");
    headCoulumn2.add("第一列2");

    headCoulumn3.add("第二列3");
    headCoulumn4.add("第三列4");
    headCoulumn5.add("第一列5");

    head.add(headCoulumn1);
    head.add(headCoulumn2);
    head.add(headCoulumn3);
    head.add(headCoulumn4);
    head.add(headCoulumn5);
    return head;
}

public static List<WriteModel> createTestListJavaMode(){
    List<WriteModel> model1s = new ArrayList<WriteModel>();
    for (int i = 0; i <10000 ; i++) {
        WriteModel model1 = new WriteModel();
        model1.setP1("第一列，第行");
        model1.setP2("121212jjj");
        model1.setP3(33+i);
        model1.setP4(44);
        model1.setP5("555");
        model1.setP6(666.2f);
        model1.setP7(new BigDecimal("454545656343434"+i));
        model1.setP8(new Date());
        model1.setP9("llll9999>&&&&&6666^^^^");
        model1.setP10(1111.77+i);
        model1s.add(model1);
    }
    return model1s;
}

public static TableStyle createTableStyle() {
    TableStyle tableStyle = new TableStyle();
    Font headFont = new Font();
    headFont.setBold(true);
    headFont.setFontHeightInPoints((short)12);
    headFont.setFontName("微软雅黑");
    tableStyle.setTableHeadFont(headFont);
    tableStyle.setTableHeadBackGroundColor(IndexedColors.WHITE);

    Font contentFont = new Font();
    contentFont.setBold(false);
    contentFont.setFontHeightInPoints((short)11);
    contentFont.setFontName("微软雅黑");
    tableStyle.setTableContentFont(contentFont);
    tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
    return tableStyle;
}

}

导出结果

EasyExcel的git地址

https://github.com/alibaba/easyexcel

参考资料

https://www.cnblogs.com/kaile/p/10869453.html
https://blog.csdn.net/qq_35206261/article/details/82844159