解析XLSX文件的短平快的解决方案

ABAP Development

解析XLSX文件的短平快的解决方案

2020-08-02 00:23发布生成海报

站内文章 / ABAP

1664 0

点击此处---> 群内免费提供SAP练习系统（在群公告中）

加入QQ群：457200227（SAP S4 HANA技术交流）群内免费提供SAP练习系统（在群公告中）

嗨，SAPpers，

我想在此博客中描述的方法并不新颖，ABAP专家可能已经知道它了，但是许多初学者经常问有关此主题的问题，因此此博客出现了。所描述的方法不是全面的Excel解析解决方案，而是当客户要求快速解决方案且截止日期为“昨天”时的生命。

问题定义

你们中的任何一个我肯定都面临着Excel文件处理这样的常见任务，该任务通常在OData服务，Webdynpro应用程序，FPM应用程序等不同的SAP环境中提出。通常，您拥有的所有内容都是XLSX文件和/或由此生成的XSTRING数据，对结构一无所知，因此需要快速解析表内容以在应用程序中进行处理。您将如何进行？

SAP提供了许多与Excel相关的编程工具，它们在此方便的页面上列出：

https://wiki.scn.sap.com/wiki/display/ABAP/Excel+with+SAP+-+An+总览

为什么不使用它们？这里有几个星号：

几乎所有标准Excel阅读工具都基于OLE，并且不允许以批处理或非对话模式阅读
其余所有都是第三方的，并且像PI库一样高度特定

有人会说：如果我们已经有了ABAP2XLSX，为什么还需要另一种工具？

ABAP2XLSX当然是一个强大的功能强大的工具，我在项目中也曾使用过它，但安装时经常遇到困难。一些客户断然拒绝向系统安装自定义软件包，一些客户对许可证问题感到困扰，有些人对第三方开放，但在大型跨国恐龙（如我的公司）中，批准将第三方安装到ABAP系统中将需要几个月的时间。现在正在工作。

而且，当完全需要Excel解析时，由于所有的截止日期都已结束，并且预期是在“昨天”，所以在这种紧急情况下，我向客户提出了我将在此处描述的解决方案，因此不能真正称为解决方案，因为有很多缺陷，但是它可以完成基本工作并将Excel文件转换为ABAP中的内部表。我邀请您将其视为概念验证而非解决方案，但是它功能齐全，可以轻松（或不太容易）适应您的需求。

方法的历史

当我分析该问题的可能解决方案时，我考虑了很多候选人，但是有原因不能满足我的需求。我甚至回顾了古代的时间片段（2009年是COVID之前的lon），其中一些很酷，例如来自久负盛名的 ABAP战士Naimesh Patel的那段时间，是的，Naimesh提供的XML生成速度很快并且具有简单性，但是格式本身在设计上逊色。尽管Spreadsheet ML的名称不是一个功能齐全的电子表格，但它只是XML的一个子集，能够表示表格数据，但有很多限制。

还有一个了不起的一件作品所做马杜奥马尔，老老实实我非常所做的工作印象深刻，但我的任务，它似乎过于复杂和仅接触XLSX一代的一部分，我急匆匆地寻找一种方式来解析。而且，她在工具中使用的iXML库在性能方面存在一些缺陷，因此不适用于大数据。

Rajesh Rajgor进行了另一项获得相同结果的良好尝试，与Madhu的Simple Transformations不同，他使用ABAP字符串模板直接构造XML模板。不幸的是，这种方法不是很灵活，并且它只是一个Spreadsheet ML，而不是一个成熟的XLSX电子表格，因此他的工作只是对Naimesh旧转换器的现代反思。

我发现真正有趣的是Trevor Zhang的导入/导出解决方案，该解决方案使用现代类和现代简洁语法编写。他们说，现代问题需要现代解决方案。

我不知道ABAP AS 752引入的cl_ehfnd_xlsx 类，但在Web上它的信息仍然很少，因此似乎更像是内部S4HANA内容，不适合客户使用。无论如何，这是一个选择，但是，是的，它需要752版本，我们的系统仍然是750版本，所以我再一次走运了：（（

解析器的组成部分

