📄 excel_sample.prg
字号:
SET talk off
local ckzs,cmsl,lszs,hbh1,hbh2,hbh3,hbh4,hbh5,hbh6,hbh7,hbh8,hbh9,hbL1,zdkd,i,add_hs,guest_name_temp
DECLARE INTEGER GetPrivateProfileString IN Win32API AS GetPrivStr ;
String cSection, String cKey, String cDefault, String @cBuffer, ;
Integer nBufferSize, String cINIFile
LOCAL lcSection, lcKey, lcBuffer, lcFileName, lcpathmain, lcpathtemp
PUBLIC UserName,UserPW
lcFileName = "rksvr.ini"
lcSection = "syspath"
lcKey = "系统"
lcBuffer = SPACE(251)
GetPrivStr(lcSection, lcKey, "", @lcBuffer, LEN(lcBuffer), CURDIR()+lcFileName)
lcpathmain=subst(allt(lcBuffer),1,len(allt(lcBuffer))-1)
lcKey = "临时"
lcBuffer = SPACE(251)
GetPrivStr(lcSection, lcKey, "", @lcBuffer, LEN(lcBuffer), CURDIR()+lcFileName)
lcpathtemp=subst(allt(lcBuffer),1,len(allt(lcBuffer))-1)
renk_ole = CreateObject('Excel.application')
renk_ole.workbooks.open(lcpathmain+"chs.xls")
renk_ole.WorkSheets("ac").Activate
if .not.used('exceltemp')
sele 0
use exceltemp ALIAS exceltemp
endif
sele exceltemp
sum occur_out to ckzs
if file('test.xls')
erase test.xls
endif
if file('renk_excel_temp.dbf')
if used('renk_excel_temp')
sele renk_excel_temp
use
endif
erase renk_excel_temp.dbf
endif
select DISTINCT measure FROM exceltemp into dbf renk_excel_temp noconsole
sele renk_excel_temp
cmsl=recc()
use
lszs=11+cmsl*2
dime lk(lszs)
hbh1='A1:'+chr(64+lszs)+'1'
hbh2='A2:'+chr(64+lszs)+'2'
hbh5='A5:'+chr(64+lszs)+'5'
hbh8='A8:'+chr(64+lszs)+'8'
hbL1=chr(64+6+cmsl)+'6:'+chr(64+6+cmsl)+'7'
hbh3='A3:'+chr(64+lszs)+'3'
hbh4='A4:'+chr(64+lszs)+'4'
hbh6='A6:'+chr(64+lszs)+'6'
hbh7='A7:'+chr(64+lszs)+'7'
hbh9='A9:'+chr(64+lszs)+'9'
with renk_ole
.Range(hbh1).select
.Selection.merge
.Range(hbh2).select
.Selection.merge
.Range(hbh5).select
.Selection.merge
.Range(hbh8).select
.Selection.merge
.Range(hbL1).select
.Selection.merge
.Range(hbh1).HorizontalAlignment = 3
.Range(hbh2).HorizontalAlignment = 3
.Range(hbh3).HorizontalAlignment = 3
.Range(hbh4).HorizontalAlignment = 2
.Range(hbh6).HorizontalAlignment = 3
.Range(hbh7).HorizontalAlignment = 3
.Range(hbh9).HorizontalAlignment = 2
ENDwith
erase renk_excel_temp.dbf
SELECT LEN(ALLTRIM(str(MAX(pack_no)))) as kd FROM exceltemp into dbf renk_excel_temp noconsole
sele renk_excel_temp
go top
zdkd=kd
use
lk(1) = iif(zdkd<5,5,zdkd)
lk(7+cmsl) = iif(zdkd<5,5,zdkd)
Renk_ole.cells(6,1).value = '袋号'
Renk_ole.cells(6,7+cmsl).value = '袋号'
erase renk_excel_temp.dbf
SELECT LEN(ALLTRIM(MAX(batch))) as kd FROM exceltemp into dbf renk_excel_temp noconsole
sele renk_excel_temp
go top
zdkd=kd
use
lk(2) = iif(zdkd<5,5,zdkd)
lk(8+cmsl) = iif(zdkd<5,5,zdkd)
Renk_ole.cells(6,2).value = '批号'
Renk_ole.cells(6,8+cmsl).value = '批号'
zdkd=len(allt(str(ckzs)))
lk(4+cmsl) = iif(zdkd<8,8,zdkd)
lk(lszs-1) = iif(zdkd<8,8,zdkd)
lk(5+cmsl) = iif(zdkd<8,8,zdkd)
lk(lszs) = iif(zdkd<8,8,zdkd)
Renk_ole.cells(6,4+cmsl).value = '件数/箱'
Renk_ole.cells(6,lszs-1).value = '件数/箱'
Renk_ole.cells(6,5+cmsl).value = '合计'
Renk_ole.cells(6,lszs).value = '合计'
erase renk_excel_temp.dbf
SELECT LEN(ALLTRIM(str(MAX(occur_out)))) as kd FROM exceltemp into dbf renk_excel_temp noconsole
sele renk_excel_temp
go top
zdkd=kd
use
for i = 1 to cmsl
lk(3+i) = iif(zdkd<5,5,zdkd)
lk(9+cmsl+i) = iif(zdkd<5,5,zdkd)
endfor
erase renk_excel_temp.dbf
if .not. used('tincts')
sele 0
use tincts ALIAS tincts
endif
SELECT MAX(LEN(ALLTRIM(tinct_name))) as kd from tincts into dbf renk_excel_temp noconsole
sele renk_excel_temp
go top
zdkd=kd
use
erase renk_excel_temp.dbf
sele tincts
use
lk(3) = iif(zdkd<5,5,zdkd)
lk(9+cmsl) = iif(zdkd<5,5,zdkd)
Renk_ole.cells(6,3).value = '颜色'
Renk_ole.cells(6,9+cmsl).value = '颜色'
for i=1 to 5+cmsl
renk_ole.ActiveSheet.Columns(chr(64+i)).ColumnWidth = lk(i)
endfor
renk_ole.ActiveSheet.Columns(chr(64+6+cmsl)).ColumnWidth = 1
for i=7+cmsl to lszs
renk_ole.ActiveSheet.Columns(chr(64+i)).ColumnWidth = lk(i)
endfor
local hbpage,hbpagebefore,hbdate,hbguest,hbtraffic,hbhandle,hbguest_qz,qz_begin
hbpagebefore = 'A3:'+chr(64+lszs-3)+'3'
hbpage = chr(64+lszs-2)+'3:'+chr(64+lszs)+'3'
hbguest = 'A4:D4'
hbtraffic = 'E4:'+chr(64+lszs-3)+'4'
hbdate = chr(64+lszs-2)+'4:'+chr(64+lszs)+'4'
if cmsl < 2
hbhandle = 'A9:E9'
hbguest_qz = 'F9:'+'M9'
qz_begin = 6
else
hbhandle = 'A9:'+chr(64+lszs-7)+'9'
hbguest_qz = chr(64+lszs-6)+'9:'+chr(64+lszs)+'9'
qz_begin = lszs-6
endif
sele exceltemp
go top
if .not. used('guest_info')
sele 0
use guest_info ALIAS guest_info
ENDIF
SELECT guest_info
LOCATE FOR ALLTRIM(exceltemp.guest_id) = ALLTRIM(guest_id)
guest_name_temp = ALLTRIM(guest_name)
sele exceltemp
with renk_ole
.Range(hbpagebefore).select
.Selection.merge
.Range(hbpage).select
.Selection.merge
.Range(hbguest).select
.Selection.merge
.Range(hbtraffic).select
.Selection.merge
.Range(hbdate).select
.Selection.merge
.Range(hbhandle).select
.Selection.merge
.Range(hbguest_qz).select
.Selection.merge
.cells(3,lszs-2).value = '№：' + LTRI(STR(page_no))
.cells(4,1).value = '客户：'+ guest_name_temp
.cells(4,5).value = '运输方式：' + ALLT(traffic)
.cells(4,lszs-2).value = '日期：'+LEFT(DTOS(DATE()),4)+'年'+SUBSTR(DTOS(DATE()),5,2)+'月'+RIGHT(DTOS(Date()),2)+'日'
.cells(9,1).value = '发货人：' + ALLT(handle)
.cells(9,qz_begin).value = '上列货品如数验收无误，收货人签章：'
endwith
IF cmsl > 1
local hbcm1,hbcm2
hbcm1 = chr(64+4)+'7:'+chr(64+3+cmsl)+'7'
hbcm2 = chr(64+10+cmsl)+'7:'+chr(64+9+cmsl*2)+'7'
ENDIF
do while .t.
do case
case recc()>10
if int(recc()/2) = recc()/2
add_hs = recc()/2 -1
else
add_hs = int(recc()+1)/2 -1
endif
case recc()>1
add_hs = recc() - 1
otherwise
exit
endcase
for i = 1 to add_hs
IF cmsl > 1
with renk_ole
.Range(hbcm1).select
.Selection.merge
.Range(hbcm2).select
.Selection.merge
endwith
ENDIF
Renk_ole.ActiveSheet.Rows(7).Insert
IF cmsl > 1
with renk_ole
.Range(hbcm1).select
.Selection.merge
.Range(hbcm2).select
.Selection.merge
endwith
ENDIF
endfor
exit
enddo
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -