kotlin下面的导出 代码
/** * 增值税表:导出功能 * @param billingYear * 年份 * @param billingMonth * 月份 * @param coreEnterpriseId * 核心企业 */ fun export(iwebReq: IwebReq, coreEnterpriseId:String ? , billingYear:String?, billingMonth: String? ): String? { var dbp = DbPro.use(iwebReq.dsName) // 查询符合条件的数据SQL var select= """ SELECT id, CONVERT(billing_year, char) AS billing_year, billing_month, core_enterprise_id, assets_trusteeship_income, account_receivable, billing_amount, tax_amount, total_amount, service_fee_income, asset_custody_income, service_charge_income, input_tax, transfer_input_tax, real_payment """ var from=""" FROM biz_value_added_tax_info a WHERE sys_status = 1 """ var paras = mutableListOf<Any>() //核心企业 if(StringUtils.notBlank(coreEnterpriseId) ){ from += " AND a.core_enterprise_id = ? " var coreEnterpriseId="${coreEnterpriseId?.trim()}" paras.add(coreEnterpriseId) } //年份 if(StringUtils.notBlank(billingYear) ){ from += " AND a.billing_year = ? " var years="${billingYear?.trim()}" paras.add(years) } //月份 if(StringUtils.notBlank(billingMonth) ){ from += " AND a.billing_month = ? " var mouths = "${billingMonth?.trim()}" paras.add(mouths.trim()) } //获取付款单信息列表 var IncomeInfoList = dbp.find(select+from,*paras.toTypedArray<Any>()) //把上面的年月核心企业作为条件查询 for (record in IncomeInfoList){ //未做完 record.set("deductionMoney","") //抵扣金额 record.set("costTax","") //成本里面的税额 record.set("differenceTax","") //成本里面的差额 record.set("outputTax","") //销项税:税额-抵扣金额 record.set("VATPayable","") //应交增值税:销项税+资产托管收入销项+服务费收入销项-进项税+进项税转出 } if (IncomeInfoList!!.size == 0) { AssertUtils.isTrue(false, "没有对应的增值数据,导出失败!") } // 导出对象 var workbook = XSSFWorkbook() val date = SimpleDateFormat("yyyyMMddHHmmss").format(java.util.Date()) val fileName = "增值税表"+date+".xlsx" var savePath = QhyffsEvnironment.me().generatePath+File.separator+"增值税" val save = File(savePath) if (!save.exists() && !save.isDirectory()) { save.mkdirs() } // 输出流 savePath = savePath +File.separator+ fileName val fout = FileOutputStream(savePath) // 创建增值税sheet名称 val sheetPay = workbook.createSheet("增值税") // 设置冻结行列 sheetPay.createFreezePane(0, 2, 0, 2) // 设置增值税信息筛选 // val payChoose = CellRangeAddress.valueOf("A1:T1") // sheetPay.setAutoFilter(payChoose) // 定义增值税sheet表头 //第一行 val headPayArrOne = arrayOf("时间","", "收入","","","","","","", "成本","","", "差额", "销项税", "增值税") // 单元格 var headPayCell: XSSFCell? = null val style = workbook.createCellStyle() // 表头样式设置 style.setBorderBottom(XSSFCellStyle.BORDER_THIN) style.setBorderLeft(XSSFCellStyle.BORDER_THIN) style.setBorderTop(XSSFCellStyle.BORDER_THIN) style.setBorderRight(XSSFCellStyle.BORDER_THIN) // 设置背景色 style.setFillForegroundColor(XSSFColor(Color(216, 216, 216))) style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND) style.setAlignment(HSSFCellStyle.ALIGN_CENTER) // 水平居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)//垂直居中 // 设置表头字体 val headFont = workbook.createFont() // 字体样式 headFont.setFontName("宋体") // 字体大小 headFont.setFontHeightInPoints(9.toShort()) // 加粗 headFont.setBold(true) style.setFont(headFont) // 设置表头 //第一行 val headPayRowOne = sheetPay.createRow(0) //创建合并单元列 var regionOne = CellRangeAddress(0, 0, 0, 1) var regionTwo = CellRangeAddress(0, 0, 2, 8) var regionFour = CellRangeAddress(0, 0, 9, 11) var regionFive = CellRangeAddress(0, 0, 12, 12) var regionSix = CellRangeAddress(0, 0, 13, 13) var regionSeven = CellRangeAddress(0, 0, 14, 19) //在sheet里增加合并单元格 sheetPay.addMergedRegion(regionOne) sheetPay.addMergedRegion(regionTwo) sheetPay.addMergedRegion(regionFour) sheetPay.addMergedRegion(regionFive) sheetPay.addMergedRegion(regionSix) sheetPay.addMergedRegion(regionSeven) // 写入增值税表头 第一行的 for (x in headPayArrOne.indices ) { sheetPay.autoSizeColumn(x) headPayCell = headPayRowOne.createCell(x) headPayCell!!.setCellValue(headPayArrOne[x]) headPayCell!!.setCellStyle(style) } //第二行 val headPayRow = sheetPay.createRow(1) //第二行 val headPayArr = arrayOf("开票年","开票月","核心企业","保理金额","开票金额","税额","价税合计","资产托管收入","服务费收入","抵扣价税合计","抵扣金额","税额", "差额","销税额","资产托管收入销项","服务费收入销项","进项税","进项税转出","应交增值税","实缴") // 写入增值税表头 第二行的 for (i in headPayArr.indices) { sheetPay.autoSizeColumn(i) headPayCell = headPayRow.createCell(i) headPayCell!!.setCellValue(headPayArr[i]) headPayCell!!.setCellStyle(style) } // 增值税sheet列宽设置 sheetPay.setColumnWidth(0, 30 * 256)// 开票年 sheetPay.setColumnWidth(1, 30 * 256)// 开票月 sheetPay.setColumnWidth(2, 30 * 256)// 核心企业 sheetPay.setColumnWidth(3, 30 * 256)// 保理金额 sheetPay.setColumnWidth(4, 30 * 256)// 开票金额 sheetPay.setColumnWidth(5, 30 * 256)// 税额 sheetPay.setColumnWidth(6, 30 * 256)// 价税合计 sheetPay.setColumnWidth(7, 30 * 256)// 资产托管收入 sheetPay.setColumnWidth(8, 30 * 256)// 服务费收入 sheetPay.setColumnWidth(9, 30 * 256)// 抵扣价税合计 sheetPay.setColumnWidth(10, 30 * 256)// 抵扣金额 sheetPay.setColumnWidth(11, 30 * 256)// 税额 sheetPay.setColumnWidth(12, 30 * 256)// 差额 sheetPay.setColumnWidth(13, 30 * 256)// 销税额 sheetPay.setColumnWidth(14, 30 * 256)// 资产托管收入销项 sheetPay.setColumnWidth(15, 30 * 256)// 服务费收入销项 sheetPay.setColumnWidth(16, 30 * 256)// 进项税 sheetPay.setColumnWidth(17, 30 * 256)// 进项税转出 sheetPay.setColumnWidth(18, 30 * 256)// 应交增值税 sheetPay.setColumnWidth(19, 30 * 256)// 实缴 // 设置单元格样式 val cellStyle = workbook.createCellStyle() // 边框 cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN) cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN) cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN) cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN) // 内容换行 cellStyle.setWrapText(true) // 垂直居中 cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER) // 设置单元格字体 val font = workbook.createFont() // 字体样式 font.setFontName("微软雅黑") // 字体大小 font.setFontHeightInPoints(9.toShort()) cellStyle.setFont(font) // 加粗 var cellPay: XSSFCell? = null var rowPay: XSSFRow? = null var i = 1 // 查询结果不为空 for (recordPay in IncomeInfoList) { // 开票年 billing_year 0---4 val billingYear = recordPay.get<String>("billingYear") // 开票月 billing_month billingMonth val billingMonth = recordPay.getInt("billingMonth").toString() // 核心企业 core_enterprise_id coreEnterpriseId val coreEnterpriseId = recordPay.get<String>("coreEnterpriseId") // 对核心企业的处理 var coreEnterpriseName = "" if (coreEnterpriseId.equals("c1001")){ coreEnterpriseName = "万科" }else if (coreEnterpriseId.equals("c1002")){ coreEnterpriseName = "华润" }else if (coreEnterpriseId.equals("c1003")){ coreEnterpriseName = "碧桂园" }else if (coreEnterpriseId.equals("c1004")){ coreEnterpriseName = "富力" }else if (coreEnterpriseId.equals("c1005")){ coreEnterpriseName = "红星美凯龙" }else if (coreEnterpriseId.equals("c1006")){ coreEnterpriseName = "金茂" }else if (coreEnterpriseId.equals("c1008")){ coreEnterpriseName = "绿城" }else if (coreEnterpriseId.equals("c1011")){ coreEnterpriseName = "金地" }else if (coreEnterpriseId.equals("c1012")){ coreEnterpriseName = "合景泰富" }else if (coreEnterpriseId.equals("c1013")){ coreEnterpriseName = "金融街" } // 保理金额 accountReceivable val accountReceivable = recordPay.getBigDecimal("accountReceivable").toString() // 开票金额 billingAmount val billingAmount = recordPay.getBigDecimal("billingAmount").toString() //税额 5--9 taxAmount val taxAmount = recordPay.getBigDecimal("taxAmount").toString() // 价税合计 totalAmount val totalAmount = recordPay.getBigDecimal("totalAmount").toString() // 资产托管收入 assetsTrusteeshipIncome val assetsTrusteeshipIncome = recordPay.getBigDecimal("assetsTrusteeshipIncome").toString() // 服务费收入 serviceFeeIncome val serviceFeeIncome = recordPay.getBigDecimal("serviceFeeIncome").toString() // 抵扣价税合计 抵扣价税合计 val 抵扣价税合计 = recordPay.get<String>("") // 抵扣金额 10--14 deductionMoney val deductionMoney = recordPay.get<String>("deductionMoney") // 税额 costTax val costTax = recordPay.get<String>("costTax") //差额 differenceTax val differenceTax = recordPay.get<String>("differenceTax") // 销税额 outputTax val outputTax = recordPay.get<String>("outputTax") // 资产托管收入销项 assetCustodyIncome val assetCustodyIncome = recordPay.getBigDecimal("assetCustodyIncome").toString() // 服务费收入销项 15--19 serviceChargeIncome val serviceChargeIncome = recordPay.getBigDecimal("serviceChargeIncome").toString() // 进项税 inputTax val inputTax = recordPay.getBigDecimal("inputTax").toString() // 进项税转出 transferInputTax val transferInputTax = recordPay.getBigDecimal("transferInputTax").toString() // 应交增值税 VATPayable val VATPayable = recordPay.get<String>("VATPayable") // 实缴 realPayment val realPayment = recordPay.getBigDecimal("realPayment").toString() // 对各元素设值 // 创建表头 //从第三行开始,下标从0开始算 rowPay = sheetPay.createRow(i+1) rowPay!!.setHeightInPoints(25f) // 开票年 billing_year cellPay = rowPay!!.createCell(0) cellPay!!.setCellValue(billingYear) cellPay!!.setCellStyle(cellStyle) // 开票月 billing_month billingMonth cellPay = rowPay!!.createCell(1) cellPay!!.setCellValue(billingMonth) cellPay!!.setCellStyle(cellStyle) // 核心企业 core_enterprise_id coreEnterpriseId cellPay = rowPay!!.createCell(2) cellPay!!.setCellValue(coreEnterpriseName) cellPay!!.setCellStyle(cellStyle) // 保理金额 accountReceivable cellPay = rowPay!!.createCell(3) cellPay!!.setCellValue(accountReceivable) cellPay!!.setCellStyle(cellStyle) // 开票金额 billingAmount cellPay = rowPay!!.createCell(4) cellPay!!.setCellValue(billingAmount) cellPay!!.setCellStyle(cellStyle) //税额 6--10 taxAmount cellPay = rowPay!!.createCell(5) cellPay!!.setCellValue(taxAmount) cellPay!!.setCellStyle(cellStyle) // 价税合计 totalAmount cellPay = rowPay!!.createCell(6) cellPay!!.setCellValue(totalAmount) cellPay!!.setCellStyle(cellStyle) // 资产托管收入 assetsTrusteeshipIncome cellPay = rowPay!!.createCell(7) cellPay!!.setCellValue(assetsTrusteeshipIncome) cellPay!!.setCellStyle(cellStyle) // 服务费收入 serviceFeeIncome cellPay = rowPay!!.createCell(8) cellPay!!.setCellValue(serviceFeeIncome) cellPay!!.setCellStyle(cellStyle) // 抵扣价税合计 抵扣价税合计 cellPay = rowPay!!.createCell(9) cellPay!!.setCellValue(抵扣价税合计) cellPay!!.setCellStyle(cellStyle) // 抵扣金额 11--15 deductionMoney cellPay = rowPay!!.createCell(10) cellPay!!.setCellValue(deductionMoney) cellPay!!.setCellStyle(cellStyle) // 税额 costTax cellPay = rowPay!!.createCell(11) cellPay!!.setCellValue(costTax) cellPay!!.setCellStyle(cellStyle) // 差额 differenceTax cellPay = rowPay!!.createCell(12) cellPay!!.setCellValue(differenceTax) cellPay!!.setCellStyle(cellStyle) // 销税额 outputTax cellPay = rowPay!!.createCell(13) cellPay!!.setCellValue(outputTax) cellPay!!.setCellStyle(cellStyle) // 资产托管收入销项 assetCustodyIncome cellPay = rowPay!!.createCell(14) cellPay!!.setCellValue(assetCustodyIncome) cellPay!!.setCellStyle(cellStyle) // 服务费收入销项 16--20 serviceChargeIncome cellPay = rowPay!!.createCell(15) cellPay!!.setCellValue(serviceChargeIncome) cellPay!!.setCellStyle(cellStyle) // 进项税 inputTax cellPay = rowPay!!.createCell(16) cellPay!!.setCellValue(inputTax) cellPay!!.setCellStyle(cellStyle) //进项税转出 transferInputTax cellPay = rowPay!!.createCell(17) cellPay!!.setCellValue(transferInputTax) cellPay!!.setCellStyle(cellStyle) // 应交增值税 VATPayable cellPay = rowPay!!.createCell(18) cellPay!!.setCellValue(VATPayable) cellPay!!.setCellStyle(cellStyle) // 实缴 realPayment cellPay = rowPay!!.createCell(19) cellPay!!.setCellValue(realPayment) cellPay!!.setCellStyle(cellStyle) i++ } // 保存到文件 workbook.write(fout) fout.close() // 保存文件信息到文件表 val fileUuid = StringUtils.randomUuid() val file = File(savePath) val fileInfo = FileInfo() fileInfo.file = file fileInfo.originalFileName = fileName val saveFile = SysFileUtils.fileInfo2SysFile(fileInfo, "") saveFile.set("uuid", fileUuid) val isTrue = saveFile.save() if (isTrue) { return fileUuid } else { return null } }
/** * 导出 * @param records * @return * @throws IOException * @throws ActiveRecordException */ public String exportResultToExcel(List<Record> records,QhyfController controller) throws IOException, ActiveRecordException { // 创建一个webbook XSSFWorkbook workbook = new XSSFWorkbook(); String savePath = EnvironmentUtils.me().getEnv().getGeneratePath()+File.separator+"保理资料"; File save = new File(savePath); if (!save.exists()&& !save.isDirectory()) { save.mkdirs(); } String date = new SimpleDateFormat("yyyyMMddHHmmss").format(new java.util.Date()); String fileName = "保理资料"+date+".xlsx"; savePath = savePath+File.separator + fileName; FileOutputStream fout = new FileOutputStream(savePath); // 创建开票信息sheet XSSFSheet sheet = workbook.createSheet("供应商保理资料"); sheet.createFreezePane(0, 1, 0, 1); // 创建表头行 XSSFRow headRow = sheet.createRow(0); String[] headArr = { "付款确认书编号", "核心企业", "供应商", "签约主体", "应收账款金额", "保理费率", "放款日", "账款到期日", "保理天数" , "放款金额", "供应商利息", "是否开票", "发票金额","发票号码", "是否回寄", "回寄单号", "项目公司", "合同名称"}; // 单元格 XSSFCell headCell = null; XSSFCellStyle style = workbook.createCellStyle(); // 表头样式设置 style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderTop(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); // 设置背景色 style.setFillForegroundColor(new XSSFColor(new Color(156, 195, 230))); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 写入表头 for (int i = 0; i < headArr.length; i++) { sheet.autoSizeColumn(i); headCell = headRow.createCell(i); headCell.setCellValue(headArr[i]); headCell.setCellStyle(style); } sheet.setColumnWidth(0, 450 * 15); sheet.setColumnWidth(2, 470 * 15); sheet.setColumnWidth(3, 580 * 15); sheet.setColumnWidth(4, 280* 15); sheet.setColumnWidth(6, 230 * 15); sheet.setColumnWidth(7, 230 * 15); sheet.setColumnWidth(9, 270 * 15); sheet.setColumnWidth(10, 420 * 15); sheet.setColumnWidth(12, 270 * 15); sheet.setColumnWidth(13, 512 * 15); sheet.setColumnWidth(15, 256 * 15); sheet.setColumnWidth(16, 540 * 15); sheet.setColumnWidth(17, 540 * 15); //设置单元格样式 XSSFCellStyle cellStyle = workbook.createCellStyle(); //边框 cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); //内容换行 cellStyle.setWrapText(true); //垂直居中 cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); XSSFFont font = workbook.createFont(); font.setColor(new XSSFColor(new Color(0, 0, 255))); XSSFCellStyle linkCellStyle = workbook.createCellStyle(); //边框 linkCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); linkCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); linkCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); linkCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); //内容换行 linkCellStyle.setWrapText(true); //垂直居中 linkCellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); linkCellStyle.setFont(font); XSSFCell cell = null; XSSFRow row = null; if (records != null && records.size() > 0) { // 查询结果不为空 for (int i = 0; i < records.size(); i++) { Record record = records.get(i); row = sheet.createRow(i + 1); row.setHeightInPoints(25); cell = row.createCell(0); cell.setCellValue(record.getStr("payId")); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(record.getStr("coreEnterpriseName")); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(record.getStr("suppliersName")); cell.setCellStyle(cellStyle); cell = row.createCell(3); cell.setCellValue(record.getStr("signBodyName")); cell.setCellStyle(cellStyle); cell.setCellStyle(cellStyle); cell = row.createCell(4); String accountReceivable = ""; if(record.get("accountReceivable") != null){ accountReceivable = record.get("accountReceivable").toString(); } cell.setCellValue(accountReceivable); cell.setCellStyle(cellStyle); cell = row.createCell(5); String suppliersRateBiz = ""; if(record.get("suppliersRateBiz") != null){ suppliersRateBiz = record.get("suppliersRateBiz").toString(); } cell.setCellValue(suppliersRateBiz); cell.setCellStyle(cellStyle); cell = row.createCell(6); String loanDate = ""; if(record.get("loanDate") != null){ loanDate = record.get("loanDate").toString(); } cell.setCellValue(loanDate); cell.setCellStyle(cellStyle); cell = row.createCell(7); String dueDate = ""; if(record.get("dueDate") != null){ dueDate = record.get("dueDate").toString(); } cell.setCellValue(dueDate); cell.setCellStyle(cellStyle); cell = row.createCell(8); String transferDays = ""; if(record.get("transferDays") != null){ transferDays = record.get("transferDays").toString(); } cell.setCellValue(transferDays); cell.setCellStyle(cellStyle); cell = row.createCell(9); String suppliersMoney = ""; if(record.get("suppliersMoney") != null){ suppliersMoney = record.get("suppliersMoney").toString(); } cell.setCellValue(suppliersMoney); cell.setCellStyle(cellStyle); cell = row.createCell(10); String suppliersInterest = ""; if(record.get("suppliersInterest") != null){ DecimalFormat df =new DecimalFormat("#.00"); suppliersInterest = df.format(record.get("suppliersInterest")); } cell.setCellValue(suppliersInterest); cell.setCellStyle(cellStyle); cell = row.createCell(11); String invoiceId = "否"; if(!StringUtils.isBlank(record.getStr("invoiceNo"))){ invoiceId = "是"; } cell.setCellValue(invoiceId); cell.setCellStyle(cellStyle); cell = row.createCell(12); String invoiceAmount = ""; if(record.get("invoiceAmount") != null){ invoiceAmount = record.get("invoiceAmount").toString(); } cell.setCellValue(invoiceAmount); cell.setCellStyle(cellStyle); cell = row.createCell(13); String invoiceDescription = ""; if(!StringUtils.isBlank(record.get("invoiceNo"))){ invoiceDescription = record.get("invoiceNo"); invoiceDescription = invoiceDescription.replace(",", "\n"); } cell.setCellValue(invoiceDescription); cell.setCellStyle(cellStyle); cell = row.createCell(14); String returnId = "否"; if(!StringUtils.isBlank(record.getStr("returnId"))){ returnId = "是"; } cell.setCellValue(returnId); cell.setCellStyle(cellStyle); cell = row.createCell(15); String returnExpressNo = ""; if(!StringUtils.isBlank(record.get("expressNo"))){ returnExpressNo = record.get("expressNo"); } cell.setCellValue(returnExpressNo); cell.setCellStyle(cellStyle); cell = row.createCell(16); String itemCompanyName = ""; if(!StringUtils.isBlank(record.get("itemCompanyName"))){ itemCompanyName = record.get("itemCompanyName"); } cell.setCellValue(itemCompanyName); cell.setCellStyle(cellStyle); cell = row.createCell(17); String contractName = ""; if(!StringUtils.isBlank(record.get("contractName"))){ contractName = record.get("contractName"); } cell.setCellValue(contractName); cell.setCellStyle(cellStyle); } } // 保存到文件 workbook.write(fout); fout.close(); // 保存文件信息到文件表 String fileUuid = StringUtils.randomUuid(); File file = new File(savePath); FileInfo fileInfo = new FileInfo(); fileInfo.setFile(file); fileInfo.setOriginalFileName(fileName); SysFile saveFile = SysFileUtils.fileInfo2SysFile(fileInfo, null); saveFile.set("uuid", fileUuid); boolean isTrue = saveFile.save(); if (isTrue) { // 保存成功 // 文件上传云端 FileInfo UpFileInfo = this.getFileInfoByUuid(fileUuid); this.upload2cloud(UpFileInfo, true); return fileUuid; } else { return null; } }