智慧城市>> easy-excel>> 返回
项目作者： freegeese

项目描述 ：
像SQL一样操作Excel，简化Excel的读写操作
高级语言： Java
项目地址: git://github.com/freegeese/easy-excel.git
创建时间： 2016-11-17T11:54:55Z
项目社区：https://github.com/freegeese/easy-excel
开源协议：
下载


像SQL一样操作Excel

  • 简化对Excel的读写操作
  • 尽可能的实现能够像SQL一样操作来操作Excel

快速上手

对Excel的简单read，使用SimpleReader类完成

  1. package com.geese.plugin.excelMapping.test;
  3. import com.geese.plugin.excelMapping.filter.read.CellAfterReadFilter;
  4. import com.geese.plugin.excelMapping.filter.read.RowAfterReadFilter;
  5. import com.geese.plugin.excelMapping.SimpleReader;
  6. import com.geese.plugin.excelMapping.SimpleWriter;
  7. import com.geese.plugin.excelMapping.config.Point;
  8. import com.geese.plugin.excelMapping.config.Table;
  9. import com.geese.plugin.excelMapping.filter.read.CellBeforeReadFilter;
  10. import com.geese.plugin.excelMapping.filter.read.RowBeforeReadFilter;
  11. import org.apache.poi.ss.usermodel.Cell;
  12. import org.apache.poi.ss.usermodel.Row;
  13. import org.junit.AfterClass;
  14. import org.junit.BeforeClass;
  15. import org.junit.Test;
  17. import java.io.FileInputStream;
  18. import java.io.FileOutputStream;
  19. import java.io.IOException;
  20. import java.io.InputStream;
  21. import java.net.URL;
  22. import java.util.*;
  24. /**
  25.  * SimpleReader 接口测试
  26.  *
  27.  * @author zhangguangyong <a href="#">1243610991@qq.com</a>
  28.  * @date 2016/11/16 18:05
  29.  * @sine 0.0.1
  30.  */
  31. public class SimpleReaderTest {
  33.     private static InputStream input;
  35.     @BeforeClass
  36.     public static void beforeClass() throws IOException {
  37.         URL url = Thread.currentThread().getContextClassLoader().getResource("demo-reader.xlsx");
  38.         // 准备数据
  39.         // Excel 表头： 姓名    | 年龄 | 身份证号    | QQ | 邮箱 | 手机
  40.         // 准备数据, 每一行是一个Map, 每一个表格是一个List<Map>
  41.         String names = "鲁沛儿 鲁天薇 鲁飞雨 鲁天纵 鲁白梦 鲁嘉胜 鲁盼巧 鲁访天 鲁清妍 鲁盼晴 张馨蓉 张白萱 张若云 张雅畅 张雅寒 张雨华";
  42.         List<Map> tableData = new ArrayList<>();
  43.         Map rowData;
  44.         for (String name : names.split("\\s+")) {
  45.             rowData = new HashMap();
  46.             rowData.put("name", name);
  47.             rowData.put("age", Double.valueOf(Math.random() * 100).intValue());
  48.             rowData.put("idCard", Double.valueOf(Math.random() * 1000000000).longValue());
  49.             rowData.put("qq", Double.valueOf(Math.random() * 1000000000).longValue());
  50.             rowData.put("email", Double.valueOf(Math.random() * 1000000000).longValue() + "@163.com");
  51.             rowData.put("phone", Double.valueOf(Math.random() * 1000000000).longValue());
  52.             tableData.add(rowData);
  53.         }
  54.         // 构建一个输出流，向被读取的excel写入测试数据
  55.         FileOutputStream output = new FileOutputStream(url.getFile());
  56.         SimpleWriter.build(output)
  57.                 .insert("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone")
  58.                 .limit(1)
  59.                 .addData(tableData)
  60.                 .execute();
  61.         output.flush();
  62.         output.close();
  64.         // 构建一个输入流，读取excel数据
  65.         input = new FileInputStream(url.getFile());
  66.     }
  68.     @AfterClass
  69.     public static void afterClass() throws IOException {
  70.         if (null != input) {
  71.             input.close();
  72.         }
  73.     }
  75.     /**
  76.      * 实例1：快速使用
  77.      */
  78.     @Test
  79.     public void test001() {
  80.         Collection result = SimpleReader.build(input)
  81.                 .select("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone")
  82.                 .execute();
  83.         System.out.println(result);
  84.     }
  86.     /**
  87.      * 实力2：可选配置
  88.      */
  89.     @Test
  90.     public void test002() {
  91.         Collection result = SimpleReader.build(input)
  92.                 .select("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone")
  93.                 .from("0")      // 可选（默认是从第0个sheet读取, [如果是数字会优先使用名称获取sheet，没找到才会使用下标获取sheet]）
  94.                 .limit(3, 5)    // 可选（默认从定义0行开始读取，读取所有行）
  95.                 .execute();
  96.         System.out.println(result.size());
  97.     }
  99.     /**
  100.      * 实例3：where条件帅选，使用占位符参数
  101.      */
  102.     @Test
  103.     public void test003() {
  104.         // 占位符参数
  105.         Collection result = SimpleReader.build(input)
  106.                 .select("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone")
  107.                 .where("name like ? and (age > ? and qq like ?) or name in ?")
  108.                 .addParameter(Arrays.asList("%鲁%", 50, "5%", Arrays.asList("张白萱", "张若云", "张雅畅", "张雅寒", "张雨华")))
  109.                 .execute();
  110.         System.out.println(result.size());
  111.     }
  113.     /**
  114.      * 实例4：where条件帅选，使用命名的参数
  115.      */
  116.     @Test
  117.     public void test004() {
  118.         // 命名的参数
  119.         Map namedParameter = new HashMap();
  120.         namedParameter.put("name", "%鲁%");
  121.         namedParameter.put("age", 26);
  122.         namedParameter.put("qq", "5%");
  123.         namedParameter.put("names", Arrays.asList("张白萱", "张若云", "张雅畅", "张雅寒", "张雨华"));
  124.         Collection result = SimpleReader.build(input)
  125.                 .select("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone")
  126.                 .where("name like :name and (age > :age and qq like :qq) or name in :names")
  127.                 .addParameter(namedParameter)
  128.                 .execute();
  129.         System.out.println(result.size());
  130.     }
  132.     /**
  133.      * 实例5：过滤器
  134.      */
  135.     @Test
  136.     public void test005() {
  137.         Collection result = SimpleReader.build(input)
  138.                 .select("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone")
  139.                 .addFilter(new RowBeforeReadFilter() {
  140.                     // 可以对 row 进行修改
  141.                     @Override
  142.                     public void doFilter(Row target, Object data, Table config) {
  143.                         System.out.println("<<<<<<<<<<<<<<<<读取行之前过滤：" + data);
  144.                     }
  145.                 }, new RowAfterReadFilter() {
  146.                     // 可以对 data 进行修改
  147.                     @Override
  148.                     public void doFilter(Row target, Object data, Table config) {
  149.                         System.out.println("读取行之后过滤：" + data + ">>>>>>>>>>>>>>>>>");
  150.                     }
  151.                 }, new CellBeforeReadFilter() {
  152.                     // 可以对 cell 进行修改
  153.                     @Override
  154.                     public void doFilter(Cell target, Object data, Point config) {
  155.                         System.out.println("<<<<<<<<<<<<<<<<读单元格之前过滤：" + data);
  156.                     }
  157.                 }, new CellAfterReadFilter() {
  158.                     // 可以对 data 进行修改
  159.                     @Override
  160.                     public void doFilter(Cell target, Object data, Point config) {
  161.                         System.out.println("读单元格之后过滤：" + data + ">>>>>>>>>>>>>>>>>");
  162.                     }
  163.                 })
  164.                 .execute();
  165.         System.out.println(result);
  166.     }
  167. }

