Solved

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

Posted on 2011-09-27
10
798 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:zcfyhome
  • 4
  • 4
10 Comments
 
LVL 40

Expert Comment

by:RQuadling
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:zcfyhome
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:RQuadling
ID: 36813631
Can you show your entire code please so that I can run it locally.
0
 

Author Comment

by:zcfyhome
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 40

Expert Comment

by:RQuadling
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:zcfyhome
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:
RQuadling earned 500 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:zcfyhome
ID: 36910911
Thank you very very much.
It works. it is  case sensitive.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
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).

895 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now