response.ContentType="application/vnd.ms-excel" resizing spreadsheet columns width

Question

Hi,

Opening an excel spreadsheet with the response object and I am curious to know if there is a way, once all the data has been written to resize/format the columns widths so that all the data fits?

Thanks, Ron.

Answer 1

Did you mean, set a response header and then throw HTML to the client browser? MS Excel can handle HTML, but you can't store a VBA macro inside HTML, so there is no simply way to achieve this.

A possible workaround is to generate the HTML and set the column width to a value enough to show all the cell content without wrapping.

To help you achieve this, try generating a workbook in your computer desktop putting text and format similar to the report being exported, then save the workbook as a html file and then take a look at the code generated. Behind all the messy CSS/HTML autogenerated code, you will find some tips to improve the html table. By example, look at this snippet:

<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">

With this you are helping MS Excel to identify your HTML file as a Sheet. Now look in the css inside the element, you will find some custom selectors like col {mso-width-source:auto;}. Try playing with its values until you get satisfied with the results, and then incorporate it on the HTML.