-1

What solutions exist (and what are the pros/cons) for including free-format, rich text data in an Excel file (along side the normal tabular data)?

This is the kind of data I'd like to include (in a separate worksheet): example of non-tabular data

Note that we're currently using openpyxl to generate Excel files from Python. We can use something other than Python/OpenPyXL if necessary, but keeping with Excel is a must (the accountants who use these reports won't use anything else).

Community
  • 1
  • 1
eddiewould
  • 1,308
  • 14
  • 32
  • Does it really have to be Excel? I like to combine tabular data, plots, documentation, and reports in IPython notebooks (you can print neat tabular data via pandas for example). Or what about CSV files? I like them a lot better than the proprietary Excel format - everyone can open and work with them, and you can also open them in Excel; here, you could add the descriptions, reports as "comments" –  Jul 06 '14 at 21:37
  • Yeah it has to be Excel - the accountants won't use anything else. Looking for something a little richer than what CSV would allow anyway - basically, looking for something similar to HTML/CSS or FO-PDF within a Excel file - i.e. something document centric. So multiple sections, bullet points, lists, headings, horizontal rules, that kind of thing. – eddiewould Jul 06 '14 at 21:38
  • 2
    This question is far too broad - if you had a *specific* question about openpyxl, that would be one thing, but as it is, it wants opinions rather than a solution to a particular problem – Ajean Jul 06 '14 at 21:57
  • Yes, @Ajean is right. It would be helpful if you would provide a rough example of how your input data looks like and maybe a screenshot of how an example Excel result file would look like. And maybe your current approach so far. In general, StackOverflow is more about giving you tips and providing suggestions/improvements. But people don't like to do "ALL" the work for you :) –  Jul 06 '14 at 22:00
  • I included openpyxl because that's what we happen to be using currently for rendering Excel files. The question is really "what solutions exist for representing document-oriented data within an Excel file". Since we're currently using OpenPyXl for rendering, solutions that can be leveraged from OpenPyXL would be preferred, but we can look at switching to using something else for rendering if necessary. – eddiewould Jul 06 '14 at 22:02
  • @eddiewould I still think even that is too broad, and since the main question only involves Excel and not any programming, it probably would be better posted on SuperUser. You'll probably get more responses there also. – Ajean Jul 06 '14 at 22:07
  • I encountered this issue some time ago and spent 2-3 entire workdays researching it. IIRC, my conclusion was that yes it's possible to apply different rich-text styles to parts of a text within a cell, but the only way to do it in Python is through making calls to the [Windows API](http://msdn.microsoft.com/en-us/library/bb149081(v=office.12).aspx). No Python libraries support applying rich-text styles to parts of a cell. – DBedrenko Jul 08 '14 at 07:14

1 Answers1

1

The Excel specification makes it pretty tricky to do what you want. Currently, the smallest unit that you can apply styles to in openpyxl is a cell. As long as you can work with that restriction, you should be okay. Outlining is also supported.

Charlie Clark
  • 14,534
  • 3
  • 37
  • 42
  • That's helpful. One possibility I considered was generating then embedding a PDF into a worksheet. That felt like a huge hack though. In the end, I decided to insert a hyperlink to our web server (from where the Excel report was generated) providing access to the non-tabular data (which is typically meta-data). – eddiewould Jul 07 '14 at 20:49
  • Seems like a good solution. More extensive formatting is possible - you might like to try and see what is supported in a round-trip - but I don't consider it high on the list of priorities. – Charlie Clark Jul 08 '14 at 09:51