General information
Category:
Coding, syntax & commands
Title:
Exporting a table to xls with formating
Hi,
Here's an example of how easy it is to convert a foxpro table into a nice format. P.S. this example does not contain any error handling.
FUNCTION EXP2XLS
LPARAMETER lcTablename
LOCAL XLApp, XLSheet, lnCounter, lnNumFields
tmpsheet = sys(2015)
PUBLIC &tmpsheet.
&tmpsheet. = GetObject('','excel.sheet')
XLApp = &tmpsheet..application
XLApp.Visible = .t.
SELECT(lcTablename)
* Copying to excel is a lot faster then doing it cell by cell
lcXlsFilename = ALLTRIM(SYS(2023)) + "\" + ALLTRIM(SYS(3)) + ".XLS"
EXPORT TO (lcXlsFilename) TYPE XLS
* Open it up
XLApp.WorkBooks.Open(lcXlsFilename)
XLSheet = XLApp.ActiveSheet
lnNumFields = FCOUNT(lcTablename)
* Making a nice first header line
FOR lnCounter = 1 TO lnNumFields
XLSheet.Cells(1,lnCounter).Value = Proper(FIELD(lnCounter))
XLSheet.Cells(1,lnCounter).Font.Bold = .T
XLSheet.Cells(1,lnCounter).interior.colorindex=15
XLSheet.Cells(1,lnCounter).interior.pattern=1
NEXT
* Make columns as wide as largest field value
XLSheet.Cells.Select
XLSheet.Cells.EntireColumn.AutoFit()
* Freezing the first line (column names)
XLSheet.Range("A2").Select
XLApp.ActiveWindow.FreezePanes = .T.
RETURN
Hope this will help some people doing some weird stuff our end-users love.
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only