ERP导出（自定义格式表格）R报表开发代码
按照正常流程新建程序，画面修改上传，程序下载修改
导入JAVA包，在global.import下
IMPORT com
IMPORT JAVA java.net.URL
IMPORT JAVA org.apache.poi.ss.util.CellRangeAddress
IMPORT JAVA org.apache.poi.ss.util.RegionUtil
IMPORT JAVA java.io.InputStream
IMPORT JAVA java.io.FileInputStream
IMPORT JAVA java.io.FileOutputStream
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFWorkbook
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFSheet
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFRow
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFCell
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFCellStyle
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFFont
IMPORT JAVA org.apache.poi.ss.usermodel.IndexedColors
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFDataFormat
IMPORT JAVA com.fourjs.fgl.lang.FglDecimal
IMPORT JAVA com.fourjs.fgl.lang.FglDate
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFWorkbook
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFSheet
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFRow
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFCell
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFCellStyle
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFFont
IMPORT JAVA org.apache.poi.ss.usermodel.IndexedColors
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFDataFormat
IMPORT JAVA org.apache.poi.xssf.streaming.SXSSFWorkbook
IMPORT JAVA org.apache.poi.ss.usermodel.CellStyle
IMPORT JAVA org.apache.poi.ss.usermodel.Sheet
IMPORT JAVA org.apache.poi.ss.usermodel.Row
IMPORT JAVA org.apache.poi.ss.usermodel.Cell
IMPORT JAVA org.apache.poi.ss.usermodel.Font
IMPORT JAVA org.apache.poi.ss.usermodel.DataFormat
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFWorkbook
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFSheet
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFRow
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFCell
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFCellStyle
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFFont
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFDataFormat
在global.variable下添加全局变量
DEFINE g_showmsg STRING
DEFINE l_xlsfile STRING
DEFINE l_xlsname STRING
DEFINE l_target STRING
DEFINE fo FileOutputStream
DEFINE import_file InputStream
DEFINE workbook XSSFWorkbook
DEFINE sheet XSSFSheet
DEFINE row XSSFRow
DEFINE cell XSSFCell
DEFINE cell_xls HSSFCell
DEFINE font1 XSSFFont
DEFINE font2 XSSFFont
DEFINE font3 XSSFFont
DEFINE font4 XSSFFont
DEFINE format1 DataFormat
DEFINE g_num FLOAT
在DIALOG ATTRIBUTES下添加查询栏位逻辑
ON ACTION controlp INFIELD xcag004
#add-point:ON ACTION controlp INFIELD xcag004 name="construct.c.xcag004"
#應用 a08 樣板自動產生(Version:3)
#開窗c段
INITIALIZE g\_qryparam.\* TO NULL
LET g\_qryparam.state = 'c'
LET g\_qryparam.reqry = FALSE
CALL q\_xcag004() #呼叫開窗
DISPLAY g\_qryparam.return1 TO xcag004 #顯示到畫面上
NEXT FIELD xcag004 #返回原欄位
#END add-point
#應用 a01 樣板自動產生(Version:2)
BEFORE FIELD xcag004
#add-point:BEFORE FIELD xcag004 name="construct.b.xcag004"
#END add-point
#應用 a02 樣板自動產生(Version:2)
AFTER FIELD xcag004
#add-point:AFTER FIELD xcag004 name="construct.a.xcag004"
#END add-point在process下的process.count_progress下添加调用方法逻辑
CALL cxcr600\_create\_tmp()
CALL cxcr600\_export\_excel()写自己的方法，供上面调用
PRIVATE FUNCTION cxcr600_create_tmp()
DROP TABLE cxcr600_tmp;
CREATE TEMP TABLE cxcr600_tmp(
t_xcah020 LIKE xcah_t.xcah020, #bom阶层
t_xcah004 LIKE xcah_t.xcah040, #主件料号
t_xcah004_desc LIKE type_t.chr200, #主件品名
t_xcah004_1_desc LIKE type_t.chr200, #主件规格
t_xcaeud015_sum LIKE xcae_t.xcaeud015, #合计工时
t_xcah031_a_sum LIKE type_t.num20_6, #合计材料费
t_price_ws LIKE type_t.num20_6, #未税单价
t_xcah031_b_sum LIKE type_t.num20_6, #合计人工费
t_xcah031_b_szb LIKE type_t.num20_6, #人工费用占比
t_xcah031_a_szb LIKE type_t.num20_6, #材料费用占比
t_xcah022 LIKE xcah_t.xcah022, #元件料号
t_xcah022_desc LIKE type_t.chr200, #元件品名
t_xcah022_1_desc LIKE type_t.chr200, #元件规格
t_xcaeud015 LIKE xcae_t.xcaeud015, #各阶段标准工时
t_xcaeud016 LIKE xcae_t.xcaeud016, #各阶段标准机时
t_xcah031_b LIKE type_t.num20_6, #各阶段人力费
t_xcah031_a LIKE type_t.num20_6, #各阶段材料费
t_xcah026 LIKE xcah_t.xcah026, #工时占比（各阶段/总）
t_xcah025 LIKE xcah_t.xcah025 #材料占比（各阶段/总）
);
END FUNCTION
PRIVATE FUNCTION cxcr600_ins_tmp(p_imaa001_a)
DEFINE p_imaa001_a LIKE imaa_t.imaa001
DEFINE l_sql STRING
DEFINE l_dex LIKE type_t.num5
DEFINE l_docno DYNAMIC ARRAY OF RECORD
l_xcah020 LIKE xcah_t.xcah020, #bom阶层
l_xcah004 LIKE xcah_t.xcah040, #主件料号
l_xcah004_desc LIKE type_t.chr200, #主件品名
l_xcah004_1_desc LIKE type_t.chr200, #主件规格
l_xcaeud015_sum LIKE xcae_t.xcaeud015, #合计工时
l_xcah031_a_sum LIKE type_t.num20_6, #合计材料费
l_price_ws LIKE type_t.num20_6, #未税单价
l_xcah031_b_sum LIKE type_t.num20_6, #合计人工费
l_xcah031_b_szb LIKE type_t.num20_6, #人工费用占比
l_xcah031_a_szb LIKE type_t.num20_6, #材料费用占比
l_xcah022 LIKE xcah_t.xcah022, #元件料号
l_xcah022_desc LIKE type_t.chr200, #元件品名
l_xcah022_1_desc LIKE type_t.chr200, #元件规格
l_xcaeud015 LIKE xcae_t.xcaeud015, #各阶段标准工时
l_xcaeud016 LIKE xcae_t.xcaeud016, #各阶段标准机时
l_xcah031_b LIKE type_t.num20_6, #各阶段人力费
l_xcah031_a LIKE type_t.num20_6, #各阶段材料费
l_xcah026 LIKE xcah_t.xcah026, #工时占比（各阶段/总）
l_xcah025 LIKE xcah_t.xcah025 #材料占比（各阶段/总）
END RECORD
DEFINE l_num LIKE type_t.num5
LET l\_sql = "DELETE FROM cxcr600\_tmp "
EXECUTE IMMEDIATE l\_sql
#插入数据
LET l\_sql = "INSERT INTO cxcr600\_tmp
SELECT xcah020,xcag004,a.imaal003,a.imaal004,0,0,0,0,0,0,xcah022,b.imaal003,b.imaal004,0,0,0,0,0,0 FROM xcag\_t
LEFT JOIN xcah\_t ON xcagent = xcahent AND xcagsite = xcahsite AND xcag001 = xcah001 AND xcag004 = xcah004
LEFT JOIN imaal\_t a ON a.imaalent = xcagent AND a.imaal001 = xcag004 AND a.imaal002 = '",g\_dlang,"'
LEFT JOIN imaal\_t b ON b.imaalent = xcahent AND b.imaal001 = xcah022 AND b.imaal002 = '",g\_dlang,"'
WHERE xcagent = ",g\_enterprise," AND xcagsite = '",g\_site,"' AND xcah004 = '",p\_imaa001\_a,"'
AND xcag001 = '101' AND xcah023 = 'A'
ORDER BY xcah020 ASC "
DISPLAY l\_sql
EXECUTE IMMEDIATE l\_sql
LET l\_sql = " SELECT \* FROM cxcr600\_tmp "
PREPARE l\_upd\_code FROM l\_sql
DECLARE l\_upd\_curs CURSOR FOR l\_upd\_code
CALL l\_docno.clear() #在进入循环之前把二维数组清空
LET l\_dex = 1 #初始化
FOREACH l\_upd\_curs INTO l\_docno\[l\_dex\].\*
#判断如果不属于A或者B料号，就删除，因为存在C，D料号属于A类型
SELECT COUNT(\*) INTO l\_num
FROM cxcr600\_tmp
WHERE t\_xcah022=l\_docno\[l\_dex\].l\_xcah022
AND (t\_xcah022 LIKE 'A%' OR t\_xcah022 LIKE 'B%')
IF cl\_null(l\_num) OR (l\_num = 0) THEN
#删除此料号
DELETE FROM cxcr600\_tmp
WHERE t\_xcah004 = l\_docno\[l\_dex\].l\_xcah004
AND t\_xcah022 = l\_docno\[l\_dex\].l\_xcah022
END IF
LET l\_num = 0
LET l\_dex = l\_dex + 1
END FOREACHEND FUNCTION
PRIVATE FUNCTION cxcr600_get_data(p_imaa001_a)
DEFINE p_imaa001_a LIKE imaa_t.imaa001
DEFINE l_sql STRING
DEFINE l_dex LIKE type_t.num5 #数组循环变量
DEFINE l_num LIKE type_t.num5 #判断是否夹治具
DEFINE l_num1 LIKE xcah_t.xcah025 #上阶主件底数
DEFINE l_num2 LIKE xcah_t.xcah026 #上阶组成用量
DEFINE l_money_sh LIKE type_t.num20_6 #主件损耗
DEFINE l\_docno DYNAMIC ARRAY OF RECORD
l\_xcah004 LIKE xcah\_t.xcah040, #主件料号
l\_xcah004\_desc LIKE type\_t.chr200, #主件品名
l\_xcah004\_1\_desc LIKE type\_t.chr200, #主件规格
l\_xcaeud015\_sum LIKE xcae\_t.xcaeud015, #合计工时
l\_xcah031\_a\_sum LIKE type\_t.num20\_6, #合计材料费
l\_price\_ws LIKE type\_t.num20\_6, #未税单价
l\_xcah031\_b\_sum LIKE type\_t.num20\_6, #合计人工费
l\_xcah031\_b\_szb LIKE type\_t.num20\_6, #人工费用占比
l\_xcah031\_a\_szb LIKE type\_t.num20\_6, #材料费用占比
l\_xcah022 LIKE xcah\_t.xcah022, #元件料号
l\_xcah022\_desc LIKE type\_t.chr200, #元件品名
l\_xcah022\_1\_desc LIKE type\_t.chr200, #元件规格
l\_xcaeud015 LIKE xcae\_t.xcaeud015, #各阶段标准工时
l\_xcaeud016 LIKE xcae\_t.xcaeud016, #各阶段标准机时
l\_xcah031\_b LIKE type\_t.num20\_6, #各阶段人力费
l\_xcah031\_a LIKE type\_t.num20\_6, #各阶段材料费
l\_xcah026 LIKE xcah\_t.xcah026, #工时占比（各阶段/总）
l\_xcah025 LIKE xcah\_t.xcah025 #材料占比（各阶段/总）
END RECORD
#======================================页签==============================================================
CALL cxcr600\_ins\_tmp(p\_imaa001\_a)
LET l\_sql = " SELECT t\_xcah004,t\_xcah004\_desc,t\_xcah004\_1\_desc,t\_xcaeud015\_sum,t\_xcah031\_a\_sum,
t\_price\_ws,t\_xcah031\_b\_sum,t\_xcah031\_b\_szb,t\_xcah031\_a\_szb,t\_xcah022,t\_xcah022\_desc,
t\_xcah022\_1\_desc,t\_xcaeud015,t\_xcaeud016,t\_xcah031\_b,t\_xcah031\_a,t\_xcah026,t\_xcah025
FROM cxcr600\_tmp ORDER BY t\_xcah020 ASC "
PREPARE l\_get\_code FROM l\_sql
DECLARE l\_get\_curs CURSOR FOR l\_get\_code
CALL l\_docno.clear() #在进入循环之前把二维数组清空
LET l\_dex = 1 #初始化
LET l\_money\_sh = 0 #初始化损耗
FOREACH l\_get\_curs INTO l\_docno\[l\_dex\].\*
#1、标准工时 取阶段料号对应的工时和
SELECT SUM(xcaeud015) INTO l\_docno\[l\_dex\].l\_xcaeud015 FROM xcae\_t
WHERE xcaeent = g\_enterprise AND xcaesite = g\_site
AND xcaestus = 'Y' AND xcae002 = l\_docno\[l\_dex\].l\_xcah004
AND xcaeud001 = l\_docno\[l\_dex\].l\_xcah022
IF cl\_null(l\_docno\[l\_dex\].l\_xcaeud015) THEN
LET l\_docno\[l\_dex\].l\_xcaeud015 = 0
END IF
#2、标准机时 取阶段料号对应的机时和
SELECT SUM(xcaeud016) INTO l\_docno\[l\_dex\].l\_xcaeud016 FROM xcae\_t
WHERE xcaeent = g\_enterprise AND xcaesite = g\_site
AND xcaestus = 'Y' AND xcae002 = l\_docno\[l\_dex\].l\_xcah004
AND xcaeud001 = l\_docno\[l\_dex\].l\_xcah022
IF cl\_null(l\_docno\[l\_dex\].l\_xcaeud016) THEN
LET l\_docno\[l\_dex\].l\_xcaeud016 = 0
END IF
#3、人力成本 取阶段料号对应的成本直接人工+间接一、二
SELECT (xcah031b+xcah031d+xcah031e) INTO l\_docno\[l\_dex\].l\_xcah031\_b FROM xcah\_t
WHERE xcahent = g\_enterprise AND xcahsite = g\_site AND xcah001 = '101'
AND xcah004 = l\_docno\[l\_dex\].l\_xcah004 AND xcah022 = l\_docno\[l\_dex\].l\_xcah022
#4、材料成本 如果是夹治具，直接去成本材料费；不是的话，取它对应的下阶材料费加上它自身的损耗
#取数判断是否夹治具
SELECT imaaud011 INTO l\_num FROM imaa\_t
WHERE imaaent = g\_enterprise AND imaastus = 'Y'
AND imaa001 = l\_docno\[l\_dex\].l\_xcah022
SELECT xcah025 INTO l\_num1 FROM xcah\_t
WHERE xcahent = g\_enterprise AND xcahsite = g\_site AND xcah001 = '101'
AND xcah004 = l\_docno\[l\_dex\].l\_xcah004 AND xcah022 = l\_docno\[l\_dex\].l\_xcah022
SELECT xcah026 INTO l\_num2 FROM xcah\_t
WHERE xcahent = g\_enterprise AND xcahsite = g\_site AND xcah001 = '101'
AND xcah004 = l\_docno\[l\_dex\].l\_xcah004 AND xcah022 = l\_docno\[l\_dex\].l\_xcah022
#损耗=单位材料\*组成用量/主件底数/(1-损耗)-单位材料\*组成用量/主件底数 更新到主件中去
SELECT (xcah030a\*xcah026/xcah025/(1-xcah027/100)-xcah030a\*xcah026/xcah025) INTO l\_money\_sh FROM xcah\_t
WHERE xcahent = g\_enterprise AND xcahsite = g\_site AND xcah001 = '101'
AND xcah004 = l\_docno\[l\_dex\].l\_xcah004 AND xcah040 = l\_docno\[l\_dex\].l\_xcah022
AND xcah023 = 'A' AND xcah022 IN (
SELECT imaa001 FROM imaa\_t
WHERE imaaent = g\_enterprise AND imaastus = 'Y'
AND (imaaud011 = 0 OR imaaud011 IS NULL)
)
IF cl\_null(l\_money\_sh) THEN
LET l\_money\_sh = 0
END IF
#判断是否夹治具料号，如果是直接取夹治具材料成本，否则的话取下阶材料和
IF l\_num <> 0 THEN
SELECT xcah031a INTO l\_docno\[l\_dex\].l\_xcah031\_a FROM xcah\_t
WHERE xcahent = g\_enterprise AND xcahsite = g\_site AND xcah001 = '101'
AND xcah004 = l\_docno\[l\_dex\].l\_xcah004 AND xcah022 = l\_docno\[l\_dex\].l\_xcah022
ELSE
SELECT SUM(xcah031a/l\_num1\*l\_num2) INTO l\_docno\[l\_dex\].l\_xcah031\_a FROM xcah\_t
WHERE xcahent = g\_enterprise AND xcahsite = g\_site AND xcah001 = '101'
AND xcah004 = l\_docno\[l\_dex\].l\_xcah004 AND xcah023 = 'M' AND xcah040 = l\_docno\[l\_dex\].l\_xcah022
AND xcah020 IN (
SELECT xcah020+1 FROM xcah\_t
WHERE xcahent = g\_enterprise AND xcahsite = g\_site AND xcah001 = '101'
AND xcah004 = l\_docno\[l\_dex\].l\_xcah004 AND xcah022 = l\_docno\[l\_dex\].l\_xcah022
)
#如果下阶无材料，费用为空，另它等于零
IF cl\_null(l\_docno\[l\_dex\].l\_xcah031\_a) THEN
LET l\_docno\[l\_dex\].l\_xcah031\_a = 0
END IF
#费用加上它的损耗
LET l\_docno\[l\_dex\].l\_xcah031\_a = l\_docno\[l\_dex\].l\_xcah031\_a+l\_money\_sh
LET l\_money\_sh = 0
END IF
IF cl\_null(l\_docno\[l\_dex\].l\_xcah031\_a) THEN
LET l\_docno\[l\_dex\].l\_xcah031\_a = 0
END IF
#5、合计人工 取axci006里面主件直接+间接
SELECT (xcag102b+xcag102d+xcag102e) INTO l\_docno\[l\_dex\].l\_xcah031\_b\_sum FROM xcag\_t
WHERE xcagent = g\_enterprise AND xcagsite = g\_site
AND xcag001 = '101' AND xcag004 = l\_docno\[l\_dex\].l\_xcah004
#6、合计材料 直接取axci006主件材料费
SELECT xcag102a INTO l\_docno\[l\_dex\].l\_xcah031\_a\_sum FROM xcag\_t
WHERE xcagent = g\_enterprise AND xcagsite = g\_site
AND xcag001 = '101' AND xcag004 = l\_docno\[l\_dex\].l\_xcah004
#7、合计工时 取axci005里面所有工时和
SELECT SUM(xcaeud015) INTO l\_docno\[l\_dex\].l\_xcaeud015\_sum FROM xcae\_t
WHERE xcaeent = g\_enterprise AND xcaesite = g\_site
AND xcaestus = 'Y' AND xcae002 = l\_docno\[l\_dex\].l\_xcah004
#8、未税单价
LET l\_docno\[l\_dex\].l\_price\_ws = cxcr600\_get\_price(l\_docno\[l\_dex\].l\_xcah004)
#9、主件人工占比
LET l\_docno\[l\_dex\].l\_xcah031\_b\_szb = l\_docno\[l\_dex\].l\_xcah031\_b\_sum/l\_docno\[l\_dex\].l\_price\_ws
#10、主件材料占比
LET l\_docno\[l\_dex\].l\_xcah031\_a\_szb = l\_docno\[l\_dex\].l\_xcah031\_a\_sum/l\_docno\[l\_dex\].l\_price\_ws
#11、工时占比
LET l\_docno\[l\_dex\].l\_xcah026= l\_docno\[l\_dex\].l\_xcaeud015/l\_docno\[l\_dex\].l\_xcaeud015\_sum
#12、材料占比
LET l\_docno\[l\_dex\].l\_xcah025 = l\_docno\[l\_dex\].l\_xcah031\_a/l\_docno\[l\_dex\].l\_xcah031\_a\_sum
LET l\_dex = l\_dex + 1
END FOREACH
CALL l\_docno.deleteElement(l\_docno.getLength())
RETURN l\_docnoEND FUNCTION
PRIVATE FUNCTION cxcr600_export_excel()
DEFINE style2 XSSFCellStyle
DEFINE style3 XSSFCellStyle
DEFINE style3_5 XSSFCellStyle
DEFINE style4 XSSFCellStyle
DEFINE style4_1 XSSFCellStyle
DEFINE style4_3 XSSFCellStyle
DEFINE style4_3_1 XSSFCellStyle
DEFINE style5 XSSFCellStyle
DEFINE style6 XSSFCellStyle
DEFINE style7 XSSFCellStyle
DEFINE style8 XSSFCellStyle
DEFINE style9 XSSFCellStyle
DEFINE workbook XSSFWorkbook
DEFINE l_str STRING
DEFINE l_cnt LIKE type_t.num10
DEFINE l_i LIKE type_t.num5
DEFINE l_n LIKE type_t.num5
DEFINE l_n2 LIKE type_t.num5
DEFINE l_fileloc STRING
DEFINE l_length LIKE type_t.num10
DEFINE region CellRangeAddress
DEFINE l_m LIKE type_t.num5
DEFINE l_flag LIKE type_t.chr100
DEFINE l_n1 LIKE type_t.num5
DEFINE l_num FLOAT
DEFINE li_font_size LIKE type_t.num5
DEFINE l_time LIKE pmaa_t.pmaacrtdt
DEFINE l_string LIKE type_t.chr200
DEFINE l_dex LIKE type_t.num5
DEFINE l_m_n LIKE type_t.num5
DEFINE l_number FLOAT
DEFINE l_sql STRING
DEFINE l_row LIKE type_t.num10
DEFINE l_row1 LIKE type_t.num10
DEFINE l_sum_days LIKE type_t.num5
DEFINE l_imaa001 LIKE imaa_t.imaa001
DEFINE r_docno DYNAMIC ARRAY OF RECORD
l_xcah004 LIKE xcah_t.xcah040, #主件料号
l_xcah004_desc LIKE type_t.chr200, #主件品名
l_xcah004_1_desc LIKE type_t.chr200, #主件规格
l_xcaeud015_sum LIKE xcae_t.xcaeud015, #合计工时
l_xcah031_a_sum LIKE type_t.num20_6, #合计材料费
l_price_ws LIKE type_t.num20_6, #未税单价
l_xcah031_b_sum LIKE type_t.num20_6, #合计人工费
l_xcah031_b_szb LIKE type_t.num20_6, #人工费用占比
l_xcah031_a_szb LIKE type_t.num20_6, #材料费用占比
l_xcah022 LIKE xcah_t.xcah022, #元件料号
l_xcah022_desc LIKE type_t.chr200, #元件品名
l_xcah022_1_desc LIKE type_t.chr200, #元件规格
l_xcaeud015 LIKE xcae_t.xcaeud015, #各阶段标准工时
l_xcaeud016 LIKE xcae_t.xcaeud016, #各阶段标准机时
l_xcah031_b LIKE type_t.num20_6, #各阶段人力费
l_xcah031_a LIKE type_t.num20_6, #各阶段材料费
l_xcah026 LIKE xcah_t.xcah026, #工时占比（各阶段/总）
l_xcah025 LIKE xcah_t.xcah025 #材料占比（各阶段/总）
END RECORD
DEFINE l_date LIKE type_t.dat
DEFINE l_day LIKE type_t.num5
DEFINE l_field LIKE type_t.chr200
DEFINE l_i_dex LIKE type_t.num10
CALL ui.Interface.frontCall("standard","opendir",["C:", "File Browser"],[g_showmsg])
LET workbook = XSSFWorkbook.create() #创建工作簿
#设定EXCEL单元格式1
LET style2 = workbook.createCellStyle()
CALL style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style2.setAlignment(XSSFCellStyle.ALIGN_CENTER)
CALL style2.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style2.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style2.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style2.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style2.setWrapText(true)
#设定EXCEL单元格式2 包涵背景颜色
LET style3 = workbook.createCellStyle()
#CALL style3.setFillForegroundColor(IndexedColors.LIME.getIndex())
CALL style3.setFillForegroundColor(IndexedColors.WHITE.getIndex())
CALL style3.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
CALL style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style3.setAlignment(XSSFCellStyle.ALIGN_CENTER)
CALL style3.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style3.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style3.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style3.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style3.setWrapText(true)
LET style7 = workbook.createCellStyle()
#CALL style7.setFillForegroundColor(IndexedColors.LIME.getIndex())
CALL style7.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex())
CALL style7.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
CALL style7.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style7.setAlignment(XSSFCellStyle.ALIGN_CENTER)
CALL style7.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style7.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style7.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style7.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style7.setWrapText(true)
#小计栏位
LET style3_5 = workbook.createCellStyle()
CALL style3_5.setFillForegroundColor(IndexedColors.YELLOW.getIndex())
CALL style3_5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
CALL style3_5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style3_5.setAlignment(XSSFCellStyle.ALIGN_CENTER)
CALL style3_5.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style3_5.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style3_5.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style3_5.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style3_5.setWrapText(true)
#设定EXCEL单元格式2 包涵背景颜色,保留小数位
LET style4 = workbook.createCellStyle()
#CALL style4.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex())
CALL style4.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
LET format1 = workbook.createDataFormat()
CALL style4.setFillForegroundColor(IndexedColors.WHITE.getIndex())
CALL style4.setDataFormat(format1.getFormat("0.000000"))
CALL style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style4.setAlignment(XSSFCellStyle.ALIGN_CENTER)
CALL style4.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style4.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style4.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style4.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style4.setWrapText(true)
LET style4_1 = workbook.createCellStyle()
#CALL style4.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex())
CALL style4_1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
LET format1 = workbook.createDataFormat()
CALL style4_1.setFillForegroundColor(IndexedColors.RED.getIndex())
CALL style4_1.setDataFormat(format1.getFormat("0.000000"))
CALL style4_1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style4_1.setAlignment(XSSFCellStyle.ALIGN_CENTER)
CALL style4_1.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style4_1.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style4_1.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style4_1.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style4_1.setWrapText(true)
LET li_font_size = 11
LET style4_3 = workbook.createCellStyle()
LET font2 = workbook.createFont()
CALL font2.setFontName("Microsoft YaHei")
CALL font2.setFontHeightInPoints(li_font_size)
CALL font2.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD)
CALL style4_3.setFont(font2)
CALL style4_3.setFillForegroundColor(IndexedColors.WHITE.getIndex())
CALL style4_3.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
LET format1 = workbook.createDataFormat()
CALL style4_3.setDataFormat(format1.getFormat("0.00%"))
CALL style4_3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style4_3.setAlignment(XSSFCellStyle.ALIGN_CENTER)
CALL style4_3.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style4_3.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style4_3.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style4_3.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style4_3.setWrapText(true)
LET li_font_size = 11
LET style4_3_1 = workbook.createCellStyle()
LET font2 = workbook.createFont()
CALL font2.setFontName("Microsoft YaHei")
CALL font2.setFontHeightInPoints(li_font_size)
CALL font2.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD)
CALL style4_3_1.setFont(font2)
CALL style4_3_1.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex())
CALL style4_3_1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
LET format1 = workbook.createDataFormat()
CALL style4_3_1.setDataFormat(format1.getFormat("0.00%"))
CALL style4_3_1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style4_3_1.setAlignment(XSSFCellStyle.ALIGN_CENTER)
CALL style4_3_1.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style4_3_1.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style4_3_1.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style4_3_1.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style4_3_1.setWrapText(true)
#设定小计的黄色小数点
LET style6 = workbook.createCellStyle()
CALL style6.setFillForegroundColor(IndexedColors.YELLOW.getIndex())
CALL style6.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
LET format1 = workbook.createDataFormat()
CALL style6.setDataFormat(format1.getFormat("0.00"))
CALL style6.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style6.setAlignment(XSSFCellStyle.ALIGN_CENTER)
CALL style6.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style6.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style6.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style6.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style6.setWrapText(true)
#第一行标题
LET li_font_size = 28
#字体设定
LET font1 = workbook.createFont() #创建字体
#CALL font1.setFontName("Microsoft JhengHei")#字体类型，比如宋体、雅黑等等
CALL font1.setFontName("Microsoft YaHei")
CALL font1.setFontHeightInPoints(li_font_size)#字体大小
CALL font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD)#字体加粗
#字体加粗，加大，标题栏目
LET style5 = workbook.createCellStyle()
#CALL style5.setFillForegroundColor(IndexedColors.LIME.getIndex())
CALL style5.setFillForegroundColor(IndexedColors.WHITE.getIndex())
CALL style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
CALL style5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style5.setAlignment(XSSFCellStyle.ALIGN_CENTER)
CALL style5.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style5.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style5.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style5.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style5.setFont(font1) #设置上述创建字体
CALL style5.setWrapText(true)
#第三行标题
LET li_font_size = 11
LET style7 = workbook.createCellStyle()
LET font3 = workbook.createFont()
CALL font3.setFontName("Microsoft YaHei")
CALL font3.setFontHeightInPoints(li_font_size)
CALL font3.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD)
CALL style7.setFont(font3)
CALL style7.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex())
CALL style7.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
CALL style7.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style7.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style7.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style7.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style7.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style7.setAlignment(XSSFCellStyle.ALIGN_CENTER)
CALL style7.setWrapText(true)
#第二行标题
LET li_font_size = 18
LET style8 = workbook.createCellStyle()
LET font4 = workbook.createFont()
CALL font4.setFontName("Microsoft YaHei")
CALL font4.setFontHeightInPoints(li_font_size)
CALL font4.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD)
CALL style8.setFont(font4)
CALL style8.setFillForegroundColor(IndexedColors.WHITE.getIndex())
CALL style8.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
CALL style8.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style8.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style8.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style8.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style8.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style8.setAlignment(XSSFCellStyle.ALIGN_CENTER)
#最后一行的文件编码
LET style9 = workbook.createCellStyle()
CALL style9.setFont(font2)
CALL style9.setFillForegroundColor(IndexedColors.WHITE.getIndex())
CALL style9.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
CALL style9.setBorderBottom(XSSFCellStyle.BORDER_THIN)
CALL style9.setBorderLeft(XSSFCellStyle.BORDER_THIN)
CALL style9.setBorderTop(XSSFCellStyle.BORDER_THIN)
CALL style9.setBorderRight(XSSFCellStyle.BORDER_THIN)
CALL style9.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
CALL style9.setAlignment(XSSFCellStyle.ALIGN_CENTER)
LET l_str = "成本明细"
LET sheet = workbook.createSheet(l_str)
#=======================================页面开始第一行=====================================================
LET row = sheet.createRow(0) #创建行
CALL row.setHeightInPoints(40) #设置行高
FOR l\_i = 0 TO 8
LET cell = row.createCell(l\_i)
CALL cell.setCellStyle(style5)
LET l\_str = "在制项目标准成本明细"
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(l\_i,l\_length)
END FOR
#合并单元格
LET region = CellRangeAddress.create(0,0,0,8) #单元格合并（起始行号，终止行号， 起始列号，终止列号）0-4共有五个单元格，对应实际表格即为将第一行的第一列到第五列合并
CALL sheet.addMergedRegion(region) #执行上述单元格条件合并
LET region = CellRangeAddress.create(0,0,9,17) #单元格合并（起始行号，终止行号， 起始列号，终止列号）0-4共有五个单元格，对应实际表格即为将第一行的第一列到第五列合并
CALL sheet.addMergedRegion(region) #执行上述单元格条件合并
#第二行
LET row = sheet.createRow(1) #创建行
CALL row.setHeightInPoints(30) #设置行高
FOR l\_i = 0 TO 8
LET cell = row.createCell(l\_i)
CALL cell.setCellStyle(style8)
LET l\_str = "成品"
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(l\_i,l\_length)
END FOR
FOR l\_i = 9 TO 17
LET cell = row.createCell(l\_i)
CALL cell.setCellStyle(style8)
LET l\_str = "各工段"
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(l\_i,l\_length)
END FOR
#合并单元格
LET region = CellRangeAddress.create(1,1,0,8) #单元格合并（起始行号，终止行号， 起始列号，终止列号）0-4共有五个单元格，对应实际表格即为将第一行的第一列到第五列合并
CALL sheet.addMergedRegion(region) #执行上述单元格条件合并
LET region = CellRangeAddress.create(1,1,9,17) #单元格合并（起始行号，终止行号， 起始列号，终止列号）0-4共有五个单元格，对应实际表格即为将第一行的第一列到第五列合并
CALL sheet.addMergedRegion(region) #执行上述单元格条件合并
#第三行
LET row = sheet.createRow(2) #创建行
CALL row.setHeightInPoints(20) #设置行高
FOR l\_i = 0 TO 17
CASE l\_i
WHEN 0 LET l\_str = '主件料号'
WHEN 1 LET l\_str = '主件品名'
WHEN 2 LET l\_str = '主件规格'
WHEN 3 LET l\_str = '合计工时(秒)'
WHEN 4 LET l\_str = '合计材料费'
WHEN 5 LET l\_str = '未税单价'
WHEN 6 LET l\_str = '合计人工费'
WHEN 7 LET l\_str = '人力占比'
WHEN 8 LET l\_str = '材料占比'
WHEN 9 LET l\_str = '料号'
WHEN 10 LET l\_str = '品名'
WHEN 11 LET l\_str = '规格'
WHEN 12 LET l\_str = '标准工时(秒)'
WHEN 13 LET l\_str = '标准机时(秒)'
WHEN 14 LET l\_str = '人力成本'
WHEN 15 LET l\_str = '材料成本'
WHEN 16 LET l\_str = '工时占比'
WHEN 17 LET l\_str = '材料占比'
END CASE
LET cell = row.createCell(l\_i)
CALL cell.setCellStyle(style7)
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(l\_i,l\_length)
END FORLET l_sql = "SELECT DISTINCT xcag004 FROM xcag_t
WHERE xcagent = ",g_enterprise," AND xcagsite = '",g_site,"'
AND xcag001 = '101' AND ",g_master.wc
PREPARE l_imaa001_code FROM l_sql
DECLARE l_imaa001_curs CURSOR FOR l_imaa001_code
LET l_imaa001 = ''
LET l_row1 = 2
#开始循环
FOREACH l_imaa001_curs INTO l_imaa001
#传递参数取数据
CALL cxcr600_get_data(l_imaa001) RETURNING r_docno
#第三行下面的数据
LET l_cnt = r_docno.getLength() #获取数组总笔数
FOR l\_i = 1 TO l\_cnt
#创建行
LET l\_row = l\_i + l\_row1
LET row = sheet.createRow(l\_row) #创建行
CALL row.setHeightInPoints(20) #设置行高
#主件料号
LET cell = row.createCell(0)
CALL cell.setCellStyle(style3)
LET l\_str = r\_docno\[l\_i\].l\_xcah004
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(0,l\_length)
#主件品名
LET cell = row.createCell(1)
CALL cell.setCellStyle(style3)
LET l\_str = r\_docno\[l\_i\].l\_xcah004\_desc
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(1,l\_length)
#主件规格
LET cell = row.createCell(2)
CALL cell.setCellStyle(style3)
LET l\_str = r\_docno\[l\_i\].l\_xcah004\_1\_desc
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(2,l\_length)
#合计工时
LET cell = row.createCell(3)
CALL cell.setCellStyle(style4)
LET l\_num = r\_docno\[l\_i\].l\_xcaeud015\_sum
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(3,l\_length)
#合计材料费
LET cell = row.createCell(4)
CALL cell.setCellStyle(style4)
LET l\_num = r\_docno\[l\_i\].l\_xcah031\_a\_sum
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(4,l\_length)
#未税单价
LET cell = row.createCell(5)
CALL cell.setCellStyle(style4)
LET l\_num = r\_docno\[l\_i\].l\_price\_ws
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(5,l\_length)
#合计人工费
LET cell = row.createCell(6)
CALL cell.setCellStyle(style4)
LET l\_num = r\_docno\[l\_i\].l\_xcah031\_b\_sum
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(6,l\_length)
#人力占比
LET cell = row.createCell(7)
CALL cell.setCellStyle(style4\_3)
LET l\_num = r\_docno\[l\_i\].l\_xcah031\_b\_szb
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(7,l\_length)
#材料占比
LET cell = row.createCell(8)
CALL cell.setCellStyle(style4\_3)
LET l\_num = r\_docno\[l\_i\].l\_xcah031\_a\_szb
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(8,l\_length)
#料号
LET cell = row.createCell(9)
CALL cell.setCellStyle(style3)
LET l\_str = r\_docno\[l\_i\].l\_xcah022
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(9,l\_length)
#品名
LET cell = row.createCell(10)
CALL cell.setCellStyle(style3)
LET l\_str = r\_docno\[l\_i\].l\_xcah022\_desc
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(10,l\_length)
#规格
LET cell = row.createCell(11)
CALL cell.setCellStyle(style3)
LET l\_str = r\_docno\[l\_i\].l\_xcah022\_1\_desc
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(11,l\_length)
#标准工时
LET cell = row.createCell(12)
CALL cell.setCellStyle(style4)
LET l\_num = r\_docno\[l\_i\].l\_xcaeud015
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(12,l\_length)
#标准机时
LET cell = row.createCell(13)
CALL cell.setCellStyle(style4)
LET l\_num = r\_docno\[l\_i\].l\_xcaeud016
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(13,l\_length)
#人力成本
LET cell = row.createCell(14)
CALL cell.setCellStyle(style4)
LET l\_num = r\_docno\[l\_i\].l\_xcah031\_b
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(14,l\_length)
#材料成本
LET cell = row.createCell(15)
CALL cell.setCellStyle(style4)
LET l\_num = r\_docno\[l\_i\].l\_xcah031\_a
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(15,l\_length)
#工时占比
LET cell = row.createCell(16)
CALL cell.setCellStyle(style4\_3)
LET l\_num = r\_docno\[l\_i\].l\_xcah026
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(16,l\_length)
#材料占比
LET cell = row.createCell(17)
CALL cell.setCellStyle(style4\_3)
LET l\_num = r\_docno\[l\_i\].l\_xcah025
CALL cell.setCellValue(l\_num)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(17,l\_length)
END FOR
#合并行
#起始行
LET l\_n2 = l\_row1+1
#客户分组合并
FOR l\_i = 0 TO 8
FOR l\_n = 1 TO l\_cnt
#当前行比较上一行的客户数据对比
IF l\_n > 1 THEN
LET l\_n1 = l\_n - 1 #上一笔
IF r\_docno\[l\_n\].l\_xcah004 <> r\_docno\[l\_n-1\].l\_xcah004 THEN
LET l\_m\_n = l\_n1 + l\_row1
#第l\_i列
LET region = CellRangeAddress.create(l\_n2,l\_m\_n,l\_i,l\_i) #单元格合并（起始行号，终止行号， 起始列号，终止列号）0-4共有五个单元格，对应实际表格即为将第一行的第一列到第五列合并
CALL sheet.addMergedRegion(region) #执行上述单元格条件合并
END IF
END IF
IF l\_n = l\_cnt AND l\_n > 1 THEN #到最后一行了
IF r\_docno\[l\_n\].l\_xcah004 = r\_docno\[l\_n-1\].l\_xcah004 THEN
LET l\_m\_n = l\_n + l\_row1
LET region = CellRangeAddress.create(l\_n2,l\_m\_n,l\_i,l\_i) #单元格合并（起始行号，终止行号， 起始列号，终止列号）0-4共有五个单元格，对应实际表格即为将第一行的第一列到第五列合并
CALL sheet.addMergedRegion(region) #执行上述单元格条件合并
END IF
END IF
END FOR
END FOR
LET l\_row1 = l\_row1+l\_cnt
LET l\_imaa001 = '' END FOREACH
LET l_row = l_row1+1
LET row = sheet.createRow(l_row) #创建行
CALL row.setHeightInPoints(20) #设置行高
FOR l_i = 0 TO 2
LET cell = row.createCell(1)
CALL cell.setCellStyle(style9)
LET l\_str = "表单编号：7-6-2-T\_002（ZJA） V01"
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(l\_i,l\_length) END FOR
FOR l_i = 15 TO 17
LET cell = row.createCell(16)
CALL cell.setCellStyle(style9)
LET l\_str = "惠州至精精密技术有限公司"
CALL cell.setCellValue(l\_str)
LET l\_length = (11.33+0.72)\*300
CALL sheet.setColumnWidth(l\_i,l\_length) END FOR
#合并单元格
LET region = CellRangeAddress.create(l_row,l_row,0,2) #单元格合并（起始行号，终止行号， 起始列号，终止列号）0-4共有五个单元格，对应实际表格即为将第一行的第一列到第五列合并
CALL sheet.addMergedRegion(region) #执行上述单元格条件合并
LET region = CellRangeAddress.create(l_row,l_row,15,17) #单元格合并（起始行号，终止行号， 起始列号，终止列号）0-4共有五个单元格，对应实际表格即为将第一行的第一列到第五列合并
CALL sheet.addMergedRegion(region)
CALL sheet.createFreezePane( 9, 3, 0, 0 ) #冻结窗口
CALL cxcr600_drop_tmp()
LET l_time = cl_get_current()
LET l_string = ''
SELECT to_char(l_time,'yyyymmddhh24miss') INTO l_string FROM DUAL
LET l_xlsname = l_string,"cxcr600.xlsx"
LET l_xlsfile = FGL_GETENV("TEMPDIR")||'/',l_xlsname
LET fo = FileOutputStream.create(l_xlsfile)
CALL workbook.write(fo)
CALL fo.close()
LET l_target = os.Path.join(FGL_GETENV("TEMPDIR"),l_xlsname)
LET l_fileloc = os.Path.join(g_showmsg,l_xlsname CLIPPED)
CALL FGL_PUTFILE(l_target ,l_fileloc)
LET l_str = cl_getmsg('adz-00701',g_lang)
LET l_str = cl_str_replace(l_str,'%1',l_xlsname)
CALL cl_ask_confirm3("",l_str)
END FUNCTION
PRIVATE FUNCTION cxcr600_get_price(p_imaa001)
DEFINE p_imaa001 LIKE imaa_t.imaa001
DEFINE p_time LIKE type_t.chr100
DEFINE r_price LIKE type_t.num20_6
DEFINE r_price_ws LIKE type_t.num20_6
DEFINE r_rate LIKE type_t.num20_6
DEFINE l_date LIKE type_t.dat
DEFINE l_bz LIKE type_t.chr100 #币种
DEFINE l_exrate LIKE type_t.num20_6 #汇率
DEFINE l_xmdt008 LIKE xmdt_t.xmdt008 #含税否
LET l_date = g_today
LET r_price = 0
LET r_rate = 0
LET l_exrate = 0
LET l_xmdt008 = ''
#判断核价单是否存在单价
SELECT NVL(xmdu011,0),NVL(xmdu012,0),xmdt005,xmdt008 INTO r_price,r_rate,l_bz,l_xmdt008 FROM (
SELECT xmdt015,xmdu011,xmdu012,xmdt005,xmdt008 FROM xmdu_t
LEFT JOIN xmdt_t ON xmdtent = xmduent AND xmdtsite = xmdusite AND xmdtdocno = xmdudocno
WHERE xmdtent = g_enterprise AND xmdtsite = g_site AND xmdtstus = 'Y' AND xmdu002 = p_imaa001 AND xmdu011 <> 0
AND xmdt015 <= l_date ORDER BY XMDT015 DESC ) WHERE rownum = 1
#存在对应的币种
IF NOT cl_null(l_bz) THEN
LET g_prog = 'axmt540'
CALL s_axmt540_get_exchange('2',l_bz,l_date) RETURNING l_exrate
LET g_prog = 'cxcr600'
END IF
#获取到单价
IF NOT cl_null(r_price) AND r_price <> 0 AND NOT cl_null(l_bz) THEN
IF l_xmdt008 = 'Y' THEN #含税单价
LET r_price = r_price*l_exrate
LET r_price_ws = r_price/(1+(r_rate/100))
RETURN r_price_ws
END IF
IF l_xmdt008 = 'N' THEN #不含税单价
LET r_price_ws = r_price*l_exrate
LET r_price = r_price*l_exrate*(1+(r_rate/100))
RETURN r_price_ws
END IF
END IF
#当核价单没有单价的时候，从订单去寻找
SELECT NVL(xmdc015,0)*xmda016,NVL(xmdc017,0) INTO r_price,r_rate FROM (
SELECT xmdc001,xmdc015,xmdc017,xmdadocdt,xmda016 FROM xmdc_t
LEFT JOIN xmda_t ON xmdcent = xmdaent AND xmdcsite = xmdasite AND xmdadocno = xmdcdocno
WHERE xmdaent = g_enterprise AND xmdasite = g_site AND xmdastus NOT IN ('N','X') AND xmdc001 = p_imaa001 AND xmdc015 <> 0
AND xmdadocdt <= l_date ORDER BY xmdadocdt DESC) WHERE rownum = 1
#获取到单价
IF NOT cl_null(r_price) AND r_price <> 0 THEN
LET r_price_ws = r_price/(1+(r_rate/100))
RETURN r_price_ws
END IF
LET r_price = 0
LET r_price_ws = 0
RETURN r_price_ws
END FUNCTION
PRIVATE FUNCTION cxcr600_drop_tmp()
DROP TABLE cxcr600_tmp;
END FUNCTION
手机扫一扫
移动阅读更方便
你可能感兴趣的文章