Repository: wangguanquan/eec
Pull request #78 (2019-09-25 12:49:35)

Fix#75
Support get cell value by name



Patch:
diff --git a/README.md b/README.md
index 6a9c1703..7a0be5b6 100755
--- a/README.md
+++ b/README.md
@@ -391,7 +391,7 @@ public void readToList() {
 #### 4. 当然既然是stream那么就可以使用流的全部功能，比如加一些过滤和聚合等。
 
 ```
-reade.sheets()
+reader.sheets()
     .flatMap(Sheet::dataRows)
     .map(row -> row.to(Regist.class))
     .filter(e -> "iOS".equals(e.platform()))
@@ -400,6 +400,26 @@ reade.sheets()
 
 以上代码相当于`select * from 用户注册 where platform = 'iOS'`
 
+
+#### 4.1 根据列名过滤列
+
+```
+@Test public void testFilter() {
+    try (ExcelReader reader = ExcelReader.read(testResourceRoot().resolve("1.xlsx"))) {
+        String[] games = reader.sheet(0)
+            .dataRows()
+            .map(row -> row.getString("游戏"))
+            .distinct()
+            .toArray(String[]::new);
+        print(Arrays.toString(games));
+    } catch (IOException e) {
+        e.printStackTrace();
+    }
+}
+```
+
+以上方法获取excel文件的"游戏"列的不重复值
+
 #### 5. xls读取
 xls读取对方法式与xlsx完全一致
 
diff --git a/src/main/java/org/ttzero/excel/reader/HeaderRow.java b/src/main/java/org/ttzero/excel/reader/HeaderRow.java
index 27931129..2863a5a6 100644
--- a/src/main/java/org/ttzero/excel/reader/HeaderRow.java
+++ b/src/main/java/org/ttzero/excel/reader/HeaderRow.java
@@ -29,6 +29,7 @@ import java.lang.reflect.Field;
 import java.lang.reflect.InvocationTargetException;
 import java.lang.reflect.Method;
 import java.util.Arrays;
+import java.util.HashMap;
 import java.util.LinkedHashMap;
 import java.util.Map;
 import java.util.StringJoiner;
@@ -36,7 +37,6 @@ import java.util.StringJoiner;
 import static org.ttzero.excel.util.ReflectUtil.listDeclaredFields;
 import static org.ttzero.excel.util.ReflectUtil.listWriteMethods;
 import static org.ttzero.excel.util.ReflectUtil.mapping;
-import static org.ttzero.excel.util.StringUtil.indexOf;
 import static org.ttzero.excel.util.StringUtil.isNotEmpty;
 
 /**
@@ -50,14 +50,18 @@ class HeaderRow extends Row {
     private int[] columns;
     private Class<?>[] fieldClazz;
     private Object t;
+    /* The column name and column position mapping */
+    private Map<String, Integer> mapping;
 
     private HeaderRow() { }
 
     static HeaderRow with(Row row) {
         HeaderRow hr = new HeaderRow();
         hr.names = new String[row.lc];
+        hr.mapping = new HashMap<>();
         for (int i = row.fc; i < row.lc; i++) {
             hr.names[i] = row.getString(i);
+            hr.mapping.put(hr.names[i], i);
         }
         // Extends from row
         hr.fc = row.fc;
@@ -157,8 +161,8 @@ class HeaderRow extends Row {
             // Annotation value is null
             else if (ec != null || ano != null || methods[i] != null) {
                 String name = f.getName();
-                n = indexOf(names, name);
-                if (n == -1 && (n = indexOf(names, StringUtil.toPascalCase(name))) == -1) {
+                n = getIndex(name);
+                if (n == -1 && (n = getIndex(StringUtil.toPascalCase(name))) == -1) {
                     declaredFields[i] = null;
                     continue;
                 }
@@ -193,6 +197,8 @@ class HeaderRow extends Row {
             }
         }
 
+
+
         return this;
     }
 
@@ -217,8 +223,8 @@ class HeaderRow extends Row {
     }
 
     private int check(String first, String second) {
-        int n = indexOf(names, first);
-        if (n == -1) n = indexOf(names, second);
+        int n = getIndex(first);
+        if (n == -1) n = getIndex(second);
         if (n == -1) {
             logger.warn(clazz + " field [" + first + "] can't find in header" + Arrays.toString(names));
         }
@@ -266,16 +272,27 @@ class HeaderRow extends Row {
     }
 
     /**
-     * Get T value by column index
+     * Get the column name by column index
      *
      * @param columnIndex the cell index
-     * @return T
+     * @return name of column
      */
     public String get(int columnIndex) {
         rangeCheck(columnIndex);
         return names[columnIndex];
     }
 
+    /**
+     * Returns the position in cell range
+     *
+     * @param columnName the column name
+     * @return the position if found otherwise -1
+     */
+    public int getIndex(String columnName) {
+        Integer index = mapping.get(columnName);
+        return index != null ? index : -1;
+    }
+
     @Override
     public String toString() {
         StringJoiner joiner = new StringJoiner(" | ");
diff --git a/src/main/java/org/ttzero/excel/reader/Row.java b/src/main/java/org/ttzero/excel/reader/Row.java
index ee3654ac..8739dbfa 100644
--- a/src/main/java/org/ttzero/excel/reader/Row.java
+++ b/src/main/java/org/ttzero/excel/reader/Row.java
@@ -111,15 +111,32 @@ public abstract class Row {
     }
 
     protected void rangeCheck(int index) {
-        if (index >= lc)
+        if (index >= lc || index < 0)
             throw new IndexOutOfBoundsException(outOfBoundsMsg(index));
     }
 
+    /**
+     * Returns {@link Cell}
+     * @param i the position of cell
+     * @return the {@link Cell}
+     */
     protected Cell getCell(int i) {
         rangeCheck(i);
         return cells[i];
     }
 
+    /**
+     * Search {@link Cell} by column name
+     *
+     * @param name the column name
+     * @return the {@link Cell}
+     */
+    protected Cell getCell(String name) {
+        int i = hr.getIndex(name);
+        rangeCheck(i);
+        return cells[i];
+    }
+
     /**
      * convert row to header_row
      *
@@ -144,6 +161,27 @@ public abstract class Row {
      */
     public boolean getBoolean(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getBoolean(c);
+    }
+
+    /**
+     * Get boolean value by column name
+     *
+     * @param columnName the cell name
+     * @return boolean
+     */
+    public boolean getBoolean(String columnName) {
+        Cell c = getCell(columnName);
+        return getBoolean(c);
+    }
+
+    /**
+     * Get boolean value
+     *
+     * @param c the {@link Cell}
+     * @return boolean
+     */
+    protected boolean getBoolean(Cell c) {
         boolean v;
         switch (c.t) {
             case BOOL:
@@ -176,6 +214,27 @@ public abstract class Row {
      */
     public byte getByte(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getByte(c);
+    }
+
+    /**
+     * Get byte value by column name
+     *
+     * @param columnName the cell name
+     * @return byte
+     */
+    public byte getByte(String columnName) {
+        Cell c = getCell(columnName);
+        return getByte(c);
+    }
+
+    /**
+     * Get byte value
+     *
+     * @param c the {@link Cell}
+     * @return byte
+     */
+    protected byte getByte(Cell c) {
         byte b = 0;
         switch (c.t) {
             case NUMERIC:
@@ -203,6 +262,27 @@ public abstract class Row {
      */
     public char getChar(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getChar(c);
+    }
+
+    /**
+     * Get char value by column name
+     *
+     * @param columnName the cell name
+     * @return char
+     */
+    public char getChar(String columnName) {
+        Cell c = getCell(columnName);
+        return getChar(c);
+    }
+
+    /**
+     * Get char value
+     *
+     * @param c the {@link Cell}
+     * @return char
+     */
+    protected char getChar(Cell c) {
         char cc = 0;
         switch (c.t) {
             case SST:
@@ -245,6 +325,27 @@ public abstract class Row {
      */
     public short getShort(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getShort(c);
+    }
+
+    /**
+     * Get short value by column name
+     *
+     * @param columnName the cell name
+     * @return short
+     */
+    public short getShort(String columnName) {
+        Cell c = getCell(columnName);
+        return getShort(c);
+    }
+
+    /**
+     * Get short value
+     *
+     * @param c the {@link Cell}
+     * @return short
+     */
+    protected short getShort(Cell c) {
         short s = 0;
         switch (c.t) {
             case NUMERIC:
@@ -272,6 +373,27 @@ public abstract class Row {
      */
     public int getInt(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getInt(c);
+    }
+
+    /**
+     * Get int value by column name
+     *
+     * @param columnName the cell name
+     * @return int
+     */
+    public int getInt(String columnName) {
+        Cell c = getCell(columnName);
+        return getInt(c);
+    }
+
+    /**
+     * Get int value
+     *
+     * @param c the {@link Cell}
+     * @return int
+     */
+    protected int getInt(Cell c) {
         int n;
         switch (c.t) {
             case NUMERIC:
@@ -317,6 +439,27 @@ public abstract class Row {
      */
     public long getLong(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getLong(c);
+    }
+
+    /**
+     * Get long value by column name
+     *
+     * @param columnName the cell name
+     * @return long
+     */
+    public long getLong(String columnName) {
+        Cell c = getCell(columnName);
+        return getLong(c);
+    }
+
+    /**
+     * Get long value
+     *
+     * @param c the {@link Cell}
+     * @return long
+     */
+    protected long getLong(Cell c) {
         long l;
         switch (c.t) {
             case LONG:
@@ -361,6 +504,27 @@ public abstract class Row {
      */
     public String getString(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getString(c);
+    }
+
+    /**
+     * Get string value by column name
+     *
+     * @param columnName the cell name
+     * @return string
+     */
+    public String getString(String columnName) {
+        Cell c = getCell(columnName);
+        return getString(c);
+    }
+
+    /**
+     * Get string value
+     *
+     * @param c the {@link Cell}
+     * @return string
+     */
+    protected String getString(Cell c) {
         String s;
         switch (c.t) {
             case SST:
@@ -402,6 +566,16 @@ public abstract class Row {
         return (float) getDouble(columnIndex);
     }
 
+    /**
+     * Get float value by column index
+     *
+     * @param columnName the cell index
+     * @return float
+     */
+    public float getFloat(String columnName) {
+        return (float) getDouble(columnName);
+    }
+
     /**
      * Get double value by column index
      *
@@ -410,6 +584,27 @@ public abstract class Row {
      */
     public double getDouble(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getDouble(c);
+    }
+
+    /**
+     * Get double value by column name
+     *
+     * @param columnName the cell name
+     * @return double
+     */
+    public double getDouble(String columnName) {
+        Cell c = getCell(columnName);
+        return getDouble(c);
+    }
+
+    /**
+     * Get double value
+     *
+     * @param c the {@link Cell}
+     * @return double
+     */
+    protected double getDouble(Cell c) {
         double d;
         switch (c.t) {
             case DOUBLE:
@@ -446,6 +641,27 @@ public abstract class Row {
      */
     public BigDecimal getDecimal(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getDecimal(c);
+    }
+
+    /**
+     * Get BigDecimal value by column name
+     *
+     * @param columnName the cell name
+     * @return BigDecimal
+     */
+    public BigDecimal getDecimal(String columnName) {
+        Cell c = getCell(columnName);
+        return getDecimal(c);
+    }
+
+    /**
+     * Get BigDecimal value
+     *
+     * @param c the {@link Cell}
+     * @return BigDecimal
+     */
+    protected BigDecimal getDecimal(Cell c) {
         BigDecimal bd;
         switch (c.t) {
             case DOUBLE:
@@ -468,6 +684,27 @@ public abstract class Row {
      */
     public Date getDate(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getDate(c);
+    }
+
+    /**
+     * Get date value by column name
+     *
+     * @param columnName the cell name
+     * @return Date
+     */
+    public Date getDate(String columnName) {
+        Cell c = getCell(columnName);
+        return getDate(c);
+    }
+
+    /**
+     * Get date value
+     *
+     * @param c the {@link Cell}
+     * @return BigDecimal
+     */
+    protected Date getDate(Cell c) {
         Date date;
         switch (c.t) {
             case NUMERIC:
@@ -498,6 +735,27 @@ public abstract class Row {
      */
     public Timestamp getTimestamp(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getTimestamp(c);
+    }
+
+    /**
+     * Get timestamp value by column name
+     *
+     * @param columnName the cell name
+     * @return java.sql.Timestamp
+     */
+    public Timestamp getTimestamp(String columnName) {
+        Cell c = getCell(columnName);
+        return getTimestamp(c);
+    }
+
+    /**
+     * Get timestamp value
+     *
+     * @param c the {@link Cell}
+     * @return java.sql.Timestamp
+     */
+    protected Timestamp getTimestamp(Cell c) {
         Timestamp ts;
         switch (c.t) {
             case NUMERIC:
@@ -534,6 +792,20 @@ public abstract class Row {
         throw new UncheckedTypeException("can't convert to java.sql.Time");
     }
 
+    /**
+     * Get time value by column name
+     *
+     * @param columnName the cell name
+     * @return java.sql.Time
+     */
+    public java.sql.Time getTime(String columnName) {
+        Cell c = getCell(columnName);
+        if (c.t == DOUBLE) {
+            return DateUtil.toTime(c.dv);
+        }
+        throw new UncheckedTypeException("can't convert to java.sql.Time");
+    }
+
     /**
      * Returns the type of cell
      *
@@ -542,6 +814,27 @@ public abstract class Row {
      */
     public CellType getCellType(int columnIndex) {
         Cell c = getCell(columnIndex);
+        return getCellType(c);
+    }
+
+    /**
+     * Returns the type of cell
+     *
+     * @param columnName the cell name
+     * @return the {@link CellType}
+     */
+    public CellType getCellType(String columnName) {
+        Cell c = getCell(columnName);
+        return getCellType(c);
+    }
+
+    /**
+     * Returns the type of cell
+     *
+     * @param c the {@link Cell}
+     * @return the {@link CellType}
+     */
+    protected CellType getCellType(Cell c) {
         CellType type;
         switch (c.t) {
             case SST:
@@ -570,7 +863,7 @@ public abstract class Row {
                 type = CellType.BLANK;
                 break;
 
-                default: type = CellType.STRING;
+            default: type = CellType.STRING;
         }
         return type;
     }
diff --git a/src/main/java/org/ttzero/excel/reader/XMLSheet.java b/src/main/java/org/ttzero/excel/reader/XMLSheet.java
index 14e1ab10..64901ca9 100644
--- a/src/main/java/org/ttzero/excel/reader/XMLSheet.java
+++ b/src/main/java/org/ttzero/excel/reader/XMLSheet.java
@@ -427,6 +427,7 @@ class XMLSheet implements Sheet {
     public XMLSheet reset() throws IOException {
         // Close the opening reader
         close();
+        header = null;
         // Reload
         return load();
     }


Test patch:
diff --git a/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java b/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java
index ad268cc1..007a861c 100644
--- a/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java
+++ b/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java
@@ -28,6 +28,7 @@ import java.io.IOException;
 import java.net.URL;
 import java.nio.file.Path;
 import java.nio.file.Paths;
+import java.util.Arrays;
 import java.util.Date;
 import java.util.Iterator;
 
@@ -150,6 +151,35 @@ public class ExcelReaderTest {
         }
     }
 
+    @Test public void testReaderByName() {
+        try (ExcelReader reader = ExcelReader.read(testResourceRoot().resolve("1.xlsx"))) {
+            reader.sheet(0).dataIterator().forEachRemaining(row -> {
+                print(row.getInt("渠道ID")); print(" | ");
+                print(row.getString("游戏")); print(" | ");
+                print(row.getString("account")); print(" | ");
+                print(row.getDate("注册时间")); print(" | ");
+                print(row.getBoolean("是否满30级")); print(" | ");
+                print(row.getChar("VIP"));
+                println();
+            });
+        } catch (IOException e) {
+            e.printStackTrace();
+        }
+    }
+
+    @Test public void testFilter() {
+        try (ExcelReader reader = ExcelReader.read(testResourceRoot().resolve("1.xlsx"))) {
+            String[] games = reader.sheet(0)
+                .dataRows()
+                .map(row -> row.getString("游戏"))
+                .distinct()
+                .toArray(String[]::new);
+            print(Arrays.toString(games));
+        } catch (IOException e) {
+            e.printStackTrace();
+        }
+    }
+
     public static class Entry {
         @ExcelColumn("渠道ID")
         private Integer channelId;