A help please - Una Ayuda por Favor

[Jose Marte2]

Ingles:

Greetings to all, I hope you are well.

I apologize for the moletias. I would like if you can help with what I'm going to ask you now.

I have a DBF table that I filled with several records.

I use the dc_workarea2excel () instruction to export that table to excel. All perfect, the problem is that I have an alphanumeric field that contains characters that starts with 031, for example. When you export the table to excel in that column, you lose 0 and only put 31.

Could you give some help with this topic? Thanks in advance.

Español:

Saludos a todos, espero que estén bien.

Pido disculpas por las moletias. Me gustaría preguntarte ahora.

Tengo una tabla DBF que llené con varios registros.

Uso la declaración dc_workarea2excel () para exportar esa tabla a Excel. Todo perfecto, el problema es que tengo un campo alfanumérico que contiene caracteres que comienzan con 031, por ejemplo. Cuando exporta la tabla para sobresalir en esa columna, pierde 0 y solo pone 31.

¿Podrías dar alguna ayuda con este tema? Gracias por adelantado.

[patito]

Jose

Una manera rápida , es renombrar el archivo .dbf a .xls

y otra alternativa es usar esta librería

HBLibXL.PRG - Klassendefinitionen für Xbase++ und LibXL.DLL
* HBLibXL.PRG - CLASS CODE for Xbase++ und LibXL.DLL
** My Homepage: http://www.familie-brandel.de (german
* http://www.familie-brandel.de/index_e.html (english)

A quick way is to rename the .dbf file to .xls.
and another alternative is to use this library LibXL.DLL



Saludos
Héctor Pezoa

[rdonnay]

Jose -

Upload your database.
I will figure out what to do about this.

[Jose Marte2]

Jose -

Upload your database.
I will figure out what to do about this.

Yes sir. thank you

[Jose Marte2]

Jose -

Upload your database.
I will figure out what to do about this.

Yes sir. thank you

Mr. thank you very much for your attention.

I can complete the idea of ​​what I want to do.

That table that filled with several dbf
of invoices, returns, credit notes, etc. I need it to arrive organized to excel by a column specified in this case by the column IDCLIE as you will see it in the table that you send as an example.

Thank you

Epañol:

Sr. muchas gracias por su atencion.

Le puedo completar la idea de lo que quiero hacer.

Esa tabla que lleno de varios dbf
de facturas, devoluciones, notas de creditos, etc. la necesito que llegue organizada a excel por una columna especifica en este caso por la columna IDCLIE como usted lo vera en la tabla que le envie de ejemplo.

Gracias

[Jose Marte2]

Jose

Una manera rápida , es renombrar el archivo .dbf a .xls

y otra alternativa es usar esta librería

HBLibXL.PRG - Klassendefinitionen für Xbase++ und LibXL.DLL
* HBLibXL.PRG - CLASS CODE for Xbase++ und LibXL.DLL
** My Homepage: http://www.familie-brandel.de (german
* http://www.familie-brandel.de/index_e.html (english)

A quick way is to rename the .dbf file to .xls.
and another alternative is to use this library LibXL.DLL


Mr. thank you very much, for the help and for your attention


Muchas gracias señor, por la ayuda y por su atención.

Saludos
Héctor Pezoa

[rdonnay]

I am guessing that you have an older version of eXpress++.

This problem was resolved several years ago in eXpress++ code.
Attached is Worksheet.xls created from the most current version.

You need to modify your DC_WorkArea2Excel() function in _DCFUNCT.PRG.

Then you need to rebuild your DCLIPX.DLL by running BUILD19_SL1.BAT or BUILD20.BAT

Here is the source code for the most current version of DC_WorkArea2Excel().

Code: Select all

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

