*&---------------------------------------------------------------------*
*& Report ZDZTXTEXCEL
*&---------------------------------------------------------------------*
REPORT ZDZTXTEXCEL.
DATA: BEGIN OF MY_DATA,
MANDT LIKE USR02-MANDT,
BNAME LIKE USR02-BNAME,
ERDAT LIKE USR02-ERDAT,
LTIME LIKE USR02-LTIME,
END OF MY_DATA.
DATA: IT_DATA LIKE MY_DATA OCCURS 0,
FILENAME TYPE STRING VALUE 'C:\sap_usr02.txt'.
WRITE: FILENAME.
SELECT USR02~MANDT USR02~BNAME USR02~ERDAT USR02~LTIME
APPENDING TABLE IT_DATA
FROM USR02.
SORT IT_DATA BY BNAME.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
CONFIRM_OVERWRITE = 'X' "如果文件存在 弹出是否覆盖文件的对话框
WRITE_FIELD_SEPARATOR = 'X' "加入字段分隔符 TAB
FILENAME = FILENAME "文件名 必须为 STRING 类型
TABLES
DATA_TAB = IT_DATA "内表
EXCEPTIONS
FILE_WRITE_ERROR = 1
FILE_NOT_FOUND = 2.
REFRESH IT_DATA. "清空内表
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
FILENAME = FILENAME "文件名 必须为 STRING 类型
FILETYPE = 'ASC'
HAS_FIELD_SEPARATOR = 'X' "可以去掉前面的加入TAB 分隔符,如果不去掉刚会出现#,并且会挤掉后面字段的二位
TABLES
DATA_TAB = IT_DATA. "内表
WRITE: /'MANDT',8'BNAME',25'ERDAT',40'LTIME'.
LOOP AT IT_DATA INTO MY_DATA.
WRITE: / MY_DATA-MANDT,
MY_DATA-BNAME UNDER 'BNAME',
MY_DATA-ERDAT UNDER 'ERDAT',
MY_DATA-LTIME UNDER 'LTIME'.
ENDLOOP.
INCLUDE OLE2INCL. "定义OLE变量
DATA: EXCEL TYPE OLE2_OBJECT,
SHEET TYPE OLE2_OBJECT,
CELL TYPE OLE2_OBJECT,
WORKBOOK TYPE OLE2_OBJECT,
XLSNAME TYPE STRING VALUE 'C:\sap_usr02.xls',
LINE TYPE I VALUE 0. "行号
CREATE OBJECT EXCEL 'EXCEL.APPLICATION'."启动Excel
IF SY-SUBRC NE 0.
WRITE: / '启动Excel失败。'.
STOP.
ENDIF.
CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOK.
SET PROPERTY OF EXCEL 'VISIBLE' = 1. "使excel 可视
SET PROPERTY OF EXCEL 'SHEETSINNEWWORKBOOK' = 1. "如果是读取excel文件中的内容 则是直接打开工作簿第一页
CALL METHOD OF WORKBOOK 'ADD'. "例如:CALL METHOD OF EXCEL 'WORKSHEETS' = SHEET EXPORTING #1 = 1.
LOOP AT IT_DATA INTO MY_DATA.
LINE = LINE + 1. "Excel 中行号从1开始
CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = LINE #2 = 1. "指定单元格,
SET PROPERTY OF CELL 'VALUE' = MY_DATA-MANDT. "写入值
CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = LINE #2 = 2. "指定单元格,
SET PROPERTY OF CELL 'VALUE' = MY_DATA-BNAME. "写入值
CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = LINE #2 = 3. "指定单元格,
SET PROPERTY OF CELL 'VALUE' = MY_DATA-ERDAT. "写入值
CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = LINE #2 = 4. "指定单元格,
SET PROPERTY OF CELL 'VALUE' = MY_DATA-LTIME. "写入值
ENDLOOP.
GET PROPERTY OF EXCEL 'ACTIVESHEET' = SHEET. "激活工作簿
GET PROPERTY OF EXCEL 'ACTIVEWORKBOOK' = WORKBOOK. "激活工作区
CALL METHOD OF WORKBOOK 'SAVEAS' EXPORTING #1 = XLSNAME #2 = 1. "将excel文件保存
CALL METHOD OF WORKBOOK 'CLOSE'. "关闭工作区
CALL METHOD OF EXCEL 'QUIT'. "退出excel
WRITE:/ XLSNAME,'DONE'. "退出成功,输出done
FREE OBJECT SHEET. "释放操作
FREE OBJECT WORKBOOK.
FREE OBJECT EXCEL.
DATA: BEGIN OF IEXCEL OCCURS 0.
INCLUDE STRUCTURE ALSMEX_TABLINE.
DATA: END OF IEXCEL.
DATA: XLS LIKE RLGRAP-FILENAME VALUE 'C:\sap_usr02.xls'.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = XLS
I_BEGIN_COL = 1
I_BEGIN_ROW = 1
I_END_COL = 100 "列
I_END_ROW = 100 "行
TABLES
INTERN = IEXCEL
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
"读取后内表保存的结构为:第一行记录第一个单元的行值 列值 单元格内容 比如 0001 0001 第一个单元格内容
WRITE: /.
IF sy-subrc <> 0.
WRITE: / XLS,'读入错误!'.
ELSE.
LOOP AT IEXCEL.
WRITE IEXCEL-VALUE(20). "取前20个字符
AT END OF ROW. "每行行尾输出空行
WRITE : /.
ENDAT.
ENDLOOP.
ENDIF.
*& Report ZDZTXTEXCEL
*&---------------------------------------------------------------------*
REPORT ZDZTXTEXCEL.
DATA: BEGIN OF MY_DATA,
MANDT LIKE USR02-MANDT,
BNAME LIKE USR02-BNAME,
ERDAT LIKE USR02-ERDAT,
LTIME LIKE USR02-LTIME,
END OF MY_DATA.
DATA: IT_DATA LIKE MY_DATA OCCURS 0,
FILENAME TYPE STRING VALUE 'C:\sap_usr02.txt'.
WRITE: FILENAME.
SELECT USR02~MANDT USR02~BNAME USR02~ERDAT USR02~LTIME
APPENDING TABLE IT_DATA
FROM USR02.
SORT IT_DATA BY BNAME.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
CONFIRM_OVERWRITE = 'X' "如果文件存在 弹出是否覆盖文件的对话框
WRITE_FIELD_SEPARATOR = 'X' "加入字段分隔符 TAB
FILENAME = FILENAME "文件名 必须为 STRING 类型
TABLES
DATA_TAB = IT_DATA "内表
EXCEPTIONS
FILE_WRITE_ERROR = 1
FILE_NOT_FOUND = 2.
REFRESH IT_DATA. "清空内表
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
FILENAME = FILENAME "文件名 必须为 STRING 类型
FILETYPE = 'ASC'
HAS_FIELD_SEPARATOR = 'X' "可以去掉前面的加入TAB 分隔符,如果不去掉刚会出现#,并且会挤掉后面字段的二位
TABLES
DATA_TAB = IT_DATA. "内表
WRITE: /'MANDT',8'BNAME',25'ERDAT',40'LTIME'.
LOOP AT IT_DATA INTO MY_DATA.
WRITE: / MY_DATA-MANDT,
MY_DATA-BNAME UNDER 'BNAME',
MY_DATA-ERDAT UNDER 'ERDAT',
MY_DATA-LTIME UNDER 'LTIME'.
ENDLOOP.
INCLUDE OLE2INCL. "定义OLE变量
DATA: EXCEL TYPE OLE2_OBJECT,
SHEET TYPE OLE2_OBJECT,
CELL TYPE OLE2_OBJECT,
WORKBOOK TYPE OLE2_OBJECT,
XLSNAME TYPE STRING VALUE 'C:\sap_usr02.xls',
LINE TYPE I VALUE 0. "行号
CREATE OBJECT EXCEL 'EXCEL.APPLICATION'."启动Excel
IF SY-SUBRC NE 0.
WRITE: / '启动Excel失败。'.
STOP.
ENDIF.
CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOK.
SET PROPERTY OF EXCEL 'VISIBLE' = 1. "使excel 可视
SET PROPERTY OF EXCEL 'SHEETSINNEWWORKBOOK' = 1. "如果是读取excel文件中的内容 则是直接打开工作簿第一页
CALL METHOD OF WORKBOOK 'ADD'. "例如:CALL METHOD OF EXCEL 'WORKSHEETS' = SHEET EXPORTING #1 = 1.
LOOP AT IT_DATA INTO MY_DATA.
LINE = LINE + 1. "Excel 中行号从1开始
CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = LINE #2 = 1. "指定单元格,
SET PROPERTY OF CELL 'VALUE' = MY_DATA-MANDT. "写入值
CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = LINE #2 = 2. "指定单元格,
SET PROPERTY OF CELL 'VALUE' = MY_DATA-BNAME. "写入值
CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = LINE #2 = 3. "指定单元格,
SET PROPERTY OF CELL 'VALUE' = MY_DATA-ERDAT. "写入值
CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = LINE #2 = 4. "指定单元格,
SET PROPERTY OF CELL 'VALUE' = MY_DATA-LTIME. "写入值
ENDLOOP.
GET PROPERTY OF EXCEL 'ACTIVESHEET' = SHEET. "激活工作簿
GET PROPERTY OF EXCEL 'ACTIVEWORKBOOK' = WORKBOOK. "激活工作区
CALL METHOD OF WORKBOOK 'SAVEAS' EXPORTING #1 = XLSNAME #2 = 1. "将excel文件保存
CALL METHOD OF WORKBOOK 'CLOSE'. "关闭工作区
CALL METHOD OF EXCEL 'QUIT'. "退出excel
WRITE:/ XLSNAME,'DONE'. "退出成功,输出done
FREE OBJECT SHEET. "释放操作
FREE OBJECT WORKBOOK.
FREE OBJECT EXCEL.
DATA: BEGIN OF IEXCEL OCCURS 0.
INCLUDE STRUCTURE ALSMEX_TABLINE.
DATA: END OF IEXCEL.
DATA: XLS LIKE RLGRAP-FILENAME VALUE 'C:\sap_usr02.xls'.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = XLS
I_BEGIN_COL = 1
I_BEGIN_ROW = 1
I_END_COL = 100 "列
I_END_ROW = 100 "行
TABLES
INTERN = IEXCEL
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
"读取后内表保存的结构为:第一行记录第一个单元的行值 列值 单元格内容 比如 0001 0001 第一个单元格内容
WRITE: /.
IF sy-subrc <> 0.
WRITE: / XLS,'读入错误!'.
ELSE.
LOOP AT IEXCEL.
WRITE IEXCEL-VALUE(20). "取前20个字符
AT END OF ROW. "每行行尾输出空行
WRITE : /.
ENDAT.
ENDLOOP.
ENDIF.