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
