Solved

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

Posted on 2009-04-09
25
834 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:zcfyhome
  • 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:zcfyhome
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
 
LVL 40

Expert Comment

by:RQuadling
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:zcfyhome
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:RQuadling
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:zcfyhome
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:
RQuadling 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:zcfyhome
ID: 24879867
Thank you very much.
I will test it.
0
 
LVL 40

Expert Comment

by:RQuadling
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:zcfyhome
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 40

Expert Comment

by:RQuadling
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:zcfyhome
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:RQuadling
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:zcfyhome
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:RQuadling
ID: 24950298
Can you show your code as it currently stands?
0
 

Author Comment

by:zcfyhome
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:RQuadling
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:zcfyhome
ID: 24951801
I just tested it.
Looks same error on line $rows=$sheet->Rows(39,39);
Anyway, thanks
0
 
LVL 40

Expert Comment

by:RQuadling
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:zcfyhome
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:RQuadling
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:zcfyhome
ID: 35111344
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

708 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now