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 in Excel file.

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 AboutAbout ExcelXML class
method_vs.bmp ProgressMethod used to show the percentage processed.
method_vs.bmp SaveCreates 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 2:39 AM by bruscain, version 17

Comments

metin Fri at 3:15 PM 
It works excellent!.. Thank you!!!

kknorr Feb 26 at 8: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 8:42 PM 
andyofnewzealand,

Download the release 1.08

Thanks,

Rodrigo

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

msykora Dec 18, 2013 at 11:48 AM 
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 3: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 3: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 3: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 7:24 PM 
Thank you.
Excellent work
Daniel Manuel López

bruscain Jul 21, 2013 at 6: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 5: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 1:13 AM 
DO do D:\ExcelXML_1.05\ExcelXML\sample05.fxp ERROR "FILE ACCESS IS DENIED"

software7 Jun 25, 2013 at 2: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 5:12 PM 
Rodrigo Thanks for your help, now if it worked properly the class. excellent work and thanks for sharing.

bruscain Jun 11, 2013 at 11:07 PM 
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 4: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 10, 2013 at 11:13 PM 
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 5: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 2:10 PM 
great work!!

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

zulqasar Jun 6, 2013 at 9: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 1:20 AM 
Great Project.Thank you.

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

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