Export table to spreadsheet

exportSpreadsheet

Does anyone know of an alternative method to export data from a table to a spreadsheet?

I am currently using this,  

exportSpreadsheet ( const tableName String, const fileName String [ , const makeRowHeaders Logical ] ) Logical

but it has some extreme limitations,  such as FONT control and the number of lines you can export.

exporting to Excel 5 format from 1997 isn't good enough for you? :-)

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

and the latest automatic update to Office, now you can't copy/paste from these ??

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

The Copy/paste issue can be easily resolved in Excel File block Settings

the new update just landed on us, the end of the week.. it wouldn't even let me edit my own xls files that came out of paradox.. what do we have to change in the settings now? (sigh)

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

In Excel (I am using Excel 2016 64bit,  version 1708 build 8431.2250),

Click the FILE menu;

Click OPTIONS;

on the left,  TRUST CENTER, then Click the TRUST CENTER SETTINGS button(on the right)

Click FILE BLOCK SETTING on the left

UNCHECK the check coxes next to the older excel versions.

did that, long ago.. something new came up last week, don't remember what it was, but I saw the changed behavior (excel 2013) and everyone at the client site saw the same behavior (excel 2017).. I just remember being really annoyed at whatever it was

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

interesting,  my Excel says that I have no updates pending.  works for me

Would love to see someones setup that is not working.

ALSO, and most importantly,  I found a work around to create excel files with the version of excel currently installed.

I'd love to hear about the workaround.. I've thought about a couple of ways to do something by firing off excel, pointing to a table or csv, but haven't tried to build it into an app yet

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Steven,  To start with I am using the excel library from Vladimir Menkin's web page

http://vmsoft.org/excellib.zip

I have added a couple of functions to the library and updated it to Paradox 11.

My additions simply allow me to pass in a table name that Iwant to export and a Spreadsheet Name that I want to save the file as.  I Allow for some header info in the first few rows of the file.

The process works,  but it is slow.  The speed bottleneck is caused by the fact that you have to set one cell value at a time. 

If I can find a way to set/paste an entire row,  then I think it would be useable.

I would upload some files for you,  But I do not see a way to do that on this message board.

I will paste the most significant addition to the code in my next message.

Let me know if you have any thoughts on how to speed this up

This code is still in DEBUG, but this is what I have so far.

method util_TabletoExcelFile(TName string,SSName String, bShowHeader Logical,strCompany String,strHeader String,strAsOfDate String,strPrintDate String) Logical
var
   ExportSuccess Logical

; table variables
   expTable tcursor
   expflds tcursor
   expfldCount LongInt
   fldval anytype

   arRow Array[] AnyType

   hdrRow longInt

   Excel oleauto
   CellAddr string
   startCellAddr String
   EndCellAddr String
   Col longInt
   Row LongInt
   datarows longint

   tabrowdata anytype
endvar

ExportSuccess = False
TRY   ; CREATE the SPREADSHEET
   If ExcelOpen(Excel) then
      If newWorkBook(Excel,"") then
         selectCells(Excel, "A1")
         setCellValue(Excel, strCompany)
         setCellsFontSize(Excel, 16)
         setCellsFontAttrib(Excel, True, False, False)

         selectCells(Excel, "A2")
         setCellValue(Excel, strHeader)
         setCellsFontSize(Excel, 14)
         setCellsFontColor(Excel, 3)
         setCellsFontAttrib(Excel, False, True, False)

         hdrRow = 5
         Row = hdrRow
         COL = 1
         expfldCount = 0
         Try  ; Open Table
              If expTable.Open(Tname) then
                 datarows = expTable.nrecords()
                 expTable.enumFieldStruct(expflds)
                     ; WRITE HEADER ROW
                      scan expflds:
                        convertRowColToA1(Excel, Row, Col, CellAddr)
                           selectCells(Excel, CellAddr)
                           setCellsFontSize(Excel, 10)
                           setCellsFontAttrib(Excel, True, False, False)
                           setCellValue(Excel, expflds."Field Name")
                           COL = COL + 1
                      endScan



                      ;  COL was previously used to increment the header columns,  so now it is a count of the fields in the table plus 1
                         COL = COL - 1   ; set the correct field count

                      ; SET DATA RANGE to same format
                             convertRowColToA1(Excel, Row+1, 1, startCellAddr)
                             convertRowColToA1(Excel, datarows+Row+1, COL, EndCellAddr)
                               setCellsFontSize(Excel, 10)
                               setCellsFontAttrib(Excel, False, False, False)


                      While expTable.eot() = False
                         Row = Row+1 ;  ROW was previously set to the header row


