详解java封装实现Excel建表读写操作

对 excel 进行读写操作是生产环境下常见的业务，网上搜索的实现方式都是基于poi和jxl第三方框架，但都不是很全面。小编由于这两天刚好需要用到，于是就参考手写了一个封装操作工具，基本涵盖了excel表（分有表头和无表头）的创建，并对它们进行读写操作。为方便大家，有需要者可以点击文后点解下载直接使用哦，当然也可以根据自己需求举一反三自己定制，相信对于聪明的你也不是什么难事。话不多说，直接贴源码

pom.xml 文件：

<properties>
 <project.build.sourceencoding>utf-8</project.build.sourceencoding>
 <maven.compiler.source>1.8</maven.compiler.source>
 <maven.compiler.target>1.8</maven.compiler.target>
 </properties>

 <dependencies>
 <dependency>
  <groupid>junit</groupid>
  <artifactid>junit</artifactid>
  <version>4.11</version>
  <scope>test</scope>
 </dependency>
 <dependency>
  <groupid>org.apache.poi</groupid>
  <artifactid>poi</artifactid>
  <version>3.17</version>
 </dependency>
 <dependency>
  <groupid>org.projectlombok</groupid>
  <artifactid>lombok</artifactid>
  <version>1.18.0</version>
  <scope>provided</scope>
 </dependency>
 <dependency>
  <groupid>org.slf4j</groupid>
  <artifactid>slf4j-log4j12</artifactid>
  <version>1.8.0-beta2</version>
  <scope>test</scope>
 </dependency>
 <dependency>
  <groupid>log4j</groupid>
  <artifactid>log4j</artifactid>
  <version>1.2.17</version>
 </dependency>
 <dependency>
  <groupid>org.slf4j</groupid>
  <artifactid>slf4j-api</artifactid>
  <version>1.8.0-beta2</version>
 </dependency>
 </dependencies>

建表工具类：excelbuider.java

/**
   * 建表工具类
   * @author sherman
   * email:[e-mail]
   * created in 2018/8/24
   */
  @slf4j
  public class excelbuilder {

   private static hssfsheet sheet;
   private static hssfworkbook wb;
   private static boolean hasheader;

   /**
    * 初始化
    * @param excellname 表名
    */
   public excelbuilder(string excellname) {
    wb = new hssfworkbook();
    sheet = wb.createsheet(excellname);
   }

   /**
    * 设置表头，装配表头数据
    * @param value 字符串数组，用来作为表头的值
    *
    */
   public excelbuilder header(string... value) {
    if (value != null && value.length != 0) {
     //设置表头样式
     hssfcellstyle cellstyle = wb.createcellstyle();
     cellstyle.setfont(font("黑体", true, 12));
     hssfrow row = sheet.createrow(0);
     for (int i = 0; i < value.length; i++) {
      hssfcell cell = row.createcell(i);
      cell.setcellvalue(value[i]);
      cell.setcellstyle(cellstyle);
     }
     hasheader = true;
    }
    return this;
 }

 /**
  * excel 表内容装配
  * @param content 待装配表格内容的二维数组
  * @return
  */
 public excelbuilder content(list<list<object>> content) {
  if (content != null && !content.isempty()) {
   int index;
   for (int i = 0; i < content.size(); i++) {
    index = hasheader == false ? i : i + 1;
    hssfrow row = sheet.createrow(index);
    for (int j = 0; j < content.get(i).size(); j++) {
     string r = "";
     object value = content.get(i).get(j);
     //根据数据类型装配
     if (value instanceof string) {
      r = (string) value;
     } else if (value instanceof number) {
      r = string.valueof(value);
     } else if (value instanceof bigdecimal) {
      r = string.valueof(value);
     } else {
      if (!(value instanceof date) && !(value instanceof timestamp)) {
       if (!(value instanceof zoneddatetime) && !(value instanceof localdatetime)) {
        if (value instanceof enum) {
         r = ((enum) value).name();
        } else if (value != null) {

         log.info("error of create row, unknow field type: " + value.getclass().getname());
        }
       } else {
        datetimeformatter formatter = datetimeformatter.ofpattern("yyyy-mm-dd");
        r = formatter.format((temporalaccessor) value);
       }
      } else {
       dateformat sdf = new simpledateformat("yyyy-mm-dd");
       r = sdf.format(value);
      }
     }

     row.createcell(j).setcellvalue(r);
    }
   }
  }
  return this;
 }

 /**
  * 自动调整列宽大小
  */
 public excelbuilder autocolumnwidth() {
  for (int j = 0; j < sheet.getrow(0).getlastcellnum(); j++) {
   int maxlength = 0;
   for (int i = 0; i <= sheet.getlastrownum(); i++) {
    string value = sheet.getrow(i).getcell(j).getstringcellvalue();
    int length = 0;
    if (value != null) {
     length = value.getbytes().length;
    }
    if (length > maxlength) {
     maxlength = length;
    }
   }
   sheet.setcolumnwidth(j, maxlength > 30 ? (30 * 256 + 186) : (maxlength * 256 + 186));
  }
  return this;
 }

 /**
  * 实例化
  * @param hasheader 是否有表头
  * @return excel表格
  */
 public abstractexcel build(boolean hasheader) {
  return hasheader ? new headerexcel(sheet) : new noheaderexcel(sheet);
 }

 /**
  *
  * @param fontname 字体名字
  * @param isbold 是否粗体
  * @param fontsize 字体大小
  * @return 字体
  */
 private hssffont font(string fontname, boolean isbold, int fontsize) {
  hssffont font = wb.createfont();
  if (fontname != null) font.setfontname(fontname);
  else font.setfontname("黑体");
  font.setbold(isbold);
  font.setfontheightinpoints((short) fontsize);
  return font;
 }

}

