Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Converting to xls
Message
De
11/05/2018 06:58:08
Cetin Basoz
Engineerica Inc.
Izmir, Turquie
 
Information générale
Forum:
Visual FoxPro
Catégorie:
Visual FoxPro et .NET
Divers
Thread ID:
01659920
Message ID:
01659929
Vues:
44
>Convert a dbf to xls - I have forgotten how to do it

IF what you are converting is not a simple and small file (less than 16K rows if I remember right) you can use "Copy To". It sort of works and creates old for excel files. For anything robust you could use something like "Vfp2Excel". There are many samples on the internet using Vfp2Excel. Here is a link for a broader discussion and code with Vfp2Excel:

A thread about Excel and compatibility

And here is one of the sample codes:
Local loStream As ADODB.Stream, lcDataSource, lcSQL, lcColumnHeaders
lcDataSource = _samples+'data'
TEXT to m.lcSQL textmerge noshow
Select Country, Region, City, Cust_Id, Company, Contact
From Customer
ENDTEXT
*** We want to use custom headers on columns, rather than fieldnames
*** Our list should match the order of columns in our select
*** We could simply omit passing headers, then field names would be used as column names
lcColumnHeaders = "Country, Region, City, CustomerID, Company Name, Contact Name"
loStream = GetDataAsAdoStream("Provider=VFPOLEDB;Data Source="+m.lcDataSource, m.lcSQL)

*** Main Excel automation part now
oExcel = Createobject("Excel.Application")
With oExcel
	.DisplayAlerts = .F.
	.Workbooks.Add
	.Visible = .T.
	With .ActiveWorkBook.ActiveSheet
		.Name = 'Customer List'
		* Send the data - safe copy to replacement
		VFP2ExcelVariation(m.loStream, .Range("A1"), m.lcColumnHeaders)
		.Columns.AutoFit()
	Endwith
Endwith

* Author: Cetin Basoz
* This is based on earlier VFP2Excel function codes
* that has been published on the internet, at various sites
* since 2001. Not to be messed with others' code who named the same but has
* nothing to do with the approaches taken here (unless copy & pasted and claimed
* to be their own work, < s > that happens).
* Note that this works with 64 bits excel too.

Function VFP2ExcelVariation(toStream, toRange, tcHeaders)
	Local loRS As ADODB.Recordset,ix
	loRS = Createobject('Adodb.Recordset')
	m.loRS.Open( m.toStream )
	* Use first row for headers
	Local Array aHeader[1]
	m.toRange.Offset(1,0).CopyFromRecordSet( m.loRS )  && Copy data starting from headerrow + 1
	For ix=1 To Iif( !Empty(m.tcHeaders), ;
			ALINES(aHeader, m.tcHeaders,1,','), ;
			m.loRS.Fields.Count )
		m.toRange.Offset(0,m.ix-1).Value = ;
			Iif( !Empty(m.tcHeaders), ;
			Alltrim(aHeader[m.ix]), ;
			Proper(m.loRS.Fields(m.ix-1).Name) )
		m.toRange.Offset(0,m.ix-1).Font.Bold = .T.
	Endfor
	m.loRS.Close()
Endfunc

Procedure GetDataAsAdoStream(tcConnection, tcSQL)
	Local loStream As 'AdoDb.Stream', ;
		loConn As 'AdoDb.Connection', ;
		loRS As 'AdoDb.Recordset'
	loStream = Createobject('AdoDb.Stream')
	loConn = Createobject("Adodb.connection")
	loConn.ConnectionString = m.tcConnection
	m.loConn.Open()
	loRS = loConn.Execute(m.tcSQL)
	m.loRS.Save( loStream )
	m.loRS.Close
	m.loConn.Close
	Return m.loStream
Endproc
Çetin Basöz

The way to Go
Flutter - For mobile, web and desktop.
World's most advanced open source relational database.
.Net for foxheads - Blog (main)
FoxSharp - Blog (mirror)
Welcome to FoxyClasses

LinqPad - C#,VB,F#,SQL,eSQL ... scratchpad
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform