package com.tievd.jyz.plugin;
|
|
import cn.hutool.core.collection.ListUtil;
|
import cn.hutool.core.date.DateTime;
|
import cn.hutool.core.date.DateUtil;
|
import cn.hutool.core.util.ObjectUtil;
|
import cn.hutool.core.util.StrUtil;
|
import cn.hutool.json.JSONArray;
|
import cn.hutool.json.JSONObject;
|
import cn.hutool.json.JSONUtil;
|
import com.tievd.cube.commons.annotations.Dict;
|
import com.tievd.cube.commons.easyexcel.EasyExcel;
|
import com.tievd.cube.commons.easyexcel.annotations.Excel;
|
import com.tievd.cube.commons.easyexcel.dict.IDictTranslator;
|
import com.tievd.cube.commons.easyexcel.model.ImportExcel;
|
import lombok.SneakyThrows;
|
import org.apache.poi.common.usermodel.HyperlinkType;
|
import org.apache.poi.hssf.util.HSSFColor;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.NumberToTextConverter;
|
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory;
|
import org.springframework.stereotype.Component;
|
|
import java.io.OutputStream;
|
import java.lang.reflect.Field;
|
import java.lang.reflect.Modifier;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
/**
|
* Author: wqy
|
* Date: 2022/10/14 11:13
|
*/
|
@Component
|
public class ReformEasyExcel implements EasyExcel {
|
|
@Override
|
@SneakyThrows
|
public <T> List<T> read(Class<T> cls, ImportExcel importExcel, IDictTranslator dictTranslator) {
|
List<T> data = new ArrayList<>();
|
Workbook workbook = WorkbookFactory.create(importExcel.getInputStream());
|
Sheet sheet = workbook.getSheetAt(importExcel.getSheetIndex());
|
int lastRowNum = sheet.getLastRowNum() + 1;
|
// 得到标题行
|
Row titleCellRangeRow = sheet.getRow(importExcel.getStartRow() - 1);
|
// 得到标题索引
|
Map<String, Integer> titleIndex = new HashMap<>();
|
for (int i = 0; i < titleCellRangeRow.getLastCellNum(); i++) {
|
titleIndex.put(titleCellRangeRow.getCell(i).getStringCellValue(), i);
|
}
|
Field[] fields = cls.getDeclaredFields();
|
// 排除static字段
|
List<Field> unStaticFieldList = ListUtil.toList(fields).stream()
|
.filter(field -> !Modifier.isStatic(field.getModifiers()) && field.getAnnotation(Excel.class)!=null)
|
.collect(Collectors.toList());
|
// 遍历数据
|
for (int i = importExcel.getStartRow(); i < lastRowNum; i++) {
|
Row row = sheet.getRow(i);
|
T obj = cls.newInstance();
|
for (Field field : unStaticFieldList) {
|
String name = field.getName();
|
Excel excel = field.getAnnotation(Excel.class);
|
name = excel.value();
|
Integer idx = titleIndex.get(name);
|
if (idx == null) {
|
continue;
|
}
|
String strValue;
|
Cell cell = row.getCell(idx);
|
CellType cellType = cell.getCellType();
|
// 单元格未填写的情况下是null
|
if (cellType == null) {
|
continue;
|
}
|
if (CellType.NUMERIC.equals(cellType)) {
|
strValue = NumberToTextConverter.toText(cell.getNumericCellValue());
|
} else {
|
strValue = row.getCell(idx).getStringCellValue();
|
}
|
if (StrUtil.isBlank(strValue)) {
|
continue;
|
}
|
String fieldTypeName = field.getType().getSimpleName().toLowerCase();
|
// 字典转换
|
Dict dict = field.getAnnotation(Dict.class);
|
if (dictTranslator != null && dict != null) {
|
strValue = dictTranslator.valueToId(strValue, dict);
|
}
|
Object value;
|
if (fieldTypeName.contains("int")) {
|
value = Integer.valueOf(strValue);
|
} else if (fieldTypeName.contains("byte")) {
|
value = Byte.valueOf(strValue);
|
} else if (fieldTypeName.contains("long")) {
|
value = Long.valueOf(strValue);
|
} else if (fieldTypeName.contains("bool")) {
|
value = Boolean.valueOf(strValue);
|
} else if (fieldTypeName.contains("date")) {
|
DateTime dateTime = DateUtil.parse(strValue);
|
value = dateTime.toJdkDate();
|
} else if (fieldTypeName.contains("double")) {
|
value = Double.valueOf(strValue);
|
} else if (field.getType().isEnum()) {
|
Class fieldClass = field.getType();
|
value = Enum.valueOf(fieldClass, strValue);
|
} else {
|
value = strValue;
|
}
|
field.setAccessible(true);
|
field.set(obj, value);
|
}
|
data.add(obj);
|
}
|
return data;
|
}
|
|
@Override
|
@SneakyThrows
|
public String readToJson(Class<?> cls, ImportExcel excel, IDictTranslator dictHandler) {
|
List<?> list = read(cls, excel, dictHandler);
|
return JSONUtil.toJsonStr(list);
|
}
|
|
@Override
|
@SneakyThrows
|
public String readToJson(ImportExcel excel) {
|
JSONArray data = JSONUtil.createArray();
|
Workbook workbook = WorkbookFactory.create(excel.getInputStream());
|
Sheet sheet = workbook.getSheetAt(excel.getSheetIndex());
|
// 得到所有行
|
int lastRowNum = sheet.getLastRowNum();
|
// 得到标题行
|
Row titleCellRangeRow = sheet.getRow(excel.getStartRow() - 1);
|
List<String> titles = new ArrayList<>();
|
for (int i = 0; i < titleCellRangeRow.getLastCellNum(); i++) {
|
titles.add(titleCellRangeRow.getCell(i).getStringCellValue());
|
}
|
// 遍历数据
|
for (int i = excel.getStartRow(); i < lastRowNum; i++) {
|
JSONObject obj = JSONUtil.createObj();
|
Row row = sheet.getRow(i);
|
int lastCellNum = row.getLastCellNum();
|
for (int j = 0; j < lastCellNum; j++) {
|
Cell cell = row.getCell(j);
|
CellType cellType = cell.getCellType();
|
if (CellType.NUMERIC.equals(cellType)) {
|
obj.set(titles.get(j), row.getCell(j).getNumericCellValue());
|
} else {
|
obj.set(titles.get(j), row.getCell(j).getStringCellValue());
|
}
|
}
|
data.add(obj);
|
}
|
return data.toString();
|
}
|
|
@Override
|
@SneakyThrows
|
public void export(List<?> list, OutputStream outputStream, IDictTranslator dictTranslator) {
|
int dataSize = list.size();
|
if (dataSize > 0) {
|
Field[] fields = list.get(0).getClass().getDeclaredFields();
|
try (XSSFWorkbook workbook = XSSFWorkbookFactory.createWorkbook()) {
|
XSSFCreationHelper creationHelper = new XSSFCreationHelper(workbook);
|
Sheet sheet = workbook.createSheet("Data");
|
Font font = workbook.createFont();
|
font.setBold(true);
|
font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
|
font.setFontHeightInPoints((short) 14);
|
CellStyle cellStyle = workbook.createCellStyle();
|
cellStyle.setFont(font);
|
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
cellStyle.setAlignment(HorizontalAlignment.CENTER);
|
// 排除static字段
|
List<Field> unStaticFieldList = ListUtil.toList(fields).stream()
|
.filter(field -> !Modifier.isStatic(field.getModifiers()) && field.getAnnotation(Excel.class)!=null)
|
.collect(Collectors.toList());
|
for (int i = 0; i < unStaticFieldList.size(); i++) {
|
Field field = unStaticFieldList.get(i);
|
String name = field.getName();
|
Excel excel = field.getAnnotation(Excel.class);
|
name = excel.value();
|
// 写Header
|
Row header = sheet.getRow(0);
|
if (header == null) {
|
header = sheet.createRow(0);
|
}
|
Cell headerCell = header.getCell(i);
|
if (headerCell == null) {
|
headerCell = header.createCell(i);
|
}
|
headerCell.setCellStyle(cellStyle);
|
headerCell.setCellValue(name);
|
// 字典转换
|
Dict dict = field.getAnnotation(Dict.class);
|
// 按列写数据
|
for (int j = 0; j < dataSize; j++) {
|
Row row = sheet.getRow(1 + j);
|
if (row == null) {
|
row = sheet.createRow(1 + j);
|
}
|
Cell cell = row.getCell(i);
|
if (cell == null) {
|
cell = row.createCell(i);
|
}
|
field.setAccessible(true);
|
String cellData = " ";
|
Object value = field.get(list.get(j));
|
if (ObjectUtil.isNotEmpty(value)) {
|
if (value instanceof Date) {
|
cellData = DateUtil.format((Date) value, "yyyy-MM-dd HH:mm:ss");
|
} else if (value instanceof List) {
|
cellData = JSONUtil.toJsonStr(value);
|
} else {
|
cellData = String.valueOf(value);
|
}
|
if (dictTranslator != null && dict != null && StrUtil.isNotEmpty(cellData)) {
|
cellData = dictTranslator.idToValue(cellData, dict);
|
}
|
}
|
cell.setCellValue(cellData);
|
//增加超连接
|
if (field.getAnnotation(ExcelHyperLink.class) != null && StrUtil.isNotBlank(cellData)) {
|
Hyperlink link = creationHelper.createHyperlink(HyperlinkType.FILE);
|
link.setAddress(cellData);
|
cell.setHyperlink(link);
|
}
|
}
|
sheet.autoSizeColumn(i);
|
//自动宽度依然会遮住内容，宽度放大到1.5倍
|
int width = sheet.getColumnWidth(i) * 15 / 10;
|
sheet.setColumnWidth(i, width);
|
}
|
// 写到输出流
|
workbook.write(outputStream);
|
}
|
}
|
}
|
|
@Override
|
public boolean supportXlsx() {
|
return true;
|
}
|
|
@Override
|
public String getExtension() {
|
return ".xlsx";
|
}
|
}
|
