07.02.02.tiptop集成篇（4gl扩展功能集成java：封装java的poi包设配4gl 案例制作excle）

DKLi1717

已于 2025-05-06 13:58:02 修改

阅读量400

点赞数 16

分类专栏：鼎捷tiptop 5.3 文章标签： java 开发语言

于 2025-03-21 16:47:25 首次发布

本文链接：https://blog.youkuaiyun.com/qq_26730689/article/details/146424430

版权

鼎捷tiptop 5.3 专栏收录该内容

82 篇文章

订阅专栏

本页目录

1、案例
2、测试

参考官方源代码poi-3.17-sources
参考官方源代码poi-ooxml-3.17-sources
官网案例

源代码可以解压后vscode查看
基础知识：07.02.01.集成篇（4gl扩展功能集成java：封装java的poi包设配4gl 使用maven模板测试兼容包）

案例

效果

在这里插入图片描述

代码

p_test.4gl


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.ss.usermodel.Row
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFCell
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFCellStyle
IMPORT JAVA org.apache.poi.ss.usermodel.IndexedColors
IMPORT JAVA org.apache.poi.ss.usermodel.FillPatternType
IMPORT JAVA org.apache.poi.ss.usermodel.HorizontalAlignment
IMPORT JAVA org.apache.poi.ss.usermodel.VerticalAlignment
IMPORT JAVA org.apache.poi.ss.usermodel.BorderStyle
IMPORT JAVA org.apache.poi.ss.usermodel.Font
IMPORT JAVA com.fourjs.fgl.lang.FglDate
IMPORT JAVA org.apache.poi.ss.usermodel.CreationHelper
import JAVA java.text.SimpleDateFormat


DATABASE ds
GLOBALS "../../config/top.global"

GLOBALS
    DEFINE temp_dir           STRING,
           filename           STRING,
           workbook           XSSFWorkbook,
           sheet              XSSFSheet,
           g_row_count        INTEGER, 
           g_cells            STRING, 
           cellL              INTEGER,
           g_sql              STRING,         
           g_table_count      INTEGER
END GLOBALS


MAIN
  OPTIONS
      FORM LINE     FIRST + 2, 
      MESSAGE LINE  LAST, 
      PROMPT LINE   LAST, 
      INPUT NO WRAP 
  DEFER INTERRUPT

  IF (NOT cl_user()) THEN
    EXIT PROGRAM
  END IF

  WHENEVER ERROR CALL cl_err_msg_log
  CALL cl_used(g_prog,g_time,1) RETURNING g_time

  ------------------------begin 可以修改----------------------
  LET temp_dir=FGL_GETENV("TEMPDIR") 
  LET filename=g_user||"_"||(g_today USING "yyyy-mm-dd")||"_"||FGL_GETPID()||"_test.xlsx"
  LET g_cells="pmm01,pmn02,pmn20,pmn31,pmn33,pmn38,pmn88"
  LET g_sql=" SELECT ",g_cells,
            " FROM PMM_FILE ",
            " INNER JOIN PMN_FILE ON pmn01=pmm01 ",
            " WHERE pmm04=TO_DATE('2024-11-01','YYYY-MM-DD')",
            " ORDER BY pmm01,pmn02 "
  ------------------------end 可以修改----------------------



  ------------------------begin 封装----------------------
  CALL workbook_create() RETURNING workbook
  CALL workbook_createsheet(workbook) RETURNING sheet
  CALL sheet_createFreezePane(sheet,0,1) # 冻结首列
  
  call getColumnAttribute() 

  LET g_row_count=0
  CALL creatHeadXLSX()
  CALL creatDataXLSX()

  CALL workbook_writeToFile(workbook,filename)
  CALL getFile(filename) 
  ------------------------end 封装----------------------


  CALL cl_used(g_prog,g_time,2) RETURNING g_time
END MAIN