LOCAL oExcel, oBook, oSheet, nRow, aStru, i, cHeader, ;
      cFieldName, cFieldType, nFieldLen, nFieldDec, cFormat, ;
      xValue, GetList[0], GetOptions, oDlg, nCount := 0, ;
      cDbfName, nKeyCount, oProgress, lStatus := .t., aData, ;
      cRow, cColumns, cRange, aRow, bError, aStru2, nFound, ;
      cFieldValue, nFieldBlock, cValue, aScope

DEFAULT nOrientation := xlLandscape, ;
        lDisplayAlerts := .f., ;
        lVisible := .f., ;
        lAutoFit := .f., ;
        cDateFormat := "US", ;     // US, USSHORT, EURO, EUROSHORT, or send custom?
        aFieldEvals := {}, ;
        cExcelFile := DC_Path(AppName(.t.)) + 'worksheet.xls', ;
        lFreezeRow1 := .t., ;
        lCsvFallBack := .f.

//  aFieldEvals -> {{FIELDNAME,CodeBlock},....}  Code blocks to evaluate for specific fields

#if XPPVER > 1900000
  // Create the "Excel.Application" object
  IF '.CSV' $ Upper(cExcelFile)
    RETURN DC_WorkArea2Csv(cExcelFile)
  ENDIF
  oExcel := CreateObject("Excel.Application")
  IF Empty( oExcel )
    IF lCsvFallBack
      DCMSGBOX 'Excel is not installed. Create CSV file instead?' YESNO TO lStatus
      IF lStatus
        RETURN DC_WorkArea2Csv(cExcelFile)
      ELSE
        RETURN .f.
      ENDIF
    ELSE
      DC_WinAlert( "Excel is not installed" )
    ENDIF
    RETURN .f.
  ENDIF
#else
  DC_WinAlert('This feature is available in Xbase++ 1.9 and later only!')
  RETURN .f.
#endif

IF upper(cDateFormat) = "USSHORT"
  cDateFormat := "m\/d\/yyyy;@"
ELSEIF upper(cDateFormat) = "US"
  cDateFormat := "mm\/dd\/yyyy;@"
ELSEIF upper(cDateFormat) = "EUROSHORT"
  cDateFormat := "d\/m\/yyyy;@"
ELSEIF upper(cDateFormat) = "EURO"
  cDateFormat := "dd\/mm\/yyyy;@"
ENDIF

// Avoid message boxes such as "File already exists". Also,
// ensure the Excel application is visible.
oExcel:DisplayAlerts := lDisplayAlerts
oExcel:visible       := lVisible

// Add a workbook to the Excel application. Query for
// the active sheet (sheet-1) and set up page/paper
// orientation.

cDbfName := dbInfo(DBO_FILENAME)
IF cDbfName = '<CURSOR>'
  nKeyCount := RecCount()
ELSE
  nKeyCount := DC_KeyCount()
ENDIF

aScope := DC_SetScopeArray()

@ 0,0 DCSAY 'Creating Excel Worksheet: ' + cExcelFile SAYSIZE 0

@ 1,0 DCPROGRESS oProgress SIZE 50,1 ;
      TYPE XBPSTATIC_TYPE_TEXT ;
      COLOR GRA_CLR_CYAN, GRA_CLR_WHITE ;
      PERCENT ;
      PERCENTCOLOR GRA_CLR_RED ;
      RADIUS 20 ;
      OUTLINE ;
      DYNAMIC ;
      EVERY Int(nKeyCount/100)

@ 3,0 DCPUSHBUTTON CAPTION 'Cancel' SIZE 9,1.2 ACTION {||lStatus:=.f.}

DCGETOPTIONS NORESIZE ALWAYSONTOP _PIXEL .f.
DCREAD GUI FIT TITLE 'Exporting to Excel' ;
   MODAL EXIT PARENT @oDlg OPTIONS GetOptions NOAUTORESTORE

oBook  := oExcel:workbooks:Add()
oSheet := oBook:ActiveSheet
oSheet:PageSetup:Orientation := nOrientation

DC_DbGoTop()

