Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
COPYing numeric data to an XLS
Message
 
To
23/07/2009 16:27:42
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Environment versions
Visual FoxPro:
VFP 9 SP2
OS:
Windows XP SP2
Miscellaneous
Thread ID:
01413826
Message ID:
01414164
Views:
58
Hi Yossi,

>You've certainly whet my appetite, but since I do not know enough about XML, I would need more info in order to implement it.

here's some code to get you started... The program converts a cursor into an Excel sheet when the cursor only has numeric and character fields. Characters fields that contain numbers are converted to numbers:
Create Cursor Sample (cData C(30))
Insert into Sample values("Some text")
Insert into Sample values("1.23")
Insert into Sample values("999-444")
Insert into Sample values("Some more text")
StrToFile(ExportToExcel(),"Sample.xls")


*========================================================================================
* Export the current cursor to Excel (XMLSS format)
*========================================================================================
Procedure ExportToExcel

	*--------------------------------------------------------------------------------------
	* Setup environment
	*--------------------------------------------------------------------------------------
	Local lcPoint
	lcPoint = Set("Point")
	Set Point To "."

	*--------------------------------------------------------------------------------------
	* Create a header
	*--------------------------------------------------------------------------------------
	Local lcHeader
	Text to m.lcHeader NoShow
		<?xml version="1.0"?>
		<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
		 xmlns:x="urn:schemas-microsoft-com:office:excel"
		 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
		  <Worksheet ss:Name="Sheet1">
		    <Table>
	EndText 
		
	*--------------------------------------------------------------------------------------
	* Assemble a string with all rows
	*--------------------------------------------------------------------------------------
	Local lcData, lcRow, lnField, luValue
	lcData = ""
	Scan
		lcRow = "<Row>"
		For lnField = 1 to Fcount()
			luValue = Evaluate(Field(m.lnField))
			Do case
			Case Vartype(m.luValue) == "C"
				luValue = Alltrim(Nvl(m.luValue,""))
				If Transform(Val(m.luValue)) == m.luValue
					lcRow = m.lcRow + ;
						[<Cell><Data ss:Type="Number">] + m.luValue+ [</Data></Cell>]
				Else
					lcRow = m.lcRow + ;
						[<Cell><Data ss:Type="String">] + STRCONV(m.luValue,9)+ [</Data></Cell>]
				EndIf 
			Case Vartype(m.luValue) == "N"
				lcRow = m.lcRow + ;
					[<Cell><Data ss:Type="Number">] + Transform(Nvl(m.luValue,0)) + ;
					[</Data></Cell>]
			Otherwise 
				Assert .F. message "Type not supported"
			EndCase 
		EndFor 
		lcRow = m.lcRow + "</Row>"
		lcData = m.lcData + m.lcRow
	EndScan 
	
	*--------------------------------------------------------------------------------------
	* Create the footer
	*--------------------------------------------------------------------------------------
	Local lcFooter
	Text to m.lcFooter noshow 
		    </Table>
		  </Worksheet>
		</Workbook>
	EndText 
	
	*--------------------------------------------------------------------------------------
	* Restore environment
	*--------------------------------------------------------------------------------------
	Set Point To m.lcPoint

Return m.lcHeader + m.lcData + m.lcFooter
There's much more you can do in XMLSS. I'll go through all (or at least most) of those things in my session at SWFox.
--
Christof
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform