JAVA poi 帮助类

 

pom.xml 添加引用:

        <!--poi-->
        <dependency>
            <groupid>org.apache.poi</groupid>
            <artifactid>poi</artifactid>
            <version>3.14</version>
        </dependency>
        <!--ooxml -->
        <dependency>
            <groupid>org.apache.poi</groupid>
            <artifactid>poi-ooxml</artifactid>
            <version>3.14</version>
        </dependency>

java没有datatable，创建一个数据保存帮助类

import java.util.arraylist;

public class exceldo {
    public exceldo() {

    }

    public exceldo(string name) {
        this.name = name;
    }

    /*
    * sheet名
    * */
    private string name;

    public string getname() {
        return this.name;
    }

    public void setname(string name) {
        this.name = name;
    }

    /*
    * 二维集合，保存excel中的数据
    * */
    private arraylist<arraylist<string>> list;

    public arraylist<arraylist<string>> getlist() {
        return this.list;
    }

    public void setlist(arraylist<arraylist<string>> list) {
        this.list = list;
    }
}

view code

poi帮助类

import org.apache.poi.hssf.usermodel.hssfworkbook;
import org.apache.poi.ss.formula.eval.erroreval;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.cellrangeaddress;
import org.apache.poi.xssf.usermodel.xssfworkbook;

import java.io.*;
import java.text.simpledateformat;
import java.util.arraylist;
import java.util.concurrent.atomic.atomicreference;

public class poihelper {
    /**
     * 根据excel路径返回集合
     */
    public static arraylist<exceldo> readexcel(string filepath) throws ioexception {
        arraylist<exceldo> list = new arraylist<>();

        workbook workbook = getworkbook(filepath);
        // sheet总数
        integer sheettotal = workbook.getnumberofsheets();
        for (integer num = 0; num < sheettotal; num++) {
            if (workbook.issheethidden(num)) continue;
            sheet sheet = workbook.getsheetat(num);

            exceldo exceldo = new exceldo(sheet.getsheetname());
            arraylist<arraylist<string>> itemlist = new arraylist<arraylist<string>>();

            //  设置最大列，默认为1
            integer maxcolumnnum = 0;
            //  不是有效列集合，连续超过三行不读取后续所有列
            arraylist<integer> novalidcolumnlist = new arraylist<>();
            //  列：按照列把数据填充到datatable中，防止无限列出现
            for (integer columnindex = 0; columnindex <= maxcolumnnum; columnindex++) {
                novalidcolumnlist.add(columnindex);
                //  列中所有数据都是null为true
                boolean isallempty = true;
                //  行
                for (integer rowindex = 0; rowindex <= sheet.getlastrownum(); rowindex++) {
                    if (columnindex == 0)
                        itemlist.add(new arraylist<string>());
                    row itemrow = sheet.getrow(rowindex);
                    if (itemrow == null) continue;
                    maxcolumnnum = maxcolumnnum < itemrow.getlastcellnum() ? itemrow.getlastcellnum() : maxcolumnnum;
                    //  把格式转换为utf-8
                    string itemcellvalue = stringhelper.formatutf8string(getvalue(itemrow, columnindex));
                    if (!stringhelper.isnullorwhitespace(itemcellvalue)) isallempty = false;
                    itemlist.get(rowindex).add(columnindex, itemcellvalue);
                }

                //  当前列有值
                if (!isallempty)
                    novalidcolumnlist.clear();
                    //  连续空白列超过三行 或 有空白行且当前行为最后一行
                else if (novalidcolumnlist.size() > 3 || (novalidcolumnlist.size() > 0 && columnindex == maxcolumnnum - 1)) {
                    for (integer i = novalidcolumnlist.size() - 1; i >= 0; i--)
                        itemlist.remove(i);
                    break;
                }
            }

            // 得到一个sheet中有多少个合并单元格
            integer sheetmergecount = sheet.getnummergedregions();
            for (integer i = 0; i < sheetmergecount; i++) {
                // 获取合并后的单元格
                cellrangeaddress range = sheet.getmergedregion(i);
                string cellvalue = itemlist.get(range.getfirstrow()).get(range.getfirstcolumn());
                for (integer mrowindex = range.getfirstrow(); mrowindex <= range.getlastrow(); mrowindex++) {
                    for (integer mcolumnindex = range.getfirstcolumn(); mcolumnindex <= range.getlastcolumn(); mcolumnindex++) {
                        itemlist.get(mrowindex).set(mcolumnindex, cellvalue);
                    }
                }
            }
            exceldo.setlist(itemlist);
            list.add(exceldo);
        }

        return list;
    }

