Excel生成类:
package com.framework.common.utils;
import com.efounder.JEnterprise.model.excelres.ExcelReturn;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @parameter 基于poi的Excel导出导入工具类
* @version 1.0
*/
/*
* ExcelUtil工具类实现功能:
* 导出时传入list<T>,即可实现导出为一个excel,其中每个对象T为Excel中的一条记录.
* 导入时读取excel,得到的结果是一个list<T>.T是自己定义的对象.
* 需要导出的实体对象只需简单配置注解,通过注解可以方便实现下面功能:
* 1.实体属性配置了注解就能导出到excel中,每个属性都对应一列.
* 2.列名称可以通过注解配置.
* 3.导出到哪一列可以通过注解配置.
* 4.鼠标移动到该列时提示信息可以通过注解配置.
* 5.用注解设置只能下拉选择不能随意填写功能.
* 6.用注解设置是否只导出标题而不导出内容,这在导出内容作为模板以供用户填写时比较实用.
*/
public class ExcelUtil<T> {
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
Class<T> clazz;
public ExcelUtil(Class<T> clazz) {
this.clazz = clazz;
}
/**
* excel数据导入
* @param sheetName
* @param input
* @return
*/
public List<T> importExcel(String sheetName, InputStream input) {
List<T> list = new ArrayList<T>();
try {
Workbook book = Workbook.getWorkbook(input);
Sheet sheet = null;
if (!"".equals(sheetName.trim())) {
// 如果指定sheet名,则取指定sheet中的内容.
sheet = book.getSheet(sheetName);
}
if (sheet == null) {
// 如果传入的sheet名不存在则默认指向第1个sheet.
sheet = book.getSheet(0);
}
// 得到数据的行数
int rows = sheet.getRows();
// 有数据时才处理
if (rows > 0) {
// 得到类的所有field.
Field[] allFields = clazz.getDeclaredFields();
// 定义一个map用于存放列的序号和field.
Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
for (Field field : allFields) {
// 将有注解的field存放到map中.
if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class);
// 获得列号
int col = getExcelCol(attr.column());
// 设置类的私有字段属性可访问.
field.setAccessible(true);
fieldsMap.put(col, field);
}
}
// 从第2行开始取数据,默认第一行是表头
for (int i = 1; i < rows; i++) {
// 得到一行中的所有单元格对象.
Cell[] cells = sheet.getRow(i);
T entity = null;
for (int j = 0; j < cells.length; j++) {
// 单元格中的内容.
String c = cells[j].getContents();
if ("".equals(c)) {
continue;
}
// 如果不存在实例则新建.
entity = (entity == null ? clazz.newInstance() : entity);
// 从map中得到对应列的field.
Field field = fieldsMap.get(j);
// 取得类型,并根据对象类型设置值.
Class<?> fieldType = field.getType();
if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
field.set(entity, Integer.parseInt(c));
} else if (String.class == fieldType) {
field.set(entity, String.valueOf(c));
} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
field.set(entity, Long.valueOf(c));
} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
field.set(entity, Float.valueOf(c));
} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
field.set(entity, Short.valueOf(c));
} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
field.set(entity, Double.valueOf(c));
} else if (Character.TYPE == fieldType) {
if ((c != null) && (c.length() > 0)) {
field.set(entity, Character.valueOf(c.charAt(0)));
}
}
}
if (entity != null) {
list.add(entity);
}
}
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
}
return list;
}
/**
*
* @Title: resList
* @Description:导出返回(数据源是含实体类的List)
* @return: ExcelReturn
* @throws
*/
public ExcelReturn resList(String FileName, String FilePath, List<T> list, boolean isFreezeFirstRow) {
return resListDynamicProcess(FileName, FilePath, list, null, null, null, null, isFreezeFirstRow);
}
public ExcelReturn resList(String FileName, String FilePath, List<T> list) {
return resListDynamicProcess(FileName, FilePath, list, null, null, null, null, true);
}
/**
*
* @Description:导出返回(数据源是含实体类的List)(含有合并单元格样式mergedRegionList)
* @param FileName
* @param FilePath
* @param list
* @return ExcelReturn
*/
public ExcelReturn resList(String FileName, String FilePath, List<T> list, List<List<Map<String, Integer>>> mergedRegionList, boolean isFreezeFirstRow) {
return resListDynamicProcess(FileName, FilePath, list, null, null, null, mergedRegionList, isFreezeFirstRow);
}
public ExcelReturn resList(String FileName, String FilePath, List<T> list, List<List<Map<String, Integer>>> mergedRegionList) {
return resListDynamicProcess(FileName, FilePath, list, null, null, null, mergedRegionList, true);
}
/**
*
* @Title: resListDynamic
* @Description:导出返回(数据源是含不含实体类的Map<key,value>实体类的List),alias:key的List,names:value的List
* @return: ExcelReturn
* @throws
*/
public ExcelReturn resListDynamic(String FileName, String FilePath, List<Map<String, Object>> list, List<Object> alias, List<Object> names, boolean isFreezeFirstRow) {
return resListDynamicProcess(FileName, FilePath, null, list, alias, names, null, isFreezeFirstRow);
}
public ExcelReturn resListDynamic(String FileName, String FilePath, List<Map<String, Object>> list, List<Object> alias, List<Object> names) {
return resListDynamicProcess(FileName, FilePath, null, list, alias, names, null, true);
}
/**
*
* @Description:导出返回(数据源是含不含实体类的Map<key,value>实体类的List)(含有合并单元格样式mergedRegionList),alias: