| Subject | Re: ExportData.wfm problem |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Mon, 14 Jul 2014 17:41:11 +0200 |
| Newsgroups | dbase.getting-started |
On Mon, 14 Jul 2014 11:50:58 +0200, Pieter van Heerden <psvh@mweb.co.za> wrote: > Creating a CSV file is easy and works fine, problem is that I also need > column headings, therefore the preference for Excel. You can use virtually the same technique to include the column headings in the .csv file as you've used to place them in the EXCEL file. The EXCEL route does, of course, have the advantage that you can format cells as you place values whereas with the .csv file you can't. Something like cLine = "" for n = 1 to q.rowset.fields.size cLine += '"'+q.rowset.fields[n].fieldname+'",' next cLine = substr(cLine,1,rat(',',cLine)-1) //get rid of final comma fCSV.writeln(cLine) Now add the records. Character values only really need to be wrapped in quote marks if they contain commas but adding the quote marks even where they aren't needed does no harm. > I went ahead and with the help of the dBase Book and Gary White on > Knowledgbase built an export program, which, surprisingly enough, > actually works. However, it takes a long time to export large rowsets, > but Gary mentioned an approach to speed up things. I'll try that next > and see what happens. > > Is there any way of getting past the warning that Excel gives the user > when opening the resultant spreadsheet? It is to the effect that the > spreadsheet is of older version than indicated by the name and asks the > user if it is safe to open the file. I'm afraid I don't know how to get rid of the warning. Mervyn. > > The resultant program is: > > // This program exports data to an Excel worksheet. > // Excel 2010 warns and asks if it is save to open - Respond with "Yes" > // Based on an example in the dBase Book by Ken Mayer > // Placing of column heaadings based on Gary White in dBase Knowldgebase > advanced > > // Adapted and tested by Pieter van Heerden > // 14 July 2014 > > // A query is defined in a calling program, made active and then > // this program is called with "Do ExportToExcel with <queryname>" > > // Get query name as parameter passed from calling program > parameters q > // instantiate Excel object > oExcel = new oleAutoclient("excel.application") > // create new workbook > oWorkBook = oExcel.workbooks.add() > // Loop through the rows of the table: > nRows = 1 > // First, let's put in column headings > for i = 1 to q.rowset.fields.size > // select a cell > oExcel.ActiveSheet.cells( nRows, i ).select() > with ( oExcel.ActiveCell ) > // set the width to match the field length > ColumnWidth := q.rowset.fields[i].length > // set the content to the field name > formula := q.rowset.fields[i].fieldName > endwith > endfor > // Follow this by exporting the data > do while not q.rowset.endOfSet > nRows++ // increment row counter > // Loop through the fields (columns) of the table, > // but not the image field which is the last one: > for nCols = 1 to q.rowset.fields.size - 1 > // get the value: > xValue = q.rowset.fields[ nCols ].value > // get the new cell to add data to: > oCell = oExcel.ActiveSheet.cells( nRows, nCols ) > // assign value to the cell: > oCell.formula = xValue > // from Gary White’s Xldef.h > // set the vertical alignment to the top: > oCell.verticalAlignment := -4160 > next // field > // Next row: > q.rowset.next() > enddo > // size the columns to fit > oExcel.ActiveSheet.Columns.AutoFit() > // save to disk > oWorkBook.SaveAs(putfile("Save data as Excel file","","",false,"*.xls")) > // close Excel > oExcel.quit() > // cleanup: > oExcel = null > > | |