;                      ;ATTEMPT AT PARSE an entire row and passing that into a range
;                             expTable.CopyToArray(arRow)
;                             convertRowColToA1(Excel, Row, 1, startCellAddr)
;                             convertRowColToA1(Excel, Row, COL, EndCellAddr)
;                             selectCells(Excel, startCellAddr+":"+EndCellAddr)
;                          tabrowdata = ""
;                          For expfldCount from 1 to COL
;                               expTable.FieldValue(expfldCount,fldval)
;                               tabrowdata = tabrowdata + fldval
;                               if expfldCount < COL then
;                                  tabrowdata = tabrowdata +chr(9)
;                               endif
;                          Endfor
 ;                            setCellValue(Excel, tabrowdata)

                          ; SET one field at a time
                           For expfldCount from 1 to COL
                               convertRowColToA1(Excel, Row, expfldCount, CellAddr)
                               selectCells(Excel, CellAddr)
                               expTable.FieldValue(expfldCount,fldval)  ; get the value from the field   ;  might be faster to pur each tcursur record into an array onetime then get the values in a loop on the array
                               setCellValue(Excel, fldval)
                           Endfor
                           expTable.NextRecord()
                      endWhile

                      expflds.Close()
                      expTable.Close()
                      saveActiveWorkbookAs(Excel, SSName)
               endif

         OnFail
               ExportSuccess = False
               ErrorShow()
         EndTry
      Endif
      ExcelShow(Excel)
;      ExcelClose(Excel)

   ELSE

   EndIf
OnFail
      ExportSuccess = False
      ErrorShow()
EndTry

Return ExportSuccess

endMethod

that's kinda like File/Open/myfile.txt, where you have to manually declare each column.. yeah, looks nice, but looks complex

I was thinking more along the lines of using Execute or ShellExecuteA to fire off a batch file that would simulate File/Open/myfile.csv, with a "save as" instruction, on the command line

don't know if that's simple, or it'd need some VB code to make it simple.. too busy putting out other fires :-)

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

I resolved this issue.   I no longer have version issues in excel.

In the end I wrote a parser to create an xml representation of the data,  then ask excel to open it.

The export process is super fast and it gives me the ability to add headers, create freeze panes, and bold face text.

all the developer has to do is call the function with the following arguments

• TName string ;  Table name to export
• SSName String; Spreadsheet name to save as
• bShowHeader Logical;  Yes/No to show header info in first four rows
• strCompany String; client name
• strHeader String ; report name
• strAsOfDate String ; As of date for the data in the file
• strPrintDate String; print date of the data (creation date)
• OpenExcel Logical ;  Display in excel when complete

more or less, what I had in mind.. only fancier :-)

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Another approach is a free 3rd party (ancient) tool called Sportamok's Paradox dBase Reader (v2.2.0). When I'm sharing database files with non-Paradox users, that's what I suggest.

Peace,
David Cheney

interesting.. never heard of that tool, but I just looked at their web page

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

One of our PCs was recently upgraded to Office 365, our other PCs are still running Excel 2016. It's no longer possible to export using exportspreadsheet on the 365 machine. You can still go to File, Export on the 365 machine and export to xls. But then you have to open the file and format the columns so that external users can import the file into their databases (not Paradox). In the past I have successfully automated this process and cut down on misreading errors for the importers.

Does anyone know why Office 365 is behaving this way? I've gone into the Trust Center and changed all the File Blocking Settings.

I don't have office 365, but.. are you saying the exportspreadsheet function won't work (what kind of error are you getting from paradox), or that 365 can't open the files (it won't take excel5 files)?

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

