之前就一直挺反感写excel导入导出的,也不是说实现起来有多难,而是那一连串的建立表格工作簿,然后循环每一列等文件流操作着实让我喜欢不起来呀。
一个偶然的机会,看到国人写了一个xxl-excel的中间件,可以直接拿过来就好。nice。
话不多说,直接上代码。
1.maven依赖
需要说明的是,我这边使用的poi依赖是3.17版本的。
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.3</version>
</dependency>
<dependency>
<groupId>com.xuxueli</groupId>
<artifactId>xxl-excel</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
接下来就是直接集成excel文件包
上图中是excel包中的内容
ExcelField
package com.example.demo.excel.annotation;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import java.lang.annotation.*;
/**
* 列属性信息
*
* 支持Java对象数据类型:Boolean、String、Short、Integer、Long、Float、Double、Date
* 支持Excel的Cell类型为:String
*
* @author xuxueli 2017-09-08 20:22:41
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelField {
/**
* 列名称
*
* @return String
*/
String name() default "";
/**
* 列宽 (大于0时生效; 如果不指定列宽,将会自适应调整宽度;)
*
* @return int
*/
int width() default 0;
/**
* 水平对齐方式
*
* @return HorizontalAlignment
*/
HorizontalAlignment align() default HorizontalAlignment.LEFT;
/**
* 时间格式化,日期类型时生效
*
* @return String
*/
String dateformat() default "yyyy-MM-dd HH:mm:ss";
}
ExcelSheet
package com.example.demo.excel.annotation;
import org.apache.poi.hssf.util.HSSFColor;
import java.lang.annotation.*;
/**
* 表信息
*
* @author xuxueli 2017-09-08 20:51:26
*/
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelSheet {
/**
* 表名称
*
* @return String
*/
String name() default "";
/**
* 表头/首行的颜色
*
* @return HSSFColorPredefined
*/
HSSFColor.HSSFColorPredefined headColor() default HSSFColor.HSSFColorPredefined.LIGHT_GREEN;
}
然后是util下的FieldReflectionUtil
package com.example.demo.excel.util;
import com.example.demo.excel.annotation.ExcelField;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* api request field, reflect util
* @author xuxueli 2017-05-26
*/
public final class FieldReflectionUtil {
private FieldReflectionUtil(){}
public static Byte parseByte(String value) {
try {
value = value.replaceAll(" ", "");
return Byte.valueOf(value);
} catch(NumberFormatException e) {
throw new RuntimeException("parseByte but input illegal input=" + value, e);
}
}
public static Boolean parseBoolean(String value) {
value = value.replaceAll(" ", "");
if (Boolean.TRUE.toString().equalsIgnoreCase(value)) {
return Boolean.TRUE;
} else if (Boolean.FALSE.toString().equalsIgnoreCase(value)) {
return Boolean.FALSE;
} else {
throw new RuntimeException("parseBoolean but input illegal input=" + value);
}
}
public static Integer parseInt(String value) {
try {
value = value.replaceAll(" ", "");
return Integer.valueOf(value);
} catch(NumberFormatException e) {
throw new RuntimeException("parseInt but input illegal input=" + value, e);
}
}
public static Short parseShort(String value) {
try {
value = value.replaceAll(" ", "");
return Short.valueOf(value);
} catch(NumberFormatException e) {
throw new RuntimeException("parseShort but input illegal input=" + value, e);
}
}
public static Long parseLong(String value) {
try {
value = value.replaceAll(" ", "");
return Long.valueOf(value);
} catch(NumberFormatException e) {
throw new RuntimeException("parseLong but input illegal input=" + value, e);
}
}
public static Float parseFloat(String value) {
try {
value = value.replaceAll(" ", "");
return Float.valueOf(value);
} catch(NumberFormatException e) {
throw new RuntimeException("parseFloat but input illegal input=" + value, e);
}
}
public static Double parseDouble(String value) {
try {
value = value.replaceAll(" ", "");
return Double.valueOf(value);
} catch(NumberFormatException e) {
th