AI专业者开发社区 1 号成员
  •  0 回帖  •  4 浏览  •  4 个月前
 

Java注解--实现简单读取excel

目录

实现工具类
基本使用

实现工具类

利用注解实现简单的 excel 数据读取，利用注解对类的属性和 excel 中的表头映射，使用 Apache 的 poi 就不用在业务代码中涉及 row,rows 这些属性了。

定义注解：

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel {

String <span class="hljs-title function_">name</span><span class="hljs-params">()</span>;



}

由于本例中只涉及根据 Excel 表头部分对 Excel 进行解析，只定义了一个 name 作为和 Excel 表头的隐射。

工具类完整代码如下：

public class ExcelUtil<T> {

Class&lt;T&gt; clazz;

<span class="hljs-keyword">public</span> <span class="hljs-title function_">ExcelUtil</span><span class="hljs-params">(Class&lt;T&gt; clazz)</span> {
    <span class="hljs-built_in">this</span>.clazz = clazz;
}

<span class="hljs-keyword">public</span> List&lt;T&gt; <span class="hljs-title function_">importExcel</span><span class="hljs-params">(String sheetName, InputStream input)</span> {
    <span class="hljs-type">int</span> <span class="hljs-variable">maxCol</span> <span class="hljs-operator">=</span> <span class="hljs-number">0</span>;
    List&lt;T&gt; list = <span class="hljs-keyword">new</span> <span class="hljs-title class_">ArrayList</span>&lt;T&gt;();
    <span class="hljs-keyword">try</span> {
        <span class="hljs-type">Workbook</span> <span class="hljs-variable">workbook</span> <span class="hljs-operator">=</span> WorkbookFactory.create(input);
        <span class="hljs-type">Sheet</span> <span class="hljs-variable">sheet</span> <span class="hljs-operator">=</span> workbook.getSheet(sheetName);
        <span class="hljs-comment">// 如果指定sheet名,则取指定sheet中的内容.</span>
        <span class="hljs-keyword">if</span> (!sheetName.trim().equals(<span class="hljs-string">""</span>)) {
            sheet = workbook.getSheet(sheetName);
        }
        <span class="hljs-comment">// 如果传入的sheet名不存在则默认指向第1个sheet.</span>
        <span class="hljs-keyword">if</span> (sheet == <span class="hljs-literal">null</span>) {
            sheet = workbook.getSheetAt(<span class="hljs-number">0</span>);
        }
        <span class="hljs-type">int</span> <span class="hljs-variable">rows</span> <span class="hljs-operator">=</span> sheet.getPhysicalNumberOfRows();
        <span class="hljs-comment">// 有数据时才处理</span>
        <span class="hljs-keyword">if</span> (rows &gt; <span class="hljs-number">0</span>) {
            List&lt;Field&gt; allFields = getMappedFiled(clazz, <span class="hljs-literal">null</span>);
            <span class="hljs-comment">// 定义一个map用于存放列的序号和field.</span>
            Map&lt;Integer, Field&gt; fieldsMap = <span class="hljs-keyword">new</span> <span class="hljs-title class_">HashMap</span>&lt;Integer, Field&gt;();
            <span class="hljs-comment">// 第一行为表头</span>
            <span class="hljs-type">Row</span> <span class="hljs-variable">rowHead</span> <span class="hljs-operator">=</span> sheet.getRow(<span class="hljs-number">0</span>);
            Map&lt;String, Integer&gt; cellMap = <span class="hljs-keyword">new</span> <span class="hljs-title class_">HashMap</span>&lt;&gt;();
            <span class="hljs-type">int</span> <span class="hljs-variable">cellNum</span> <span class="hljs-operator">=</span> rowHead.getPhysicalNumberOfCells();
            <span class="hljs-keyword">for</span> (<span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> <span class="hljs-number">0</span>; i &lt; cellNum; i++){
                cellMap.put(rowHead.getCell(i).getStringCellValue().toLowerCase(), i);
            }
            <span class="hljs-keyword">for</span> (Field field : allFields) {
                <span class="hljs-comment">// 将有注解的field存放到map中.</span>
                <span class="hljs-keyword">if</span> (field.isAnnotationPresent(Excel.class)) {
                    <span class="hljs-type">Excel</span> <span class="hljs-variable">attr</span> <span class="hljs-operator">=</span> field.getAnnotation(Excel.class);
                    <span class="hljs-comment">// 根据Name来获取相应的failed</span>
                    <span class="hljs-type">int</span> <span class="hljs-variable">col</span> <span class="hljs-operator">=</span> cellMap.get(attr.name().toLowerCase());
                    field.setAccessible(<span class="hljs-literal">true</span>);
                    fieldsMap.put(col, field);
                }
            }
            <span class="hljs-comment">// 从第2行开始取数据</span>
            <span class="hljs-keyword">for</span> (<span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> <span class="hljs-number">1</span>; i &lt; rows; i++) {
                <span class="hljs-type">Row</span> <span class="hljs-variable">row</span> <span class="hljs-operator">=</span> sheet.getRow(i);
                <span class="hljs-type">T</span> <span class="hljs-variable">entity</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
                <span class="hljs-keyword">for</span> (<span class="hljs-type">int</span> <span class="hljs-variable">j</span> <span class="hljs-operator">=</span> <span class="hljs-number">0</span>; j &lt; cellNum; j++) {
                    <span class="hljs-type">Cell</span> <span class="hljs-variable">cell</span> <span class="hljs-operator">=</span> row.getCell(j);
                    <span class="hljs-keyword">if</span> (cell == <span class="hljs-literal">null</span>) {
                        <span class="hljs-keyword">continue</span>;
                    }
                    <span class="hljs-type">int</span> <span class="hljs-variable">cellType</span> <span class="hljs-operator">=</span> cell.getCellType();
                    <span class="hljs-type">String</span> <span class="hljs-variable">c</span> <span class="hljs-operator">=</span> <span class="hljs-string">""</span>;
                    <span class="hljs-keyword">if</span> (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
                        <span class="hljs-type">DecimalFormat</span> <span class="hljs-variable">df</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">DecimalFormat</span>(<span class="hljs-string">"0"</span>);
                        c = df.format(cell.getNumericCellValue());
                    } <span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
                        c = String.valueOf(cell.getBooleanCellValue());
                    } <span class="hljs-keyword">else</span> {
                        c = cell.getStringCellValue();
                    }
                    <span class="hljs-keyword">if</span> (c == <span class="hljs-literal">null</span> || c.equals(<span class="hljs-string">""</span>)) {
                        <span class="hljs-keyword">continue</span>;
                    }
                    entity = (entity == <span class="hljs-literal">null</span> ? clazz.newInstance() : entity);
                    <span class="hljs-comment">// 从map中得到对应列的field.</span>
                    <span class="hljs-type">Field</span> <span class="hljs-variable">field</span> <span class="hljs-operator">=</span> fieldsMap.get(j);
                    <span class="hljs-keyword">if</span> (field == <span class="hljs-literal">null</span>) {
                        <span class="hljs-keyword">continue</span>;
                    }
                    <span class="hljs-comment">// 取得类型,并根据对象类型设置值.</span>
                    Class&lt;?&gt; fieldType = field.getType();
                    <span class="hljs-keyword">if</span> (String.class == fieldType) {
                        field.set(entity, String.valueOf(c));
                    } <span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> ((Integer.TYPE == fieldType)
                            || (Integer.class == fieldType)) {
                        field.set(entity, Integer.valueOf(c));
                    } <span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> ((Long.TYPE == fieldType)
                            || (Long.class == fieldType)) {
                        field.set(entity, Long.valueOf(c));
                    } <span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> ((Float.TYPE == fieldType)
                            || (Float.class == fieldType)) {
                        field.set(entity, Float.valueOf(c));
                    } <span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> ((Short.TYPE == fieldType)
                            || (Short.class == fieldType)) {
                        field.set(entity, Short.valueOf(c));
                    } <span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> ((Double.TYPE == fieldType)
                            || (Double.class == fieldType)) {
                        field.set(entity, Double.valueOf(c));
                    } <span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> (Character.TYPE == fieldType) {
                        <span class="hljs-keyword">if</span> (c.length() &gt; <span class="hljs-number">0</span>) {
                            field.set(entity, c.charAt(<span class="hljs-number">0</span>));
                        }
                    }
                }
                <span class="hljs-keyword">if</span> (entity != <span class="hljs-literal">null</span>) {
                    list.add(entity);
                }
            }
        }
    } <span class="hljs-keyword">catch</span> (Exception e) {
        e.printStackTrace();
    }
    <span class="hljs-keyword">return</span> list;
}