ExportSpreadsheet definitely doesn't work on that machine so I don't think it's an Excel issue. I'm now inclined to reinstall Paradox on that machine in the hope that the command line will work again.

The error message is 'Sorry we couldn't find...xls'. When you manually go to find the file it isn't there, although the db file is there. That's why I think it might be the command that's either missing or corrupted.

you'll have to explain with a bit more detail..

are you running paradox interactively, using scripts/forms/etc, or via a command line/batch file process?

are you getting a paradox error when you use exportspreadsheet, an error from your app? an error message from some other program?

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

This is the code attached to the button:

method pushButton(var eventInfo Event)
var
StartDate, EndDate String
StrDoc, StrDir String
MyQBE, qVar, qVar1, qVar2 Query
pmMenu PopupMenu
db Database
strFileName,StrName, StrName1 String
liRetVal longInt
elib array[1] library
Excel oleauto
tbl Table
tcStruct TCursor
dynNewStru DynArray[] AnyType
ui UIObject
endVar
Action(DataEndEdit)
;Edit.setLabel()

StartDate.view("START: Day/Month/Year:")
EndDate.view("END: Day/Month/Year:")

if not db.open(":Primary:")
then errorshow()
Return
endif

MyQBE=Query
Answer: :Private:DDReport.db

:Primary:Primary Data.DB | Customer No | Name | Bank Name | Bank Address | AC1 | AC2 | AC3 | AC4 | AC5 | AC6 | AC7 | AC8 | SC1 | SC2 | SC3 | SC4 | SC5 | SC6 | PayMethod | Lessor | Vat Rate | File Status | DDCodeCoNo |
| _Join1 | Check As Payers Name | calc _AC1+_AC2+_AC3+_AC4+_AC5+_AC6+_AC7+_AC8 As Account Number | calc _S1+_S2+_S3+_S4+_S5+_S6 As Sort Code | _AC1 | _AC2 | _AC3 | _AC4 | _AC5 | _AC6 | _AC7 | _AC8 | _S1 | _S2 | _S3 | _S4 | _S5 | _S6 | =Direct Debit | _Join2, =8 or 47 or 62 | _V | =Live or Income or Managed or Invoiced | Check As Org Ref |

:Primary:Primary Payments.DB | Customer No | Due Date | Rental | Inv | Service | Late Payment Charge | Annual Fees | Insurance Premium | List | MNS | Income |
| _Join1 | Check >=~StartDate, <=~EndDate, As Date | _R, calc((_A+_I+_L+_S+_R)*(1+_V/100)) As Amount | =Blank | _S | _L | _A | _I | Check as Transaction Type | Check as Account Type | |

:Primary:Primary Lessor.DB | Lessor | Lessor Name | Org Sort Code | Org Acc No |
| _Join2 | Check As Org Name | Check | Check |

EndQuery

if NOT MyQBE.ExecuteQBE() then
errorshow()
endif
tbl.attach(":Private:DDReport.DB")
tbl.enumFieldStruct(":Private:DDReport_Struct.DB")

TcStruct.open(":Private:DDReport_Struct.DB")
TcStruct.edit()
scan TcStruct:
if TcStruct."Size" > 18
then
TcStruct."Size" = 18
endIf
endscan
TcStruct.endEdit()
TcStruct.close()

dynNewStru["FIELDSTRUCT"] = (":Private:DDReport_Struct.db")
Tbl.restructure(dynNewStru)
if ui.attach(":Private:DDReport_Struct.db") then
ui.delete()
DMRemoveTable(":Private:DDReport_Struct.db")
tbl.attach(":Private:DDReport_Struct.db")
tbl.delete()
endif

strFileName=":Private:DDReportSort.qbe"
if qVar.readFromFile(strFileName) = FALSE then
msgStop("Error reading " + strFileName,
"Please make sure the file exists and try again.")
return
endIf

if NOT qVar.executeQBE() then
errorShow()
endif

strName=":Private:DDChange17.qbe"
if qVar1.readFromFile(strName) = FALSE then
msgStop("Error reading " + strName,
"Please make sure the file exists and try again.")
return
endIf

if NOT qVar1.executeQBE() then
errorShow()
endif

