38209 XLSX Workbook

Read from and write to XLSX format files without any automation or export with support for full cell formatting in Excel

Release 17 - Fixed issues with loading the formatting for an existing XLSX file. Added support for cell indentation, rotated text formatting, and cell in-line text formatting (mixed formatting of a text string inside a cell).


38236

Project Manager: Greg Green

38362 Latest release of XLSX Workbook

Last edited Sep 28 at 7:36 PM by gagreen1214, version 6

Comments

gagreen1214 Sep 30 at 1:38 AM 
Matt--

This is now fixed in Release 17. Thank you for sending a sample file to test with.

Greg

mattslay Aug 28 at 12:56 PM 
Hi Greg - I'm experimenting with this library for use in my app. When I opened a file, set some cell values, and then saved it, all of the cell coloring are stripped out and any cell border lines are removed.

I sent you an email with screenshot. Also attached the file in you need it to study the situation.

gagreen1214 Jul 17 at 3:27 AM 
tamayok--

There is a method to read an XLSX file into the cursors -- see OpenXlsxWorkbook() method; there are other methods for returning the cell values, formulas, and formatting.

Greg

tamayok Jul 15 at 4:42 AM 
Just became aware of this GREAT tool... THANKS!

I second the request to have a method exactly like requested by: oscarrd - May 14

I really need the inverse to your: 'tabletoexcel.prg' ... ExcelToCursor()


what names for fields? .T. if the first record is the fieldname or .F. if a standard 'Column1', 'Column2'... if the name provided fails DBF stds, assign 'Column1', 'Column2'..

what columns? all populated ones

what field size? the longest required by the data in the cell or 255-56; it is not perfect but at least it is something.

DougHennig Jul 1 at 7:01 PM 
Hi Greg.

I can see the issue with the grid: I was creating one on the fly rather than using one in a form as a quick-n-dirty way to get a spreadsheet from a cursor with a little bit of formatting.

Doug

gagreen1214 Jul 1 at 6:02 PM 
Doug--

I have incorporated your comments into the code base except for #5 at this point. There was a problem with having a private datasession separate from the grid. I had another user have a problem with the grid losing the cursorsource setting when switching datasessions. So there is the need to define the working cursors in the same datasession as the grid. I also agree with your basic comment in #5 so I am looking at how to accommodate both needs. I also elected not to set the General field type value to "" in the code location that you gave; instead I added this to the SetCellValue() method (DO CASE on VARTYPE) so that it would be applicable for any use of General field types.

Greg

DougHennig Jun 30 at 11:22 PM 
Hi Greg.

I have some other suggestions:

1. If you have a sheet named "Test1" and want to add a sheet named "Test", the SEEK in AddSheet can find the Test1 record if EXACT is set off. I changed it to this:

*** DH 2016-06-30: use PADR on seek so no partial name match
***IF SEEK(BINTOC(tnWB), "c_workbooks", "workbook") .AND. !SEEK(BINTOC(tnWB)+UPPER(tcSheetName), "c_sheets", "shname")
IF SEEK(BINTOC(tnWB), "c_workbooks", "workbook") .AND. !SEEK(BINTOC(tnWB)+UPPER(padr(tcSheetName, len(c_sheets.shname))), "c_sheets", "shname")

2. SaveTableToWorkbook: accept an optional tcSheetName parameter:

*** DH 2016-06-30: accept optional tcSheetName parameter
***LPARAMETERS tcAlias, tcWBName, tlFreeze, tlSaveWB
LPARAMETERS tcAlias, tcWBName, tlFreeze, tlSaveWB, tcSheetName

Use the parameter if passed:

*** DH 2016-06-30: use either the alias or tcSheetName as the sheet name
*** lnSh = this.AddSheet(lnWB, lcAlias)
local lcSheetName
if vartype(tcSheetName) = 'C' and not empty(tcSheetName)
lcSheetName = tcSheetName
else
lcSheetName = lcAlias
endif vartype(tcSheetName) = 'C' ...
lnSh = this.AddSheet(lnWB, lcSheetName)
*** DH 2016-06-30: end of new code

3. Same with SaveGridToWorkbook:

*** DH 2016-06-30: accept optional tcSheetName parameter
***LPARAMETERS toGrid, tcWBName, tlFreeze, tlSaveWB
LPARAMETERS toGrid, tcWBName, tlFreeze, tlSaveWB, tcSheetName

Use the parameter if passed:

