Clipper-excel

[Daniel Du Pré]

Hola Amigos,
Hace mucho tiempo yo usaba CLIPWKS53.LIB para crear planillas de excel. ¿Hay alguna librería que sustituya a esa con su manual?

Gracias por la ayuda
Saludos cordiales
Daniel

Google traslator

Hello friends,
A long time ago I used CLIPWKS53.LIB to create Excel spreadsheets. Is there any library replacing that with your manual?


Thanks for the help
Best regards
Daniel

[Antonino Perricone]

Hello,

In our application we use ole object to export to excel and to OpenOffice calc:

//#define SHOW_PROGRAMMA

class ExcelExporter

   // MSExcel

   DATA oExcel

   // Openoffice

   DATA oServiceManager, oDoc

   // comune

   DATA oSheet

   DATA formats

   

   METHOD New() Constructor

   //METHOD SetSheetName() TODO

   //METHOD SelectSheet(index) TODO

   METHOD SetCellValue(nCol, nRow, val)

   METHOD SetCellFormat(nCol, nRow, n) // 1 currency, 2 date

   METHOD SetCellBackground(nCol, nRow, r,g,b)

   METHOD SetColumnWidth(nCol, w) //w is in millimeters

   METHOD Save()

endclass

METHOD New(cSheetName) class ExcelExporter

   local oWorkBook

   local oDesktop, oParams

   IF ( ::oExcel := win_oleCreateObject( "Excel.Application" ) ) != NIL

      // uso excel

      oWorkBook := ::oExcel:WorkBooks:Add()

      ::oSheet := ::oExcel:ActiveSheet()

      // Limitation on name

      // • The name must be shorten than 31 characters.

      // • The name must not contain character: : \  /  ?  *  [ or ].

      if LEN(cSheetName)>31

         cSheetName := LEFT(cSheetName,31)

      endif

      cSheetName := STRTRAN(cSheetName,":","")

      cSheetName := STRTRAN(cSheetName,"/","")

      cSheetName := STRTRAN(cSheetName,"\","")

      cSheetName := STRTRAN(cSheetName,"?","")

      cSheetName := STRTRAN(cSheetName,"*","")

      ::oSheet:Name := cSheetName

   #ifdef SHOW_PROGRAMMA

      ::oExcel:Visible := .T.

   #endif      

   elseif ( ::oServiceManager := win_oleCreateObject( "com.sun.star.ServiceManager" ) ) != nil

      // use OpenOffice

      oDesktop := ::oServiceManager:createInstance( "com.sun.star.frame.Desktop" )

      oParams := {}

      #ifndef SHOW_PROGRAMMA

         aAdd( oParams, ::oServiceManager:Bridge_GetStruct( "com.sun.star.beans.PropertyValue" ) )

         oParams[1]:Name := "Hidden"

         oParams[1]:Value := .T.

      #endif

      ::oDoc := oDesktop:loadComponentFromURL( "private:factory/scalc", "_blank", 0, oParams )

      ::oSheet := ::oDoc:getSheets:getByIndex(0)

   else

      MsgAlert( "No spreadsheed application found\n Please install and run OpenOffice Calc or MSOffice Excel.", "Alert" )

      return nil

   endif

return Self

METHOD SetCellValue(nCol, nRow, oVal) class ExcelExporter

   if Empty(::oServiceManager)

      //Excel

      ::oSheet:Cells( nRow, nCol ):Value := oVal

   else

      // OpenOffice

      if ValType(oVal) == "C"

         ::oSheet:getCellByPosition(nCol-1,nRow-1):setString( oVal ) 

      else

         ::oSheet:getCellByPosition(nCol-1,nRow-1):setValue( oVal ) 

      endif

   endif

return nil

METHOD SetCellFormat(nCol, nRow, n)  class ExcelExporter

// 1 valuta, 2 data

   if Empty(::oServiceManager)

      //Excel

      switch n

      case 1

         ::oSheet:Cells( nRow, nCol ):NumberFormat = /*chr(128)+*/" #.##0,00"

         exit

      case 2

         ::oSheet:Cells( nRow, nCol ):NumberFormat = "gg/mm/aa"

         exit

      end

   else

      // OpenOffice

      if ::formats == nil

         ::formats := {103,36}   // la valuta non funziona :(

       endif

      ::oSheet:getCellByPosition(nCol-1,nRow-1):setPropertyValue( "NumberFormat", ::formats[n])

   endif

return nil

METHOD SetCellBackground(nCol, nRow, r,g,b) class ExcelExporter

   if Empty(::oServiceManager)

      //Excel

      ::oSheet:Cells( nRow, nCol ):Interior:Color = RGB(r,g,b)

   else

      // OpenOffice

      ::oSheet:getCellByPosition(nCol-1,nRow-1):setPropertyValue( "CellBackColor", RGB(b,g,r)  ) //B e R sono scambiati

   endif

return nil

METHOD SetColumnWidth(nCol, w) class ExcelExporter

   LOCAL oCol

   if Empty(::oServiceManager)

      //Excel

      ::oSheet:Columns(nCol):ColumnWidth := w

   else

      // OpenOffice

      oCol := ::oSheet:getColumns():getByIndex(nCol-1)

      oCol:setPropertyValue( "Width", w*300  )

   endif

return nil

METHOD Save(initialName) class ExcelExporter

   local oDispatcher

   if Empty(::oServiceManager)

      //Excel

      ::oExcel:GetSaveAsFilename(initialName)

#ifndef SHOW_PROGRAMMA

      ::oExcel:Quit()

#endif

   else

      // OpenOffice

      oDispatcher := ::oServiceManager:CreateInstance( "com.sun.star.frame.DispatchHelper" )

      oDispatcher:executeDispatch(::oDoc:GetCurrentController():GetFrame(), ".uno:Save", "", 0, {})

#ifndef SHOW_PROGRAMMA

      ::oDoc:close(0)

#endif

   endIf

return nil

It is a mix of Italian and English, and use some our/other libraries functions.

I hope It helps you.

Regards,

Antonino

[Daniel Du Pré]

Antonino,
Thank you very much for the help.

Best regards