DC_WorkArea2Excel()

[Eugene Lutsenko]

For some reason, this function creates Excel-file with two headers separated by a blank line. Everything else turns perfectly. Somebody tell me how to do that to retrieve files from one heading?

Code: Select all

cExcelFile = M_ApplsPath+"\Inp_data\Out_transp.xls"
DC_WorkArea2Excel( cExcelFile,,,,,,,,, aColumnNames )

[rdonnay]

I don't have enough information to help you.

[Eugene Lutsenko]

Hi, Roger! I am glad to talk with you!

In my program did transpose mode database. This mode provides, for example, such a file:



This piece of software that converts DBF to Excel:

Code: Select all

***** Записать транспонированный файл в виде Excel-файла

CLOSE ALL
USE Out_transp EXCLUSIVE NEW
SELECT Out_transp
aFields      := {}
aColumnNames := {}
FOR j=1 TO FCOUNT()
    AADD(aFields, FIELDNAME(j))
    AADD(aColumnNames, A_RecName[j])
NEXT

*DC_WorkArea2Excel( cExcelFile, nOrientation, lDisplayAlerts, ;
*                            lVisible, aFields, lAutoFit, cDateFormat, aFieldEvals, ;
*                            cPassword, aColumnNames )

cExcelFile = M_ApplsPath+"\Inp_data\Out_transp.xls"
DC_WorkArea2Excel( cExcelFile,,,,,,,,, aColumnNames )

The result is what is shown in the figure below:



The last figure shows the first source database, then transpose via Excel. So you need to do in my program, so this is perfect. And then what really gives the program is already in the field of conversion Excel. We have two header columns separated by a blank line.

Crossed out the extra line. How to convert dbf to Excel so that they do not occur?

[Eugene Lutsenko]

It seems to have understood what was going on. DC_WorkArea2Excel function displays the names of fields DBF-file, and then another after another blank line and column names from a file I can now clarify the issue. How can I make this function do not output column names fields and a blank line after them? Or maybe a little to alter this function so that it works?

[rdonnay]

What does the function A_Recname() return?

[Eugene Lutsenko]

This is an array with the names of the columns. It contains the names that I would like them to have. And this name (rec), because all this mode of transposition of matrices.

[rdonnay]

Ok, I have worked out the problems.

It appears that you have made changes to DC_WorkArea2Excel() to add the capability to use custom column headers.
Your code obviously is not working.

I have made the following changes to DC_WorkArea2Excel() which will be in build 261.

Code: Select all

 1349. Added 3 new parameters to DC_WorkArea2Excel().  <lFreezeRow1>
       (default TRUE) causes the first row of the Excel sheet to be bold font
       and locked from scrolling. <lCSVFallBack> (default FALSE) causes a CSV
       file to be created in the event that the Excel ActiveX control cannot
       be loaded. <aColumnNames> is a 1-dimensional array with the names of
       the columns.  Default is the field name.

I have attached an updated _DCFUNCT.PRG.
Copy it to your \exp19\source\dclipx folder and run BUILD19_SL1.BAT to rebuild DCLIPX.DLL.

Here is the sample program I used to test everything.

Code: Select all

#INCLUDE "dcdialog.CH"
#Pragma Library("dclip1.lib")

FUNCTION Main()

LOCAL j, aFields[0], aColumnNames[0], cExcelFile

DC_LoadRdds()
USE \exp19\data\xtest INDEX \exp19\data\xtest.cdx VIA 'FOXCDX'
DC_SetScope(0,'100')
DC_SetScope(1,'100')

FOR j := 1 TO FCount()
  AADD(aFields, FieldName(j))
  AADD(aColumnNames, DC_CapFirst(Lower(FieldName(j))))
NEXT

// DC_WorkArea2Excel( cExcelFile, nOrientation, lDisplayAlerts, ;
//                   lVisible, aFields, lAutoFit, cDateFormat, aFieldEvals, ;
//                   cPassword, lFreezeRow1, lCSVFallBack, aColumnNames )

cExcelFile = 'c:\test\lutsenko\xtest.xls'

DC_DbGoTop()
DC_WorkArea2Excel( cExcelFile,,,.f.,aFields,,,,,,, aColumnNames )
DC_SpawnUrl( cExcelFile )

RETURN nil

* -----------

PROC appsys ; RETURN

[Eugene Lutsenko]

Thank you!

I obtained a single sheet of two tables, the left needs and the right superfluous. Line of column headings in bold, that's what we need. But after it is empty and another line, like a line of column headings that are also unnecessary. All unnecessary elements I identified with a yellow background. Whether they can somehow get rid of?

Inp_xls => dbf => Out_xls
Out_xls = Inp_xls

[Eugene Lutsenko]

I would like to receive the following result. If you convert xls to dbf, and then back to the dbf to xls, then the source and destination xls files must be identical.

Inp_xls.xls => Inp_xls.dbf => Out_xls.xls
Out_xls.xls === Inp_xls.xls

I tried to do it, but for some reason the final xls-file is not written with the name "Out_xls.xls"

Example here. Roger text functions have not changed.

http://lc.kubagro.ru/Dima/xls-dbf-xls.rar

May be included in the 261 assembly function: LC_Excel2WorkArea()?
It works well and is useful.

[rdonnay]

Can you please just give me a small sample program that demonstrates your problem?

I don't see how it is possible to do what you want.

There is insufficient information in an XLS file to create a DBF file.
There is no field length, type or decimal info.
I don't know what the yellow data is. If you want to filter out some data, you will need to write custom code for this.

Maybe the better solution for you is to copy the data to an array and use DC_Array2Excel().