excel导入导出(一)

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
<version>3.2.1</version>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.8.3</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.5</version>
</dependency>

package com.dt.FileIStream;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelCell {
/**
* 顺序 default 100
*
*/
int index(); /**
* 当值为null时要显示的值 default StringUtils.EMPTY
*
*/
String defaultValue() default ""; /**
* 用于验证
*
*/
Valid valid() default @Valid(); @Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@interface Valid { String[] in() default {}; boolean allowNull() default true; double gt() default Double.NaN; double lt() default Double.NaN; double ge() default Double.NaN; double le() default Double.NaN;
}
}

package com.dt.FileIStream;public class ExcelLog {
private Integer rowNum;
private Object object;
private String log; public Integer getRowNum() {
return rowNum;
}
public void setRowNum(Integer rowNum) {
this.rowNum = rowNum;
}
public Object getObject() {
return object;
}
public void setObject(Object object) {
this.object = object;
}
public String getLog() {
return log;
}
public ExcelLog(Object object, String log) {
super();
this.object = object;
this.log = log;
}
public ExcelLog(Object object, String log, Integer rowNum) {
super();
this.rowNum = rowNum;
this.object = object;
this.log = log;
}
public void setLog(String log) {
this.log = log;
}}

package com.dt.FileIStream;import com.cargosmart.dt.simoptz.util.ExcelUtil;import java.util.ArrayList;
import java.util.List;public class ExcelLogs {
private Boolean hasError;
private List<ExcelLog> logList; public ExcelLogs() {
super();
hasError = false;
}
public Boolean getHasError() {
return hasError;
}
public void setHasError(Boolean hasError) {
this.hasError = hasError;
}
public List<ExcelLog> getLogList() {
return logList;
}
public List<ExcelLog> getErrorLogList() {
List<ExcelLog> errList = new ArrayList<>();
for (ExcelLog log : this.logList) {
if (log != null && ExcelUtil.isNotBlank(log.getLog())) {
errList.add(log);
}
}
return errList;
}
public void setLogList(List<ExcelLog> logList) {
this.logList = logList;
}}

package com.dt.FileIStream;import java.util.Collection;
import java.util.Map;public class ExcelSheet<T> {
private String sheetName;
private Map<String,String> headers;
private Collection<T> dataset; /**
* @return the sheetName
*/
public String getSheetName() {
return sheetName;
} /**
* Excel页签名称
*
* @param sheetName
* the sheetName to set
*/
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
} /**
* Excel表头
*
*/
public Map<String,String> getHeaders() {
return headers;
} /**
* @param headers
* the headers to set
*/
public void setHeaders(Map<String,String> headers) {
this.headers = headers;
} /**
* Excel数据集合
*
* @return the dataset
*/
public Collection<T> getDataset() {
return dataset;
} /**
* @param dataset
* the dataset to set
*/
public void setDataset(Collection<T> dataset) {
this.dataset = dataset;
}}

package com.dt.FileIStream;import java.lang.reflect.Field;public class FieldForSortting {
private Field field;
private int index; public FieldForSortting(Field field) {
super();
this.field = field;
} public FieldForSortting(Field field, int index) {
super();
this.field = field;
this.index = index;
} public Field getField() {
return field;
} public void setField(Field field) {
this.field = field;
} public int getIndex() {
return index;
} public void setIndex(int index) {
this.index = index;
}}

public class Model {
@ExcelCell(index = 0)
private String a;
@ExcelCell(index = 1)
private String b;
@ExcelCell(index = 2)
private String c;
@ExcelCell(index = 3)
private Date d; public Date getD() {
return d;
} public void setD(Date d) {
this.d = d;
} public Model(String a, String b, String c,Date d) {
this.a = a;
this.b = b;
this.c = c;
this.d = d;
} /**
* @return the a
*/
public String getA() {
return a;
} /**
* @param a
* the a to set
*/
public void setA(String a) {
this.a = a;
} /**
* @return the b
*/
public String getB() {
return b;
} /**
* @param b
* the b to set
*/
public void setB(String b) {
this.b = b;
} /**
* @return the c
*/
public String getC() {
return c;
} /**
* @param c
* the c to set
*/
public void setC(String c) {
this.c = c;
}
}

