2,727
社区成员
发帖
与我相关
我的任务
分享
LOCAL lcExcelFile,lcTryTimes,lcEndTime
THISFORM.COMMAND2.ENABLED = .F.
THISFORM.text1.ENABLED = .F.
lcTryTimes = 0
IF FILE('D:\RC_Upload\Upload\RC TEST.xls')
THISFORM.error_msg('有异常记录没有处理!!!'+CHR(13)+'请联系解决!!!')
RETURN
ENDIF
IF ALLTRIM(THISFORM.label4.CAPTION) == "Welcome!请先输入延时时间,再点击启动开始!"
THISFORM.label4.CAPTION = ""
ENDIF
IF THISFORM.text1.VALUE == 0 AND EMPTY(THISFORM.label4.CAPTION) AND THISFORM.timer = .F.
THISFORM.error_msg("请输入扫描间隔时间以开始!")
RETURN
ENDIF
IF THISFORM.text1.VALUE < 30 AND EMPTY(THISFORM.label4.CAPTION) AND THISFORM.timer = .F.
THISFORM.error_msg("扫描间隔时间过短!至少为30秒!")
RETURN
ENDIF
**启用Timer
IF THISFORM.timer1.ENABLED = .F. AND THISFORM.text1.VALUE > 0
THISFORM.timer1.ENABLED = .T.
RETURN
ENDIF
THISFORM.label4.CAPTION = ""
**文件操作处理
COPY FILE "D:\RC_Upload\RC TEST.xls" TO "D:\RC_Upload\Upload\RC TEST.xls"&&转移目录
lcExcelFile = "D:\RC_Upload\Upload\RC TEST.xls"
**处理结束
**开始!!
T1 = SECONDS() &&开始时间
OPEN DATABASE mesdb
IF !USED('mgevmesupload')
USE mgevmesupload IN 0 NODATA
ENDIF
**准备Excel对象以及Bar
myExcel=CREATEOBJECT('excel.application') &&创建一个对象
myExcel.VISIBLE=.F.&&不可见
oExcel=myExcel.workbooks.OPEN(lcExcelFile) &&打开指定文件
o_SheetName=myExcel.APPLICATION.ActiveSheet.NAME &&获取当前激活工作表的名称
UsedRange =oExcel.worksheets(o_SheetName).UsedRange &&返回工作表中可使用的区域,UsedRange表的属性
o_rows=UsedRange.ROWS.COUNT &&汇总行
o_cols=UsedRange.COLUMNS.COUNT &&汇总列
IF o_rows = thisform.record &&还没有新纪录产生
THISFORM.uploadok
MESSAGEBOX("暂无新数据产生,程序将自动继续执行!!",0+48,"提示",2000)
DELETE FILE "D:\RC_Upload\Upload\RC TEST.xls"
THISFORM.text1.VALUE = THISFORM.delay_value
THISFORM.timer1.ENABLED = .T.
RETURN
ELSE
lcBarlen = o_rows - thisform.record + 1
ENDIF
IF lcBarlen < 15
lcBarUnit = 1 &&Bar的Unit大小
ELSE
lcBarUnit = ROUND((lcBarlen-1)/27,0) &&Bar的Unit大小
ENDIF
**准备结束
**行数限制检测结束
**统计各数据的所在列
upTime = 1000
upId = 1000
upOk = 1000
upType = 1000
FOR j = 1 TO o_cols
IF myExcel.cells(1,j).VALUE == "mcgs_time"
upTime = j
ENDIF
IF myExcel.cells(1,j).VALUE == "id"
upId = j
ENDIF
IF myExcel.cells(1,j).VALUE == "total_qualified"
upOk = j
ENDIF
IF myExcel.cells(1,j).VALUE == "type"
upType = j
ENDIF
IF myExcel.cells(1,j).VALUE == "light_off_current"
uplfc = j
ENDIF
IF myExcel.cells(1,j).VALUE == "light_on_current"
uploc = j
ENDIF
IF myExcel.cells(1,j).VALUE == "charge_current"
upcc = j
ENDIF
IF myExcel.cells(1,j).VALUE == "maximum_current"
upmc = j
ENDIF
IF myExcel.cells(1,j).VALUE == "cut_off_voltage"
upcov = j
ENDIF
IF myExcel.cells(1,j).VALUE == "initial_full_charge_capacity"
upifcc = j
ENDIF
IF myExcel.cells(1,j).VALUE == "full_charge_capacity"
upfcc = j
ENDIF
IF myExcel.cells(1,j).VALUE == "percentage_of_capacity"
uppoc = j
ENDIF
ENDFOR
**统计结束
**判断数据所在列的名称正确性
DO CASE
CASE upTime = 1000
THISFORM.error_msg('没有检测到"日期"数据!!'+CHR(13)+'请确认"日期"第一行是否为"mcgs_time"')
myExcel.workbooks.CLOSE &&关闭工作区
myExcel.QUIT &&关闭excel
DELETE FILE "D:\RC_Upload\Upload\RC TEST.xls"
RETURN
CASE upId = 1000
THISFORM.error_msg('没有检测到"ID"数据!!'+CHR(13)+'请确认"ID"号码第一行是否为"id"')
myExcel.workbooks.CLOSE &&关闭工作区
myExcel.QUIT &&关闭excel
DELETE FILE "D:\RC_Upload\Upload\RC TEST.xls"
RETURN
CASE upOk = 1000
THISFORM.error_msg('没有检测到"合格"数据!!'+CHR(13)+'请确认"合格"第一行是否为"total_qualified"')
myExcel.workbooks.CLOSE &&关闭工作区
myExcel.QUIT &&关闭excel
DELETE FILE "D:\RC_Upload\Upload\RC TEST.xls"
RETURN
CASE upType = 1000
THISFORM.error_msg('没有检测到"类型"数据!!'+CHR(13)+'请确认"类型"第一行是否为"type"')
myExcel.workbooks.CLOSE &&关闭工作区
myExcel.QUIT &&关闭excel
DELETE FILE "D:\RC_Upload\Upload\RC TEST.xls"
RETURN
OTHERWISE
ENDCASE
**判断结束
**综合检测
**判断ID长度
WITH THISFORM.label4
.FONTBOLD = .T.
.CAPTION = "正在进行ID长度检测...请稍等..."
.ALIGNMENT = 2
.FORECOLOR = RGB(0,0,255)
ENDWITH
FOR i=thisform.record+1 TO o_rows
m1=myExcel.cells(i,upId).VALUE &&判断ID号
IF VARTYPE(m1) = "N" AND m1 = 0
m1 = "0"
ENDIF
IF LEN(ALLTRIM(m1)) <> 16 AND "0" <> ALLTRIM(m1) AND !EMPTY(m1)
THISFORM.error_msg('"ID"号码长度不符合规范!!'+CHR(13)+'请检查第'+ALLTRIM(STR(i))+'行第'+ALLTRIM(STR(upId))+'列数据')
THISFORM.uploaderror
DELETE FILE "D:\RC_Upload\Upload\RC TEST.xls"
RETURN
ENDIF
ENDFOR
THISFORM.label4.CAPTION = "ID号码长度检测OK...即将开始检测合格数据..."
INKEY(0.7,"H")
**ID判断结束
**判断合格数据
THISFORM.label4.CAPTION = "正在进行合格数据检测...请稍等..."
FOR i=thisform.record+1 TO o_rows
m1=myExcel.cells(i,upOk).VALUE &&判断是否OK
IF m1<>0 AND m1<>1
THISFORM.error_msg('"合格"数据不符合规范!!'+CHR(13)+'请检查第'+ALLTRIM(STR(i))+'行第'+ALLTRIM(STR(upOk))+'列数据')
THISFORM.uploaderror
DELETE FILE "D:\RC_Upload\Upload\RC TEST.xls"
RETURN
ENDIF
ENDFOR
THISFORM.label4.CAPTION = "合格数据检测OK...开始检测类型数据..."
INKEY(0.7,"H")
**合格判断结束
**判断类型数据
THISFORM.label4.CAPTION = "正在进行类型数据检测...请稍等..."
FOR i=thisform.record+1 TO o_rows
m1=myExcel.cells(i,upType).VALUE &&判断是否OK
IF LOWER(m1)<>"dc" AND LOWER(m1)<>"xsq" AND LOWER(m1)<>"qdq" AND LOWER(m1)<>"cdq"
THISFORM.error_msg('"类型"数据不符合规范!!'+CHR(13)+'请检查第'+ALLTRIM(STR(i))+'行第'+ALLTRIM(STR(upType))+'列数据')
THISFORM.uploaderror
DELETE FILE "D:\RC_Upload\Upload\RC TEST.xls"
RETURN
ENDIF
ENDFOR
THISFORM.label4.CAPTION = "类型数据检测OK...即将开始上传..."
INKEY(0.7,"H")
WITH THISFORM.label4
.FONTBOLD = .F.
.CAPTION = ""
.ALIGNMENT = 0
.FORECOLOR = RGB(0,255,128)
ENDWITH
**类型判断结束
**综合检测结束
**开始上传
SELECT mgevmesupload
FOR i=thisform.record+1 TO o_rows
m1=myExcel.cells(i,upTime).VALUE &&日期时间
m2=myExcel.cells(i,upId).VALUE &&ID号
m3=myExcel.cells(i,upOk).VALUE &&OK
m4=myExcel.cells(i,upType).VALUE &&Type
m5=myExcel.cells(i,uplfc).VALUE
m6=myExcel.cells(i,uploc).VALUE
m7=myExcel.cells(i,upcc).VALUE
m8=myExcel.cells(i,upmc).VALUE
m9=myExcel.cells(i,upcov).VALUE
m10=myExcel.cells(i,upifcc).VALUE
m11=myExcel.cells(i,upfcc).VALUE
m12=myExcel.cells(i,uppoc).VALUE
m.gc_id = m2
IF VARTYPE(m2) = "N" AND m2 = 0
m2 = "0"
ENDIF
IF "0" <> ALLTRIM(m2) AND !EMPTY(m2)
REQUERY('mgevmesupload')
IF RECCOUNT() <> 0
DO CASE
CASE LOWER(m4) == "dc"
REPLACE up_time WITH TTOC(DATETIME()),date_time WITH TTOC(m1),id_data WITH m2,ok_data WITH m3,type_data WITH m4;
,ifcc WITH m10,fcc WITH m11,poc WITH m12
CASE LOWER(m4) == "xsq"
REPLACE up_time WITH TTOC(DATETIME()),date_time WITH TTOC(m1),id_data WITH m2,ok_data WITH m3,type_data WITH m4;
,lfc WITH m5,loc WITH m6,cc WITH m7
CASE LOWER(m4) == "qdq"
REPLACE up_time WITH TTOC(DATETIME()),date_time WITH TTOC(m1),id_data WITH m2,ok_data WITH m3,type_data WITH m4
CASE LOWER(m4) == "cdq"
REPLACE up_time WITH TTOC(DATETIME()),date_time WITH TTOC(m1),id_data WITH m2,ok_data WITH m3,type_data WITH m4;
,mc WITH m8,cov WITH m9
OTHERWISE
ENDCASE
THISFORM.label7.CAPTION = ALLTRIM(m2)
THISFORM.label9.CAPTION = ALLTRIM(STR(o_rows-i))
IF TABLEUPDATE(.T.,.T.) = .F.
TABLEREVERT(.T.)
INKEY(3,"H")&&延时1s,排除错误
i = i - 1
IF lcTryTimes = 11
THISFORM.error_msg(ALLTRIM(m2)+'更新失败!!!'+CHR(13)+'请截图此界面联系解决!!!')
THISFORM.uploaderror
RETURN
ENDIF
lcTryTimes = lcTryTimes + 1
ELSE
IF lcTryTimes <> 0
lcTryTimes = 0
ENDIF
ENDIF
IF MOD(i-1,lcBarUnit) = 0 &&Bar Start
THISFORM.label4.CAPTION = THISFORM.label4.CAPTION + CHR(43136)
ENDIF
ELSE
APPEND BLANK
DO CASE
CASE LOWER(m4) == "dc"
REPLACE up_time WITH TTOC(DATETIME()),date_time WITH TTOC(m1),id_data WITH m2,ok_data WITH m3,type_data WITH m4;
,ifcc WITH m10,fcc WITH m11,poc WITH m12
CASE LOWER(m4) == "xsq"
REPLACE up_time WITH TTOC(DATETIME()),date_time WITH TTOC(m1),id_data WITH m2,ok_data WITH m3,type_data WITH m4;
,lfc WITH m5,loc WITH m6,cc WITH m7
CASE LOWER(m4) == "qdq"
REPLACE up_time WITH TTOC(DATETIME()),date_time WITH TTOC(m1),id_data WITH m2,ok_data WITH m3,type_data WITH m4
CASE LOWER(m4) == "cdq"
REPLACE up_time WITH TTOC(DATETIME()),date_time WITH TTOC(m1),id_data WITH m2,ok_data WITH m3,type_data WITH m4;
,mc WITH m8,cov WITH m9
OTHERWISE
ENDCASE
THISFORM.label7.CAPTION = ALLTRIM(m2)
THISFORM.label9.CAPTION = ALLTRIM(STR(o_rows-i))
IF TABLEUPDATE(.T.,.T.) = .F.
TABLEREVERT(.T.)
INKEY(3,"H")&&延时1s,排除错误
i = i - 1
IF lcTryTimes = 11
THISFORM.error_msg(ALLTRIM(m2)+'新增失败!!!'+CHR(13)+'请截图此界面,联系解决!!!')
THISFORM.uploaderror
RETURN
ENDIF
lcTryTimes = lcTryTimes + 1
ELSE
IF lcTryTimes <> 0
lcTryTimes = 0
ENDIF
ENDIF
IF MOD(i-1,lcBarUnit) = 0 &&Bar Start
THISFORM.label4.CAPTION = THISFORM.label4.CAPTION + CHR(43136)
ENDIF
ENDIF
ENDIF
ENDFOR
IF lcBarlen < 27
FOR i=lcBarlen TO 27
THISFORM.label4.CAPTION = THISFORM.label4.CAPTION + CHR(43136) &&BarEnd
ENDFOR
ELSE
THISFORM.label4.CAPTION = THISFORM.label4.CAPTION + CHR(43136) &&BarEnd
ENDIF
**上传结束
T2 = SECONDS() &&结束时间
T3 = T2-T1
MESSAGEBOX('数据录入已完成!!'+CHR(13)+'用时'+ALLTRIM(STR(T3,10,3))+'秒',0+48,"提示",2000)
THISFORM.uploadok
thisform.record = o_rows
thisform.filesize = FSIZE("D:\RC_Upload\Upload\RC TEST.xls")
**备份历史记录
lcEndTime = STRTRAN(DTOS(DATE())+TIME(),':','') + '.xls'
COPY FILE "D:\RC_Upload\Upload\RC TEST.xls" TO "D:\RC_Upload\History\&lcEndTime"&&备份历史记录
DELETE FILE "D:\RC_Upload\Upload\RC TEST.xls"
**进入等待
THISFORM.text1.VALUE = THISFORM.delay_value
THISFORM.timer1.ENABLED = .T.