Repository: wangguanquan/eec
Pull request #359 (2023-09-05 13:02:29)

Fix#358 读取文件时开放额外属性以解析特殊表头
#358 读取文件时开放额外属性以解析特殊表头


Patch:
diff --git a/src/main/java/org/ttzero/excel/entity/Workbook.java b/src/main/java/org/ttzero/excel/entity/Workbook.java
index c854fea1..ece0a8e7 100644
--- a/src/main/java/org/ttzero/excel/entity/Workbook.java
+++ b/src/main/java/org/ttzero/excel/entity/Workbook.java
@@ -70,8 +70,6 @@ import static org.ttzero.excel.util.FileUtil.exists;
  * A typical example as follow:
  * <blockquote><pre>
  * new Workbook("{name}", "{author}")
- *     // Print debug logs
- *     .watch(System.out::println)
  *     // Auto size the column width
  *     .setAutoSize(true)
  *     // Add a Worksheet
diff --git a/src/main/java/org/ttzero/excel/reader/HeaderRow.java b/src/main/java/org/ttzero/excel/reader/HeaderRow.java
index 0e859c06..a68f0f58 100644
--- a/src/main/java/org/ttzero/excel/reader/HeaderRow.java
+++ b/src/main/java/org/ttzero/excel/reader/HeaderRow.java
@@ -59,11 +59,26 @@ import static org.ttzero.excel.entity.Sheet.int2Col;
 import static org.ttzero.excel.util.ReflectUtil.listDeclaredFields;
 import static org.ttzero.excel.util.ReflectUtil.listDeclaredMethods;
 import static org.ttzero.excel.util.StringUtil.EMPTY;