package com.dt.util;import com.cargosmart.dt.simoptz.dscoe.FileIStream.*;
import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.ComparatorUtils;
import org.apache.commons.collections.comparators.ComparableComparator;
import org.apache.commons.collections.comparators.ComparatorChain;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;public class ExcelUtil { private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class); /**
* 用来验证excel与Vo中的类型是否一致 <br>
* Map<栏位类型,只能是哪些Cell类型>
*/
private static Map<Class<?>, CellType[]> validateMap = new HashMap<>(); static {
validateMap.put(String[].class, new CellType[]{CellType.STRING});
validateMap.put(Double[].class, new CellType[]{CellType.NUMERIC});
validateMap.put(String.class, new CellType[]{CellType.STRING});
validateMap.put(Double.class, new CellType[]{CellType.NUMERIC});
validateMap.put(Date.class, new CellType[]{CellType.NUMERIC, CellType.STRING});
validateMap.put(Integer.class, new CellType[]{CellType.NUMERIC});
validateMap.put(Float.class, new CellType[]{CellType.NUMERIC});
validateMap.put(Long.class, new CellType[]{CellType.NUMERIC});
validateMap.put(Boolean.class, new CellType[]{CellType.BOOLEAN});
} /**
* 获取cell类型的文字描述
*
* @param cellType <pre>
* CellType.BLANK
* CellType.BOOLEAN
* CellType.ERROR
* CellType.FORMULA
* CellType.NUMERIC
* CellType.STRING
* </pre>
* @return
*/
private static String getCellTypeByInt(CellType cellType) {
if(cellType == CellType.BLANK)
return "Null type";
else if(cellType == CellType.BOOLEAN)
return "Boolean type";
else if(cellType == CellType.ERROR)
return "Error type";
else if(cellType == CellType.FORMULA)
return "Formula type";
else if(cellType == CellType.NUMERIC)
return "Numeric type";
else if(cellType == CellType.STRING)
return "String type";
else
return "Unknown type";
} /**
* 获取单元格值
*
* @param cell
* @return
*/
private static Object getCellValue(Cell cell) {
if (cell == null
|| (cell.getCellTypeEnum() == CellType.STRING && isBlank(cell
.getStringCellValue()))) {
return null;
}
CellType cellType = cell.getCellTypeEnum();
if(cellType == CellType.BLANK)
return null;
else if(cellType == CellType.BOOLEAN)
return cell.getBooleanCellValue();
else if(cellType == CellType.ERROR)
return cell.getErrorCellValue();
else if(cellType == CellType.FORMULA) {
try {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
return cell.getNumericCellValue();
}
} catch (IllegalStateException e) {
return cell.getRichStringCellValue();
}
}
else if(cellType == CellType.NUMERIC){
if ((cell).getBooleanCellValue()) {
return cell.getDateCellValue();
} else {
return cell.getNumericCellValue();
}
}
else if(cellType == CellType.STRING)
return cell.getStringCellValue();
else
return null;
} public static <T> void exportExcel(Map<String,String> headers, Collection<T> dataset, OutputStream out) {
exportExcel(headers, dataset, out, null);
} public static <T> void exportExcel(Map<String,String> headers, Collection<T> dataset, OutputStream out,
String pattern) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(); write2Sheet(sheet, headers, dataset, pattern);
try {
workbook.write(out);
} catch (IOException e) {
logger.error(e.toString(), e);
}
} public static void exportExcel(String[][] datalist, OutputStream out,boolean autoColumnWidth) {
try {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(); for (int i = 0; i < datalist.length; i++) {
String[] r = datalist[i];
HSSFRow row = sheet.createRow(i);
for (int j = 0; j < r.length; j++) {
HSSFCell cell = row.createCell(j);
//cell max length 32767
if (r[j] != null && r[j].length() > 32767) {
r[j] = "--此字段过长(超过32767),已被截断--" + r[j];
r[j] = r[j].substring(0, 32766);
}
cell.setCellValue(r[j]);
}
}
//自动列宽
if(autoColumnWidth) {
if (datalist.length > 0) {
int colcount = datalist[0].length;
for (int i = 0; i < colcount; i++) {
sheet.autoSizeColumn(i);
}
}
}
workbook.write(out);
} catch (IOException e) {
logger.error(e.toString(), e);
}
}
public static void exportExcel(String[][] datalist, OutputStream out) {
exportExcel(datalist,out,true);
} public static <T> void exportExcel(List<ExcelSheet<T>> sheets, OutputStream out) {
exportExcel(sheets, out, null);
} public static <T> void exportExcel(List<ExcelSheet<T>> sheets, OutputStream out, String pattern) {
if (CollectionUtils.isEmpty(sheets)) {
return;
}
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
for (ExcelSheet<T> sheet : sheets) {
// 生成一个表格
HSSFSheet hssfSheet = workbook.createSheet(sheet.getSheetName());
write2Sheet(hssfSheet, sheet.getHeaders(), sheet.getDataset(), pattern);
}
try {
workbook.write(out);
} catch (IOException e) {
logger.error(e.toString(), e);
}
} /**
* 每个sheet的写入
*
* @param sheet 页签
* @param headers 表头
* @param dataset 数据集合
* @param pattern 日期格式
*/
private static <T> void write2Sheet(HSSFSheet sheet, Map<String,String> headers, Collection<T> dataset,
String pattern) {
//时间格式默认"yyyy-MM-dd"
if (isBlank(pattern)){
pattern = "yyyy-MM-dd";
}
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
// 标题行转中文
Set<String> keys = headers.keySet();
Iterator<String> it1 = keys.iterator();
String key = ""; //存放临时键变量
int c= 0; //标题列数
while (it1.hasNext()){
key = it1.next();
if (headers.containsKey(key)) {
HSSFCell cell = row.createCell(c);
HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
cell.setCellValue(text);
c++;
}
} // 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = it.next();
try {
if (t instanceof Map) {
//允许您选择性地取消特定代码段（即，类或方法）中的警告
@SuppressWarnings("unchecked")
Map<String, Object> map = (Map<String, Object>) t;
int cellNum = 0;
//遍历列名
Iterator<String> it2 = keys.iterator();
while (it2.hasNext()){
key = it2.next();
if (!headers.containsKey(key)) {
logger.error("Map 中 不存在 key [" + key + "]");
continue;
}
Object value = map.get(key);
HSSFCell cell = row.createCell(cellNum); cellNum = setCellValue(cell,value,pattern,cellNum,null,row); cellNum++;
}
} else {
List<FieldForSortting> fields = sortFieldByAnno(t.getClass());
int cellNum = 0;
for (int i = 0; i < fields.size(); i++) {
HSSFCell cell = row.createCell(cellNum);
Field field = fields.get(i).getField();
field.setAccessible(true);
Object value = field.get(t); cellNum = setCellValue(cell,value,pattern,cellNum,field,row); cellNum++;
}
}
} catch (Exception e) {
logger.error(e.toString(), e);
}
}
// 设定自动宽度
for (int i = 0; i < headers.size(); i++) {
sheet.autoSizeColumn(i);
}
} private static int setCellValue(HSSFCell cell,Object value,String pattern,int cellNum,Field field,HSSFRow row){
String textValue = null;
if (value instanceof Integer) {
int intValue = (Integer) value;
cell.setCellValue(intValue);
} else if (value instanceof Float) {
float fValue = (Float) value;
cell.setCellValue(fValue);
} else if (value instanceof Double) {
double dValue = (Double) value;
cell.setCellValue(dValue);
} else if (value instanceof Long) {
long longValue = (Long) value;
cell.setCellValue(longValue);
} else if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
cell.setCellValue(bValue);
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else if (value instanceof String[]) {
String[] strArr = (String[]) value;
for (int j = 0; j < strArr.length; j++) {
String str = strArr[j];
cell.setCellValue(str);
if (j != strArr.length - 1) {
cellNum++;
cell = row.createCell(cellNum);
}
}
} else if (value instanceof Double[]) {
Double[] douArr = (Double[]) value;
for (int j = 0; j < douArr.length; j++) {
Double val = douArr[j];
// 值不为空则set Value
if (val != null) {
cell.setCellValue(val);
} if (j != douArr.length - 1) {
cellNum++;
cell = row.createCell(cellNum);
}
}
} else {
// 其它数据类型都当作字符串简单处理
String empty = "";
if(field != null) {
ExcelCell anno = field.getAnnotation(ExcelCell.class);
if (anno != null) {
empty = anno.defaultValue();
}
}
textValue = value == null ? empty : value.toString();
}
if (textValue != null) {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
return cellNum;
} /**
* 把Excel的数据封装成voList
*
* @param clazz vo的Class
* @param inputStream excel输入流
* @param pattern 如果有时间数据，设定输入格式。默认为"yyy-MM-dd"
* @param logs 错误log集合
* @param arrayCount 如果vo中有数组类型,那就按照index顺序,把数组应该有几个值写上.
* @return voList
* @throws RuntimeException
*/
public static <T> Collection<T> importExcel(Class<T> clazz, InputStream inputStream,
String pattern, ExcelLogs logs, Integer... arrayCount) {
Workbook workBook;
try {
workBook = WorkbookFactory.create(inputStream);
} catch (Exception e) {
logger.error("load excel file error",e);
return null;
}
List<T> list = new ArrayList<>();
Sheet sheet = workBook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.rowIterator();
try {
List<ExcelLog> logList = new ArrayList<>();
// Map<title,index>
Map<String, Integer> titleMap = new HashMap<>(); while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (row.getRowNum() == 0) {
if (clazz == Map.class) {
// 解析map用的key,就是excel标题行
Iterator<Cell> cellIterator = row.cellIterator();
Integer index = 0;
while (cellIterator.hasNext()) {
String value = cellIterator.next().getStringCellValue();
titleMap.put(value, index);
index++;
}
}
continue;
}
// 整行都空，就跳过
boolean allRowIsNull = true;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Object cellValue = getCellValue(cellIterator.next());
if (cellValue != null) {
allRowIsNull = false;
break;
}
}
if (allRowIsNull) {
logger.warn("Excel row " + row.getRowNum() + " all row value is null!");
continue;
}
StringBuilder log = new StringBuilder();
if (clazz == Map.class) {
Map<String, Object> map = new HashMap<>();
for (String k : titleMap.keySet()) {
Integer index = titleMap.get(k);
Cell cell = row.getCell(index);
// 判空
if (cell == null) {
map.put(k, null);
} else {
cell.setCellType(CellType.STRING);
String value = cell.getStringCellValue();
map.put(k, value);
}
}
list.add((T) map); } else {
T t = clazz.newInstance();
int arrayIndex = 0;// 标识当前第几个数组了
int cellIndex = 0;// 标识当前读到这一行的第几个cell了
List<FieldForSortting> fields = sortFieldByAnno(clazz);
for (FieldForSortting ffs : fields) {
Field field = ffs.getField();
field.setAccessible(true);
if (field.getType().isArray()) {
Integer count = arrayCount[arrayIndex];
Object[] value;
if (field.getType().equals(String[].class)) {
value = new String[count];
} else {
// 目前只支持String[]和Double[]
value = new Double[count];
}
for (int i = 0; i < count; i++) {
Cell cell = row.getCell(cellIndex);
String errMsg = validateCell(cell, field, cellIndex);
if (isBlank(errMsg)) {
value[i] = getCellValue(cell);
} else {
log.append(errMsg);
log.append(";");
logs.setHasError(true);
}
cellIndex++;
}
field.set(t, value);
arrayIndex++;
} else {
Cell cell = row.getCell(cellIndex);
String errMsg = validateCell(cell, field, cellIndex);
if (isBlank(errMsg)) {
Object value = null;
// 处理特殊情况,Excel中的String,转换成Bean的Date
if (field.getType().equals(Date.class)
&& cell.getCellTypeEnum() == CellType.STRING) {
Object strDate = getCellValue(cell);
try {
value = new SimpleDateFormat(pattern).parse(strDate.toString());
} catch (ParseException e) { errMsg =
MessageFormat.format("the cell [{0}] can not be converted to a date ",
CellReference.convertNumToColString(cell.getColumnIndex()));
}
} else {
value = getCellValue(cell);
// 处理特殊情况,excel的value为String,且bean中为其他,且defaultValue不为空,那就=defaultValue
ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
if (value instanceof String && !field.getType().equals(String.class)
&& isNotBlank(annoCell.defaultValue())) {
value = annoCell.defaultValue();
}
}
field.set(t, value);
}
if (isNotBlank(errMsg)) {
log.append(errMsg);
log.append(";");
logs.setHasError(true);
}
cellIndex++;
}
}
list.add(t);
logList.add(new ExcelLog(t, log.toString(), row.getRowNum() + 1));
}
}
logs.setLogList(logList);
} catch (InstantiationException e) {
throw new RuntimeException(MessageFormat.format("can not instance class:{0}",
clazz.getSimpleName()), e);
} catch (IllegalAccessException e) {
throw new RuntimeException(MessageFormat.format("can not instance class:{0}",
clazz.getSimpleName()), e);
}
return list;
} /**
* 驗證Cell類型是否正確
*
* @param cell cell單元格
* @param field 欄位
* @param cellNum 第幾個欄位,用於errMsg
* @return
*/
private static String validateCell(Cell cell, Field field, int cellNum) {
//通过CellReference获得当前cell单元格所在列
String columnName = CellReference.convertNumToColString(cellNum);
String result = null;
CellType[] cellTypeArr = validateMap.get(field.getType());
if (cellTypeArr == null) {
result = MessageFormat.format("Unsupported type [{0}]", field.getType().getSimpleName());
return result;
}
ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
if (cell == null
|| (cell.getCellTypeEnum() == CellType.STRING && isBlank(cell
.getStringCellValue()))) {
if (annoCell != null && annoCell.valid().allowNull() == false) {
result = MessageFormat.format("the cell [{0}] can not null", columnName);
}
;
} else if (cell.getCellTypeEnum() == CellType.BLANK && annoCell.valid().allowNull()) {
return result;
} else {
List<CellType> cellTypes = Arrays.asList(cellTypeArr); // 如果類型不在指定範圍內,並且沒有默認值
if (!(cellTypes.contains(cell.getCellTypeEnum()))
|| isNotBlank(annoCell.defaultValue())
&& cell.getCellTypeEnum() == CellType.STRING) {
StringBuilder strType = new StringBuilder();
for (int i = 0; i < cellTypes.size(); i++) {
CellType cellType = cellTypes.get(i);
strType.append(getCellTypeByInt(cellType));
if (i != cellTypes.size() - 1) {
strType.append(",");
}
}
result =
MessageFormat.format("the cell [{0}] type must [{1}]", columnName, strType.toString());
} else {
// 类型符合验证,但值不在要求范围内的
// String in
if (annoCell.valid().in().length != 0 && cell.getCellTypeEnum() == CellType.STRING) {
String[] in = annoCell.valid().in();
String cellValue = cell.getStringCellValue();
boolean isIn = false;
for (String str : in) {
if (str.equals(cellValue)) {
isIn = true;
}
}
if (!isIn) {
result = MessageFormat.format("the cell [{0}] value must in {1}", columnName, in);
}
}
// 数字型
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
double cellValue = cell.getNumericCellValue();
// 小于
if (!Double.isNaN(annoCell.valid().lt())) {
if (!(cellValue < annoCell.valid().lt())) {
result =
MessageFormat.format("the cell [{0}] value must less than [{1}]", columnName,
annoCell.valid().lt());
}
}
// 大于
if (!Double.isNaN(annoCell.valid().gt())) {
if (!(cellValue > annoCell.valid().gt())) {
result =
MessageFormat.format("the cell [{0}] value must greater than [{1}]", columnName,
annoCell.valid().gt());
}
}
// 小于等于
if (!Double.isNaN(annoCell.valid().le())) {
if (!(cellValue <= annoCell.valid().le())) {
result =
MessageFormat.format("the cell [{0}] value must less than or equal [{1}]",
columnName, annoCell.valid().le());
}
}
// 大于等于
if (!Double.isNaN(annoCell.valid().ge())) {
if (!(cellValue >= annoCell.valid().ge())) {
result =
MessageFormat.format("the cell [{0}] value must greater than or equal [{1}]",
columnName, annoCell.valid().ge());
}
}
}
}
}
return result;
} /**
* 根据annotation的seq排序后的栏位
*
* @param clazz
* @return
*/
private static List<FieldForSortting> sortFieldByAnno(Class<?> clazz) {
Field[] fieldsArr = clazz.getDeclaredFields();
List<FieldForSortting> fields = new ArrayList<>();
List<FieldForSortting> annoNullFields = new ArrayList<>();
for (Field field : fieldsArr) {
ExcelCell ec = field.getAnnotation(ExcelCell.class);
if (ec == null) {
// 没有ExcelCell Annotation 视为不汇入
continue;
}
int id = ec.index();
fields.add(new FieldForSortting(field, id));
}
fields.addAll(annoNullFields);
sortByProperties(fields, true, false, "index");
return fields;
} private static void sortByProperties(List<? extends Object> list, boolean isNullHigh,
boolean isReversed, String... props) {
if (CollectionUtils.isNotEmpty(list)) {
Comparator<?> typeComp = ComparableComparator.getInstance();
if (isNullHigh == true) {
typeComp = ComparatorUtils.nullHighComparator(typeComp);
} else {
typeComp = ComparatorUtils.nullLowComparator(typeComp);
}
if (isReversed) {
typeComp = ComparatorUtils.reversedComparator(typeComp);
} List<Object> sortCols = new ArrayList<Object>(); if (props != null) {
for (String prop : props) {
sortCols.add(new BeanComparator(prop, typeComp));
}
}
if (sortCols.size() > 0) {
Comparator<Object> sortChain = new ComparatorChain(sortCols);
Collections.sort(list, sortChain);
}
}
} private static boolean isBlank(String str){
if(str == null){
return true;
}
return str.length() == 0;
} public static boolean isNotBlank(String str){
return !isBlank(str);
}}

