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;
}

最后

以上就是靓丽帅哥为你收集整理的alibaba-easyexcel使用的全部内容，希望文章能够帮你解决alibaba-easyexcel使用所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。