让我简要概述一下.XLSX解析的基本要素，在开始实现之前必须了解这些基本要素：

实际上，XLSX格式不是单个文件，而是定义Excel工作簿外观的一组文件。这与旧的.XLS（它是二进制不可提取的容器）和原始SpreadsheetML（在单个XML中定义工作表）有根本的不同。
XLSX文件的主要停用部分是

工作表文件（sheet1.xml，sheet2.xml等），它们包含用于在工作表上放置数据的标记
共享字符串文件（sharedStrings.xml），它包含重复数据删除的值数组
样式定义（styles.xml），用于定义工作表单元格的外观
工作簿文件（workbook.xml），它在其中建立工作簿和工作表的结构
很多其他的…

对于解析任务，我们仅对前两个感兴趣：sheet1.xml和sharedStrings.xml， 这是在ABAP中成功重新创建表的绝对最低要求。

所述sheet1.xml文件描述横跨片材的数据结构，它的核心部分是<sheetData>

<sheetData><row r="1" x14ac:dyDescent="0.25" spans="1:107"><c r="A1" t="s"><v>21</v></c><c r="B1" t="s"><v>22</v></c><c r="C1" t="s"><v>23</v></c></row></sheetData>

共享数据只不过是一个以字符串格式存储的Excel工作表值数组：

<sst count="309" uniqueCount="83">
	<si>
		<t>MANDT</t>
	</si>
	<si>
		<t>CARRID</t>
	</si>
	<si>
		<t>CONNID</t>
	</si>
	<si>
		<t>COUNTRYFR</t>
	</si></sst>

让我分享一下我从XLSX文件中获取表格并将其转换为ABAP itab的步骤：

将工作表结构（sheet1.xml）提取到XSTRING XML中
提取工作表值的数组（sharedStrings.xml）
通过ST转换将两个XML转换为内部表
通过将工作表文件中的索引与值数组进行映射来构造结果内部表

足够多的文字，让我们跳到代码。

主解析类xlsx_reader：

