导出Excel功能的简单封装
表格实体类:
/**
* @Description:Excel表格实体
* @Author:wengjunhe
* @Date: 2019/03/26
*/
public class ExcelCellBean {
/**
* 表头
*/
private String title;
/**
* 对应字段
*/
private String field;
public String getTitle() {
return title;
}
public String getField() {
return field;
}
public void setTitle(String title) {
this.title = title;
}
public void setField(String field) {
this.field = field;
}
}
工具类:
**
* @Description:Excel导出工具类
* @Author:wengjunhe
* @Date: 2019/03/26
*/
public class ExcelUtils {
public static HSSFWorkbook get(List<ExcelCellBean> excelCellBeanList, List<Map<String, Object>> resultList) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow header = sheet.createRow(0);
//时间格式
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("yyyy年MM月dd日 HH:mm:ss"));
List<String> titles = getTitles(excelCellBeanList);
//设置表头
for (int i = 0; i < titles.size(); i++) {
HSSFCell cell = header.createCell(i);
cell.setCellValue(titles.get(i));
}
//设置行数据
for (int i = 0; i < resultList.size(); i++) {
HSSFRow row = sheet.createRow(i + 1);
Map<String, Object> map = resultList.get(i);
for (int j = 0; j < excelCellBeanList.size(); j++) {
HSSFCell cell = row.createCell(j);
ExcelCellBean excelCellBean = excelCellBeanList.get(j);
Object cellValue = map.get(excelCellBean.getField());
if (null == cellValue) {
cell.setCellValue("无");
} else if (cellValue instanceof Short) {
cell.setCellValue(Short.parseShort(String.valueOf(cellValue)));
} else if (cellValue instanceof Byte) {
cell.setCellValue(Byte.parseByte(String.valueOf(cellValue)));
} else if (cellValue instanceof Integer) {
cell.setCellValue(Integer.parseInt(String.valueOf(cellValue)));
} else if (cellValue instanceof Long) {
cell.setCellValue(Long.parseLong(String.valueOf(cellValue)));
} else if (cellValue instanceof Float) {
cell.setCellValue(Float.parseFloat(String.valueOf(cellValue)));
} else if (cellValue instanceof Double) {
cell.setCellValue(Double.parseDouble(String.valueOf(cellValue)));
} else if (cellValue instanceof BigDecimal) {
cell.setCellValue(Double.parseDouble(String.valueOf(cellValue)));
} else if (cellValue instanceof String) {
cell.setCellValue(String.valueOf(cellValue));
} else if (cellValue instanceof Timestamp) {
cell.setCellStyle(cellStyle);
cell.setCellValue(String.valueOf(cellValue));
} else {
cell.setCellValue(String.valueOf(cellValue));
}
}
}
return workbook;
}
/**
* @Description: 导出Excel
* @Param: [headers, fields, resultList]
* @return: org.apache.poi.hssf.usermodel.HSSFWorkbook
*/
public static HSSFWorkbook get(String[] headers, String[] fields, List<Map<String, Object>> resultList) {
List<ExcelCellBean> excelCellBeans = new ArrayList<>();
for (int i = 0; i < headers.length; i++) {
ExcelCellBean excelCellBean = new ExcelCellBean();
excelCellBean.setTitle(headers[i]);
excelCellBean.setField(fields[i]);
excelCellBeans.add(excelCellBean);
}
return get(excelCellBeans, resultList);
}
/**
* @Description: 获取表头
* @Param: [excelCellBeanList]
* @return: java.util.List<java.lang.String>
*/
public static List<String> getTitles(List<ExcelCellBean> excelCellBeanList) {
ArrayList<String> titles = new ArrayList<>();
for (ExcelCellBean excelCellBean : excelCellBeanList) {
titles.add(excelCellBean.getTitle());
}
return titles;
}
}
调用:
private HSSFWorkbook getHSSFWorkbook(List<Map<String, Object>> productList) {
String[] headers = {"商品ID","商品名称", "商家名称", "一级类目","二级类目","是否共享",
"累计销量", "最高日销量", "最低日销量", "当日销量", "价格", "佣金",
"近七日销量", "价格变动区间", "当日均价", "近七天均价", "历史均价",
"创建日期", "更新日期", "监控版本"};
//field
String[] fields = {"productId","productName", "sellerName", "channel","cateName","isShare",
"solds", "topSolds", "lowSolds", "daySolds", "price", "commissionAmount",
"weekAverageSolds", "priceSection", "dayAveragePrice", "sevenAveragePrice", "historyAveragePrice",
"createTime", "updateTime", "monitorVersion"};
HSSFWorkbook workbook = ExcelUtils.get(headers, fields, productList);
return workbook;
}
结果