ASP tables to Excel

[McKirahan]

I am using ASP to generate an HTML table
from a database. I use FSO to output a file
with an XLS extension then open it.

I can set the column width and row height;
I can set the font family, size, and weight.
I know I can include formulas in a "cell".

What else can I do when I generate the table that
will take effect when the file is opened in Excel?

I'd like to do things like autofit and sorting.

The client does allow Office Automation on
the server so I can't write to Excel directly.

Thanks in advance for any advice or links.

 

[Mike Brind]

I am using ASP to generate an HTML table
from a database. I use FSO to output a file
with an XLS extension then open it.

I can set the column width and row height;
I can set the font family, size, and weight.
I know I can include formulas in a "cell".

What else can I do when I generate the table that
will take effect when the file is opened in Excel?

I'd like to do things like autofit and sorting.

The client does allow Office Automation on
the server so I can't write to Excel directly.

Thanks in advance for any advice or links.

You are limited by the fact that what you are producing is in fact an html
file that Excel accepts, and not a genuine native Excel file. The best way
to fiddle around with what is possible is to apply things like autoformat to
an existing .xls file, then save it as html and open the result in Notepad
or an html editor. Have a look at the xml declarations and styles to see if
any of them look promising.

Don't really know what you mean by "sorting". An Excel file can't sort.
The user does this. Or do you want to provide the initial sort based on
user choice prior to generating the file? If this is the case, you will
need to provide a variable for a SORT BY clause, which would acquire its
value from a drop-down list or radio button, or some such mechanism.

 

[Mike Brind]

You are limited by the fact that what you are producing is in fact an html
file that Excel accepts, and not a genuine native Excel file. The best
way to fiddle around with what is possible is to apply things like
autoformat to an existing .xls file, then save it as html and open the
result in Notepad or an html editor. Have a look at the xml declarations
and styles to see if any of them look promising.

Don't really know what you mean by "sorting". An Excel file can't sort.
The user does this. Or do you want to provide the initial sort based on
user choice prior to generating the file? If this is the case, you will
need to provide a variable for a SORT BY clause, which would acquire its
value from a drop-down list or radio button, or some such mechanism.

"SORT BY"??!? Of course I mean ORDER BY.

Home time for me, I think...

 

[McKirahan]

You are limited by the fact that what you are producing is in fact an html
file that Excel accepts, and not a genuine native Excel file. The best way
to fiddle around with what is possible is to apply things like autoformat to
an existing .xls file, then save it as html and open the result in Notepad
or an html editor. Have a look at the xml declarations and styles to see if
any of them look promising.

Don't really know what you mean by "sorting". An Excel file can't sort.
The user does this. Or do you want to provide the initial sort based on
user choice prior to generating the file? If this is the case, you will
need to provide a variable for a SORT BY clause, which would acquire its
value from a drop-down list or radio button, or some such mechanism.

Thanks for your reply.

Yeah -- forget sorting I was thinking about what I do when in Excel..

Basically, I was thinking of all the things I do when I create
an Excel file directly from script like Autofit and PageSetup,

I'll think about developing a script that does this after the fact.

I've got a generic report writer that allows a user to select fields
from a table and sequence then, when they submit the form,
the selections are used to generate an HTML table which is
saved vis FSO and the user is given a link to open/save it.