页面增加导入数据按钮,新增点击导入按钮后弹出的页面组件
< section>
< gv-data-table ref = " table" :table = " table" @on-reset = " onReset" @on-list-btn = " onListBtn" >
< template v-slot: search= " scope" >
< div class = " gv-row" >
< gv-form-item key-name = " 关键字" prop = " keyWord" :colspan = " 5" >
< el-input :placeholder = " ' 请输入报价单号/项目编号/客户名称/销售/计费单号' | translate" v-model = " scope.search.keyWord"
clearable > </ el-input>
</ gv-form-item>
</ div>
< div class = " gv-row" >
< gv-form-item key-name = " custVoDepartment" prop = " department" :colspan = " 5" >
< el-cascader filterable @change = " departmentChange" v-model = " department.value"
:options = " department.data"
:props = " department.defaultProps"
clearable > </ el-cascader>
</ gv-form-item>
</ div>
< div class = " gv-row" >
< gv-form-item key-name = " 坏帐标志" prop = " isDadDebts" >
< gv-select code-type = " IsDadDebts" options-set = " 2" v-model = " scope.search.isDadDebts" >
</ gv-select>
</ gv-form-item>
</ div>
</ template>
< template v-slot: toolbar= " scope" >
< div class = " pull-left" >
< el-button class = " gv-btn gv-btn-primary" @click = " openImportDialogPage()" >
{{ '导入表格数据' | translate }}
</ el-button>
</ div>
</ template>
</ gv-data-table>
< transition name = " dialog-fade" >
< test-item-price-revise-app-adj-pay v-if = " isShowPay" @onClose = " onCallPay" :testitemNo = " testitemNo"
:detailId = " detailId" :quotationNo = " quotationNo"
:testitemId = " testitemId"
:msTestItemOutlayRecordVoList = " msTestItemOutlayRecordVoList"
:toRmbPrice = " toRmbPrice"
:rmbActualAgentPrice = " rmbActualAgentPrice"
:isAgent = " isAgent"
:agentSupplierCode = " agentSupplierCode"
:agentSupplierName = " agentSupplierName" />
</ transition>
< transition name = " dialog-fade" >
< import-dialog-page-of-excel
v-if = " isShowImportDialogPage"
@closeImportDialogPage = " closeImportDialogPage" />
</ transition>
</ section>
javascript">
define ( function ( require ) {
var TestItemPriceReviseAppAdjPay = require ( './components/testItemPriceReviseAppAdjPay' ) ;
var ImportDialogPageOfExcel = require ( './components/importDialogPageOfExcel' ) ;
return Vue. gvUtil. Page ( {
template : require ( './index.html' ) ,
name : 'testItemOutlayRecordApp' ,
components : {
TestItemPriceReviseAppAdjPay : TestItemPriceReviseAppAdjPay,
ImportDialogPageOfExcel : ImportDialogPageOfExcel,
} ,
params : function ( ) {
return {
isShowPay : false ,
msTestItemOutlayRecordVoList : [ ] ,
testitemNo : '' ,
detailId : '' ,
testitemId : '' ,
toRmbPrice : '' ,
rmbActualAgentPrice : '' ,
isAgent : '' ,
agentSupplierCode : '' ,
agentSupplierName : '' ,
isShowImportDialogPage : false ,
}
} ,
datas : function ( ) {
return {
table : {
basic : {
api : "testORList" ,
vo : "testItemOutlayReviseList" ,
context : "core" ,
singleElection : false ,
multipleElection : false ,
expand : false ,
autoSearch : true ,
isQuotationSearchShow : true
} ,
search : {
keyWord : '' ,
salesDepartmentCode : '' ,
department : '' ,
isDadDebts : '' ,
isShowSiteTest : '0'
} ,
fields : [ {
prop : null ,
labelKey : 'gTitleOperation' ,
width : '100px' ,
btns : [ {
btnKey : '支出调整' ,
flag : '1' ,
type : 'btn'
} ]
} , {
prop : 'quotationNo' ,
width : '130px' ,
labelKey : '报价单号'
} , {
prop : 'testitemNo' ,
width : '140px' ,
labelKey : '项目编号'
} , {
prop : 'isMain' ,
width : '120px' ,
labelKey : '是否主项目'
} , {
prop : 'starName' ,
width : '220px' ,
labelKey : '客户名称'
} , {
prop : 'salesName' ,
width : '100px' ,
labelKey : '销售' ,
} , {
prop : 'salesDepartmentName' ,
width : '130px' ,
labelKey : '部门' ,
showOverflowTooltip : true ,
} , {
prop : 'quotationStatus' ,
width : '110px' ,
labelKey : '报价单状态' ,
format : {
codeType : 'quotationStatus' ,
type : 'ggcode'
}
} , {
prop : 'orgName' ,
width : '130px' ,
labelKey : '承接实验室' ,
showOverflowTooltip : true ,
} , {
prop : 'status' ,
width : '120px' ,
labelKey : '项目状态' ,
format : {
codeType : 'LabProjectStatus' ,
type : 'ggcode'
}
} , {
prop : 'incomeConfirmStatus' ,
width : '150px' ,
labelKey : '是否收入确认' ,
format : {
codeType : 'IncomeConfirmStatus' ,
type : 'ggcode'
}
} , {
prop : 'isDadDebts' ,
width : '120px' ,
labelKey : '坏帐标记' ,
format : {
codeType : 'IsDadDebts' ,
type : 'ggcode'
}
} , {
prop : 'toRmbPrice' ,
labelKey : '项目金额(RMB)' ,
width : '150px' ,
align : 'right' ,
format : {
type : 'num' ,
}
} , {
prop : 'rmbActualAgentPrice' ,
labelKey : '外包金额(RMB)' ,
width : '150px' ,
align : 'right' ,
format : {
type : 'num' ,
}
} , {
prop : 'outlayPriceTotal' ,
labelKey : '支出成本(RMB)' ,
width : '150px' ,
align : 'right' ,
format : {
type : 'num' ,
}
} , {
prop : 'netPrice' ,
labelKey : '净额(RMB)' ,
width : '150px' ,
align : 'right' ,
format : {
type : 'num' ,
}
} , {
prop : 'logUpdateTime' ,
labelKey : '最新修改金额时间' ,
width : '150px' ,
align : 'right'
} ]
} ,
department : {
data : [ {
label : '暂无数据' ,
value : ''
} ] ,
defaultProps : {
multiple : false ,
expandTrigger : 'hover' ,
children : 'children' ,
label : 'label' ,
value : 'id'
} ,
value : [ ]
}
}
} ,
events : {
onReset : function ( ) {
this . table. search. salesDepartmentCode = '' ;
this . department. value = [ ] ;
} ,
onListBtn : function ( row, type ) {
switch ( type) {
case '1' :
this . onOpenPay ( row) ;
break ;
}
} ,
onOpenPay : function ( row ) {
this . isShowPay = true ;
this . msTestItemOutlayRecordVoList = row. msTestItemOutlayRecordVoList;
this . detailId = row. detailId;
this . testitemId = row. testitemId ;
this . testitemNo = row. testitemNo;
this . quotationNo = row. quotationNo;
this . toRmbPrice = row. toRmbPrice;
this . rmbActualAgentPrice = row. rmbActualAgentPrice;
this . isAgent = row. isAgent;
this . agentSupplierCode = row. agentSupplierCode;
this . agentSupplierName = row. agentSupplierName;
} ,
onCallPay : function ( flag ) {
this . isShowPay = false ;
if ( flag) {
this . searchLists ( ) ;
}
this . detailId = null ;
this . testitemId = '' ;
this . msTestItemOutlayRecordVoList = [ ] ;
this . testitemNo = '' ;
} ,
} ,
methods : {
initPage : function ( ) {
this . requestDepartment ( ) ;
} ,
departmentChange : function ( data ) {
if ( data instanceof Array && data. length > 0 ) {
this . table. search. salesDepartmentCode = data[ data. length - 1 ] ;
} else {
this . table. search. salesDepartmentCode = ''
}
} ,
requestDepartment : function ( ) {
const _this = this ;
let data = {
orgCate : '1' ,
fdOrgType : '2'
} ;
let url = Vue. gvUtil. getUrl ( {
apiName : 'orgSearchTree' ,
contextName : 'auth'
} ) ;
Vue. gvUtil. http. post ( url, data) . then ( function ( res ) {
if ( res. resCode === '0000' ) {
_this. setDepartment ( [ res. resData. navMenusData] )
}
} ) ;
} ,
setDepartment : function ( data ) {
for ( let i = 0 ; i < data. length; i++ ) {
if ( data[ i] . children instanceof Array && data[ i] . children. length > 0 ) {
this . setDepartment ( data[ i] . children) ;
} else {
data[ i] . children = undefined
}
}
this . department. data = data;
} ,
openImportDialogPage : function ( ) {
setTimeout ( ( ) => {
this . isShowImportDialogPage = true ;
} , 500 )
} ,
closeImportDialogPage : function ( param ) {
this . isShowImportDialogPage = false ;
if ( param == 'close' ) {
window. location. reload ( ) ;
}
} ,
}
} ) ;
} ) ;
javascript">( function ( ) {
return {
api : {
'testORList' : '/msTestitemDetails/find_testItem_outlay_revise_list' ,
'testPRGetPriceRecordAdd' : '/msTestitemOutlayRecord/add' ,
'orgSearchTree' : '/org/element/findTreeByCateid' ,
'importCapexDataOfExcel' : '/msTestitemOutlayRecord/importCapexDataOfExcel' ,
'exportCapexDataTemplate' : '/msTestitemOutlayRecord/exportCapexDataTemplate' ,
} ,
router : [ ]
}
} ) ( ) ;
components组件文件夹
importDialogPageOfExcel.html
< section>
< el-dialog
title = " 导入表格数据"
:visible.sync = " isShowUpload"
width = " 40%" custom-class = " gv-dialog-form"
:close-on-click-modal = " false" style = " min-height : 500px; "
:before-close = " onClose" >
< gv-form ref = " uploadForm" :model = " uploadForm" >
< gv-panel sub-title = " gRequiresTitle" >
< div class = " gv-row" >
< gv-form-item key-name = " 模板下载" :colspan = " 5" >
< el-button @click = " downLoad()" type = " primary" >
{{'gBtnDownLoad' | translate}}< i class = " el-icon-upload el-icon--right" > </ i>
</ el-button>
</ gv-form-item>
</ div>
< div class = " gv-row" >
< gv-form-item key-name = " Excel导入" prop = " file" :colspan = " 5" >
< el-input v-model = " uploadForm.fileSize" v-show = " false" > </ el-input>
< el-upload class = " upload-demo"
:auto-upload = " false"
ref = " upFile"
action = " upFileUrl"
:multiple = " false"
:limit = " 1"
:on-remove = " handleRemove"
:on-change = " getFile"
:file-list = " uploadFormFileList"
:on-exceed = " onExceed" >
< el-button size = " small" type = " primary" > {{'gChangeUpload' | translate}}</ el-button>
< div slot = " tip" class = " el-upload__tip" > {{'temporaryPriceVoOnlyUploadExcelFile' |
translate}}
</ div>
</ el-upload>
</ gv-form-item>
</ div>
</ gv-panel>
</ gv-form>
< el-row class = " toolbar-btn txt-center" >
< el-button class = " gv-btn gv-btn-primary" type = " primary" @click.stop = " onuUploadSubmit()" >
{{ '确认' | translate }}
</ el-button>
< el-button class = " gv-btn gv-btn-white" @click.stop = " onClose" >
{{ 'gBtnClose' | translate('Close') }}
</ el-button>
</ el-row>
</ el-dialog>
</ section>
importDialogPageOfExcel.js
javascript">
define ( function ( require ) {
return Vue. gvUtil. Page ( {
template : require ( './importDialogPageOfExcel.html' ) ,
name : 'importDialogPageOfExcelApp' ,
props : {
quotationInfo : {
type : Object,
requires : true ,
}
} ,
datas : function ( ) {
return {
isShowUpload : true ,
uploadForm : {
file : [ ] ,
} ,
uploadFormFileList : [ ]
}
} ,
events : {
onCloseUpload : function ( param ) {
if ( this . uploadFormFileList. length > 0 ) {
this . uploadFormFileList = [ ] ;
}
this . $emit ( 'closeImportDialogPage' , param) ;
} ,
onClose : function ( ) {
if ( this . uploadFormFileList. length > 0 ) {
this . uploadFormFileList = [ ] ;
}
this . $emit ( 'closeImportDialogPage' , '' ) ;
} ,
getFile : function ( File, uploadFormFileList ) {
if ( this . uploadFormFileList. length > 0 ) {
this . handleRemove ( File, this . uploadFormFileList) ;
}
let file = File. raw;
let fileinfo = file. name. split ( '.' ) ;
let type = fileinfo[ fileinfo. length - 1 ] ;
if ( ! ( file. type === 'application/vnd.ms-excel ' || type === 'xlsx' || type === 'xls' ) ) {
Vue. gvUtil. alert ( {
msg : Vue. gvUtil. getInzTranslate ( '请上传Excel文件' ) ,
} ) . then ( function ( ) {
} )
this . uploadFormFileList = [ ] ;
return ;
}
this . uploadFormFileList = uploadFormFileList;
} ,
handleRemove : function ( File, uploadFormFileList ) {
this . uploadFormFileList = uploadFormFileList;
} ,
onExceed : function ( File, uploadFormFileList ) {
this . uploadFormFileList = [ {
raw : File[ 0 ] ,
name : File[ 0 ] . name
} ] ;
this . checkUploadFlie ( File, this . uploadFormFileList) ;
} ,
checkUploadFlie : function ( File, uploadFormFileList ) {
if ( this . uploadFormFileList. length > 0 ) {
this . handleRemove ( File, this . uploadFormFileList) ;
}
let file = uploadFormFileList[ 0 ] . raw;
let fileinfo = file. name. split ( '.' ) ;
let type = fileinfo[ fileinfo. length - 1 ] ;
if ( ! ( file. type === 'application/vnd.ms-excel ' || type === 'xlsx' || type === 'xls' ) ) {
Vue. gvUtil. alert ( {
msg : Vue. gvUtil. getInzTranslate ( '请上传Excel文件' ) ,
} ) . then ( function ( ) {
} )
this . uploadFormFileList = [ ] ;
return ;
}
this . uploadFormFileList = uploadFormFileList;
} ,
} ,
methods : {
initPage : function ( ) {
} ,
onuUploadSubmit : function ( ) {
const _this = this ;
_this. $refs. uploadForm. validate ( function ( valid ) {
if ( valid) {
if ( _this. uploadFormFileList. length > 0 ) {
let formData = new FormData ( ) ;
formData. append ( 'file' , _this. uploadFormFileList[ 0 ] . raw) ;
var url = Vue. gvUtil. getUrl ( {
apiName : 'importCapexDataOfExcel' ,
contextName : 'core' ,
} )
Vue. gvUtil. http. post ( url, formData) . then ( function ( res ) {
_this. appTempUploadSuccessSubmit ( res) ;
} )
} else {
Vue. gvUtil. message ( Vue. gvUtil. getInzTranslate ( '校验不通过,请上传Excel文件!' ) )
}
}
} )
} ,
appTempUploadSuccessSubmit : function ( res ) {
const _this = this ;
if ( res. resCode === '0000' ) {
if ( res. resData === "导入成功" ) {
Vue. gvUtil. message ( res. resData, 3000 , 'success' ) ;
_this. onCloseUpload ( 'close' ) ;
} else {
_this. $alert ( res. resData, '' , {
dangerouslyUseHTMLString : true
} ) ;
}
}
} ,
downLoad : function ( ) {
var url = Vue. gvUtil. getUrl ( {
apiName : 'exportCapexDataTemplate' ,
contextName : 'core' ,
serachParms : {
templateId : 'exportCapexDataTemplate' ,
}
} ) ;
url = url;
var a = document. createElement ( "a" ) ;
a. href = url;
$ ( "body" ) . append ( a) ;
a. click ( ) ;
$ ( a) . remove ( ) ;
} ,
} ,
} ) ;
} ) ;
新增点击导入按钮后,处理“支出成本调整Excel导入表格数据”的后台方法
java">
@ApiOperation ( "支出成本调整Excel导入表格数据" )
@RequestMapping ( value = { "/importCapexDataOfExcel" } , method = { RequestMethod . POST } )
public BaseResponse < String > importCapexDataOfExcel ( HttpServletRequest request, @RequestParam ( "file" ) MultipartFile file) {
BaseResponse < String > result = null ;
try {
result = msTestitemOutlayRecordService. importCapexDataOfExcel ( request, file) ;
} catch ( Exception e) {
LOG . info ( "core_api_importCapexDataOfExcel导入失败" , e) ;
result = new BaseResponse < > ( ResCodeConstant . RESCODE_SUCCESS , TemplateParam . IMPORT_FAILED ) ;
}
return result;
}
@ApiOperation ( "支出成本调整Excel模板导出" )
@GetMapping ( "/exportCapexDataTemplate" )
public Object exportCapexDataTemplate ( HttpServletRequest request, HttpServletResponse response, @RequestParam String templateId) {
HttpServletResponse httpServletResponse = null ;
if ( StringUtils . isEmpty ( templateId) ) {
return new BaseResponse ( ResCodeConstant . RESCODE_SUCCESS , TemplateParam . NO_RELATED_TEMPLATE_FOUND ) ;
}
try {
httpServletResponse = msTestitemOutlayRecordService. exportCapexDataTemplate ( request, response, templateId) ;
} catch ( Exception e) {
LOG . error ( "core_api_exportCapexDataTemplate下载模板失败" , e) ;
return new BaseResponse ( ResCodeConstant . RESCODE_SUCCESS , TemplateParam . FAILED_TO_DOWNLOAD_TEMPLATE ) ;
}
return httpServletResponse;
}
java">
BaseResponse < String > importCapexDataOfExcel ( HttpServletRequest request, MultipartFile file) ;
HttpServletResponse exportCapexDataTemplate ( HttpServletRequest request, HttpServletResponse response, String templateId) ;
java">
@Autowired
private MsApplicationMainService msApplicationMainService;
public static final String TESTITEM_NO = "项目编号" ;
public static final String QUOTATION_NO = "报价单号" ;
public static final String OUTLAY_PRICE = "支出金额" ;
public static final String OUTLAY_TYPE = "支出类型" ;
public static final String REMARK = "支出原因" ;
public static final String CREATETIME = "支出时间" ;
public static final String OUTLAYRECORD_TEMPLATE_DOWN_PATH = "files" + File . separator + "template" + File . separator + "outlayRecord" + File . separator;
public static final String OUTLAYRECORD_FORM = "testItemOutlayRecord" ;
public static final String OUTLAYRECORD_NAME = "支出成本调整模板" ;
public static final String XLS = ".xls" ;
public Map < String , Object > findTestItemOutlayReviseList ( HttpServletRequest request, @RequestBody @ApiParam ( "入参对象" ) MsTestitemOutlayReviseReqVo msTestitemOutlayReviseReqVo, String testitemNo) {
Map < String , Object > map = new HashMap < String , Object > ( 4 ) ;
String fdId = ( String ) this . redisTemplate. opsForHash ( ) . get ( request. getHeader ( CommonConstant . TP_SESSION_KEY ) , BaseConstant . REDIS_LOGIN_USER_FDID_KEY ) ;
msTestitemOutlayReviseReqVo. setFdId ( fdId) ;
msTestitemOutlayReviseReqVo. setIsShowSiteTest ( "0" ) ;
if ( ! "" . equals ( testitemNo) || testitemNo != "" ) {
msTestitemOutlayReviseReqVo. setTestitemNo ( testitemNo) ;
}
if ( "0" . equals ( msTestitemOutlayReviseReqVo. getIsShowSiteTest ( ) ) ) {
msTestitemOutlayReviseReqVo. setUserPerInfo ( msApplicationMainService. createUserPermissionsVo2 ( 1 , msTestitemOutlayReviseReqVo. getFdId ( ) , UserPermissionsType . JURISDICTION , UserPermissionsVo. Joiner . AND , "mb.sales_code" ) ) ;
} else {
msTestitemOutlayReviseReqVo. setUserPerInfo ( msApplicationMainService. createUserPermissionsVo2 ( 1 , msTestitemOutlayReviseReqVo. getFdId ( ) , UserPermissionsType . JURISDICTION , UserPermissionsVo. Joiner . AND , "mstm.sales_code" ) ) ;
}
map. put ( "testItemOutlayReviseList" , msTestitemDetailsService. findTestItemNoList ( msTestitemOutlayReviseReqVo) ) ;
return map;
}
@Override
@Transactional ( propagation = Propagation . REQUIRED , rollbackFor = Exception . class )
public BaseResponse < String > importCapexDataOfExcel ( HttpServletRequest request, MultipartFile file) {
Map < String , Object > listOutlayMap = this . findTestItemOutlayReviseList ( request, new MsTestitemOutlayReviseReqVo ( ) , "" ) ;
List < String > testitemNoList = new ArrayList < String > ( ) ;
List < MsTestitemPriceReviseVo > priceReviseList = new ArrayList < MsTestitemPriceReviseVo > ( ) ;
if ( ! listOutlayMap. isEmpty ( ) ) {
priceReviseList = ( List < MsTestitemPriceReviseVo > ) listOutlayMap. get ( "testItemOutlayReviseList" ) ;
if ( priceReviseList. size ( ) != 0 ) {
for ( int i = 0 ; i < priceReviseList. size ( ) ; i++ ) {
testitemNoList. add ( priceReviseList. get ( i) . getTestitemNo ( ) ) ;
}
}
}
String result = TemplateParam . IMPORT_SUCCEEDED ;
StringBuffer excel Result = new StringBuffer ( ) ;
Boolean isGoOn = true ;
String testitemNo = "" ;
Boolean isRepeatNo = false ;
String cellValue = "" ;
Boolean isToQuery = true ;
Boolean isRowTestitemNo = false ;
String messCell = "" ;
String outlayPriceReg = "^[0-9]+(.[0-9]+)?$" ;
try {
Workbook workbook = this . getWorkbook ( file. getInputStream ( ) , file. getResource ( ) . getFilename ( ) ) ;
if ( null == workbook) {
result = "创建Excel为空,导入失败。" ;
return new BaseResponse ( ResCodeConstant . RESCODE_SUCCESS , result) ;
}
Sheet sheet = null ;
Row row = null ;
Cell cell = null ;
List < Map < String , String > > allRowList = new ArrayList < Map < String , String > > ( ) ;
int isEmptyRow = 0 ;
for ( int i = 0 ; i < workbook. getNumberOfSheets ( ) ; i++ ) {
sheet = workbook. getSheetAt ( i) ;
if ( sheet == null ) {
continue ;
}
for ( int j = sheet. getFirstRowNum ( ) + 1 ; j <= sheet. getLastRowNum ( ) ; j++ ) {
int rowNum = j + 1 ;
isGoOn = true ;
isRepeatNo = false ;
isToQuery = true ;
isRowTestitemNo = false ;
row = sheet. getRow ( j) ;
if ( row == null ) {
isEmptyRow++ ;
continue ;
}
if ( ( "" . equals ( getValue2Format ( row. getCell ( 0 ) ) ) || getValue2Format ( row. getCell ( 0 ) ) == "" ) &&
( "" . equals ( getValue2Format ( row. getCell ( 1 ) ) ) || getValue2Format ( row. getCell ( 1 ) ) == "" ) &&
( "" . equals ( getValue2Format ( row. getCell ( 2 ) ) ) || getValue2Format ( row. getCell ( 2 ) ) == "" ) &&
( "" . equals ( getValue2Format ( row. getCell ( 3 ) ) ) || getValue2Format ( row. getCell ( 3 ) ) == "" ) &&
( "" . equals ( getValue2Format ( row. getCell ( 4 ) ) ) || getValue2Format ( row. getCell ( 4 ) ) == "" ) &&
( "" . equals ( getValue2Format ( row. getCell ( 5 ) ) ) || getValue2Format ( row. getCell ( 5 ) ) == "" ) &&
( "" . equals ( getValue2Format ( row. getCell ( 6 ) ) ) || getValue2Format ( row. getCell ( 6 ) ) == "" ) &&
( "" . equals ( getValue2Format ( row. getCell ( 7 ) ) ) || getValue2Format ( row. getCell ( 7 ) ) == "" ) &&
( "" . equals ( getValue2Format ( row. getCell ( 8 ) ) ) || getValue2Format ( row. getCell ( 8 ) ) == "" ) &&
( "" . equals ( getValue2Format ( row. getCell ( 9 ) ) ) || getValue2Format ( row. getCell ( 9 ) ) == "" ) &&
( "" . equals ( getValue2Format ( row. getCell ( 10 ) ) ) || getValue2Format ( row. getCell ( 10 ) ) == "" )
) {
isEmptyRow++ ;
continue ;
}
if ( isEmptyRow > 0 && isEmptyRow <= 3 ) {
return new BaseResponse ( ResCodeConstant . RESCODE_SUCCESS , "表格中存在空行,导入失败。" ) ;
} else if ( isEmptyRow >= 4 && isEmptyRow <= 7 ) {
break ;
} else if ( isEmptyRow > 7 ) {
break ;
}
Map < String , String > cellMap = new HashMap < > ( ) ;
for ( int k = row. getFirstCellNum ( ) ; k < row. getLastCellNum ( ) ; k++ ) {
if ( k >= 11 ) {
break ;
}
if ( k == 0 ) {
messCell = TESTITEM_NO ;
} else if ( k == 1 ) {
messCell = QUOTATION_NO ;
} else if ( k == 6 ) {
messCell = OUTLAY_PRICE ;
} else if ( k == 7 ) {
messCell = OUTLAY_TYPE ;
} else if ( k == 8 ) {
messCell = REMARK ;
} else if ( k == 9 ) {
messCell = CREATETIME ;
}
cell = row. getCell ( k) ;
cellValue = getValue2Format ( cell) ;
if ( k == 0 || k == 1 || k == 6 || k == 7 || k == 8 || k == 9 ) {
if ( "" . equals ( cellValue) || cellValue == "" ) {
excel Result. append ( "第[" + rowNum + "]行,[" + messCell + "]为空,请确认导入的文件。<br/>" ) ;
isGoOn = false ;
}
}
if ( isGoOn) {
if ( k == 0 ) {
if ( allRowList. size ( ) != 0 ) {
for ( int z = 0 ; z < allRowList. size ( ) ; z++ ) {
testitemNo = allRowList. get ( z) . get ( "testitemNo" ) ;
if ( StringUtils . isNotEmpty ( testitemNo) ) {
if ( cellValue. equals ( testitemNo) || cellValue == testitemNo) {
excel Result. append ( "[项目编号:" + cellValue + "]在表格中重复存在,请确认导入的文件。<br/>" ) ;
isRepeatNo = true ;
isToQuery = false ;
isRowTestitemNo = true ;
break ;
}
} else {
excel Result. append ( "已导入数据项目编号存在异常。<br/>" ) ;
}
}
}
if ( ! isRepeatNo) {
if ( ! testitemNoList. contains ( cellValue) ) {
excel Result. append ( "[项目编号:" + cellValue + "]查询不到,请确认导入的文件。<br/>" ) ;
isToQuery = false ;
isRowTestitemNo = true ;
} else {
cellMap. put ( "testitemNo" , cellValue) ;
testitemNo = cellValue;
isRowTestitemNo = false ;
}
}
if ( isRowTestitemNo) {
break ;
}
} else {
if ( k == 1 ) {
cellMap. put ( "quotationNO" , cellValue) ;
} else if ( k == 6 ) {
if ( ! cellValue. matches ( outlayPriceReg) ) {
excel Result. append ( "第[" + rowNum + "]行,[" + messCell + "]异常,请确认导入的文件。<br/>" ) ;
isToQuery = false ;
} else {
cellMap. put ( "outlayPrice" , cellValue) ;
}
} else if ( k == 7 ) {
if ( "差旅费" . equals ( cellValue) || cellValue == "差旅费" ) {
cellMap. put ( "outlayType" , "1" ) ;
} else if ( "运费" . equals ( cellValue) || cellValue == "运费" ) {
cellMap. put ( "outlayType" , "2" ) ;
} else if ( "外包调整" . equals ( cellValue) || cellValue == "外包调整" ) {
cellMap. put ( "outlayType" , "3" ) ;
} else if ( "样品费" . equals ( cellValue) || cellValue == "样品费" ) {
cellMap. put ( "outlayType" , "4" ) ;
} else if ( "工装" . equals ( cellValue) || cellValue == "工装" ) {
cellMap. put ( "outlayType" , "5" ) ;
} else if ( "汇兑损失" . equals ( cellValue) || cellValue == "汇兑损失" ) {
cellMap. put ( "outlayType" , "6" ) ;
} else if ( "收入调整" . equals ( cellValue) || cellValue == "收入调整" ) {
cellMap. put ( "outlayType" , "7" ) ;
} else if ( "其他" . equals ( cellValue) || cellValue == "其他" ) {
cellMap. put ( "outlayType" , "9" ) ;
} else if ( "专用耗材" . equals ( cellValue) || cellValue == "专用耗材" ) {
cellMap. put ( "outlayType" , "11" ) ;
} else {
excel Result. append ( "第[" + rowNum + "]行,[" + messCell + "]异常,请确认导入的文件。<br/>" ) ;
isToQuery = false ;
}
} else if ( k == 8 ) {
cellValue = "[自动导入]" + cellValue;
cellMap. put ( "remark" , cellValue) ;
} else if ( k == 9 ) {
DateTimeFormatter dtf = DateTimeFormatter . ofPattern ( "yyyy-MM-dd HH:mm:ss" ) ;
try {
LocalDateTime . parse ( cellValue, dtf) ;
cellMap. put ( "CreateTime" , cellValue) ;
} catch ( Exception e) {
excel Result. append ( "第[" + rowNum + "]行,[" + messCell + "]异常,请确认导入的文件。<br/>" ) ;
isToQuery = false ;
}
} else if ( k == 10 && isToQuery) {
Map < String , Object > testItemMap = this . findTestItemOutlayReviseList ( request, new MsTestitemOutlayReviseReqVo ( ) , testitemNo) ;
if ( ! testItemMap. isEmpty ( ) ) {
List < MsTestitemPriceReviseVo > priceReviseList2 = ( List < MsTestitemPriceReviseVo > ) testItemMap. get ( "testItemOutlayReviseList" ) ;
if ( priceReviseList2. size ( ) != 0 ) {
cellMap. put ( "detailId" , priceReviseList2. get ( 0 ) . getDetailId ( ) ) ;
cellMap. put ( "agentSupplierCode" , priceReviseList2. get ( 0 ) . getAgentSupplierCode ( ) ) ;
cellMap. put ( "testitemId" , priceReviseList2. get ( 0 ) . getTestitemId ( ) ) ;
}
} else {
excel Result. append ( "[项目编号:" + testitemNo + "]查询不到相对应的数据,请确认导入的文件。<br/>" ) ;
}
}
}
}
}
if ( ! cellMap. isEmpty ( ) ) {
allRowList. add ( cellMap) ;
}
}
}
if ( excel Result. toString ( ) . length ( ) > 0 ) {
String excel ResultStr = "" ;
String resultStr[ ] = excel Result. toString ( ) . split ( "<br/>" ) ;
for ( int i = 0 ; i < resultStr. length; i++ ) {
if ( i < 20 ) {
excel ResultStr += resultStr[ i] + "<br/>" ;
}
}
return new BaseResponse ( ResCodeConstant . RESCODE_SUCCESS , excel ResultStr) ;
}
if ( allRowList. size ( ) != 0 ) {
for ( int i = 0 ; i < allRowList. size ( ) ; i++ ) {
try {
MsTestitemOutlayRecordVo msTestitemOutlayRecordVo = new MsTestitemOutlayRecordVo ( ) ;
msTestitemOutlayRecordVo. setTestitemNo ( allRowList. get ( i) . get ( "testitemNo" ) ) ;
msTestitemOutlayRecordVo. setQuotationNo ( allRowList. get ( i) . get ( "quotationNO" ) ) ;
msTestitemOutlayRecordVo. setOutlayPrice ( new BigDecimal ( allRowList. get ( i) . get ( "outlayPrice" ) ) ) ;
msTestitemOutlayRecordVo. setOutlayType ( allRowList. get ( i) . get ( "outlayType" ) ) ;
msTestitemOutlayRecordVo. setRemark ( allRowList. get ( i) . get ( "remark" ) ) ;
msTestitemOutlayRecordVo. setCreateTime ( new SimpleDateFormat ( "yyyy-MM-dd HH:mm:ss" ) . parse ( allRowList. get ( i) . get ( "CreateTime" ) ) ) ;
msTestitemOutlayRecordVo. setDetailId ( allRowList. get ( i) . get ( "detailId" ) ) ;
msTestitemOutlayRecordVo. setAgentSupplierCode ( allRowList. get ( i) . get ( "agentSupplierCode" ) ) ;
msTestitemOutlayRecordVo. setTestitemId ( allRowList. get ( i) . get ( "testitemId" ) ) ;
this . add ( request, msTestitemOutlayRecordVo) ;
} catch ( Exception e) {
LOG . error ( e. getMessage ( ) , e) ;
return new BaseResponse ( ResCodeConstant . RESCODE_SUCCESS , "支出成本调整保存失败" ) ;
}
}
} else {
result = "获取导入的数据为空,导入失败。" ;
return new BaseResponse ( ResCodeConstant . RESCODE_SUCCESS , result) ;
}
} catch ( IOException e) {
e. printStackTrace ( ) ;
return new BaseResponse ( ResCodeConstant . RESCODE_SUCCESS , "导入报错,请联系管理员。" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
return new BaseResponse ( ResCodeConstant . RESCODE_SUCCESS , "导入报错,请联系管理员。" ) ;
}
return new BaseResponse ( ResCodeConstant . RESCODE_SUCCESS , result) ;
}
public Workbook getWorkbook ( InputStream inputStream, String fileName) throws Exception {
Workbook wb = null ;
boolean isE2007 = false ;
String xlsx = TemplateParam . NO_POINT_XLSX ;
if ( fileName. endsWith ( xlsx) ) {
isE2007 = true ;
}
try {
if ( isE2007) {
wb = new XSSFWorkbook ( inputStream) ;
} else {
wb = new HSSFWorkbook ( inputStream) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
throw new Exception ( "importCapexDataOfExcel_解析的文件格式有误" ) ;
}
return wb;
}
public String getValue2Format ( Cell cell) {
String value = "" ;
if ( null == cell) {
return value;
}
switch ( cell. getCellType ( ) ) {
case NUMERIC :
if ( DateUtil . isCellDateFormatted ( cell) ) {
Date date = DateUtil . getJavaDate ( cell. getNumericCellValue ( ) ) ;
SimpleDateFormat format = new SimpleDateFormat ( ) ;
value = format. format ( date) ;
} else {
BigDecimal big = new BigDecimal ( cell. getNumericCellValue ( ) ) ;
value = big. toString ( ) ;
}
break ;
case STRING :
value = cell. getStringCellValue ( ) . trim ( ) ;
break ;
case FORMULA :
value = String . valueOf ( cell. getNumericCellValue ( ) ) ;
if ( value. equals ( "NaN" ) ) {
value = cell. getStringCellValue ( ) ;
}
break ;
case BOOLEAN :
value = " " + cell. getBooleanCellValue ( ) ;
break ;
default :
value = cell. getStringCellValue ( ) . trim ( ) ;
}
if ( "null" . endsWith ( value. trim ( ) ) ) {
value = "" ;
}
return value;
}
@Override
@Transactional ( propagation = Propagation . REQUIRED , rollbackFor = Exception . class )
public HttpServletResponse exportCapexDataTemplate ( HttpServletRequest request, HttpServletResponse response, String templateId) {
InputStream inputStream = null ;
try {
new MsBusinessMainApi ( ) . getLicense ( ) ;
String templatePath = OUTLAYRECORD_TEMPLATE_DOWN_PATH + OUTLAYRECORD_FORM + XLS ;
String templateName = OUTLAYRECORD_NAME + XLS ;
ClassPathResource classPathResource = new ClassPathResource ( templatePath) ;
inputStream = classPathResource. getInputStream ( ) ;
com. aspose. cells. Workbook workbook = new com. aspose. cells. Workbook( inputStream) ;
inputStream. close ( ) ;
response. setHeader ( "content-Type" , "application/vnd.ms-excel " ) ;
response. setHeader ( "Content-Disposition" , "attachment;filename=" + new String ( templateName. getBytes ( "GBK" ) , "iso8859-1" ) ) ;
workbook. save ( response. getOutputStream ( ) , workbook. getFileFormat ( ) ) ;
} catch ( IOException e) {
LOG . error ( "获取不到文件流" , e) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
if ( inputStream != null ) {
try {
inputStream. close ( ) ;
} catch ( IOException e) {
e. printStackTrace ( ) ;
}
}
}
return null ;
}