Writing a function to Excel

Attached you'll find a .t file containing the user command colNumToExcelCol().

This user command is used to convert a column number (eg. 34) to an Excel column name (eg. AH).

Why would you need to do such a thing? If you are exporting data to an Excel file, and would like to have Excel do the work of sums, multiplications, or whatever, then you can use Flexsim to write a function to a cell. For instance, if you wrote out two cells to Excel, then wanted a third cell to be their sum, you could do the following:


excelwritenum(row, col, 123);
excelwritenum(row, col+1, 456);
excelwritestr(row, col+2,

concat("=SUM(",

colNumToExcelCol(col), numtostring(row,0,0),
", ",
colNumToExcelCol(col+1), numtostring(row,0,0),

")")

);

This is especially helpful if your column numbers may vary. In these cases you won't want to hard code Excel column names into your code. This user command saves you from having to do that.

Hopefully it will be useful to someone.

Ben

Ben,

In Flexsim's Excel communication DLL there are functions that already do this. Unfortunately, they are not currently exposed to the user. I've sent an email to the development team asking to make standard Flexscript commands for this functionality.

On another note, my conversion functions can only handle the number-to-string conversion for up to 256 columns. It can be changed to handle more than that if people need it. Versions of Excel prior to 2007 were limited to 256 columns. Now, with the .xslx file format, Excel can handle around 1000 columns (I think). How many of you out there are using that format, and do you really need that many columns?

A.J.

By the way, my estimate on the number of columns that Office 2007 can support was way off. It isn't 1000. It looks like it's actually 16384. Anybody out there actually want to use that many with Flexsim?

A.J.

You may want to update your underlying code to work for any number of columns. This will make it future proof, and the code is really quite simple. You can check out my user command as an example.

It just seems to me that if the functionality is going to be exposed for general use, it ought to work for general use.

Ben,

When I wrote it, supporting 256 columns was all it needed to do. But it should be easy to change. Your approach was a bit different than mine, but I think I like yours better. Thanks.

A.J.