nRow := 1

// Feed in the data from the table to the Cells
// of the sheet.

aStru := dbStruct()

IF Valtype(aFields) == 'A'
  aStru2 := AClone(aStru)
  aStru := Array(0)

  FOR i := 1 TO Len(aFields)
    cFieldName := Upper(Alltrim(aFields[i]))
    nFound := AScan(aStru2,{|a|Upper(a[1])==cFieldName})
    IF nFound > 0
      AAdd( aStru, aStru2[nFound] )
    ENDIF
  NEXT
ENDIF

aFields := Array(0)
FOR i := 1 TO Len(aStru)
  cFieldName := aStru[i,1]
  nFieldDec := aStru[i,4]
  IF Valtype(&(cFieldName)) $ 'NF'
    IF nFieldDec == 0
      cFormat := '0'
    ELSE
      cFormat := '0.' + Repl('0',nFieldDec)
    ENDIF
  ELSEIF Valtype(&(cFieldName))=='D'
    cFormat := cDateFormat
  ELSEIF Valtype(&(cFieldName)) $ 'CM'
    cFormat := "@"                        // Preserves leading 0s on strings that look like numbers
  ELSE
    cFormat := ''
  ENDIF
  AAdd(aFields,{cFieldName,cFieldName,cFormat})
NEXT

FOR i := 1 TO Len(aFields)
  IF Len(aFields[i]) < 3
    ASize(aFields[i],3)
  ENDIF
  IF Valtype(aColumnNames) == 'A' .AND. Len(aColumnNames) == Len(aFields)
    cHeader := aColumnNames[i]
  ELSE
    cHeader := aFields[i,2]
  ENDIF
  IF !Empty(cHeader)
    oSheet:Cells(nRow,i):Value := cHeader
  ENDIF
  cFormat := aFields[i,3]
  IF !Empty(cFormat)
    oSheet:Columns(i):NumberFormat := cFormat
  ENDIF
NEXT

aRow := Array(Len(aStru))
aData := Array(0)
cColumns := Get_Excel_Column_ID(Len(aRow))

nRow += 2
DO WHILE !DC_Eof() .AND. lStatus
  DC_CompleteEvents()
  DC_GetProgress(oProgress,nCount++,nKeyCount)
  FOR i := 1 TO Len(aFields)
    cFieldName := aFields[i,1]
    IF Valtype(cFieldName) == 'C'
      cFieldValue := &(cFieldName)
      nFieldBlock := aScan(aFieldEvals,{|a|upper(a[1])==upper(cFieldName)})
      IF nFieldBlock > 0
        cFieldValue := Eval(aFieldEvals[nFieldBlock,2],cFieldValue)
      ENDIF
    ELSEIF Valtype(cFieldName) == 'B'
      cFieldValue := Eval(cFieldName)
    ENDIF
    IF Valtype(cFieldValue) == 'D'
      aRow[i] := Dtoc(cFieldValue)
    ELSEIF Valtype(cFieldValue) = 'C'
      aRow[i] := Trim(cFieldValue)
    ELSEIF Valtype(cFieldValue) = 'M'
      cValue := Substr(Trim(cFieldValue),1,1024)
      aRow[i] := cValue
    ELSE
      aRow[i] := cFieldValue
    ENDIF
  NEXT
  AAdd( aData, AClone(aRow) )
  nRow++
  DC_DbSkip(1)
  IF !Empty(aScope) .AND. !(Recno()$aScope)
    EXIT
  ENDIF
ENDDO

cRange := 'A3:' + cColumns + Ltrim(Str(nRow-1))

oDlg:destroy()

oSheet:Range(cRange):Value := aData

// Force a reformat for the size of the first column
IF lAutoFit
  FOR i := 1 TO Len(aFields)
    oSheet:Columns(i):AutoFit()
  NEXT
ENDIF

