alibaba-easyexcel使用

1. 工具类，设置好表头样式

/**============================================================
 * 修改记录：
 * 日期                作者           内容
 * =============================================================
 * 2019年8月9日       AINY
 * ============================================================*/

package com.ainy.gofsix.common.utils;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.apache.poi.ss.usermodel.IndexedColors;

import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.TableStyle;
import com.ainy.gofsix.common.constant.ExcelConstants;

/**
 * <p>
 * TODO Alibaba EasyExcel导出Excel工具类
 * </p>
 *
 * <p>
 * Copyright: 版权所有 (c) 2002 - 2008<br>
 * </p>
 *
 * @author AINY
 * @version 2019年8月9日
 */

public class AliExcelUtils {

	/**
	 * 表格样式 TODO
	 * 
	 * @param
	 * @return TableStyle
	 */
	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.GREY_40_PERCENT);
		// 设置表格主体样式
		Font contentFont = new Font();
		contentFont.setBold(true);
		contentFont.setFontHeightInPoints((short) 12);
		contentFont.setFontName("黑体");
		tableStyle.setTableContentFont(contentFont);
		tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);

		return tableStyle;
	}

	/**
	 * 用车企业查询统计表头设计
	 * 
	 * @param
	 * @return List<List<String>>
	 */
	public static List<List<String>> createUseVehicleCompanyHead() {
		// 模型上没有注解，表头数据动态传入
		List<List<String>> head = new ArrayList<List<String>>();
		List<String> headCoulumn1 = new ArrayList<String>();

		head.add(getNormalHeadCoulumn(headCoulumn1, ExcelConstants.EXCEL_HEAD_USECOMPANY));
		List<String> headCoulumn01;
		List<String> headCoulumn02;
		List<String> headCoulumn03;
		for (int i = 1; i < 13; i++) {
			headCoulumn01 = new ArrayList<String>();
			headCoulumn02 = new ArrayList<String>();
			headCoulumn03 = new ArrayList<String>();
			headCoulumn01.add(i + "月");
			headCoulumn01.add(ExcelConstants.EXCEL_HEAD_USECOUNT);
			headCoulumn01.add(ExcelConstants.EXCEL_HEAD_USECOUNT);

			headCoulumn02.add(i + "月");
			headCoulumn02.add(ExcelConstants.EXCEL_HEAD_MONEY);
			headCoulumn02.add(ExcelConstants.EXCEL_HEAD_MONEY);

			headCoulumn03.add(i + "月");
			headCoulumn03.add(ExcelConstants.EXCEL_HEAD_OVERMONEY);
			headCoulumn03.add(ExcelConstants.EXCEL_HEAD_OVERMONEY);
			head.add(headCoulumn01);
			head.add(headCoulumn02);
			head.add(headCoulumn03);
		}
		head.add(getNormalHeadCoulumn(headCoulumn1, ExcelConstants.EXCEL_HEAD_TOTALUSECOUNT));
		head.add(getNormalHeadCoulumn(headCoulumn1, ExcelConstants.EXCEL_HEAD_MONEY));
		return head;
	}

	/**
	 * 出租车公司查询统计表头设计
	 * 
	 * @param
	 * @return List<List<String>>
	 */
	public static List<List<String>> createRentVehicleCompanyHead(Date startDate, Date endDate) {
		long daysBetween = (endDate.getTime() - startDate.getTime() + 1000000) / (60 * 60 * 24 * 1000) + 1;
		Calendar calendar = new GregorianCalendar();
		calendar.setTime(startDate);
		calendar.add(calendar.DATE, 1); // 把日期往后增加一天,整数 往后推,负数往前移动
		startDate = calendar.getTime(); // 这个时间就是日期往后推一天的结果
		// 模型上没有注解，表头数据动态传入
		List<List<String>> head = new ArrayList<List<String>>();
		List<String> headCoulumn1 = new ArrayList<String>();
		head.add(getNormalHeadCoulumn(headCoulumn1, ExcelConstants.EXCEL_HEAD_RENTCOMPANY));
		List<String> headCoulumn01;
		List<String> headCoulumn02;
		for (int i = 0; i < daysBetween; i++) {
			headCoulumn01 = new ArrayList<String>();
			headCoulumn02 = new ArrayList<String>();
			calendar.add(calendar.DATE, i);

			headCoulumn01.add(DateUtils.dateTime(calendar.getTime()));
			headCoulumn01.add(ExcelConstants.EXCEL_HEAD_USECOUNT);
			headCoulumn01.add(ExcelConstants.EXCEL_HEAD_USECOUNT);

			headCoulumn02.add(DateUtils.dateTime(calendar.getTime()));
			headCoulumn02.add(ExcelConstants.EXCEL_HEAD_MONEY);
			headCoulumn02.add(ExcelConstants.EXCEL_HEAD_MONEY);
			head.add(headCoulumn01);
			head.add(headCoulumn02);
		}
		head.add(getNormalHeadCoulumn(headCoulumn1, ExcelConstants.EXCEL_HEAD_TOTAL_VEHICLE_NUM));
		head.add(getNormalHeadCoulumn(headCoulumn1, ExcelConstants.EXCEL_HEAD_TOTAL_INCOME));
		return head;
	}

	/**
	 * 设置普通表头
	 * 
	 * @param
	 * @return List<String>
	 */
	private static List<String> getNormalHeadCoulumn(List<String> headCoulumn1, String excelHeadCompany) {
		headCoulumn1 = new ArrayList<>();
		for (int i = 0; i < 3; i++) {
			headCoulumn1.add(excelHeadCompany);
		}
		return headCoulumn1;

	}

}

2. 插入记录，生成excel文件

public void getAliExcel1(Context ctx, HttpServletRequest request, HttpServletResponse response) throws IOException {
		/// String fileName = new String(("用车企业查询统计 " + new
		/// SimpleDateFormat("yyyy-MM-dd").format(new Date()))
		/// .getBytes(), "UTF-8");
		String fileName = new String(("出租车公司查询统计 ").getBytes("UTF-8"), "ISO8859-1");
		ServletOutputStream out = response.getOutputStream();
		response.setContentType("multipart/form-data");
		response.setCharacterEncoding("utf-8");
		response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
		ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);

		Table table1 = new Table(1);
		table1.setTableStyle(AliExcelUtils.createTableStyle());
		// 根据参数判断是用车企业查询统计还是出租车公司查询统计
		table1.setHead(AliExcelUtils.createRentVehicleCompanyHead(null, null));
		Sheet sheet1 = new Sheet(1, 0);
		sheet1.setSheetName("第一个sheet");
		/// writer.write1(rows, sheet1,table1);
		writer.write1(getUseCompanyList(ctx), sheet1, table1);

		writer.finish();
		out.flush();

	}

3. 模拟数据

	/**
	 * 用车企业查询统计
	 * 
	 * @param
	 * @return List<List<Object>>
	 */
	private List<List<Object>> getUseCompanyList(Context ctx) {
		List<List<Object>> rows = new ArrayList<>();
		for (int i = 0; i < 100; i++) {
			List<Object> row = new ArrayList<>();
			row.add("String" + i);
			row.add(Long.valueOf(56456456L + i));
			rows.add(row);
		}
		return rows;
	}