Spreadsheet to Grid Tables: is there a tool to do it?

[aborruso]

Description

Hi,
I have a table with some rowspan, as the one below.

+---------------------+----------+
| Property            | Earth    |
+=============+=======+==========+
|             | min   | -89.2 °C |
| Temperature +-------+----------+
| 1961-1990   | mean  | 14 °C    |
|             +-------+----------+
|             | max   | 56.7 °C  |
+-------------+-------+----------+

Pandoc is able to manage it and then also Quarto.

Is there a tool to convert a spreadsheet to a pandoc grid table?

Thank you

[mcanouil]

There must be tools online to convert to markdown grid tables.
Not sure you'll get answers here about those.

[aborruso]

If you got no hit after online search, there was nearly no chance at all to get one here as Quarto repository is way more specific than Internet.

It is a thought that has good logic. My logic was that there are a lot of experienced pandoc users here, who maybe had a little-known arrow in their bow.

[Steinthal]

Have a look at tableconvert.com. You can paste Excel data or load an Excel file and have it converted to different table styles (Markdown, CSV, LaTeX, rst, etc). There's also an API available.

[Steinthal]

Depending on the size of your table you could paste it into Word (maybe in landscape mode) and then use pandoc to convert the docx file to markdown+grid_tables.

[aborruso]

Have a look at tableconvert.com. You can paste Excel data or load an Excel file and have it converted to different table styles (Markdown, CSV, LaTeX, rst, etc). There's also an API available.

@Steinthal as I wrote above, I have a table with some rowspan and I want output that takes that into account, like the one below.

That site to me doesn't seem to be able to generate the table with the rowspan. I will try the Word solution now.

Thank you

+---------------------+----------+
| Property            | Earth    |
+=============+=======+==========+
|             | min   | -89.2 °C |
| Temperature +-------+----------+
| 1961-1990   | mean  | 14 °C    |
|             +-------+----------+
|             | max   | 56.7 °C  |
+-------------+-------+----------+

[aborruso]

Depending on the size of your table you could paste it into Word (maybe in landscape mode) and then use pandoc to convert the docx file to markdown+grid_tables.

Hi @Steinthal also using word, I have the output without rowspan.

Thank you

[cderv]

Not a specific answer but you could try build some pipeline maybe using some computation like R which has

• some packages to read from excel (like readxl or openxlsx)
• some packages to write to pandoc's markdown (like pander::pandoc.table(..., style = "grid")

So possibly something can be made out of your these. Possibly Python ecosystem has similar tooling 🤷

[aborruso]

Not a specific answer but you could try build some pipeline maybe using some computation like R which has

@cderv are you aware of any that take into account merged cells in input and produce an html rowspan in output?

Don't waste your time searching, just asking if you know if there is one.

Thank you very much

[Steinthal]

@aborruso, since I'm dealing with a similar question (for PDF), I followed @cderv's suggestion and came up with this solution:

---
title: "Test"
format: html
---

```{r}
#| warning: false
#| message: false
#| echo: false
#| output: false

library(ftExtra)
library(flextable)
library(openxlsx)
```

some text …

```{r}
#| warning: false
#| message: false
#| echo: false
#| output: asis

atab <- read.xlsx("<your_file>.xlsx", sheet=1, colNames = TRUE, fillMergedCells = TRUE)

ft1 <- flextable(atab) |>
  width(width = c(2.5, 1, 1.2)) |> 
  merge_v(j = "Property") |>
  theme_vanilla() |>
  mk_par(j = 2, part = "header", value = as_paragraph(" ")) |>
  align(j = 3, align = "right", part = "all")

flextable_to_rmd(colformat_md(ft1, part="body"))
```

Theme_vanilla adds some lines that may help. If you don't like it or don't need it, simply remove the line.

You need to add a header for the second column in Excel for flextable to work but it is removed with mk_par().
That solution is specifically designed for your example and needs to be more "flexible" to be used for other Excel tables as well.

[aborruso]

Wow, thank you very much @Steinthal !

It works. For me it is a bit difficult to adapt then to the real case, because I don't know R, but I know there is a way.

Thank you

[cderv]

Awesome ! thanks @Steinthal !

jupyter/jupyter_core#384 (comment)

Do you know python ? there should be the equivalent tools

• pandas can read excel files (https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html)
• Maybe https://pypi.org/project/markdown-grid-tables/ to create Markdown grid table ?
• There would be great_tables as a python package that is equivalent of R gt (similar to flextable). It would allow you to produce merged cells probably , but this is early and not yet supported. (https://posit-dev.github.io/great-tables/)
  You can open a FR there for your use case.

[aborruso]

You can open a FR there for your use case.

Good idea, thank you posit-dev/great-tables#104