1. Removed interface method ZOF_EXCEL_READER~CAN_READ_FILE
As was suggested in the comments, the method ZIF_EXCEL_READER~CAN_READ_FILE is superfluous, as the exception ZCX_EXCEL in LOAD( ) / LOAD_FILE( ) already gives this information.
This is an incompatible change! But it's unlikely that this method is used by many clients, as it didn't to anything useful. Also, the demo programs don't use it.
2. Switched the zip archive reader creation from the former "on demand" creation to a one-time creation in the LOAD method. Necessary for the next point:
3. Removed private attribute EXCEL2007 (which kept the input XSTRING): After generation of the zip archive, these data are not necessary any more.
4. Factored out Stefan Schmöcker's "alternate zip" concept into a local subclass: The object "zip" refers to a local class - which is either a proxy to CL_ABAP_ZIP, or creates a zip object of dynamic type and calls its GET method of a class dynamically (the "alternate zip" case).
Depending on the answer of my question
https://github.com/ivanfemia/abap2xlsx/issues/232#issuecomment-42115340
this abstraction could be removed in a forthcoming commit as soon as it has been clarified that CL_ABAP_ZIP works as expected in all cases.
5. Further cleaning.- Removed some comments like "ToDo: Comment this" if necessary, and also some misleading comments which gave unwanted attention to a certain defect (issue 234) and the circumstances of that defect, thereby obfuscating the far more general nature of the commented code.
There was a book in reading strings from the SST, caused by not catching the "node value" event in the main loop. Shared strings could point wrongly to the first table element (which usually contains the empty string).
Let me start by saying we really love the ABAP2XLSX project. Compared to exporting CSV files and importing them to Excel, it makes transferring data from SAP systems to Excel and vice versa so much easier.
What we did not like so far was the optimization of column width, especially because of the way this is realized in the XLSX file format (why did they not just add an attribute "optimize column width = true"?). Anyway, ABAP2XLSX being an open source project, we decided to try to bring some progress to this feature.
We ended up rewriting the method CALCULATE_CELL_WIDTH of the class ZCL_EXCEL_WORKSHEET.
We added the following features:
a) Before, the calculation of the cell width in ABAP2XLSX did not take into account the fact that Excel uses a proportional font by default. Thus, columns with many "i"s ended up too wide, while columns containing many "W"s ended up not wide enough.
We now use the function module LOAD_FONT, contained in SAP package BC. Thus, it should be available in pretty much every SAP system that runs ABAP. LOAD_FONT returns an internal table containing the width of every letter known in a font. You can upload Excel's default font Calibri using transaction SM73. Make sure to upload all four files (standard, bold, italic, bold and italic) and to use the description "Calibri", i.e. exactly the name Excel displays for the font. If you want to use other fonts in your files created by ABAP2XLSX, you can of course upload other fonts as well.
We calculate the sum of the widths of all characters in a cell, and add some cell padding, similar to the way Excel does it. If the font is not available in your SAP system, we revert back to a logic similar to the old one.
As calling LOAD_FONT for every cell took several seconds when we tested with larger tables, we store the values read from the function in a hash table. Thus, we only have to call the function once for every combination of font name and font attributes used in your Excel file.
b) XLSX stores dates in a cryptic format. For dates, the current implementation calculates the width of the cryptic values, thus, the columns end up too narrow.
Fortunately, ABAP2XLSX already contains a method to transform the cryptic value into an ABAP date. We then use ABAP's WRITE statement to create a date in the format the user is preferring, and calculate the width of this formatted date.
c) ABAP2XLSX currently does not consider auto filters. Thus, a column may end up too narrow, not showing the complete header text.
We fixed this by adding a fixed value to the cell width if an auto filter is activated. We use a fixed value because the size of the auto filter button in Excel does not depend on the font size you use.
d) ABAP2XLSX only estimates the width of the characters. Excel adds a small fixed amount to the width of the characters to create some horizontal cell padding. We added cell padding as well, as this brought the cell width even closer to the values calculated by Excel.
e) ABAP2XLSX already uses ABAP's data type FLOAT to store the width of a cell. However, CALCULATE_CELL_WIDTH used data type I (integer) to calculate the cell width before. Changing the parameter type for the parameter EP_WIDTH from I to FLOAT, and adjusting the variable used by the calling method CALCULATE_COLUMN_WIDTHS in the same way, made the calculation even more precise.
Extract the parts for loading the file from application server /
presentation server into two new private methods.
Not only is the code better readable (currently, the very important LOAD
call at the end of the method almost vanishes after all that file
loading code) - it also saves memory. Currently, when the LOAD message
is processed, not only the EXCEL_DATA xstring, but also BIN_TAB, the
auxiliary table of bytes which is retrieved from GUI_UPLOAD, is kept in
the memory as a redundant copy of the raw data, although during the
parse process it is not needed any more.
In the interface method ZIF_EXCEL_READER~LOAD_FILE, add an optional
import parameter I_FROM_APPLSERVER of type flag with default SY-BATCH,
and use this parameter, instead of SY-BATCH directly, for the decision
whether to load the file from application or from presentation server.
This will make the method more flexible, decoupling the decision "Where
to read the file from" from the circumstance "Am I in batch mode". By
making the parameter optional and using the default value SY-BATCH, all
existing client code will work as before.
In methid ZIF_EXCEL_READER~LOAD_FILE, change the import parameter
I_FILENAME from type STRING to the generic type CSEQUENCE, and move it
to a string internally where needed.
This saves the client from having to move a CHAR field to an auxiliary
STRING field, only for not having a "type conflict" when calling the
method.
