38209 ExcelXML

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

38236

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.

GOALS
  • 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.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.


38236

Sample 01
sample01.png

sample01_excel.png


Sample 02
sample02.png

sample02_excel.png


Sample 03
sample03.png

sample03_excel.png


Sample 04 - No Grid control
sample04.png

sample04_excel.png

Last edited Jun 10, 2013 at 3:43 AM by bruscain, version 19

Comments

MauroPolo Nov 17 at 10:39 AM 
Tips for converting xml to xlsx
...
.OpenAfterSaving = .F.
.Save("test.XML")
oExcel = CreateObject("Excel.Application")
if vartype(oExcel) != "O"
* could not instantiate Excel object
* show an error message here
return .F.
EndIf
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"
endif
EndIf
oExcel= .null.

where
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 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 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 at 11:58 PM 
Excelente herramienta, muchas gracias por compartir.
Como puedo insertar una fila para colocar un titulo?

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

kknorr Feb 26 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, "")
endif

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

Thanks for this class
Ken Knorr

bruscain Feb 21 at 9:42 PM 
andyofnewzealand,

Download the release 1.08

Thanks,

Rodrigo

andyofnewzealand Feb 19 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 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 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 at 4:52 PM 
Hi everyone,
Please download the last version.
Thanks,
Rodrigo

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
else
this.alias = alias()
endif

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()
afields(this.afields,lcAlias)

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

gracias

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.
Thanks,
Rodrigo

software7 Jul 19, 2013 at 6:51 AM 
Rodrigo,
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 
Bairinis,

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
SET COLLATE TO "MACHINE"

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



Rodrigo

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)

SET COLLATE TO "SPANISH"

SELECT IdArticulo ;
FROM Articulos ;
INTO CURSOR curArticulos1 READWRITE

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)
ELSE
MESSAGEBOX("Archivo no guardado", 16)
ENDIF

bruscain Jun 11, 2013 at 12:13 AM 
Bairinis,

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

Rodrigo

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.