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

 

测试二：

 

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

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

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