?
Solved

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

Posted on 2011-09-27
10
Medium Priority
?
816 Views
Last Modified: 2012-05-12
I am using php , com to generate excel file.
How I can set excel cell alignment and text wrap?  

maybe like
$cell->VerticalAlignment=xlCenter (not work)
I can't find document about it.

Anyone can help , 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
  • 4
  • 4
10 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36715507
Extending the example code I provided you in an earlier question ...
<?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';

// Additional code for http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_27343581.html
$o_Cell->RowHeight = 100; // Make the cell big enough to see the effect
$o_Cell->ColumnWidth = 100; // Make the cell big enough to see the effect
$o_Cell->HorizontalAlignment = xlCenter;
$o_Cell->VerticalAlignment = xlCenter;
$o_Cell->WrapText = True;

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


This produces the spreadsheet : NewBook.xls. Sheet 2 contains the amended cell.
0
 

Author Comment

by:Tim
ID: 36719727
Hi my code is below:  

                                       $temp_rang="A1:C2";
                                          $cell=$sheet2->Range($temp_rang);
              $cell->activate;
              $Cell->WrapText = True;
              $cell->MergeCells = True;
              $cell->value = "sadsadsad-sasadsad";
              $cell->BORDERS($xlEdgeRight)->Weight=2;
            $cell->BORDERS($xlEdgeBottom)->Weight=2;
            $Cell->HorizontalAlignment = xlRigh;

The mergecell works. but the wraptext not work.
Also the HorizontalAlignment right   not work.

Please help
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36813631
Can you show your entire code please so that I can run it locally.
0
Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

 

Author Comment

by:Tim
ID: 36817530
my code:
            
      $xlEdgeLeft = 7;
      $xlEdgeTop = 8;
      $xlEdgeBottom = 9;
      $xlEdgeRight = 10;
            
      
      $excelfile_name=test.".xls";
   
    $excel = new COM("excel.application") or die("Unable to instanciate excel");

    $excel->Visible = 0;
    $excel->DisplayAlerts = 0;       
    //open  document
    $excel->Workbooks->Open("C:\projects\lab_report_templates\qest.xls");
//  this is blank excel file

    $book=$excel->Workbooks(1);

      $sheet2=$book->worksheets(1);
      $sheet2->activate;
      
       $temp_rang="A1:C2";
                                          $cell=$sheet2->Range($temp_rang);
              $cell->activate;
              $Cell->WrapText = True;
              $cell->MergeCells = True;
              $cell->value = "sadsadsad- sasadsad- gdfgsdgfdsgfdsg";
              $cell->BORDERS($xlEdgeRight)->Weight=2;
            $cell->BORDERS($xlEdgeBottom)->Weight=2;
            $Cell->HorizontalAlignment = $xlRight;

      $file_name = "C:\projects\lab_report\\".$excelfile_name;
    if (file_exists($file_name)){
          unlink($file_name);
      }
      
      
    $excel->Workbooks[1]->SaveAs($file_name);
   
    //closing excel
    $book->Close(false);
      unset($sheet);
    unset($sheet2);
         $excel->Quit();
      //$excel->Release();
    $excel = null;
      unset($book);
      unset($excel);
      
      //unset($excel);
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36909173
Is that your entire script?

You have used $Cell and $cell.

These aren't the same thing.

If you add ...

error_reporting(-1);

to the top of your code you'll see more errors.

Can you add that line, run your code and show me the entire PHP output as well as the entire script you are using.

0
 

Author Comment

by:Tim
ID: 36909737
I got the error like below:

Creating default object from empty value in C:\projects\labweb\excelreport.php on line 29.

This line is    " $Cell->WrapText = True; "

Looks the WrapText  not right.

Please help.


0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 2000 total points
ID: 36910735
No.

$Cell is not right.

The variable is $cell.

PHP's variables are case sensitive.

php -r "$BOB = new stdclass; $bob->fred = 'jane'; var_dump($BOB, $bob);"

Open in new window


outputs ...

Strict Standards: Creating default object from empty value in Command line code on line 1
object(stdClass)#1 (0) {
}
object(stdClass)#2 (1) {
  ["fred"]=>
  string(4) "jane"
}

Open in new window


$bob didn't exist. Trying to create an object from nothing AND then create a property to this magic/invisible/non-existent object ... that's an E_STRICT error.

Change $bob to $BOB ...

php -r "$BOB = new stdclass; $BOB->fred = 'jane'; var_dump($BOB, $bob);"

Open in new window


and you get ...

Notice: Undefined variable: bob in Command line code on line 1
object(stdClass)#1 (1) {
  ["fred"]=>
  string(4) "jane"
}
NULL

Open in new window


See?



0
 

Author Comment

by:Tim
ID: 36910911
Thank you very very much.
It works. it is  case sensitive.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses

777 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