*** DH 2016-06-30: use either the workbook name or tcSheetName as the sheet name
*** lnSh = this.AddSheet(lnWB, JUSTSTEM(tcWBName))
local lcSheetName
if vartype(tcSheetName) = 'C' and not empty(tcSheetName)
lcSheetName = tcSheetName
else
lcSheetName = JUSTSTEM(tcWBName)
endif vartype(tcSheetName) = 'C' ...
lnSh = this.AddSheet(lnWB, lcSheetName)
*** DH 2016-06-30: end of new code

4. Have SaveGridToWorkbook call GetWorkbook if CreateWorkbook returns 0 like SaveTableToWorkbook does:

lnWB = this.CreateWorkbook(tcWBName)
*** DH 2016-06-30: if workbook exists, get reference to it
IF lnWB = 0
lnWB = this.GetWorkbook(tcWBName)
ENDIF
*** DH 2016-06-30: end of new code

5. Both SaveTableToWorkbook and SaveGridToWorkbook call CreateWorkingCursors which prevents them from adding a new sheet to an existing workbook if OpenXLSWorkbook is called first. I commented out the code calling CreateWorkingCursors and DestroyWorkingCursors in both methods since they aren't needed (Init creates the cursors):

*** DH 2016-06-30: don't call CreateWorkingCursors: they're already open and that prevents
*** calling OpenXlsxWorkbook first to append to existing workbook
***this.AllowChangeDE = False
***IF this.UsePrivateDE
*** this.CreateWorkingCursors()
***ENDIF

and:

*** DH 2016-06-30: don't call DestroyWorkingCursors.
***IF this.UsePrivateDE
*** this.DestroyWorkingCursors()
***ENDIF

6. If the grid HeaderHeight is 0, don't output column headers in SaveGridToWorkbook:

FOR lnCol=1 TO toGrid.ColumnCount
*** DH 2016-06-30: added next line so only output headings if HeaderHeight > 0
if toGrid.HeaderHeight > 0
this.SetCellValue(lnWB, lnSh, lnRow, lnCol, toGrid.Columns(lnCol).Header1.Caption)
*** DH 2016-06-30: added ENDIF
endif toGrid.HeaderHeight > 0
this.SetColumnWidth(lnWB, lnSh, lnCol, this.ConvertPixelsToExcelUnits(toGrid.Columns(lnCol).Width))
ENDFOR
*** DH 2016-06-30: if no headers, start in first row
if toGrid.HeaderHeight = 0
lnRow = 0
endif toGrid.HeaderHeight = 0
*** DH 2016-06-30: end of new code

Doug

gagreen1214 Jun 21 at 2:36 PM 
Doug--

Thank you for this addition -- I will incorporate as soon as I can and will post an update.

Greg

DougHennig Jun 20 at 6:39 PM 
Hi Greg.

A small bug in SaveGridToWorkbook: it should ignore General fields to avoid an error (not a common thing, I realize):

FOR lnCol=1 TO toGrid.ColumnCount
lcField = toGrid.Columns(lnCol).ControlSource
*** 2016-06-20 DH: use blank for General fields
if type(lcField) = 'G'
this.SetCellValue(lnWB, lnSh, lnRow, lnCol, '')
else
*** 2016-06-20 DH: end of new code
this.SetCellValue(lnWB, lnSh, lnRow, lnCol, &lcField)
*** 2016-06-20 DH: added ENDIF
endif
ENDFOR

Doug

BennyThomas Jun 10 at 11:08 PM 
Hi Greg,

I downloaded the latest class. Now I am getting error on wordwrap.

Error 13 occurred @ line 7 of VFPXWORKBOOKXLSX.SETCELLWORDWRAP.
Message: Alias not found
Error 13 occurred @ line 9 of VFPXWORKBOOKXLSX.SETCELLWORDWRAP.
Message: Alias 'C_CELLS' is not found.
With the previous class (Workbookxlsx) there was no problem.

rega10 Jun 1 at 10:15 AM 
Hi Greg, great work!

Is there a way to deactivate the gridlines similar to DisplayGridlines = False when using automation?
Thank you!
RG

John_Harbour May 27 at 4:05 PM 
Hi, Greg

Just some thoughts on the design of a ToCursor() method.

It should take two parameters, the name (alias) of the cursor and the number of the sheet to be imported.

The fields names could be F1, F2, ... F255. Make each a char(254) and populate with the headers and data, converted to char where necessary.

The user programmer can then do
select left(f1, 20) as foo, val(f2) as bar, etc. into MyCursor
to create another cursor with the data converted as she wants it.

The cursor you create should be READWRITE to allow rows to be deleted, values changed etc.

Hope this is useful.
Regards

John

