Operation OLEObject Excel Command Powerbuilder
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | oleobject ole_excel //1. Create Excel Object ole_excel = ConnectToNewObject('Excel.application') //2. Add a new workbook ole_excel.Workbooks.Add //3. Set the third worksheet as the active worksheet ole_excel.Worksheets("sheet3").Activate //4. Open the specified workbook ole_excel.Workbooks.Open("c:\temp\temp.xls") //5. Display the Excel window ole_excel.Visible = True //false //6. Change Excel Title bar ole_excel.Caption = "VFP application calls Microsoft Excel" //7．Assign the Value ole_excel.cells(1,4).Value = XM To the cell (XM Is the Database field Name) //8. Set the Width Of the specified column (unit: Number Of characters) ole_excel.ActiveSheet.Columns(1).ColumnWidth = 5 //9. Set the Height Of the specified row (unit: points) (Set the row Height To 1 cm, 1 pound = 0.035 cm) ole_excel.ActiveSheet.Rows(1).RowHeight = 1/0.035 //10. Insert the page break before the 18th Line ole_excel.Worksheets("Sheet1").Rows(18).PageBreak = 1 //11. Remove page break before column 4 ole_excel.ActiveSheet.Columns(4).PageBreak = 0 //12. Specify the Border Line Width (Borders parameters are as follows) ole.ActiveSheet.Range("b3:d3").Borders(2).Weight = 3 //13. Set the Type Of the four Border lines (Borders parameters: 1-Left, 2-Right, 3-Top, 4-bottom, 5 -Oblique, 6-Oblique/; LineStyle Value: 1 And 7-fine solid, 2-thin virtual, 4-dot virtual, 9-Double thin solid Line) ole_excel.ActiveSheet.Range("b3:d3").Borders(2).LineStyle = 1 //14. Set the page header ole_excel.ActiveSheet.PageSetup.CenterHeader = "Report 1" //15. Set footer ole_excel.ActiveSheet.PageSetup.CenterFooter = "No＆Page" //16. Set the margin From the header To the Top To 2 cm ole_excel.ActiveSheet.PageSetup.HeaderMargin = 2/0.035 //17. Set the bottom margin Of the footer To 3 cm ole_excel.ActiveSheet.PageSetup.FooterMargin = 3/0.035 //18. Set the Top margin To 2 cm ole_excel.ActiveSheet.PageSetup.TopMargin = 2/0.035 //19. Set the bottom margin To 4 cm ole_excel.ActiveSheet.PageSetup.BottomMargin = 4/0.035 //20. Set the Left margin To 2 cm vole_excel.ActiveSheet.PageSetup.LeftMargin = 2/0.035 //21. Set the Right margin To 2 cm ole_excel.ActiveSheet.PageSetup.RightMargin = 2/0.035 //22. Set the horizontal centering Of the page ole_excel.ActiveSheet.PageSetup.CenterHorizontally = true //false //23. Set the page To be vertically centered ole_excel.ActiveSheet.PageSetup.CenterVertically = True //fase //24. Set the page paper size (1-narrow Line 8 5 11 39-wide Line 14 11) ole_excel.ActiveSheet.PageSetup.PaperSize = 1 //25. Print grid lines ole_excel.ActiveSheet.PageSetup.PrintGridlines = True //fase //26. Copy the entire worksheet ole_excel.ActiveSheet.UsedRange.Copy //27. Copy the designated area ole_excel.ActiveSheet.Range("A1:E2").Copy //28. Paste ole_excel.WorkSheet("Sheet2").Range("A1").PasteSpecial //29. Insert a row Of. before the Second row ole_excel.ActiveSheet.Rows(2).Insert //30. Insert a column. before the Second column ole_excel.ActiveSheet.Columns(2).Insert //31. Set the font ole_excel.ActiveSheet.Cells(2,1).font.Name = "Arial" //32. Set the font size ole_excel.ActiveSheet.Cells(1,1).font.size = 25 //33. Set the font To Italic ole_excel.ActiveSheet.Cells(1,1).font.Italic = True //fase //34. Set the font Of the whole column To ole_excel.ActiveSheet.Columns(1).font.Bold = True //false //35. Clear cell formula ole_excel.ActiveSheet.Cells(1,4).ClearContents //36. Print Preview worksheet ole_excel.ActiveSheet.PrintPreview //37. Print out the worksheet ole_excel.ActiveSheet.PrintOut //38. the worksheet Is also ole_excel.ActiveWorkbook.SaveAs("c:\temp\temp.xls") //39. Give up saving ole_excel.ActiveWorkbook.saved = True //false //40. Close the workbook ole_excel.Workbooks.Close //41. Exit Excel ole_excel.quit //42 Combine String worksheet,beginRowcol,EndRowCol ole_excel.ActiveWorkbook.Sheets(worksheet).Range(beginRowcol ":" EndRowCol).Select ole_excel.ActiveWorkbook.Sheets(worksheet).Range(beginRowcol ":" EndRowCol). MergeCells //43 sheet renamed ole_excel.ActiveWorkbook.Sheets(olename).Select ole_excel.ActiveWorkbook.Sheets(olename).Name = newname |
Example 1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | //exampl 1 oleobject MyOLE MyOLE = Create oleobject ConnectErr = MyOLE.ConnectToNewObject ("excel.Application") MyOLE.Visible = False //Open The specified XLS file activates workbooks MyOLE.Application.Workbooks.Open (FilePath) //After making changes to the XLS file, whether it is necessary to warn the user when closing the file. MyOLE.Application.DisplayAlerts = False If IsNull(MyOLE) Then Return -1; If ConnectErr < 0 Then Choose Case ConnectErr Case -1 MessageBox('error prompt','invalid call') Case -2 MessageBox('error prompt' ,'Class name not found') Case -3 MessageBox('error prompt','object cannot be created') Case -4 MessageBox('error prompt','file cannot be connected') Case -5 MessageBox('error prompt', 'Cannot connect to the current object') Case -6 MessageBox('error message','file is invalid') Case -7 MessageBox("error message","file does not exist or has been opened") Case -8 MessageBox("error message ","The server cannot load the selected file") Case -9 MessageBox("error prompt","other error") End Choose Return -1 End If |
example 2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | //example 2 oleobject xlApp //Declare Excel application object xlApp = Create oleobject oleobject xlsub1,xlsub2,xlsub3,obook1,obook2,xlsub4,obook3 ret = xlApp.ConnectToNewObject( "Excel.Sheet") //Open the automation object should be excel.application" If ret < 0 Then MessageBox("Connect to Excel Failed !",String(ret)) Return //Operate the first excel file String ls_file3 = "C:\mytest.xls" obook3 = xlApp.Application. Workbooks.Open(ls_file3) //Open the file xlsub4 = xlApp.Application.ActiveWorkbook.Worksheets[1] //Activate the first page //Assign value to the cell xlsub4.cells[c,3].Value = String(mydata.GetItemDecimal (r,'rs')) xlsub4.cells[c,5].Value = String(mydata.GetItemDecimal(r,'yp')) xlsub4.cells[c,6] = String(mydata.GetItemDecimal(r,'jc')) xlsub4.cells[c,7] = String(mydata.GetItemDecimal(r,'hy')) xlsub4.cells[c, 8] = String(mydata.GetItemDecimal(r,'zl')) //Merge an excel file String ls_ran ls_ran = "B6:M"+String(ld_1+1) obook1 = xlApp.Application.Workbooks.Open(ls_file2) xlsub1 = xlApp.Application.ActiveWorkbook.Worksheets[1] xlsub1.Range(ls_ran).Copy() //Copy the selected content to the memory xlApp.Application.Visible = False xlsub2 = obook3.Worksheets.add() xlsub2. Name = " Sub- total" xlsub2.Range(ls_ran).PasteSpecial() //Paste the content of the previous file //Add to sub-total obook3.Worksheets("Sub-total").Activate() Integer a[6] a = {8,9,10,11,12,13} xlsub2.Range(ls_ran).Select() xlApp.Application.Selection.Subtotal(3,-4157,a, True, False, True) xlsub2.Rows( 1).Insert() xlsub2.cells[1,1] = Left(gs_shuqi,4)+"year"+Right(gs_shuqi,2)+"month" xlsub2.cells[1,1].font.Name = " Song Ti" xlsub2.cells[1,1].font.size = 22 xlsub2.Range("A1:M1").Select() xlApp.Application.Selection.Merge() xlApp.Application.Selection.HorizontalAlignment = 2 obook1. Save() obook3.Save() obook3.SaveAs(is_docname) obook1.Close() obook3.Close() xlApp.Application.quit() xlApp.DisconnectObject() End If |
example 3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | //example 3 Long numcols , numrows , c, r oleobject xlApp , xlsub Int ret //Set the # of columns and rows to process //Currently Set to copy the entire DW numcols = Long(dw_1.Object.DataWindow.column.Count) numrows = dw_1.RowCount() //Create the oleobject variable xlapp xlApp = Create oleobject //Connect to Excel and check the return code ret = xlApp.ConnectToNewObject( "Excel.Sheet" ) If ret < 0 Then MessageBox("Connect to Excel Failed !",String(ret)) Return End If //Open a particular Excel file xlApp.Application.Workbooks.Open("c:\temp.xls") //,false,true //Make Excel visible xlApp.Application.Visible = True //Resolve the Excel reference once //This technique shortens the script and improves performance xlsub = xlApp.Application.ActiveWorkbook.Worksheets[1] //Loop thru the Datawindow and Excel sheet //The for/next loop copies all rows for each column For c = 1 To numcols For r = 1 To numrows xlsub.cells[r,c] = dw_1.Object.Data[r,c] Next Next //Save opened file //xlApp.Application.Activeworkbook.Save() //SaveAs a different filename //xlApp.Application.Activeworkbook.SaveAs("c:\file2.xls") //clean up |
example 4
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | //example 4 //the basic statement Ole_object.Workbooks.Add //Create a new Excel file Ole_object.Workbooks.Open("FileName") //Open an existing Excel file Ole_object.ActiveWorkbook.Sheets("SheetName").Select //Select a job in the file Table Ole_object.Application.Run("MacroName") //Run the macro Ole_object.Application.Visible = True //The Excel file is visible Ole_object.Application.ScreenUpdating = True //Set the visible attribute ②Format //setting Ole_object.ActiveSheet.Columns( "A :U ").AutoFit //Column width is automatically adjusted Ole_object.ActiveSheet.Columns( "A:Z ").ColumnWidth = 6.75 //Column width Ole_object.ActiveSheet.Rows( "1:100 ").RowHeight = 12 //Line height Ole_object.Application.StandardFont = "ArialNarrow "; //Set the font Ole_object.Application.StandardFontSize = "8 " //Set the font size Ole_object. ActiveSheet.font.size = "8 " //Set the font size Ole_object.ActiveSheet.font.Bold = True //Bold Ole_object.ActiveSheet.font.Italic = True //Italic Ole_object.ActiveSheet.font.Underline = True //Underline Ole_object.ActiveSheet.font.StrikeThrough = True //StrikeThrough=True// StrikeThrough Ole_object.ActiveSheet.HorizontalAlignment = 3 //Horizontal: 4 to the right; 3 to the center; 2 to the left Ole_object.ActiveSheet.VerticalAlignment = 2 //Vertical: 3 to the bottom; 2 to the center; 1 to the top Ole_object.ActiveSheet.cells(2, 1).font.Name = 'Heitian' //Set font Ole_object.ActiveSheet.cells(2,1).font.size = 25 //Set font size ③Work //area operation: Ole_object.ActiveSheet.Range( "A1:Z10 ").Property = Value //Set the property value in a working area Ole_object.ActiveSheet.Range("A1:Z10").Merge //Merge cells Ole_object.ActiveSheet.Range("A1:Z10").WrapText = False //Auto wrap is prohibited Ole_object.ActiveSheet.Range( "A1:Z10 ").Borders(b_type).Weight = i_val //b_type: 1 left border; 2 right border; 3 upper border; 4 lower border; 5 left upper border; 6 //Right Upper Border (above For the operation Of the cell) 7 Left boundary; boundary 8; 9 at the boundary; Right boundary 10; 11 Inner vertical boundary; internal horizontal boundary 12 (For the above regions operate as) //i_val: 0 infinity line; and 1, 2, 3. . . In turn, the boundary lines are bolded Ole_object.ActiveSheet.Range( "A1:Z10 ").Borders(b_type).LineStyle = 1 //1 and 7—fine solid, 2—fine virtual, 4—point virtual, 9—double thin solid Line //assignment operation Ole_object.ActiveSheet.range("A1:Z10").cells.Value = "name" Ole_object.ActiveSheet.range( "A1:Z10 ").cells.Value = 1 Ole_object.ActiveSheet.range("A1 :Z10 ").cells.Value = "2003-01-01" Ole_object.ActiveSheet.Range( "A1:Z10 ").Cells(1,2).Value = "Age" //Assignment of a cell in the range Ole_object.ActiveSheet.Range( "A1:Z1 ").Cells(1,1).Value = "SUM(RC[-9]:RC[-1])" |
example 5
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | //example 5 ls_filename = "C:\alt_report\wf_alt_report.xls " If FileExists(ls_filename) Then FileDelete(ls_filename) oleobject Loo_Excel Loo_Excel = Create oleobject If Loo_Excel.ConnectToNewObject( "Excel.Application ") <> 0 Then MessageBox('Prompt','Excel is not installed in the system, this operation cannot be performed!') Return End If Loo_Excel.Application.Workbooks.Add() Loo_Excel.Application.Workbooks(1).worksheets(1) Loo_Excel.Application.Visible = True Loo_Excel.Application.ScreenUpdating = False //'Processing header... .. ' Loo_Excel.Range( "A1:AB1 ").Select Loo_Excel.Selection.MergeCells = True Loo_Excel.Selection.HorizontalAlignment = 3 Loo_Excel.Selection.VerticalAlignment = 3 Loo_Excel.Selection.font.Bold = True Loo_Excel.Selection.font.size = 14 Loo_Excel.Selection.RowHeight = 28.50 Loo_Excel.ActiveCell.FormulaR1C1 = dw_3.Describe( "ls_title "+ ".text ") / //Write the header Loo_Excel.Application.workbooks(1). worksheets(1).cells(3, 1 ).Value = "Order number" //dw_2.describe( "po_no "+ ".name "+ "_t.text ") Loo_Excel.Application.workbooks(1).worksheets (1).cells(3, 2 ).Value = "Customer section number" Loo_Excel.Application.workbooks(1).worksheets(1).cells(3, 3 ).Value = dw_2.Describe( "cust_nam_t.text ") / //Content For I = 1 To dw_2.RowCount() Loo_Excel.Application.workbooks(1).worksheets(1).cells(3+I, 1).Value = String( dw_2.Object.po_no[I] ) Next Loo_Excel.Application.ActiveWorkbook.SaveAs(ls_filename) Loo_Excel.Application.ScreenUpdating = True Loo_Excel.DisconnectObject() |
example 6
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | //example 6 Long ll_ret String ls_ret String ls_string1 oleobject myoleobject myoleobject = create oleobject ll_ret = myoleobject.connecttoobject("c:\temp.xls") Messagebox("Result!",string(ll_ret)) myoleobject.Application.Visible = TRUE // writing myoleobject.application.workbooks(1).worksheets(1).cells(1,1).value="Test" // read ls_ret = myoleobject.application.workbooks(1).worksheets(1).cells(1,1).value //Excel File Open myOleObject.WorkBooks.Open("c:\a.xls") // Sheet Name ls_string1 = myOleObject.sheets[1].Name // Auto-Finish Sheet myOleObject.Worksheets[1].Columns.AutoFit // read the number of sheets ll_PageCnt = myOleObject.application.Workbooks(1).Worksheets.Count //read ls_ret = myOleobject.application.workbooks(1).worksheets(1).Range( "A1" ).Value //writing myOleobject.application.workbooks(1).worksheets(1).Range( "A1" ).Value = 'Test' //Row Delete Delete row myOleobject.application.Workbooks(1).Worksheets(1).rows("1").delete // give the sheet a name myoleobject.Sheets[1].Name = "SheetName" //save myoleobject.application.workbooks(1).Save() //save as myoleobject.application.workbooks(1).SaveAs("c:\b.xls") //Save as - by sheet (text file separated by tabs) myoleobject.application.Workbooks(1).Worksheets(i).SaveAs("c:\c.txt",3) //Save format differs depending on the argument value (3) - Refer to Excel Help // open new myoleobject.connecttonewobject("excel.application") //open file myoleobject.WorkBooks.Opentext('c:\tmpgoodef.xls') // change title bar myoleobject.Application.caption = "My Excel" // Process after selecting the first Row myoleobject.rows("1:1").select // bold text myoleobject.selection.font.bold = true // in italics myoleobject.selection.font.italic = true // font color myoleobject.selection.font.Color = RGB(128,128,0) //border color myoleobject.selection.Borders.Color = RGB (0,0,128) // word wrap myoleobject.selection.wraptext = true // align horizontally myoleobject.selection.horizontalalignment = true // vertical alignment myoleobject.selection.verticalalignment = true //show status bar myoleobject.statusbar = " My Status" //Repeat the first Row when printing (displayed as header) myoleobject.Activesheet.PageSetup.PrintTitleRows = "$1:$1" //Copy the first sheet - Refer to Excel Help for details myoleobject.Worksheets(1).copy // close without saving myoleobject.WorkBooks(1).Close(false) // disconnect myoleobject.DisConnectObject() destroy myoleobject |
Example 7 Function Export
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | //==================================================================== // Function: f_exportexcel() //-------------------------------------------------------------------- // Description: //-------------------------------------------------------------------- // Arguments: // value datawindow dw_1 //-------------------------------------------------------------------- // Returns: integer //-------------------------------------------------------------------- // Author: PB.BaoGa Date: 2022/01/08 //-------------------------------------------------------------------- // Usage: f_exportexcel ( datawindow dw_1 ) //-------------------------------------------------------------------- // Copyright (c) PB.BaoGa(TM), All rights reserved. //-------------------------------------------------------------------- // Modify History: // //==================================================================== Constant Integer ppLayoutBlank = 12 OLEObject ole_object, xlapp //Define a class // Create an instance of this class------OLE_Object and xlapp ole_object = Create OLEObject xlapp = Create OLEObject ///////////////////////////////////////////// //////////////////// // Integer li_ret Long i,j,k // connect------ConnectToObject li_ret = ole_object.ConnectToObject("","Excel.Application") If li_ret <> 0 Then // If Excel is not open, create a new one --------ConnectToNewObject li_ret = ole_object.ConnectToNewObject("Excel.Application") If li_ret <> 0 Then MessageBox('OLE error', 'OLE could not connect! Error number: ' + String(li_ret)) Return 0 End If //ole_object.application.workbooks.open("e:\bank\lls2.xls") ole_object.Visible = False// True End If ole_object.Workbooks.Add xlapp = ole_object.Application.activeworkbook.worksheets[1] //xlapp.Activate //Set the first worksheet as the active worksheet/ string ls_col Long numcols,numrows numcols = Long(dw_1.Object.datawindow.column.count) //Get the number of columns and rows of data window data (the number of rows should be the number of data rows + 1) numrows = dw_1.RowCount() //Get the number of columns and rows of data window data (the number of rows should be the number of data rows + 1) //The name of the first field in Excel is "serial number" xlapp.cells[1,1] = 'Serial number' //write table body For i = 1 To numcols dw_1.SetColumn(i) //Set the current field xlapp.cells[1,i+1] = dw_1.GetColumnName() //Get the name of the current field ls_col = Trim(dw_1.GetColumnName()) For j = 1 To numrows If i = 1 Then xlapp.cells[j+1,1] = "=row()-1" xlapp.cells[j+1,i+1] = dw_1.Object.Data[j,i] ElseIf Match(ls_col, "ID card") Then //Determine whether it is: "ID card field" xlapp.cells[j+1,i+1] = "'"+String(dw_1.Object.Data[j,i]) Else xlapp.cells[j+1,i+1] = dw_1.Object.Data[j,i] //Write out the contents of the table End If Next Next //powerbuilder selects all data in Excel to draw frame lines xlapp.UsedRange.Borders(1).LineStyle = 1 xlapp.UsedRange.Borders(2).LineStyle = 1 xlapp.UsedRange.Borders(3).LineStyle = 1 xlapp.UsedRange.Borders(4).LineStyle = 1 xlapp.UsedRange.RowHeight = 37 //0.035 (set row height to 1cm, 1lb=0.035cm) //Set the footer page &P xlapp.PageSetup.CenterFooter = "Page&P" //Set the page shuiping to center xlapp.PageSetup.CenterHorizontally = True xlapp.UsedRange.font.Name = "Arial " //Set the font xlapp.UsedRange.font.Size = "11" //Set the font size //Otherwise xlapp.UsedRange will fail! ! // xlapp.Rows("1:1").font.Name = "Bold" //The first line xlapp.Rows("1:1").font.Bold = True //The first line The first line font becomes "bold" xlapp.Rows("1:1").HorizontalAlignment = 3 //The first row 1: Left 2: Right 3: Center xlapp.Columns(1).HorizontalAlignment = 3 //The first column is centered xlapp.cells.Select //Select all table xlapp.cells.EntireColumn.AutoFit //excel automatically adjusts the cell width xlapp.Range( "a1:a1").Select //Select cell A1 //Shutting down and disconnecting from the server // ole_object.Quit() //Exit Excel file ole_object.DisconnectObject() xlapp.DisconnectObject() Destroy ole_object Destroy xlapp Return 1 |
Excel File Format enumeration ClickHere
Google Luck!
Users Today : 33
Users Yesterday : 52
This Month : 1573
This Year : 44431
Total Users : 183804
Views Today : 104
Total views : 781735
Who's Online : 1
Buenas tardes, una consulta, cuando me exporta a excel me elimina los ceros de la izquierda de algunas columnas, debe ser porque no tiene el formato de texto en excel ¿Cómo puedo dar el formato de texto a una columna o a un rango de columna? algo así: myoleobject.activeworkbook.sheets(1).Columns(“E:E”).Style = “Text”
Muchas gracias