欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

JAVA poi 帮助类

程序员文章站 2024-01-16 12:10:28
pom.xml 添加引用: Java没有datatable,创建一个数据保存帮助类 import java.util.ArrayList; public class ExcelDO { public ExcelDO() { } public ExcelDO(String name) { this.n ......

 

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,创建一个数据保存帮助类

JAVA poi 帮助类
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帮助类

JAVA 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

JAVA poi 帮助类
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
JAVA poi 帮助类
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