Solved

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

Posted on 2011-03-10
13
830 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:zcfyhome
  • 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:zcfyhome
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:zcfyhome
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:zcfyhome
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:zcfyhome
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:zcfyhome
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

785 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