因为程序版本的限制,最多只能用到poi3.15,所以用不了easyexcel。小类初衷。草率的写,也没有测试什么很大量数据的情况,有问题可以指出来哟。
由于程序的中心思想是解决导出excel,所以数据是单纯的对象属性不包括list等等。分为两个步骤,一个是注释,还有一个就是工具类操作。
我的程序是测试导成本地的,如果你要用来发给前端什么的也可以另外写输出。
看了别人文章发现我好像确实草率的写了,因为就支持String类型,参考了一下补一个基础类型均可的转换函数。
由于业务需要,需要搞带下拉框和级联下拉的模板,所以又补充了一些功能。
由于特别需要,又加了数据导出还能带下拉级联,还有合并数据也能导出和导入的情况
注释定义:
/**
* @Auther: ms.y
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Inherited
public @interface PoiExcel {
String value();
int index();
boolean isString() default false;
int ref() default 0;
String format() default "";
boolean mergable() default false;
int cellWidth() default 2;
}
注释类的属性分别表示
value 表示标题
index 表示在excel中的列位置,从0开始
isString处理数字字符串被读取成数字导致的科学计数法和末尾加.0问题
ref表示级联下拉引用的上一级列数据
format如果是数值的话格式化数值
mergable该列是否合并,用于写入excel,导入本身已写判断
cellWidth列宽,设置为默认列宽的两倍,填倍数
工具类的使用:1.调用工具类导出
HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(null, a, TestEntity.class, 1);
2.excel导成对象
FileInputStream fis = new FileInputStream(new File(“F://test.xls”));
List testList = ExcelUtil.readExcelData(fis, TestEntity.class);
3.导出带下拉框和级联的的模板
HSSFWorkbook wb = ExcelUtil.createModel(“test”,t);
1.对象导出成数据,如果传入字典值参数,根据传入的字典值,部分列数据带下拉或级联框的数据
ExcelUtil.getHSSFWorkbook
2.对象导出成数据,根据列上注释是否可合并,判断列值并合并,如果传入字典值参数,根据传入的字典值,部分列数据带下拉或级联框的数据
ExcelUtil.getHSSFWorkbookMergable
3.读取excel数据,如果存在合并数据,也会对应读取到每条数据中
ExcelUtil.readExcelData
4.创建模板
ExcelUtil.createModel
PS:只支持Map<String,List>数据的级联下拉,并且map的key必须是ref列中的List的值,而且list值不能是单纯字母+数字,
容易导致excel无法识别是数据还是列值,因此导致数据下拉失效,也不能数字开头和特殊字符
思路详解:
1.导出,导出是将对象导出excel,以list中每条数据对应excel每行
a.每个sheet页的创建表头
b.反射获取对象中每个属性对应excel中的列值
c.反射读取数据,写入excel
2.导入,导入以excel读取,转换成实体对象队列
a.循环读取sheet页行数据
b.反射实例化对象,并获取对象上列配置
c.根据列配置读取对应列数据存入对应对象属性
3.导出模板,带下拉和级联,普通属性以创建表头为主,带参数的list和map才是处理的重点
a.获取对象属性注释,普通属性直接创建对应列表头
b.如果是list数据,表示下拉,反射获取对应属性对象list,获取属性对象size和方法get,循环获取list中的值,把获取的值存在新建的隐藏sheet注释在对象上的行值中,建立命名管理,并在对应列中建立数据有效性索引
c.如果是map数据,也是先获取属性对象map,获取entrySet方法,获取entrySet,再循环entrySet,把数据依旧写入注释对应行中,因为一个map存对应的list数据,所以每个list都要建立命名管理,并以key为命名,之后再在对应列创建数据有效性索引。
注:
为什么在模板中的隐藏数据要存成行不是列,是每次新建行数据,原先写入的行数据被清空,所以数据存以行为基准,注释中的index即是模板和导入的列,也是隐藏数据存储中的行值。第二个注意的点是ref指向的是级联的前一级,前一级可以是数据也可以是list或者级联数据,但是这个数据的值,必然是后一级的命名中的key值,这个必须注意,不清楚可以去百度excel级联怎么搞,看一下就理解了
工具类定义:
/**
* @Auther: ms.y
*/
public class ExcelUtil<T> {
/**
* 根据传入的对象导出excel
*
* @param wb
* @param dataList
* @param <T>
* @param pageSize 一页多少条
* @return
*/
public static <T> HSSFWorkbook getHSSFWorkbook(HSSFWorkbook wb, List<T> dataList, Class<T> clazz, int pageSize) {
if (wb == null) {
wb = new HSSFWorkbook();
}
if (dataList == null || dataList.size() == 0) {
createHeader(wb, String.valueOf(0), clazz);
return wb;
}
int j = 0;
int maxRow = pageSize;//sheet.getLastRowNum();65535
if (maxRow > 65535) {
maxRow = 65535;
}
int dataSize = dataList.size();
Sheet sheet = null;
for (int i = 0; i < dataSize; i++) {
if (i % maxRow == 0) {
sheet = createHeader(wb, String.valueOf(j++), clazz);
}
writeDatas(sheet, dataList.get(i), clazz, (i % maxRow + 1));
}
try {
if (wb != null) {
wb.close();
}
} catch (IOException e) {
e.printStackTrace();
return null;
}
return wb;
}
/**
* 根据传入的对象导出excel,数据列可合并
*
* @param wb
* @param dataList
* @param <T>
* @param pageSize 一页多少条
* @return
*/
public static <T> HSSFWorkbook getHSSFWorkbookMergable(HSSFWorkbook wb, List<T> dataList, Class<T> clazz, int pageSize) {
if (wb == null) {
wb = new HSSFWorkbook();
}
if (dataList == null || dataList.size() == 0) {
createHeader(wb, String.valueOf(0), clazz);
return wb;
}
int j = 0;
int maxRow = pageSize;//sheet.getLastRowNum();65535
if (maxRow > 65535) {
maxRow = 65535;
}
int dataSize = dataList.size();
Sheet sheet = null;
for (int i = 0; i < dataSize; i++) {
if (i % maxRow == 0) {
sheet = createHeader(wb, String.valueOf(j++), clazz);
}
writeDatas(sheet, dataList.get(i), clazz, (i % maxRow + 1));
}
int page = (int) Math.ceil(Double.valueOf(dataSize) / Double.valueOf(maxRow));
for (int i = 0; i < page; i++) {
sheet = wb.getSheetAt(i);
int end = (i + 1) * maxRow;
end = end > dataSize ? dataSize : end;
List<int[]> mergeRegions = getDataMergeRange(dataList.subList(i * maxRow, end), clazz);
if (mergeRegions.size() == 0) {
continue;
}
createMergeRegion(sheet, mergeRegions);
}
try {
if (wb != null) {
wb.close();
}
} catch (IOException e) {
e.printStackTrace();
return null;
}
return wb;
}
/**
* 导出带下拉或级联框的数据
*
* @param wb
* @param dataList
* @param clazz
* @param pageSize
* @param dictionary
* @param <T>
* @param <K>
* @return
*/
public static <T, K> HSSFWorkbook getHSSFWorkbook(HSSFWorkbook wb, List<T> dataList, Class<T> clazz, int pageSize, K dictionary) {
if (wb == null) {
wb = new HSSFWorkbook();
}
if (dataList == null || dataList.size() == 0) {
createHeader(wb, String.valueOf(0), clazz);
return wb;
}
int j = 0;
int maxRow = pageSize;//sheet.getLastRowNum();65535
if (maxRow > 65535) {
maxRow = 65535;
}
int dataSize = dataList.size();
Sheet sheet = null;
for (int i = 0; i < dataSize; i++) {
if (i % maxRow == 0) {
sheet = createHeader(wb, String.valueOf(j++), clazz);
}
writeDatas(sheet, dataList.get(i), clazz, (i % maxRow + 1));
createDropDownData(wb, sheet, dictionary);
}
try {
if (wb != null) {
wb.close();
}
} catch (IOException e) {
e.printStackTrace();
return null;
}
return wb;
}
/**
* 导出带下拉或级联框的数据
*
* @param wb
* @param dataList
* @param clazz
* @param pageSize
* @param dictionary
* @param <T>
* @param <K>
* @return
*/
public static <T, K> HSSFWorkbook getHSSFWorkbookMergable(HSSFWorkbook wb, List<T> dataList, Class<T> clazz, int pageSize, K dictionary) {
if (wb == null) {
wb = new HSSFWorkbook();
}
if (dataList == null || dataList.size() == 0) {
createHeader(wb, String.valueOf(0), clazz);
return wb;
}
int j = 0;
int maxRow = pageSize;//sheet.getLastRowNum();65535
if (maxRow > 65535) {
maxRow = 65535;
}
int dataSize = dataList.size();
Sheet sheet = null;
for (int i = 0; i < dataSize; i++) {
if (i % maxRow == 0) {
sheet = createHeader(wb, String.valueOf(j++), clazz);
}
writeDatas(sheet, dataList.get(i), clazz, (i % maxRow + 1));
createDropDownData(wb, sheet, dictionary);
}
int page = (int) Math.ceil(Double.valueOf(dataSize) / Double.valueOf(maxRow));
for (int i = 0; i < page; i++) {
sheet = wb.getSheetAt(i);
int end = (i + 1) * maxRow;
end = end > dataSize ? dataSize : end;
List<int[]> mergeRegions = getDataMergeRange(dataList.subList(i * maxRow, end), clazz);
if (mergeRegions.size() == 0) {
continue;
}
createMergeRegion(sheet, mergeRegions);
}
try {
if (wb != null) {
wb.close();
}
} catch (IOException e) {
e.printStackTrace();
return null;
}
return wb;
}
/**
* 创建合并区域
*/
private static void createMergeRegion(Sheet sheet, List<int[]> mergeRange) {
//range[0], range[1], range[2], range[3] 表示 起始行,终止行,起始列,终止列
mergeRange.stream().forEach(range -> sheet.addMergedRegion(new CellRangeAddress(range[0], range[1], range[2], range[3])));
}
/**
* 获取数据合并区域
* @param data
* @param clazz
* @param <T>
* @return
*/
private static <T> List<int[]> getDataMergeRange(List<T> data, Class<T> clazz) {
List<int[]> ranges = new ArrayList<>();
Field[] fields = clazz.getDeclaredFields();
List<Field> fieldList = Arrays.asList(fields);
if (data.size() <= 1) {
return ranges;
}
fieldList.stream().forEach(field -> {
field.setAccessible(true);
PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
if (poiExcel != null && poiExcel.mergable()) {
Object firstRowData = null;
Object lastRowData = null;
Object firstValue = null;
Object lastValue = null;
int firstRow = 0;
int lastRow = 1;
int dataSize = data.size();
int lastRowNum = dataSize - 1;
try {
while (lastRow < dataSize) {
if (lastRow == 1) {
firstRowData = data.get(firstRow);
lastRowData = data.get(lastRow);
firstValue = field.get(firstRowData);
lastValue = field.get(lastRowData);
}
if (firstRowData == null || lastRowData == null || firstValue == null || lastValue == null || !String.valueOf(firstValue).equals(String.valueOf(lastValue))) {
if ((lastRow != firstRow + 1)) {
int[] range = new int[]{firstRow + 1, lastRow, poiExcel.index(), poiExcel.index()};
ranges.add(range);
}
if (lastRow != lastRowNum) {
firstRow = lastRow;
firstRowData = data.get(firstRow);
firstValue = field.get(firstRowData);
}
} else if (lastRow == lastRowNum && String.valueOf(firstValue).equals(String.valueOf(lastValue))) {
int[] range = new int[]{firstRow + 1, lastRow + 1, poiExcel.index(), poiExcel.index()};
ranges.add(range);
}
lastRow++;
if (lastRow != dataSize) {
lastRowData = data.get(lastRow);
lastValue = field.get(lastRowData);
}
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
});
return ranges;
}
/**
* 创建下拉数据
*
* @param wb
* @param data
*/
private static <T> void createDropDownData(Workbook wb, Sheet sheet, T data) {
Class clazz = data.getClass();
Field[] fields = clazz.getDeclaredFields();
List<Field> fieldList = Arrays.asList(fields);
boolean tag = wb.getSheet("hidden") == null;
Sheet hiddenSheet = wb.getSheet("hidden") == null ? wb.createSheet("hidden") : wb.getSheet("hidden");
wb.setSheetHidden(1, true);
fieldList.stream().forEach(field -> {
field.setAccessible(true);
PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
if (poiExcel != null) {
if (List.class.isAssignableFrom(field.getType())) {
String[] dropDownDatas;
Type type = field.getGenericType();
if (type instanceof ParameterizedType) {
try {
Object fieldData = field.get(data);
if (fieldData != null) {
if (tag) {
Class StringClazz = fieldData.getClass();//获取到属性的值的Class对象
Method m = StringClazz.getDeclaredMethod("size");
int size = (Integer) m.invoke(fieldData);//调用list的size方法,得到list的长度
dropDownDatas = new String[size];
for (int i = 0; i < size; i++) {//遍历list,调用get方法,获取list中的对象实例
Method getM = StringClazz.getDeclaredMethod("get", int.class);
getM.setAccessible(true);
dropDownDatas[i] = getM.invoke(fieldData, i).toString();
}
Row row = hiddenSheet.createRow(poiExcel.index());
for (int i = 0; i < dropDownDatas.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(dropDownDatas[i]);
}
createDataName(wb, "list" + poiExcel.index(), "hidden!$A$" + (poiExcel.index() + 1) + ":$" + getPos(dropDownDatas.length)
+ "$" + (poiExcel.index() + 1));
}
DataValidation data_validation = createDataValidation("list" + poiExcel.index(), 1, 65533,
poiExcel.index(), poiExcel.index());
sheet.addValidationData(data_validation);
}
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
if (Map.class.isAssignableFrom(field.getType())) {
Type type = field.getGenericType();
if (type instanceof ParameterizedType) {
try {
Object fieldData = field.get(data);
if (fieldData != null) {
if (tag) {
Class mapClazz = fieldData.getClass();//获取到属性的值的Class对象
Row row = hiddenSheet.createRow(poiExcel.index());
int from = 0;//标识在行数据中级联的起始列和终止列
int end = 0;
Method entrySetM = mapClazz.getDeclaredMethod("entrySet");
Set<Map.Entry> entrySet = (Set<Map.Entry>) entrySetM.invoke(fieldData);
Cell cell;
for (Map.Entry entry : entrySet) {
List<String> listValue = (List<String>) entry.getValue();
for (String s : listValue) {
cell = row.createCell(end++);
cell.setCellValue(s);
}
//循环创建子序列名
createDataName(wb, entry.getKey().toString(), "hidden!$" + getPos(from + 1)
+ "$" + (poiExcel.index() + 1) + ":$" + getPos(end) + "$" + (poiExcel.index() + 1));
from = end;
}
}
for (int j = 1; j < 102; j++) {
DataValidation data_validation = createDataValidation("INDIRECT($" + getPos(poiExcel.ref() + 1) + "$" + (j + 1) + ")",
j, j, poiExcel.index(), poiExcel.index());
sheet.addValidationData(data_validation);
}
}
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
});
}
/**
* 获取cell数据,转换为String类型
*
* @param cell
* @return
*/
private static String getCellStringValue(Cell cell, Boolean isString, String format) {
//判断是否为null或空串
if (cell == null || cell.toString().trim().equals("")) {
return "";
}
String cellValue;
CellType cellType = cell.getCellTypeEnum();
// 以下是判断数据的类型
switch (cellType) {
case NUMERIC: // 数字
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
Date date = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm");
cellValue = formater.format(date);
} else {
if (isString) {
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
} else {
cellValue = String.valueOf(cell.getNumericCellValue());
cellValue = cellValue.endsWith(".0") ? cellValue.substring(0, cellValue.indexOf(".")) : cellValue;
if (!"".equals(format)) {
DecimalFormat df = new DecimalFormat(format);
cellValue = df.format(cell.getNumericCellValue());
}
}
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "";
break;
}
return cellValue;
}
/**
* 创建表格头
*
* @param wb
* @param sheetName
* @param clazz
* @param <T>
* @return
*/
private static <T> Sheet createHeader(Workbook wb, String sheetName, Class<T> clazz) {
Sheet sheet = wb.createSheet(sheetName);
Row row = sheet.createRow(0);
Field[] fields = clazz.getDeclaredFields();
Cell cell;
CellStyle style = getStringCenterStyle(wb);
for (Field field : fields) {
PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
if (poiExcel == null) {
continue;
}
cell = row.createCell(poiExcel.index());
cell.setCellStyle(style);//设置单元格格式为"文本"
cell.setCellType(CellType.STRING);
cell.setCellValue(poiExcel.value());
if (poiExcel.cellWidth() > 0) {
sheet.setColumnWidth(poiExcel.index(), sheet.getColumnWidth(poiExcel.index()) * poiExcel.cellWidth());
} else {
sheet.setColumnWidth(poiExcel.index(), sheet.getColumnWidth(poiExcel.index()) * 2);
}
}
return sheet;
}
/**
* 行数据填写
*
* @param sheet
* @param data
* @param clazz
* @param rowNum
* @param <T>
*/
private static <T> void writeDatas(Sheet sheet, T data, Class<T> clazz, int rowNum) {
Field[] fields = clazz.getDeclaredFields();
List<Field> fieldList = Arrays.asList(fields);
Row row = sheet.createRow(rowNum);
fieldList.stream().forEach(field -> {
field.setAccessible(true);
PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
if (poiExcel != null) {
try {
Object val = field.get(data);
if (val != null) {
row.createCell(poiExcel.index()).setCellValue(String.valueOf(val));
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
});
}
/**
* sheet数据读取
*
* @param sheet
* @param clazz
* @param <T>
* @return
*/
private static <T> List<T> ReadDatas(Sheet sheet, Class<T> clazz) {
List<T> datas = new ArrayList<>();
Field[] fields = clazz.getDeclaredFields();
List<Field> fieldList = Arrays.asList(fields);
Iterator<Row> iterator = sheet.iterator();
int mergerdRegions = sheet.getNumMergedRegions();
boolean hasMergerdRegions = mergerdRegions > 0;
List<int[]> regions = new ArrayList<>();
Map<String, Cell> mergerdValues = new HashMap<>();
if (hasMergerdRegions) {
for (int i = 0; i < mergerdRegions; i++) {
CellRangeAddress cellAddresses = sheet.getMergedRegion(i);
int fr = cellAddresses.getFirstRow();
int lr = cellAddresses.getLastRow();
int fc = cellAddresses.getFirstColumn();
int lc = cellAddresses.getLastColumn();
regions.add(new int[]{fr, lr, fc, lc});
Row row = sheet.getRow(fr);
mergerdValues.put("" + fr + lr + fc + lc, row.getCell(fc));
}
}
while (iterator.hasNext()) {
try {
Row row = iterator.next();
if (row == null || row.getRowNum() == 0) {
continue;
}
int rowIndex = row.getRowNum();
T data = clazz.newInstance();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell == null) {
continue;
}
int cellIndex = cell.getColumnIndex();
fieldList.stream().forEach(field -> {
PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
field.setAccessible(true);
if (poiExcel != null) {
if (poiExcel.index() == cellIndex) {//列和对象列一致
try {
if (hasMergerdRegions) {
List<int[]> finds = regions.stream().filter(region -> {
if (rowIndex >= region[0] && rowIndex <= region[1] && cellIndex >= region[2] && cellIndex <= region[3]) {
return true;
}
return false;
}).collect(Collectors.toList());
if (CollectionUtils.isEmpty(finds)) {
field.set(data, typeParser(field.getType(), getCellStringValue(cell, poiExcel.isString(), poiExcel.format())));
} else {
int[] find = finds.get(0);
field.set(data, typeParser(field.getType(), getCellStringValue(mergerdValues.get("" + find[0] + find[1] + find[2] + find[3]), poiExcel.isString(), poiExcel.format())));
}
} else {
field.set(data, typeParser(field.getType(), getCellStringValue(cell, poiExcel.isString(), poiExcel.format())));
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
});
}
if (!isObjEmpty(data)) {
datas.add(data);
}
} catch (InstantiationException e) {
e.printStackTrace();
return null;
} catch (IllegalAccessException e) {
e.printStackTrace();
return null;
}
}
return datas;
}
/**
* String值转换成对应类型
*
* @param type
* @param value
* @param <T>
* @return
*/
private static <T> Object typeParser(Class<T> type, String value) {
if (type == null) {
return null;
}
if (value == null || value.length() == 0) {
if (type.equals(int.class) || type.equals(double.class) || type.equals(float.class)
|| type.equals(byte.class) || type.equals(long.class) || type.equals(short.class)) {
return 0;
}
if (type.equals(boolean.class)) {
return false;
}
if (type.equals(char.class)) {
return '\u0000';
}
return null;
}
if (type.equals(char.class) || type.equals(String.class) || type.equals(Character.class)) {
return value;
} else if (type.equals(int.class) || type.equals(Integer.class)) {
if (value.contains(".")) {
value = value.substring(0, value.indexOf("."));
}
return Integer.parseInt(value);
} else if (type.equals(double.class) || type.equals(Double.class)) {
return Double.parseDouble(value);
} else if (type.equals(float.class) || type.equals(Float.class)) {
return Float.parseFloat(value);
} else if (type.equals(boolean.class) || type.equals(Boolean.class)) {
return Boolean.parseBoolean(value);
} else if (type.equals(byte.class) || type.equals(Byte.class)) {
return Byte.parseByte(value);
} else if (type.equals(long.class) || type.equals(Long.class)) {
return Long.parseLong(value);
} else if (type.equals(short.class) || type.equals(Short.class)) {
return Short.parseShort(value);
} else {
return null;
}
}
/**
* 样式
*
* @param wb
* @return
*/
private static CellStyle getStringCenterStyle(Workbook wb) {
CellStyle style = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat("@"));
style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
return style;
}
/**
* 读取excel数据
*
* @param is
* @param clazz
* @param <T>
* @return
*/
public static <T> List<T> readExcelData(InputStream is, Class<T> clazz) {
List<T> dataList = null;
try {
dataList = new ArrayList<>();
Workbook workbook = WorkbookFactory.create(is);
Iterator<Sheet> sheets = workbook.sheetIterator();
while (sheets.hasNext()) {
Sheet sheet = sheets.next();
if (sheet == null) {
continue;
}
List<T> sheetData = ReadDatas(sheet, clazz);
if (sheetData != null) {
dataList.addAll(sheetData);
}
}
} catch (IOException e) {
e.printStackTrace();
}
return dataList;
}
/**
* 读取excel数据,指定sheet页
*
* @param is
* @param clazz
* @param <T>
* @return
*/
public static <T> List<T> readExcelData(InputStream is, Class<T> clazz, int sheetIndex) {
List<T> dataList = null;
try {
dataList = new ArrayList<>();
Workbook workbook = WorkbookFactory.create(is);
Sheet sheet = workbook.getSheetAt(sheetIndex);
if (sheet == null) {
return null;
}
List<T> sheetData = ReadDatas(sheet, clazz);
if (sheetData != null) {
dataList.addAll(sheetData);
}
} catch (IOException e) {
e.printStackTrace();
}
return dataList;
}
/**
* 导出表格模板
*
* @param sheetName
* @param <T>
* @return
*/
public static <T> Workbook createModel(String sheetName, T data) {
Workbook wb = new HSSFWorkbook();
Class clazz = data.getClass();
Field[] fields = clazz.getDeclaredFields();
List<Field> fieldList = Arrays.asList(fields);
Sheet sheet = createHeader(wb, sheetName, clazz);
Sheet hiddenSheet = wb.createSheet("hidden");
wb.setSheetHidden(1, true);
fieldList.stream().forEach(field -> {
field.setAccessible(true);
PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
if (poiExcel != null) {
if (List.class.isAssignableFrom(field.getType())) {
String[] dropDownDatas;
Type type = field.getGenericType();
if (type instanceof ParameterizedType) {
try {
Object fieldData = field.get(data);
if (fieldData != null) {
Class StringClazz = fieldData.getClass();//获取到属性的值的Class对象
Method m = StringClazz.getDeclaredMethod("size");
int size = (Integer) m.invoke(fieldData);//调用list的size方法,得到list的长度
dropDownDatas = new String[size];
for (int i = 0; i < size; i++) {//遍历list,调用get方法,获取list中的对象实例
Method getM = StringClazz.getDeclaredMethod("get", int.class);
getM.setAccessible(true);
dropDownDatas[i] = getM.invoke(fieldData, i).toString();
}
Row row = hiddenSheet.createRow(poiExcel.index());
for (int i = 0; i < dropDownDatas.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(dropDownDatas[i]);
}
createDataName(wb, "list" + poiExcel.index(), "hidden!$A$" + (poiExcel.index() + 1) + ":$" + getPos(dropDownDatas.length)
+ "$" + (poiExcel.index() + 1));
DataValidation data_validation = createDataValidation("list" + poiExcel.index(), 1, 65533,
poiExcel.index(), poiExcel.index());
sheet.addValidationData(data_validation);
}
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
if (Map.class.isAssignableFrom(field.getType())) {
Type type = field.getGenericType();
if (type instanceof ParameterizedType) {
try {
Object fieldData = field.get(data);
if (fieldData != null) {
Class mapClazz = fieldData.getClass();//获取到属性的值的Class对象
Row row = hiddenSheet.createRow(poiExcel.index());
int from = 0;//标识在行数据中级联的起始列和终止列
int end = 0;
Method entrySetM = mapClazz.getDeclaredMethod("entrySet");
Set<Map.Entry> entrySet = (Set<Map.Entry>) entrySetM.invoke(fieldData);
Cell cell;
for (Map.Entry entry : entrySet) {
List<String> listValue = (List<String>) entry.getValue();
for (String s : listValue) {
cell = row.createCell(end++);
cell.setCellValue(s);
}
//循环创建子序列名
createDataName(wb, entry.getKey().toString(), "hidden!$" + getPos(from + 1)
+ "$" + (poiExcel.index() + 1) + ":$" + getPos(end) + "$" + (poiExcel.index() + 1));
from = end;
}
for (int j = 1; j < 102; j++) {
DataValidation data_validation = createDataValidation("INDIRECT($" + getPos(poiExcel.ref() + 1) + "$" + (j + 1) + ")",
j, j, poiExcel.index(), poiExcel.index());
sheet.addValidationData(data_validation);
}
}
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
});
return wb;
}
/**
* 创建数据约束
*
* @param name
* @param fromRow
* @param endRow
* @param fromColumn
* @param endColumn
* @return
*/
private static DataValidation createDataValidation(String name, int fromRow, int endRow, int fromColumn, int endColumn) {
//起始行,终止行,起始列,终止列
CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, fromColumn, endColumn);
DVConstraint constraint = DVConstraint.createFormulaListConstraint(name);
return new HSSFDataValidation(regions, constraint);
}
/**
* 创建命名数据
*/
private static void createDataName(Workbook wb, String name, String region) {
Name namedCell = wb.createName();
namedCell.setNameName(name);
namedCell.setRefersToFormula(region);
}
/**
* 根据输入的数字返回excel列数字符
*
* @param size
* @return
*/
private static String getPos(int size) {
if (size <= 0) {
return "A";
}
String[] args = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S",
"T", "U", "V", "W", "X", "Y", "Z"};
boolean tag = true;//用来结束循环
StringBuffer reversepos = new StringBuffer();
while (tag) {
int pos = size % 26;
if (pos == 0) {
pos = 25;//整除说明是26结尾,故为Z
size--;//之所以size会减1是因为列并没有数字中所谓的10这回事,Z后面就是AA
} else {
pos -= 1;
}
int result = size / 26;
if (result == 0) {
reversepos.append(args[pos]);
tag = false;
} else {
reversepos.append(args[pos]);
size /= 26;
}
}
return reversepos.reverse().toString();//因为是从类似个位开始算起来,所以要倒转
}
/**
* 判断对象值是否全为null
*
* @param object
* @param <T>
*/
private static <T> boolean isObjEmpty(T object) {
Class clazz = object.getClass();
Field[] fields = clazz.getDeclaredFields();
List<Field> fieldList = Arrays.asList(fields);
boolean tag = true;
for (Field field : fieldList) {
field.setAccessible(true);
try {
Object fieldData = field.get(object);
if (fieldData != null) {
tag = false;
break;
}
} catch (IllegalAccessException e) {
e.printStackTrace();
return false;
}
}
return tag;
}
}
测试模板导出和对象导入导出的实体类:
public class TestList {
@PoiExcel(index = 0, value = "t1")
private String t1;
@PoiExcel(index = 1, value = "下拉框")
private List<String> list;
@PoiExcel(index = 2, value = "t2")
private String t2;
@PoiExcel(index = 3, value = "下拉框2")
private List<String> list2;
@PoiExcel(index = 4, value = "级联下拉",ref = 3)
private Map<String,List<String>> mapDatas;
public String getT1() {
return t1;
}
public void setT1(String t1) {
this.t1 = t1;
}
public List<String> getList() {
return list;
}
public void setList(List<String> list) {
this.list = list;
}
public String getT2() {
return t2;
}
public void setT2(String t2) {
this.t2 = t2;
}
public List<String> getList2() {
return list2;
}
public void setList2(List<String> list2) {
this.list2 = list2;
}
public Map<String, List<String>> getMapDatas() {
return mapDatas;
}
public void setMapDatas(Map<String, List<String>> mapDatas) {
this.mapDatas = mapDatas;
}
public static void main(String[] args) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
//导出带下拉级联的数据
List<TestEntity> a = new ArrayList<>();
TestEntity a1 = new TestEntity();
a1.setT1("1");
a1.setT2("2");
a1.setT4(1);
a1.setT5(false);
TestEntity a2 = new TestEntity();
a2.setT1("3");
a2.setT3("t3");
a2.setT4(2);
a2.setT5(true);
TestEntity a3 = new TestEntity();
a3.setT1("5");
a3.setT2("6");
a3.setT3("t3");
a3.setT5(true);
a.add(a1);
a.add(a2);
a.add(a3);
TestList t = new TestList();
List<String> b = new ArrayList<>();
b.add("1");
b.add("2");
b.add("3");
t.setList(b);
List<String> aa = new ArrayList<>();
aa.add("测试1");
aa.add("测试2");
t.setList2(aa);
Map<String, List<String>> map = new HashMap<>();
String[] aa1 = {"1", "2", "3", "4"};
String[] aa2 = {"1", "2", "3", "4", "5", "6", "7"};
map.put("测试1", Arrays.asList(aa1));
map.put("测试2", Arrays.asList(aa2));
t.setMapDatas(map);
HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(null, a, TestEntity.class, 1, t);
FileOutputStream fos = null;
try {
fos = new FileOutputStream(new File("D://test.xls"));
wb.write(fos);
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
@Component
public class TestEntity {
@PoiExcel(value = "测试1",index = 1)
private String t1;
@PoiExcel(value = "测试2",index = 0)
private String t2;
private String t3;
@PoiExcel(value = "测试4",index = 2)
private Integer t4;
@PoiExcel(value = "测试5",index = 3)
private boolean t5;
public String getT1() {
return t1;
}
public void setT1(String t1) {
this.t1 = t1;
}
public String getT2() {
return t2;
}
public void setT2(String t2) {
this.t2 = t2;
}
public String getT3() {
return t3;
}
public void setT3(String t3) {
this.t3 = t3;
}
public int getT4() {
return t4;
}
public void setT4(int t4) {
this.t4 = t4;
}
public boolean isT5() {
return t5;
}
public void setT5(boolean t5) {
this.t5 = t5;
}
@Override
public String toString() {
return "TestEntity{" +
"t1='" + t1 + '\'' +
", t2='" + t2 + '\'' +
", t3='" + t3 + '\'' +
", t4=" + t4 +
", t5=" + t5 +
'}';
}
}