T100如何使用Java的poi包导出excel

T100如何使用Java的poi包导出excel

之前用字符串拼接导出来的Excel。有些office是打不开的，wps可以打开。所以改用poi写的，让office也可以打开

poi官网说明：https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Cell.html

1.引入Java包

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

2.编写导出方法

PUBLIC FUNCTION cs_cxmp002_make_xls_poi(p_data){
DEFINE p_data DYNAMIC ARRAY OF l_data
DEFINE fo FileOutputStream    #文件流对象
    DEFINE workbook SXSSFWorkbook #workbook 对象
    DEFINE sheet Sheet    #sheet 对象
    DEFINE row Row        #row  行对象
    DEFINE cell Cell      #cell  对象
    DEFINE style CellStyle   #style 
    DEFINE headerFont Font   #字体对象
    DEFINE l_i like type_t.num10
DEFINE l_timestr like type_t.chr200
DEFINE name_prefix STRING
DEFINE file_prefix STRING
DEFINE xlsfile    STRING

DEFINE l_fmt string    
DEFINE l_format  DataFormat  
DEFINE style_num CellStyle


LET workbook = SXSSFWorkbook.create() #新建一个workbook 工作簿
    LET style = workbook.createCellStyle()
    CALL style.setAlignment(CellStyle.ALIGN_CENTER) #设置样式居中
    
    LET l_fmt="0.00" 
    LET style_num=workbook.createCellStyle()
    LET l_format = workbook.createDataFormat()         
    CALL style_num.setDataFormat(l_format.getFormat(l_fmt)) #设置数字格式，保留2位小数
    CALL style_num.setAlignment(CellStyle.ALIGN_CENTER) #设置样式居中    
    
    LET sheet = workbook.createSheet("订单明细表") #建立一个sheet

LET row = sheet.createRow(0) #新建一个行 
IF g_dlang<>'zh_CN'  THEN    
    CALL cs_cxmp002_setRowCell(row,0,"UNIQID",style)
    CALL cs_cxmp002_setRowCell(row,1,"ORDER NO.",style)
    CALL cs_cxmp002_setRowCell(row,2,"DATE",style)
    CALL cs_cxmp002_setRowCell(row,3,"BRAND",style)
    CALL cs_cxmp002_setRowCell(row,4,"STATUS",style)
    CALL cs_cxmp002_setRowCell(row,5,"SALESMAN",style)
    CALL cs_cxmp002_setRowCell(row,6,"FULL NAME",style)
    CALL cs_cxmp002_setRowCell(row,7,"CUSTOMER NO.",style)    
    CALL cs_cxmp002_setRowCell(row,8,"TRANSACTION PARTY ACRONYM",style)
    CALL cs_cxmp002_setRowCell(row,9,"CUSTOMER NO. ORDER",style)
    CALL cs_cxmp002_setRowCell(row,10,"LINE NO.",style)
    
    CALL cs_cxmp002_setRowCell(row,11,"ITEM NO.",style)
    CALL cs_cxmp002_setRowCell(row,12,"ITEM NAME",style)    
    CALL cs_cxmp002_setRowCell(row,13,"PRODUCTION NOTE",style)
    CALL cs_cxmp002_setRowCell(row,14,"AGREED SHIPPING DATE",style)
    CALL cs_cxmp002_setRowCell(row,15,"BATCH ORDER QUANTITY",style)
    CALL cs_cxmp002_setRowCell(row,16,"UNIT PRICE",style)
    CALL cs_cxmp002_setRowCell(row,17,"BATCH NO TAX AMOUNT",style)
    CALL cs_cxmp002_setRowCell(row,18,"SHIPPED QTY",style)
    CALL cs_cxmp002_setRowCell(row,19,"UNSHIPPED QUANTITY",style)
    CALL cs_cxmp002_setRowCell(row,20,"SHIP DATE",style)
    
    CALL cs_cxmp002_setRowCell(row,21,"SHIPMENT NUMBER",style)
    CALL cs_cxmp002_setRowCell(row,22,"SHIPPED TAX-INCLUDED AMOUNT",style)
    CALL cs_cxmp002_setRowCell(row,23,"TRACKING NUMBER",style)
    CALL cs_cxmp002_setRowCell(row,24,"EXPRESS NAME",style)
    CALL cs_cxmp002_setRowCell(row,25,"CUSTOMER ITEM NUMBER",style)
 ELSE
   CALL cs_cxmp002_setRowCell(row,0,"UNIQID",style)
    CALL cs_cxmp002_setRowCell(row,1,"订单单号",style)
    CALL cs_cxmp002_setRowCell(row,2,"订单日期",style)
    CALL cs_cxmp002_setRowCell(row,3,"品牌",style)
    CALL cs_cxmp002_setRowCell(row,4,"状态",style)
    CALL cs_cxmp002_setRowCell(row,5,"打单人员",style)
    CALL cs_cxmp002_setRowCell(row,6,"打单人员说明",style)
    CALL cs_cxmp002_setRowCell(row,7,"客户编号",style)    
    CALL cs_cxmp002_setRowCell(row,8,"客户编号说明",style)
    CALL cs_cxmp002_setRowCell(row,9,"客户订购单号",style)
    CALL cs_cxmp002_setRowCell(row,10,"收货地址",style)
    
    CALL cs_cxmp002_setRowCell(row,11,"料件编号",style)
    CALL cs_cxmp002_setRowCell(row,12,"品名",style)    
    CALL cs_cxmp002_setRowCell(row,13,"生产备注",style)
    CALL cs_cxmp002_setRowCell(row,14,"预计交货日期",style)
    CALL cs_cxmp002_setRowCell(row,15,"订购量",style)
    CALL cs_cxmp002_setRowCell(row,16,"单价",style)
    CALL cs_cxmp002_setRowCell(row,17,"未税金额",style)
    CALL cs_cxmp002_setRowCell(row,18,"已出货数量",style)
    CALL cs_cxmp002_setRowCell(row,19,"未出货量",style)
    CALL cs_cxmp002_setRowCell(row,20,"出货日期",style)
    
    CALL cs_cxmp002_setRowCell(row,21,"出货单号",style)
    CALL cs_cxmp002_setRowCell(row,22,"已出货含税金额",style)
    CALL cs_cxmp002_setRowCell(row,23,"快递单号",style)
    CALL cs_cxmp002_setRowCell(row,24,"快递名称",style)
    CALL cs_cxmp002_setRowCell(row,25,"客户料号",style)
END IF	

 
    FOR l_i=1 TO p_data.getLength()
      LET row = sheet.createRow(l_i) #新建一个行 
      CALL cs_cxmp002_setRowCell(row,0,p_data[l_i].uniqid,style)
       CALL cs_cxmp002_setRowCell(row,1,p_data[l_i].xmdadocno,style)
       CALL cs_cxmp002_setRowCell(row,2,p_data[l_i].xmdadocdt,style)
       CALL cs_cxmp002_setRowCell(row,3,p_data[l_i].xmdaua002,style)
       CALL cs_cxmp002_setRowCell(row,4,p_data[l_i].xmdastus_desc,style)
       CALL cs_cxmp002_setRowCell(row,5,p_data[l_i].xmda002,style)
       CALL cs_cxmp002_setRowCell(row,6,p_data[l_i].xmda002_desc,style)
       CALL cs_cxmp002_setRowCell(row,7,p_data[l_i].xmda004,style)    
       CALL cs_cxmp002_setRowCell(row,8,p_data[l_i].xmda004_desc,style)
       CALL cs_cxmp002_setRowCell(row,9,p_data[l_i].xmda033,style)
       CALL cs_cxmp002_setRowCell(row,10,p_data[l_i].address,style)
       
       CALL cs_cxmp002_setRowCell(row,11,p_data[l_i].xmdc001,style)
       CALL cs_cxmp002_setRowCell(row,12,p_data[l_i].xmdc001_desc,style)    
       CALL cs_cxmp002_setRowCell(row,13,p_data[l_i].xmdc050,style)
       CALL cs_cxmp002_setRowCell(row,14,p_data[l_i].xmdc012,style)
       IF NOT cl_null(p_data[l_i].xmdc007) THEN
         CALL cs_cxmp002_setRowCell_float(row,15,p_data[l_i].xmdc007,style_num) #number
       ELSE
         CALL cs_cxmp002_setRowCell(row,15,"",style) 
       END IF
       IF NOT cl_null(p_data[l_i].xmdc015) THEN
         CALL cs_cxmp002_setRowCell_float(row,16,p_data[l_i].xmdc015,style_num)  #number
       ELSE 
         CALL cs_cxmp002_setRowCell(row,16,"",style) 
       END IF
       IF NOT cl_null(p_data[l_i].xmdc046) THEN
         CALL cs_cxmp002_setRowCell_float(row,17,p_data[l_i].xmdc046,style_num) #number
       ELSE 
         CALL cs_cxmp002_setRowCell(row,17,"",style) 
       END IF
       IF NOT cl_null(p_data[l_i].xmdd014) THEN
         CALL cs_cxmp002_setRowCell_float(row,18,p_data[l_i].xmdd014,style_num) #number
       ELSE
         CALL cs_cxmp002_setRowCell(row,18,"",style) 
       END IF
       IF NOT cl_null(p_data[l_i].xmdd014_1) THEN
         CALL cs_cxmp002_setRowCell_float(row,19,p_data[l_i].xmdd014_1,style_num) #number
       ELSE
         CALL cs_cxmp002_setRowCell(row,19,"",style) 
       END IF
             
       CALL cs_cxmp002_setRowCell(row,20,p_data[l_i].xmdkpstdt,style)
       
       CALL cs_cxmp002_setRowCell(row,21,p_data[l_i].xmdkdocno,style)
       IF NOT cl_null(p_data[l_i].xmdl028) THEN
         CALL cs_cxmp002_setRowCell_float(row,22,p_data[l_i].xmdl028,style_num)   #number
       ELSE
         CALL cs_cxmp002_setRowCell(row,22,"",style) 
       END IF       
       CALL cs_cxmp002_setRowCell(row,23,p_data[l_i].xmdkud003,style)
       CALL cs_cxmp002_setRowCell(row,24,p_data[l_i].xmdkud001,style)
       CALL cs_cxmp002_setRowCell(row,25,p_data[l_i].xmdc027,style)
    
    END FOR
    
#自动调整列宽
FOR l_i=1 TO p_data.getLength()
    CALL sheet.autoSizeColumn(l_i) 
   END FOR
   
   SELECT to_char(sysdate,'yyyy-MM-dd-HH24-mi-ss') INTO l_timestr FROM dual
   LET name_prefix=l_timestr
   LET file_prefix = FGL_GETENV("TEMPDIR")||'/',name_prefix
   LET xlsfile = file_prefix,".xlsx"    #包含文件路径
   
    LET fo = FileOutputStream.create(xlsfile);
    CALL workbook.write(fo);
    CALL fo.close();
    
    RETURN xlsfile
}

PUBLIC FUNCTION cs_cxmp002_setRowCell(p_row,p_cellIndex,p_value,p_style)
DEFINE p_row Row        #row  行对象
DEFINE p_cellIndex like type_t.num10
DEFINE p_value string
DEFINE p_style CellStyle
DEFINE cell Cell      #cell  对象

   LET cell=p_row.createCell(p_cellIndex)
   CALL cell.setCellValue(p_value)
   CALL cell.setCellStyle(p_style)
  
END FUNCTION

PUBLIC FUNCTION cs_cxmp002_setRowCell_float(p_row,p_cellIndex,p_value,p_style)
DEFINE p_row Row        #row  行对象
DEFINE p_cellIndex like type_t.num10
DEFINE p_value float
DEFINE p_style CellStyle
DEFINE cell Cell      #cell  对象

   LET cell=p_row.createCell(p_cellIndex)
   CALL cell.setCellValue(p_value)
   CALL cell.setCellStyle(p_style)
  
END FUNCTION