Excel reports: Prototype/Research HTML tables to Excel

[clemens-msupply]

Requirements for this issue have been gathered in: https://github.com/msupply-foundation/open-msupply/issues/2059

Most of the details in this issue has been discussed in a group meeting (31/1/24).

Overall Idea:

• Reports are generally done in HTML.
• HTML is our intermediate format to export to HTML, PDF or EXCEL (In the future we might define a subset which can created/edit in a report designer UI)
• For the Excel report, parse the report HTML and extract available tables.
• Found tables are then translated to Excel (on the remote server)
• If needed the report HTML is annotated with custom HTML attributes to style Excel files
• If it's not possible or too difficult to define HTML and EXCEL in the same file we need the option to have multiple report definitions (one for each target export type)
• It should be possible to use an Excel template file that is filled with data/tables from the HTML report
• Support CSV export using the same idea/code?

Example HTML annotated with custom Excel attributes:

 <table border="1"
    data-excel-work-sheet="My optional worksheet name"
    data-excel-start-column="2"
    data-excel-start-row="1"
 >
  <tr>
    <th>Company</th>
    <th>Contact</th>
    <th>Country</th>
  </tr>
  <tr>
    <td>Alfreds Futterkiste</td>
    <td data-excel-type="string">Maria Anders</td>
    <td rowspan="2">Germany</td>
  </tr>
  <tr>
    <td colspan="2">Centro comercial Moctezuma</td>
  </tr>
  <tr>
    <td colspan="2" data-excel-colspan="3"></td>
    <td data-excel-formula="=SUMIF(C1:C1, &quot;Assignments&quot;,C[0]:C[0])">Formula result</td>
  </tr>
  <tr>
    <td colspan="2" data-excel-colspan="3"></td>
    <td ss:Formula="=SUMIF(C1:C1, &quot;Assignments&quot;,C[0]:C[0])">Formula result 2</td>
  </tr>
</table>

Open questions

• Is there a Rust lib to parse HTML and css. And then match HTML AST nodes to css nodes?
• Table pagination in HTML (do we do it manually?)
• Other existing Excel templating lib or formats?
• Visual report builder libs? e.g. report bro
• How should custom attributes look like? look at Excel's xml format and maybe use same syntax?

TODOs

• [ ] #3098
• [ ] #3324
• [ ] #3099
• [ ] #3100
• [ ] Support for styling, formulas and some custom attributes

Job Code

OMS:REPT

[DhanyaHerath]

@clemens-msupply have updated with a jobcode! When you have a chance would you mind trying to estimate how long you think this work will take please?

[clemens-msupply]

The last point is a bit open-ended, so I will leave it out. For the first three points I would estimate 4-5 days.

[craigdrown]

For table pagination in HTML - for display we don't want it right? Or are you thinking of a table w 10K rows? Maybe we give a message and only send the Excel if too many rows? For printing, I'd expect users to print from Excel, but if they print from the UI then as long as we use tags on the header rows they'll repeat each page

[clemens-msupply]

Yes as far as I know this is as it already works for HTML, i.e. the table is displayed without pagination and when printed it is split onto the pages. Will test this again when implementing!

Regarding reports with many rows, this can indeed become a problem. The graphql endpoints have a hardcoded pagination limit, e.g. 5000 rows. If there are more data, we either need to be able to override this limit or implement pagination support for the report engine, i.e. do multiple queries till all rows are fetched...

Another solution would be to allow raw SQL for report. I summarized my thoughts on it in this issue: https://github.com/msupply-foundation/open-msupply/issues/2853

[regotaina]

Tested on V1.7.RC1

🧪 Testing

Setup

• [x] Desktop app
• [x] Any data file

Tests

Tested with requisitions, stock takes, it works!