/**
* 导入核算项目编号导入模板
*
* @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")
}
浙公网安备 33010602011771号