Export to Excel with custom headers

[wgcs]

I need to create an export function from my VFP application to an excel XLS file. Another company specified the format of the XLS file, and it is like this:
The first row contains the names of each column,
The following rows are one row per record.

This seems great... just like VFP, except the column names have spaces and funny characters in them, such as:
"Registration Date", and "Risk Mgmt #"

VFP, of course, doesn't allow spaces in field names, and certainly won't allow a "#" character...
Is there any way to rename the fields as they are exported?

I'm hoping not to require Excel to be installed on my customers' computers, and therefore would like to avoid automation...

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[Mike Gagnon]

Create a cursor from your table and name your fields as required.

SELECT cDate as Date,CID as ClientID from alias() into cursor myCursor

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[wgcs]

Is there a way in VFP to name a field "Risk Mgmt #"?

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[wgcs]

What I have so far seems to work...
I create the cursor with fields of the right length, eg:

CREATE CURSOR cExport ( Risk_Mgmt__ C(20) )
COPY TO export.xls TYPE XLS

Then translate them (first occurance only, case insensitive):
lcFile = FILETOSTR('export.xls')
lcFile = STRTRAN(lcFile,'risk_mgmt__','Risk Mgmt #',1,1,1)
STRTOFILE(lcFile,'export.xls')

This seems like a real kludge, but it works.

Now, I wish that Dates from VFP would store in a prettier format than "12-May-98"

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[Nifrabar]

Hi!
Here is my approach which might meet your requirements; once a field has a caption, that caption will be used otherwise the fieldname is used.
Captions are set in the table-designer and can also hold digits like # and or @ etc.

EXPORT to &lcxFileName type XL5 &&fields &thisformset.cFieldlist

* captions as headings
oExcel = CreateObject("Excel.Application")
oExcel.WorkBooks.Open(lcFileName)
oExcel.Visible = .T.
oSheet = oExcel.ActiveSheet

lnRows = ALINES(laData, lcFields, .F., ",")
DIMENSION laData[lnRows]

lnKolommen=alen(laData,1)

FOR i=1 TO lnKolommen
  IF type(laData[i])= "C" OR type(laData[i])= "N" OR;
     type(laData[i])= "I" OR type(laData[i])= "C" OR;
     type(laData[i])= "D" OR type(laData[i])= "L"
    oSheet.Cells(1,i).Font.Bold = .T.
    oSheet.Cells(1,i).Value = thisformset.getfieldcaption(laData[i])
    lcLetter		=IIF( i > 26 , CHR( 64 + MOD( i , 26)) , CHR ( 64 + i) )
    lcExtraLetter	=IIF( i > 26 , CHR( 64 + INT( i / 26)) , "" )
    lcKolom = "'" + lcExtraletter + lcLetter + ":" + lcExtraLetter + lcLetter + "'"
    osheet.Columns(EVALUATE(lcKolom)).EntireColumn.Autofit
  ENDIF
ENDFOR

SET SAFETY &lcOldsafety

function getfieldcaption
LPARAMETERS pcField
LOCAL lcCaption,lcField,lcOldAlias
lcOldAlias=alias()
SELECT (this.basetable)
* check field existst in basetable
IF LEN(FIELD(pcField))<>0
  lcField=this.basetable+"."+pcField
  lcCaption=DBGETPROP(lcField, "Field", "Caption")
  IF EMPTY(lcCaption)
    lcCaption=lcField
  ENDIF
ELSE
  lcCaption=""
ENDIF

IF !EMPTY(lcOldAlias)
  SELECT &lcOldAlias 
ENDIF

RETURN lcCaption
endfunc

 

[Nifrabar]

Oops...

I forgot you will not use automation.
I've heard there is a 'EXCEL-DLL' which might be used by you? So no need to install EXCEL itself.

But I wonder, once third party requires EXCEL-format, they don't need EXCEL?

-Bart

 

[wgcs]

The reason for the export is so that the .XLS file can then be submitted (either through email or on a website) as a data upload.

We had no part in choosing the format for the data upload (we would choose CSV which is much lighter-weight and compatible), and the organization that designed it decided that an excel spreadsheet would be the most user-friendly format. (They intended it to be filled in by hand, but we want our software to be able to auto-create it)

Thanks for posting the Automation code, though! It seems very effective.

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[Nifrabar]

Bill, we all have (from time to time) to deal with client-requirements. I by myself do also struggle with some export to Excel matters. See thread1251-969507.
-Bart