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

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
TimSenior PHP DeveloperAsked:
Who is Participating?
 
Richard QuadlingConnect With a Mentor Senior Software DeveloperCommented:
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
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
TimSenior PHP DeveloperAuthor Commented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Richard QuadlingSenior Software DeveloperCommented:
Can you show your entire code please so that I can run it locally.
0
 
TimSenior PHP DeveloperAuthor Commented:
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
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
TimSenior PHP DeveloperAuthor Commented:
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
 
TimSenior PHP DeveloperAuthor Commented:
Thank you very very much.
It works. it is  case sensitive.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.