IF lFreezeRow1
  oSheet:Range("A1:A1"):EntireRow:Font:Bold := .t.
  oSheet:Activate()
  oSheet:Application:ActiveWindow:SplitRow := 1
  oSheet:Application:ActiveWindow:FreezePanes := .T.
ENDIF

bError := ErrorBlock( {|e| Break(e) } )

BEGIN SEQUENCE

  IF '.XLSX' $ Upper(cExcelFile)
    // Save workbook as ordinary excel file.
    oBook:SaveAs(cExcelFile,xlOpenXMLWorkbook,cPassword)
  ELSE
    // Save workbook as ordinary excel file
    oBook:SaveAs(cExcelFile,xlWorkbookNormal,cPassword)
  ENDIF

RECOVER

END SEQUENCE

ErrorBlock(bError)

oSheet:destroy()
oBook:close()
oBook:destroy()

// Quit Excel
oExcel:Quit()
oExcel:Destroy()

RETURN .t.

[Jose Marte2]

I am guessing that you have an older version of eXpress++.

This problem was resolved several years ago in eXpress++ code.
Attached is Worksheet.xls created from the most current version.

You need to modify your DC_WorkArea2Excel() function in _DCFUNCT.PRG.

Then you need to rebuild your DCLIPX.DLL by running BUILD19_SL1.BAT or BUILD20.BAT

Here is the source code for the most current version of DC_WorkArea2Excel().

Code: Select all

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

LOCAL oExcel, oBook, oSheet, nRow, aStru, i, cHeader, ;
      cFieldName, cFieldType, nFieldLen, nFieldDec, cFormat, ;
      xValue, GetList[0], GetOptions, oDlg, nCount := 0, ;
      cDbfName, nKeyCount, oProgress, lStatus := .t., aData, ;
      cRow, cColumns, cRange, aRow, bError, aStru2, nFound, ;
      cFieldValue, nFieldBlock, cValue, aScope

DEFAULT nOrientation := xlLandscape, ;
        lDisplayAlerts := .f., ;
        lVisible := .f., ;
        lAutoFit := .f., ;
        cDateFormat := "US", ;     // US, USSHORT, EURO, EUROSHORT, or send custom?
        aFieldEvals := {}, ;
        cExcelFile := DC_Path(AppName(.t.)) + 'worksheet.xls', ;
        lFreezeRow1 := .t., ;
        lCsvFallBack := .f.

//  aFieldEvals -> {{FIELDNAME,CodeBlock},....}  Code blocks to evaluate for specific fields

#if XPPVER > 1900000
  // Create the "Excel.Application" object
  IF '.CSV' $ Upper(cExcelFile)
    RETURN DC_WorkArea2Csv(cExcelFile)
  ENDIF
  oExcel := CreateObject("Excel.Application")
  IF Empty( oExcel )
    IF lCsvFallBack
      DCMSGBOX 'Excel is not installed. Create CSV file instead?' YESNO TO lStatus
      IF lStatus
        RETURN DC_WorkArea2Csv(cExcelFile)
      ELSE
        RETURN .f.
      ENDIF
    ELSE
      DC_WinAlert( "Excel is not installed" )
    ENDIF
    RETURN .f.
  ENDIF
#else
  DC_WinAlert('This feature is available in Xbase++ 1.9 and later only!')
  RETURN .f.
#endif

IF upper(cDateFormat) = "USSHORT"
  cDateFormat := "m\/d\/yyyy;@"
ELSEIF upper(cDateFormat) = "US"
  cDateFormat := "mm\/dd\/yyyy;@"
ELSEIF upper(cDateFormat) = "EUROSHORT"
  cDateFormat := "d\/m\/yyyy;@"
ELSEIF upper(cDateFormat) = "EURO"
  cDateFormat := "dd\/mm\/yyyy;@"
ENDIF

// Avoid message boxes such as "File already exists". Also,
// ensure the Excel application is visible.
oExcel:DisplayAlerts := lDisplayAlerts
oExcel:visible       := lVisible

