Excel export of browse: Controlling column titles

[Jeff Fried]

There are several ways to export a browse to an Excel file, ranging from fully customized to exporting what you see on the browse after manipulating it.

It is the latter about which I have a question. Once a user has done a column sort and one or more quick filters, the user then wants to export to Excel. This is easily accomplished with <OBJECT>.QUICK_EXPORT() or the existing default Excel export button on the tool bar.

Here's the question about this "raw" export. Is there a way to control the column headings rather than using field names? Field names can often be unfriendly to the user, especially if you rely on your custom field titles when putting them in a form or browse view.

For example, let's say I have a field named ts_name. The field label for this is "Trade show name". When this "raw" Excel export takes place, the column title is ts_name. I would like it be be "Trade show name". I don't mind creating a custom export for this, but if I do this, then I don't know of a way to let the custom export also export what the user sees as the result of their sorting and filtering.

Any ideas? Or is neither of these possible in your opinion?

Thanks.

Jeff

[Peter.Greulich]

Re: Excel export of browse: Controlling column titles

Yes, you can. v11 does this by using it's .NET functionality. But the good news is, that this has already been built into v10. Search the v11 help (or WIKI) for "Excel". There's a lot of help on this and many examples. Just make sure you have the last v10 patch - I don't know at what point they introduced this methodology into v10, but it might not work in earlier builds. This is very cool functionality.

[Jeff Fried]

Re: Excel export of browse: Controlling column titles

Yes, you can. v11 does this by using it's .NET functionality. But the good news is, that this has already been built into v10. Search the v11 help (or WIKI) for "Excel". There's a lot of help on this and many examples. Just make sure you have the last v10 patch - I don't know at what point they introduced this methodology into v10, but it might not work in earlier builds. This is very cool functionality.

Well, this sounds very exciting. They're actually using v 10.5, so I assume that this revision already has what you are referring to.

Thanks Peter.

Jeff

[Jeff Fried]

Re: Excel export of browse: Controlling column titles

Yes, you can. v11 does this by using it's .NET functionality. But the good news is, that this has already been built into v10. Search the v11 help (or WIKI) for "Excel". There's a lot of help on this and many examples. Just make sure you have the last v10 patch - I don't know at what point they introduced this methodology into v10, but it might not work in earlier builds. This is very cool functionality.

Peter,

I tried to determine, with V11 loaded and updated for most recent patch, within the desktop environment, where you would specify that the field columns should have field titles rather than field names. Trying this both for the quick export to Excel as well as the custom operation, I could not find this anywhere.

Where do you accomplish this?

Thanks.

Jeff

[MoGrace]

Re: Excel export of browse: Controlling column titles

Hi Jeff,
If you created an ASCII text file with only the data (no field names), couldn't you script to add the names you want into the first record of that file and then open Excel with that file?

In the field rules for the table you have the option of adding a descriptive field name that will display on forms, you should be able to get this property for the fields you select for export.

Copied from the Object Explorer for the browse and pasted into IW
?topparent:Help_id.Object.Column_title
="Help ID"

[Jeff Fried]

Re: Excel export of browse: Controlling column titles

Hi Jeff,
If you created an ASCII text file with only the data (no field names), couldn't you script to add the names you want into the first record of that file and then open Excel with that file?

In the field rules for the table you have the option of adding a descriptive field name that will display on forms, you should be able to get this property for the fields you select for export.

Copied from the Object Explorer for the browse and pasted into IW
?topparent:Help_id.Object.Column_title
="Help ID"

Well, according to Peter G, the solution is simpler than this. He says it's already built in (somewhere) to be able to do the quick export to Excel using the form of the browse that the user has set up (sorts and filters).

I'm trying to keep this simple, and if in the end I need to end up with field names instead of field labels, so be it. Since I couldn't find anywhere what Peter was saying, I think he misunderstood what I'm trying to do.

Thanks for the ASCII approach. That certainly is a way to go, but identifying which columns and in what order to be populated in the first record has a potential set of issues.

Jeff

[MoGrace]

Re: Excel export of browse: Controlling column titles

Jeff I just took a saved browse filtered it and sorted it, then using the print options, saved it as an Excel file - it opened with my column titles and not the field names. Granted it had a couple of blank lines at the top - but it seems this is getting closer to what you want to do.

