导读
最近，公司在做导入导出的项目，首先想到的是 poi 的导入和导出。如果每次导入和导出都要重写的话，那么，实在是浪费时间和精力。于是，封装了原生的 poi 的导入和导出。在封装的时候，就会出现一系列的问题。
在进行导入和导出的时候，我们必须要熟悉 Excel，尤其是它所支持的类型，如图所示：
因为我用的是 java，这要和 java 语言相匹配。java 有八大基本类型（包装类型）、字符串类型、日期类型、时间戳类型等。Excel 类型要和这些类型相匹配，否则，就会出现导入和导出的问题。
导出
导出的思想
* 采用 hibernate 或 mybatis 框架，从数据库中取出数据，假设名字为 originalList
* 使用 appach 下面的 beanutils 框架的 PropertyUtils.getProperty((Object bean, String name)) 方法过滤数据。它是通过对象的属性名称拿到对象的属性值。这里面用到的反射。
* 将过滤后的数据放到 JsonObject 集合中，key 是当前对象的属性名，value 是属性对应的属性值。假设名为 dataList。
* 使用 LinkedHashMap 封装 Excel 表的表头，也就是上图中的第一行数据。key 值是上述对象的数值名，value 值表头的中文名。假设名为 headerMap。*** 为什么使用 LinkedHashMap，而不是 HashMap？这个在下文说 ***
* 实例化 poi 导出的各个对象，便于操作。
* 遍历 headerMap，获取当前的 key 值。
* 遍历 dataList，如果 list 中的当前对象是 JsonObject 对象，直接获取与 key 值相同的属性值。如果不是 JsonObject 对象，可以通过 PropertyUtils.getProperty((Object bean, String name)) 获取值。
* 拿到值后，创建当前单元格，把数据填充进去。
* 响应客户端的导出请求
解决“为什么使用 LinkedHashMap，而不是 HashMap？”这个问题
1、因为 HashMap 的 key 值是对象的 hashCode 值。这样存储方式是散列存储，就像浴缸中的鱼一样，它的位置是不确定的，因而，输出结果具有很多不确定性。也就是说，输出结果的顺序和我们存储的顺序不一致，如图所示：
2、但是，LinkedHashMap 采用的是链表，链表节点的存储的是下一个引用对象的首地址。就像是一根绳子上节点，系在哪儿就在那儿，位置不会改变的，如图所示：
上述思想的方法
/**
* Created By zby on 18:22 2019/3/9
*
* @param response 响应客户端的导出请求
* @param headerMap 创建表头
* @param dataList 数据集
* @param excelName excel 表名
*/
public static void exportSimpleExcel(HttpServletResponse response, LinkedHashMap<String, String> headerMap, List<JSONObject> dataList, String excelName) {
// 创建个 workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建一个 sheet
XSSFSheet sheet = workbook.createSheet();
Pattern chinese_pattern = Pattern.compile(“[\\u4e00-\\u9fa5]”);
if (sheet != null) {
try {
// 写数据
Integer i = 0;
for (Map.Entry<String, String> entry : headerMap.entrySet()) {
String key = entry.getKey();
String value = entry.getValue();
Row headRow = sheet.getRow(0);
if (isNull(headRow)) {
headRow = sheet.createRow(0);
}
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
cellStyle.setFillBackgroundColor(HSSFColor.GREY_40_PERCENT.index);
// 创建标题行, 如果第一遍已经创建了，就直接根据下标获取行，
// 如果没有创建标题行，就根据当前下标创建当前行，以下相同。
Cell headCell = headRow.createCell(i);
headCell.setCellValue(value);
headCell.setCellStyle(cellStyle);
// 设置标题下的数据，每创建标题的一个单元格，就创建该单元格下的所有列
// 行数就是一行标题单元格 + 数据单元格的个数，即 merginRow+j
for (int j = 0; j < dataList.size(); j++) {
Object obj = dataList.get(j);
// 需要判断 json 是否是 jsonObject 的实例化对象，别人在调用这个方法时，
// 我们不清楚 jsons 集合中是否存在其他类的对象，因为需要作个验证
Object val = null;
if (obj instanceof JSONObject) {
JSONObject json = (JSONObject) obj;
val = json.get(key);
}
// else {这里如果不是 JsonObject 对象，就用这种方式获取
// try {
// // 这是 appach 下的方法，其通过属性名称, 反射得到当前对象属性的数值
// //json 当中的 key 就相当于其属性，其值就是 value 值
// val = getProperty(obj, key);
// } catch (Exception e) {
// if (obj != null) {
// LogUtil.warn(logger, “ 类: ” + obj.getClass() + “, 属性: ” + key);
// }
// val = null;
// }
// }
Row row = sheet.getRow(1 + j);
if (isNull(row)) {
row = sheet.createRow(1 + j);
}
Cell cell = row.createCell(i);
cell.setCellStyle(cellStyle);
if (val instanceof Double) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(((Double) val).doubleValue());
} else if (val instanceof Integer) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(((Integer) val).intValue());
} else if (val instanceof Date) {
cell.setCellValue(ISO_DATETIME_FORMAT.format(val));
} else if (val instanceof Calendar) {
cell.setCellValue((Calendar) val);
} else if (val instanceof Boolean) {
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
cell.setCellValue(((Boolean) val).booleanValue());
} else if (val instanceof String || val instanceof RichTextString) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
String cellValue = (String) val;
int width = cellValue.length();
Matcher matcher = chinese_pattern.matcher(cellValue);
while (matcher.find()) {
width++;
}
width = (width > 6) ? ((width < 80) ? width : 80) : 6;
sheet.setColumnWidth(i, (256 * width + 184) + 500);
cell.setCellValue(cellValue);
} else {
cell.setCellValue(“”);
}
}
i++;
}
String realPath = (getSispPath() + “uploadExcelRecord”);
// 新的文件名
String newFileName = excelName + DateUtil.ISO_DATETIME_FORMAT_NONE.format(new Date()) + “.xlsx”;
// 判断路径是否存在
File dir = new File(realPath);
if (!dir.exists()) {
dir.mkdirs();
}
// 写入到新的 excel
File newFile = new File(realPath, newFileName);
FileOutputStream fos = new FileOutputStream(newFile);
workbook.write(fos);
fos.flush();
fos.close();
downloadExcel(response, newFile, excelName);
deleteFile(newFile);
} catch (Exception e) {
logger.error(“——————————————————————————数据写入表格失败————————————————————————————”);
}
}
}
注意事项
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(((Double) val).doubleValue());
针对这两句，我们查看 poi 的 cell.setCellValue() 方法的底层是如何实现的。
void setCellValue(double var1);
void setCellValue(Date var1);
void setCellValue(Calendar var1);
void setCellValue(RichTextString var1);
void setCellValue(String var1);
void setCellValue(boolean var1);
在 java 的八大类型当中，poi 支持浮点型的 double 类型，整型的 integer 类型。为什么是这两种类型？这两种格式是 java 的默认类型。double 可以转整型的。所有，我们在进行数据导出时，一定要主营待导出对象的数据类型，避免数据无法导出。
导入
导入的思想
* 在导入时，接收到客户端发出的导入请求。
* 设置导入数据的开始行和结束行，开始行默认是 0，结束行调用者给出的参数
* 设计导入的开始列和结束列，开始列一般是 0，结束列一般是调用给出的列
* 获取导入的文件，判断 Excel 的版本。
导入的方法
/**
* Created By zby on 19:24 2019/3/9
*
* @param request 客户端发出的导入请求
* @param sheetDataStartRow 导入的开始行
* @param sheetDataEndCol 导入的结束列
*/
public static LinkedHashMap<String, List<JSONObject>> importMultiSheetExcel(HttpServletRequest request, Integer sheetDataStartRow, String sheetDataEndCol) {
// 创建导入和行和列
LinkedHashMap<Integer, String> sheetDataEndColMap = new LinkedHashMap<>();
LinkedHashMap<Integer, Integer> sheetFirstDataRowMap = new LinkedHashMap();
sheetDataEndColMap.put(0, sheetDataEndCol);
sheetFirstDataRowMap.put(0, sheetDataStartRow);
LinkedHashMap<String, List<JSONObject>> resMap = new LinkedHashMap<>();
try {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
ifNullThrow(multipartRequest, ResultCodeEnum.ILLEGAL_PARAM);
MultipartFile file = multipartRequest.getFile(“file”);
Workbook work = getWorkbook(file.getInputStream(), file.getOriginalFilename());
ifNullThrow(work, ResultCodeEnum.ILLEGAL_PARAM);
Sheet sheet = null;
Row row = null;
Cell cell = null;
// 遍历 Excel 中所有的 sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (null == sheet) {
continue;
}
String sheetDataEndCo = sheetDataEndColMap.get(i);
Integer sheetFirstDataRow = sheetFirstDataRowMap.get(i);
sheetDataEndCo = isNotBlank(sheetDataEndCo) ? sheetDataEndCo.toUpperCase() : null;
sheetFirstDataRow = isNotNull(sheetFirstDataRow) ? sheetFirstDataRow : 1;
List<JSONObject> list = new ArrayList<>();
// 遍历当前 sheet 中的所有行
for (int j = 0; j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row == null) {
continue;
}
if (j >= sheetFirstDataRow.intValue()) {
JSONObject json = new JSONObject();
// 导入限制最大列索引数为 200，正常根据 sheetDataEndCol 确定，最大列索引数限制只用来防止传入错误
for (int k = 0; k < 200; k++) {
String colName = CellReference.convertNumToColString(k);
cell = row.getCell(k);
if (isNotNull(cell)) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
cell.setCellType(Cell.CELL_TYPE_STRING);
json.put(colName, cell.getStringCellValue().trim());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
json.put(colName, cell.getDateCellValue());
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
json.put(colName, cell.getStringCellValue().trim());
}
}
} else {
json.put(colName, null);
}
if (colName.equals(sheetDataEndCo)) {
break;
}
}
list.add(json);
}
}
resMap.put(“sheet” + i, list);
}
return resMap;
} catch (Exception e) {
throw new GeneralBizException(“Excel 导入异常：” + e.getMessage());
}
}
注意事项
因为 Excel 格式的时间和 java 的时间不一致，我们在导入时需要注意 Excel 的格式。首先判断当前单元格的数据是不是数字型的，如果是数字型的，在判断是不是日期类型的，如果是日期类型，再转为日期类型。否则，全部是字符型的数据。
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
cell.setCellType(Cell.CELL_TYPE_STRING);
json.put(colName, cell.getStringCellValue().trim());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if(HSSFDateUtil.isCellDateFormatted(cell)){
json.put(colName, cell.getDateCellValue());
}else {
cell.setCellType(Cell.CELL_TYPE_STRING);
json.put(colName, cell.getStringCellValue().trim());
}
}
这里为什么不做数值型的判断，因为，字符串可以转为各中数值型的数据。
结束语
任何框架的搭建，都需要扎实的基础，只有基础足够强大，再加上灵活的设计思想，就能够解决很多事情。