LOCAL fsheet,fname,oExcel,fname1
cDefPath = ADDBS(JUSTPATH(SYS(16)))
SET DEFAULT TO (cDefPath)
sys(3099,70)
SET SAFETY OFF
xlsFile = cDefPath + "维修发料统计.xls"
WITH CREATEOBJECT("Excel.Application")
.DisplayAlerts = 0
.WorkBooks.Open(xlsFile)
nRow = .CountA(.Range("A:A")) - 2
nCol = .CountA(.Range("4:4"))
arr
= .Cells(5,1).Resize(nRow,nCol).Value
.WorkBooks.Close
.Quit
ENDWITH
CREATE CURSOR tt (序号 I,经销商简称 C(10),工单号 c(12),零部件代码 C(18),零部件名称 C(30),仓库代码 C(10),库位代码 C(12),零部件数量 N(6,2),销售单价 N(6,2),;
零部件销售金额 N(6,2),零部件成本金额 N(6,2),车牌号 C(10),车系 C(10),领料人 C(10),维修类型 C(10))
INSERT INTO tt FROM ARRAY arr
SELECT * FROM tt INTO cursor 细 READWRITE
SELECT 细
REPLACE 仓库代码 WITH Iif("TW-" $ 零部件代码, '配件仓库B', '配件仓库A') ALL
REPLACE 工单号 WITH righ(工单号,10) all
SELECT
序号 ,工单号,零部件代码,零部件名称,销售单价,SUM(零部件数量) as
数量,领料人,仓库代码,车牌号,维修类型 ;
FROM 细
GROUP BY 零部件代码 INTO cursor 明细1
BROWSE
select * from 明细1 where 数量 > 0 INTO cursor 明细
brow
SET DEFAULT TO d:\xls
lc_filename=cDefPath+'模版.xls'
IF !FILE(lc_filename)
MESSAGEBOX('不存在模版文件')
RETURN
ENDIF
SELECT DISTINCT 工单号 FROM 明细 INTO CURSOR RKD
eole=createobject("excel.application")
eole.workbooks.open(lc_filename,.F.,.F.)
m=20
SELECT RKD
SCAN
FOR q=2 TO m&&保证清空前面工单的记录
eole.cells(q,1).value=''
eole.cells(q,2).value=''
eole.cells(q,3).value=''
eole.cells(q,4).value=''
eole.cells(q,5).value=''
eole.cells(q,6).value=''
eole.cells(q,7).value=''
eole.cells(q,8).value=''
eole.cells(q,9).value=''
eole.cells(q,10).value=''
eole.cells(q,11).value=''
eole.cells(q,12).value=''
eole.cells(q,13).value=''
eole.cells(q,14).value=''
ENDFOR
SELECT 序号 as 行,车牌号 as T,零部件代码 AS 项目,零部件名称 as 说明,序号 AS 套餐,序号 AS W,数量,;
销售单价 as 含税单价,领料人 as 折扣,序号 as 总计,维修类型
as V,序号 as P,工单号 AS I,仓库代码 as 仓库名称 ;
FROM 明细 ;
WHERE 工单号=RKD.工单号 ORDER BY I ;
INTO CURSOR TEMP
z='d:\xls\'+ALLTRIM(temp.I)+ALLTRIM(temp.T)+(temp.V)+ALLTRIM(temp.折扣)+'.xls'
IF FILE(z)
DELETE FILE (z)
ENDIF
**
copy to (z)
type xl5
SELECT temp
m=1
SCAN
m=m+1
eole.cells(m,1).value=行
eole.cells(m,2).value=T
eole.cells(m,3).value=项目
eole.cells(m,4).value=说明
eole.cells(m,5).value=套餐
eole.cells(m,6).value=W
eole.cells(m,7).value=数量
eole.cells(m,8).value=含税单价
eole.cells(m,9).value=折扣
eole.cells(m,10).value=总计
eole.cells(m,11).value=V
eole.cells(m,12).value=P
eole.cells(m,13).value=I
eole.cells(m,14).value=仓库名称
ENDSCAN
eole.ActiveWorkbook.Saved=.t.
eole.ActiveWorkbook.SaveAs(z)
SELECT RKD
ENDSCAN
eole.quit
RELEASE eole
MESSAGEBOX('处理完成)
以上代码是拼的实现的有点乱,不能处理按编码对数量进行汇总
[此贴子已经被作者于2022-1-4 02:51编辑过]