Hi All
I�ve recently upgraded to version 11 and am looking to generate Excel sheets with the new ExcelDocument function! In version 10 I did this using ole.GetObject("","Excel.Application") and I have managed to replicate most of the things I used in the past.
I�m looking for a way to apply an auto filter and set the width of my columns automatically.
Here is my new code which creates some random data
and here is my old code, which uses the auto filter and auto fit
Thank you for your help
Pete
I�ve recently upgraded to version 11 and am looking to generate Excel sheets with the new ExcelDocument function! In version 10 I did this using ole.GetObject("","Excel.Application") and I have managed to replicate most of the things I used in the past.
I�m looking for a way to apply an auto filter and set the width of my columns automatically.
Here is my new code which creates some random data
Code:
xlname = tmp_dirv+time("LF-MMdd-0h0m0s")+".xlsx" delete doc delete sheet dim Doc as Office::ExcelDocument dim Sheet as Office::Spreadsheet Sheet=Doc.AddSheet("Data") Sheet.Write(1,1,"Job no.") Sheet.Write(1,2,"Section") Sheet.Write(1,3,"Date") Sheet.Write(1,4,"Time") Sheet.Write(1,5,"Duration(Min)") Sheet.Write(1,6,"Value(GBP)") Sheet.Split(2, 1) for i = 2 to 200 Sheet.Write(i,1,"JOB"+padl(ltrim(str(asc(rand_string(1)),4,0)),4,"0")) Sheet.Write(i,2,"Sec "+upper(rand_string(1))) Sheet.Write(i,4,time("dd/MM/yyyy",now()+(i*555))) Sheet.Write(i,3,time("0h:0m",now()+(i*555))) Sheet.Write(i,5,round(rand()*100,2)) Sheet.Write(i,6,round(rand()*100,2)) next Doc.Save(xlname) sys_open(xlname)
Code:
xlApp = ole.GetObject("","Excel.Application") xlApp.Workbooks.add() xlApp.ActiveSheet.Name = "Data" xlApp.Range("A1").Select() xlApp.ActiveCell.FormulaR1C1 = "Job no." xlApp.ActiveSheet.Name = "Data" xlApp.Range("B1").Select() xlApp.ActiveCell.FormulaR1C1 = "Section" xlApp.ActiveSheet.Name = "Data" xlApp.Range("C1").Select() xlApp.ActiveCell.FormulaR1C1 = "Date" xlApp.ActiveSheet.Name = "Data" xlApp.Range("D1").Select() xlApp.ActiveCell.FormulaR1C1 = "Time" xlApp.ActiveSheet.Name = "Data" xlApp.Range("E1").Select() xlApp.ActiveCell.FormulaR1C1 = "Duration(Min)" xlApp.ActiveSheet.Name = "Data" xlApp.Range("F1").Select() xlApp.ActiveCell.FormulaR1C1 = "Value(GBP)" xlApp.Range("A2").Select() xlApp.ActiveWindow.FreezePanes = .t. for i = 2 to 200 xlApp.Range("A"+ltrim(str(i,4,0))).Select() xlApp.ActiveCell.FormulaR1C1 = "JOB"+padl(ltrim(str(asc(rand_string(1)),4,0)),4,"0") xlApp.Range("B"+ltrim(str(i,4,0))).Select() xlApp.ActiveCell.FormulaR1C1 = "Sec "+upper(rand_string(1)) xlApp.Range("C"+ltrim(str(i,4,0))).Select() xlApp.ActiveCell.FormulaR1C1 = time("dd/MM/yyyy",now()+(i*555)) xlApp.Range("D"+ltrim(str(i,4,0))).Select() xlApp.ActiveCell.FormulaR1C1 = time("0h:0m",now()+(i*555)) xlApp.Range("E"+ltrim(str(i,4,0))).Select() xlApp.ActiveCell.FormulaR1C1 = round(rand()*100,2) xlApp.Range("F"+ltrim(str(i,4,0))).Select() xlApp.ActiveCell.FormulaR1C1 = round(rand()*100,2) next xlApp.Cells.EntireColumn.AutoFit()'Sets column widths xlApp.Range("A1").Select() xlApp.Selection.AutoFilter()'Sets autofilter xlApp.Visible = .t.
Pete
Comment