temp

2023-10-16 18:13:46

temp1

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.tigercontrols.valveapigateway.api.exception.CodeException;
import com.tigercontrols.valveapigateway.api.exception.ErrorCode;
import com.tigercontrols.valveapigateway.newApi.util.ServletUtils;
import lombok.extern.slf4j.Slf4j;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import static java.lang.System.out;

/**
 * @author ：byH
 * @date ：Created 2021/1/7 8:15
 * @description：Easyexcel导出excel
 */
@Slf4j
public class ExcelAgent {
    /**
     * 动态数据导出
     *
     * @param headerList 需要导出的表头名称
     * @param list       需要导出的数据
     * @param type       生成类型 1多sheet 2多table
     */
    public static String exportDynami(List<String> headerList, List<List<Object>> list, int type, String fileName) throws CodeException {
        if (list.size() >= 1000000) {
            throw new CodeException(ErrorCode.UNEXISTED_ERROR, "The data Out of data range,The max value is 1000000");
        }
        try {
            //获取response
            HttpServletResponse response = ServletUtils.getResponse();
            ExcelWriter excelWriter = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
            //获取名称
            List<String> valveNameList = new ArrayList<>();
            for (List<Object> objectList : list) {
                valveNameList.add((String) objectList.get(0));
            }
            //去重
            List<String> collectList = valveNameList.stream().distinct().collect(Collectors.toList());

            // 创建一个表格
            if (type == 1) {
                Table table = new Table(1);
                // 动态添加 表头 headList --> 所有表头行集合
                List<List<String>> headList = new ArrayList<>();
                // 第 n 行 的表头
                for (String s : headerList) {
                    List<String> headTitle = new ArrayList<>();
                    headTitle.add(s);
                    headList.add(headTitle);
                }
                table.setHead(headList);
                String ifValveName;
                for (int i = 0; i < collectList.size(); i++) {
                    List<List<Object>> newList = new ArrayList<>();
                    for (List<Object> objects : list) {
                        ifValveName = (String) objects.get(0);
                        if (collectList.get(i).equals(ifValveName)) {
                            newList.add(objects);
                        }
                    }
                    Sheet sheet = new Sheet(i + 1);
                    sheet.setSheetName(collectList.get(i));
                    excelWriter.write1(newList, sheet, table);
                }
            } else {
                //table
                String ifValveName;
                for (int i = 0; i < collectList.size(); i++) {
                    List<List<Object>> newList = new ArrayList<>();
                    for (List<Object> objects : list) {
                        ifValveName = (String) objects.get(0);
                        if (collectList.get(i).equals(ifValveName)) {
                            newList.add(objects);
                        }
                    }
                    // 创建一个表格
                    Table table = new Table(i + 1);
                    // 动态添加 表头 headList --> 所有表头行集合
                    List<List<String>> headList = new ArrayList<>();
                    // 第 n 行 的表头
                    for (String s : headerList) {
                        List<String> headTitle = new ArrayList<>();
                        headTitle.add(s);
                        headList.add(headTitle);
                    }
                    table.setHead(headList);
                    Sheet sheet = new Sheet(1);
                    sheet.setSheetName("汇总");
                    excelWriter.write1(newList, sheet, table);
                }
            }
            //清理缓存
            excelWriter.finish();
            log.info("export success");
            return "";
        } catch (Exception e) {
            e.printStackTrace();
            log.info("export fail");
            return e.getMessage();
        } finally {
            // 关闭流
            out.close();

        }
    }

    /**
     * 导出文件时为Writer生成OutputStream
     *
     * @param fileName 文件名
     * @param response response
     * @return 流
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        try {
            fileName = URLEncoder.encode(fileName, "utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }

/**
     * 获取response
     */
    public static HttpServletResponse getResponse() {
        return getRequestAttributes().getResponse();
    }