excel的抽象父类：

/**
 * @author sherman
 * created in 2018/8/24
 */

public abstract class abstractexcel {
 private final hssfsheet sheet;

 public abstractexcel() {
  hssfworkbook wb = new hssfworkbook();
  sheet = wb.createsheet();
 }

 public abstractexcel(string sheetname){
  hssfworkbook wb = new hssfworkbook();
  sheet = wb.createsheet(sheetname);
 }

 public abstractexcel(hssfsheet sheet) {
  this.sheet = sheet;
 }



 public abstract list<map<string, string>> getpayload();


 public void write(outputstream op) throws ioexception {
  sheet.getworkbook().write(op);
  sheet.getworkbook().close();
 }

 public string getstringformatcellvalue(hssfcell cell) {
  string cellval = "";
  decimalformat df = new decimalformat("#");
  switch (cell.getcelltypeenum()) {
   case string:
    cellval = cell.getstringcellvalue();
    break;
   case numeric:
    string dataformat = cell.getcellstyle().getdataformatstring();
    if (dateutil.iscelldateformatted(cell)) {
     cellval = df.format(cell.getdatecellvalue());
    } else if ("@".equals(dataformat)) {
     cellval = df.format(cell.getnumericcellvalue());
    } else {
     cellval = string.valueof(cell.getnumericcellvalue());
     df = new decimalformat("#.#########");
     cellval = df.format(double.valueof(cellval));
    }
    break;
   case boolean:
    cellval = string.valueof(cell.getbooleancellvalue());
    break;
   case formula:
    cellval = string.valueof(cell.getcellformula());
    break;
   default:
    cellval = "";
  }
  return cellval;
 }


}

有表头实现类

/**
 * @author sherman
 * created in 2018/8/24
 */

public class headerexcel extends abstractexcel {
 private final static boolean hasheader = true;
 private final hssfsheet sheet;

 public headerexcel(hssfsheet sheet) {
  super(sheet);
  this.sheet = sheet;
 }

