# Import Excel Automapping Entity (POI lib)
###### tags: `spring`
原本打算用poi-object-mapper處理上傳Excel，但瘋狂報錯，且要用的功能也很陽春，自己改成要的樣子
Step1. 建立@Annotation給Entity與上傳文件標頭mapping用

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.METHOD})
public @interface ExcelColumn {
    String value() default "";
    boolean ignore() default false; // 匯入時跳過此欄位
}

該範例為使用JPA Support建立出來，且有使用lombok這個lib
有欄位名稱不同的添加@ExcelColumn

@Data
@Entity
@Table(name = "CcyPairFXRate")
@IdClass(CcyPairFXRateId.class)
public class CcyPairFXRate implements Serializable {
    @Id
    @Column(name = "DataDate", nullable = false)
    @ExcelColumn("日期")
    private String dataDate;
    @Id
    @Column(name = "RateType", nullable = false)
    @ExcelColumn("類別")
    private String rateType;
    @Id
    @Column(name = "CCYPair", nullable = false)
    private String CCYPair;
    @Column(name = "Rate")
    @ExcelColumn("Rate")
    private Float rate;
}

Step2. 初始化上傳文件 & 取得Excel標頭名稱的List

XSSFWorkbook workbook = new XSSFWorkbook(multipartFile.getInputStream());
XSSFSheet worksheet = workbook.getSheetAt(0);
XSSFRow row0 = worksheet.getRow(0);
int num = row0.getLastCellNum() == 0 ? 0 : row0.getLastCellNum() - 1;
List<String> headerArr = new ArrayList<>();
for (int i = 0; i <= num; i++) {
    headerArr.add(row0.getCell(i).getStringCellValue());
}

Step3. 取得Entity的欄位名稱與宣告的@ExcelColumn做mapping

public T setEntityFromXSSFRow (T item, XSSFRow row, List<String> headerArr) throws NoSuchFieldException, IllegalAccessException {
        int colIndex = headerArr.size();
        HashMap<String, String> modelMap = new HashMap<>();
        for (Field f : item.getClass().getDeclaredFields()) {
            // 僅接受有輸入Annotation的欄位
//            if (!f.isAnnotationPresent(SheetColumn.class)) {
//                continue;
//            }
            ExcelColumn ec = f.getAnnotation(ExcelColumn.class);
            if(ec != null && ec.ignore()) continue; // 跳過ignore
            String header = ec == null || ec.value() == null || ec.value().isEmpty() ? f.getName() : ec.value();
            modelMap.put(header, f.getName());
        }
        // Step4
        // ...
        // Step4
    }

Step4. 將取得的欄位標頭與內容轉值後放入Entity內

public T setEntityFromXSSFRow (T item, XSSFRow row, List<String> headerArr) throws NoSuchFieldException, IllegalAccessException {
        // step3
        // ...
        // step3
        int colIndex = headerArr.size();
        for (int i = 0; i < colIndex; i++) {
            String fileName = modelMap.get(headerArr.get(i));
            if(fileName == null || fileName.isEmpty()) {
                continue;
            }
            Field field = item.getClass().getDeclaredField(fileName);
            field.setAccessible(true);
            switch (row.getCell(i).getCellType().toString()) {
                case "STRING" -> field.set(item, row.getCell(i).getStringCellValue());
                case "NUMERIC" -> {
                    if (Float.class.equals(field.getType())) {
                        field.set(item, (float) row.getCell(i).getNumericCellValue());
                    } else if (Integer.class.equals(field.getType())) {
                        field.set(item, (int) row.getCell(i).getNumericCellValue());
                    } else {
                        field.set(item, row.getCell(i).getNumericCellValue());
                    }
                }
                case "DATE" -> field.set(item, row.getCell(i).getDateCellValue());
            }
        }
        return  item;
    }

Step5. service 呼叫 mapper 將逐筆entity放回List中

List<CcyPairFXRate> ccy = new ArrayList<>();
Mapper<CcyPairFXRate> mapper = new Mapper<>();
for (int index = 1; index < worksheet.getPhysicalNumberOfRows(); index++) {
    CcyPairFXRate cc = new CcyPairFXRate();
    XSSFRow row = worksheet.getRow(index);
    ccy.add(mapper.setEntityFromXSSFRow(cc, row, headerArr));
}

ref
[poi-object-mapper](https://github.com/millij/poi-object-mapper)