John_Harbour May 26 at 3:02 PM 
Hi,
Thanks for this. It's very useful. I found a couple of bugs today. Methods SetCellFont() and GetCellDatatype() contain two calls to SetDefaultDataSessionId(), rather than one to SetClassDataSession() and another to SetDefaultDataSessionId().

Regards
John Harbour

PaladinTodd May 17 at 3:01 PM 
I don't see any distribution notes for this. Does a client need anything beyond the source code contained in the ZIP? No Dlls? No ODBC install?

gagreen1214 May 16 at 2:56 AM 
All--

I have updated the class to include support for British Pound and Euro currencies -- thanks to another on this site who did most of the ground work for this. I also corrected some bugs in the class using a private datasession for its cursors. I and another person have been using these updates for some time to try to make sure the bugs are worked out before my posting.

I believe this is now stable and want to release this.

As for a ToCursor() method... I would have questions: what columns? what names for fields? I have not included this type of method as I am not sure how I would 'decide' the field names. There is a method for reading the spreadsheet into the internal cursors. Once read in, there are methods to determine the cell values, number of rows and number of columns. From this you could write your own implementation of storing to a cursor or table.

oscarrd May 14 at 5:06 PM 
Hello,
it's there a way to import and XLSX to a cursor?
I have read documentation and test program from the folder but I have not found a way to do this

Something like:
lcFile="myExcelfile.xlsx"
loExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx")
loExcel.OpenXlsxWorkbook(lcFile)
loExcel.SaveToCursor("MyCursor")

select MyCursor


Thanks

rkaye Apr 4 at 5:40 PM 
Hi Brett,

I just noticed that there is a release 12 available which came out in February. (The version I've been working with is v10.) It is marked as a beta but might be worth checking out.

rkaye Apr 1 at 7:18 PM 
It also doesn't seem to be very fond of currency symbols other than $.

rkaye Apr 1 at 3:43 PM 
Looks like I'm running into similar issues with SET POINT. There is some discussion in the comments here about a possible solution being implemented. Was this ever done?

TIA

rkaye Mar 31 at 10:02 PM 
Hi Brett,

Sounds like a good reason to use the save from grid. At the moment I don't have that requirement but if I ever do I will report back here.

I just added regular old Excel automation code to open the spreadsheet after it gets created. With the appropriate prompts and exception handling, of course. :-)

BrettHudson Mar 29 at 12:03 AM 
Thanks Rkaye for reviewing this. Again, I think that this is a great routine.

The issue we are having appears to be related with saving off and restoring the data sessions. I note that if I comment out the code in the SetClassDataSession and SetDefaultDataSession procedures in the class that I do then have access to all the control source settings in the passed grid object. However, if I comment out the code in these procedures, I then get messages asking if I would like to overwrite the working XML files that are setup in the WriteSupportXMLS procedure (such as CORE.XML). There appears to be some issue with saving off and then resetting the default data sessions.

PS. Like you, we are placing the class directly on the form itself and then calling if from a button on the form.

PPS. It would also be nice if there were some procedure type call to actually cause the XLSX file that was created, be it from the grid or the table, to be activated and displayed to the screen. At present (unless I am missing something), it creates the XLSX field but doesn't actually access it.

BTW. The reason we are using the grid to extract the data from, rather than just a table, is that we have developed our grid processing so that the end users can dynamically amend the grid layouts, (the header caption, the individual fields, setup new fields etc) and as we want the XLSX workbook to reflect the details of the grid as displayed on the screen rather than the underlying cursor, we need to export the details of the grid.

rkaye Mar 25 at 2:23 PM 
Hi Brett,

I've just finished ripping out all my old copy to type xl? and replacing with this class. I have not implemented the savegridtoworkbook method so can't offer any specific advice on that. I may poke at it a bit but I've found the SaveTableToWorkbook method to be so simple to use I haven't bothered with the grid method. Here are a few things I've noted (consolidating my previous comments into one for easier reading).

Excel is sensitive to the presence of lower ASCII control characters in the XML. You will need to sanitize your data. I found the Microsoft Open XML SDK 2.5 Productivity Tool for Microsoft Office to be invaluable for troubleshooting the XML created by the class.

My standard implementation has to been to drop the class on a form as opposed to creating in method code, etc. I found during my testing that it was helpful to reset things before each call to create a workbook. There doesn't appear to be an explicit reset method in the class but I found that deleteallworkbooks does the trick. But, if you do that it also wipes out all the working cursors. The answer I found was to trigger the assign method on the codepage property.

The createworkingcursors method does not save and restore the current workarea. As I've primarily implemented the class by using the SaveTableToWorkbook method this bit me on a few occasions. I've updated my local copy of the class to do that.

