Upload Or Download Excel(.xls/.xlsx) File In Kotlin II

上篇文章说明了如何导出模板,接下来咱们就聊聊如何使用模板写入数据,该模板亦可支持用户自定义属性,所谓自定义属性即为用户可为一行(一条数据),添加额外的属性值(非模板定义属性field),增强数据的完整性及补充说明性.自定义属性应在类class中声明,并以key/value形式体现或被标记,避免使用反射造成性能上的体验不良好.

/**
 * 读取excel对象
 * @param dataRow 数据起始行(based on 0)
 * @param addiAttrColumn 附加属性起始列 (based on 0)
 * @param ins 数据流
 * @return 对象集合
 */
inline fun <T : BaseAdditionalAttrs> read(clazz: Class<T>, dataRow: Int, addiAttrColumn: Int, ins: InputStream, fileName: String) = ins.use {
	XSSFWorkbook(it).use { workbook ->
		val sheet = workbook.getSheet(fileName)
		val rowValues = mutableListOf<List<String>>()
		val lastRowNum = sheet.lastRowNum
		for (i in (0..lastRowNum)) {  //遍历所有行
			val row = sheet.getRow(i)
			val columnValues = mutableListOf<String>()
			for (j in sheet.getRow(1).toList().indices) { // 遍历第1行所有列(基于0)
				if (row.isNull()) continue
				val columnValue = if (row.getCell(j) == null) "" else row.getCell(j).stringCellValue
				columnValues.add(columnValue)
			}
			if (columnValues.isNotEmpty()) { //去除空行数据
				rowValues.add(columnValues)
			}
		}
		val mapper = ModelMapper()
		val fields = rowValues[0] //域名行
		val additionalAttrNames = rowValues[1].drop(addiAttrColumn) //属性名行
		rowValues.drop(dataRow).map {
			mapper.map(fields.zip(it).toMap(), clazz).apply {
				if (additionalAttrNames.isNotEmpty()) { //附加属性KV
					this.bizContent = additionalAttrNames.zip(it.drop(addiAttrColumn)).toMap().toMutableMap()
				}
			}
		}
	}
}

abstract class BaseAdditionalAttrs(var bizContent: MutableMap<String, String> = mutableMapOf()) : Serializable

所要导入的数据class必须继承BaseAdditionalAttrs,该类的目的是:存放用户所定义的附加属性及值.使用kotlin这里有个坑,kotlin

的非空安全.foreach强制集合元素不能为null,而单元格没有值的话,源码定义返回null:

@Override
    public XSSFCell getCell(int cellnum, MissingCellPolicy policy) {
        if(cellnum < 0) {
            throw new IllegalArgumentException("Cell index must be >= 0");
        }

        // Performance optimization for bug 57840: explicit boxing is slightly faster than auto-unboxing, though may use more memory
        final Integer colI = Integer.valueOf(cellnum); // NOSONAR
        XSSFCell cell = _cells.get(colI);
        switch (policy) {
            case RETURN_NULL_AND_BLANK:
                return cell;
            case RETURN_BLANK_AS_NULL:
                boolean isBlank = (cell != null && cell.getCellType() == CellType.BLANK);
                return (isBlank) ? null : cell;
            case CREATE_NULL_AS_BLANK:
                return (cell == null) ? createCell(cellnum, CellType.BLANK) : cell;
            default:
                throw new IllegalArgumentException("Illegal policy " + policy);
        }
    }

但是kotlin遍历的返回均为非空,将无任何值的单元格忽略,也就是说相当于少读了一个单元格,接下来的mapper映射便会出现数据侧移,赋值将会混乱. 因此要使用常规的for循环,读出下标并判断返回值为null时返回空串.

excel导入附加属性,所有的附加属性以KV形式放在父类属性bizContent中,key:属性名称,value:数据值