对Excel的简单write，使用SimpleWriter类完成

  1. package com.geese.plugin.excelMapping.test;
  3. import com.geese.plugin.excelMapping.SimpleWriter;
  4. import com.geese.plugin.excelMapping.config.Point;
  5. import com.geese.plugin.excelMapping.config.Table;
  6. import com.geese.plugin.excelMapping.filter.CellWriteFilter;
  7. import com.geese.plugin.excelMapping.filter.RowWriteFilter;
  8. import org.apache.poi.ss.usermodel.Cell;
  9. import org.apache.poi.ss.usermodel.Row;
  10. import org.junit.BeforeClass;
  11. import org.junit.Test;
  13. import java.io.*;
  14. import java.net.URL;
  15. import java.util.ArrayList;
  16. import java.util.HashMap;
  17. import java.util.List;
  18. import java.util.Map;
  20. /**
  21.  * SimpleWriter 接口测试
  22.  *
  23.  * @author zhangguangyong <a href="#">1243610991@qq.com</a>
  24.  * @date 2016/11/16 18:06
  25.  * @sine 0.0.1
  26.  */
  27. public class SimpleWriterTest {
  28.     private static OutputStream output;
  29.     private static InputStream template;
  31.     @BeforeClass
  32.     public static void beforeClass() throws IOException {
  33.         // 输出
  34.         URL url = Thread.currentThread().getContextClassLoader().getResource("demo-writer.xlsx");
  35.         output = new FileOutputStream(url.getFile());
  37.         // 模板
  38.         url = Thread.currentThread().getContextClassLoader().getResource("demo-writer-template.xlsx");
  39.         template = new FileInputStream(url.getFile());
  40.     }
  42.     /**
  43.      * 实例1：快速使用
  44.      */
  45.     @Test
  46.     public void test001() {
  47.         // Excel 表头： 姓名    | 年龄 | 身份证号    | QQ | 邮箱 | 手机
  48.         // 准备数据, 每一行是一个Map, 每一个表格是一个List<Map>
  49.         String names = "鲁沛儿 鲁天薇 鲁飞雨 鲁天纵 鲁白梦 鲁嘉胜 鲁盼巧 鲁访天 鲁清妍 鲁盼晴 张馨蓉 张白萱 张若云 张雅畅 张雅寒 张雨华";
  50.         List<Map> tableData = new ArrayList<>();
  51.         Map rowData;
  52.         for (String name : names.split("\\s+")) {
  53.             rowData = new HashMap();
  54.             rowData.put("name", name);
  55.             rowData.put("age", Double.valueOf(Math.random() * 100).intValue());
  56.             rowData.put("idCard", Double.valueOf(Math.random() * 1000000000).longValue());
  57.             rowData.put("qq", Double.valueOf(Math.random() * 1000000000).longValue());
  58.             rowData.put("email", Double.valueOf(Math.random() * 1000000000).longValue() + "@163.com");
  59.             rowData.put("phone", Double.valueOf(Math.random() * 1000000000).longValue());
  60.             tableData.add(rowData);
  61.         }
  62.         // 通过SimpleWriter类操作
  63.         SimpleWriter.build(output)  // 必选，将生成的excel输出到什么地方
  64.                 .insert("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone")  // 必选，数据Key与Excel列的映射
  65.                 .addData(tableData) // 必选，插入的数据
  66.                 .execute(); // 执行
  67.     }
  69.     /**
  70.      * 实例2：使用可选配置
  71.      */
  72.     @Test
  73.     public void test002() {
  74.         // Excel 表头： 姓名    | 年龄 | 身份证号    | QQ | 邮箱 | 手机
  75.         // 准备数据, 每一行是一个Map, 每一个表格是一个List<Map>
  76.         String names = "鲁沛儿 鲁天薇 鲁飞雨 鲁天纵 鲁白梦 鲁嘉胜 鲁盼巧 鲁访天 鲁清妍 鲁盼晴 张馨蓉 张白萱 张若云 张雅畅 张雅寒 张雨华";
  77.         List<Map> tableData = new ArrayList<>();
  78.         Map rowData;
  79.         for (String name : names.split("\\s+")) {
  80.             rowData = new HashMap();
  81.             rowData.put("name", name);
  82.             rowData.put("age", Double.valueOf(Math.random() * 100).intValue());
  83.             rowData.put("idCard", Double.valueOf(Math.random() * 1000000000).longValue());
  84.             rowData.put("qq", Double.valueOf(Math.random() * 1000000000).longValue());
  85.             rowData.put("email", Double.valueOf(Math.random() * 1000000000).longValue() + "@163.com");
  86.             rowData.put("phone", Double.valueOf(Math.random() * 1000000000).longValue());
  87.             tableData.add(rowData);
  88.         }
  89.         // 通过SimpleWriter类操作
  90.         SimpleWriter.build(output)  // 必选，将生成的excel输出到什么地方
  91.                 .insert("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone")  // 必选，数据Key与Excel列的映射
  92.                 .into("0xx")  // 可选（默认：插入到第0个sheet, 可以指定名称，比如：xx数据报表）
  93.                 .limit(0, 10) // 可选（参数1：从哪行开始插入，参数2：插入多少航，默认：0,tableData.size()）
  94.                 .addData(tableData) // 必选，插入的数据
  95.                 .execute(); // 执行
  96.     }
  98.     /**
  99.      * 实例3：使用单元格过滤器和行过滤器
  100.      */
  101.     @Test
  102.     public void test003() {
  103.         // Excel 表头： 姓名    | 年龄 | 身份证号    | QQ | 邮箱 | 手机
  104.         // 准备数据, 每一行是一个Map, 每一个表格是一个List<Map>
  105.         String names = "鲁沛儿 鲁天薇 鲁飞雨 鲁天纵 鲁白梦 鲁嘉胜 鲁盼巧 鲁访天 鲁清妍 鲁盼晴 张馨蓉 张白萱 张若云 张雅畅 张雅寒 张雨华";
  106.         List<Map> tableData = new ArrayList<>();
  107.         Map rowData;
  108.         for (String name : names.split("\\s+")) {
  109.             rowData = new HashMap();
  110.             rowData.put("name", name);
  111.             rowData.put("age", Double.valueOf(Math.random() * 100).intValue());
  112.             rowData.put("idCard", Double.valueOf(Math.random() * 1000000000).longValue());
  113.             rowData.put("qq", Double.valueOf(Math.random() * 1000000000).longValue());
  114.             rowData.put("email", Double.valueOf(Math.random() * 1000000000).longValue() + "@163.com");
  115.             rowData.put("phone", Double.valueOf(Math.random() * 1000000000).longValue());
  116.             tableData.add(rowData);
  117.         }
  118.         /**
  119.          * 写入到row之前过滤，可对row和data进行修改
  120.          */
  121.         RowWriteFilter rowWriteFilter = new RowWriteFilter() {
  122.             @Override
  123.             public void doFilter(Row target, Object data, Table config) {
  124.                 System.out.println(data);
  125.             }
  126.         };
  127.         /**
  128.          * 写入到cell之前过滤，可对cell和data进行修改
  129.          */
  130.         CellWriteFilter cellWriteFilter = new CellWriteFilter() {
  131.             @Override
  132.             public void doFilter(Cell target, Object data, Point config) {
  133.                 System.out.println(data);
  134.             }
  135.         };
  136.         // 通过SimpleWriter类操作
  137.         SimpleWriter.build(output)  // 必选，将生成的excel输出到什么地方
  138.                 .insert("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone")  // 必选，数据Key与Excel列的映射
  139.                 .addFilter(rowWriteFilter, cellWriteFilter)
  140.                 .addData(tableData) // 必选，插入的数据
  141.                 .execute(); // 执行
  142.     }
  144.     /**
  145.      * 实例4：使用模板
  146.      */
  147.     @Test
  148.     public void test004() {
  149.         // Excel 表头： 姓名    | 年龄 | 身份证号    | QQ | 邮箱 | 手机
  150.         // 准备数据, 每一行是一个Map, 每一个表格是一个List<Map>
  151.         String names = "鲁沛儿 鲁天薇 鲁飞雨 鲁天纵 鲁白梦 鲁嘉胜 鲁盼巧 鲁访天 鲁清妍 鲁盼晴 张馨蓉 张白萱 张若云 张雅畅 张雅寒 张雨华";
  152.         List<Map> tableData = new ArrayList<>();
  153.         Map rowData;
  154.         for (String name : names.split("\\s+")) {
  155.             rowData = new HashMap();
  156.             rowData.put("name", name);
  157.             rowData.put("age", Double.valueOf(Math.random() * 100).intValue());
  158.             rowData.put("idCard", Double.valueOf(Math.random() * 1000000000).longValue());
  159.             rowData.put("qq", Double.valueOf(Math.random() * 1000000000).longValue());
  160.             rowData.put("email", Double.valueOf(Math.random() * 1000000000).longValue() + "@163.com");
  161.             rowData.put("phone", Double.valueOf(Math.random() * 1000000000).longValue());
  162.             tableData.add(rowData);
  163.         }
  164.         // 通过SimpleWriter类操作
  165.         SimpleWriter.build(output, template)  // 必选，将按照模板去生成excel
  166.                 .insert("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone")  // 必选，数据Key与Excel列的映射
  167.                 .limit(1)
  168.                 .addData(tableData) // 必选，插入的数据
  169.                 .execute(); // 执行
  170.     }
  171. }

