Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

luau: add advanced Excel writer helper functions #1474

Open
jqnatividad opened this issue Dec 13, 2023 · 3 comments
Open

luau: add advanced Excel writer helper functions #1474

jqnatividad opened this issue Dec 13, 2023 · 3 comments
Labels
enhancement New feature or request qsv pro requires backend/cloud services

Comments

@jqnatividad
Copy link
Owner

jqnatividad commented Dec 13, 2023

With the to excel command, we already have a robust CSV to Excel exporter.

However, it doesn't allow you to:

  • format cells
  • add formulas
  • add conditional formatting
  • do merged cells,
  • add charts, etc.

Leveraging the rust_xlsxwriter crate, add Excel helper functions to the luau DSL so users can create advanced, formatted Excel spreadsheets using the qsv scripting DSL.

@jqnatividad jqnatividad added enhancement New feature or request qsv pro requires backend/cloud services labels Dec 13, 2023
@ondohotola
Copy link

ondohotola commented Dec 13, 2023 via email

@jqnatividad
Copy link
Owner Author

jqnatividad commented Dec 14, 2023

Include a reference recipe as well that creates a formatted Excel with:

  • Sheet 1 named "Data" - containing the "prettified" data with conditional formatting for alternating row colors and outlier highlighting. Also each column can have sparklines just below/above the column name showing its distribution.
  • Sheet 2 named "Data Dictionary" with an annotated Data Dictionary
  • Sheet 3 named "Summary Statistics" - with Stats
  • Sheet 4 named "Frequency" with Frequency tables
  • Sheet 5 named "Charts" with different "smart" visualizations using stats, frequency, etc.

@jqnatividad
Copy link
Owner Author

jqnatividad commented Dec 22, 2023

Per DOI-DO/dcat-us#131, also add a "Machine-readable" sheet as the last sheet in the workbook.

The Machine-readable sheet will have all the relevant metadata elements for the package/resource, and should hew closely to the configured DCAT profile (use DCAT-US 3.0 for the initial reference implementation).

The format of the "Machine-readable" sheet should be as follows:

  • The first few rows of the sheet will be the "metadata header" section. The first row will be "# METADATA-BEGIN"
  • All metadata elements will be key-value pairs, with each kv pair on its own line, with the key and value separated by a colon. The line starts with the "#" symbol (e.g. # Title:"The title of the dataset")
  • The "metadata header" section ends with a line with "# METADATA-END"

After the metadata header section, a "normal", RFC-4180 compliant CSV file follows. The CSV file will be the machine-readable version of the "Data" in Sheet 1.

The recipe can then be called from Datapusher+ when populating the CKAN datastore.

Data exported from CKAN can also invoke the recipe to create an "Annotated Excel export" of a dataset.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request qsv pro requires backend/cloud services
Projects
None yet
Development

No branches or pull requests

2 participants