strName1=":Private:LessorChange.qbe"
if qVar2.readFromFile(strName1) = FALSE then
msgStop("Error reading " + strName1,
"Please make sure the file exists and try again.")
return
endIf

if NOT qVar2.executeQBE() then
errorShow()
endif

If Not exportSpreadsheet(":Private:DDReport.db","Transaction.xls", True)
then
errorShow()
endIf

strDir = "W:\\Primary\\Leasing\\"
strDoc = "Transaction.xls"

if not elib[1].open(":Primary:excel") then
msgstop("Error","Can't open Excel") return
endif
;open Excel
if not elib[1].ExcelOpen(Excel) then
msgstop("Error","Can't open Excel application") return
endif
if not isFile( strDir + strDoc ) then
msgStop( "Can't Open File", "Reason: Can't find a file named " +
strDir + strDoc + "\"; please check the name." )
else
ShellExecuteA( 0, "open", StrDoc, "", StrDir, 3 )
endIf

;bold header
elib[1].selectCells(Excel,"A1:K1")
elib[1].setCellsFontAttrib(Excel,true,false,false)

;Column format
elib[1].selectCells(Excel,"A1:K150")
elib[1].autofitColumnWidth(Excel)

;text format
elib[1].selectCells(Excel,"A2:F150")
elib[1].setCellsFormat(Excel,"@")

;Number format
elib[1].selectCells(Excel,"G2:G150")
elib[1].setCellsFormat(Excel,"###0.00")

;text format
elib[1].selectCells(Excel,"H2:J150")
elib[1].setCellsFormat(Excel,"@")

;date format
elib[1].selectCells(Excel,"K2:K150")
elib[1].setCellsFormat(Excel,"dd/mm/yy")

;Save workbook

elib[1].saveActiveWorkbookAs(Excel,"W:\\Primary\\Leasing\\Transaction.xls")

excel.close()

endMethod

The exportspreadsheet isn't creating the xls file which is why I think Paradox needs to be reloaded.

the one thing I see.. when I use ShellExecuteA, I don't put the trailing slashes at the end of the folder name, but I don't know if that matters

you - strDir = "W:\\Primary\\Leasing\\"

 me - strDir = "W:\\Primary\\Leasing"

other than that, looks like it skips all the earlier errors, it thinks it made the file, it thinks it opened excel, etc

only other thing, do you have full read/write/create to that folder?

you can reinstall, I guess, to see if it helps.. sorry I can't think of anything else

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Hi andwye, I use Office 365 and I believe (based on your syntax) the same excel library that you are using.

I have a Paradox app installed on more than 100 customer machines using various excel versions. 

The library excel is old and I found that it is a little unreliable from the standoint of using it to directly populate files. 

I now use a function (that I wrote into the same Lib) that exports my table to an XML text file in my privatre folder,  then using the excel library I open Excel, tell excel to open the  XML, then save it to the target name/path as a worksheet.

Your actual customization may be more complicated than mine,  but for me this process is actually very simple.

In the end,  I get a spreedsheet that is compatible with whichever version of excel happens to be installed. It has a customized header regardless of which report was run(we have 50+ reports in our app)

I would be happy to share what I am using.  I don't think this website allows attachements, so we will need to make other arrangements.

Perhaps direct email  or  webmeeting(I can provide)

Let me know if you are interested.

Jeff

First reply to Steven,

I uninstalled Paradox on the offending PC and reinstalled Paradox 9 and upgraded to version 3. The export to spreadsheet command now works. However, the subsequent formatting commands still don't work. I'm now looking at the ODBC to see if the two programs can talk to each other.

Second reply to Jeff,

I would definitely like to know more about your program/function. As I'm based in the UK, and the time differences between here and Frankfort is 5 hours it would be better to exchange emails.

Andrew Wyeth

andrew@leasingprogrammes.co.uk

I look forward to hearing from you. 

just keep one thing in mind.. it's why Jeff, myself, and others, are often having to reinvent the wheel.. paradox development stopped in 2002.. it talked to excel 5, lotus, quattro, etc..

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

I do understand and that's why I'm installing a Filemaker system but it's taking time to replicate the versatility that we have with Paradox. Such a shame!