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