背景:因为要导出发货信息表格到不同平台(如:风火递)导入发货或数据,所以有根据字段反射导出Excel表格,废话不多说,直接上代码:
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import javax.annotation.PostConstruct;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* desc 韵达Excel表格相关
* author liuyayi
* date 2024.05.11 14:05
**/
@Service
@Slf4j
@AllArgsConstructor
public class ExcelUtil extends com.joolun.cloud.mall.common.util.ExcelUtil {
private final UploadExcelUtil uploadExcelUtil;
public String excelUtilInit(List<OrderInfo> excelDTOList, ExcelDTO excelDTO) throws IOException, InvocationTargetException, NoSuchMethodException, IllegalAccessException, ClassNotFoundException {
String path = super.init();
if (excelDTO.getExcelVType().equals(MallConstants.EXCEL_V_TYPE_1)) {//导出新版excel
return exportExcel(excelDTOList, path, excelDTO);
} else {//导出老版excel
return exportOldExcel(excelDTOList, path, excelDTO);
}
}
public String exportExcel(List<OrderInfo> excelDTOList, String path, ExcelDTO excelDTO) throws IOException, NoSuchMethodException, InvocationTargetException, IllegalAccessException, ClassNotFoundException {
// 创建一个工作簿
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
// 创建工作表
XSSFSheet sheet = xssfWorkbook.createSheet("运单信息");
List<Map<String, String>> infoList = excelDTO.getInfoList();
XSSFRow row = sheet.createRow(0);//创建标题行
for (int column = 0; column < infoList.size(); column++) {
// 创建行
sheet.setColumnWidth(column, 38 * 256);//设置标题行格式
Map<String, String> map = infoList.get(column);
String excelTitelName = map.get("excelTitelName");//导出文档列名
String fieldName = map.get("fieldName");//系统列名
row.createCell(column).setCellValue(excelTitelName);//填充标题行数据
int rowNumber = 1;//控制列的变量
XSSFRow row1;
String finalStr = "";
for (OrderInfo dto : excelDTOList) {
for (OrderItem orderItem : dto.getListOrderItem()) {
if (ObjectUtil.isEmpty(sheet.getRow(rowNumber))) {
row1 = sheet.createRow(rowNumber);//创建新行
} else {
row1 = sheet.getRow(rowNumber);//获得原来的行
}
if (StringUtils.isEmpty(fieldName)) {//如果没有输入系统列名,默认为空字符串
finalStr = "";
} else if (fieldName.equals("getUserMessage")) {
Class dtoClass = dto.getClass();
Method dtoMethod = dtoClass.getMethod(fieldName);
if(dtoMethod.invoke(dto) != null){
finalStr = dtoMethod.invoke(dto).toString();
}
} else if (fieldName.equals("getUserName") || fieldName.equals("getTelNum") || fieldName.equals("getAddress")) {
Class dtoClass = dto.getClass();
Method dtoMethod = dtoClass.getMethod("getOrderLogistics");
Object orderLogistics = dtoMethod.invoke(dto);
Class orderLogisticsClass = orderLogistics.getClass();
Method orderLogsitMethod = orderLogisticsClass.getMethod(fieldName);
if(orderLogsitMethod.invoke(dto.getOrderLogistics()) != null){
finalStr = orderLogsitMethod.invoke(dto.getOrderLogistics()).toString();
}
//System.out.println(fieldName+":"+finalStr);
} else if (fieldName.equals("getDeliverName") || fieldName.equals("getDeliverPhone") || fieldName.equals("getDeliverAddr")) {
Class excelDTOClass = excelDTO.getClass();
Method excelDTOClassMethod = excelDTOClass.getMethod(fieldName);
if (fieldName.equals("getDeliverAddr")) {
finalStr = ((List<String>) excelDTOClassMethod.invoke(excelDTO)).get(2);
} else {
if(excelDTOClassMethod.invoke(excelDTO) != null){
finalStr = excelDTOClassMethod.invoke(excelDTO).toString();
}
}
} else {
Class orderClass = orderItem.getClass();
Method orderMethod = orderClass.getMethod(fieldName);
if(orderMethod.invoke(orderItem) != null){
finalStr = orderMethod.invoke(orderItem).toString();
}
}
row1.createCell(column).setCellValue(finalStr);
rowNumber++;
}
}
}
try {
FileOutputStream fileOutputStream = null;
path = getFileName(path, excelDTO.getExcelVType());//生成导出文件路径
fileOutputStream = new FileOutputStream(path);//导出
xssfWorkbook.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
return uploadExcelUtil.uploadFile(path);
} catch (Exception e) {
log.error(e.toString());
//System.out.println(e);
throw new RuntimeException(e);
}
}
public String exportOldExcel(List<OrderInfo> excelDTOList, String path, ExcelDTO excelDTO) throws IOException, NoSuchMethodException, InvocationTargetException, IllegalAccessException {
// 创建一个工作簿
Workbook workbook = new HSSFWorkbook();
//创建工作表
Sheet sheet = workbook.createSheet("运单信息");
List<Map<String, String>> infoList = excelDTO.getInfoList();
//创建行
Row row = sheet.createRow(0);
for (int column = 0; column < infoList.size(); column++) {
// 创建行
sheet.setColumnWidth(column, 38 * 256);//设置标题行格式
Map<String, String> map = infoList.get(column);
String excelTitelName = map.get("excelTitelName");//导出文档列名
String fieldName = map.get("fieldName");//系统列名
row.createCell(column).setCellValue(excelTitelName);//填充标题行数据
int rowNumber = 1;//控制列的变量
Row row1;
String finalStr = "";
for (OrderInfo dto : excelDTOList) {
for (OrderItem orderItem : dto.getListOrderItem()) {
if (ObjectUtil.isEmpty(sheet.getRow(rowNumber))) {
row1 = sheet.createRow(rowNumber);//创建新行
} else {
row1 = sheet.getRow(rowNumber);//获得原来的行
}
if (StringUtils.isEmpty(fieldName)) {//如果没有输入系统列名,默认为空字符串
finalStr = "";
} else if (fieldName.equals("getUserMessage")) {
Class dtoClass = dto.getClass();
Method dtoMethod = dtoClass.getMethod(fieldName);
if (dtoMethod.invoke(dto) != null) {
finalStr = (dtoMethod.invoke(dto).toString());
}
} else if (fieldName.equals("getUserName") || fieldName.equals("getTelNum") || fieldName.equals("getAddress")) {
Class dtoClass = dto.getClass();
Method dtoMethod = dtoClass.getMethod("getOrderLogistics");
Object orderLogistics = dtoMethod.invoke(dto);
Class orderLogisticsClass = orderLogistics.getClass();
Method orderLogsitMethod = orderLogisticsClass.getMethod(fieldName);
if (orderLogsitMethod.invoke(dto.getOrderLogistics()) != null) {
finalStr = orderLogsitMethod.invoke(dto.getOrderLogistics()).toString();
}
} else if (fieldName.equals("getDeliverName") || fieldName.equals("getDeliverPhone") || fieldName.equals("getDeliverAddr")) {
Class excelDTOClass = excelDTO.getClass();
Method excelDTOClassMethod = excelDTOClass.getMethod(fieldName);
if (fieldName.equals("getDeliverAddr")) {
finalStr = ((List<String>) excelDTOClassMethod.invoke(excelDTO)).get(2);
} else {
if(excelDTOClassMethod.invoke(excelDTO) != null){
finalStr = excelDTOClassMethod.invoke(excelDTO).toString();
}
}
} else {
Class orderClass = orderItem.getClass();
Method orderMethod = orderClass.getMethod(fieldName);
if (orderMethod.invoke(orderItem) != null) {
finalStr = orderMethod.invoke(orderItem).toString();
}
}
row1.createCell(column).setCellValue(finalStr);
rowNumber++;
}
}
}
try {
FileOutputStream fileOutputStream = null;
path = getFileName(path, excelDTO.getExcelVType());//生成导出文件路径
fileOutputStream = new FileOutputStream(path);//导出
workbook.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
return uploadExcelUtil.uploadFile(path);
} catch (Exception e) {
log.error(e.toString());
//System.out.println(e);
throw new RuntimeException(e);
}
}
private String getFileName(String path, String excelVType) {
path = path + "excel_pakage";
if (excelVType.equals(MallConstants.EXCEL_V_TYPE_1)) {//新版本
path = path + ".xlsx";
} else {//老版本
path = path + ".xls";
}
return path;
}
}