package org.dcexam.cms.core.plugin;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dcexam.cms.module.entity.Question;
/**
* @author 王海明
* @createData 2017年8月15日 下午2:24:35
* @说明 : poi 导入 导出
*/
public class PoiPlugin {
public static void main(String[] args) throws Exception {
// 每行的 逻辑。。。
// String path = "E:/test1.xlsx";
String path = "E:/question_1.xlsx";
InputStream input = new FileInputStream(new File(path));
Map<String, Function<Object, Object>> formaterMap = new HashMap<>();
formaterMap.put("difficulty", value -> {
return Integer.valueOf((String) value);
});
formaterMap.put("qtid", value -> {
String typeStr = (String) value;
int type = 0;
if (typeStr.startsWith("单选"))
type = 1;
else if (typeStr.startsWith("多选"))
type = 2;
else if (typeStr.startsWith("判断"))
type = 3;
else if (typeStr.startsWith("排序"))
type = 4;
else if (typeStr.startsWith("填空"))
type = 5;
else if (typeStr.startsWith("简答"))
type = 6;
return type;
});
@SuppressWarnings("serial")
List<Object> listFromExcel = getListFromExcel(path, input, new HashMap<String, String>() {
{
put("题目类型", "qtid");
}
}, Question.class, formaterMap);
System.out.println(listFromExcel);
}
/**
* @param filepath 文件名称(文件的全路径也可以)
* @param in inputStream 流
* @param map 第一行 和 class 类 字段 对应规则 为null 则 默认对应
* @param clazz 对应的实体类的类 文件
* @param formater 格式化。。参数类型是 Map<Object,Function<Object,Object>>
* @return
* @说明 暂时 仅支持 xls,xlsx 格式 的导入
*/
public static List<Object> getListFromExcel(String filepath, InputStream in, Map<String, String> map,
@SuppressWarnings("rawtypes") Class clazz, Map<String, Function<Object, Object>> formater) {
List<Object> list = null;
// 创建Excel工作薄
Workbook work = getWorkBook(filepath, in);
if (null == work) {
throw new RuntimeException("创建Excel工作薄为空！");
}
Sheet sheet = null;
Row row = null;
// Cell cell = null;
list = new ArrayList<>();
// 遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
Row firstRow = sheet.getRow(0);
// 遍历当前sheet中的所有行,rowNum 从 0 开始。。。以 长度 - 1 结尾。。
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
// 如果 row 为空。。。或者它是 第一行。。。
if (row == null || row.getRowNum() == 0) {
continue;
}
/*
* System.out.println(sheet.getFirstRowNum());
* System.out.println(sheet.getLastRowNum());
*/
try {
Object obj = null;
if (clazz != null) {
obj = clazz.newInstance();
} else {
throw new RuntimeException("clazz 不能为空");
}
// 遍历所有的列
// 从0 开始 。。。以长度 结尾。。。。也就是不用 <=....
for (int index = row.getFirstCellNum(); index < row.getLastCellNum(); index++) {
// 第一列 应该全部是字符串。。。
String field = (String) getCellValue(firstRow.getCell(index));
Object cellValue = getCellValue(row.getCell(index));
buildObj(field, cellValue, map, clazz, obj, formater);
}
list.add(obj);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
try {
if (in != null)
in.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
private static void buildObj(String fieldstr, Object value, Map<String, String> map,
@SuppressWarnings("rawtypes") Class clazz, Object obj, Map<String, Function<Object, Object>> formatter)
throws Exception {
Field field = null;
if (map != null) {
fieldstr = map.get(fieldstr) == null ? fieldstr : map.get(fieldstr);
}
try {
field = clazz.getDeclaredField(fieldstr);
} catch (NoSuchFieldException e) {
//出现此异常，直接return
return;
} catch (Exception e) {
//出现其它类型异常。。抛出
e.printStackTrace();
throw e;
}
field.setAccessible(true);
Function<Object, Object> func = null;
if (formatter != null) {
func = formatter.get(fieldstr);
}
if (func != null) {
Object apply = func.apply(value);
field.set(obj, apply);
} else {
field.set(obj, value);
}
}
/**
* 此方法 应为 私有方法。。
*
* @param path
* @param input
* @return
*/
private static Workbook getWorkBook(String path, InputStream input) {
Workbook workbook = null;
// File file = new File(path);
if (path.endsWith(".xls"))
try {
workbook = new HSSFWorkbook(input);
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
else if (path.endsWith(".xlsx")) {
try {
workbook = new XSSFWorkbook(input);
} catch (Exception e) {
// TODO: handle exception
}
} else {
throw new RuntimeException("不支持的文档格式");
}
return workbook;
}
@SuppressWarnings("deprecation") // 将要过时的方法
private static Object getCellValue(Cell cell) {
Object value = null;
DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
value = sdf.format(cell.getDateCellValue());
} else {
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
}