CLASS xlsx_reader DEFINITION.
  PUBLIC SECTION.
    METHODS:  read IMPORTING file  TYPE string                             first TYPE abap_bool
                             ddic TYPE string                   EXPORTING tab  TYPE REF TO data,
              extract_xml IMPORTING iv_xml_index TYPE i
                                    xstring      TYPE xstring                   RETURNING VALUE(rv_xml_data)  TYPE xstring.ENDCLASS.CLASS xlsx_reader IMPLEMENTATION.
  METHOD read.
        TYPES: BEGIN OF ty_row,
             value TYPE string,
             index TYPE abap_bool,
           END OF ty_row,
           BEGIN OF ty_worksheet,
             row_id TYPE i,
             row    TYPE TABLE OF ty_row WITH EMPTY KEY,
           END OF ty_worksheet,
           BEGIN OF ty_si,
             t TYPE string,
           END OF ty_si.
    " Excel varaibles
    DATA: data  TYPE TABLE OF ty_si,
          sheet TYPE TABLE OF ty_worksheet.
    " RTTS variables
    DATA: lo_struct TYPE REF TO cl_abap_structdescr,
          table     TYPE abap_component_tab.
    FIELD-SYMBOLS: <table> TYPE STANDARD TABLE.
    TRY. " loading XLSX zip from file
        DATA(xstring_xlsx)  = cl_openxml_helper=>load_local_file( file ).
      CATCH cx_openxml_not_found.
    ENDTRY.
    "Read the sheet XML
    DATA(xml_sheet) = extract_xml( EXPORTING xstring = xstring_xlsx iv_xml_index = 2 ).
    "Read the shared data XML
    DATA(xml_data)  = extract_xml( EXPORTING xstring = xstring_xlsx iv_xml_index = 3 ).
    TRY.
      " transforming sheet structure into ABAP
      CALL TRANSFORMATION zsheet        SOURCE XML xml_sheet        RESULT root = sheet.
      " transforming shared data into ABAP
      CALL TRANSFORMATION zxlsx        SOURCE XML xml_data        RESULT root = data.
      CATCH cx_xslt_exception.
      CATCH cx_st_match_element.
      CATCH cx_st_ref_access.
    ENDTRY.
  DATA(header_line) = VALUE #( sheet[ 1 ]-row OPTIONAL ).
  IF first IS NOT INITIAL AND header_line IS NOT INITIAL. "building itab from first line
    table = VALUE #( BASE table FOR ls_key IN header_line                              ( name = data[ ls_key-value + 1 ]-t                                type = CAST #( cl_abap_datadescr=>describe_by_name( VALUE #( data[ ls_key-value + 1 ]-t OPTIONAL ) ) )
                               )
                            ).
    DELETE sheet INDEX 1.
  ELSE. "building itab of strings
    DELETE header_line WHERE value IS INITIAL.
    DO lines( header_line ) TIMES.
      APPEND VALUE #( name = 'field' && sy-index type = CAST #( cl_abap_typedescr=>describe_by_name( 'STRING' ) ) ) TO table.
    ENDDO.
  ENDIF.
  " creating structure from DDIC structure
  IF ddic IS NOT INITIAL.
      lo_struct ?= cl_abap_structdescr=>describe_by_name( ddic ).
    ELSEIF table IS NOT INITIAL.
    " create structure from previously constructed type handle
      TRY.
          lo_struct = cl_abap_structdescr=>create( table ).
        CATCH cx_sy_struct_creation .
      ENDTRY.
  ENDIF.
  " creating table from structure
  CHECK lo_struct IS BOUND.
  DATA(dyntable_type) = cl_abap_tabledescr=>create( p_line_type = lo_struct ).
  CREATE DATA tab TYPE HANDLE dyntable_type.
  ASSIGN tab->* TO <table>.* mapping structure and data
    LOOP AT sheet ASSIGNING FIELD-SYMBOL(<fs_row>).
      APPEND INITIAL LINE TO <table> ASSIGNING FIELD-SYMBOL(<line>).
      DELETE <fs_row>-row WHERE value IS INITIAL.
      LOOP AT <fs_row>-row ASSIGNING FIELD-SYMBOL(<fs_cell>).
        ASSIGN COMPONENT sy-tabix OF STRUCTURE <line> TO FIELD-SYMBOL(<fs_field>).
        CHECK sy-subrc = 0.
        <fs_field> = COND #( WHEN <fs_cell>-index = abap_false THEN <fs_cell>-value ELSE VALUE #( data[ <fs_cell>-value + 1 ]-t OPTIONAL ) ).
      ENDLOOP.
    ENDLOOP.
  ENDMETHOD.
  METHOD extract_xml.
    TRY.
        DATA(lo_package)  = cl_xlsx_document=>load_document( iv_data = xstring ).
        DATA(lo_parts)    = lo_package->get_parts( ).
        CHECK lo_parts IS BOUND AND lo_package IS BOUND.
        DATA(lv_uri)      = lo_parts->get_part( 2 )->get_parts( )->get_part( iv_xml_index )->get_uri( )->get_uri( ).
        DATA(lo_xml_part) = lo_package->get_part_by_uri( cl_openxml_parturi=>create_from_partname( lv_uri ) ).
        rv_xml_data       = lo_xml_part->get_data( ).
      CATCH cx_openxml_format cx_openxml_not_found.
    ENDTRY.
  ENDMETHOD.ENDCLASS.

图纸文件的转换方式

<?sap.transform simple?><tt:transform xmlns:tt="http://www.sap.com/transformation-templates" template="main">
  <tt:root name="root"/>
  <tt:template name="main">
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3">
      <tt:skip count="4"/>
      <sheetData>
        <tt:loop name="row" ref="root">
          <row>
            <tt:attribute name="r" value-ref="row_id"/>
            <tt:loop name="cells" ref="$row.ROW">
              <c>
                <tt:cond><tt:attribute name="t" value-ref="index"/><tt:assign to-ref="index" val="C('X')"/></tt:cond>
                <tt:cond>
                  <v>
                    <tt:value ref="value"/>
                  </v>
                </tt:cond>
              </c>
            </tt:loop>
          </row>
        </tt:loop>
      </sheetData>
      <tt:skip/>
    </worksheet>
  </tt:template></tt:transform>

共享字符串的转换zxlsx

