Export to Excel Formatting

Question

I have set up a basic html table with a reader in each cell (don't ask) that exports to excel, I also have a datagrid that exports to excel. Both work without issue in regards to actually creating the spreadsheet, but I have a question regarding formatting. Inside each cell is a list of names, i.e.:

Bob Smith Jim Bob John Miller Susie Q Jane Doe

When the spreadsheet comes up it places each name in a separate "sub cell" instead of having it all in one cell. Is there a way I can make it so that the names in each html table cell are in a single Excel cell?

Answer 1

You could try surrounding them with quotes, e.g. "Bob Smith".

Answer 2

It's probably the <br> which is creating the sub-cells. Try putting "& chr(13)" or "& vbCrLf" or even "\r\n" instead.

Excel is a complete crapshoot when it comes to formatting stuff. So, I often avoid the headache of exporting directly to excel and providing a view of the report which is copy and pastable into excel instead.

For example, I had a report which my users could click a button to view in 3 different ways:

1. A YUI enhanced html table with sorting, column resizing etc. (Great for browser but tricky to paste properly into excel)
2. A simple HTML table which the users could copy and paste into excel very easily. With an html table each <TD> corresponds to a cell and <tr> is a row.
3. A CSV view which could also be pasted into excel or into notepad.

All i did was write a format method which I could pass in as parameters what the header prefix/suffix, row prefix/suffix, col prefix/suffix, and table prefix/suffix. I'd pass the appropriate params into the format method based on a GET param.