	    <!--阿里easyExcel-->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>2.1.6</version>
		</dependency>

temp2

JAVA使用easyexcel操作Excel

蒋固金 2018-05-04 22:40:48 temp

133430

分类专栏： JAVA 文章标签： Excel easyexcel java poi

本文链接：https://blog.csdn.net/jianggujin/article/details/80200400

版权

JAVA 专栏收录该内容

163 篇文章 0 订阅

订阅专栏

之前写过一篇《JAVA操作Excel》，介绍了jxl和poi读写Excel的实现，今天为大家介绍一下使用easyexcel对Excel进行读写，项目主页地址：https://github.com/alibaba/easyexcel

作者对easyexcel的介绍是：

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便

使用easyexcel，首先我们需要添加maven依赖：

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>1.0.1</version>
</dependency>

首先，我们先来看看如何写Excel，写入Excel，我们可以通过com.alibaba.excel.ExcelWriter类实现，下面我们来看一下最简单的无表头的实现

package test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
public class ExcelWriteTest {
/**

* 每行数据是List<String>无表头

*

* @throws IOException

*/
@Test
public void writeWithoutHead() throws IOException {
try (OutputStream out = new FileOutputStream("withoutHead.xlsx")?? {

ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, false);

Sheet sheet1 = new Sheet(1, 0);

sheet1.setSheetName("sheet1");

List<List<String>> data = new ArrayList<>();
for (int i = 0; i < 100; i++) {

List<String> item = new ArrayList<>();

item.add("item0" + i);

item.add("item1" + i);

item.add("item2" + i);

data.add(item);

}

writer.write0(data, sheet1);

writer.finish();

}

}

}

生成的Excel样式如下：
temp

很多时候，我们在生成Excel的时候都是需要添加表头的，使用easyexcel可以很容易的实现，我们可以对上面的例子进行简单的改造，为其添加表头

package test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
public class ExcelWriteTest {
@Test
public void writeWithoutHead() throws IOException {
try (OutputStream out = new FileOutputStream("withHead.xlsx")?? {

ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

Sheet sheet1 = new Sheet(1, 0);

sheet1.setSheetName("sheet1");

List<List<String>> data = new ArrayList<>();
for (int i = 0; i < 100; i++) {

List<String> item = new ArrayList<>();

item.add("item0" + i);

item.add("item1" + i);

item.add("item2" + i);

data.add(item);

}

List<List<String>> head = new ArrayList<List<String>>();

List<String> headCoulumn1 = new ArrayList<String>();

List<String> headCoulumn2 = new ArrayList<String>();

List<String> headCoulumn3 = new ArrayList<String>();

headCoulumn1.add("第一列");

headCoulumn2.add("第二列");

headCoulumn3.add("第三列");

head.add(headCoulumn1);

head.add(headCoulumn2);

head.add(headCoulumn3);

Table table = new Table(1);

table.setHead(head);

writer.write0(data, sheet1, table);

writer.finish();

}

}

}

效果如下：

temp

除了上面添加表头的方式，我们还可以使用实体类，为其添加com.alibaba.excel.annotation.ExcelProperty注解来生成表头，实体类数据作为Excel数据

package test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
public class ExcelWriteTest {
@Test
public void writeWithHead() throws IOException {
try (OutputStream out = new FileOutputStream("withHead.xlsx")?? {

ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

Sheet sheet1 = new Sheet(1, 0, ExcelPropertyIndexModel.class);

sheet1.setSheetName("sheet1");

List<ExcelPropertyIndexModel> data = new ArrayList<>();
for (int i = 0; i < 100; i++) {

ExcelPropertyIndexModel item = new ExcelPropertyIndexModel();

item.name = "name" + i;

item.age = "age" + i;

item.email = "email" + i;

item.address = "address" + i;

item.sax = "sax" + i;

item.heigh = "heigh" + i;

item.last = "last" + i;

data.add(item);

}

writer.write(data, sheet1);

writer.finish();

}

}
public static class ExcelPropertyIndexModel extends BaseRowModel {
  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"姓名"</span>, index = <span class="hljs-number">0</span>)
  <span class="hljs-keyword">private</span> String name;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"年龄"</span>, index = <span class="hljs-number">1</span>)
  <span class="hljs-keyword">private</span> String age;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"邮箱"</span>, index = <span class="hljs-number">2</span>)
  <span class="hljs-keyword">private</span> String email;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"地址"</span>, index = <span class="hljs-number">3</span>)
  <span class="hljs-keyword">private</span> String address;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"性别"</span>, index = <span class="hljs-number">4</span>)
  <span class="hljs-keyword">private</span> String sax;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"高度"</span>, index = <span class="hljs-number">5</span>)
  <span class="hljs-keyword">private</span> String heigh;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"备注"</span>, index = <span class="hljs-number">6</span>)
  <span class="hljs-keyword">private</span> String last;

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getName</span>() {
     <span class="hljs-keyword">return</span> name;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setName</span>(String name) {
     <span class="hljs-keyword">this</span>.name = name;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getAge</span>() {
     <span class="hljs-keyword">return</span> age;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setAge</span>(String age) {
     <span class="hljs-keyword">this</span>.age = age;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getEmail</span>() {
     <span class="hljs-keyword">return</span> email;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setEmail</span>(String email) {
     <span class="hljs-keyword">this</span>.email = email;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getAddress</span>() {
     <span class="hljs-keyword">return</span> address;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setAddress</span>(String address) {
     <span class="hljs-keyword">this</span>.address = address;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getSax</span>() {
     <span class="hljs-keyword">return</span> sax;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setSax</span>(String sax) {
     <span class="hljs-keyword">this</span>.sax = sax;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getHeigh</span>() {
     <span class="hljs-keyword">return</span> heigh;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setHeigh</span>(String heigh) {
     <span class="hljs-keyword">this</span>.heigh = heigh;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getLast</span>() {
     <span class="hljs-keyword">return</span> last;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setLast</span>(String last) {
     <span class="hljs-keyword">this</span>.last = last;
  }

}

}

效果如下：
temp

如果单行表头表头还不满足需求，没关系，还可以使用多行复杂的表头

package test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
public class ExcelWriteTest {
@Test
public void writeWithMultiHead() throws IOException {
try (OutputStream out = new FileOutputStream("withMultiHead.xlsx")?? {

ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

Sheet sheet1 = new Sheet(1, 0, MultiLineHeadExcelModel.class);

sheet1.setSheetName("sheet1");

List<MultiLineHeadExcelModel> data = new ArrayList<>();
for (int i = 0; i < 100; i++) {

MultiLineHeadExcelModel item = new MultiLineHeadExcelModel();

item.p1 = "p1" + i;

item.p2 = "p2" + i;

item.p3 = "p3" + i;

item.p4 = "p4" + i;

item.p5 = "p5" + i;

item.p6 = "p6" + i;

item.p7 = "p7" + i;

item.p8 = "p8" + i;

item.p9 = "p9" + i;

data.add(item);

}

writer.write(data, sheet1);

writer.finish();

}

}
public static class MultiLineHeadExcelModel extends BaseRowModel {
  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头1"</span>, <span class="hljs-string">"表头1"</span>, <span class="hljs-string">"表头31"</span> }, index = <span class="hljs-number">0</span>)
  <span class="hljs-keyword">private</span> String p1;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头1"</span>, <span class="hljs-string">"表头1"</span>, <span class="hljs-string">"表头32"</span> }, index = <span class="hljs-number">1</span>)
  <span class="hljs-keyword">private</span> String p2;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头3"</span>, <span class="hljs-string">"表头3"</span>, <span class="hljs-string">"表头3"</span> }, index = <span class="hljs-number">2</span>)
  <span class="hljs-keyword">private</span> String p3;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头4"</span>, <span class="hljs-string">"表头4"</span>, <span class="hljs-string">"表头4"</span> }, index = <span class="hljs-number">3</span>)
  <span class="hljs-keyword">private</span> String p4;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头5"</span>, <span class="hljs-string">"表头51"</span>, <span class="hljs-string">"表头52"</span> }, index = <span class="hljs-number">4</span>)
  <span class="hljs-keyword">private</span> String p5;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头6"</span>, <span class="hljs-string">"表头61"</span>, <span class="hljs-string">"表头611"</span> }, index = <span class="hljs-number">5</span>)
  <span class="hljs-keyword">private</span> String p6;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头6"</span>, <span class="hljs-string">"表头61"</span>, <span class="hljs-string">"表头612"</span> }, index = <span class="hljs-number">6</span>)
  <span class="hljs-keyword">private</span> String p7;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头6"</span>, <span class="hljs-string">"表头62"</span>, <span class="hljs-string">"表头621"</span> }, index = <span class="hljs-number">7</span>)
  <span class="hljs-keyword">private</span> String p8;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头6"</span>, <span class="hljs-string">"表头62"</span>, <span class="hljs-string">"表头622"</span> }, index = <span class="hljs-number">8</span>)
  <span class="hljs-keyword">private</span> String p9;

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP1</span>() {
     <span class="hljs-keyword">return</span> p1;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP1</span>(String p1) {
     <span class="hljs-keyword">this</span>.p1 = p1;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP2</span>() {
     <span class="hljs-keyword">return</span> p2;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP2</span>(String p2) {
     <span class="hljs-keyword">this</span>.p2 = p2;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP3</span>() {
     <span class="hljs-keyword">return</span> p3;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP3</span>(String p3) {
     <span class="hljs-keyword">this</span>.p3 = p3;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP4</span>() {
     <span class="hljs-keyword">return</span> p4;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP4</span>(String p4) {
     <span class="hljs-keyword">this</span>.p4 = p4;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP5</span>() {
     <span class="hljs-keyword">return</span> p5;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP5</span>(String p5) {
     <span class="hljs-keyword">this</span>.p5 = p5;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP6</span>() {
     <span class="hljs-keyword">return</span> p6;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP6</span>(String p6) {
     <span class="hljs-keyword">this</span>.p6 = p6;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP7</span>() {
     <span class="hljs-keyword">return</span> p7;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP7</span>(String p7) {
     <span class="hljs-keyword">this</span>.p7 = p7;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP8</span>() {
     <span class="hljs-keyword">return</span> p8;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP8</span>(String p8) {
     <span class="hljs-keyword">this</span>.p8 = p8;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP9</span>() {
     <span class="hljs-keyword">return</span> p9;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP9</span>(String p9) {
     <span class="hljs-keyword">this</span>.p9 = p9;
  }

}

}

效果如下：
temp

怎么样，这些已经基本满足我们的日常需求了，easyexcel不仅支持上述几种形式，还支持在一个sheet中添加多个表

package test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
public class ExcelWriteTest {
@Test
public void writeWithMultiTable() throws IOException {
try (OutputStream out = new FileOutputStream("withMultiTable.xlsx")?? {

ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

Sheet sheet1 = new Sheet(1, 0);

sheet1.setSheetName("sheet1");
     <span class="hljs-comment">// 数据全是List&lt;String&gt; 无模型映射关系</span>
     Table table1 = <span class="hljs-keyword">new</span> Table(<span class="hljs-number">1</span>);
     List&lt;List&lt;String&gt;&gt; data1 = <span class="hljs-keyword">new</span> ArrayList&lt;&gt;();
     <span class="hljs-keyword">for</span> (<span class="hljs-keyword">int</span> i = <span class="hljs-number">0</span>; i &lt; <span class="hljs-number">5</span>; i++) {
        List&lt;String&gt; item = <span class="hljs-keyword">new</span> ArrayList&lt;&gt;();
        item.add(<span class="hljs-string">"item0"</span> + i);
        item.add(<span class="hljs-string">"item1"</span> + i);
        item.add(<span class="hljs-string">"item2"</span> + i);
        data1.add(item);
     }
     writer.write0(data1, sheet1, table1);

     <span class="hljs-comment">// 模型上有表头的注解</span>
     Table table2 = <span class="hljs-keyword">new</span> Table(<span class="hljs-number">2</span>);
     table2.setClazz(MultiLineHeadExcelModel.class);
     List&lt;MultiLineHeadExcelModel&gt; data2 = <span class="hljs-keyword">new</span> ArrayList&lt;&gt;();
     <span class="hljs-keyword">for</span> (<span class="hljs-keyword">int</span> i = <span class="hljs-number">0</span>; i &lt; <span class="hljs-number">5</span>; i++) {
        MultiLineHeadExcelModel item = <span class="hljs-keyword">new</span> MultiLineHeadExcelModel();
        item.p1 = <span class="hljs-string">"p1"</span> + i;
        item.p2 = <span class="hljs-string">"p2"</span> + i;
        item.p3 = <span class="hljs-string">"p3"</span> + i;
        item.p4 = <span class="hljs-string">"p4"</span> + i;
        item.p5 = <span class="hljs-string">"p5"</span> + i;
        item.p6 = <span class="hljs-string">"p6"</span> + i;
        item.p7 = <span class="hljs-string">"p7"</span> + i;
        item.p8 = <span class="hljs-string">"p8"</span> + i;
        item.p9 = <span class="hljs-string">"p9"</span> + i;
        data2.add(item);
     }
     writer.write(data2, sheet1, table2);

     <span class="hljs-comment">// 模型上没有注解，表头数据动态传入,此情况下模型field顺序与excel现实顺序一致</span>
     List&lt;List&lt;String&gt;&gt; head = <span class="hljs-keyword">new</span> ArrayList&lt;List&lt;String&gt;&gt;();
     List&lt;String&gt; headCoulumn1 = <span class="hljs-keyword">new</span> ArrayList&lt;String&gt;();
     List&lt;String&gt; headCoulumn2 = <span class="hljs-keyword">new</span> ArrayList&lt;String&gt;();
     List&lt;String&gt; headCoulumn3 = <span class="hljs-keyword">new</span> ArrayList&lt;String&gt;();
     headCoulumn1.add(<span class="hljs-string">"第一列"</span>);
     headCoulumn2.add(<span class="hljs-string">"第二列"</span>);
     headCoulumn3.add(<span class="hljs-string">"第三列"</span>);
     head.add(headCoulumn1);
     head.add(headCoulumn2);
     head.add(headCoulumn3);
     Table table3 = <span class="hljs-keyword">new</span> Table(<span class="hljs-number">3</span>);
     table3.setHead(head);
     writer.write0(data1, sheet1, table3);

     writer.finish();
  }

}
public static class MultiLineHeadExcelModel extends BaseRowModel {
  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头1"</span>, <span class="hljs-string">"表头1"</span>, <span class="hljs-string">"表头31"</span> }, index = <span class="hljs-number">0</span>)
  <span class="hljs-keyword">private</span> String p1;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头1"</span>, <span class="hljs-string">"表头1"</span>, <span class="hljs-string">"表头32"</span> }, index = <span class="hljs-number">1</span>)
  <span class="hljs-keyword">private</span> String p2;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头3"</span>, <span class="hljs-string">"表头3"</span>, <span class="hljs-string">"表头3"</span> }, index = <span class="hljs-number">2</span>)
  <span class="hljs-keyword">private</span> String p3;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头4"</span>, <span class="hljs-string">"表头4"</span>, <span class="hljs-string">"表头4"</span> }, index = <span class="hljs-number">3</span>)
  <span class="hljs-keyword">private</span> String p4;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头5"</span>, <span class="hljs-string">"表头51"</span>, <span class="hljs-string">"表头52"</span> }, index = <span class="hljs-number">4</span>)
  <span class="hljs-keyword">private</span> String p5;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头6"</span>, <span class="hljs-string">"表头61"</span>, <span class="hljs-string">"表头611"</span> }, index = <span class="hljs-number">5</span>)
  <span class="hljs-keyword">private</span> String p6;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头6"</span>, <span class="hljs-string">"表头61"</span>, <span class="hljs-string">"表头612"</span> }, index = <span class="hljs-number">6</span>)
  <span class="hljs-keyword">private</span> String p7;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头6"</span>, <span class="hljs-string">"表头62"</span>, <span class="hljs-string">"表头621"</span> }, index = <span class="hljs-number">7</span>)
  <span class="hljs-keyword">private</span> String p8;

  <span class="hljs-annotation">@ExcelProperty</span>(value = { <span class="hljs-string">"表头6"</span>, <span class="hljs-string">"表头62"</span>, <span class="hljs-string">"表头622"</span> }, index = <span class="hljs-number">8</span>)
  <span class="hljs-keyword">private</span> String p9;

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP1</span>() {
     <span class="hljs-keyword">return</span> p1;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP1</span>(String p1) {
     <span class="hljs-keyword">this</span>.p1 = p1;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP2</span>() {
     <span class="hljs-keyword">return</span> p2;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP2</span>(String p2) {
     <span class="hljs-keyword">this</span>.p2 = p2;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP3</span>() {
     <span class="hljs-keyword">return</span> p3;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP3</span>(String p3) {
     <span class="hljs-keyword">this</span>.p3 = p3;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP4</span>() {
     <span class="hljs-keyword">return</span> p4;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP4</span>(String p4) {
     <span class="hljs-keyword">this</span>.p4 = p4;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP5</span>() {
     <span class="hljs-keyword">return</span> p5;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP5</span>(String p5) {
     <span class="hljs-keyword">this</span>.p5 = p5;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP6</span>() {
     <span class="hljs-keyword">return</span> p6;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP6</span>(String p6) {
     <span class="hljs-keyword">this</span>.p6 = p6;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP7</span>() {
     <span class="hljs-keyword">return</span> p7;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP7</span>(String p7) {
     <span class="hljs-keyword">this</span>.p7 = p7;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP8</span>() {
     <span class="hljs-keyword">return</span> p8;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP8</span>(String p8) {
     <span class="hljs-keyword">this</span>.p8 = p8;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getP9</span>() {
     <span class="hljs-keyword">return</span> p9;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setP9</span>(String p9) {
     <span class="hljs-keyword">this</span>.p9 = p9;
  }

}

}

效果如下：
temp

如果表头的样式不满足我们的需求，需要调整，我们可以使用com.alibaba.excel.metadata.TableStyle定义我们需要的样式，然后调用table对象的setTableStyle方法进行设置。

好了，到这里写入excel就基本介绍完了，下面我们就来看看如何读取excel，实际上现在的这个版本(1.0.1)在读取的时候是有BUG的，读取03版的.xls格式的excel正常，但是读取07版的.xlsx版的excel就会出异常，原因是在解析的时候sheet临时文件路径拼装有误，下面是我针对这个版本修复后的实现，大家可以替换掉原包中的实现

package com.alibaba.excel.read;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import javax.xml.parsers.ParserConfigurationException;
import com.alibaba.excel.read.v07.RowHandler;
import com.alibaba.excel.read.v07.XmlParserFactory;
import com.alibaba.excel.read.v07.XMLTempFile;
import com.alibaba.excel.read.context.AnalysisContext;
import com.alibaba.excel.read.exception.ExcelAnalysisException;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.util.FileUtil;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.xmlbeans.XmlException;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookPr;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;
public class SaxAnalyserV07 extends BaseSaxAnalyser {
<span class="hljs-keyword">private</span> SharedStringsTable sharedStringsTable;

<span class="hljs-keyword">private</span> List&lt;String&gt; sharedStringList = <span class="hljs-keyword">new</span> LinkedList&lt;String&gt;();

<span class="hljs-keyword">private</span> List&lt;SheetSource&gt; sheetSourceList = <span class="hljs-keyword">new</span> ArrayList&lt;SheetSource&gt;();

<span class="hljs-keyword">private</span> <span class="hljs-keyword">boolean</span> use1904WindowDate = <span class="hljs-keyword">false</span>;

<span class="hljs-keyword">private</span> <span class="hljs-keyword">final</span> String path;

<span class="hljs-keyword">private</span> File tmpFile;

<span class="hljs-keyword">private</span> String workBookXMLFilePath;

<span class="hljs-keyword">private</span> String sharedStringXMLFilePath;

<span class="hljs-keyword">public</span> <span class="hljs-title">SaxAnalyserV07</span>(AnalysisContext analysisContext) <span class="hljs-keyword">throws</span> Exception {
    <span class="hljs-keyword">this</span>.analysisContext = analysisContext;
    <span class="hljs-keyword">this</span>.path = XMLTempFile.createPath();
    <span class="hljs-keyword">this</span>.tmpFile = <span class="hljs-keyword">new</span> File(XMLTempFile.getTmpFilePath(path));
    <span class="hljs-keyword">this</span>.workBookXMLFilePath = XMLTempFile.getWorkBookFilePath(path);
    <span class="hljs-keyword">this</span>.sharedStringXMLFilePath = XMLTempFile.getSharedStringFilePath(path);
    start();
}

<span class="hljs-annotation">@Override</span>
<span class="hljs-keyword">protected</span> <span class="hljs-keyword">void</span> <span class="hljs-title">execute</span>() {
    <span class="hljs-keyword">try</span> {
        Sheet sheet = analysisContext.getCurrentSheet();
        <span class="hljs-keyword">if</span> (!isAnalysisAllSheets(sheet)) {
            <span class="hljs-keyword">if</span> (<span class="hljs-keyword">this</span>.sheetSourceList.size() &lt; sheet.getSheetNo() || sheet.getSheetNo() == <span class="hljs-number">0</span>) {
                <span class="hljs-keyword">return</span>;
            }
            InputStream sheetInputStream = <span class="hljs-keyword">this</span>.sheetSourceList.get(sheet.getSheetNo() - <span class="hljs-number">1</span>).getInputStream();
            parseXmlSource(sheetInputStream);
            <span class="hljs-keyword">return</span>;
        }
        <span class="hljs-keyword">int</span> i = <span class="hljs-number">0</span>;
        <span class="hljs-keyword">for</span> (SheetSource sheetSource : <span class="hljs-keyword">this</span>.sheetSourceList) {
            i++;
            <span class="hljs-keyword">this</span>.analysisContext.setCurrentSheet(<span class="hljs-keyword">new</span> Sheet(i));
            parseXmlSource(sheetSource.getInputStream());
        }

    } <span class="hljs-keyword">catch</span> (Exception e) {
        stop();
        <span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> ExcelAnalysisException(e);
    } <span class="hljs-keyword">finally</span> {
    }

}

<span class="hljs-keyword">private</span> <span class="hljs-keyword">boolean</span> <span class="hljs-title">isAnalysisAllSheets</span>(Sheet sheet) {
    <span class="hljs-keyword">if</span> (sheet == <span class="hljs-keyword">null</span>) {
        <span class="hljs-keyword">return</span> <span class="hljs-keyword">true</span>;
    }
    <span class="hljs-keyword">if</span> (sheet.getSheetNo() &lt; <span class="hljs-number">0</span>) {
        <span class="hljs-keyword">return</span> <span class="hljs-keyword">true</span>;
    }
    <span class="hljs-keyword">return</span> <span class="hljs-keyword">false</span>;
}

<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">stop</span>() {
    FileUtil.deletefile(path);
}

<span class="hljs-keyword">private</span> <span class="hljs-keyword">void</span> <span class="hljs-title">parseXmlSource</span>(InputStream inputStream) {
    <span class="hljs-keyword">try</span> {
        ContentHandler handler = <span class="hljs-keyword">new</span> RowHandler(<span class="hljs-keyword">this</span>, <span class="hljs-keyword">this</span>.sharedStringsTable, <span class="hljs-keyword">this</span>.analysisContext,
            sharedStringList);
        XmlParserFactory.parse(inputStream, handler);
        inputStream.close();
    } <span class="hljs-keyword">catch</span> (Exception e) {
        <span class="hljs-keyword">try</span> {
            inputStream.close();
        } <span class="hljs-keyword">catch</span> (IOException e1) {
            e1.printStackTrace();
        }
        <span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> ExcelAnalysisException(e);
    }
}

<span class="hljs-keyword">public</span> List&lt;Sheet&gt; <span class="hljs-title">getSheets</span>() {
    List&lt;Sheet&gt; sheets = <span class="hljs-keyword">new</span> ArrayList&lt;Sheet&gt;();
    <span class="hljs-keyword">try</span> {
        <span class="hljs-keyword">int</span> i = <span class="hljs-number">1</span>;
        <span class="hljs-keyword">for</span> (SheetSource sheetSource : <span class="hljs-keyword">this</span>.sheetSourceList) {
            Sheet sheet = <span class="hljs-keyword">new</span> Sheet(i, <span class="hljs-number">0</span>);
            sheet.setSheetName(sheetSource.getSheetName());
            i++;
            sheets.add(sheet);
        }
    } <span class="hljs-keyword">catch</span> (Exception e) {
        stop();
        <span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> ExcelAnalysisException(e);
    } <span class="hljs-keyword">finally</span> {

    }

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

<span class="hljs-keyword">private</span> <span class="hljs-keyword">void</span> <span class="hljs-title">start</span>() <span class="hljs-keyword">throws</span> IOException, XmlException, ParserConfigurationException, SAXException {

    createTmpFile();

    unZipTempFile();

    initSharedStringsTable();

    initUse1904WindowDate();

    initSheetSourceList();

}

<span class="hljs-keyword">private</span> <span class="hljs-keyword">void</span> <span class="hljs-title">createTmpFile</span>() <span class="hljs-keyword">throws</span> FileNotFoundException {
    FileUtil.writeFile(tmpFile, analysisContext.getInputStream());
}

<span class="hljs-keyword">private</span> <span class="hljs-keyword">void</span> <span class="hljs-title">unZipTempFile</span>() <span class="hljs-keyword">throws</span> IOException {
    FileUtil.doUnZip(path, tmpFile);
}

<span class="hljs-keyword">private</span> <span class="hljs-keyword">void</span> <span class="hljs-title">initSheetSourceList</span>() <span class="hljs-keyword">throws</span> IOException, ParserConfigurationException, SAXException {
    <span class="hljs-keyword">this</span>.sheetSourceList = <span class="hljs-keyword">new</span> ArrayList&lt;SheetSource&gt;();
    InputStream workbookXml = <span class="hljs-keyword">new</span> FileInputStream(<span class="hljs-keyword">this</span>.workBookXMLFilePath);
    XmlParserFactory.parse(workbookXml, <span class="hljs-keyword">new</span> DefaultHandler() {
        <span class="hljs-annotation">@Override</span>
        <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">startElement</span>(String uri, String localName, String qName, Attributes attrs) <span class="hljs-keyword">throws</span> SAXException {
            <span class="hljs-keyword">if</span> (qName.toLowerCase(Locale.US).equals(<span class="hljs-string">"sheet"</span>)) {
                String name = <span class="hljs-keyword">null</span>;
                <span class="hljs-keyword">int</span> id = <span class="hljs-number">0</span>;
                <span class="hljs-keyword">for</span> (<span class="hljs-keyword">int</span> i = <span class="hljs-number">0</span>; i &lt; attrs.getLength(); i++) {
                    <span class="hljs-keyword">if</span> (attrs.getLocalName(i).toLowerCase(Locale.US).equals(<span class="hljs-string">"name"</span>)) {
                        name = attrs.getValue(i);
                    }<span class="hljs-javadoc">/** else if (attrs.getLocalName(i).toLowerCase(Locale.US).equals("r:id")) {
                        id = Integer.parseInt(attrs.getValue(i).replaceAll("rId", ""));
                        try {
                            InputStream inputStream = new FileInputStream(XMLTempFile.getSheetFilePath(path, id));
                            sheetSourceList.add(new SheetSource(id, name, inputStream));
                        } catch (FileNotFoundException e) {
                            e.printStackTrace();
                        }
                    } **/</span>
                    <span class="hljs-comment">//应该使用sheetId属性</span>
                    <span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> (attrs.getLocalName(i).toLowerCase(Locale.US).equals(<span class="hljs-string">"sheetid"</span>)) {
                        id = Integer.parseInt(attrs.getValue(i));
                        <span class="hljs-keyword">try</span> {
                            InputStream inputStream = <span class="hljs-keyword">new</span> FileInputStream(XMLTempFile.getSheetFilePath(path, id));
                            sheetSourceList.add(<span class="hljs-keyword">new</span> SheetSource(id, name, inputStream));
                        } <span class="hljs-keyword">catch</span> (FileNotFoundException e) {
                            e.printStackTrace();
                        }
                   }
                }

            }
        }

    });
    workbookXml.close();
    <span class="hljs-comment">// 排序后是倒序，不符合实际要求</span>
    <span class="hljs-comment">// Collections.sort(sheetSourceList);</span>
    Collections.sort(sheetSourceList, <span class="hljs-keyword">new</span> Comparator&lt;SheetSource&gt;() {
       <span class="hljs-annotation">@Override</span>
       <span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">compare</span>(SheetSource o1, SheetSource o2) {
          <span class="hljs-keyword">return</span> o1.id - o2.id;
       }
    });
}

<span class="hljs-keyword">private</span> <span class="hljs-keyword">void</span> <span class="hljs-title">initUse1904WindowDate</span>() <span class="hljs-keyword">throws</span> IOException, XmlException {
    InputStream workbookXml = <span class="hljs-keyword">new</span> FileInputStream(workBookXMLFilePath);
    WorkbookDocument ctWorkbook = WorkbookDocument.Factory.parse(workbookXml);
    CTWorkbook wb = ctWorkbook.getWorkbook();
    CTWorkbookPr prefix = wb.getWorkbookPr();
    <span class="hljs-keyword">if</span> (prefix != <span class="hljs-keyword">null</span>) {
        <span class="hljs-keyword">this</span>.use1904WindowDate = prefix.getDate1904();
    }
    <span class="hljs-keyword">this</span>.analysisContext.setUse1904WindowDate(use1904WindowDate);
    workbookXml.close();
}

<span class="hljs-keyword">private</span> <span class="hljs-keyword">void</span> <span class="hljs-title">initSharedStringsTable</span>() <span class="hljs-keyword">throws</span> IOException, ParserConfigurationException, SAXException {
    <span class="hljs-comment">//因为sharedStrings.xml文件不一定存在，所以在处理之前增加判断</span>
    File sharedStringXMLFile = <span class="hljs-keyword">new</span> File(<span class="hljs-keyword">this</span>.sharedStringXMLFilePath);
    <span class="hljs-keyword">if</span> (!sharedStringXMLFile.exists()) {
        <span class="hljs-keyword">return</span>;
    }
    InputStream inputStream = <span class="hljs-keyword">new</span> FileInputStream(<span class="hljs-keyword">this</span>.sharedStringXMLFilePath);
    <span class="hljs-comment">//this.sharedStringsTable = new SharedStringsTable();</span>
    <span class="hljs-comment">//this.sharedStringsTable.readFrom(inputStream);</span>

    XmlParserFactory.parse(inputStream, <span class="hljs-keyword">new</span> DefaultHandler() {
        <span class="hljs-annotation">@Override</span>
        <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">characters</span>(<span class="hljs-keyword">char</span>[] ch, <span class="hljs-keyword">int</span> start, <span class="hljs-keyword">int</span> length) {
            sharedStringList.add(<span class="hljs-keyword">new</span> String(ch, start, length));
        }

    });
    inputStream.close();
}

<span class="hljs-keyword">private</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">SheetSource</span> <span class="hljs-keyword">implements</span> <span class="hljs-title">Comparable</span>&lt;<span class="hljs-title">SheetSource</span>&gt; {<!-- --></span>

    <span class="hljs-keyword">private</span> <span class="hljs-keyword">int</span> id;

    <span class="hljs-keyword">private</span> String sheetName;

    <span class="hljs-keyword">private</span> InputStream inputStream;

    <span class="hljs-keyword">public</span> <span class="hljs-title">SheetSource</span>(<span class="hljs-keyword">int</span> id, String sheetName, InputStream inputStream) {
        <span class="hljs-keyword">this</span>.id = id;
        <span class="hljs-keyword">this</span>.sheetName = sheetName;
        <span class="hljs-keyword">this</span>.inputStream = inputStream;
    }

    <span class="hljs-keyword">public</span> String <span class="hljs-title">getSheetName</span>() {
        <span class="hljs-keyword">return</span> sheetName;
    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setSheetName</span>(String sheetName) {
        <span class="hljs-keyword">this</span>.sheetName = sheetName;
    }

    <span class="hljs-keyword">public</span> InputStream <span class="hljs-title">getInputStream</span>() {
        <span class="hljs-keyword">return</span> inputStream;
    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setInputStream</span>(InputStream inputStream) {
        <span class="hljs-keyword">this</span>.inputStream = inputStream;
    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">getId</span>() {
        <span class="hljs-keyword">return</span> id;
    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setId</span>(<span class="hljs-keyword">int</span> id) {
        <span class="hljs-keyword">this</span>.id = id;
    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">compareTo</span>(SheetSource o) {
        <span class="hljs-keyword">if</span> (o.id == <span class="hljs-keyword">this</span>.id) {
            <span class="hljs-keyword">return</span> <span class="hljs-number">0</span>;
        } <span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> (o.id &gt; <span class="hljs-keyword">this</span>.id) {
            <span class="hljs-keyword">return</span> <span class="hljs-number">1</span>;
        } <span class="hljs-keyword">else</span> {
            <span class="hljs-keyword">return</span> -<span class="hljs-number">1</span>;
        }
    }
}

}

另外，使用easyexcel读取excel的时候需要设置excel的版本，但是有些时候我们无法预知excel的版本，所以个人感觉这样不是太好，所以模仿poi写了一个用于获取com.alibaba.excel.ExcelReader对象的工具类

package com.alibaba.excel.read;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import org.apache.poi.EmptyFileException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.DocumentFactoryHelper;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.util.IOUtils;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.context.AnalysisContext;
import com.alibaba.excel.read.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
public class ExcelReaderFactory {
/**

* @param in

*           文件输入流

* @param customContent

*           自定义模型可以在

*           {@link AnalysisEventListener#invoke(Object, AnalysisContext) }

*           AnalysisContext中获取用于监听者回调使用

* @param eventListener

*           用户监听

* @throws IOException

* @throws EmptyFileException

* @throws InvalidFormatException

*/
public static ExcelReader getExcelReader(InputStream in, Object customContent,

AnalysisEventListener<?> eventListener) throws EmptyFileException, IOException, InvalidFormatException {
// 如果输入流不支持mark/reset，需要对其进行包裹
if (!in.markSupported()) {

in = new PushbackInputStream(in, 8);

}
  <span class="hljs-comment">// 确保至少有一些数据</span>
  <span class="hljs-keyword">byte</span>[] header8 = IOUtils.peekFirst8Bytes(in);
  ExcelTypeEnum excelTypeEnum = <span class="hljs-keyword">null</span>;
  <span class="hljs-keyword">if</span> (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
     excelTypeEnum = ExcelTypeEnum.XLS;
  }
  <span class="hljs-keyword">if</span> (DocumentFactoryHelper.hasOOXMLHeader(in)) {
     excelTypeEnum = ExcelTypeEnum.XLSX;
  }
  <span class="hljs-keyword">if</span> (excelTypeEnum != <span class="hljs-keyword">null</span>) {
     <span class="hljs-keyword">return</span> <span class="hljs-keyword">new</span> ExcelReader(in, excelTypeEnum, customContent, eventListener);
  }
  <span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> InvalidFormatException(<span class="hljs-string">"Your InputStream was neither an OLE2 stream, nor an OOXML stream"</span>);

}
/**

* @param in

*           文件输入流

* @param customContent

*           自定义模型可以在

*           {@link AnalysisEventListener#invoke(Object, AnalysisContext) }

*           AnalysisContext中获取用于监听者回调使用

* @param eventListener

*           用户监听

* @param trim

*           是否对解析的String做trim()默认true,用于防止 excel中空格引起的装换报错。

* @throws IOException

* @throws EmptyFileException

* @throws InvalidFormatException

*/
public static ExcelReader getExcelReader(InputStream in, Object customContent,

AnalysisEventListener<?> eventListener, boolean trim)
throws EmptyFileException, IOException, InvalidFormatException {
// 如果输入流不支持mark/reset，需要对其进行包裹
if (!in.markSupported()) {

in = new PushbackInputStream(in, 8);

}
  <span class="hljs-comment">// 确保至少有一些数据</span>
  <span class="hljs-keyword">byte</span>[] header8 = IOUtils.peekFirst8Bytes(in);
  ExcelTypeEnum excelTypeEnum = <span class="hljs-keyword">null</span>;
  <span class="hljs-keyword">if</span> (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
     excelTypeEnum = ExcelTypeEnum.XLS;
  }
  <span class="hljs-keyword">if</span> (DocumentFactoryHelper.hasOOXMLHeader(in)) {
     excelTypeEnum = ExcelTypeEnum.XLSX;
  }
  <span class="hljs-keyword">if</span> (excelTypeEnum != <span class="hljs-keyword">null</span>) {
     <span class="hljs-keyword">return</span> <span class="hljs-keyword">new</span> ExcelReader(in, excelTypeEnum, customContent, eventListener, trim);
  }
  <span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> InvalidFormatException(<span class="hljs-string">"Your InputStream was neither an OLE2 stream, nor an OOXML stream"</span>);

}

}

下面我们就来写一个简单的读取Excel的示例：

package test;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import org.junit.Test;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.read.ExcelReaderFactory;
import com.alibaba.excel.read.context.AnalysisContext;
import com.alibaba.excel.read.event.AnalysisEventListener;
public class ExcelReadTest {
@Test
public void read() throws Exception {
try (InputStream in = new FileInputStream("withoutHead.xlsx")?? {

AnalysisEventListener<List<String>> listener = new AnalysisEventListener<List<String>>() {
        <span class="hljs-annotation">@Override</span>
        <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">invoke</span>(List&lt;String&gt; object, AnalysisContext context) {
           System.err.println(<span class="hljs-string">"Row:"</span> + context.getCurrentRowNum() + <span class="hljs-string">" Data:"</span> + object);
        }

        <span class="hljs-annotation">@Override</span>
        <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">doAfterAllAnalysed</span>(AnalysisContext context) {
           System.err.println(<span class="hljs-string">"doAfterAllAnalysed..."</span>);
        }
     };
     ExcelReader excelReader = ExcelReaderFactory.getExcelReader(in, <span class="hljs-keyword">null</span>, listener);
     excelReader.read();
  }

}

}

正如写入Excel的时候可以使用数据模型一样，在读取Excel的时候也可以直接将数据映射为模型对象，区别在于要使用ExcelReader #read的重载方法。

package test;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import org.junit.Test;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.read.ExcelReaderFactory;
import com.alibaba.excel.read.context.AnalysisContext;
import com.alibaba.excel.read.event.AnalysisEventListener;
public class ExcelReadTest {
@Test
public void read() throws Exception {
try (InputStream in = new FileInputStream("withHead.xlsx")?? {

AnalysisEventListener<ExcelPropertyIndexModel> listener = new AnalysisEventListener<ExcelPropertyIndexModel>() {
        <span class="hljs-annotation">@Override</span>
        <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">invoke</span>(ExcelPropertyIndexModel object, AnalysisContext context) {
           System.err.println(<span class="hljs-string">"Row:"</span> + context.getCurrentRowNum() + <span class="hljs-string">" Data:"</span> + object);
        }

        <span class="hljs-annotation">@Override</span>
        <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">doAfterAllAnalysed</span>(AnalysisContext context) {
           System.err.println(<span class="hljs-string">"doAfterAllAnalysed..."</span>);
        }
     };
     ExcelReader excelReader = ExcelReaderFactory.getExcelReader(in, <span class="hljs-keyword">null</span>, listener);
     <span class="hljs-comment">// 第二个参数为表头行数，按照实际设置</span>
     excelReader.read(<span class="hljs-keyword">new</span> Sheet(<span class="hljs-number">1</span>, <span class="hljs-number">1</span>, ExcelPropertyIndexModel.class));
  }

}
public static class ExcelPropertyIndexModel extends BaseRowModel {
  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"姓名"</span>, index = <span class="hljs-number">0</span>)
  <span class="hljs-keyword">private</span> String name;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"年龄"</span>, index = <span class="hljs-number">1</span>)
  <span class="hljs-keyword">private</span> String age;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"邮箱"</span>, index = <span class="hljs-number">2</span>)
  <span class="hljs-keyword">private</span> String email;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"地址"</span>, index = <span class="hljs-number">3</span>)
  <span class="hljs-keyword">private</span> String address;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"性别"</span>, index = <span class="hljs-number">4</span>)
  <span class="hljs-keyword">private</span> String sax;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"高度"</span>, index = <span class="hljs-number">5</span>)
  <span class="hljs-keyword">private</span> String heigh;

  <span class="hljs-annotation">@ExcelProperty</span>(value = <span class="hljs-string">"备注"</span>, index = <span class="hljs-number">6</span>)
  <span class="hljs-keyword">private</span> String last;

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getName</span>() {
     <span class="hljs-keyword">return</span> name;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setName</span>(String name) {
     <span class="hljs-keyword">this</span>.name = name;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getAge</span>() {
     <span class="hljs-keyword">return</span> age;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setAge</span>(String age) {
     <span class="hljs-keyword">this</span>.age = age;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getEmail</span>() {
     <span class="hljs-keyword">return</span> email;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setEmail</span>(String email) {
     <span class="hljs-keyword">this</span>.email = email;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getAddress</span>() {
     <span class="hljs-keyword">return</span> address;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setAddress</span>(String address) {
     <span class="hljs-keyword">this</span>.address = address;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getSax</span>() {
     <span class="hljs-keyword">return</span> sax;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setSax</span>(String sax) {
     <span class="hljs-keyword">this</span>.sax = sax;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getHeigh</span>() {
     <span class="hljs-keyword">return</span> heigh;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setHeigh</span>(String heigh) {
     <span class="hljs-keyword">this</span>.heigh = heigh;
  }

  <span class="hljs-keyword">public</span> String <span class="hljs-title">getLast</span>() {
     <span class="hljs-keyword">return</span> last;
  }

  <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setLast</span>(String last) {
     <span class="hljs-keyword">this</span>.last = last;
  }

  <span class="hljs-annotation">@Override</span>
  <span class="hljs-keyword">public</span> String <span class="hljs-title">toString</span>() {
     <span class="hljs-keyword">return</span> <span class="hljs-string">"ExcelPropertyIndexModel [name="</span> + name + <span class="hljs-string">", age="</span> + age + <span class="hljs-string">", email="</span> + email + <span class="hljs-string">", address="</span> + address
           + <span class="hljs-string">", sax="</span> + sax + <span class="hljs-string">", heigh="</span> + heigh + <span class="hljs-string">", last="</span> + last + <span class="hljs-string">"]"</span>;
  }

}

}

以上就是关于easyexcel的使用方法介绍，如有疑问，欢迎交流指正。

temp

码农公寓

temp1

temp2

JAVA使用easyexcel操作Excel

相关文章