38209 ExcelXML

Convert a Table or a Grid control into a Microsoft Excel XML Spreadsheet file.


Project Manager: Rodrigo Bruscain

The ExcelXML project is designed to bring to the Visual FoxPro the possibility to generate a Excel file converting 99% of all visual caracteristics from a Grid. However, there is the possibility to generate a file without a Grid.

  • Excel files with over 65,535 rows.
  • No limit size. (it depents on the Operational System)
  • Convert a Grid Control into a Excel XML file considering 99% of the visual characteristics.
  • It is possible to use Grid Dynamics properties.
  • Based in all Grid visual properties.
  • It is possible to convert a table without a Grid Control as well.
  • Easy to implement and it is not necessary to change your code.
  • Compatible with Microsoft Excel 2003 or higher.
  • The files can be opened by OpenOffice reducing conversion errors.
  • Open the file by Excel and save in other formats to reduce the size without loose the information.
  • It is not necessary to have the Microsoft Excel installed.

Release version 1.08
NEW - Document author included as username or computername.
FIX - Field Date and DateTime with the year lower than 1900 will be forced to 1900. This is necessary because the lower allowed year in a cell of Excel is 1900
FIX - Automatic column width for fields type char bigger that 190 columns

Release version 1.07
FIX - Data with the characters "<" or ">" are replaced for "[" and "]" in order to avoid data conflict during the conversion.
FIX - Alias error when the property "RecordSourceType" in the Grid control is diferent than 1.
FIX - Included samples with no progressbar in order to avoid the error ole 0x80040154. This error happens only in some machines that the ActiveX ProgressBar doesn't exists.

Release version 1.06
FIX - SET COLLATE TO controled at run-time to avoid the error "Invalid Key Length"

Release version 1.05
FIX - Column data type Date and DateTime when converted has wrong format.
FIX - Column data type DateTime is not considered the time in current value field.

Release version 1.04
FIX - Sheet name cannot exceed 31 characters
FIX - Sheet name cannot contain any of the following characteres: : \ / ? * [ ]
FIX - Sheet name cannot be blank. If is blank, will be changed to "Sheet1"

Release version 1.03
FIX - Field Date/Datetime with NULL or EMPTY value builds a Excel file incorrectly.
FIX - Index error when the index tag is too big.

vfpxreleasesmall.png Download Latest Release of ExcelXML
See the samples included in the file

Properties and Methods Description
property_vs.bmp ColumnCount Returns the number of columns included in the Excel file.
property_vs.bmp File Inform the name of Excel file. If you don't inform the name with the extension, the XML extension will be included. The default file name is "Book1"
property_vs.bmp GridObject Inform the Grid control object to convert a Grid control in an Excel XML file.
property_vs.bmp HasFilter .T. Includes the option Filter in all columns in the generated file.
property_vs.bmp LockHeader .T. locks the header in the generated file. This option in Excel is called by Freeze Top Row.
property_vs.bmp OpenAfterSaving .T. to open the file after saving it.
property_vs.bmp RowCount Returns the number of rows included in the Excel file.
property_vs.bmp SetStyles .T. to define that the Excel file will have the Grid visual characteristics transported.
property_vs.bmp SheetName Excel sheet name. The default name is "Sheet1"
property_vs.bmp xmlEncoding XML encoding type used to set the code that defines special characters. Default code is "iso-8859-1".
property_vs.bmp Version Object that contains the information about this class.
method_vs.bmp About About ExcelXML class
method_vs.bmp Progress Method used to show the percentage processed.
method_vs.bmp Save Creates the Excel XML file.


Sample 01


Sample 02


Sample 03


Sample 04 - No Grid control


Last edited Feb 21, 2014 at 9:35 PM by bruscain, version 24


trial03 Sep 30, 2016 at 7:13 AM 

suggestion : add one function to allow excel sheet have a TITLE row. eg. Sales Data for Sept 2016


