Powerbuilder - Keep Column names when saving as Excel format

Question

I am kind of new to PowerBuilder and I'd like to know if it was possible to keep the "visible" value of a column name when using the SaveAs() Method of my DataWindow. Currently, my report shows columns like "Numéro PB" or "Poste 1-3", but when I save, it shows the Database's names. ie: "no_pb" and "pos_1_3"...

As I am working on a deployed application, I have to make my changes and implementations As user-friendly as possible, and they won't understand anything of that.

I already use the dw2xls api to save an exact copy of the report, but they want to have an option saving only the raw data, and I don't think I can achieve it using their API.

Also, I was asked not to use the Excel OLE object to do it...

Anyone's got an idea?

Thanks, Michael

Answer 1

dw.saveas(<string with filename and path>,CSV!,TRUE) saves the datawindow data as a comma separated value text file with the first row having the column headers (database names in the dw painter).

To set the column headings in a saveas you could first access the data with

any la_dwdata[]              // declare array   
la_dwdata = dw_1.Object.Data // get all data for all rows in dw_1 in the Primary! buffer

from here you would create an output file consisting initially of a series of strings along with the column names you want and then the data from the array converted to a string (you loop through the array). If you insert commas between the values and name the file with the 'CSV' extension, it will load into Excel. Since this approach will also include any non visible data, you may have to use other logic to exclude them if the users don't want to see it.

So now you have a string consisting of lines of data separated by tabs along with a crlf at the end of each. You create your 'header string' with the user friendly column names in the format of 'blah,blah,blah~r~n' (this is three 'blah' strings separated by commas with a crlf at the end).

Now you parse the string obtained from dw_1.Object.Data to find the first line, strip it off, then replace it with the header string you created. You can use the replace method to replace the remaining tabs with a comma. Now you save the string to a file with a .CSV extension and you can load it into Excel

Answer 2

This assumes that your display columns match your raw columns. Create a DataStore ds_head . Set your report DW as the DataObject (no data). I'm calling the DataWindow with the report you want to save dw_report. You'll want to delete the two temporary files when you're done. You may need to specify EncodingUTF8! or some other encoding instead of ANSI depending on what the data in the DataWindow is. Note: Excel will open this CSV but some other programs may not like it because the header row has a trailing comma.

``

ds_head.saveAsFormattedText("file1.csv", EncodingANSI!, ",")
dw_report.saveAs("file2.csv", CSV!, FALSE, EncodingANSI!)
run("copy file1.csv file2.csv output.csv")