Solved

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

Posted on 2011-09-27
10
813 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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 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:Tim
ID: 36910911
Thank you very very much.
It works. it is  case sensitive.
0

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

707 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