Here’s the code. I got it from someone else along the lines.
This is inside a sub where I pass a Stylegrid object as source and file as a Folderitem.
After this is called the file is launched with f.launch.
dim output as BinaryStream
dim tmp as string
dim d as date
dim i,j as integer
if file=nil then return
output = file.CreateBinaryFile(“excel”)
// sGrid - assume row 1 is the header
for i=1 to source.Cols
// replace EndOfLine with spaces
output.write ReplaceAll(source.CellText(i,1),EndOfLine, " ") +chr(9) // sample -> //sGrid1.CellText(3,i)
next
output.write chr(13)
for i=2 to source.Rows
for j=0 to source.Cols
output.write source.CellText(j,i)+chr(9) // sGrid is backwards… (col,row)
next
output.write chr(13)
next
output.close
Dim result As Integer
result = MsgBox ("Completed Exporting to Excel file: " + file.Name,64,“File Export Complete”)
And get a ton of security warning messages. I learned how to do ExcelXML in 2011 so I wouldn’t lose clients due to security issues. These days I can do XLSX too.
for my application, i create my reports in html format and then generate pdf using wkhtmltopdf. i simply use the same html with minor changes as below when i need to have the feature to export to excel or word.
SELECT CASE gExport
CASE "Word"
gHTMLstr=REPLACEALL(gHTMLstr, "[ContentType]", "<meta http-equiv=""Content-Type"" content=""application/msword;charset=utf-8"" />" )
CASE "Excel"
gHTMLstr=REPLACEALL(gHTMLstr, "[ContentType]", "<meta http-equiv=""Content-Type"" content=""application/vnd.ms-excel;charset=utf-8"" />" )
CASE ELSE
gHTMLstr=REPLACEALL(gHTMLstr, "[ContentType]", "<meta http-equiv=""Content-Type"" content=""text/html;charset=utf-8"" />" )
END SELECT
while the XML format can be done with Xojo xml classes, it may be a lot of work, so I think libXL is better choice.
and this includes placing images and handling read/write of old BIFF format.
For Mac, AppleScript provides a comprehensive dictionary of functions to control Excel. Unfortunately the documentation is next to useless. But I have used AppleScript where I want to export a tab-delimited file and also display it in Excel.
Christian’s MBS XL plug-in is my choice for writing xls and xlsx files.
If the extension for the file does not match the format Excel 2007 ( years old) and later will give a warning that the file’s extension does not match the format of the file then asks if the user wants to delete the file.
Well I can tell you this is NOT the case with EXCEL 2011 for OSX… it opens an HTML file with XLS extension with no problem, no error messages, no warnings… and the resulting EXCEL spreadsheet is exactly what I expected it to be.
FYI… Apples “Numbers” DOES NOT OPEN it as a spreadsheet… it opens it as a “text file”
[quote=246092:@Dave S]Well I can tell you this is NOT the case with EXCEL 2011 for OSX… it opens an HTML file with XLS extension with no problem, no error messages, no warnings… and the resulting EXCEL spreadsheet is exactly what I expected it to be.
FYI… Apples “Numbers” DOES NOT OPEN it as a spreadsheet… it opens it as a “text file”[/quote]
i notice that… just wonder why can’t Numbers does not open the file as a spreadsheet
Over half of my users are hospitals and health insurance related. Security settings are turned right up.
I’m not surprised that Excel 2011 OSX does not return the warning. MS probably assumes that any badness in the file would be Windows specific. Not necessarily a good assumption in my opinion but probably valid.