接口说明

  • 暂无

高级部分

StandardReader 接口使用

  1. package com.geese.plugin.excelMapping.test;
  3. import com.geese.plugin.excelMapping.StandardReader;
  4. import com.geese.plugin.excelMapping.StandardWriter;
  5. import com.geese.plugin.excelMapping.config.Point;
  6. import com.geese.plugin.excelMapping.config.Table;
  7. import com.geese.plugin.excelMapping.filter.read.CellAfterReadFilter;
  8. import com.geese.plugin.excelMapping.filter.read.CellBeforeReadFilter;
  9. import com.geese.plugin.excelMapping.filter.read.RowAfterReadFilter;
  10. import com.geese.plugin.excelMapping.filter.read.RowBeforeReadFilter;
  11. import org.apache.poi.ss.usermodel.Cell;
  12. import org.apache.poi.ss.usermodel.Row;
  13. import org.junit.BeforeClass;
  14. import org.junit.Test;
  16. import java.io.FileInputStream;
  17. import java.io.FileOutputStream;
  18. import java.io.IOException;
  19. import java.io.InputStream;
  20. import java.net.URL;
  21. import java.util.*;
  23. /**
  24.  * <p> 标准Excel读取接口测试 <br>
  25.  *
  26.  * @author zhangguangyong <a href="#">1243610991@qq.com</a>
  27.  * @date 2016/11/16 22:36
  28.  * @sine 0.0.1
  29.  */
  30. public class StandardReaderTest {
  31.     static InputStream input;
  33.     @BeforeClass
  34.     public static void beforeClass() throws IOException {
  35.         URL url = Thread.currentThread().getContextClassLoader().getResource("demo-reader.xlsx");
  36.         // 准备数据
  37.         // Excel 表头： 姓名    | 年龄 | 身份证号    | QQ | 邮箱 | 手机
  38.         // 准备数据, 每一行是一个Map, 每一个表格是一个List<Map>
  39.         String[] names = "鲁沛儿 鲁天薇 鲁飞雨 鲁天纵 鲁白梦 鲁嘉胜 鲁盼巧 鲁访天 鲁清妍 鲁盼晴 张馨蓉 张白萱 张若云 张雅畅 张雅寒 张雨华".split("\\s+");
  40.         List<Map> tableData = new ArrayList<>();
  41.         Map rowData;
  42.         for (int i = 0; i < 100; i++) {
  43.             rowData = new HashMap();
  44.             rowData.put("name", names[Double.valueOf(Math.random() * names.length).intValue()]);
  45.             rowData.put("age", Double.valueOf(Math.random() * 100).intValue());
  46.             rowData.put("idCard", Double.valueOf(Math.random() * 1000000000).longValue());
  47.             rowData.put("qq", Double.valueOf(Math.random() * 1000000000).longValue());
  48.             rowData.put("email", Double.valueOf(Math.random() * 1000000000).longValue() + "@163.com");
  49.             rowData.put("phone", Double.valueOf(Math.random() * 1000000000).longValue());
  50.             tableData.add(rowData);
  51.         }
  52.         // 构建一个输出流，向被读取的excel写入测试数据
  53.         FileOutputStream output = new FileOutputStream(url.getFile());
  54.         StandardWriter.build(output)
  55.                 .insert(
  56.                         "0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone into 0",
  57.                         "0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone into 1"
  58.                 )
  59.                 .addData("0", 0, tableData)
  60.                 .addData("1", 0, tableData)
  61.                 .execute();
  62.         output.flush();
  63.         output.close();
  65.         // 构建一个输入流，读取excel数据
  66.         input = new FileInputStream(url.getFile());
  67.     }
  69.     /**
  70.      * 实例1：快速上手
  71.      */
  72.     @Test
  73.     public void test001() {
  74.         Object result = StandardReader
  75.                 .build(input)
  76.                 .select("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone from 0")
  77.                 .execute();
  78.         System.out.println(result);
  79.     }
  81.     /**
  82.      * 实例2：可选配置
  83.      */
  84.     @Test
  85.     public void test002() {
  86.         StandardReader
  87.                 .build(input)
  88.                 // limit: [startRow, size] 从哪行开始读，读取多少行
  89.                 .select("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone from 0 limit 0, 10")
  90.                 // 绑定过滤器到一个sheet上
  91.                 .addFilter("0", new RowBeforeReadFilter() {
  92.                     @Override
  93.                     public void doFilter(Row target, Object data, Table config) {
  94.                         System.out.println("<><><><><><><>读取Row之前过滤：" + data + "<><><><><><><>");
  95.                     }
  96.                 }, new RowAfterReadFilter() {
  97.                     @Override
  98.                     public void doFilter(Row target, Object data, Table config) {
  99.                         System.out.println("<><><><><><><>读取Row之后过滤：" + data + "<><><><><><><>");
  100.                     }
  101.                 }, new CellBeforeReadFilter() {
  102.                     @Override
  103.                     public void doFilter(Cell target, Object data, Point config) {
  104.                         System.out.println("<><><><><><><>读取Cell之前过滤：" + data + "<><><><><><><>");
  105.                     }
  106.                 }, new CellAfterReadFilter() {
  107.                     @Override
  108.                     public void doFilter(Cell target, Object data, Point config) {
  109.                         System.out.println("<><><><><><><>读取Cell之后过滤：" + data + "<><><><><><><>");
  110.                     }
  111.                 })
  112.                 .execute();
  113.     }
  115.     /**
  116.      * 实例3：where 条件过滤
  117.      */
  118.     @Test
  119.     public void test003() {
  120.         Map namedParameter = new HashMap();
  121.         namedParameter.put("name", "鲁%");
  122.         namedParameter.put("age", 20);
  123.         namedParameter.put("qq", "%12%");
  124.         namedParameter.put("names", Arrays.asList("张馨蓉", "张白萱", "张若云"));
  125.         Object result = StandardReader
  126.                 .build(input)
  127.                 // where 条件过滤，支持占位符和命名参数
  128.                 .select(
  129.                         "0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone from 0 where name like ? and (age > ? or qq like ? or name in ?)",
  130.                         "0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone from 1 where name like :name and (age > :age or qq like :qq or name in :names)"
  131.                 )
  132.                 // 添加占位符参数
  133.                 .addParameter("0", 0, Arrays.asList("鲁%", 20, "%12%", Arrays.asList("张馨蓉", "张白萱", "张若云")))
  134.                 // 添加命名的参数
  135.                 .addParameter("1", 0, namedParameter)
  136.                 .execute();
  137.         System.out.println(result);
  138.     }
  140.     /**
  141.      * 实例4：散列点
  142.      */
  143.     @Test
  144.     public void test004() {
  145.         Object result = StandardReader
  146.                 .build(input)
  147.                 .select(
  148.                         "{0-1 name, 0-2 age from 0}",
  149.                         "{1-1 name, 1-2 age from 1}"
  150.                 )
  151.                 .execute();
  152.         System.out.println(result);
  153.     }
  154. }