[Jeff Fried]

Re: Excel export of browse: Controlling column titles

Jeff I just took a saved browse filtered it and sorted it, then using the print options, saved it as an Excel file - it opened with my column titles and not the field names. Granted it had a couple of blank lines at the top - but it seems this is getting closer to what you want to do.

So rather than the standard Export to Excel, which I find just uses field names, you're saying that if you print to Excel it results in an Excel file which is essentially WYSIWYG of the browse. I never thought about printing to Excel. I'm going to try this now and get back.

Jeff

[MoGrace]

Re: Excel export of browse: Controlling column titles

The print genie also lets you copy the Xbasic which you can put in a button on the browse toolbar.

[Jeff Fried]

Re: Excel export of browse: Controlling column titles

The print genie also lets you copy the Xbasic which you can put in a button on the browse toolbar.

Thanks for all of this. Suddenly I need to wait to experiment with this, but the big idea is printing rather than exporting. I'll try formulating this when I have a chance. And yes, I know I can just create a custom Excel export button that prints rather than exports, and this may be the way to get the friendlier field labels.

Thanks for your help.

Jeff

[Peter.Greulich]

Re: Excel export of browse: Controlling column titles

Where do you accomplish this?

See the attached image. The arrow is pointing below where there are a dozen or so links w. many examples.

NET Excel.png


You can export the data as you are doing currently and then modify the spreadsheet dynamically.

e.g.

dim Doc as Office::ExcelDocument
dim Sheet as Office::Spreadsheet

dim filename as C = "C:\Users\Peter\A5\_A5_Clients\xxxxx\zzz\Sample.xlsx"

IF Doc.Load(filename)
    IF Doc.FindSheet(Sheet,"Sheet1")
        Sheet.Write(1,1,"Column1 Heading") 
        Doc.Save(filename)
    END IF
END IF

delete doc
sys_open(filename)

[Jeff Fried]

Re: Excel export of browse: Controlling column titles

See the attached image. The arrow is pointing below where there are a dozen or so links w. many examples.

[ATTACH=CONFIG]35509[/ATTACH]


You can export the data as you are doing currently and then modify the spreadsheet dynamically.

e.g.

dim Doc as Office::ExcelDocument
dim Sheet as Office::Spreadsheet

dim filename as C = "C:\Users\Peter\A5\_A5_Clients\xxxxx\zzz\Sample.xlsx"

IF Doc.Load(filename)
    IF Doc.FindSheet(Sheet,"Sheet1")
        Sheet.Write(1,1,"Column1 Heading") 
        Doc.Save(filename)
    END IF
END IF

delete doc
sys_open(filename)

Wow! That's a lot of setup to go from the simple Export to Excel toolbar icon, which essentially gives the user everything they manipulated EXCEPT the field labels, to a result that is certainly custom but provides, content-wise, what they would get from the simple Export to Excel.

I thought your original assertion about achieving field labels in the Export to Excel was something added to later V10 and V11 that would be a kind of switch that enabled field labels over field names. Once it needs programming, there are many ways to achieve the result. I'm trying to avoid programming to achieve this. Maybe the print to Excel is the answer.

Thanks.

Jeff

[Peter.Greulich]

Re: Excel export of browse: Controlling column titles

If all you are trying to do is change column headings, the code sample I gave you is quite simple. You can use action scripting to run the Excel export and then add inline xbasic by modifying the above code. But if that's too much, I guess the other methods are possible too.

[Jeff Fried]

Re: Excel export of browse: Controlling column titles

If all you are trying to do is change column headings, the code sample I gave you is quite simple. You can use action scripting to run the Excel export and then add inline xbasic by modifying the above code. But if that's too much, I guess the other methods are possible too.

Without having studied this, my instinct tells me that if a user manipulated the browse to the point of deleting and repositioning columns, then exporting to Excel based on the selected fields, sorted and filtered the way he wants, then getting the proper field labels on top of each Excel spreadsheet column seems to be the challenge. Ideally, if a simple substitution such as if fieldname="ts_name" then use "Trade Show name" would be a nice and tidy way to solve this.

Thanks.

Jeff

[MoGrace]

Re: Excel export of browse: Controlling column titles

Jeff,
I suppose it all depends on what your plans for the spreadsheet are...