如何把Excel表格内容输入vfp数据库

weixin_40962593 2017-11-06 10:23:48
我想把Excel表格内容输入到FoxPro数据库，但是Excel表只能做成自由表，没法加入数据库。请大神帮忙
...全文
595 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
fojiao8745 2017-11-27
  • 打赏
  • 举报
 回复
前段时间刚做了一个这个功能，思路是生成excel ole对象，然后结合远程视图、ODBC、SQL Server，以下贴出main.prg： 
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.
十豆三 2017-11-08
  • 打赏
  • 举报
 回复
个人感觉自由表比数据库中表好用，或要么不用VFP的表，直接SQL Server
lygcw9602 2017-11-07
  • 打赏
  • 举报
 回复
生成自由表後，打開數據庫，添加你生成的表，假如表名為：TEST.DBF OPEN DATABASE 數據庫名 ADD TABLE TEST
Foxpro中如何实现对EXCEL表格的操作
Foxpro中如何实现对EXCEL表格的操作
VFP和Excel都可以用来进行处理数据库表格.doc
VFP和Excel都可以用来进行处理数据库表格.doc
信息技术调研数据Excel文件与远程VFP数据库在低网络开销环境下的相互调用 (2012年)
本文利用VFP远程/离线视图提出了针对远程VFP数据库与本地Excel数据相互调用时节省网络负载的一种方法,有效地实现了Excel表格数据通过离线视图输入到远程后台数据库以及从远程数据库导出Excel数据表格等功能。
VFP全面控制EXCEL收藏
VFP和Excel都可以用来进行处理数据库表格，如果巧妙地将二者的优点结合起来，将会大大方便我们的工作
vfp示例表格
请大家看看，用什么算法编排考号。需求是这样的，如1年级有8个班，那么考号编排规则是：第1个班的第一个学生考号为1号，第2个班的第一个学生考号为2号，以此类推。 我现在的办法是导出到excel里面用公式算出考号后，在保存为dbf。确点是 数据库结构 要丢失。 求解决vfp 具体算法。每个班的学生还可能不一样。
VFP

2,722

社区成员

29,108

社区内容

发帖
与我相关
我的任务
社区描述
VFP，是Microsoft公司推出的数据库开发软件，用它来开发数据库，既简单又方便。
社区管理员
  • VFP社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章