回复 19楼 吹水佬
谢谢!
回复 20楼 xcy524100
感谢提示在测试的时候是没有EXCEL表格被打开,有时甚至是重新启动计算机后做的测试
另外,出现问题都是在粘贴的时候,感觉这个粘贴功能很脆弱,一会行一会不行的
CREATE CURSOR 测试表 (编号 C(10), 入库单号 C(7), 入库日期 D, 物料名称 C(20), 数量 I,; 单价 N(14,2), 单位 C(8), 规格 C(30), 付款 L, 日期时间 T, 备注 M) FOR i=1 To 10 INSERT INTO 测试表 VALUES (PADL(i,10,"0"), 'C'+PADL(i,6,'0'), {^2018-03-01}+INT(RAND()*10),; '物料'+PADL(i,3,'0'), INT(RAND()*1000), RAND()*10, '个', '"测试双引号AB"CD"',; IIF(i%2=0,.t.,.f.), DATETIME(), '"备注_"'+TRANSFORM(i)+'"'+0h0D0A+'_'+TRANSFORM(i)+'"'+0h0D0A) ENDFOR AFIELDS(aFieldInfo) FOR i=1 TO ALEN(aFieldInfo,1) aFieldInfo[i,8] = ICASE(; && 数据格式 aFieldInfo[i,2]=="I", '##0;[=0]""',; && 整数格式 INLIST(aFieldInfo[i,2],"B","N","F"), '#,##0.'+REPLICATE("0",aFieldInfo[i,4])+';[=0]""',; && 小数格式 INLIST(aFieldInfo[i,2],"C","V","W","M"), '@',; && 文本格式 aFieldInfo[i,2]=="D", 'yyyy-m-d',; && 日期格式 aFieldInfo[i,2]=="T", 'yyyy-m-d hh:mm:ss',; && 日期时间格式 'G/通用格式') ENDFOR SELECT * FROM 测试表 INTO ARRAY arr sc = CREATEOBJECT("ScriptControl") sc.Language = "VBScript" TEXT TO vbsCode TEXTMERGE NOSHOW PRETEXT 7 FUNCTION fun(vfpArray, aFieldInfo) dim oExcel,oRange, nRows, nCols, nCol set oExcel = CREATEOBJECT("Excel.Application") oExcel.Workbooks.Add nRows = UBound(vfpArray,1) nCols = UBound(vfpArray,2) for nCol=1 to nCols oExcel.Range(oExcel.Cells(1,nCol),oExcel.Cells(nRows,nCol)).Select oExcel.Selection.NumberFormatLocal = aFieldInfo(nCol,8) next set oRange = oExcel.Range(oExcel.Cells(1,1),oExcel.Cells(nRows,nCols)) oRange.Value = vfpArray oRange.Columns.AutoFit oExcel.Visible = 1 END FUNCTION ENDTEXT sc.AddCode(vbsCode) sc.Run("fun", @arr, @aFieldInfo)
CREATE CURSOR 测试表 (编号 C(10), 入库单号 C(7), 入库日期 D, 物料名称 C(20), 数量 I,; 单价 N(14,2), 单位 C(8), 规格 C(30), 付款 L, 日期时间 T, 备注 M) FOR i=1 To 100 INSERT INTO 测试表 VALUES (PADL(i,10,"0"), 'C'+PADL(i,6,'0'), {^2018-03-01}+INT(RAND()*10),; '物料'+PADL(i,3,'0'), INT(RAND()*1000), RAND()*10, '个', '"测试双引号AB"CD"',; IIF(i%2=0,.t.,.f.), DATETIME(), '"备注_"'+TRANSFORM(i)+'"'+0h0D0A+'_'+TRANSFORM(i)+'"'+0h0D0A) ENDFOR AFIELDS(aFieldInfo) FOR i=1 TO ALEN(aFieldInfo,1) aFieldInfo[i,8] = ICASE(; && 数据格式 aFieldInfo[i,2]=="I", '##0;[=0]""',; && 整数格式 INLIST(aFieldInfo[i,2],"B","N","F"), '#,##0.'+REPLICATE("0",aFieldInfo[i,4])+';[=0]""',; && 小数格式 INLIST(aFieldInfo[i,2],"C","V","W","M"), '@',; && 文本格式 aFieldInfo[i,2]=="D", 'yyyy-m-d',; && 日期格式 aFieldInfo[i,2]=="T", 'yyyy-m-d hh:mm:ss',; && 日期时间格式 'G/通用格式') ENDFOR sc = CREATEOBJECT("ScriptControl") sc.Language = "VBScript" TEXT TO vbsCode TEXTMERGE NOSHOW PRETEXT 7 dim oExcel,oRange, nRows, nCols, nCol set oExcel = CREATEOBJECT("Excel.Application") oExcel.Workbooks.Add function SetFormat(aFieldInfo, nRows) for nCol=1 to UBound(aFieldInfo,1) oExcel.Range(oExcel.Cells(1,nCol),oExcel.Cells(nRows,nCol)).Select oExcel.Selection.NumberFormatLocal = aFieldInfo(nCol,8) next end function function Append(vfpArray, nRow) nRows = UBound(vfpArray,1) nCols = UBound(vfpArray,2) oExcel.Range(oExcel.Cells(nRow,1),oExcel.Cells(nRow+nRows-1,nCols)).Value = vfpArray end function function Show(nRows, nCols) oExcel.Range(oExcel.Cells(1,1),oExcel.Cells(nRows,nCols)).Columns.AutoFit oExcel.Visible = 1 end function ENDTEXT sc.AddCode(vbsCode) sc.Run("SetFormat", @aFieldInfo, RECCOUNT("测试表")) nStep = 10 FOR i=1 TO RECCOUNT("测试表") STEP nStep SELECT * FROM 测试表 WHERE BETWEEN(RECNO(),i,i+nStep-1) INTO ARRAY arr sc.Run("Append", @arr, i) ENDFOR sc.Run("Show",RECCOUNT("测试表"),FCOUNT("测试表"))