<span class="hljs-comment">/**
 * 得到实体类所有通过注解映射了数据表的字段
 *
 * <span class="hljs-doctag">@param</span> clazz
 * <span class="hljs-doctag">@param</span> fields
 * <span class="hljs-doctag">@return</span>
 */</span>
<span class="hljs-keyword">private</span> List&lt;Field&gt; <span class="hljs-title function_">getMappedFiled</span><span class="hljs-params">(Class clazz, List&lt;Field&gt; fields)</span> {
    <span class="hljs-keyword">if</span> (fields == <span class="hljs-literal">null</span>) {
        fields = <span class="hljs-keyword">new</span> <span class="hljs-title class_">ArrayList</span>&lt;Field&gt;();
    }
    <span class="hljs-comment">// 得到所有定义字段</span>
    Field[] allFields = clazz.getDeclaredFields();
    <span class="hljs-comment">// 得到所有field并存放到一个list中.</span>
    <span class="hljs-keyword">for</span> (Field field : allFields) {
        <span class="hljs-keyword">if</span> (field.isAnnotationPresent(Excel.class)) {
            fields.add(field);
        }
    }
    <span class="hljs-keyword">if</span> (clazz.getSuperclass() != <span class="hljs-literal">null</span>
            &amp;&amp; !clazz.getSuperclass().equals(Object.class)) {
        getMappedFiled(clazz.getSuperclass(), fields);
    }

    <span class="hljs-keyword">return</span> fields;
}



}