BrettHudson Apr 28, 2015 at 8:01 AM 
Great coding. A really useful tool. I have started including the details in some local projects. I am wondering how hard it would be to include an option to automatically setup totals for all/any numeric type fields in the file. This would really help as we could then setup a kind of 'auto sum' for those numeric columns.

MauroPolo Nov 17, 2014 at 10:39 AM 
Tips for converting xml to xlsx
.OpenAfterSaving = .F.
oExcel = CreateObject("Excel.Application")
if vartype(oExcel) != "O"
* could not instantiate Excel object
* show an error message here
return .F.
oExcel.visible = .T.
If File("test.xml")
oWorkbook = oExcel.Application.Workbooks.Open("test.xml")
oWorkbook.saveas("test",51) && xlsx, see below for list of few valid excel recent format
If File("test.xlsx")
Delete File "test.xml"
oExcel= .null.

51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

drsasa Nov 13, 2014 at 9:29 PM 
Really great class. Thank you for sharing.

@CHamlin yea class is slow u can speed it up too 100x changing method buildrows to not write to hdd evry single row. Basicly store up in one variable all rows then all write in xml with one call strtofile ...

CHamlin May 19, 2014 at 10:00 PM 
I have a very large amount of data (14,569 records) to be exported. 182 records per second...by the time it ends it is around 43 records per second. Anyone else experiencing this huge slow down with large amounts of data?

palmory May 6, 2014 at 11:58 PM 
Excelente herramienta, muchas gracias por compartir.
Como puedo insertar una fila para colocar un titulo?

metin Apr 18, 2014 at 4:15 PM 
It works excellent!.. Thank you!!!

kknorr Feb 26, 2014 at 9:27 PM 
I needed to export memo fields longer then 255 chars.
I modified buildrows to do transform of lcDataColumn at end of each case. For last Otherwise
if len(lcDataColumn) < 256
lcDataColumn = transform(lcDataColumn, "")

in lcXmlRows = lcXmlRows ....
removed the transform(lcDataColumn, "") and replaced it with lcDataColumn

Thanks for this class
Ken Knorr

bruscain Feb 21, 2014 at 9:42 PM 

Download the release 1.08



andyofnewzealand Feb 19, 2014 at 3:39 AM 
Hi again Rodrigo. I also get an error if I try to export a character field with length > 190.

andyofnewzealand Feb 17, 2014 at 9:29 PM 
Works great. Just a couple of things - in the next upgrade can you allow for a date < 01.01.1500. I get an error if this happens. Also the error message displays an incorrect path to the error log file. Thanks Rodrigo

jallad Jan 30, 2014 at 10:13 PM 
Works fine with Arabic Language ( encoding :Windows-1256 )

and RightToleft forms
<Worksheet ss:Name="<<this.SheetName>>" ss:RightToLeft="<<thisform.RightToleft...

Great work

Thank you for sharing

bruscain Jan 14, 2014 at 4:52 PM 
Hi everyone,
Please download the last version.

msykora Dec 18, 2013 at 12:48 PM 
Thank you for this great class.
I try export some real grid data and found problem,
if data contains character "<" or ">" , saved xml document can not be opened.

MauroPolo Nov 7, 2013 at 4:50 PM 
Problem solved!

I've create a properties named alias where store table alias and substitute all the string with this.GridObject.RecordSource with this.alias and correct the code below.

if this.GridObject.recordsourcetype = 1
this.alias = this.GridObject.RecordSource
this.alias = alias()

MauroPolo Nov 7, 2013 at 4:20 PM 
I've problem with a SQL recordsource in a grid.
VFP report 'Alias not found' in afields row in the Save method:

*- environment
lcAlias = this.GridObject.RecordSource
lnRecno = recno()

Can you help me?

benjaminrojas Oct 8, 2013 at 4:03 AM 
buenas noches;
de antemano muchas gracias por esta maravillosa herramienta lo he instalado sin dificultad en mi pc, sin embargo en otros pcs he tenido el siguiente problema: cuando voy a ejecutar el form me sale el siguiente aviso: codigo de erro ole 0x80040154 codigo de estado de com desconocido . se pasara por alto el objeto ole, agradezco si me pueden ayudar a solucionarlo


