|
Home > Archive > Programming with dBASE > June 2005 > Generate a non-Crystal report
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Generate a non-Crystal report
|
|
|
| I am trying to come up with a solution that will allow my 5.7 app to produce reports without the built in Crystal 3.0 engine.
Is it possible to programatically create and direct output to an MS Excel, Word or Adobe pdf file, then open this on the user's desktop?
I realize that rewriting this 5.7 app to a dBPlus 32 bit app would solve this problem, however, I will eventually re-write this as a web app and I need to come up with a fast solution for an existing vdB 57 customer.
| |
| Rick Gearardo 2005-06-15, 1:23 pm |
|
> Is it possible to programatically create and direct output to an MS Excel,
> Word or Adobe pdf file, then open >this on the user's desktop?
Yes it is. Look in the dde-ole-dll-winapi newsgroup. Also try to find
Excel.how on the dBase website.
To get you started:
x = new oleautoClient("excel.application")
You may have to use the actual version number of Excel depending on the
operating system and how Excel was installed. If you search the registry for
"excel.application" it will show you, for example:
x = new oleautoClient("excel.application.8")
x.sheetsInNewWorkbook = 1
x.workbooks.add()
x.visible = true
a = x.activeSheet
o = a.cells(1,1)
o.font.bold := true
o.formula = "My column name"
n = 2
r.first()
do while .not. r.endOfSet
o = a.cells(n,1)
o.formula := r.fields["myFieldname"].value
n++
r.next()
enddo
a.columns.autofit()
x.sheets.add()
a = x.activeSheet
etc.
The following would write your file to excel:
for i = 1 to r.fields.size
o = a.cells(1,i)
o.font.bold := true
o.formula := r.fields[i].fieldName
next
n = 2
r.first()
do while .not. r.endOfSet
for i = 1 to r.fields.size
o = a.cells(n,i)
o.formula := r.fields[i].value
next
n++
r.next()
enddo
If you need to do something and can't figure it out: open Excel and start a
macro, do what you want to do in Excel, stop the macro and then step into
it. This will show you the VBA statements required by Excel. The dBase
statements are not always an exact match and alot of assignments are
constants. For example:
x.pageSetup.orientation = xlLandscape
xlLandscape is a constant (2)
x.pageSetup.orientation = 2
If you hold your cursor over the constant name it will tell you its value.
Rick
| |
|
| Thank you Rick. However this appears to be dBPLus code. Is there a vdB5.7 version of this code available?
Thank you
John
Rick Gearardo Wrote:[color=darkred
]
> Yes it is. Look in the dde-ole-dll-winapi newsgroup. Also try to find
> Excel.how on the dBase website.
>
> To get you started:
> x = new oleautoClient("excel.application")
>
> You may have to use the actual version number of Excel depending on the
> operating system and how Excel was installed. If you search the registry for
> "excel.application" it will show you, for example:
>
> x = new oleautoClient("excel.application.8")
> x.sheetsInNewWorkbook = 1
> x.workbooks.add()
> x.visible = true
> a = x.activeSheet
> o = a.cells(1,1)
> o.font.bold := true
> o.formula = "My column name"
> n = 2
> r.first()
> do while .not. r.endOfSet
> o = a.cells(n,1)
> o.formula := r.fields["myFieldname"].value
> n++
> r.next()
> enddo
> a.columns.autofit()
> x.sheets.add()
> a = x.activeSheet
> etc.
>
> The following would write your file to excel:
>
> for i = 1 to r.fields.size
> o = a.cells(1,i)
> o.font.bold := true
> o.formula := r.fields[i].fieldName
> next
>
> n = 2
> r.first()
> do while .not. r.endOfSet
> for i = 1 to r.fields.size
> o = a.cells(n,i)
> o.formula := r.fields[i].value
> next
> n++
> r.next()
> enddo
>
> If you need to do something and can't figure it out: open Excel and start a
> macro, do what you want to do in Excel, stop the macro and then step into
> it. This will show you the VBA statements required by Excel. The dBase
> statements are not always an exact match and alot of assignments are
> constants. For example:
>
> x.pageSetup.orientation = xlLandscape
>
> xlLandscape is a constant (2)
>
> x.pageSetup.orientation = 2
>
> If you hold your cursor over the constant name it will tell you its value.
>
> Rick
>
>
| |
| Rick Gearardo 2005-06-16, 3:23 am |
| Sorry,
You're right. The autoclient and Excel syntax are the same, you just need to
change the dBase code.
x = oleAutoclient("excelapplication")
x.visible = true
a = x.aciveSheet
etc.
sele 1
use myFile
go top
n = 1
do while .not. eof()
o = a.cells(n,1)
o.formula = myFile->myField
n = n + 1
skip
enddo
Rick
> Thank you Rick. However this appears to be dBPLus code. Is there a
> vdB5.7 version of this code available?
| |
|
| This works very well! Thank you, Rick.
I have some moderately formatted 1 page summary reports with graphics and outlined cells that I would like to duplicate in an Excel format. The easiest way would be to use a pre-existing Excel worksheet .xls or template .xlt and just plug the values into
pre-determined cells.
Can a specific, named Excel worksheet be programatically selected, loaded and populated with data? If so, what would be the syntax?
Many Thanks!
John
Rick Gearardo Wrote:
> Sorry,
>
> You're right. The autoclient and Excel syntax are the same, you just need to
> change the dBase code.
| |
|
| Is there a list somewhere that contains the dB5.7 syntax to control certain functions in an Excel worksheet, like font attributes, outlines, cell color, page layout, etc?
Thanks again!
Rick Gearardo Wrote:
> Sorry,
> You're right. The autoclient and Excel syntax are the same, you just need to
> change the dBase code.
> x = oleAutoclient("excelapplication")
> x.visible = true
> a = x.aciveSheet
> etc.
| |
| Michael Nuwer [dBVIPS] 2005-06-22, 1:23 pm |
| John wrote:
>
> Can a specific, named Excel worksheet be programatically selected, loaded and populated with data? If so, what would be the syntax?
>
The following are a few thing I've done:
oExcel = new oleAutoclient("Excel.Application")
cFileName = "myTemplate.xlt"
cHomeDir = set( "dire" ) + "\" + cFileName
oExcel.workbooks.open(cHomeDir)
cCol = "B"
nOffSet = 20 // start at row
for i = 1 to 10
cRange = cCol + ltrim(str(i+nOffSet)
)
oExcel.Range( cRange ).Select()
oExcel.activecell.formula = i
endfor
oExcel.Range( "A104").Select()
oExcel.activecell.formula = _app.header
oExcel.Range( "D104").Select()
oExcel.activecell.formula = _app.header2
oExcel.Range( "A1" ).Select()
if lower(right(cFileNam
e,3))='xlt'
cFileName = putFile("Save As","",".xls")
oExcel.ActiveWorkbook.SaveAs(cFileName)
else
oExcel.ActiveWorkbook.Save()
endif
oExcel.workbooks.close()
//
//oExcel.close(" e:\2000_seniorsurvey
\ChartData.xls")
// oExcel.visible = .T.
| |
|
| This works great! Thank you Michael.
I keep getting stuck on little things such as the correct vdB5.7 syntax for Right-Aligning the contents of the cell and selecting font name such as "Arial Narrow".
> John wrote:
Michael Nuwer [dBVIPS] Wrote:[color=darkred
]
> The following are a few thing I've done:
> oExcel = new oleAutoclient("Excel.Application")
> cFileName = "myTemplate.xlt"
> cHomeDir = set( "dire" ) + "\" + cFileName
> oExcel.workbooks.open(cHomeDir)
>
|
|
|
|
|