Solved

Where can find the document about php com function for  excel?

Posted on 2009-04-09
25
864 Views
Last Modified: 2013-12-13
I will using php com function for change excel file to get report. But I can not find the menu or document about all method in com function.  I need know how to set pagebreak, how to change font size and how to set autoajust. Thanks
0
Comment
Question by:Tim
  • 11
  • 10
  • +1
25 Comments
 
LVL 5

Expert Comment

by:synx
ID: 24229411
Documentation on COM functions: http://us.php.net/manual/en/ref.com.php
Or at a higher level: http://us.php.net/com

Hope that helps.
0
 

Author Comment

by:Tim
ID: 24389531
I know these link. But I can not find any detail  documnet about $excel = new COM("excel.application");
I would like know the how to set pagebreak, how to change font size and how to set autoajust for $excel
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 24832032
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24841321
The great thing about COM is that it is language agnostic. Sure the specific language may have some restrictions or idiosynchrosis, but the code, more or less, is translatable from 1 language to the next.

So, how does this help?

Well, if you can do it in Excel, and more specifically, if you can record a macro doing it in Excel, then you can do it in PHP.

So, having recorded the macro, look at the calls used.

It is now just a case of translating them from VB to PHP, taking care of the language specific aspects.

VB has named parameters, PHP doesn't - you have to put all the preceding paramaters.

dot in VB is -> in PHP

that sort of stuff.

The VBA help is essential. Without it you are going to be guessing a LOT of things.

With regard to your PHP code, make sure you've got the type library registered. Without it, you will not be able to access the constants the Excel can.

Also, make sure you close the application. PHP won't do it for you and you will end up with a LOT of versions of Excel floating around.

Below is a script which works on an Word document (sorry no Excel ones at hand).

Read the comments. Look at the methods and properties set in conjunction with the VBA manual...

Then, if you can record a macro in Excel that does what you want, then I can walk you through a PHP script that does the same.

OOI, I use COM to get PHP to talk to Crystal Reports. I use a Type Library Browser to help me get all the info I need - no manual required (well, for most of it).

PHP + COM is an excellent combination - just takes a little getting used to.
<?php
ini_set('com.autoregister_casesensitive', 1); // Optional. When set wdPropertyWords does NOT equal WDPROPERTYWORDS
ini_set('com.autoregister_typelib', 1); // Auto registry the loaded typelibrary - allows access to constants.
ini_set('com.autoregister_verbose', 0); // Suppress Warning: com::com(): Type library constant emptyenum is already defined in $s on line %d messages.
 
$o_Word = new COM('Word.Application') or die('Cannot load MS Word');
$o_Word->Visible = 0;
 
$o_Doc = $o_Word->Documents->Open('z:/5words.doc');
echo 'There are ', $o_Doc->BuiltInDocumentProperties(wdPropertyWords), ' word(s) in this document.';
$o_Doc->Close(False);
$o_Word->Quit();
unset($o_Doc);
unset($o_Word);

Open in new window

0
 

Author Comment

by:Tim
ID: 24873158
Thanks all.
I am working on PHP + COM  Excel.
I can create a excel , but I can not find how to deal with some  issue;
Like how to set page brake, how to set row height...
Any help?
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24873955
Like I said, if you record the task in an Excel Macro of what you want to do, then I can explain how to code it in PHP.

It really is just a case of using the appropriate syntax and some fairy dust.
0
 

Author Comment

by:Tim
ID: 24879280
Hi RQuadling:
I record this.
Sub Macro1()
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
End Sub

Could you tell me the PHP+COM code?

Thanks
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 500 total points
ID: 24879795
Ok.

The "ActiveCell" part is inappropriate for an automated process as no cell is active. So, I'll choose cell G8 as the cell.


<?php
ini_set('com.autoregister_casesensitive', 1); // Optional. When set wdPropertyWords does NOT equal WDPROPERTYWORDS
ini_set('com.autoregister_typelib', 1); // Auto registry the loaded typelibrary - allows access to constants.
ini_set('com.autoregister_verbose', 0); // Suppress Warning: com::com(): Type library constant emptyenum is already defined in $s on line %d messages.
 
// Load Excel
$o_Excel = new COM('Excel.Application') or die('Cannot load MS Excel');
 
// Hide Excel.
$o_Excel->Visible = False;
 
// Create a blank workbook
$o_Wbk = $o_Excel->Workbooks->Add();
 
// Shortcut to sheet 2
$o_Wks = $o_Wbk->Sheets(2);
 
// Shortcut to the cell(G8)
$o_Cell = $o_Wks->Cells(6,7);
 
// Add the Horizontal Page Break to the worksheet.
$o_Wks->HPageBreaks->Add($o_Cell);
 
// No shortcuts
$o_Wbk->Sheets(3)->HPageBreaks->Add($o_Wbk->Sheets(3)->Cells(26,26));
 
// Suppress the "Overwrite file" dialog.
$o_Excel->DisplayAlerts = False;
 
// Save the workbook.
$o_Wbk->SaveAs('C:\\NewBook.xls');
 
// Close the workbook.
$o_Wbk->Close();
 
// Quit Excel.
$o_Excel->Quit();
 
// Tidy up - STRONGLY recommended and do in reverse order.
unset($o_Cell);
unset($o_Wks);
unset($o_Wbk);
unset($o_Excel);

Open in new window

0
 

Author Comment

by:Tim
ID: 24879867
Thank you very much.
I will test it.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24879911
OK.

I ran that code from my command line and then I opened the workbook ... well, I've attached it.
NewBook.xls
0
 

Author Comment