代码很简单，获取 sheet，解析第一行，并和实体类标有注解的字段一一对应，用 hashMap 记录下来，然后循环取得 Excel 中剩下所有的数据，根据 map 的对应关系将值 set 到对应字段。

基本使用

待解析表格如下：

定义实体类:

public class User {

<span class="hljs-meta">@Excel(name = "filed1")</span>
<span class="hljs-keyword">private</span> String name;
<span class="hljs-meta">@Excel(name = "filed2")</span>
<span class="hljs-keyword">private</span> String nameEn;
<span class="hljs-meta">@Excel(name = "filed3")</span>
<span class="hljs-keyword">private</span> Integer age;
<span class="hljs-meta">@Excel(name = "filed4")</span>
<span class="hljs-keyword">private</span> String six;
<span class="hljs-meta">@Excel(name = "filed5")</span>
<span class="hljs-keyword">private</span> String weight;

<span class="hljs-comment">// ...getter setter</span>



}

使用工具类：

public static void main (String[] args) {
    FileInputStream fileInputStream = null;
    try {
        fileInputStream = new FileInputStream("D://data.xlsx");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    ExcelUtil<User> util = new ExcelUtil<>(User.class);
    List<User> jalanHotelList = util.importExcel("user", fileInputStream);
    // do something
}

利用这个思路可以扩展出导出 excel 功能，利用注解指定导出的 excel 表头，甚至可以轻松控制 excel 表头的颜色，合并属性等等，在 xdemo 中有详细复杂的示例，可以研究下。由于我的需求很简单，就不整那么复杂啦。