kotlin导入 代码

    /**
     * 导入核算项目编号导入模板
     *
     * @param controller
     * @return
     * @throws Exception
     */
    @Throws(Exception::class)
    fun import(iwebReq: IwebReq,fileUuid:String): Map<String,Any>? {
        // 返回数据
        var resultMap =  HashMap<String, Any>();
        // 记录数据是否写表
        var isInsert = false

        StringUtils.notBlank(fileUuid)
        // 解析文件获取数据
        val fileInfo = this.getFileInfoByUuid(fileUuid)
        AssertUtils.notNull(fileInfo,"未获取核算项目表单id${fileInfo}")
        val excelSheetName = "核算项目编号导入模板"
        // 设置文件解析范围
        val itemcompanySheet = ImportFilePageInfo(1, 0, 1)
        val pageInfos = HashMap<String, ImportFilePageInfo>()
        pageInfos[excelSheetName] = itemcompanySheet

        // 获取文件数据
        val path = fileInfo.getStorePath()

        val tempFile = File(path)
        //判断文件是否存在
        if (!tempFile.exists()) {
            //创建文件父目录
            tempFile.getParentFile().mkdirs()
            //从oss上读取文件
            val AliReader = AliOssReaderImpl()
            Files.createParentDirs(FileUtils.getFile(fileInfo.getStorePath()))
            AliReader.read(fileInfo, NioUtils.DEFAULT_CHARSETS, object : IInputStreamCallback {
                @Throws(Exception::class)
                override fun executeInputStream(inputStream: InputStream, saveFileInfo: FileInfo) {
                    // 写入到本地文件
                    NioUtils.write2file(fileInfo.getStorePath(), inputStream, NioUtils.DEFAULT_CHARSETS)
                }
            })
        }
        // 获取文件数据
        val excelMap = QhyfAbstractService().importFromExcel(fileInfo, pageInfos)
        if(excelMap.isEmpty()){
            throw RuntimeException()
        }

        // 获取项目公司数据
        val itemcompanyLsit = excelMap.get(excelSheetName) as List<Map<String, Any>>
        if (itemcompanyLsit.size == 0) {
            val exit: String? = null
            AssertUtils.notNull(exit!!, "不允许导入空表！")
        }
        //        AssertTools.hasItem(itemcompanyLsit);
        // 付款确认书编号
        var payId: String? = null
        // 核算项目编号
        var accountingItemNumber: String? = null

        var errorMsg = ""
        var msg = ""
        var errorFlag = false
        //创建表头
        val workbook = XSSFWorkbook()
        var savePath =QhyffsEvnironment.me().generatePath+File.separator
        val save = File(savePath)
        if (!save.exists() && !save.isDirectory()) {
            save.mkdirs()
        }
        val fileName = "核算项目编号导入模板.xls"
        savePath = savePath + File.separator + fileName
        val fout = FileOutputStream(savePath)
        // 创建检测结果sheet
        val sheet = workbook.createSheet(excelSheetName)
        // 设置冻结行列
        sheet.createFreezePane(0, 1, 0, 1)
        // 设置表头,第一行
        val headRow = sheet.createRow(0)
        val headArr = arrayOf("付款确认书编号", "核算项目编号", "错误提示")

        // 单元格
        var headCell: XSSFCell? = null
        val style = workbook.createCellStyle()
        // 表头样式设置
        style.borderBottom = XSSFCellStyle.BORDER_THIN
        style.borderLeft = XSSFCellStyle.BORDER_THIN
        style.borderTop = XSSFCellStyle.BORDER_THIN
        style.borderRight = XSSFCellStyle.BORDER_THIN
        // 设置背景色
        style.setFillForegroundColor(XSSFColor(Color(156, 195, 230)))
        style.fillPattern = XSSFCellStyle.SOLID_FOREGROUND
        // 列宽设置
        sheet.setColumnWidth(0, 20 * 256)
        sheet.setColumnWidth(1, 20 * 256)
        sheet.setColumnWidth(2, 30 * 256)

        // 写入表头
        for (i in headArr.indices) {
            sheet.autoSizeColumn(i)
            headCell = headRow.createCell(i)
            headCell!!.setCellValue(headArr[i])
            headCell.cellStyle = style
        }


        //设置单元格样式
        val cellStyle = workbook.createCellStyle()
        //边框
        cellStyle.borderBottom = XSSFCellStyle.BORDER_THIN
        cellStyle.borderLeft = XSSFCellStyle.BORDER_THIN
        cellStyle.borderTop = XSSFCellStyle.BORDER_THIN
        cellStyle.borderRight = XSSFCellStyle.BORDER_THIN
        //内容换行
        cellStyle.wrapText = true
        //垂直居中
        cellStyle.verticalAlignment = XSSFCellStyle.VERTICAL_CENTER

        // 设置单元格字体
        val font = workbook.createFont()
        // 字体样式
        font.fontName = "微软雅黑"
        // 字体大小
        font.fontHeightInPoints = 9.toShort()
        cellStyle.setFont(font)

        var cellPay: XSSFCell? = null
        var rowPay: XSSFRow? = null

        var rowNum = 1
        var num = 0
        for (itemcompanyData in itemcompanyLsit) {
            // 第一二列都为空时，跳过
            if (itemcompanyData["c1"] == null && itemcompanyData["c0"] == null) {
                //如果付款确认书uuid和核算项目编号都为空则不过任何处理
                num = num + 1
                if (num == 199) {
                    val exit: String? = null
                    AssertUtils.notNull(exit!!, "不允许导入空表！")
                }
                continue
            } else {
                // 核算项目编号和付款确认书编号同时不为空
                if(itemcompanyData["c1"] != null && itemcompanyData["c0"] != null ){
                    // 去掉空格，回车后判断是否为空
                    accountingItemNumber = this.replaceBlank(itemcompanyData["c1"].toString())
                    if (StringUtils.notBlank(accountingItemNumber) && StringUtils.notBlank(this.replaceBlank(itemcompanyData["c0"].toString()))) {
                        // 判断付款确认书编号 是否存在
                        payId = this.isExistPayId(this.replaceBlank(itemcompanyData["c0"].toString()))
                        if(StringUtils.notBlank(payId)){
                            // 获取付款确认书信息表ID
                            val sql = String.format("SELECT id FROM biz_pay_comfirm_info  WHERE pay_id=? AND sys_status = 1 ")
                            val record = Db.findFirst(sql, payId)
                            // 获取payID
                            var id= record?.getStr("id")
                            //如果payID，存在，直接把核算项目编号 设置进去
                            if(id != null){
                                var sql = """
                                            UPDATE
                                            biz_pay_comfirm_info
                                             SET
                                             accounting_item_number = ?
                                            WHERE
                                            id = ?
                                         """
                                var dbp = DbPro.use(iwebReq.dsName)
                                dbp.update(sql,accountingItemNumber,id)
                                // 记录写表成功
                                isInsert = true
                            }
                        }else {
                            // 付款确认书编号不存在
                            msg = "付款确认书编号不存在；"
                            errorMsg = errorMsg + msg
                        }
                    }else {
                        // 付款确认书编号不能为空字符串或者核算项目编号不能为空字符串
                        msg = "付款确认书编号不能为空字符串或者核算项目编号不能为空字符串"
                        errorMsg = errorMsg + msg
                    }
                }
                // 付款确认书编号为空
                else if(itemcompanyData["c0"] == null){
                    // 付款确认书编号为空
                    msg = "付款确认书编号不能为空；"
                    errorMsg = errorMsg + msg
                }
                // 核算项目编号为空
                else if(itemcompanyData["c1"]  == null){
                    // 核算项目编号为空
                    msg = "核算项目编号不能为空；"
                    errorMsg = errorMsg + msg
                }
            }
            if (StringUtils.notBlank(errorMsg)) {
                // 存在错误信息
                errorFlag = true
                rowPay = sheet.createRow(rowNum)
                rowPay!!.setHeightInPoints(25f)
                for (i in 0..2) {
                    cellPay = rowPay!!.createCell(i)
                    if (i == 2) {
                        cellPay!!.setCellValue(errorMsg)
                    } else {
                        if (itemcompanyData["c$i"] == null) {
                            cellPay!!.setCellValue("")
                        } else {
                            cellPay!!.setCellValue(itemcompanyData["c$i"].toString())
                        }
                    }
                    cellPay!!.setCellStyle(cellStyle)
                }
                rowNum++
                errorMsg = ""
            }
        }
        var errFileUuid = ""
        try {
            if (errorFlag) {
                workbook.write(fout)
                fout.close()
                // 保存文件信息到文件表
                errFileUuid = StringUtils.randomUuid()
                val file = File(savePath)
                val savefileInfo = FileInfo()
                savefileInfo.file = file
                savefileInfo.originalFileName = fileName
                val saveFile = SysFileUtils.fileInfo2SysFile(savefileInfo, "")
                saveFile.set("uuid", errFileUuid)
                val isTrue = saveFile.save()
                if (isTrue) {
                    // 保存成功，文件上传云端
                    val upFileInfo = this.getFileInfoByUuid(errFileUuid)
                    SysFileService().upload2cloud(upFileInfo,true,iwebReq)
                    // 记录写表成功
                    isInsert = true
                } else {
                    // 记录写表不成功
                    isInsert = false
                }
            }
        } catch (e: IOException) {
            e.printStackTrace()
        }
        // 保存数据是否写表
        resultMap.put("isFindList", isInsert)
        // 保存生成错误文件ID
        resultMap.put("fileId", errFileUuid)
       return resultMap
    }


    /**
     * 去掉字符串中的空格、回车、换行符、制表符
     *
     * @param str
     * 字符串
     * @return 去除后的字符串
     * @throws Exception
     */
    @Throws(Exception::class)
    private fun replaceBlank(str: String): String {
        var str = str
        // 替换字符串中的空格、回车、换行符、制表符
        str = str.replace("\\s*|\t|\r|\n".toRegex(), "")
        return str
    }


    /**
     * @return
     * @throws ActiveRecordException
     */
    @Throws(ActiveRecordException::class)
    private fun isExistPayId(payId: String): String? {
        val sql = String.format("SELECT pay_id FROM biz_pay_comfirm_info  WHERE pay_id=? AND sys_status = 1 ")
        val record = Db.findFirst(sql, payId)
        return record?.getStr("payId")
    }

 

转载于:https://www.cnblogs.com/xiaowoniulx/p/10244335.html