Solved

How to set part of cell font style to Italic when using php COM to generate excel

Posted on 2011-03-10
13
839 Views
Last Modified: 2012-08-13
I using php , com to generate excel file.
Now I need set some words to Italic style, like ABCD EF to  ABCD EF

Code like this
$excelfile_name="test.xls";
 $excel = new COM("excel.application") or die("Unable to instanciate excel");
$excel->Workbooks->Open("C:\template.xls");
 $book=$excel->Workbooks(1);
$sheet=$book->worksheets(1);
$sheet->activate;
$cell = $sheet->Cells(7,2) ;  
  $cell->activate;
$cell->value = 'ABCD EF';
.......(need set 'ABCD' font style to Italic)

Thanks
0
Comment
Question by:Tim
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35099888
I'm not familiar with handling this through PHP, but in Excel VBA it would be something like this:

    ActiveCell.Characters(Start:=1, Length:=4).Font.FontStyle = "Bold"
    ActiveCell.Characters(Start:=5, Length:=3).Font.FontStyle = "Regular"

Does that help?

Maybe this?

    $Cell.Characters(Start:=1, Length:=4).Font.FontStyle = "Bold"
    $Cell.Characters(Start:=5, Length:=3).Font.FontStyle = "Regular"
0
 

Author Comment

by:Tim
ID: 35108810
I test $Cell->Characters , it said "Fatal error: Call to a member function Characters() on a non-object in ".

Any help?
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35110053
since characters is an array, maybe it needs more, like

$Cell->Characters(1,1)
?
0
Industry Leaders: 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!

 

Author Comment

by:Tim
ID: 35110134
Thanks.
I using  $Cell->Characters(1,4)->font->fontStyle = "Bold";
Still get the error "Fatal error: Call to a member function Characters() on a non-object in  "
Any help?
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35110253
Is there any way in the tool you use to query what's valid?  In Excel, when you type the "." after activecell, a popup box appears showing valid choices ("intellisense").

(I also sent a notifcation to other Excel experts to see if any have worked with PHP)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35110259
Never worked with PHP but see of this link helps you?

http://www.codeunit.co.za/2010/07/14/phpexcel-how-to-bold-a-cells-value/

Sid
0
 

Author Comment

by:Tim
ID: 35110295
I Think  RQuadling may know how to do this.
Before he give me the answer about php COM excel issue.
I don't know how to ask him, could you help?
Thanks
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 35120397
Hi.

Just checking in ... be back in a while with what I've found.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 35120419
"Fatal error: Call to a member function Characters() on a non-object in" means that $Cell is incorrect.

Can you show the rest of the code?
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 500 total points
ID: 35120468
Here is an updated example.
<?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 some text to the cell.
$o_Cell->Value = 'AB_CD_EF';

// Get Font object for 'CD'
$o_Font = $o_Cell->Characters(4,2)->Font;

// Add 20pt bold.
$o_Font->Bold = True;
$o_Font->Size = 20;

// 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_Font);
unset($o_Cell);
unset($o_Wks);
unset($o_Wbk);
unset($o_Excel);

Open in new window


Run the code, open the workbook, look at sheet 2. Hopefully you should get the same as shown in this file : NewBook.xls
0
 

Author Closing Comment

by:Tim
ID: 35128333
Thanks. It's working.
Thank you very much
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 35128901
Glad to have helped.
0
 

Author Comment

by:Tim
ID: 36710935
Hi RQuadling:
Could you check my new question.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_27343581.html

How to set cell alignment and text wrap when using php COM to generate excel

Thanks
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

713 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