package com.zy.asrs.wms.utils;
|
|
import com.zy.asrs.framework.common.Cools;
|
import com.zy.asrs.wms.asrs.entity.MatField;
|
import com.zy.asrs.wms.asrs.entity.template.OrderTemplate;
|
import io.swagger.annotations.ApiModelProperty;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.lang.reflect.Field;
|
import java.lang.reflect.Method;
|
import java.lang.reflect.Modifier;
|
import java.net.URLEncoder;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
/**
|
* Created by vincent on 2/17/2024
|
*/
|
public class ExcelUtil {
|
|
public static void build(Workbook workbook, HttpServletResponse response) {
|
response.reset();
|
Utils.cors(response);
|
response.setContentType("application/octet-stream; charset=utf-8");
|
try {
|
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("export", "UTF-8"));
|
workbook.write(response.getOutputStream());
|
} catch (IOException ignore) {}
|
}
|
|
public static <T> Workbook create(List<T> list, Class<T> clz) {
|
return create(list, clz, null);
|
}
|
|
public static <T> Workbook create(List<T> list, Class<T> clz, List<MatField> dynamicFields) {
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
Sheet sheet = workbook.createSheet(clz.getSimpleName());
|
|
Row header = sheet.createRow(0);
|
Field[] fields = Cools.getAllFields(clz);
|
int headerIdx = 0;
|
for (Field field : fields) {
|
if (Modifier.isFinal(field.getModifiers())
|
|| Modifier.isStatic(field.getModifiers())
|
|| Modifier.isTransient(field.getModifiers())) {
|
continue;
|
}
|
String memo = "未知";
|
if (field.isAnnotationPresent(ApiModelProperty.class)) {
|
memo = field.getAnnotation(ApiModelProperty.class).value() + "[" + field.getName() + "]";
|
}
|
header.createCell(headerIdx).setCellValue(memo);
|
headerIdx++;
|
}
|
|
//动态字段
|
if(dynamicFields != null){
|
for (MatField field : dynamicFields) {
|
header.createCell(headerIdx).setCellValue(field.getDescribe() + "[" + field.getName() + "]");
|
headerIdx++;
|
}
|
}
|
|
int rowIndex = 1;
|
for (T t : list) {
|
Row row = sheet.createRow(rowIndex++);
|
|
int cellIndex = 0;
|
for (Field field : fields) {
|
if (Modifier.isFinal(field.getModifiers())
|
|| Modifier.isStatic(field.getModifiers())
|
|| Modifier.isTransient(field.getModifiers())) {
|
continue;
|
}
|
|
field.setAccessible(true); // 此行很重要，特别是字段为private时
|
Object value = null;
|
try {
|
value = field.get(t);
|
} catch (IllegalAccessException e) {
|
e.printStackTrace();
|
}
|
if (value != null) {
|
if (value instanceof Date) {
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
row.createCell(cellIndex).setCellValue(sdf.format((Date) value));
|
} else {
|
row.createCell(cellIndex).setCellValue(value.toString());
|
}
|
}
|
cellIndex++;
|
}
|
|
//动态字段
|
if(dynamicFields != null){
|
for (Field field : fields) {
|
if (field.getName().equals("dynamicFields")) {
|
try {
|
Map<String, Object> map = (Map<String, Object>) field.get(t);
|
for (MatField matField : dynamicFields) {
|
Object value = map.get(matField.getName());
|
if(value != null){
|
row.createCell(cellIndex).setCellValue(value.toString());
|
cellIndex++;
|
}
|
}
|
} catch (IllegalAccessException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
}
|
|
}
|
|
for (int i = 0; i <= fields.length; i++) {
|
sheet.autoSizeColumn(i);
|
}
|
|
return workbook;
|
}
|
|
public static <T> List<T> parseExcelFile(MultipartFile file, Class<T> clz) {
|
List<T> dataList = new ArrayList<>();
|
try (Workbook workbook = new HSSFWorkbook(file.getInputStream())) {
|
ArrayList<HashMap<String, Object>> list = new ArrayList<>();
|
ArrayList<String> fields = new ArrayList<>();
|
|
Sheet sheet = workbook.getSheetAt(0);
|
for (Row row : sheet) {
|
HashMap<String, Object> map = new HashMap<>();
|
if (!fields.isEmpty()) {
|
for (String field : fields) {
|
map.put(field, null);
|
}
|
list.add(map);
|
}
|
|
for (Cell cell : row) {
|
if (cell.getRowIndex() == 0) {
|
Pattern pattern = Pattern.compile("\\[(.*?)\\]");
|
Matcher matcher = pattern.matcher(cell.getStringCellValue());
|
while (matcher.find()) {
|
fields.add(matcher.group(1));
|
}
|
}else {
|
String value = "";
|
if(cell.getCellType() == CellType.NUMERIC.getCode()) {
|
// 先将数字转换为字符串
|
value = String.valueOf(cell.getNumericCellValue());
|
// 处理字符串value
|
} else if(cell.getCellType() == CellType.STRING.getCode()) {
|
// 直接获取字符串值
|
value = cell.getStringCellValue();
|
// 处理字符串value
|
}
|
map.put(fields.get(cell.getColumnIndex()), value);
|
}
|
}
|
|
}
|
|
for (HashMap<String, Object> map : list) {
|
T t = ExcelUtil.parseData(clz, map);
|
dataList.add(t);
|
}
|
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
return dataList;
|
}
|
|
public static <T> T parseData(Class<T> clz, Map<String, Object> map) {
|
try {
|
T newInstance = clz.newInstance();
|
Field[] fields = Cools.getAllFields(clz);
|
for (Field field : fields) {
|
field.setAccessible(true);
|
if (!map.containsKey(field.getName())) {
|
if (Modifier.isTransient(field.getModifiers()) && field.getName().equals("dynamicFields")) {
|
Method method = clz.getMethod("syncFieldMap", Map.class);
|
method.invoke(newInstance, map);
|
}
|
continue;
|
}
|
if (map.get(field.getName()) == null) {
|
continue;
|
}
|
// 判断字段类型并赋值
|
if (field.getType() == String.class) {
|
field.set(newInstance, String.valueOf(map.get(field.getName())));
|
} else if (field.getType() == Integer.class) {
|
field.set(newInstance, Integer.parseInt(map.get(field.getName()).toString()));
|
} else if (field.getType() == Float.class) {
|
field.set(newInstance, Float.parseFloat(map.get(field.getName()).toString()));
|
} else if (field.getType() == Double.class) {
|
field.set(newInstance, Double.parseDouble(map.get(field.getName()).toString()));
|
} else if (field.getType() == Boolean.class) {
|
field.set(newInstance, Boolean.parseBoolean(map.get(field.getName()).toString()));
|
}
|
}
|
return newInstance;
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return null;
|
}
|
|
}
|