software7 Jul 24, 2013 at 8:24 PM 
Thank you.
Excellent work
Daniel Manuel López

bruscain Jul 21, 2013 at 7:53 PM 
Daniel Manuel,
All samples included are supporting a ProgressBar in case you want to implement that.
Please, see all the properties and methods available in the ExcelXML class.

software7 Jul 19, 2013 at 6:51 AM 
La rutina BUILDROWS se torna lenta al procesar 5000 registros, coloque una barra de progreso que indica el avance de progreso y me quedo muy bien. Me gustaría que esa barra de progreso se incluyera en tu siguiente versión. Favor de decirme tu mail para enviarte el código y lo puedas integrar. puedes escribirme a mi mail dmanuell@hotmail.com Atte. Daniel Manuel López Ciudad de México. Gracias

jackyuan1204 Jul 4, 2013 at 2:13 AM 
DO do D:\ExcelXML_1.05\ExcelXML\sample05.fxp ERROR "FILE ACCESS IS DENIED"

software7 Jun 25, 2013 at 3:17 AM 
Excuse me, I´m not speak Inglish.
XP save well but Windows 7 with Office 2013 fail in Save, help me Please.

Bairinis Jun 12, 2013 at 6:12 PM 
Rodrigo Thanks for your help, now if it worked properly the class. excellent work and thanks for sharing.

bruscain Jun 12, 2013 at 12:07 AM 

You have to use SET COLLATE TO "MACHINE" before executing the class.
Try that. If ok, I will make some modification in the class to avoid this error.
I'll wait for your answer.

* You have to set as below

local loExcelXML, llOk
loExcelXML = newobject("ExcelXML","ExcelXML.VCX")
loExcelXML.SheetName = "Articulos"
loExcelXML.OpenAfterSaving = .t.
llOk = loExcelXML.Save("Ejemplo.XML")


Bairinis Jun 11, 2013 at 5:49 PM 
thanks for responding Rodrigo

Estoy usando Visual Foxpro 9 con todas sus actualizaciones y el siguiente codigo es el que tengo.

* La columna IdArticulo es de tipo C(25)


SELECT IdArticulo ;
FROM Articulos ;

SELECT curArticulos1

local loExcelXML, llOk
loExcelXML = newobject("ExcelXML","ExcelXML.VCX")
loExcelXML.SheetName = "Articulos"
loExcelXML.OpenAfterSaving = .t.
llOk = loExcelXML.Save("Ejemplo.XML")

IF llOk
MESSAGEBOX("Archivo guardado", 64)
MESSAGEBOX("Archivo no guardado", 16)

bruscain Jun 11, 2013 at 12:13 AM 

What is the VFP version are you using?
What is the information that returns if you run ..... ? set("collate")


Bairinis Jun 10, 2013 at 6:33 PM 
En las siguientes lineas de código muestra el siguiente error: Invalid Key Length

PROCEDURE BuildHeadersStyles

index on ssAlignH + ssAlignV + ssFontName + ;
ssFontFamily + ssFontSize + ssFontColor + ;
ssFontBold + ssFontItalic + ssFontUnderline + ssFontStrikeThru + ;
ssBackColor + ssPattern + ssFormat + ssTop + ssBottom tag idxStyle

tavocrochenci Jun 7, 2013 at 3:10 PM 
great work!!

almonts Jun 6, 2013 at 3:50 PM 
Great work. Run & Go OK !!!

zulqasar Jun 6, 2013 at 10:59 AM 
Awesome! I do a lot of XL integration in my apps and this is sure to make my job easy. Just can't thank you enough for this great project.

SUWATPAT Jun 1, 2013 at 2:20 AM 
Great Project.Thank you.

gomes_metha May 29, 2013 at 10:28 PM 
Great! Thank you for sharing.

cmanuel May 29, 2013 at 9:13 PM 
Great idea! Thank you for sharing this.