Kudos again to the folks who put this class together! Any plans to tackle the DOCX file format? :-)

BrettHudson Mar 24 at 5:36 AM 
This looks to be great code and I am very keen to get it up and running.

However, I am having a couple of issues trying to get it working when exporting the contents of a grid using the 'SaveGridtoWorkbook' procedure. (It may be something with the setup of our forms but I am not sure).

We have our own locally developed class that programmatically generates the grids as the form itself is generated at run time. (We drop our local grid class onto the form at design time). I find that if I place a copy of the XLSX workbook class directly onto my form, and I then execute the following code (passing the name of the local grid on the form):

thisform.VFPXWORKBOOKXLSX1.savegridtoworkbook(mylocalform.grid, "My Local Grid Workbook")

that the code in the 'SaveGridToWorkbook' procedure is unable to 'see' the COLUMNS[i].CONTROLSOURCE field. They are all being processed as blank.
(I.E. the lcfield = togrid.COLUMNS(lncol).CONTROLSOURCElcfield line of code resolves to blank). Interestingly, the code that sets up the workbook headers works in that the correct 'captions' are being written to the worksheet column headings.

Not sure why this is happening but would appreciate any suggestions as to what may the issue.

Thanks, Brett

rkaye Mar 8 at 1:11 PM 
I'm going to handle the illegal characters (ASCII values below 32) by removing them via regex. (I use Craig Boyd's FLL for that.) My gut is that this should be part of the getxmlstring method which currently handles transforming &, <, >, and ' but for now I'm doing this before passing the data over to VFPXWorkbookXLSX.

rkaye Mar 7 at 7:16 PM 
In my research I found something called the Open XML 2.5 Productivity Tool for Microsoft Office and it's proving invaluable in figuring out what is making Excel unhappy. For example, if it doesn't seem to like ampersands in property values. (i.e. setting CompanyName to "My Company & Son" makes Excel complain when opening the file).

It also appears to dislike other characters such as the one referenced below (the fun continues):
---------------------------
Open XML SDK 2.5 Productivity Tool for Microsoft Office
---------------------------
Cannot open the file: Part /xl/sharedStrings.xml: '', hexadecimal value 0x18, is an invalid character. Line 1373, position 2368.

rkaye Mar 4 at 10:45 PM 
One more comment before I put this away for the weekend. I'm going to guess that this implementation is for an earlier version of the OpenXML spec ( > 2.5 ) and there's something that needs to be updated to avoid Excel 2013 thinking the files created by the class are corrupt. If any work is still being done on this library it would be good to know before I start duplicating efforts.

rkaye Mar 4 at 1:53 PM 
Some more feedback. I'm finding that Excel 2013 detects what it thinks is file corruption. Allowing the repair to proceed will let the file be opened. My best guess at this point is it's unhappy about something in the metadata XML but unfortunately Excel's log doesn't provide any useful details.

rkaye Mar 2 at 6:13 PM 
Excellent work in this library! And I love the documentation!

I recently had to refactor an old form that was using COPY TO...TYPE XLS and decided to replace with this. For the most part things have been easy to understand and implementing is a snap. I added an instance to a form and went from there. I have a couple of thoughts to share. First, I think that integrating this into an EXE requires some refactoring on the error handling. I'd rather have my application level handler kick in instead of throwing up messageboxes with Microsoft Visual Foxpro captions. Second, whilst trying to troubleshoot some issues I was having with repeated calls to create a file, I thought that it was possible some properties needed to be reset to default values after each invocation of SaveTableAsWorkbook. In that process I put a call to deleteallworkbooks at the beginning of my method. I'm not sure if you would classify this as a bug but the destroyallworkbooks method closes the c_strings cursor. According to the comments I saw in the code, the creation of this particular cursor was moved out of the createcursors method into the codepage_assign method. This meant that the first time it worked fine and subsequent passes returned alias not found errors. Once I realized what was happening it was easy enough to handle but I thought I would mention it.

Again, really good work in this class and I tip my hat to you for making it so easy to use.

