WorkbookXLSX R30 2

[gbcpastor]

I'm currently learning how to use a class called WorkbookXLSX and I know some of you are quite familiar with it. The problem I'm having is:

I'm trying to post a field to a cell and it has 15 leading spaces but every time I post it, it trims both leading and trailing spaces. This data must be posted exactly as original. Please help with any ideas you may have.

Thanks in advance!

 

[Chris Miller]

Where is your problem, actually? You just have a string starting with a single quote. Inside the string, that is. And then set the cell value to that.

So for example

oExcel.ActiveWorkbook.Cells(1,1).Value=['    1]

 

[gbcpastor]

Where is your problem, actually? You just have a string starting with a single quote. Inside the string, that is. And then set the cell value to that.

So for example

oExcel.ActiveWorkbook.Cells(1,1).Value=['    1]

I'm actually using the WorkbookXLSX R30 class. it looks like you are not, at least I can't find that function in the class. When I use the following, the single quote shows up in the first position of the cell.

lcExcel.SetCellValue(lnWb, lnsh1, 1, 1, "' 123")

result in cell 1,1
' 123

 

[Chris Miller]

Of course the value is including the single quote, also when you read the vlaue back.
Have you looked at the sheet within Excel itself, after putting that in?

 

[Chris Miller]

There's even this question about getting a single quote to show up in Excel - the inverse problem, in a way:

Redirecting

Are you seeing the single quote within Excel? Also when you tab to any other cell than A1?

I have read that you had problems with the classlibrary trimming values before putting them into Excel, but even if you would use the unaltered class and it would trim the value within VFP before setting it into the cell it would stil start with the single quote and end in the digit(s).

Are you saying your Excel version differs from that display?
The single quote behavior is still working in Excel 2021, from my experience and since Excel 1, perhaps, surely since Excel 95. Did they (MS) abondon that in a later version than 2021? I don't think they can, that would break any sheet using it.

 

[gbcpastor]

When I look at the spreadsheet, in the cell I'm creating, it has the single quote showing as the first character and it adds 1 to the length of the actual data.

Take a look.

 

[Chris Miller]

Your Excel file doesn't let me see what your Excel version displays.

If you activate the cell of course it shows the single quote, otherwise you couldn't edit what's actually in the cell, could you? But if you don't activate that cell the quote doesn't show, it also doesn't print, if you print the sheet.

If that's the showstopper for you, then make column A width 0 (the actual column width) and let the actual data export start in Column B, so the usually initially selected cell A1 is not having anything in it. Or start exporting in row 2, add some headers perhaps, or make the first row's height 0.

 

[Chris Miller]

A completely different approach: Would it help to format the column that's having the issue right aligend?

 

[gbcpastor]

A completely different approach: Would it help to format the column that's having the issue right aligend?

Right Aligned would trim the space to the right causing a new error.

 

[gbcpastor]

Your Excel file doesn't let me see what your Excel version displays.

If you activate the cell of course it shows the single quote, otherwise you couldn't edit what's actually in the cell, could you? But if you don't activate that cell the quote doesn't show, it also doesn't print, if you print the sheet.

If that's the showstopper for you, then make column A width 0 (the actual column width) and let the actual data export start in Column B, so the usually initially selected cell A1 is not having anything in it. Or start exporting in row 2, add some headers perhaps, or make the first row's height 0.

In the file I attached, the first field that has leading spaces is A3 so the A1 doesn't work.

I'm using version 2013 excel.

I'd love to know how to force the single quote formatting without showing the single quote. There has to be a way to do it, I just haven't been able to figure it out. I thought you had a way you were using to do this thru this class. If you do, that's what I'm looking for.

 

[atlopes]

GBCPastor (and Greg),

The problem comes from how the XML DOM treats spaces in text nodes.

First of all, apply the patches Greg mentioned in the thread.

Then, in the method Init of the VFPxWorkbookXLSX class, add a line #39 with the following contents:

    this.oXDOM.preserveWhiteSpace = .T.

In the method WriteSheetXmls, add a line #20:

    FWRITE(lhFile, ' xml:space="preserve"')

In the method WriteStringsXml, change line #13 to:

FWRITE(lhFile, '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' + TRANSFORM(tnTotStrCnt) + '" uniqueCount="' + TRANSFORM(tnUnqStrCnt) + '" xml:space="preserve">')   && Change 19Jun2018: missplaced >

With these patches applied, the following code

Excel = CREATEOBJECT("VFPxWorkbookXLSX")
WB = m.excel.createworkbook("testgg.xlsx")
Sh = m.excel.addsheet(m.wb, "Test")
m.excel.setcellvalue(m.wb, m.sh, 1, 1, "    Hello!")
m.excel.saveworkbook(m.wb)

will result in

 

[gbcpastor]

GBCPastor (and Greg),

The problem comes from how the XML DOM treats spaces in text nodes.

First of all, apply the patches Greg mentioned in the thread.

Then, in the method Init of the VFPxWorkbookXLSX class, add a line #39 with the following contents:

    this.oXDOM.preserveWhiteSpace = .T.

In the method WriteSheetXmls, add a line #20:

    FWRITE(lhFile, ' xml:space="preserve"')

In the method WriteStringsXml, change line #13 to:

FWRITE(lhFile, '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' + TRANSFORM(tnTotStrCnt) + '" uniqueCount="' + TRANSFORM(tnUnqStrCnt) + '" xml:space="preserve">')   && Change 19Jun2018: missplaced >

With these patches applied, the following code

Excel = CREATEOBJECT("VFPxWorkbookXLSX")
WB = m.excel.createworkbook("testgg.xlsx")
Sh = m.excel.addsheet(m.wb, "Test")
m.excel.setcellvalue(m.wb, m.sh, 1, 1, "    Hello!")
m.excel.saveworkbook(m.wb)

will result in
View attachment 214

I'm really hoping this will work. Right now, when I run the code, I get an error.



I'm not understanding why this is. The class is in the same folder.

 

[atlopes]

I'm really hoping this will work. Right now, when I run the code, I get an error.

View attachment 215

I'm not understanding why this is. The class is in the same folder.

Put the class in scope by running the following:

SET CLASSLIB TO vfpxworkbookxlsx.vcx ADDITIVE

Or instantiate the class using NEWOBJECT() instead:

Excel = NEWOBJECT("VFPxWorkbookXLSX", "vfpxworkbookxlsx.vcx")

 

[gbcpastor]

Put the class in scope by running the following:

SET CLASSLIB TO vfpxworkbookxlsx.vcx ADDITIVE

Or instantiate the class using NEWOBJECT() instead:

Excel = NEWOBJECT("VFPxWorkbookXLSX", "vfpxworkbookxlsx.vcx")

I would have loved to say all fixed. But while it did run correctly, the result was still the same even after the changes you sent.



Please let me know if you have any other ideas. (I'm willing to send all I have on this if it would help)

 

[atlopes]

Can you attach your patched version of Greg's library?

 

[Chris Miller]

The single quote format forcing should work in Excel 2013, but I don't have Excel2013 to test, so could you do a screenshot of the excel sheet created by this code (not using Gregs library, but that doesn't matter to show the format forcing works):

oExcel = CreateObject("Excel.Application")
oExcel.WorkBooks.Add()
oExcel.ActiveWorkbook.Cells(2,1).Value=['1]
oExcel.ActiveWorkbook.Cells(3,1).Value=[' 2]
oExcel.ActiveWorkbook.Cells(4,1).Value=['  3]
oExcel.ActiveWorkBook.SaveAs('C:\test\some.xlsx') && adapt file name as you need.

You might need ActiveWorkBook.ActiveSheet to get to Cells. Using intellisense helps, if you start like this:

Local oExcel as Excel.Application
oExcel = CreateObject("Excel.Application")

Then typing oExcel and a dot will trigger intellisense to list all the properties and objects of the application object, like the Workbooks collection. You'll see whether ActiveWorkbook will already give access to Cells or only to the Sheets collection of a workbook, including the special ActiveSheet. Then you can also shorten the code using WITH... ENDWITH.

IIRC ActiveWorkBook.Cells() - or ActiveWorkBook.ActiveSheet.Cells() - addresses cells by (row, col), not by (col, row). So the code will fill three cells in column 1, rows 2-4 with 1-3 and increasing indentation, no single quote visible. Otherwise use Cells(1,2) (1,3) (1,4). The effect will show better, if you use a monospaced font, but it will show anyway. The interesting thing is just, whether the single quote shows or not.

Making that work with Gregs class clearly needs fixes, as I see in the latest thread posts, but first don't tell me you see the quote in the cells, when cell A1 is selected after opening the xlsx file with Excel 2013.

Aside from the difference it makes to generate XML instead of OLE automation of an Excel.Application object and how XML handles whitespace, there's nothing very special about how the single quote works, it just acts as a cell format modifier, forcing the cell value to be taken and displayed as is as text, not the usual "Standard" format, that means inferring the data type depending on the value, like interpreting dates, numbers, etc. which you don't want when you want a number treated as text. You can manually do the single quote check, too, as described in the Micrososft Answers thread. Enter a value like 'Name' with both single quotes into a cell, leave the cell, it shows Name' without the first quote. That's automatic and needs no special handling otherwise, also doing it with code instead of manually. If XML also treats a single quote character special, that would be an additional problem to XML whitespace handling, but there shouldn't be a problem with that, that would be impossible to solve in Gregs class library. it's just a matter to preserve this, too, like spaces. maybe an escape sequence would be necessary, but a single quote would only have a special meaning for attribute values within tags, in the XML of an XLSX workbook worksheet the values of cells are the text between an open/close XML tag pair, not within tags, so almost anything goes, the pointy brackets < and > are problematic, as they are tag delimiters, but almost anything else needs no special treatment from the perspective of XML.

There's definitely no problem with getting this character into a cell value and let it work its "magic" when using OLE automation of Excel.Application. Unless really Excel 2013 differs from that. I don't think so, as I remember having used this in newer Excel versions, still.

 

[gbcpastor]

Can you attach your patched version of Greg's library?

Sure.

 

[atlopes]

GBCPastor,

In the Init method, you have:

        TRY
            this.oXDOM = CREATEOBJECT('MSXML2.DOMDocument')
        CATCH TO loException
        *    this.oXDOM = .NULL.
            this.oXDOM.preserveWhiteSpace = .T.     &&& ADDED by SK 082924
        ENDTRY

It must be:

        TRY
            this.oXDOM = CREATEOBJECT('MSXML2.DOMDocument')
            this.oXDOM.preserveWhiteSpace = .T.     &&& ADDED by SK 082924
        CATCH TO loException
            this.oXDOM = .NULL.
        ENDTRY

In the SetCellValue method, you must comment the line after CASE lcDataType = DATA_TYPE_CHAR

*                   txCellValue = ALLTRIM(txCellValue)

In the WriteSheetXmls method, remove the ALLTRIM() as Greg told you. Lines should be

                                    FWRITE(lhFile, ' t="inlineStr"><is><t>' + this.GetXMLString(xl_cells.cellvalue) + '</t></is></c>')

in CASE xl_cells.datatype = DATA_TYPE_CHAR, and

                                FWRITE(lhFile, '<v>' + xl_cells.cellvalue + '</v>')

in CASE xl_cells.datatype = DATA_TYPE_TEXT.

The rest seems Ok.

 

[ggreen61]

@atlopes

I have incorporated your suggestions into the class and will post a new release update. However, I did not add the line #20 as you suggested to the method WriteSheetXMLs; instead the preserve is added to the cell value as an attribute. Also, it is not necessary to add the line to the WriteStringsXML as this is also set at each string value as it is written. Note that I do not write the sharedStrings.xml file for a new workbook that is created by this class; all the string values are written as inline text in the sheet#.xml files. The sharedStrings.xml is only used when reading an existing workbook into the class.

I would appreciate any testing that you would do. Thank you for the suggestions.

Attached is Release 45 -- I will update VFPx later this week to allow some testing by members of this forum. Again, thank you.

 

[ggreen61]

gbcpastor--

See the above reply for a download of the updated class. You should be able to now have leading spaces and the class automatically assign to preserve the spaces. However, if you are trying to indent the text over an amount, you should use the following methods to create a style with the desired indent. The concept of styles is used to be able to define all the formatting for a cell via a style. See the section "Cell Styles" on page 20 of the documentation and the Demo method for examples.

LOCAL loExcel, lnWb, lnSh, lnStyle
loExcel = NEWOBJECT("VFPxWorkbookXLSX", "vfpxworkbookxlsx.vcx")
lnWb =loExcel.CreateWorkbook("testgg.xlsx")
lnSh = loExcel.AddSheet(lnWb, "Test")
lnStyle = loExcel.CreateFormatStyle(lnWb)
loExcel.AddStyleIndent(lnSh, lnStyle, 4)             && indents by 4 spaces
loExcel.SetCellValue(lnWb, lnSh, 1, 1, "Hello!")
loExcel.SetCellStyle(lnWb, lnSh, 1, 1, lnStyle)
loExcel.SaveWorkbook(lnWb)

 

[gbcpastor]

GBCPastor,

In the Init method, you have:

        TRY
            this.oXDOM = CREATEOBJECT('MSXML2.DOMDocument')
        CATCH TO loException
        *    this.oXDOM = .NULL.
            this.oXDOM.preserveWhiteSpace = .T.     &&& ADDED by SK 082924
        ENDTRY

It must be:

        TRY
            this.oXDOM = CREATEOBJECT('MSXML2.DOMDocument')
            this.oXDOM.preserveWhiteSpace = .T.     &&& ADDED by SK 082924
        CATCH TO loException
            this.oXDOM = .NULL.
        ENDTRY

In the SetCellValue method, you must comment the line after CASE lcDataType = DATA_TYPE_CHAR

*                   txCellValue = ALLTRIM(txCellValue)

In the WriteSheetXmls method, remove the ALLTRIM() as Greg told you. Lines should be

                                    FWRITE(lhFile, ' t="inlineStr"><is><t>' + this.GetXMLString(xl_cells.cellvalue) + '</t></is></c>')

in CASE xl_cells.datatype = DATA_TYPE_CHAR, and

                                FWRITE(lhFile, '<v>' + xl_cells.cellvalue + '</v>')

in CASE xl_cells.datatype = DATA_TYPE_TEXT.

The rest seems Ok.

Absolutely perfect. Thank you so much. I appreciate all your help.