Does anybody have an example of exporting a recordset to and excel spreadsheet.
I have the ExcelWriter from Einhugur. But if there is some other plugin that would work better I’m not against buying another plugin if necessary.
This is for a web app.
Anybody have any recommendations?
if all you care about is “data” and not formatting or formulas… just write it as an HTML table… Excel can read those directly
It should be relatively straightforward to achieve. I like Einhugur ExcelWriter because it uses enums and forces you to code properly. I haven’t experimented with the MBS offering for Excel yet. Handling the data types from the database to set the cell format would add to implementation time, but I don’t see that as being anything more than tedious. If you’d like a professionally crafted implementation feel free to reach out to me privately.
exporting to an excel xlsx raw file is quite tricky, but can be done
exporting to a csv file (of html like Dave said above) that excel can open is quite trivial.
(except the text encodings that excel treats … on its own !)
I use a Method that exports a RecordSet to any of the following: CSV, Tab-Delimited, XLSX, SQL or PDF (also XML, but I removed it). I used it for both Desktop and Web apps. I also have a Method that turns any ListBox into a RecordSet for input into this Method (if anyone wants it).
At the end I Zip anything that is not a PDF for web downloading (so the browser doesn’t try to view it).
[code]Protected Function getExportRecordSetWAD(rs As RecordSet, myType As String, f As FolderItem, downloadsFolder As String = “Downloads”, exportPrefix As String = “Export”, myTableName As String = “”) as FolderItem
#Pragma Unused downloadsFolder
'take any recordset and export the contents to a tab-delimited, CSV, XLS/XML or PDF file ready to download to the user’s browser
Dim tempTextOutputStream As TextOutputStream
Dim tempString As String
Dim g As FolderItem 'used by Web and Console plus PDF
Dim numRows As Integer
Select Case myType
Case “Export to CSV”, “Export to CSV
”, “Open as CSV”
myType = “CSV”
Case “Export to Tab-Delimited”, “Export to Tab-Delimited
”, “Open as Tab-Delimited”
myType = “TXT”
Case “Export to XLSX”, “Export to XLSX
”, “Open as XLSX”
myType = “XLSX”
Case “Export to PDF”, “Export to PDF
”, “Open as PDF”
myType = “PDF”
End Select
If f = Nil Then 'choose the default location on the server
f = CommonFolders.getDownloadFolderItemWAD(app.applicationName + “_” + exportPrefix + “.” + Lowercase(myType), True)
If f = Nil Then Return Nil
End If
select case myType
case “SQL” 'create an SQL update file
tempTextOutputStream = TextOutputStream.Create(f)
While Not rs.EOF
tempString = "INSERT INTO `" + myTableName + "` SET " + EndOfLine
for tempInt As Integer = 1 to rs.FieldCount
tempString = tempString + "`" + rs.IdxField(tempInt).Name + "` = '" + CommonSQL.getFormatWAD("MySQL", rs.IdxField(tempInt).StringValue) + "',"
if tempInt <> rs.FieldCount then tempString = tempString + EndOfLine
next
if right(tempString, 1) = "," then tempString = left(tempString, len(tempString) - 1) + ";" 'replace the final comma with a semi-colon to end the command
tempTextOutputStream.WriteLine(tempString)
rs.MoveNext
wend
case “TXT” 'create a tab delimited file
tempTextOutputStream = TextOutputStream.Create(f)
'write the headers
tempString = ""
for tempInt As Integer = 1 to rs.FieldCount
tempString = tempString + rs.IdxField(tempInt).Name + chr(9)
next
if right(tempString, 1) = chr(9) then tempString = left(tempString, len(tempString) - 1) 'remove trailing tab
tempTextOutputStream.WriteLine(tempString)
'write the rows
while not rs.EOF
tempString = ""
for tempInt As Integer = 1 to rs.FieldCount
tempString = tempString + rs.IdxField(tempInt).StringValue + chr(9)
next
if right(tempString, 1) = chr(9) then tempString = left(tempString, len(tempString) - 1) 'remove trailing tab
tempTextOutputStream.WriteLine(tempString)
rs.MoveNext
wend
tempTextOutputStream.WriteLine(tempString)
case “XLSX” 'create an XLSX file
Dim writer As ExcelWriterWorkbook = ExcelWriterWorkbook.Create(f, ZStream.GetZipEngineHandle) 'ZStream.GetZipEngineHandle gives an error
Dim sheet As ExcelWriterWorksheet = writer.AddWorksheet
numRows = 1
'write the headers
for tempInt As Integer = 1 to rs.FieldCount
sheet.CellText(tempInt - 1, 0) = rs.IdxField(tempInt).Name
next
'write the rows
while not rs.EOF
for tempInt As Integer = 1 to rs.FieldCount
sheet.CellText(tempInt - 1, numRows) = rs.IdxField(tempInt).StringValue
next
numRows = numRows + 1
rs.MoveNext
wend
'Set the autofilter on column 0 to 1 and rows 0 to 10
sheet.SetAutoFilter(0, 0, rs.FieldCount - 1, numRows)
Call writer.Close()
'case "XML" 'create an XML file with an XLS extension, so that it loads into Microsoft Excel
'Dim workbook As ExcelExporterWorkbook
'Dim sheet As ExcelExporterSheet
'Dim row as ExcelExporterRow
'
'workbook = new ExcelExporterWorkbook()
'workbook.Author = exportPrefix
'workbook.LastAuthor = exportPrefix
'workbook.Company = exportPrefix
'workbook.CreatedDate = tempDate
'
'sheet = workbook.Sheet(1) 'add a new worksheet
'
'sheet.Name = "Sheet1"
'sheet.PageLayoutView = True
'sheet.PageSetupLandscape = True
'
'row = New ExcelExporterRow
'row.AutoFitHeight = true
'
''write the headers
'tempString = ""
'for tempInt As Integer = 1 to rs.FieldCount
'row.Cell(tempInt).Text = rs.IdxField(tempInt).Name
'sheet.Column(tempInt).Width = 100
'next
'
'sheet.AppendRow(row)
'
'while not rs.EOF 'import the records into the spreadsheet
'row = New ExcelExporterRow
'row.AutoFitHeight = True
'for tempInt As Integer = 1 to rs.FieldCount
'row.Cell(tempInt).Text = rs.IdxField(tempInt).StringValue
'if str(rs.IdxField(tempInt).StringValue.val) = rs.IdxField(tempInt).StringValue then 'check if cell's value is a number
'row.Cell(tempInt).DataType = ExcelExporterCell.DataTypeNumber
'else
'row.Cell(tempInt).DataType = ExcelExporterCell.DataTypeString
'end if
'next
'sheet.AppendRow(row)
'
'rs.MoveNext
'wend
'
'workbook.Save(f)
case “CSV” 'create a comma-separated-value file
tempTextOutputStream = TextOutputStream.Create(f)
'write the headers
tempString = ""
for tempInt As Integer = 1 to rs.FieldCount
tempString = tempString + CommonStrings.getCleanTextForCSVWAD(rs.IdxField(tempInt).Name) + ","
next
if right(tempString, 1) = "," then tempString = left(tempString, len(tempString) - 1) 'remove trailing tab
tempTextOutputStream.WriteLine(tempString)
'write the rows
while not rs.EOF
tempString = ""
for tempInt As Integer = 1 to rs.FieldCount
tempString = tempString + CommonStrings.getCleanTextForCSVWAD(rs.IdxField(tempInt).StringValue) + ","
next
if right(tempString, 1) = "," then tempString = left(tempString, len(tempString) - 1) 'remove trailing tab
tempTextOutputStream.WriteLine(tempString)
rs.MoveNext
wend
case “PDF” 'create a PDF file
g = CommonFolders.getDownloadFolderItemWAD(“TempHTML.html”, True, True)
CommonFolders.doStringToFileWAD(CommonHTML.getTableHeadingsAndValuesHTML(rs, True, CommonHTML.kTableHeadPrefix, CommonHTML.kTableHeadSuffix, CommonHTML.kHeadingStyle, CommonHTML.kNormalRecordStyle), g) 'convert the recordset to HTML
f = CommonPDF.getHTMLToPDFWAD(g.URLPath, f, "Landscape")
end select
if tempTextOutputStream <> nil then tempTextOutputStream.Close
'text files are often shown in the browser, but not if they’re zipped
#if TargetWeb or TargetConsole then
select case myType
case “TXT”, “CSV”, “XLSX”
g = f.Parent.Child(f.Name + “.zip”)
if g <> nil and g.Exists then g.Delete
Call CommonZip.getZipCompressWAD(f, g, True)
If g <> Nil And g.Exists Then f = g
end select
#endif
if f <> nil And f.Exists then
Return f
end if
Return nil
Exception err
CommonOS.doHandleExceptionWAD(err, CurrentMethodName)
End Function[/code]
Have you tried XLBookMBS class in MBS Xojo XL Plugin?
We can read/write older and newer Excel file formats with LibXL.
@Christian Schmitz
Are there any examples? This needs to handle blob text fields, can it handle those?
@David Cox
Are you using any library? Could you post a project? Same question will it handle blob text fields?
I use the Einhugur and MBS plugins. There is a Method called above to create a temporary Download folder with the file in it easy, and one to escape any dodgy text characters, and another to turn HTML into a PDF (not asked by the OP), all easily added but I can post if you want.
No support for BLOB fields and I’m not sure why you would want to export BLOBs to Excel.
MBS Plugin comes with examples.
You can even add pictures and place them into cells.
@Christian Schmitz
I don’t see an XLMbs plugin or examples. Unless it’s call MBS Xojo XL Plugin?
Where are they located?
Did you download our plugin?
Look in examples/XL folder?
They are also listed here:
https://www.monkeybreadsoftware.net/plugins-mbsxlplugin.shtml
You need a seperate license though and if I’m using Mac to develop a web app, I will need 2 correct?
Yes, you may need a Linux for the server and optionally a Mac one for development.
See LibXL price list.