Formatting Excel Reports

Hi can anyone tell me if they know of any code that allows you to format ( set margins, page breaks etc.) data that is being output to an excel file.

At the moment i have got the excel output file looking exactly how i want it, only problem is that the data in the file is in groups and when printing in excel the page breaks appear right on the middle of a group so i will get half on one page and half on the next.

Oh and if anyone does have any solutions, i might need a little help implementing it because my data is arranged in 2 columns. As shown in the picture, although unlike the picture the number of rows per group changes depending on the data.

Any ideas??

edit: oh and i don't have access to Cold Fusion 7, i think i'm using 6.1

For that kind of control I think you'd need to use something like Apache POI.

ahh just had a look at poi and it seems to be overly complicated for what i want. I have to install a variety of different packages and classes on the server which i don't have access to (i would have to request that the server admin do it all). When all i really want is a page break command for excel

Remember that when you create "Excel formatted output" all you are really doing is creating an HTML table that Excel can read and render. Unfortunately since there is no HTML "page break" command I don't think there is any way to get a page break to register in Excel either. The reason that you can do this in something like Apache POI is because with POI you are not just creating an HTML table, you are creating an actual Excel binary file, which means you can do much more.

That said, you might be able to do this in CFMX 7 with their new report builder and Excel output options (in fact I think it uses Apache POI under the hood).

yeah i think cf7's reporting features would have the features i need, too bad my work wont buy cf7. Oh well i might just have to force my groups to all be 8 rows which will mean there will be 14 groups per page (i.e. an excel page is 56 rows). Only downside is that some groups will only have enough data for 5 rows leaving a lot of white space.

If it helps, you can pass over the cf codes for breaks, and Excel will recognize them.

For example, our excel reports have titles before the actual data output in the spreadsheet, and all we have to do is pass over something like:

<CFSET title = "#maintitle##chr(13)##subtitle##chr(13)##date##chr(13)#">

And that shows up in the first cell in Excel as:

Main Title
Sub Title
Date
(and then the data output)

Bah, I just reread your original post and realized this isn't what you asked for...I'm not sure how you would pass over margin or page-break info, sorry