导读
最近，公司在做导入导出的项目，首先想到的是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值，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());
}
}这里为什么不做数值型的判断，因为，字符串可以转为各中数值型的数据。
结束语
任何框架的搭建，都需要扎实的基础，只有基础足够强大，再加上灵活的设计思想，就能够解决很多事情。
java
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。