# @mathodName: creatHeadXLSX
# @author:DKLi1717 2665252078@qq.com
# @date:Created in 2025-04-02
# @description: 创建xlsx的首行
FUNCTION creatHeadXLSX() 
    DEFINE headstr           STRING,
           i                 INTEGER,
           l_xlsx02          VARCHAR(50),
           headRow            Row,
           headCell           XSSFCell
           


    CALL sheet_createrow(sheet,g_row_count) RETURNING headRow
    # 创建列并添值
    FOR i = 1 to cellL
      CALL row_createcell(headRow,(i-1)) RETURNING headCell
      CALL headCell.setCellType(XSSFCell.CELL_TYPE_STRING)
      SELECT xlsx02 INTO l_xlsx02 FROM xlsx_temp WHERE xlsx00=i
      CALL headCell.setCellValue(l_xlsx02)
      CALL setStyle(headCell,"HeadStyle")
    END FOR

END FUNCTION

# @mathodName: creatDataXLSX
# @author:DKLi1717 2665252078@qq.com
# @date:Created in 2025-04-02
# @description: 创建xlsx的内容数据
FUNCTION creatDataXLSX()
  DEFINE dataRow            Row,
         dataCell           XSSFCell,
         i                  INTEGER,
         j                  INTEGER,
         l_xlsx01           VARCHAR(20),
         l_xlsx04           VARCHAR(50),
         l_xlsx07           INT,
         vs                 LIKE type_file.chr1000,
         vi                 INTEGER,
         vf                 FLOAT,
         vd                 Date,
         vb                 BOOLEAN,
         vfd                FglDate,
         l_sql              STRING,
         ch                 CreationHelper,
         cs                 XSSFCellStyle,
         df                 org.apache.poi.ss.usermodel.DataFormat,
         dateFormat         SimpleDateFormat
         
  CALL creationhelper_creat(workbook) RETURNING ch
  CALL style_create(workbook) RETURNING cs
  CALL dataformat_creat(workbook) RETURNING df

  FOR i=1 TO g_table_count
    LET g_row_count=g_row_count+1
    CALL sheet_createrow(sheet,g_row_count) RETURNING dataRow
    FOR j = 1 to cellL
      CALL row_createcell(dataRow,(j-1)) RETURNING dataCell
      SELECT xlsx01,xlsx04,xlsx07 INTO l_xlsx01,l_xlsx04,l_xlsx07  FROM xlsx_temp WHERE xlsx00=j
      LET l_sql=" select ",l_xlsx01," from(select rownum r,a.",l_xlsx01," from xlsx_temp_data a where rownum<= ",i," ) where r >=",i
      CASE
        WHEN l_xlsx04 = 'VARCHAR2' OR l_xlsx04 = 'CLOB' 
          LET vs=""
          PREPARE tmp_vs FROM l_sql
          EXECUTE tmp_vs INTO vs
          CALL dataCell.setCellType(XSSFCell.CELL_TYPE_STRING)
          CALL dataCell.setCellValue(vs)
        WHEN l_xlsx04 = 'NUMBER' AND l_xlsx07=0
          LET vi=0
          PREPARE tmp_vi FROM l_sql
          EXECUTE tmp_vi INTO vi
          CALL dataCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC)
          CALL dataCell.setCellValue(vi)
        WHEN l_xlsx04 = 'NUMBER' AND l_xlsx07>0
          LET vf=0.0
          PREPARE tmp_vf FROM l_sql
          EXECUTE tmp_vf INTO vf
          CALL dataCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC)
          CALL dataCell.setCellValue(vf)
        WHEN l_xlsx04 = 'DATE'
          PREPARE tmp_vd FROM l_sql
          EXECUTE tmp_vd INTO vd
          LET vfd=vd
          LET dateFormat=SimpleDateFormat.create("yyyy-MM-dd")
          CALL cs.setDataFormat(df.getFormat("yyyy-MM-dd"))
          CALL dataCell.setCellStyle(cs)
          CALL dataCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC)
          CALL dataCell.setCellValue(dateFormat.parse(vfd.toString()))
        WHEN l_xlsx04 = 'BLOB'
          PREPARE tmp_vb FROM l_sql
          EXECUTE tmp_vb INTO vb
          CALL dataCell.setCellType(XSSFCell.CELL_TYPE_BOOLEAN)
          CALL dataCell.setCellValue(vb)
      END CASE 
    END FOR
  END FOR
  

  
END FUNCTION