by:Tim
ID: 24880228
I already made my code working base on your help.
Thank you very much.
Also. could check this.
Could you make it as php+com
Sub AutoFitMergedCellRowHeight()
    Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    Dim CurrCell As Range, RangeWidth As Single
    Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    If ActiveCell.MergeCells Then
       With ActiveCell.MergeArea
            If .Rows.Count = 1 And .WrapText = True Then
                Application.ScreenUpdating = False
                CurrentRowHeight = .RowHeight
                ActiveCellWidth = ActiveCell.ColumnWidth
                RangeWidth = .Width
                For Each CurrCell In Selection
                    MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
                Next
                .MergeCells = False
                .Cells(1).ColumnWidth = MergedCellRgWidth
                While .Cells(1).Width < RangeWidth
                  .Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 0.5
                Wend
                .Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 0.5
                .EntireRow.AutoFit
                PossNewRowHeight = .RowHeight
                .Cells(1).ColumnWidth = ActiveCellWidth
                .MergeCells = True
                .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
                 CurrentRowHeight, PossNewRowHeight)
            End If
        End With
    End If
End Sub

Open in new window

0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24880410
Sure.

But you are using Activexxxx. As there is no user controlling the application, what is the Activexxxx?

If this macro is part of the spreadsheet already, then you could do something as simple as ...

// Select the cell you want to start with.
$o_Wks->Cells(1,1)->Select();

// Run the macro.
$o_Excel->Run('AutoFitMergedCellRowHeight');

See the snippet and file.

NOTE: Things like macro security and digital signatures may get in the way. The macro is signed by me, so you may need to accept the signature first before the PHP+COM code will work.




<?php
ini_set('com.autoregister_casesensitive', 1); // Optional. When set wdPropertyWords does NOT equal WDPROPERTYWORDS
ini_set('com.autoregister_typelib', 1); // Auto registry the loaded typelibrary - allows access to constants.
ini_set('com.autoregister_verbose', 0); // Suppress Warning: com::com(): Type library constant emptyenum is already defined in $s on line %d messages.
 
// Load Excel
$o_Excel = new COM('Excel.Application') or die('Cannot load MS Excel');
 
// Show Excel.
$o_Excel->Visible = True;
 
// Create a blank workbook
$o_Wbk = $o_Excel->Workbooks->Open('C:\\ShowRichard.xls');
 
$o_Excel->Run('ShowRichard.xls!ThisWorkbook.ShowRichard');
 
// Close the workbook.
$o_Wbk->Close();
 
// Quit Excel.
$o_Excel->Quit();
 
// Tidy up - STRONGLY recommended and do in reverse order.
unset($o_Cell);
unset($o_Wks);
unset($o_Wbk);
unset($o_Excel);

Open in new window

ShowRichard.xls
0
 

Author Comment

by:Tim
ID: 24880635
Thank you  very much.
Last question, How can I do this using PHP+Com?
Sub Macro1()
    Rows("39:39").RowHeight = 23.25
End Sub
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24883334
Have a go.

Take a look at how I declared a cell in my first script.

You know the name of the collection (rows), you see that "G8" is (8,8).

So, maybe ...

Rows(39,39)->RowHeight = 23.25

I'm not exactly sure which scale is used, so you may have the wrong value there.
0
 

Author Comment

by:Tim
ID: 24939023
Hi RQuadling:
I tried
Rows(39,39)->RowHeight = 23.25
It looks not work.
Any help, thanks

0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24950298
Can you show your code as it currently stands?
0
 

Author Comment

by:Tim
ID: 24951264
My code:
$row=$sheet->Rows(39,39);
$row->activate;
$row->RowHeight = 23.25;

Also I tried:
$sheet->Rows(39,39)->RowHeight = 23.25;

Both got the error:
Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Unknown<br/><b>Description:</b> Unknown' in C:\projects\labweb\newexcelreport.php:1421 Stack trace: #0 C:\projects\labweb\newexcelreport.php(1421): variant->Rows(39, 39) #1 C:\projects\labweb\newexcelreport.php(177): report_pcm_in_en('b', '62269', Array, Array, Array, Array, Array, Array, Array, Array) #2 {main} thrown in C:\projects\labweb\newexcelreport.php on line 1421

Any help?
Thanks


;
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24951719
Hmmm.

As Rows is plural, I wonder if ...

$rows=$sheet->Rows(39,39);
$rows->activate;
foreach($rows as $row) {
 $row->RowHeight = 23.25;
}

may work better.



0
 

Author Comment

by:Tim
ID: 24951801
I just tested it.
Looks same error on line $rows=$sheet->Rows(39,39);
Anyway, thanks
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24952209
I wonder if you can just set the row height on a single cell on that row?

$o_Cell = $o_Wks->Cells(6,7);
$o_Cell->RowHeight = 23.25;

 ... testing ...

Yep!

Using the lines above in the code that you accepted seems to work!

The 23.25 equates to 31pixels (according to Excel when I left-click and hold on the row separator on the left hand side).

I suspect that the size is relative to the font.

Or not.

A value of 75 equates to 100 pixels.

0
 

Author Comment

by:Tim
ID: 24952295
Wooooooo.
You are  real expert.
The cell->.RowHeight    works.  
how come, the cell have rowheight.
Good.

Thank you very much.
Have a good day.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24952367
A cell belongs to a row, so cell->RowHeight may actually be a shortcut to cell->row->rowheight.

I wonder ...

$o_Wks->Rows('21:21').RowHeight = 23.25;

I may have just got the syntax for Rows() wrong.
0
 

Author Comment

by:Tim
ID: 35111344
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question