?
Solved

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

Posted on 2009-04-09
25
Medium Priority
?
886 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

Expert Comment

by:Steve Bink
ID: 24832032
0
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 
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 2000 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

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses

771 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