javascript/excel question

[djmc48]

Hello all,

Im dynamically creating an excel spreadsheet file through javascript, using data that I queried from a database and returned to the javasript function. I can create an excel file by using the following code...

var filename = "data.xls";
var fso = new ActiveXObject("Scripting.FileSystemObject");
var file = fso.CreateTextFile(filename, true);

file.WriteLine('<table border="1" cellspacing="0">');
file.WriteLine('<th bgcolor="#00CCFF">title1</th>');
file.WriteLine('<tr><td>row1col1</td></tr>');
file.WriteLine('</table>');
...
and so on. Excel recognizes this, and brings everything up just fine. This is just a total shot in the dark, but does anyone know if there is something that I can do to a column so that excel will use a function for that column, or am I stuck manually putting in all of the functions after the script makes the file?

Thanks,

dj

[khaki]

does anyone know if there is something that I can do to a column so that excel will use a function for that columnhi dj...

you really need to explain that better.

;) k

[djmc48]

sorry, (i dont use excel often at all so its hard for me to explain :( )... what I mean is that I do know that a column in excel can have a function associated with it, such as adding all values up in that column...etc, and I was wondering if theres a way to specify that when creating the file in javascript. probably not, but its worth asking. does that help at all? im not quite sure how else to explain it...

thanks again for any help,

dj

[khaki]

I do know that a column in excel can have a function associated with it, such as adding all values up in that column actually...
no
that's not true.

a "cell" can have a function (or formula) in it that can add values.
and there are a few ways of doing it (although you are building the spreadsheet dynamically... so VBA and Named Range methods are not available).

here are some functions and formulas that you can generate dynamically:
(this goes in a "cell")

functions:
=SUM(A3:A25)
=PRODUCT(A3,A4,A5)
=AVERAGE(A3:A25)

formulas:
=A3+A4
=A3*A4
=A3+A4(/6)


Excel uses the same mathematical operators as VB.

You can use IF statements and TEXT functions, and all kinds of stuff.
But ultimately... if you are going to create spreadsheets dynamically... you should learn a little bit about Excel in order to make the resulting spreadsheet useful.

did that help at all?
;) k

[djmc48]

ok, that sounds good. thanks for your help. yeah i guess you are right about learning the excel stuff. im not really the one that is going to be using the file, but i will look into learning the syntax and so on so that i understand better whats going on. :)

thanks,

dj