11

I used PHPExcel library to generate excel files based on the table created by the mysql query. I created multiple tabs with individual data from different queries.

I need to align the data in the all the cells in all the tabs (worksheets) to center.

This is my code:

$mysql_xls = new MySqlExcelBuilder($mysql_db,$mysql_user,$mysql_pass);

// Add the SQL statements to the spread sheet

$tab_name = "tabname";
$mysql_xls->add_page($tab_name,$sql_statement,NULL,'A',1);

$phpExcel = $mysql_xls->getExcel();

$phpExcel->setActiveSheetIndex(0); // Set the sheet to the first page (default first page).

I tried the following to align the text in the cells but no change:

$phpExcel->getActiveSheet(0)->getStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
Littm
  • 4,923
  • 4
  • 30
  • 38
user1702273
  • 123
  • 1
  • 1
  • 6

1 Answers1

30

Option #1

Set a default style for the entire workbook

$objPHPExcel->getDefaultStyle()
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

Option #2

Apply the style to a range of cells (the entire worksheet in this case) on each individual worksheet

$phpExcel->getActiveSheet()
    ->getStyle( $phpExcel->getActiveSheet()->calculateWorksheetDimension() )
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Mr.Backer Thanks for the reply and for the amazing Library! your answer worked perfectly but i am getting a white space on the right of the cells in the first row(column names), which i don't want, is there any solution to get rid of that space. here is a Screen shot: http://tinypic.com/view.php?pic=2sayqvc&s=6 – user1702273 Sep 27 '12 at 09:36
  • You'll need to set the column width to autocalculate – Mark Baker Sep 27 '12 at 10:00
  • Sorry, but the formatting to center is not applied for the column names, is there any reason for that and how can apply the same formatting for the columns names i.e row 1. – user1702273 Sep 27 '12 at 10:23
  • Which of the two methods did you use? PHPExcel doesn't know it's a column name, it's just another cell in the worksheet, so it doesn't pick and choose which cells to apply the styling to – Mark Baker Sep 27 '12 at 10:45
  • I used Option #1. except for the first row the formatting is applied for all the cells. – user1702273 Sep 27 '12 at 10:51
  • If you're setting the global default to be centred, then it should apply to all cells in the workbook unless you explicitly override it for individual cells – Mark Baker Sep 27 '12 at 10:56
  • Some formatting is being applied to the first row so that they are BOLD by default. May be that's causing the problem. But to be sure am not applying any formatting in the code. For your reference i use the MySqlExcelBuilder Class to build the excels from the mysql query. – user1702273 Sep 27 '12 at 11:06