[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 920
  • Last Modified:

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

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
Tim
Asked:
Tim
  • 11
  • 10
  • +1
1 Solution
 
synxCommented:
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
 
TimSenior PHP DeveloperAuthor Commented:
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
 
Steve BinkCommented:
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Richard QuadlingSenior Software DeverloperCommented:
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
 
TimSenior PHP DeveloperAuthor Commented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
TimSenior PHP DeveloperAuthor Commented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
TimSenior PHP DeveloperAuthor Commented:
Thank you very much.
I will test it.
0
 
Richard QuadlingSenior Software DeverloperCommented:
OK.

I ran that code from my command line and then I opened the workbook ... well, I've attached it.
NewBook.xls
0
 
TimSenior PHP DeveloperAuthor Commented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
TimSenior PHP DeveloperAuthor Commented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
TimSenior PHP DeveloperAuthor Commented:
Hi RQuadling:
I tried
Rows(39,39)->RowHeight = 23.25
It looks not work.
Any help, thanks

0
 
Richard QuadlingSenior Software DeverloperCommented:
Can you show your code as it currently stands?
0
 
TimSenior PHP DeveloperAuthor Commented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
TimSenior PHP DeveloperAuthor Commented:
I just tested it.
Looks same error on line $rows=$sheet->Rows(39,39);
Anyway, thanks
0
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
TimSenior PHP DeveloperAuthor Commented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
TimSenior PHP DeveloperAuthor Commented:
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 11
  • 10
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now