转载

poi几多愁，恰似源码的温柔

导读

最近，公司在做导入导出的项目，首先想到的是poi的导入和导出。如果每次导入和导出都要重写的话，那么，实在是浪费时间和精力。于是，封装了原生的poi的导入和导出。在封装的时候，就会出现一系列的问题。

在进行导入和导出的时候，我们必须要熟悉Excel，尤其是它所支持的类型，如图所示：

poi几多愁，恰似源码的温柔

因为我用的是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值。这样存储方式是散列存储，就像浴缸中的鱼一样，它的位置是不确定的，因而，输出结果具有很多不确定性。也就是说，输出结果的顺序和我们存储的顺序不一致，如图所示：

poi几多愁，恰似源码的温柔

2、但是，LinkedHashMap采用的是链表，链表节点的存储的是下一个引用对象的首地址。就像是一根绳子上节点，系在哪儿就在那儿，位置不会改变的，如图所示：

poi几多愁，恰似源码的温柔

上述思想的方法

/**
     * 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());
    }
}

这里为什么不做数值型的判断，因为，字符串可以转为各中数值型的数据。