# @mathodName: setStyle
# @author:DKLi1717 2665252078@qq.com
# @date:Created in 2025-04-02
# @description: 设置样式
# @param vc org.apache.poi.xssf.usermodel.XSSFCell
# @param v STRING
FUNCTION setStyle(vc,v)
  DEFINE vc               XSSFCell,
         v                STRING

  DEFINE i                 INTEGER,
         fh                SMALLINT,
         cellStyle         XSSFCellStyle,
         headFont          Font,
         dataFont          Font

  FOR i = 1 to cellL
     CALL sheet_columnwidth_set(sheet,(i-1),20.0) # 列宽
  END FOR

  CALL style_create(workbook) RETURNING cellStyle

  # 边框
  CALL cellStyle.setBorderTop(BorderStyle.THIN)
  CALL cellStyle.setBorderBottom(BorderStyle.THIN)
  CALL cellStyle.setBorderLeft(BorderStyle.THIN)
  CALL cellStyle.setBorderRight(BorderStyle.THIN)

  CASE 
    WHEN v='HeadStyle'
      CALL cellStyle.setAlignment(HorizontalAlignment.CENTER) # 水平居中
      CALL cellStyle.setVerticalAlignment(VerticalAlignment.CENTER) # 垂直居中
      CALL cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex())#背景颜色
      CALL cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); #背景颜色状态
      # 首列字体样式
      CALL font_create(workbook) RETURNING headFont
      LET fh=18
      CALL headFont.setFontHeightInPoints(fh)
      CALL headFont.setFontName("华文楷体")
      CALL headFont.setColor(IndexedColors.WHITE.getIndex())
      CALL cellStyle.setFont(headFont)
  END CASE
  CALL cell_style_set(vc,cellStyle)


END FUNCTION

