对 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
测试二：
看起来效果还不错，当然还有很多不完善的地方，有需要的朋友可以在此基础上扩展定制，例如读取表数据结构方式，实现行数增删改查据或者创建表标题等等。
或者有朋友有更好的实现方案，欢迎前来交流！
最后的最后，当然忘不了附上笨工具的源码啦！
如对本文有疑问， 点击进行留言回复！！
浅谈Java如何实现一个基于LRU时间复杂度为O(1)的缓存
JDK1.6“新“特性Instrumentation之JavaAgent(推荐)
before社区电量是什么意思 Before社区电量获得方法
网友评论