import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.map.LinkedMap;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.util.StringUtils;
import tv.zhongchi.common.enums.*;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* @ClassName ExcelExportUtil
* @Author ZhangRF
* @CreateDate 2021/03/05
* @Decription Excel导出工具
*/
@Slf4j
public class ExcelExportUtil {
/**
* Excel表格导出
*
* @param response HttpServletResponse对象
* @param excelData Excel表格的数据，封装为List<List<String>>
* @param sheetName sheet的名字
* @param fileName 导出Excel的文件名
* @param columnWidth Excel表格的宽度，建议为15
* @throws IOException 抛IO异常
*/
public static void exportExcel(HttpServletResponse response,
List<List<String>> excelData,
String sheetName,
String fileName,
int columnWidth) throws IOException {
XSSFWorkbook workbook = exportExcel(excelData, sheetName, columnWidth);
//准备将Excel的输出流通过response输出到页面下载
//八进制输出流
response.setContentType("application/octet-stream");
//设置导出Excel的名称
response.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
//刷新缓冲
response.flushBuffer();
//workbook将Excel写入到response的输出流中，供页面下载该Excel文件
workbook.write(response.getOutputStream());
//关闭workbook
workbook.close();
}
/**
* 创建Excel表格
*
* @param excelData
* @param sheetName
* @param columnWidth
* @return
*/
public static XSSFWorkbook exportExcel(List<List<String>> excelData,
String sheetName,
int columnWidth) {
//声明一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//生成一个表格，设置表格名称
XSSFSheet sheet = workbook.createSheet(sheetName);
XSSFCellStyle cellStyle = workbook.createCellStyle();
if (columnWidth > 0) {
//设置表格列宽度
sheet.setDefaultColumnWidth(columnWidth);
}
//Chris设置单元格自动换行
cellStyle.setWrapText(true);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//写入List<List<String>>中的数据
int rowIndex = 0;
for (List<String> data : excelData) {
//创建一个row行，然后自增1
XSSFRow row = sheet.createRow(rowIndex++);
//遍历添加本行数据
for (int i = 0; i < data.size(); i++) {
//创建一个单元格
XSSFCell cell = row.createCell(i);
//Chirs设置单元格样式
cell.setCellStyle(cellStyle);
//创建一个内容对象
XSSFRichTextString text = new XSSFRichTextString(data.get(i));
//将内容对象的文字内容写入到单元格中
cell.setCellValue(text);
}
}
if (columnWidth == 0) {
//列宽自适应
autoColumnWidth(sheet);
}
return workbook;
}
/**
* Excel表格写入磁盘
*
* @param path 磁盘路径
* @param excelData
* @param sheetName
* @param fileName
* @param columnWidth
*/
public static void exportPathExcel(String path,
List<List<String>> excelData,
String sheetName,
String fileName,
int columnWidth) {
try {
XSSFWorkbook workbook = exportExcel(excelData, sheetName, columnWidth);
//判断是否存在目录. 不存在则创建
FileUtil.isPathExist(path);
//输出Excel文件1
FileOutputStream output = new FileOutputStream(path + fileName);
workbook.write(output);//写入磁盘
output.close();
} catch (Exception e) {
log.error("===报表写入磁盘失败===" + fileName);
}
}
/**
* Excel表格导出
*
* @param response HttpServletResponse对象
* @param list 导出的数据集
* @param sheetName sheet的名字
* @param fileName 导出Excel的文件名
* @param clazz 实体,按照传入的实体字段顺序导出
* @param <T>
*/
public static <T> void exportExcel(HttpServletResponse response,
List<?> list,
String sheetName,
String fileName,
Class<T> clazz) throws IOException {
List<List<String>> excelData = new ArrayList<>();
List<String> head = exportHeadList(clazz);
excelData.add(head);
//导出数据集合
excelData.addAll(exportDataList(list, clazz));
exportExcel(response, excelData, sheetName, fileName, 15);
}
/**
* 报表写入磁盘
*
* @param path 磁盘路径
* @param list
* @param sheetName
* @param fileName
* @param clazz
* @param <T>
* @throws IOException
*/
public static <T> void exportPathExcel(String path,
List<?> list,
String sheetName,
String fileName,
Class<T> clazz) throws IOException {
List<List<String>> excelData = new ArrayList<>();
List<String> head = exportHeadList(clazz);
excelData.add(head);
//导出数据集合
excelData.addAll(exportDataList(list, clazz));
exportPathExcel(path, excelData, sheetName, fileName, 15);
}
/**
* Excel表格导出
*
* @param response HttpServletResponse对象
* @param list 导出的数据集
* @param sheetName sheet的名字
* @param fileName 导出Excel的文件名
* @param clazz 实体,按照传入的实体字段顺序导出
* @param map key实体字段参数，value表头值
* @param <T>
*/
public static <T> void exportExcel(HttpServletResponse response,
List<?> list,
String sheetName,
String fileName,
Class<T> clazz,
LinkedMap<Object, Object> map) throws IOException {
List<List<String>> excelData = new ArrayList<>();
List<String> head = exportHeadTwoList(clazz, map);
excelData.add(head);
//导出数据集合
excelData.addAll(exportDataList(list, clazz, map));
exportExcel(response, excelData, sheetName, fileName, 15);
}
/**
* Excel表格导出
*
* @param response HttpServletResponse对象
* @param list 导出的数据集
* @param sheetName sheet的名字
* @param fileName 导出Excel的文件名
* @param clazz 实体,按照传入的实体字段顺序导出
* @param map key实体字段参数，value表头值
* @param payTypeMoneyMap key实体字段参数，value表数值，报表最后一行数据
* @param <T>
*/
public static <T> void exportExcel(HttpServletResponse response,
List<?> list,
String sheetName,
String fileName,
Class<T> clazz,
LinkedMap<Object, Object> map,
Map<Object, Object> payTypeMoneyMap) throws IOException {
List<List<String>> excelData = new ArrayList<>();
List<String> head = exportHeadTwoList(clazz, map);
excelData.add(head);
//导出数据集合
excelData.addAll(exportDataList(list, clazz, map, payTypeMoneyMap));
exportExcel(response, excelData, sheetName, fileName, 15);
}
/**
* 获取表头集合
*
* @param clazz 实体,按照传入的实体字段顺序导出
* @param <T>
* @return
*/
public static <T> List<String> exportHeadList(Class<T> clazz) {
List<String> headList = new ArrayList<>();
Field[] declaredFields = clazz.getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
Field declaredField = declaredFields[i];
headList.add(declaredField.getAnnotation(ApiModelProperty.class).value());
}
return headList;
}
/**
* 获取表头集合
*
* @param clazz 实体,按照传入的实体字段顺序导出
* @param map key实体字段参数，value表头值
* @param <T>
* @return
*/
public static <T> List<String> exportHeadTwoList(Class<T> clazz, LinkedMap<Object, Object> map) {
List<String> headList = new ArrayList<>();
Field[] declaredFields = clazz.getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
Field declaredField = declaredFields[i];
if (declaredField.getName().equals("payTypeIdAndMoneyMap")) {
//当字段为payTypeIdAndMoneyMap 调用headList
headList.addAll(exportHeadMap(map));
} else if (declaredField.getName().equals("projectIdAndMoneyMap")) {
headList.addAll(exportHeadMap(map));
} else if (declaredField.getName().equals("map")) {
headList.addAll(exportHeadMap(map));
} else {
headList.add(declaredField.getAnnotation(ApiModelProperty.class).value());
}
}
return headList;
}
/**
* 获取表头集合
*
* @param map key实体字段参数，value表头值
* @return
*/
public static List<String> exportHeadMap(LinkedMap<Object, Object> map) {
List<String> headList = new ArrayList<>();
for (Map.Entry<Object, Object> entity : map.entrySet()) {
headList.add(entity.getValue().toString());
}
return headList;
}
/**
* 解析数据集
*
* @param list
* @param clazz
* @param <T>
* @return
*/
public static <T> List<List<String>> exportDataList(List<?> list, Class<T> clazz) {
List<List<String>> excelData = new ArrayList<>();
for (Object obj : list) {
T t = null;
try {
t = clazz.newInstance();
BeanUtils.copyProperties(obj, t);
} catch (Exception e) {
continue;
}
//数据拼装
List<String> dataList = new ArrayList<>();
for (Field declaredField : t.getClass().getDeclaredFields()) {
declaredField.setAccessible(true);
dataList.add(getData(declaredField, t));
}
excelData.add(dataList);
}
return excelData;
}
/**
* 解析数据集
*
* @param list
* @param clazz
* @param map key实体字段参数(表头)，value表头值
* @param <T>
* @return
*/
public static <T> List<List<String>> exportDataList(List<?> list, Class<T> clazz, LinkedMap<Object, Object> map) {
List<List<String>> excelData = new ArrayList<>();
for (Object obj : list) {
T t = null;
try {
t = clazz.newInstance();
BeanUtils.copyProperties(obj, t);
} catch (Exception e) {
continue;
}
//数据拼装
List<String> dataList = new ArrayList<>();
for (Field declaredField : t.getClass().getDeclaredFields()) {
declaredField.setAccessible(true);
String data = "";
try {
if (declaredField.getName().equals("payTypeIdAndMoneyMap")) {
Map<Object, Object> payTypeMoneyMap = (Map<Object, Object>) declaredField.get(t);
//map 表头，payTypeIdAndMoneyMap value表头值
dataList.addAll(exportDataMap(map, payTypeMoneyMap));
} else if (declaredField.getName().equals("projectIdAndMoneyMap")) {
Map<Object, Object> projectIdAndMoneyMap = (Map<Object, Object>) declaredField.get(t);
dataList.addAll(exportDataMap(map, projectIdAndMoneyMap));
} else if (declaredField.getName().equals("map")) {
Map<Object, Object> dataMap = (Map<Object, Object>) declaredField.get(t);
dataList.addAll(exportDataMap(map, dataMap));
} else {
dataList.add(getData(declaredField, t));
}
} catch (Exception e) {
e.printStackTrace();
data = "";
}
}
excelData.add(dataList);
}
return excelData;
}
/**
* 解析数据集
*
* @param list
* @param clazz
* @param map
* @param payTypeMoneyMap
* @param <T>
* @return
*/
public static <T> List<List<String>> exportDataList(List<?> list, Class<T> clazz, LinkedMap<Object, Object> map,
Map<Object, Object> payTypeMoneyMap) {
List<List<String>> excelData = new ArrayList<>();
for (Object obj : list) {
T t = null;
try {
t = clazz.newInstance();
BeanUtils.copyProperties(obj, t);
} catch (Exception e) {
continue;
}
//数据拼装
List<String> dataList = new ArrayList<>();
for (Field declaredField : t.getClass().getDeclaredFields()) {
declaredField.setAccessible(true);
String data = "";
try {
data = getData(declaredField, t);
if (StringUtils.isEmpty(data)) {
data = "";
}
dataList.add(data);
} catch (Exception e) {
e.printStackTrace();
data = "";
}
}
excelData.add(dataList);
}
return excelData;
}
/**
* LinkedMap<Object, Object> 根据map的key值来确定传入的value值，不存在的都赋值0
*
* @param key 传入map集合中的key
* @param value 导出的数值
* @param map LinkedMap<Object, Object> 根据map的key值来确定传入的value值，不存在的都赋值0
* @return
*/
public static List<String> exportDataMap(Object key, Object value, LinkedMap<Object, Object> map) {
List<String> dataList = new ArrayList<>();
for (Map.Entry<Object, Object> entity : map.entrySet()) {
if (entity.getKey().equals(key)) {
dataList.add(value.toString());
} else {
dataList.add(String.valueOf(0));
}
}
return dataList;
}
/**
* LinkedMap<Object, Object> 根据map的key值来确定传入的payTypeMoneyMap value值，不存在的都赋值0
*
* @param map 表头
* @param payTypeMoneyMap 表头值
* @return
*/
public static List<String> exportDataMap(LinkedMap<Object, Object> map, Map<Object, Object> payTypeMoneyMap) {
List<String> dataList = new ArrayList<>();
for (Map.Entry<Object, Object> entity : map.entrySet()) {
Object paidMoney = payTypeMoneyMap.get(entity.getKey());
if (!StringUtils.isEmpty(paidMoney)) {
dataList.add(paidMoney.toString());
} else {
dataList.add(String.valueOf(0));
}
}
return dataList;
}
/**
* 解码数据
*
* @param declaredField 字段
* @param t 实体数据
* @param <T>
* @return
*/
private static <T> String getData(Field declaredField, T t) {
String data = "";
try {
if (!StringUtils.isEmpty(declaredField.get(t))) {
String typeName = declaredField.getType().getName();
String name = declaredField.getName();
if (name.equals("businessType")) {
Integer businessType = (Integer) declaredField.get(t);
BusinessTypeEnum businessTypeEnum = BusinessTypeEnum.parse(businessType);
if (businessTypeEnum == null) {
data = "";
} else {
data = businessTypeEnum.getValue();
}
} else if (name.equals("grade")) {
Integer grade = (Integer) declaredField.get(t);
GradeEnum gradeEnum = GradeEnum.parse(grade);
if (gradeEnum == null) {
data = "临时";
} else {
data = gradeEnum.getValue();
}
} else if (name.equals("isFirstVisit")) {
Integer isFirstVisit = (Integer) declaredField.get(t);
IsFirstVisitEnum isFirstVisitEnum = IsFirstVisitEnum.parse(isFirstVisit);
if (StringUtils.isEmpty(isFirstVisitEnum)) {
data = "初诊";
} else if (isFirstVisit.equals(IsFirstVisitEnum.YES.getKey())) {
data = "初诊";
} else if (isFirstVisit.equals(IsFirstVisitEnum.NO.getKey())) {
data = "复诊";
} else {
data = "初诊";
}
} else if (name.equals("sex")) {
Integer sex = (Integer) declaredField.get(t);
if (StringUtils.isEmpty(sex)) {
data = "";
} else if (sex.equals(1)) {
data = "男";
} else if (sex.equals(2)) {
data = "女";
}
} else if (name.equals("lossStatus")) {
Integer lossStatus = (Integer) declaredField.get(t);
LossStatusEnum lossStatusEnum = LossStatusEnum.parse(lossStatus);
if (lossStatusEnum == null) {
data = "流失";
} else {
data = lossStatusEnum.getValue();
}
} else if (name.equals("workStatus")) {
Integer workStatus = (Integer) declaredField.get(t);
WorkStatusEnum workStatusEnum = WorkStatusEnum.parse(workStatus);
if (workStatusEnum == null) {
data = "";
} else {
data = workStatusEnum.getValue();
}
} else {
if (typeName.equals("java.util.Date")) {
data = DateUtil.GetFormatTime((Date) declaredField.get(t), DateUtil.YEAR_MONTH_DAY_HOUR_MINUTE_SECOND);
} else {
data = declaredField.get(t).toString();
}
}
}
if (StringUtils.isEmpty(data)) {
data = "";
}
} catch (Exception e) {
e.printStackTrace();
data = "";
}
return data;
}
/**
* 列宽自适应
*
* @param sheet
* @return
*/
public static void autoColumnWidth(XSSFSheet sheet) {
int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells();
for (int i = 0; i < maxColumn; i++) {
sheet.autoSizeColumn(i);
}
for (int columnNum = 0; columnNum <= maxColumn; columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row currentRow;
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
Cell currentCell = currentRow.getCell(columnNum);
try {
int length = currentCell.toString().getBytes("GBK").length;
if (columnWidth < length + 1) {
columnWidth = length + 8;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
int colWidth = sheet.getColumnWidth(columnNum) * 2;
if (colWidth < 255 * 256) {
sheet.setColumnWidth(columnNum, colWidth < 3000 ? 3000 : colWidth);
} else {
sheet.setColumnWidth(columnNum, 6000);
}
// sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
