转EXCEL是对是负数标记为红色,该怎么处理
转EXCEL是对是负数标记为红色
SET DATE YMD
SET CENTURY ON
SET SAFETY off
USE tjbbzz IN 0
INDEX on 订单号+款号 TAG sy
TOTAL ON 订单号+款号 TO temp
SELECT 20
USE temp
ALTER TABLE temp ADD 业务员 c(10)
ALTER TABLE temp ADD 交货日期 c(10)
ALTER TABLE temp ADD 计划数量 n(10)
UPDATE temp SET 业务员=tjddbzzbf.业务员,交货日期=tjddbzzbf.交货日期,计划数量=tjddbzzbf.计划数量 from tjddbzzbf WHERE ALLTRIM(temp.订单号)==ALLTRIM(tjddbzzbf.订单号)
REPLACE ALL 下差 WITH 织片-计划数量 FOR 织片<计划数量
SELECT 报表日期,业务员,交货日期,订单号,款号,计划数量,织片,下差,套口,手缝,洗水,车唛,成检 from temp INTO dbf/tmp1
USE IN temp
SELECT 1
SELECT tmp1
ncow=Reccount() &&统计记录数
SELECT * FROM tmp1 INTO CURSOR FOXTABLE
CEXCELFILE = PUTFILE("保存为(&N):","tmp1.xlsx","xlsx") &&保存为2007EXCEL版本
IF EMPTY(CEXCELFILE)
RETURN
ENDIF
SELECT FOXTABLE
OEXCELSHEET = GETOBJECT("","Excel.Sheet")
IF NOT TYPE("oExcelSheet") = "O"
= MESSAGEBOX("Excel对象创建失败，程序将中止！",16,"Error")
RETURN
ENDIF
**************************************************
OEXCEL = CREATEOBJECT("excel.application")
WITH OEXCEL
.WORKBOOKS.ADD
.SHEETS("Sheet1").SELECT
.SHEETS("Sheet1").NAME ="报表"
.VISIBLE = .F.
.DISPLAYALERTS = .F.
.ActiveWindow.DisplayZeros = .F.
.CELLS.SELECT
WITH .SELECTION.FONT
.NAME = "宋体"
.SIZE = 10
ENDWITH
ENDWITH
DLINE_S = "A1:"+CHR(ASC("A")+FCOUNT()-1)+"1"
WITH OEXCEL
.CELLS(1, 1).VALUE = "生产日报表"
.ACTIVESHEET.RANGE(DLINE_S).MERGE
.ACTIVESHEET.ROWS(1).ROWHEIGHT = 40
.RANGE(DLINE_S).SELECT
WITH .SELECTION
.FONT.NAME = "宋体"
.FONT.SIZE = 24
.FONT.BOLD = .T.
.HORIZONTALALIGNMENT = 3
.VERTICALALIGNMENT = 2
ENDWITH
ENDWITH
ROW = 2
JLS = TRANSFORM(RECCOUNT()+ROW)
RECROW = TRANSFORM(ROW+1)
ROW_BROWSE = 30
OEXCEL.ACTIVESHEET.ROWS(ROW).ROWHEIGHT = 24
OEXCEL.ACTIVESHEET.ROWS('&RECROW.:&JLS').ROWHEIGHT=20
DLINE_S = "A"+TRANSFORM(ROW)+":"+CHR(ASC("A")+FCOUNT()-1)+TRANSFORM(ROW)
OEXCEL.ACTIVESHEET.RANGE(DLINE_S).BORDERS(3).LINESTYLE = 9
FOR I = 1 TO FCOUNT()
OEXCEL.ACTIVESHEET.COLUMNS(I).COLUMNWIDTH = IIF(LEN(FIELD(I)) > FSIZE(FIELD(I)),LEN(FIELD(I)),FSIZE(FIELD(I)))
OEXCEL.CELLS(ROW, I).VALUE = FIELD(I)
OEXCEL.ACTIVEWORKBOOK.SHEETS(1).CELLS(ROW, I).HORIZONTALALIGNMENT = 3
ENDFOR
OEXCEL.RANGE(DLINE_S).SELECT
OEXCEL.SELECTION.BORDERS(-4107).LINESTYLE = 1
SCAN
IF MOD(RECNO(),ROW_BROWSE) = 1
DLINE_X = "A"+TRANSFORM(RECNO())+":"+CHR(ASC("A")+FCOUNT()-1)+TRANSFORM(RECNO())
SET DATE YMD
SET CENTURY ON
SET SAFETY off
USE tjbbzz IN 0
INDEX on 订单号+款号 TAG sy
TOTAL ON 订单号+款号 TO temp
SELECT 20
USE temp
ALTER TABLE temp ADD 业务员 c(10)
ALTER TABLE temp ADD 交货日期 c(10)
ALTER TABLE temp ADD 计划数量 n(10)
UPDATE temp SET 业务员=tjddbzzbf.业务员,交货日期=tjddbzzbf.交货日期,计划数量=tjddbzzbf.计划数量 from tjddbzzbf WHERE ALLTRIM(temp.订单号)==ALLTRIM(tjddbzzbf.订单号)
REPLACE ALL 下差 WITH 织片-计划数量 FOR 织片<计划数量
SELECT 报表日期,业务员,交货日期,订单号,款号,计划数量,织片,下差,套口,手缝,洗水,车唛,成检 from temp INTO dbf/tmp1
USE IN temp
SELECT 1
SELECT tmp1
ncow=Reccount() &&统计记录数
SELECT * FROM tmp1 INTO CURSOR FOXTABLE
CEXCELFILE = PUTFILE("保存为(&N):","tmp1.xlsx","xlsx") &&保存为2007EXCEL版本
IF EMPTY(CEXCELFILE)
RETURN
ENDIF
SELECT FOXTABLE
OEXCELSHEET = GETOBJECT("","Excel.Sheet")
IF NOT TYPE("oExcelSheet") = "O"
= MESSAGEBOX("Excel对象创建失败，程序将中止！",16,"Error")
RETURN
ENDIF
**************************************************
OEXCEL = CREATEOBJECT("excel.application")
WITH OEXCEL
.WORKBOOKS.ADD
.SHEETS("Sheet1").SELECT
.SHEETS("Sheet1").NAME ="报表"
.VISIBLE = .F.
.DISPLAYALERTS = .F.
.ActiveWindow.DisplayZeros = .F.
.CELLS.SELECT
WITH .SELECTION.FONT
.NAME = "宋体"
.SIZE = 10
ENDWITH
ENDWITH
DLINE_S = "A1:"+CHR(ASC("A")+FCOUNT()-1)+"1"
WITH OEXCEL
.CELLS(1, 1).VALUE = "生产日报表"
.ACTIVESHEET.RANGE(DLINE_S).MERGE
.ACTIVESHEET.ROWS(1).ROWHEIGHT = 40
.RANGE(DLINE_S).SELECT
WITH .SELECTION
.FONT.NAME = "宋体"
.FONT.SIZE = 24
.FONT.BOLD = .T.
.HORIZONTALALIGNMENT = 3
.VERTICALALIGNMENT = 2
ENDWITH
ENDWITH
ROW = 2
JLS = TRANSFORM(RECCOUNT()+ROW)
RECROW = TRANSFORM(ROW+1)
ROW_BROWSE = 30
OEXCEL.ACTIVESHEET.ROWS(ROW).ROWHEIGHT = 24
OEXCEL.ACTIVESHEET.ROWS('&RECROW.:&JLS').ROWHEIGHT=20
DLINE_S = "A"+TRANSFORM(ROW)+":"+CHR(ASC("A")+FCOUNT()-1)+TRANSFORM(ROW)
OEXCEL.ACTIVESHEET.RANGE(DLINE_S).BORDERS(3).LINESTYLE = 9
FOR I = 1 TO FCOUNT()
OEXCEL.ACTIVESHEET.COLUMNS(I).COLUMNWIDTH = IIF(LEN(FIELD(I)) > FSIZE(FIELD(I)),LEN(FIELD(I)),FSIZE(FIELD(I)))
OEXCEL.CELLS(ROW, I).VALUE = FIELD(I)
OEXCEL.ACTIVEWORKBOOK.SHEETS(1).CELLS(ROW, I).HORIZONTALALIGNMENT = 3
ENDFOR
OEXCEL.RANGE(DLINE_S).SELECT
OEXCEL.SELECTION.BORDERS(-4107).LINESTYLE = 1
SCAN
IF MOD(RECNO(),ROW_BROWSE) = 1
DLINE_X = "A"+TRANSFORM(RECNO())+":"+CHR(ASC("A")+FCOUNT()-1)+TRANSFORM(RECNO())