import org.junit.Test;import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;public class TestExportBean {
@Test
public void exportXls() throws IOException {
//用排序的Map且Map的键应与ExcelCell注解的index对应
Map<String,String> map = new LinkedHashMap<>();
map.put("a","姓名");
map.put("b","年龄");
map.put("c","性别");
map.put("d","出生日期");
Collection<Object> dataset=new ArrayList<Object>();
dataset.add(new Model("", "", "",null));
dataset.add(new Model("fg", "25", "分隔符",null));
dataset.add(new Model("王五", "34", "男",new Date()));
File f=new File("test11.xls");
OutputStream out =new FileOutputStream(f); ExcelUtil.exportExcel(map, dataset, out);
out.close();
}
}

import org.junit.Test;import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;public class TestExportMap {
@Test
public void exportXls() throws IOException {
List<Map<String,Object>> list = new ArrayList<>();
Map<String,Object> map =new LinkedHashMap<>();
map.put("name", "");
map.put("age", "");
map.put("birthday","");
map.put("sex","");
Map<String,Object> map2 =new LinkedHashMap<String, Object>();
map2.put("name", "测试是否是中文长度不能自动宽度.测试是否是中文长度不能自动宽度.");
map2.put("age", null);
map2.put("sex", null);
map.put("birthday",null);
Map<String,Object> map3 =new LinkedHashMap<String, Object>();
map3.put("name", "张三");
map3.put("age", 12);
map3.put("sex", "男");
map3.put("birthday",new Date());
list.add(map);
list.add(map2);
list.add(map3);
Map<String,String> map1 = new LinkedHashMap<>();
map1.put("name","姓名");
map1.put("age","年龄");
map1.put("birthday","出生日期");
map1.put("sex","性别");
File f= new File("test2.xls");
OutputStream out = new FileOutputStream(f);
ExcelUtil.exportExcel(map1,list, out );
out.close();
}
}

import org.junit.Test;import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.Collection;
import java.util.Map;/**
* 测试导入Excel 97/2003
*/
public class TestImportExcel { @Test
public void importXls() throws FileNotFoundException {
File f=new File("src/test/resources/test.xls");
InputStream inputStream= new FileInputStream(f); ExcelLogs logs =new ExcelLogs();
Collection<Map> importExcel = ExcelUtil.importExcel(Map.class, inputStream, "yyyy/MM/dd HH:mm:ss", logs , 0); for(Map m : importExcel){
System.out.println(m);
}
} @Test
public void importXlsx() throws FileNotFoundException {
File f=new File("src/test/resources/test.xlsx");
InputStream inputStream= new FileInputStream(f); ExcelLogs logs =new ExcelLogs();
Collection<Map> importExcel = ExcelUtil.importExcel(Map.class, inputStream, "yyyy/MM/dd HH:mm:ss", logs , 0); for(Map m : importExcel){
System.out.println(m);
}
}}