Solved

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

Posted on 2011-09-27
10
808 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
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 …

740 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