jbrand Feb 19 at 12:48 PM 
The problem with the decimal point also exist in other methodes. For example in SetCellValue() you used lcCellValue = TRANSFORM(lnWholePart + lnFractPart). If set point is set to "," then Excel ignores the fractional part. Or some lines later you look with ATC("." explicit for a point, so this also wont work. I think possible problems can occur in all methodes where you write xml-contents with transform etc.
Perhaps it could be a solution, if you put the whole class in a separate datasession. Then you can setup your environment independent from the users settings.

gagreen1214 Feb 11 at 2:16 PM 
Thank you for the tip on the decimal point -- I did find that as the potential problem but trying to solve when writing the numeric formats. Will add this to the beginning of the save method and restore back to user default value.

jbrand Feb 11 at 9:29 AM 
First of all, many thanks for this great product. I have the same problem as Tom (hbgmail) posted on Dec 1, 2015. I think I have found the problem. It is the decimal point character. In most languages this is the period. If you change this setting via "set point to" for example to a comma (Germany), then all numeric values in the xml-files are printed with comma and Excel dosn't work with that. As a workarround I put set point to "." bevore SaveWorkbook() and directly after set point to ",".

DarrenL_DSS Jan 26 at 4:41 PM 
Many thanks for a great product, got me out of a real hole recently! One enhancement that would be really great to implement if possible is the Excel equivalent loWS.UsedRange.EntireColumn.Autofit. I know there is a column best fit option but this does not work correctly especially when the first few rows are merged cells for headings etc.

excelvbaisfun Dec 28, 2015 at 9:43 PM 
Hi Greg,

Thanks for the follow up. Sorry for my own delay. I wasn't sure if this forum would email me a reply. Anyways, great work and thank you so much for trying to find a method for this amazing task.

This is fantastic!

Yours,

Dan

gagreen1214 Dec 8, 2015 at 12:28 AM 
Dan--

I have the code in development for reading an XLSX file in development and have not yet released it on this forum. The code is partially working and still has some work to do in determining the cell types correctly.

Greg

excelvbaisfun Dec 7, 2015 at 9:03 PM 
This is really cool! I noticed the summary up top says you can Read XLSX files. I'm trying to use an XLSX file for importing as if it were an XLS file, or at least to form a cursor from the XLSX data, but I can't figure out how to create a cursor so that I can import data from that cursor or directly from the XLSX if necessary.

The old way is:
APPEND FROM (m.lcInFile) TYPE XLS

but how can we open an XLSX file and append our cursor from the XLSX file? Basically, how do we read this or create a useable cursor from it?

Thanks!!!
Dan

gagreen1214 Dec 6, 2015 at 3:28 PM 
hbgmail--

Please send me an example XML file with a PRG file that will reproduce the error and I will troubleshoot the problem. Send to anduril58 at hotmail dot com. Thank you.

Greg

hbgmail Dec 1, 2015 at 8:29 AM 
Hello,
I tried the not modified code on window7 pro with excel 2013. It produces a file but when I try to open it I always get "file must be repaired. sheet2.xml part with xml error.error on load line1 col0, sheet3.xml line1 col0, sheet4 line1, col 595"
If I agree , i can see it.
Does someone have the same effect ? Any ideas ?

I remember the same problems with an early version of Vilhelms Exel tools. He said it was something with the Euro sign.

Thanks a lot in advance
tom

gagreen1214 Oct 30, 2015 at 6:53 PM 
Cortiel--

I just tried the code:

this.SetRowHeight(lnWb,lnSh1, 6, 25)

In the method Demo() of the class for sheet 1 and the row height is set without any errors. I will need your XLSX file to be able to determine the cause of the problem. Also, please indicate which sheet number you are trying to set this value for the row height.

Greg

gagreen1214 Oct 30, 2015 at 6:08 PM 
Cortiel--

Please send me the XLSX file that you created with the oExcel.SetRowHeight(lnWb,lnSheet, 6, 25) statement so that I can debug the problem at anduril58 at gmail dot com. Thank you.

Greg

cortiel Oct 30, 2015 at 5:16 PM 
also, a method to add header and footers would be nice

P

cortiel Oct 30, 2015 at 5:15 PM 
everyting works great except when I add this line:

?oExcel.SetRowHeight(lnWb,lnSheet, 6, 25)


Excel claims the workbook is corrupted and does not open it :-(

gagreen1214 Oct 26, 2015 at 9:01 PM 
Thank you for the contribution -- will add this to the class (however, the reference to the r:id has to be dynamically determined as this applies to the sheet number - I will update to get this correct). The Margins can be set by the method SetSheetMargins() - see the Documentation() method for more details.

cortiel Oct 26, 2015 at 5:19 PM 
i added a property called pcLandscape to the object
and at the bottom of WriteSheetXMLs
if added a ifelse:

IF NOT this.pcLandscape
FWRITE(lhFile, '"/></worksheet>')
ELSE
FWRITE(lhFile, '"/><pageSetup orientation="landscape" r:id="rId1"/></worksheet>')
ENDIF

cortiel Oct 26, 2015 at 2:16 PM 
how do I change the layout to landscape and the margins to 0
other than that it's great!