+import static org.ttzero.excel.util.StringUtil.lowFirstKey;
+import static org.ttzero.excel.util.StringUtil.toCamelCase;
 
 /**
  * @author guanquan.wang at 2019-04-17 11:55
  */
 public class HeaderRow extends Row {
+    /**
+     * Force Import (Match with field name if without {@code ExcelColumn} annotation)
+     */
+    public static final int FORCE_IMPORT = 1;
+    /**
+     * Ignore Case (Ignore case matching column names)
+     */
+    public static final int  IGNORE_CASE = 1 << 1;
+    /**
+     * Camel Case (CAMEL_CASE to camelCase)
+     */
+    public static final int  CAMEL_CASE = 1 << 2;
+
     protected String[] names;
     protected Class<?> clazz;
     protected Object t;
@@ -76,13 +91,25 @@ public class HeaderRow extends Row {
     protected static final Field detailMessageField;
     // Specify total rows of header
     protected int headRows;
+    /**
+     * Simple properties
+     *
+     * <blockquote><pre>
+     *  Bit  | Contents
+     * ------+---------
+     * 31. 1 | Force Import
+     * 30. 1 | Ignore Case (Ignore case matching column names)
+     * 29. 1 | Camel Case
+     * </pre></blockquote>
+     */
+    protected int option;
 
     static {
         Field field = null;
         try {
             field = Throwable.class.getDeclaredField("detailMessage");
             field.setAccessible(true);
-        } catch (NoSuchFieldException e) {
+        } catch (Exception e) {
             // Ignore
         }
         detailMessageField = field;
@@ -117,7 +144,7 @@ public class HeaderRow extends Row {
         if (headRows == 1) {
             for (int i = row.fc; i < row.lc; i++) {
                 this.names[i] = row.getString(i);
-                this.mapping.put(this.names[i], i);
+                this.mapping.put(makeKey(this.names[i]), i);
 
                 Cell cell = new Cell();
                 cell.setSv(this.names[i]);
@@ -138,7 +165,7 @@ public class HeaderRow extends Row {
                 }
                 if (buf.length() > 1) buf.deleteCharAt(buf.length() - 1);
                 this.names[i] = buf.toString();
-                this.mapping.put(this.names[i], i);
+                this.mapping.put(makeKey(this.names[i]), i);
 
                 Cell cell = new Cell();
                 cell.setSv(this.names[i]);
@@ -381,8 +408,12 @@ public class HeaderRow extends Row {
      * @return the position if found otherwise -1
      */
     public int getIndex(String columnName) {
-        Integer index = mapping != null ? mapping.get(columnName) : null;
-        return index != null ? index : -1;
+        if (mapping != null) {
+            if ((option & 2) == 2) columnName = columnName.toLowerCase();
+            Integer index = mapping.get(columnName);
+            return index != null ? index : -1;
+        }
+        return -1;
     }
 
     @Override
@@ -682,14 +713,14 @@ public class HeaderRow extends Row {
     protected Map<String, Method> attachOtherColumn(Class<?> clazz) {
         Method[] writeMethods;
         try {
-            writeMethods = listDeclaredMethods(clazz, method -> method.getAnnotation(ExcelColumn.class) != null
-                    || method.getAnnotation(RowNum.class) != null);
+            writeMethods = (option & 1) == 1 ? listDeclaredMethods(clazz)
+                : listDeclaredMethods(clazz, method -> method.getAnnotation(ExcelColumn.class) != null || method.getAnnotation(RowNum.class) != null);
         } catch (IntrospectionException e) {
             LOGGER.warn("Get [" + clazz + "] read declared failed.", e);
             return Collections.emptyMap();
         }
 
-        return Arrays.stream(writeMethods).filter(m -> m.getParameterCount() == 1).collect(Collectors.toMap(Method::getName, a -> a, (a, b) -> b));
+        return Arrays.stream(writeMethods).filter(m -> m.getParameterCount() == 1).collect(Collectors.toMap(a -> { String k = a.getName(); return k.startsWith("set") ? lowFirstKey(k.substring(3)) : k; }, a -> a, (a, b) -> b));
     }
 
     /**
@@ -726,7 +757,25 @@ public class HeaderRow extends Row {
         // Row Num
         RowNum rowNum = ao.getAnnotation(RowNum.class);
         if (rowNum != null) return createColumnByAnnotation(rowNum);
-        return null;
+
+        ListSheet.EntryColumn column = null;
+        // Direct match method or field name
+        if ((option & 1) == 1) {
+            if (Field.class.isAssignableFrom(ao.getClass())) {
+                Field f = (Field) ao;
+                column = new ListSheet.EntryColumn(f.getName());
+                column.field = f;
+                column.clazz = f.getType();
+            }
+            else if (Method.class.isAssignableFrom(ao.getClass())) {
+                Method m = (Method) ao;
+                String k = m.getName();
+                column = new ListSheet.EntryColumn(k.startsWith("set") ? lowFirstKey(k.substring(3)) : k);
+                column.method = m;
+                if (m.getParameterCount() == 1) column.clazz = m.getParameterTypes()[0];
+            }
+        }
+        return column;
     }
 
     /**
@@ -777,4 +826,22 @@ public class HeaderRow extends Row {
             }
         }
     }
+
+    public HeaderRow setOptions(int option) {
+        this.option = option;
+        if (mapping != null && (option & 6) > 0) {
+            Map<String, Integer> m = new HashMap<>(mapping.size());
+            for (Map.Entry<String, Integer> entry : mapping.entrySet()) {
+                m.put(makeKey(entry.getKey()), entry.getValue());
+            }
+            this.mapping = m;
+        }
+        return this;
+    }
+
+    protected String makeKey(String key) {
+        if ((option & 4) == 4) key = toCamelCase(key);
+        if ((option & 2) == 2) key = key.toLowerCase();
+        return key;
+    }
 }
diff --git a/src/main/java/org/ttzero/excel/reader/Sheet.java b/src/main/java/org/ttzero/excel/reader/Sheet.java
index e0a3a301..03c0c379 100644
--- a/src/main/java/org/ttzero/excel/reader/Sheet.java
+++ b/src/main/java/org/ttzero/excel/reader/Sheet.java
@@ -400,6 +400,69 @@ public interface Sheet extends Closeable {
         }
     }
 
+    /**
+     * Use field name matching without {@link org.ttzero.excel.annotation.ExcelColumn} annotation
+     *
+     * <p>When converting row data to Java objects, only fields with {@code ExcelColumn} annotations
+     * are matched by default. {@code forceImport} will skip this restriction,
+     * and fields without {@code ExcelColumn} annotations will be matched with field name
+     *
+     * @return this {@link Sheet}
+     */
+    default Sheet forceImport() {
+        return addHeaderColumnReadOption(HeaderRow.FORCE_IMPORT);
+    }
+
+    /**
+     * Settings ignore case matching column names
+     *
+     * @return this {@link Sheet}
+     */
+    default Sheet headerColumnIgnoreCase() {
+        return addHeaderColumnReadOption(HeaderRow.IGNORE_CASE);
+    }
+
+    /**
+     * Convert column name to camel case
+     *
+     * @return this {@link Sheet}
+     */
+    default Sheet headerColumnToCamelCase() {
+        return addHeaderColumnReadOption(HeaderRow.CAMEL_CASE);
+    }
+
+    /**
+     * Add header columns preprocessing properties
+     *
+     * @return this {@link Sheet}
+     */
+    default Sheet addHeaderColumnReadOption(int option) {
+        return setHeaderColumnReadOption(getHeaderColumnReadOption() | option);
+    }
+
+    /**
+     * Setting header columns preprocessing properties
+     *
+     * <ul>
+     *     <li>HeaderRow.FORCE_IMPORT: Match with field name if without ExcelColumn annotation</li>
+     *     <li>HeaderRow.IGNORE_CASE: Ignore case matching column names</li>
+     *     <li>HeaderRow.CAMEL_CASE: CAMEL_CASE to camelCase</li>
+     * </ul>
+     * <blockquote><pre>
+     *     reader.sheet(0).setHeaderColumnReadOption(HeaderRow.FORCE_IMPORT | HeaderRow.IGNORE_CASE)
+     * </pre></blockquote>
+     *
+     * @return this {@link Sheet}
+     */
+    Sheet setHeaderColumnReadOption(int option);
+
+    /**
+     * Returns Header column options
+     *
+     * @return this {@link Sheet}
+     */
+    int getHeaderColumnReadOption();
+
     /**
      * Make worksheets parse value only
      *
diff --git a/src/main/java/org/ttzero/excel/reader/XMLSheet.java b/src/main/java/org/ttzero/excel/reader/XMLSheet.java
index a15cbfa0..2a971311 100644
--- a/src/main/java/org/ttzero/excel/reader/XMLSheet.java
+++ b/src/main/java/org/ttzero/excel/reader/XMLSheet.java
@@ -69,6 +69,7 @@ public class XMLSheet implements Sheet {
         this.lastRowMark = sheet.lastRowMark;
         this.hrf = sheet.hrf;
         this.hrl = sheet.hrl;
+        this.option = sheet.option;
     }
 
     protected String name;
@@ -94,7 +95,9 @@ public class XMLSheet implements Sheet {
     protected Drawings drawings;
     // Header row
     protected int hrf, hrl;
-
+    // Simple properties
+    // The low 16 bits are allocated to the header, while the high 16 bits are occupied by the sheet
+    protected int option;
 
     /**
      * Setting the worksheet name
@@ -272,6 +275,7 @@ public class XMLSheet implements Sheet {
             Row row = hrf == 0 ? findRow0(this::createHeader) : getHeader(hrf, hrl);
             if (row != null) {
                 header = row instanceof HeaderRow ? (HeaderRow) row : row.asHeader();
+                header.setOptions(option << 16 >>> 16);
                 sRow.setHr(header);
             }
         } else if (hrl > 0 && hrl > sRow.index) {
@@ -321,13 +325,13 @@ public class XMLSheet implements Sheet {
                 mergeCells = mergeCells.stream().filter(dim -> dim.firstRow < toRowNum || dim.lastRow > fromRowNum).collect(Collectors.toList());
             }
 
-            return new HeaderRow().with(mergeCells, rows);
+            return new HeaderRow().with(mergeCells, rows).setOptions(option << 16 >>> 16);
         }
         // Single row
         else {
             Row row = nextRow();
             for (; row != null && row.getRowNum() < fromRowNum; row = nextRow());
-            return new HeaderRow().with(row);
+            return new HeaderRow().with(row).setOptions(option << 16 >>> 16);
         }
     }
 
@@ -362,6 +366,7 @@ public class XMLSheet implements Sheet {
         if (row == null) throw new IllegalArgumentException("Specify the bind row must not be null.");
         if (!row.equals(header)) {
             header = row instanceof HeaderRow ? (HeaderRow) row : row.asHeader();
+            header.setOptions(option << 16 >>> 16);
             sRow.setHr(header);
         }
         try {
@@ -629,7 +634,7 @@ public class XMLSheet implements Sheet {
          */
         if (hrf == 0 && nIter.hasNext()) {
             Row row = nIter.next();
-            if (header == null) header = row.asHeader();
+            if (header == null) header = row.asHeader().setOptions(option << 16 >>> 16);
             row.setHr(header);
         }
         return nIter;
@@ -658,6 +663,30 @@ public class XMLSheet implements Sheet {
         }
     }
 
+    /**
+     * Setting header columns preprocessing properties
+     *
+     * @return this {@link Sheet}
+     */
+    @Override
+    public Sheet setHeaderColumnReadOption(int option) {
+        if (option >= 1 << 17)
+            LOGGER.warn("Unrecognized options will be discarded");
+        int o = option & ((1 << 17) - 1);
+        this.option = this.option >>> 16 << 16 | o;
+        if (header != null) header.setOptions(o);
+        return this;
+    }
+
+    /**
+     * Returns Header column options
+     *
+     * @return this {@link Sheet}
+     */
+    @Override
+    public int getHeaderColumnReadOption() {
+        return header != null ? header.option : option << 16 >>> 16;
+    }
 
     /**
      * Reset the {@link XMLSheet}'s row index to begging
@@ -900,6 +929,9 @@ class XMLCalcSheet extends XMLSheet implements CalcSheet {
         this.mark = sheet.mark;
         this.sRow = sheet.sRow;
         this.lastRowMark = sheet.lastRowMark;
+        this.hrf = sheet.hrf;
+        this.hrl = sheet.hrl;
+        this.option = sheet.option;
 
         if (this.path != null) {
 
@@ -1005,6 +1037,9 @@ class XMLMergeSheet extends XMLSheet implements MergeSheet {
         this.mark = sheet.mark;
         this.sRow = sheet.sRow;
         this.lastRowMark = sheet.lastRowMark;
+        this.hrf = sheet.hrf;
+        this.hrl = sheet.hrl;
+        this.option = sheet.option;
 
         if (path != null) {
             if (reader != null && !ready) this.load0();
diff --git a/src/main/java/org/ttzero/excel/util/StringUtil.java b/src/main/java/org/ttzero/excel/util/StringUtil.java
index 120c48df..cbd657d1 100644
--- a/src/main/java/org/ttzero/excel/util/StringUtil.java
+++ b/src/main/java/org/ttzero/excel/util/StringUtil.java
@@ -119,16 +119,17 @@ public class StringUtil {
      * @param name a string to processor
      * @return a camel case string
      */
-    public static String toPascalCase(String name) {
+    public static String toCamelCase(String name) {
         if (name.indexOf('_') < 0) return name;
         char[] oldValues = name.toLowerCase().toCharArray();
         final int len = oldValues.length;
         int i = 1, idx = i;
         for (int n = len - 1; i < n; i++) {
             char c = oldValues[i], cc = oldValues[i + 1];
-            if (c == '_' && cc >= 'a' && cc <= 'z') {
-                oldValues[idx++] = (char) (cc - 32);
+            if (c == '_') {
                 i++;
+                if (cc == '_') continue;
+                oldValues[idx++] = cc >= 'a' && cc <= 'z' ? (char) (cc - 32) : cc;
             }
             else {
                 oldValues[idx++] = c;


Test patch:
diff --git a/src/test/java/org/ttzero/excel/reader/ExcelReaderTest2.java b/src/test/java/org/ttzero/excel/reader/ExcelReaderTest2.java
index a484bf98..d97cf78e 100644
--- a/src/test/java/org/ttzero/excel/reader/ExcelReaderTest2.java
+++ b/src/test/java/org/ttzero/excel/reader/ExcelReaderTest2.java
@@ -18,12 +18,17 @@
 package org.ttzero.excel.reader;
 
 import org.junit.Test;
+import org.ttzero.excel.entity.ListMapSheet;
+import org.ttzero.excel.entity.Workbook;
 
 import java.io.IOException;
+import java.util.Arrays;
+import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.stream.Collectors;
 
+import static org.ttzero.excel.entity.WorkbookTest.defaultTestPath;
 import static org.ttzero.excel.reader.ExcelReaderTest.testResourceRoot;
 
 /**
@@ -58,4 +63,101 @@ public class ExcelReaderTest2 {
         }
     }
 
+    @Test public void testForceImport() throws IOException {
+        Map<String, Object> data1 = new HashMap<>();
+        data1.put("id", 1);
+        data1.put("name", "abc");
+
+        Map<String, Object> data2 = new HashMap<>();
+        data2.put("id", 2);
+        data2.put("name", "xyz");
+        new Workbook()
+            .addSheet(new ListMapSheet().setData(Arrays.asList(data1, data2)))
+            .writeTo(defaultTestPath.resolve("Force Import.xlsx"));
+
+        try (ExcelReader reader = ExcelReader.read(defaultTestPath.resolve("Force Import.xlsx"))) {
+            List<U> list = reader.sheet(0).forceImport().dataRows().map(row -> row.to(U.class)).collect(Collectors.toList());
+            assert list.size() == 2;
+            assert "1: abc".equals(list.get(0).toString());
+            assert "2: xyz".equals(list.get(1).toString());
+        }
+    }
+
+    @Test public void testUpperCaseRead() throws IOException {
+        Map<String, Object> data1 = new HashMap<>();
+        data1.put("ID", 1);
+        data1.put("NAME", "abc");
+
+        Map<String, Object> data2 = new HashMap<>();
+        data2.put("ID", 2);
+        data2.put("NAME", "xyz");
+
+        new Workbook()
+            .addSheet(new ListMapSheet(Arrays.asList(data1, data2)))
+            .writeTo(defaultTestPath.resolve("Upper case Reader test.xlsx"));
+
+        try (ExcelReader reader = ExcelReader.read(defaultTestPath.resolve("Upper case Reader test.xlsx"))) {
+            List<U> list = reader.sheet(0).forceImport().dataRows().map(row -> row.to(U.class)).collect(Collectors.toList());
+            assert list.size() == 2;
+            assert "0: null".equals(list.get(0).toString());
+            assert "0: null".equals(list.get(1).toString());
+
+            list = reader.sheet(0).reset().addHeaderColumnReadOption(HeaderRow.FORCE_IMPORT | HeaderRow.IGNORE_CASE)
+                .dataRows().map(row -> row.to(U.class)).collect(Collectors.toList());
+            assert list.size() == 2;
+            assert "1: abc".equals(list.get(0).toString());
+            assert "2: xyz".equals(list.get(1).toString());
+        }
+    }
+
+    @Test public void testCamelCaseRead() throws IOException {
+        Map<String, Object> data1 = new HashMap<>();
+        data1.put("USER_ID", 1);
+        data1.put("USER_NAME", "abc");
+
+        Map<String, Object> data2 = new HashMap<>();
+        data2.put("USER_ID", 2);
+        data2.put("USER_NAME", "xyz");
+
+        new Workbook()
+            .addSheet(new ListMapSheet(Arrays.asList(data1, data2)))
+            .writeTo(defaultTestPath.resolve("Underline case Reader test.xlsx"));
+
+        try (ExcelReader reader = ExcelReader.read(defaultTestPath.resolve("Underline case Reader test.xlsx"))) {
+            List<User> list = reader.sheet(0).forceImport().dataRows().map(row -> row.to(User.class)).collect(Collectors.toList());
+            assert list.size() == 2;
+            assert "0: null".equals(list.get(0).toString());
+            assert "0: null".equals(list.get(1).toString());
+
+            list = reader.sheet(0).reset().addHeaderColumnReadOption(HeaderRow.FORCE_IMPORT | HeaderRow.CAMEL_CASE)
+                .dataRows().map(row -> row.to(User.class)).collect(Collectors.toList());
+            assert list.size() == 2;
+            assert "1: abc".equals(list.get(0).toString());
+            assert "2: xyz".equals(list.get(1).toString());
+        }
+    }
+
+    public static class U {
+        int id;
+        String name;
+
+        public void setId(int id) {
+            this.id = id;
+        }
+
+        @Override
+        public String toString() {
+            return id + ": " + name;
+        }
+    }
+
+    public static class User {
+        int userId;
+        String userName;
+
+        @Override
+        public String toString() {
+            return userId + ": " + userName;
+        }
+    }
 }