- 工具类,设置好表头样式
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;
public class AliExcelUtils {
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;
}
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;
}
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;
}
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;
}
}
- 插入记录,生成excel文件
public void getAliExcel1(Context ctx, HttpServletRequest request, HttpServletResponse response) throws IOException {
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(getUseCompanyList(ctx), sheet1, table1);
writer.finish();
out.flush();
}
- 模拟数据
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;
}