Hi Pritpal,
Yes, opening a DBF seems very efficient and you can then manipulate
the spreadsheet as required (eg. adding columns, rows, titles,
formatting fields, etc.).
However, I need to open multiple files in the _same_ spreadsheet
using multiple sheets - Is there a way to do this? When I try to open
multiple files, it creates multiple instances of Excel instead.
TIA.
Regards,
Randy.
At 06:23 PM 6/5/2009, you wrote:
Hi
Randy Portnoff-2 wrote:
>
> The OLE interface to Excel (ie. HBOLE) is extremely slow and
> memory-intensive.
>
> Is there an alternative to HBOLE (eg. third party product) that is
> more efficient for creating XLS files?
>
I do it like this:
1) Build a .csv file as below ( you can modify it to use dbf instead of
array )
2) Submit the resulting file to Excel and then saveas .xls file.
//----------------------------------------------------------------------//
Function Array2Excel( aData, cFileName, aAttr, lHeader, lOpenExcel )
Local i, j, nRow := 1
Local oApp, oWB, oAS, nRows, cType
Local cServer := 'Excel.Application'
Local nColumns
DEFAULT aAttr TO {}
DEFAULT lHeader TO .F.
DEFAULT lOpenExcel TO .T.
if empty( aAttr )
lHeader := .f.
endif
if !Array2Csv( aData, cFileName, aAttr )
Return nil
endif
nColumns := len( aData[ 1 ] )
nRows := len( aData )
TRY
oApp := CreateObject( cServer )
CATCH
Return nil
END
oApp:Visible := .f.
oApp:DisplayAlerts := .f.
oApp:Workbooks:Close()
oWB := oApp:Workbooks:Open( cFileName )
oAs := oWB:ActiveSheet()
for j := 1 to nColumns
cType := valtype( aData[ 1,j ] )
oAs:columns( j ):NumberFormat( if( cType == 'C', '@', if( cType ==
'N', '???.??', ;
if( cType == 'D', 'mm/dd/yyyy', '@' ) )
) )
next
// Columns be made visible properly
for j := 1 to nColumns
oAs:columns( j ):Autofit()
oAs:Cells( 1,j ):font:bold := .t.
oAs:Cells( 1,j ):font:color := RGB( 27,79,216 )
next
if !( lOpenExcel )
oWB:Close( 'SaveChanges',.f. )
oAS := nil
oWB := nil
oApp:Quit()
oApp := nil
else
oApp:Visible := .t.
endif
Return nil
//----------------------------------------------------------------------//
Function Array2CSV( aArray, cCsvFile, aAttr )
Local i, s
Local nRows := len( aArray )
Local nCols := len( aArray[ 1 ] )
Local aTyp := array( nCols )
Local aCsv := {}
aeval( aArray[ 1 ], {|e,i| aTyp[ i ] := valtype( e ) } )
for i := 1 TO nRows
s := ''
aeval( aArray[ i ], {|e, j| s += X2Csv( e, aTyp[ j ] ) + if( j ==
nCols, '', ',' ) } )
aadd( aCsv, s )
next
s := ''
aeval( aAttr, {|e_, i| s += e_[ 2 ]+',' } )
s += CRLF
aeval( aCsv, {|e| s += e + CRLF } )
memowrit( cCsvFile, s )
Return file( cCsvFile )
//----------------------------------------------------------------------//
Static Function X2Csv( x, cTyp )
Local xVar := ''
do case
case cTyp == 'C'
if at( '"', x ) > 0 .OR. at( ',', x ) > 0
xVar := '"' + strtran( x, '"', '""' ) + '"'
elseif IsDigit( left( x,1 ) )
return '="'+ x +'"'
else
return x
endif
case cTyp == 'N'
xVar := ltrim( str( x ) )
case cTyp == 'D'
xVar := dtoc( x )
case cTyp == 'L'
xVar := if( x, 'Yes','No' )
endcase
Return xVar
//----------------------------------------------------------------------//
If this code had been useful to you then please tell on this list.
Regards
Pritpal Bedi
--
View this message in context:
http://www.nabble.com/GTWVG-%2B-MS-SDK-7.0a-%28MSVS-2010-Beta%29-errors-tp23895517p23896457.html
Sent from the Harbour - Dev mailing list archive at Nabble.com.
_______________________________________________
Harbour mailing list
Harbour@harbour-project.org
http://lists.harbour-project.org/mailman/listinfo/harbour
_______________________________________________
Harbour mailing list
Harbour@harbour-project.org
http://lists.harbour-project.org/mailman/listinfo/harbour