// Add a workbook to the Excel application. Query for
// the active sheet (sheet-1) and set up page/paper
// orientation.

cDbfName := dbInfo(DBO_FILENAME)
IF cDbfName = '<CURSOR>'
  nKeyCount := RecCount()
ELSE
  nKeyCount := DC_KeyCount()
ENDIF

aScope := DC_SetScopeArray()

@ 0,0 DCSAY 'Creating Excel Worksheet: ' + cExcelFile SAYSIZE 0

@ 1,0 DCPROGRESS oProgress SIZE 50,1 ;
      TYPE XBPSTATIC_TYPE_TEXT ;
      COLOR GRA_CLR_CYAN, GRA_CLR_WHITE ;
      PERCENT ;
      PERCENTCOLOR GRA_CLR_RED ;
      RADIUS 20 ;
      OUTLINE ;
      DYNAMIC ;
      EVERY Int(nKeyCount/100)

@ 3,0 DCPUSHBUTTON CAPTION 'Cancel' SIZE 9,1.2 ACTION {||lStatus:=.f.}

DCGETOPTIONS NORESIZE ALWAYSONTOP _PIXEL .f.
DCREAD GUI FIT TITLE 'Exporting to Excel' ;
   MODAL EXIT PARENT @oDlg OPTIONS GetOptions NOAUTORESTORE

oBook  := oExcel:workbooks:Add()
oSheet := oBook:ActiveSheet
oSheet:PageSetup:Orientation := nOrientation

DC_DbGoTop()

nRow := 1

// Feed in the data from the table to the Cells
// of the sheet.

aStru := dbStruct()

IF Valtype(aFields) == 'A'
  aStru2 := AClone(aStru)
  aStru := Array(0)

  FOR i := 1 TO Len(aFields)
    cFieldName := Upper(Alltrim(aFields[i]))
    nFound := AScan(aStru2,{|a|Upper(a[1])==cFieldName})
    IF nFound > 0
      AAdd( aStru, aStru2[nFound] )
    ENDIF
  NEXT
ENDIF

aFields := Array(0)
FOR i := 1 TO Len(aStru)
  cFieldName := aStru[i,1]
  nFieldDec := aStru[i,4]
  IF Valtype(&(cFieldName)) $ 'NF'
    IF nFieldDec == 0
      cFormat := '0'
    ELSE
      cFormat := '0.' + Repl('0',nFieldDec)
    ENDIF
  ELSEIF Valtype(&(cFieldName))=='D'
    cFormat := cDateFormat
  ELSEIF Valtype(&(cFieldName)) $ 'CM'
    cFormat := "@"                        // Preserves leading 0s on strings that look like numbers
  ELSE
    cFormat := ''
  ENDIF
  AAdd(aFields,{cFieldName,cFieldName,cFormat})
NEXT

FOR i := 1 TO Len(aFields)
  IF Len(aFields[i]) < 3
    ASize(aFields[i],3)
  ENDIF
  IF Valtype(aColumnNames) == 'A' .AND. Len(aColumnNames) == Len(aFields)
    cHeader := aColumnNames[i]
  ELSE
    cHeader := aFields[i,2]
  ENDIF
  IF !Empty(cHeader)
    oSheet:Cells(nRow,i):Value := cHeader
  ENDIF
  cFormat := aFields[i,3]
  IF !Empty(cFormat)
    oSheet:Columns(i):NumberFormat := cFormat
  ENDIF
NEXT

aRow := Array(Len(aStru))
aData := Array(0)
cColumns := Get_Excel_Column_ID(Len(aRow))

