I am working on a web app and I would like to export data in an Excel sheet and open this Excel file on the PC using it.
I used the Xojo example for exporting data to an Excel sheet. it works fine when I test it on my PC. If I use it on a remote server, it opens the Excel sheet on this remote server.
I would like to know if it is possible to export the data from a web app and open it into an Excel sheet on the user’s PC.
Thanks.
the users pc’s would basically have to download the produced excel spreadsheet
same as any other file on a any other web server
You can do that. Use a webfile object to provide download to user’s computer.
You can provide CSV file with string functions.
If you need a real excel file you can try the MBS XL Plugin which can read/write excel files without need of Excel app.
Here is my code: I have cut extra to make it shorter.
Dim strSQL As String
strSQL = “SELECT * FROM rencontres order by date_r, heure_r”
Dim RS As RecordSet
Dim excel As New ExcelApplication
Dim book As ExcelWorkbook
excel.Visible = True
book = excel.Workbooks.Add
excel.ActiveSheet.Name = “Transfert rencontre”
RS = Session.mSpeedDB.SQLSelect(strSQL)
excel.Range(“A1”, “A1”).value =“id”
For i As Integer = 1 To RS.RecordCount
excel.Range(“A” + str(i + 1), “A” + str(i + 1)).value = RS.Field(“ID”).StringValue
RS.MoveNext
Next
RS.close
RS = Nil
Actually, in my code, I open an Excel book and I write into it in memory.
To use a webfile object, I certainly have to write on server’s disk and download it afterward. I do not think it is possible to open directly the excel sheet?
this code uses Excel app on Windows and this may no work in Web App, especially on Linux server.
On my Windows server, Excel is installed. So, it is possible to save a file on the server and transfer it using a Webfile.
The following code made the job:
// Delete the file if it exists
Dim f as FolderItem
Dim fileName as String = “C:\Temp\Sheet” + Session.userLogin + “.xlsx”
f=GetFolderItem(fileName)
f.Delete()
// Save the file on Disk
excel.AlertBeforeOverwriting = False
excel.ActiveSheet.SaveAs(fileName)
excel.Quit
excel = Nil
Download the file to the PC
f = GetFolderItem(fileName)
If f <> Nil And f.Exists Then
XLSFile = WebFile.Open(f)
XLSFile.ForceDownload = True
ShowURL(XLSFile.URL)
End If
f = Nil
Thanks all for your answers. It really helped me.
I assume I really can’t write to excel without having it installed on the server?
you have to have a plugin/class to write to XLS and XLSX formats. The later format you could write yourself (the tech specs are on Microsoft’s website). But it will take some work.
I know MBS has a plugin for writing to XLS(X) files. it cost money and I dont know the details. Christian will have to speak up.
I think there might be one at RBLibrary but dont hold me to that. That is from the memory in the back of the head where it is flawed. Norm or someone would have to speak about that one.
sb
thanks … I’ve done it for years on the desktop, but I’m sure there’s a necessary trick on a web app.
Yes, we have a XL Plugin:
http://www.monkeybreadsoftware.de/xojo/plugin-xls.shtml
you can read/write excel files in older or newer format without Excel.
sorry, ain’t got no money.
Any other ideas? Anyone understand how the plugin works so I can build my own?
Well, the developers of libXL (the library underlying the MBS plugin) surely do, but it’s not a trivial task. There are a couple of native RB/Xojo classes. Some will write to the Excel XML format. Here’s one that appears to be FREE, though I haven’t used it personally.
wonder if I can use this class in a web app?
THanks
I would guess so, assuming you’re doing this server-side. It’s just Xojo code as far as I can tell. You might try it out first in a desktop app as it might be a bit easier to test and understand how it works that way.
Not sure I’d call Claude’s answer conclusive. It’s a great answer and I wish I could use it. However, it’s not my server, it’s Phillip Zedalis’ server. So it’s not up to me to install excel on the server.
I’ll try out Peter’s suggestion and see if I can pull it off.
an alternative is to build an xlsx file using xml classes yourself.
Or to write a CSV file.
got Peter’s suggestion to work just fine on the web without installing excel.
oy . . . still need some assistance. I’ve got add formatting to the excel writer program. If I can get the basics, I’ll figger out the rest. thanks.
[quote=106862:@John Scanlan]sorry, ain’t got no money.
Any other ideas?[/quote]
Lighttpd + PHP + PHPExcel. All completely free and I doubt you’ll find a more comprehensive library for creating spreadsheets.
If you have LibreOffice installed you can use it’s command line options to direct the generated files straight to a printer too. I used this combination to provide the reports for a large desktop project I created in Xojo for my company. I just couldn’t find a report writer I liked.
I have used the LibXL (mentioned by Christian) in a Windows app with a different language (PowerBASIC) and it works great. I was able to create a formatted Excel output file with formulas from scratch without Excel installed on the machine. For Windows it was supplied as a DLL.
It is a bit tedious since you must specify formatting (and formulas if they apply) for every cell as well as the values you want in the cell. For my app I did all of the column heading stuff first then my main loop formatted each cell and added the data values. At the end of the loop I put some formulas for totals into the final row.
If the comma delimited output is not acceptable this is an alternative … but yes … not free.
It appears Christian has done all of the hard work to get LibXL working in XOJO. I had to create a “DECLARES” file for PowerBASIC for every LibXL function because LibXL only had that for languages like C++, C#, and Delphi.