StandardWriter 接口使用

  1. package com.geese.plugin.excelMapping.test;
  3. import com.geese.plugin.excelMapping.StandardWriter;
  4. import com.geese.plugin.excelMapping.config.Point;
  5. import com.geese.plugin.excelMapping.config.Table;
  6. import com.geese.plugin.excelMapping.filter.CellWriteFilter;
  7. import com.geese.plugin.excelMapping.filter.RowWriteFilter;
  8. import org.apache.poi.ss.usermodel.Cell;
  9. import org.apache.poi.ss.usermodel.Row;
  10. import org.junit.BeforeClass;
  11. import org.junit.Test;
  13. import java.io.*;
  14. import java.net.URL;
  15. import java.util.ArrayList;
  16. import java.util.HashMap;
  17. import java.util.List;
  18. import java.util.Map;
  20. /**
  21.  * <p> 标准Excel写入接口测试 <br>
  22.  *
  23.  * @author zhangguangyong <a href="#">1243610991@qq.com</a>
  24.  * @date 2016/11/16 21:41
  25.  * @sine 0.0.1
  26.  */
  27. public class StandardWriterTest {
  28.     private static OutputStream output;
  29.     private static InputStream template;
  31.     @BeforeClass
  32.     public static void beforeClass() throws IOException {
  33.         // 输出
  34.         URL url = Thread.currentThread().getContextClassLoader().getResource("demo-writer.xlsx");
  35.         output = new FileOutputStream(url.getFile());
  36.         // 模板
  37.         url = Thread.currentThread().getContextClassLoader().getResource("demo-writer-template.xlsx");
  38.         template = new FileInputStream(url.getFile());
  39.     }
  41.     /**
  42.      * 实例1：快速上手
  43.      */
  44.     @Test
  45.     public void test001() {
  46.         // 准备表格数据
  47.         List tableData = new ArrayList();
  48.         Map rowData;
  49.         for (int i = 0; i < 50; i++) {
  50.             rowData = new HashMap();
  51.             rowData.put("name", "隔壁老王" + i);
  52.             rowData.put("age", Double.valueOf((Math.random() * 100)).intValue());
  53.             rowData.put("idCard", Double.valueOf((Math.random() * 1000000000)).longValue());
  54.             rowData.put("qq", Double.valueOf((Math.random() * 100000000)).longValue());
  55.             rowData.put("email", Double.valueOf((Math.random() * 1000000)).longValue() + "@qq.com");
  56.             rowData.put("phone", Double.valueOf((Math.random() * 1000000000)).longValue());
  57.             tableData.add(rowData);
  58.         }
  59.         // 把数据插入到excel对应的位置
  60.         StandardWriter.build(output)
  61.                 .insert("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone into Sheet1")
  62.                 .addData("Sheet1", 0, tableData)
  63.                 .execute();
  64.     }
  66.     /**
  67.      * 实例11：可选配置项
  68.      */
  69.     @Test
  70.     public void test002() {
  71.         // 准备表格数据
  72.         List tableData = new ArrayList();
  73.         Map rowData;
  74.         for (int i = 0; i < 50; i++) {
  75.             rowData = new HashMap();
  76.             rowData.put("name", "隔壁老王" + i);
  77.             rowData.put("age", Double.valueOf((Math.random() * 100)).intValue());
  78.             rowData.put("idCard", Double.valueOf((Math.random() * 1000000000)).longValue());
  79.             rowData.put("qq", Double.valueOf((Math.random() * 100000000)).longValue());
  80.             rowData.put("email", Double.valueOf((Math.random() * 1000000)).longValue() + "@qq.com");
  81.             rowData.put("phone", Double.valueOf((Math.random() * 1000000000)).longValue());
  82.             tableData.add(rowData);
  83.         }
  84.         // 把数据插入到excel对应的位置
  85.         StandardWriter
  86.                 // template: 使用模板来接收写入的数据
  87.                 .build(output, template)
  88.                 // limit:[startRow, size] 从哪行开始写，写多少行 默认：[0, tableData.size()]
  89.                 .insert("0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone into Sheet1 limit 1, 30")
  90.                 // filter: 在数据写入到row或cell之前，可以对数据进行过滤修改, 过滤器需要绑定到某个Sheet上执行
  91.                 .addFilter("Sheet1", new RowWriteFilter() {
  92.                     @Override
  93.                     public void doFilter(Row target, Object data, Table config) {
  94.                         System.out.println("<<<<<<<<<<<<写入Row之前过滤：" + data + ">>>>>>>>>>>");
  95.                     }
  96.                 }, new CellWriteFilter() {
  97.                     @Override
  98.                     public void doFilter(Cell target, Object data, Point config) {
  99.                         System.out.println("<<<<<<<<<<<<写入Cell之前过滤：" + data + ">>>>>>>>>>>");
  100.                     }
  101.                 })
  102.                 .addData("Sheet1", 0, tableData)
  103.                 .execute();
  104.     }
  106.     /**
  107.      * 实例3：列表 + 散列点
  108.      */
  109.     @Test
  110.     public void test003() {
  111.         // 准备表格数据
  112.         List tableData = new ArrayList();
  113.         Map rowData;
  114.         for (int i = 0; i < 50; i++) {
  115.             rowData = new HashMap();
  116.             rowData.put("name", "隔壁老王" + i);
  117.             rowData.put("age", Double.valueOf((Math.random() * 100)).intValue());
  118.             rowData.put("idCard", Double.valueOf((Math.random() * 1000000000)).longValue());
  119.             rowData.put("qq", Double.valueOf((Math.random() * 100000000)).longValue());
  120.             rowData.put("email", Double.valueOf((Math.random() * 1000000)).longValue() + "@qq.com");
  121.             rowData.put("phone", Double.valueOf((Math.random() * 1000000000)).longValue());
  122.             tableData.add(rowData);
  123.         }
  124.         // 准备散列点数据 EASY-EXCEL
  125.         Map pointData = new HashMap();
  126.         pointData.put("e", "E");
  127.         pointData.put("a", "A");
  128.         pointData.put("s", "S");
  129.         pointData.put("y", "Y");
  131.         pointData.put("x", "X");
  132.         pointData.put("c", "C");
  133.         pointData.put("l", "L");
  135.         // 把数据插入到excel对应的位置
  136.         StandardWriter.build(output)
  137.                 .insert(
  138.                         "0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone into Sheet1",
  139.                         "{0-8 e, 0-9 a, 0-10 s, 0-11 y, 1-8 e, 1-9 x, 1-10 c, 1-11 e, 1-12 l into Sheet1}"
  140.                 )
  141.                 .addData("Sheet1", 0, tableData)
  142.                 .addData("Sheet1", pointData)
  143.                 .execute();
  144.     }
  146.     /**
  147.      * 实例4：多个表格 + 散列点
  148.      */
  149.     @Test
  150.     public void test004() {
  151.         // 准备表格数据
  152.         List tableData1 = new ArrayList();
  153.         Map rowData;
  154.         for (int i = 0; i < 50; i++) {
  155.             rowData = new HashMap();
  156.             rowData.put("name", "隔壁老王" + i);
  157.             rowData.put("age", Double.valueOf((Math.random() * 100)).intValue());
  158.             rowData.put("idCard", Double.valueOf((Math.random() * 1000000000)).longValue());
  159.             rowData.put("qq", Double.valueOf((Math.random() * 100000000)).longValue());
  160.             rowData.put("email", Double.valueOf((Math.random() * 1000000)).longValue() + "@qq.com");
  161.             rowData.put("phone", Double.valueOf((Math.random() * 1000000000)).longValue());
  162.             tableData1.add(rowData);
  163.         }
  165.         List tableData2 = new ArrayList();
  166.         for (int i = 0; i < 20; i++) {
  167.             rowData = new HashMap();
  168.             rowData.put("name1", "隔壁老王" + i);
  169.             rowData.put("age1", Double.valueOf((Math.random() * 100)).intValue());
  170.             rowData.put("idCard1", Double.valueOf((Math.random() * 1000000000)).longValue());
  171.             rowData.put("qq1", Double.valueOf((Math.random() * 100000000)).longValue());
  172.             rowData.put("email1", Double.valueOf((Math.random() * 1000000)).longValue() + "@qq.com");
  173.             rowData.put("phone1", Double.valueOf((Math.random() * 1000000000)).longValue());
  174.             tableData2.add(rowData);
  175.         }
  177.         // 准备散列点数据 EASY-EXCEL
  178.         Map pointData = new HashMap();
  179.         pointData.put("e", "E");
  180.         pointData.put("a", "A");
  181.         pointData.put("s", "S");
  182.         pointData.put("y", "Y");
  184.         pointData.put("x", "X");
  185.         pointData.put("c", "C");
  186.         pointData.put("l", "L");
  188.         // 把数据插入到excel对应的位置
  189.         StandardWriter.build(output)
  190.                 .insert(
  191.                         "0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone into Sheet1",
  192.                         "7 name1, 8 age1, 9 idCard1, 10 qq1, 11 email1, 12 phone1 into Sheet1 limit 5",
  193.                         "{0-8 e, 0-9 a, 0-10 s, 0-11 y, 1-8 e, 1-9 x, 1-10 c, 1-11 e, 1-12 l into Sheet1}"
  194.                 )
  195.                 .addData("Sheet1", 0, tableData1)
  196.                 .addData("Sheet1", 1, tableData2)
  197.                 .addData("Sheet1", pointData)
  198.                 .execute();
  199.     }
  201.     /**
  202.      * 实例5：多sheet插入
  203.      */
  204.     @Test
  205.     public void test005() {
  206.         // 准备表格数据
  207.         List tableData1 = new ArrayList();
  208.         Map rowData;
  209.         for (int i = 0; i < 50; i++) {
  210.             rowData = new HashMap();
  211.             rowData.put("name", "隔壁老王" + i);
  212.             rowData.put("age", Double.valueOf((Math.random() * 100)).intValue());
  213.             rowData.put("idCard", Double.valueOf((Math.random() * 1000000000)).longValue());
  214.             rowData.put("qq", Double.valueOf((Math.random() * 100000000)).longValue());
  215.             rowData.put("email", Double.valueOf((Math.random() * 1000000)).longValue() + "@qq.com");
  216.             rowData.put("phone", Double.valueOf((Math.random() * 1000000000)).longValue());
  217.             tableData1.add(rowData);
  218.         }
  220.         List tableData2 = new ArrayList();
  221.         for (int i = 0; i < 20; i++) {
  222.             rowData = new HashMap();
  223.             rowData.put("name1", "隔壁老王" + i);
  224.             rowData.put("age1", Double.valueOf((Math.random() * 100)).intValue());
  225.             rowData.put("idCard1", Double.valueOf((Math.random() * 1000000000)).longValue());
  226.             rowData.put("qq1", Double.valueOf((Math.random() * 100000000)).longValue());
  227.             rowData.put("email1", Double.valueOf((Math.random() * 1000000)).longValue() + "@qq.com");
  228.             rowData.put("phone1", Double.valueOf((Math.random() * 1000000000)).longValue());
  229.             tableData2.add(rowData);
  230.         }
  232.         // 准备散列点数据 EASY-EXCEL
  233.         Map pointData = new HashMap();
  234.         pointData.put("e", "E");
  235.         pointData.put("a", "A");
  236.         pointData.put("s", "S");
  237.         pointData.put("y", "Y");
  239.         pointData.put("x", "X");
  240.         pointData.put("c", "C");
  241.         pointData.put("l", "L");
  243.         // 把数据插入到excel对应的位置
  244.         StandardWriter.build(output)
  245.                 .insert(
  246.                         "0 name, 1 age, 2 idCard, 3 qq, 4 email, 5 phone into 0",
  247.                         "7 name1, 8 age1, 9 idCard1, 10 qq1, 11 email1, 12 phone1 into 1 limit 5",
  248.                         "{0-8 e, 0-9 a, 0-10 s, 0-11 y, 1-8 e, 1-9 x, 1-10 c, 1-11 e, 1-12 l into 0}",
  249.                         "{0-8 e, 0-9 a, 0-10 s, 0-11 y, 1-8 e, 1-9 x, 1-10 c, 1-11 e, 1-12 l into 1}"
  250.                 )
  251.                 .addData("0", 0, tableData1)
  252.                 .addData("1", 0, tableData2)
  253.                 .addData("0", pointData)
  254.                 .addData("1", pointData)
  255.                 .execute();
  256.     }
  257. }