Home » File Formats » Spreadsheet_Excel_Writer » Bug #2426

[2004-10-01 04:45 UTC] derrickbtan dot lists at gmail dot com

Description:
------------
I am not able to set the format for merged cells using worksheet's (not formats) setmerge function. I was trying to specify a border for a set of merged cells on multiple lines. I also could not see a way to vertically align the data.

Reproduce code:
---------------
// $this = custom excel writer class
$formatArray = array( 'size' => 10, 'top' => 1, 'left' => 1, 'right' => 1,'align' => 'center' );

$this->format['box_trl'] = $this->xls->addFormat( $formatArray );
// getCol simply returns the integer
$ws->write( $row, $this->getCol("N"), "Notes", $this->format['box_trl'] );
// Merge the 2x2 columns
$ws->setMerge( $row++, $col++, $row--, $col );

Expected result:
----------------
A 2x2 merged cell with top, left and right borders.

Actual result:
--------------
The top and left borders appear on the first formatted cell, the rest are unformated.

[2004-10-13 15:23 UTC] smudge187 at hotmail dot com

This is an enormous problem when trying to create advanced excel spreadsheets used in business reporting. Is there even a workaround for this problem?

[2005-06-09 14:21 UTC] bxny78 at yahoo dot com

Here's a work around, however all entries in the border will be centered

$cart =& $xls->addWorksheet('workSheet');
$borderC =& $xls->addFormat();
$borderC->setFontFamily('Arial');
$borderC->setSize('10');
$borderC->setAlign('left');
$borderC->setColor('black');
$borderC->setTextWrap();
$borderC->setBorder (1);
$borderC->setAlign('merge'); # Required for cell merge

setupMergeCells(1, 1, 4, 4, $borderC, $cart);
$cart->write(1,1,"some testing",$borderC);

function setupMergeCells($sRow, $sCol, $eRow, $eCol, $fStyle, $cart)
{
$cart->mergeCells ($sRow, $sCol, $eRow, $eCol);
for($i = $sRow; $i <=$eRow; $i++)
{
for($j = $sCol; $j <= $eCol; $j++)
{
$cart->write($i,$j,'',$fStyle);
}
}
}

[2005-11-12 23:45 UTC] xnoguer at php dot net

this works for me:

require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer('bla.xls');
$worksheet =& $workbook->addWorksheet('data');
$workbook->setVersion(8);

$format = $workbook->addFormat(array('top' => 1, 'left' => 1, 'right' => 1,'align' => 'center'));
$worksheet->write(1, 1, "hola", $format);
$worksheet->writeBlank(1, 2, $format);
$worksheet->writeBlank(2, 1, $format);
$worksheet->writeBlank(2, 2, $format);
$worksheet->mergeCells(1, 1, 2, 2);

$workbook->close();

[2005-11-12 23:45 UTC] xnoguer at php dot net

this works for me:

require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer('bla.xls');
$worksheet =& $workbook->addWorksheet('data');
$workbook->setVersion(8);

$format = $workbook->addFormat(array('top' => 1, 'left' => 1, 'right' => 1,'align' => 'center'));
$worksheet->write(1, 1, "hola", $format);
$worksheet->writeBlank(1, 2, $format);
$worksheet->writeBlank(2, 1, $format);
$worksheet->writeBlank(2, 2, $format);
$worksheet->mergeCells(1, 1, 2, 2);

$workbook->close();

[2005-11-12 23:45 UTC] xnoguer at php dot net

this works for me:

require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer('bla.xls');
$worksheet =& $workbook->addWorksheet('data');
$workbook->setVersion(8);

$format = $workbook->addFormat(array('top' => 1, 'left' => 1, 'right' => 1,'align' => 'center'));
$worksheet->write(1, 1, "hola", $format);
$worksheet->writeBlank(1, 2, $format);
$worksheet->writeBlank(2, 1, $format);
$worksheet->writeBlank(2, 2, $format);
$worksheet->mergeCells(1, 1, 2, 2);

$workbook->close();