    /*
     * 把集合中的数据保存为excel文件
     * */
    public static void saveexcel(arraylist<exceldo> dolist, string filedirectorypath) {
        dolist.foreach(item -> {
            workbook workbook = new hssfworkbook();
            sheet sheet = workbook.createsheet(item.getname());
            arraylist<arraylist<string>> itemlist = item.getlist();
            if (itemlist != null || !itemlist.isempty()) {
                for (integer rownum = 0; rownum < itemlist.size(); rownum++) {
                    arraylist<string> rowlist = itemlist.get(rownum);
                    row row = sheet.createrow(rownum);
                    for (integer columnnum = 0; columnnum < rowlist.size(); columnnum++) {
                        cell codecell = row.createcell(columnnum);
                        codecell.setcellvalue(rowlist.get(columnnum));
                    }
                }
            }
            string filepath = filedirectorypath + item.getname() + ".xls";
            try {
                outputstream stream = new fileoutputstream(filepath);// 将workbook写到输出流中
                workbook.write(stream);
                stream.flush();
                stream.close();
            } catch (filenotfoundexception e) {
                e.printstacktrace();
            } catch (ioexception e) {
                e.printstacktrace();
            }
        });
    }

    //  根据文件路径，返回文档对象
    public static workbook getworkbook(string filepath) throws ioexception {
        string extension = filehelper.getextension(filepath);
        inputstream stream = new fileinputstream(filepath);
        //hssf提供读写microsoft excel xls格式档案的功能。（97-03）
        //xssf提供读写microsoft excel ooxml xlsx格式档案的功能。
        //hwpf提供读写microsoft word doc格式档案的功能。
        //hslf提供读写microsoft powerpoint格式档案的功能。
        //hdgf提供读microsoft visio格式档案的功能。
        //hpbf提供读microsoft publisher格式档案的功能。
        //hsmf提供读microsoft outlook格式档案的功能。
        switch (extension) {
            case "xls":
                return new hssfworkbook(stream);
            case "xlsx":
            case "xlsm":
                return new xssfworkbook(stream);
        }
        //抛出自定的业务异常
        throw new error("excel格式文件错误");
    }

    /*
     * poi特殊日期格式：数字格式化成-yyyy年mm月dd日，格式
     * */
    private static arraylist<string> poidatelist = new arraylist<string>() {
        {
            add("年");
            add("月");
            add("日");
        }
    };

    /// <summary>
    /// 获取xssfrow的值（全部统一转成字符串）
    /// </summary>
    /// <param name="row"></param>
    /// <param name="index"></param>
    /// <returns></returns>
    public static string getvalue(row row, int index) {
        cell rowcell = row.getcell(index);
        return rowcell == null ? "" : getvaluebycellstyle(rowcell, rowcell.getcelltype());
    }