<?sap.transform simple?><tt:transform xmlns:tt="http://www.sap.com/transformation-templates" template="main">
  <tt:root name="ROOT"/>
  <tt:template name="main">
      <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <tt:loop name="line" ref=".ROOT">
          <si>
            <t>
              <tt:value ref="t"/>
            </t>
          </si>
        </tt:loop>
      </sst>
  </tt:template></tt:transform>

示例调用程序：

START-OF-SELECTION.PARAMETERS: p_file TYPE string LOWER CASE DEFAULT `C:\table.xlsx`.SELECTION-SCREEN BEGIN OF BLOCK out WITH FRAME TITLE text-s01.SELECTION-SCREEN BEGIN OF LINE.
  SELECTION-SCREEN COMMENT 1(25) text-002.
  PARAMETERS: p_hdr  TYPE xfeld MODIF ID hdr USER-COMMAND hdr.
  SELECTION-SCREEN COMMENT 30(25) text-001.
  PARAMETERS: p_ddic TYPE string MODIF ID dic.SELECTION-SCREEN END OF LINE.SELECTION-SCREEN END OF BLOCK out.AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  p_file = cl_openxml_helper=>browse_local_file_open( iv_title = 'Select XLSX File' iv_filename = '' iv_extpattern = 'All files(*.*)|*.*' ).AT SELECTION-SCREEN OUTPUT.
  IF p_hdr = abap_true.
    DATA(imp) = 1.
    CLEAR: p_ddic.
  ELSE.
    imp = 0.
  ENDIF.
  LOOP AT SCREEN.
    CASE screen-group1.
      WHEN 'DIC'.
      SCREEN-input = COND #( WHEN imp = 1 THEN 0 ELSE 1 ).
    ENDCASE.
    MODIFY SCREEN.
  ENDLOOP.
  AT SELECTION-SCREEN.
    FIELD-SYMBOLS: <fs_out> TYPE ANY.
    IF sy-ucomm = 'ONLI'.
        DATA(reader) = NEW xlsx_reader( ).
        reader->read( EXPORTING file = p_file first = p_hdr ddic = p_ddic IMPORTING tab = DATA(tab) ).
        ASSIGN tab->* TO FIELD-SYMBOL(<table>).
    ENDIF.

使用样本

该程序的想法是，用户有两个解析选项：解析为通用字符串表或手中接收完整类型的内部表。可以在选择屏幕上（DDIC结构输入框）显式指定类型，或者程序可以从Excel表的第一行派生类型，前提是该表中填充有数据元素名称（使用第一行作为结构复选框）。

第一个线型表MARC的样本输入（列的子集）

然后输出到全类型的itab

明确指定的DDIC结构KALC的样本输入

请注意，尽管“数字存储为字符串”值，但我们在ABAP中收到了全类型表，甚至FLTP值也被正确存储

最后是转换为字符串表的最简单情况

结论

关于当前示例实现的局限性的几点说明：

它仅解析此变体中的第一张表（通过添加几行代码即可轻松解决）
它不尊重Excel工作表中的空白列
日期无法正确识别，因为在sharedStrings.xml中它们以大纪元格式存储
如果用户区域设置与发送文件的用户的设置不同，则十进制数值可能会在解析时抛出转储

归根结底，我并没有设定为所有情况提供全面工具的任务，而是向社区展示了如何以简单和标准的方式来完成它，您可以自由地根据自己的需要调整和调整班级。

现在，关于我的解决方案相对于其他方法的优势：

简洁明了，该类仅包含100行代码
绝对标准，基于CL_XLSX_DOCUMENT类，除最古老的版本（尤其是≥7.02的版本）外，几乎所有版本均可用
例如，建立在与XSLT相反的简单转换之上，例如采用统一方法。ST在大数据上的速度明显更快，它们更直观地适应客户需求，并且（最贴切！）它们是双面的，可用于序列化和反序列化。
性能。我在大量数据上测试了解析器，它的解析度足够好（在100Mb文件上运行10秒），而ABAP2XLSX解析基于CL_IXML类（iXML库），并且在大型数据集上的表现不佳。

亲爱的社区，请随时发表评论并分享您的想法！

赞赏支持