PureBasic Forums - English

Posted: **Mon Feb 09, 2009 1:56 pm**

If some body has code or snippet to manipulate excel file, i am trying to
assemble coding to be able to manipulate Excel files.
like How to create style, merge cell, add borderline, manipulate range etc... I will also supply all code to the french community.

Here is a sample of code for excel from different coders

Code: Select all

IncludePath "L:\COMate 4.3"
XIncludeFile "COMate.pbi"

date$ = FormatDate("%dd/%mm/%yyyy", Date())

Define.COMateObject ExcelObject, WorkBook
Define.COMateObject ExcelObject, WorkSheet
Define.COMateObject ExcelObject, Sheet
ExcelObject = COMate_CreateObject("Excel.Application")

If ExcelObject
  If ExcelObject\SetProperty("Visible = #True") = #S_OK
    ;Add a workbook, Ajouter un Classeur
    ;WorkBook = ExcelObject\GetObjectProperty("Workbooks\Add")
    ;or opening a worbook, Ouvrir un Classeur
    WorkBook = ExcelObject\GetObjectProperty("Workbooks\Open('c:\test.xlsx')") 
    ;Setting Workbook preference
    ; Setting Font
    ExcelObject\SetProperty ("StandardFont = 'Arial' ")
    ; Setting »Font size
    ExcelObject\SetProperty ("StandardFontSize = 10 ")
    ; Opening a new workbook with only 1 sheet
    ExcelObject\setproperty("SheetsInNewWorkbook = 1")
    WorkSheet = ExcelObject\GetObjectProperty("Worksheets\Add") 
    WorkSheet\SetProperty("Name = 'Another'")
    WorkSheet = ExcelObject\GetObjectProperty("Sheets('Sheet1')")  
    ;WorkSheet\Invoke("Activate()")
    If WorkBook
      
       ;  Font manipulation,  Manipulation des polices 
       ; set Font Name,  Nom
      ExcelObject\SetProperty("Cells(1,1)\Font\name = 'Verdana'")
       ; set Font size,  Dimension
      ExcelObject\SetProperty("Cells(1,1)\Font\Size = 14")
      ; set Font Style , Style de police
      ExcelObject\SetProperty("Cells(1,1)\Font\FontStyle = 'Normal'")  ;Bold, Italic, Bold Italic as string
      ; set Font underline,  Police souslignée
      ExcelObject\SetProperty("Cells(1,1)\Font\Underline = #True")  ;As boolean (#True, false) 
      ;  Text Color index  Index de couleur du texte
      ; 1 = noir 2 = Blanc ?, 3 =Rouge, 4 =vert,  5 = Blue, 6 = Jaune, 7 = Rose, 8 = Turquoise as integer etc.....
      ExcelObject\SetProperty("Cells(1,1)\Font\ColorIndex = 8") 
      
      
      
      ExcelObject\SetProperty("Cells(1,1)Style\Borders\bottom = #true ")
      ;Write Text
      ExcelObject\SetProperty("Cells(1,1) = 'Hello'")
      ExcelObject\SetProperty("Cells(1,2) = 'from'")
      ExcelObject\SetProperty("Cells(1,3) = 'COMate!'")
      ExcelObject\SetProperty("Cells(1,5) = 'Today$0027s date is'")  ;$0027 is a hex escape code used to insert a ' (ascii 39) character.
      ExcelObject\SetProperty("Cells(1,6) = '" + date$ + "' AS DATE")
      ExcelObject\SetProperty("Cells(2,1) = 6.25")
      ExcelObject\SetProperty("Cells(3,1) = 1.75")
      ExcelObject\SetProperty("Cells(4,1) = '=A2+A3'")
      ;-------------ExcelObject\Evaluate("'A4')=SUM(A2:A3)")
      
      ;Read a cell 
      Cell$ = ExcelObject\GetStringProperty("Cells(1,1)") 
      ;Save workbook
      WorkBook\Invoke( "SaveAs('C:\Test.xlsx') ")
    
      WorkBook\Release()
    EndIf
  EndIf
  ExcelObject\Release()
Else
  MessageRequester("COMate -Excel demo", "Couldn't create the application object!")
EndIf

Thank you for your support

Posted: **Sun Apr 12, 2009 1:07 am**

Would you test and use this code with COMate for Excel?
I've made an ExcelFunctions.pbi and a Testsource for this .

Here the full source for all:

http://www.purebasic.fr/german/viewtopi ... 466#239466

Greeting Falko (from german Forum)

PureBasic Forums - English

Manipulating Excel with COMate

Manipulating Excel with COMate