    /// <summary>
    /// 根据单元格的类型获取单元格的值
    /// </summary>
    /// <param name="rowcell"></param>
    /// <param name="type"></param>
    /// <returns></returns>
    public static string getvaluebycellstyle(cell rowcell, int rowcelltype) {
        string value = "";
        switch (rowcelltype) {
            case cell.cell_type_string:
                value = rowcell.getstringcellvalue();
                break;
            case cell.cell_type_numeric:
                string dataformat = rowcell.getcellstyle().getdataformatstring();
                atomicreference<boolean> isdate = new atomicreference<>(false);
                if (!stringhelper.isnullorwhitespace(dataformat))
                    poidatelist.foreach(x -> isdate.set(isdate.get() || dataformat.contains(x)));
                dataformatter formatter = new dataformatter();
                if (dateutil.iscelldateformatted(rowcell)) {
                    value = new simpledateformat("yyyy-mm-dd").format(dateutil.getjavadate(rowcell.getnumericcellvalue()));
                } else if (dateutil.iscellinternaldateformatted(rowcell)) {
                    value = new simpledateformat("yyyy-mm-dd").format(dateutil.getjavadate(rowcell.getnumericcellvalue()));
                }
                //有些情况，时间搓？数字格式化显示为时间,不属于上面两种时间格式
                else if (isdate.get()) {
                    value = new simpledateformat("yyyy-mm-dd").format(rowcell.getdatecellvalue());
                } else {
                    if (stringhelper.isnullorwhitespace(dataformat)) {
                        value = string.valueof(rowcell.getnumericcellvalue());
                    } else {
                        if (rowcell.getcellstyle().getdataformatstring().contains("$")) {
                            value = "$" + rowcell.getnumericcellvalue();
                        } else if (rowcell.getcellstyle().getdataformatstring().contains("￥")) {
                            value = "￥" + rowcell.getnumericcellvalue();
                        } else if (rowcell.getcellstyle().getdataformatstring().contains("¥")) {
                            value = "¥" + rowcell.getnumericcellvalue();
                        } else if (rowcell.getcellstyle().getdataformatstring().contains("€")) {
                            value = "€" + string.valueof(rowcell.getnumericcellvalue());
                        } else {
                            value = string.valueof(rowcell.getnumericcellvalue());
                        }
                    }
                }
                break;
            case cell.cell_type_boolean:
                value = string.valueof(rowcell.getbooleancellvalue());
                break;
            case cell.cell_type_error:
                value = erroreval.gettext(rowcell.geterrorcellvalue());
                break;
            case cell.cell_type_formula:
                //  todo: 是否存在 嵌套 公式类型
                value = getvaluebycellstyle(rowcell, rowcell.getcachedformularesulttype());
                string cellvalue = string.valueof(rowcell.getcellformula());
                break;
            default:
                system.out.println(rowcell);
                break;
        }
        return value;
    }
}

view code

stringhelper

import java.io.unsupportedencodingexception;

public class stringhelper {
    /*
        把特殊字符转换为utf-8格式
    */
    public static string formatutf8string(string str) throws unsupportedencodingexception {
        if (isnullorwhitespace(str)) return "";
        string newstr = changecharset(str, "utf-8").trim();
        return newstr;
    }

    /**
     * 字符串编码转换的实现方法
     *
     * @param str        待转换编码的字符串
     * @param newcharset 目标编码
     * @return
     * @throws unsupportedencodingexception
     */
    public static string changecharset(string str, string newcharset) throws unsupportedencodingexception {
        if (isnullorwhitespace(str)) return "";
        //用默认字符编码解码字符串。
        byte[] bs = str.getbytes();
        //用新的字符编码生成字符串
        return new string(bs, newcharset);
    }

    /*
        判断字符是否为空，为空返回true
    */
    public static boolean isnullorwhitespace(string str) {
        return str == null || str.isempty() ? true : false;
    }
}

view code

filehelper

import java.io.file;

public class filehelper {
    //  返回指定的路径字符串的扩展名,不包含“。”,转小写
    public static string getextension(string filepath) {
        file file = new file(filepath);
        string filename = file.getname();
        return filename.substring(filename.lastindexof(".") + 1, filename.length()).tolowercase();
    }
}

view code