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,创建一个数据保存帮助类
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; } }
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; } }
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; } }
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(); } }
上一篇: 某种密码