使用POI将Excel转为JSONArray, POI版本: 4.1.2
代码如下:
/**
* 读取某一个单元格值
* @param cell
* @return
* @throws Exception
*/
def getCellValueByCell(Cell cell) throws Exception {
//判断是否为null或空串
def sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
def sdv = new SimpleDateFormat("yyyy/MM/dd")
if (cell == null || cell.toString().trim()=="") {
return ""
}
def value
CellType cellType = cell.getCellType()
CellType.BLANK
if (CellType.BLANK == cellType) {
value = ""
} else if (CellType.BOOLEAN == cellType) {
value = cell.getBooleanCellValue()
} else if (CellType.ERROR == cellType) {
value = cell.getErrorCellValue()
} else if (CellType.FORMULA == cellType) {
value = cell.getNumericCellValue()
} else if (CellType.NUMERIC == cellType) {
if (DateUtil.isCellDateFormatted(cell)) {
Date date = (Date) sdv.parse(cell.getDateCellValue().toString()) //将读出的时间进行格式转化,
value=sdf.format(date)//最终输出格式为 yyyy-MM-dd HH:mm:ss
} else {
value = new DecimalFormat("0.########").format(cell.getNumericCellValue())
}
} else if (CellType.STRING == cellType) {
value = cell.getStringCellValue()
} else {
log.info("不能识别类型!")
throw new Exception("不能识别类型!")
}
return value
}
/**
* 读取Excel文件转成json
* @param path 文件路径
* @return 结果
* @throws IOException
*/
def readExcel(String path,List keys = null) {
def array = new JSONArray()
InputStream is
XSSFWorkbook workbook
try {
def file = new File(path)
if (!file.exists())
return array
is = new FileInputStream(file)
workbook = new XSSFWorkbook(is)
// 获得第一个工作表对象(编号从0开始,0,1,2,3,....)
XSSFSheet sheet = workbook.getSheetAt(0)
// 获取sheet的第一行数据
XSSFRow row = sheet.getRow(0)
// 列数
def colNum = row.getLastCellNum()
def addFlag = 0
// 初始化key,从第一行数据中获取
if (!keys){
addFlag = 1
keys = new ArrayList<String>()
for (int ki = 0; ki < colNum; ki++) {
keys << getCellValueByCell(row.getCell(ki)).toString()
}
}
def keysSize = keys.size()
// 如果传入的key不够, 补齐key
if (addFlag==0 && keysSize<colNum){
def addNum = colNum - keysSize
for (int ai = 0; ai < addNum; ai++) {
keys << "key_${ai}"
}
}
// 逐行获取数据, 封装为JSONObject
for(int i=addFlag;i<=sheet.getLastRowNum();i++){
def object=new JSONObject()
XSSFRow r = sheet.getRow(i)
for(int j=0;j<keys.size();j++){
XSSFCell cell=r.getCell(j)
object.put(keys[j], getCellValueByCell(cell))
}
array.add(object)
}
} catch (Exception e) {
log.error(e.localizedMessage,e)
} finally {
if (workbook)
workbook.close()
if (is)
is.close()
}
return array
}
Excel第一行为key, 从第二行开始为数据, 如:
stdNo | stdName | gender | deptId |
---|---|---|---|
112233001 | 程龙 | 1 | 1 |
112233002 | 奚凤 | 0 | 1 |
… | … | … | … |
这样, 就可以得到想要的结果