Setting cell properties in exported Excel file

I have code to export a grid control of data out to an excel file, and then use f.launch to open the file.

However, all of the headers do not have any wrapping and it makes it hard to read and
work by customers to reformat it.

is there a way to set the text wrapping on the first row when I export out the data?

depends on how you write the excel file

If you use MBS XL Plugin to write Excel file, there is a XLFormatMBS.Wrap property to you can define a text format which wraps.

I’d rather not have to buy libXL for mac and windows just to wrrite out to excel. It’s already working I just don’t have any formatting.

and depending on how you write this out you may not need to fork out for a plugin

but that depends on how you DO write it out

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”)

its a tab delimited file
there is NO option or mechanism to do anything other than dump JUST the data

you need to write a real spreadsheet that can contain styling
http://great-white-software.com/rblibrary/index.php?main_page=index&cPath=34
one of the two excel writing options on there should

You might find the older BIFF format too obscure/unsupported by the latest editions of Excel, so I’d suggest using the XML format writer instead.

create a html file with xls file extension and launch it

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.

Maybe Simon’s Excel exporter can help

http://www.simcarsoftware.com/newfree/newxojo/index.html

what kind of security message did you get??

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”

i never have this problem with my clients.

[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.

It probably examines the contents & realizes its not a spreadsheet