 public headerexcel(string sheetname, string excelpath) {
  hssfworkbook wb = null;
  try {
   wb = new hssfworkbook(new poifsfilesystem(new fileinputstream(excelpath)));
  } catch (ioexception e) {
   e.printstacktrace();
  }
  sheet = sheetname == null || sheetname.isempty() ? wb.getsheetat(0) : wb.getsheet(sheetname);
 }

 @override
 public list<map<string, string>> getpayload() {
  list<map<string, string>> payload = new arraylist<>();
  hssfrow headrow = sheet.getrow(0);
  for (int i = 1; i <= sheet.getlastrownum(); i++) {
   hssfrow currentrow = sheet.getrow(i);
   map<string, string> map = new hashmap<>();
   for (int j = 0; j < sheet.getrow(i).getlastcellnum(); j++) {
    map.put(getstringformatcellvalue(headrow.getcell(j)), getstringformatcellvalue(currentrow.getcell(j)));
   }
   payload.add(map);
  }
  return payload;
 }


}

无表头实现类

/**
 * @author sherman
 * created in 2018/8/24
 */

public class noheaderexcel extends abstractexcel {
 private final static boolean hasheader = false;
 private hssfsheet sheet;

 public noheaderexcel(hssfsheet sheet) {
  super(sheet);
  this.sheet = sheet;
 }

 public noheaderexcel(string sheetname, string excelpath) {
  hssfworkbook wb = null;
  try {
   wb = new hssfworkbook(new poifsfilesystem(new fileinputstream(excelpath)));
  } catch (ioexception e) {
   e.printstacktrace();
  }
  sheet = sheetname == null || sheetname.isempty() ? wb.getsheetat(0) : wb.getsheet(sheetname);
 }


 @override
 public list<map<string, string>> getpayload() {
  list<map<string, string>> payload = new arraylist<>();
  for (int i = 0; i < sheet.getlastrownum(); i++) {
   hssfrow currentrow = sheet.getrow(i);
   map<string, string> map = new hashmap<>();
   for (int j = 0; j <= sheet.getrow(i).getlastcellnum(); j++) {
    map.put(string.valueof(j), getstringformatcellvalue(currentrow.getcell(j)));
   }
   payload.add(map);
  }
  return payload;
 }


}

测试工具类：

/**
 * unit test for simple app.
 */
public class apptest 
{
 /**
  * 测试建表，写表操作
  */
 @test
 public void testexportexcel()
 {
  //测试数据
  string[] headers = new string[]{"a","b","c","d","e"};
   list<list<object>> valuelist = new linkedlist<>();
  for (char i = 'a'; i <= 'e' ; i++) {
   list<object> rowlist = new linkedlist<>();
   for (int j = 0; j <= 4; j++) {
    rowlist.add(i+string.valueof(j));
   }
   valuelist.add(rowlist);
  }

 abstractexcel excel = new excelbuilder("报名表")
   .header(headers)
   .content(valuelist)
   .autocolumnwidth()
   .build(true);

  try {
   file file = new file("e:\\excel\\test.xls");
   fileoutputstream op = new fileoutputstream(file);
   excel.write(op);
  } catch (ioexception e) {
   e.printstacktrace();
  }
 }

 /**
  * 测试读取表数据操作
  */
 @test
 public void testimportexcel(){
  abstractexcel excel = new headerexcel(null,"e:/excel/test.xls");
  list<map<string,string>> values = excel.getpayload();
  values.foreach(stringstringmap -> {
   stringstringmap.entryset().foreach(stringstringentry -> {
    system.out.println(stringstringentry.getkey()+"---->"+stringstringentry.getvalue());
   });

  });
 }

}

附图：

测试1

测试二：

看起来效果还不错，当然还有很多不完善的地方，有需要的朋友可以在此基础上扩展定制，例如读取表数据结构方式，实现行数增删改查据或者创建表标题等等。

或者有朋友有更好的实现方案，欢迎前来交流！

最后的最后，当然忘不了附上笨工具的源码啦！