nRow += 2
DO WHILE !DC_Eof() .AND. lStatus
  DC_CompleteEvents()
  DC_GetProgress(oProgress,nCount++,nKeyCount)
  FOR i := 1 TO Len(aFields)
    cFieldName := aFields[i,1]
    IF Valtype(cFieldName) == 'C'
      cFieldValue := &(cFieldName)
      nFieldBlock := aScan(aFieldEvals,{|a|upper(a[1])==upper(cFieldName)})
      IF nFieldBlock > 0
        cFieldValue := Eval(aFieldEvals[nFieldBlock,2],cFieldValue)
      ENDIF
    ELSEIF Valtype(cFieldName) == 'B'
      cFieldValue := Eval(cFieldName)
    ENDIF
    IF Valtype(cFieldValue) == 'D'
      aRow[i] := Dtoc(cFieldValue)
    ELSEIF Valtype(cFieldValue) = 'C'
      aRow[i] := Trim(cFieldValue)
    ELSEIF Valtype(cFieldValue) = 'M'
      cValue := Substr(Trim(cFieldValue),1,1024)
      aRow[i] := cValue
    ELSE
      aRow[i] := cFieldValue
    ENDIF
  NEXT
  AAdd( aData, AClone(aRow) )
  nRow++
  DC_DbSkip(1)
  IF !Empty(aScope) .AND. !(Recno()$aScope)
    EXIT
  ENDIF
ENDDO

cRange := 'A3:' + cColumns + Ltrim(Str(nRow-1))

oDlg:destroy()

oSheet:Range(cRange):Value := aData

// Force a reformat for the size of the first column
IF lAutoFit
  FOR i := 1 TO Len(aFields)
    oSheet:Columns(i):AutoFit()
  NEXT
ENDIF

IF lFreezeRow1
  oSheet:Range("A1:A1"):EntireRow:Font:Bold := .t.
  oSheet:Activate()
  oSheet:Application:ActiveWindow:SplitRow := 1
  oSheet:Application:ActiveWindow:FreezePanes := .T.
ENDIF

bError := ErrorBlock( {|e| Break(e) } )

BEGIN SEQUENCE

  IF '.XLSX' $ Upper(cExcelFile)
    // Save workbook as ordinary excel file.
    oBook:SaveAs(cExcelFile,xlOpenXMLWorkbook,cPassword)
  ELSE
    // Save workbook as ordinary excel file
    oBook:SaveAs(cExcelFile,xlWorkbookNormal,cPassword)
  ENDIF

RECOVER

END SEQUENCE

ErrorBlock(bError)

oSheet:destroy()
oBook:close()
oBook:destroy()

// Quit Excel
oExcel:Quit()
oExcel:Destroy()

RETURN .t.

Ingles:
Greetings, Mr.
Thank you very much for your help and your attentions

Mr. I apologize but to perform the operation that tells me the compilation gives me the following errors

variable xlOpenXMLWorkbook not found and function DC_WorkArea2Csv not found

Could you help me with that part

Thank you.

Español:

Saludos, Sr.
Mil gracias por su ayuda y sus atenciones

Sr. me disculpa pero a realizar la operacion que me dice el la compilacion me da los siguientes errores

variable xlOpenXMLWorkbook no encontrada y funcion DC_WorkArea2Csv no encontrada

Me podria usted ayudar con esa parte

Muchas gracias.

[Auge_Ohr]

variable xlOpenXMLWorkbook not found

that is a Constant of Excel COM.

you can get these COM Constant using Tlb2Ch.exe and CLSID or ProgID

open a CMD Box and copy/ paste this

Code: Select all

Tlb2Ch.exe "Excel.Application" >> EXCEL.CH

you will get EXCEL.CH with all Constant of Excel COM

p.s. Tlb2Ch.exe is found in same Directory like XPP.EXE

[Eugene Lutsenko]

The reason for the described problem is that the number with leading zero is perceived as a number, not as a text. There are several very simple ways to fix this.
1. Make a fictitious string in the database with text values in the fields. Then the entire field will be treated as a text type field.
2. Instead of the number 0 to write the letter O.
3. Before numbers with leading zeros to put some character, can be not displayed.