Generate XLSX files

dev
1

Hello, I have a website where HTML tables are created dynamically. Some cells are also linked. Now I would like to offer an export function with which the tables can be saved as xlsx files. I have now read that Lucee does not offer spreadsheet manipulation. So something like SpreadsheetMergeCells or SpreadsheetNew. Before I look any further. What would you recommend to generate XLSX files and offer them as a download? Cells also have to be merged.

2
3

Thank you,
I wanted to try cfspreadsheet-lucee-5 first.
I copied the lex file into the lucee-server\deploy directory as described on the website. After that, a folder was automatically created with the name “failed-to-deploy”. What kind of error is that now? I have to be honest, I’ve really had enough of Lucee. There’s always something. Things always don’t work or there’s no solution. I’m considering whether we shouldn’t switch to Adobe Coldfusion instead. That can’t be right.

4

@donald - take a look at the other option:

This is the beast and easiest way to work with spreadsheets and is actively maintained by @Julian_Halliwell and you don’t have to mess around with installing extensions.

1 Like
5

Thanks, I tested the example from their website:

<cfscript>
spreadsheet = New spreadsheetLibrary.Spreadsheet();
data = QueryNew( "First,Last", "VarChar, VarChar", [ [ "Susi", "Sorglos" ], [ "Frumpo", "McNugget" ] ] );
workbook = spreadsheet.new();
spreadsheet.addRows( workbook, data );
spreadsheet.write( workbook, "c:/temp/data.xls" );
</cfscript>

I was a bit surprised that an Excel file is written to the hard drive of the web server. I hope I can also use it to create xlsx files for visitors and that they can then save the files on their PCs.

6

If you are looking to download to the client look at the download() function that helps with this:

There is also a useful downloadFileFromQuery() function.

7

Thanks, I wanted to access the spreadsheetLibrary folder one level up. So instead of

spreadsheet = New spreadsheetLibrary.spreadsheet();

I wanted

spreadsheet = New ../spreadsheetLibrary.spreadsheet();

That way I only have to provide this folder once for all my websites. But I get an error message when I add “…/”.

8

Try using quotes around the path:

spreadsheet = New "../spreadsheetLibrary.spreadsheet"();
9

Unfortunately, that doesn’t work. I copied the spreadsheetLibrary folder there, but the website says that the resource I’m looking for cannot be displayed. If I just use “spreadsheetLibrary.spreadsheet”(); without “…/” then it works. Do I perhaps need to configure something in IIS or somewhere else?

10

Sorry, it should be

spreadsheet = New "../spreadsheetLibrary/spreadsheet"();

with a slash instead of dot in the path.

Another option would be to create an application mapping to the spreadsheet library.

11

Thank you. A few more questions. For example, if I write the value 2.4 (I use the German version of Office) into a cell, the result is 2,4. I would like to change the cell property beforehand, e.g. to text or string, so that Excel does not automatically change the cell content. Maybe this can be done using formatCell, but I haven’t found anything suitable quickly.
The other question is, how can my cell content contain a comma? Commas are used as a separator, e.g. in the addColumn function:

spreadsheet.addColumn( workbook, "2,4,test2");
12

You can specify data types when adding values. For example setCellValue takes an optional fifth type argument:

spreadsheet.setCellValue( workbook, "2.4", 1, 1, "string" );

Have a look at the documentation for more examples. Other methods, such as addRow(), also allow you to specify datatypes.

You can either specify a different delimiter…

spreadsheet.addColumn( workbook=workbook, data="2,4|test2", delimiter="|" );

…or simply pass the data in as an array instead of a list:

spreadsheet.addColumn( workbook, [ "2,4", "test2" ] );

Again I would encourage you to look at the documentation:

13

Thanks, it’s a shame that you can’t specify a type with addColum or addRow. Although you can do it again with addRows.

14

You can’t with addColumn() but you can with addRow() using the datatypes argument, the same as with addRows().

15

Actually, you can now… Version 4.2.0 just released supports this.

1 Like