java封装实现Excel建表读写操作
程序员文章站
2022-04-28 21:48:28
对 Excel 进行读写操作是生产环境下常见的业务,网上搜索的实现方式都是基于POI和JXL第三方框架,但都不是很全面。小编由于这两天刚好需要用到,于是就参考手写了一个封装操作工具,基本涵盖了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:1253950375@qq.com * 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
测试二:
看起来效果还不错,当然还有很多不完善的地方,有需要的朋友可以在此基础上扩展定制,例如读取表数据结构方式,实现行数增删改查据或者创建表标题等等。
或者有朋友有更好的实现方案,欢迎前来交流!
最后的最后,当然忘不了附上笨工具的源码啦!
上一篇: sqlserver序列定时初始化
下一篇: Web前端JQuery面试题(一)
推荐阅读