Excel output: retaining number format for rows

[md0u80c9]

Hi,

Many thanks for huxtable!

I've been using openxlsx to produce Excel sheets of output but was already considering the headache of how to output both Excel and RMarkdown together - so this package is exactly what I was after!

I have one challenge which I also faced in openxlsx and haven't really found a satisfactory solution for. I wondered if you have any bright ideas as I get the impression it will be more challenging to workaround in huxtable and I don't think my openxlsx workaround will be useable.

Basically I have a table of hospitals in each column of my table. On the rows, each row has a percentage. There is then a combined percentage, and a grade allocated between A-E.

The problem is that the presence of the A-E causes all the numeric values to become strings - rendering having it in Excel at all a bit pointless.

In openxlsx I solved this by transposing the results and then rendering the numeric rows separately to the non numeric ones. I get the impression though that this isn't really an option in huxtable as we are passing the whole table via as_Workbook, and the opportunities for transposing as far as I can see are all in the pre-export stage.

Would it be possible to perhaps use the data type of each row in the data frame to determine the row type? This might need a feature request within openxlsx to support this of course. Alternatively is there a tidy way to manage the problem with huxtable?

[md0u80c9]

OK - here's a suggestion of how it could be implemented:

• Accept a transpose option on as_Workbook (default = FALSE to maintain compatibility).
• If transpose is true, then instead of using openxlsx::write_rows to write all the data in one go, transpose one column at a time to a row and plot that (which will preserve the format).
• Optimise by checking adjacent rows for their type so that identical types can be done together.

This will give the minor headache of having to work with the spreadsheet rotated by 90 degrees until the last step, but would make mixing types in columns possible.

[hughjonesd]

I don't think a transpose option makes sense in as_Workbook. Why not write the numeric rows only as a huxtable, then add the grade A-E via openxlsx::writeData?

[md0u80c9]

I think that's the workaround I'm going to have to use. However, that then requires custom code for openxlsx versus the other output formats.

Thinking about it I wonder whether there would be a way of breaking down the writing of a huxtable to 'numeric' versus 'text' outputs. I haven't delved into the package source, but I wonder if as_Workbook itself could write the numerics separately from the non-numerics. That might also help with date outputs which I guess are similarly mangled as text.

[hughjonesd]

The issue is that huxtables are just ordinary data frames at heart. If you add a character to a column, it all becomes character. It's not optimal but nothing is... some way to mark cells as numeric might be the way forward - perhaps via number_format. -- Sent from Gmail Mobile

[md0u80c9]

Is there a way to overlay two hux tables on a sheet? Or specify coordinates to an existing sheet?

This might work for me as a workaround but maybe not as a general solution.

Set up two hux tables - one containing numerics and one non numerics. Overlay the two tables on the same workbook one after the other.

For other output formats merge the two hux tables together and use the single hux table for output.

Is it possible to specify existing worksheet, x and y coords for an outputted table? This would also be helpful for plotting multiple tables on a single sheet (but with big cavats about formatting as setting column rows etc could overwrite existing values).

[hughjonesd]

Check openxlsx docs. I think there is something. -- Sent from Gmail Mobile

[hughjonesd]

Could you check if this solves your problem (latest version in master)?