alibaba-easyexcel使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
/**============================================================
* 修改记录：
* 日期
作者
内容
* =============================================================
* 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;
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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();
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
	/**
* 用车企业查询统计
*
* @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;
}