# @mathodName: getColumnAttribute
# @author:DKLi1717 2665252078@qq.com
# @date:Created in 2025-04-02
# @description: 创建临时表：一个存字段属性，一预创建临时表存数据
FUNCTION getColumnAttribute() 
  DEFINE l_cells    STRING,
         cellAs     DYNAMIC ARRAY OF STRING,
         i          INTEGER,
         asns       DYNAMIC ARRAY OF STRING,
         l_sql      STRING,
         get_sql    LIKE type_file.chr1000,
         l_cell     LIKE type_file.chr21

  DROP TABLE xlsx_temp
  CREATE TEMP TABLE xlsx_temp(
                                xlsx00   INT,        # 顺序 
                                xlsx01   VARCHAR(20),# 字段
                                xlsx02   VARCHAR(50),# 字段名称
                                xlsx03   VARCHAR(30),# 表名称
                                xlsx04   VARCHAR(50),# 字段类型
                                xlsx05   INT,        # 长度
                                xlsx06   INT,        # 整数长度
                                xlsx07   INT         # 小数长度
                                )

  CALL cellAs.clear()
  DELETE FROM xlsx_temp

  CALL cl_str_split(g_cells , ",") RETURNING cellAs
  LET cellL=cellAs.getlength()

  # 获取字段数组
  LET l_cells=""
  FOR i=1 TO cellL
    CALL cl_str_split(cellAs[i], " ") RETURNING asns 
    IF asns.getlength()>1 THEN
      LET cellAs[i]=asns[2]
    END IF
    IF i=1 THEN
      LET l_cells="'"||cellAs[i]||"'"
    ELSE
      LET l_cells=l_cells||",'"||cellAs[i]||"'"
    END IF
  END FOR
  LET g_cells=l_cells
  LET l_cells="("||l_cells||")"

  LET l_sql=" INSERT INTO xlsx_temp ",
            " SELECT 0,LOWER(COLUMN_NAME),gaq03,LOWER(TABLE_NAME),DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,0 ",
            " FROM dba_tab_columns ",
            " LEFT JOIN gaq_file ON gaq01=LOWER(COLUMN_NAME) ",
            "                   AND gaq02='",g_lang,"'",
            " WHERE TABLE_NAME=SUBSTR(REPLACE(COLUMN_NAME,'UD',''),1,LENGTH(REPLACE(COLUMN_NAME,'UD',''))-2)||'_FILE' ",
            "    AND OWNER = '",g_plant,"'",
            "    AND LOWER(COLUMN_NAME) IN  ( ",g_sql," )"

  EXECUTE IMMEDIATE l_sql
  IF STATUS THEN 
    CALL cl_err('INSERT INTO xlsx_temp:',STATUS,1)
    return
  END IF

  # 按提供的列顺序排队
  FOR i=1 TO cellL
    LET  l_cell=cellAs[i]
    UPDATE xlsx_temp SET xlsx00=i WHERE xlsx01=l_cell
  END FOR
  
  LET l_sql=" SELECT LISTAGG(t,',') WITHIN GROUP (ORDER BY t)  AS cn ",
            " FROM ( ",
            "        SELECT CASE ",
            "                 WHEN DATA_TYPE='VARCHAR2' THEN COLUMN_NAME||' VARCHAR2('||DATA_LENGTH||')' ",
            "                 WHEN DATA_TYPE='NUMBER' AND DATA_SCALE=0  THEN COLUMN_NAME||' NUMBER('||DATA_PRECISION||')'  ",
            "                 WHEN DATA_TYPE='NUMBER' AND DATA_SCALE>0  THEN COLUMN_NAME||' NUMBER('||DATA_PRECISION||','||DATA_SCALE||')'  ",
            "                 WHEN DATA_TYPE='DATE'  THEN COLUMN_NAME||' DATE' ",
            "                 WHEN DATA_TYPE='BLOB'  THEN COLUMN_NAME||' BLOB' ",
            "                 WHEN DATA_TYPE='CLOB'  THEN COLUMN_NAME||' CLOB' ",
            "               END t ",
            "        FROM dba_tab_columns ",
            "        WHERE TABLE_NAME=SUBSTR(REPLACE(COLUMN_NAME,'UD',''),1,LENGTH(REPLACE(COLUMN_NAME,'UD',''))-2)||'_FILE' ",
            "           AND OWNER = '",g_plant,"'",
            "           AND LOWER(COLUMN_NAME) IN ( ",g_sql," )",
            " ) "
   PREPARE tmp_pre FROM l_sql
   EXECUTE tmp_pre INTO get_sql

   DROP TABLE xlsx_temp_data
   LET l_sql=" CREATE TEMP TABLE xlsx_temp_data( ",get_sql," ) "
   EXECUTE IMMEDIATE l_sql
   IF STATUS THEN 
    CALL cl_err('CREATE TEMP TABLE :',STATUS,1)
    return
  END IF
  DELETE FROM xlsx_temp_data


  LET g_sql=" INSERT INTO xlsx_temp_data ",g_sql
  EXECUTE IMMEDIATE g_sql
   IF STATUS THEN 
    CALL cl_err('INSERT INTO xlsx_temp_data :',STATUS,1)
    return
  END IF

  SELECT COUNT(*) INTO g_table_count FROM xlsx_temp_data
END FUNCTION

# @mathodName: getFile
# @author:DKLi1717 2665252078@qq.com
# @date:Created in 2025-04-02
# @description: 下載
FUNCTION getFile(filename) 
    DEFINE filename    STRING
    
    LET status =cl_download_file(temp_dir||"/"||filename,"c:/tiptop/"||filename)
    IF status THEN
      CALL cl_err('','lib-630',0)
      IF NOT cl_null(temp_dir||filename) THEN
        RUN "rm -rf "||temp_dir||"/"||filename
      END IF
    ELSE
       CALL cl_err('','lib-631',0)
    END IF
END FUNCTION

测试

封装代码

s_poi_excel.4gl

# 上传代码
cd /u1/toptest/topcust/csub/4gl
put s_poi_excel.4gl
cd /u1/toptest/topcust/czz/4gl
put p_test.4gl


# 编译代码
cd /u1/toptest/topcust/csub/4gl
r.c2 s_poi_excel
#查询：连结档案：sub
# 表格新增：模组代码：CSUB  程序代码：s_poi_excel 是否连接：是
exe2 p_link
# 配置后link一下
r.l2 sub

cd /u1/toptest/topcust/czz/4gl
r.c2 p_test
r.l2 p_test
exe2 p_test