Home » File Formats » Spreadsheet_Excel_Writer » Bug #8303

[2006-07-27 06:57 UTC] dsanders at baselinesolutions dot com dot au

Description:
------------
It'd be great to add support for auto sizing of the column widths... I've attempted to create my own patch for this, but I don't fully understand how this package stores the cell information so I guess you guys could do a better job.

A possible simple patch for this would be in Worksheet.php. (see test script)

This patch assumes that 1 unit of width is about the same as the width of 1 character at size 10 Arial. It's not accurate, but it gives me satisfactory sizing of the columns.

Test script:
---------------
@@ -497,6 +497,12 @@ class Spreadsheet_Excel_Writer_Worksheet
{
$num_sheets = count($sheetnames);

+ // set the column widths, if we need to
+ if (!empty($this->col_auto_width))
+ {
+ $this->_setColumnAutoWidths();
+ }
+
/***********************************************
* Prepend in reverse order!!
*/
@@ -740,6 +746,44 @@ class Spreadsheet_Excel_Writer_Worksheet
}
}

+ var $col_auto_width_padding = array();
+ var $col_widths = array();
+ var $col_auto_width = array();
+ /**
+ *
+ * @access public
+ * @param integer $firstcol first column on the range
+ * @param integer $lastcol last column on the range
+ * @param bool $flag turn on/off the auto-width
+ * @param integer $padding amount of padding to add to the column width
+ */
+ function setColumnAutoWidth($firstcol, $lastcol, $flag = true, $padding = 0)
+ {
+ for ($col = $firstcol; $col <= $lastcol; $col++)
+ {
+ if ($flag)
+ {
+ $this->col_auto_width[$col] = $flag;
+ }
+ else
+ {
+ unset($this->col_auto_width[$col]);
+ }
+ $this->col_auto_width_padding[$col] = $padding;
+ }
+ }
+
+ function _setColumnAutoWidths()
+ {
+ foreach ($this->col_auto_width as $col => $something)
+ {
+ if (isset($this->col_widths[$col]))
+ {
+ $this->setColumn($col,$col,$this->col_widths[$col] + $this->col_auto_width_padding[$col]);
+ }
+ }
+ }
+
/**
* Set which cell or cells are selected in a worksheet
*
@@ -1410,6 +1454,16 @@ class Spreadsheet_Excel_Writer_Worksheet
*/
function writeNumber($row, $col, $num, $format = 0)
{
+ // record the number of digits, if we are auto column sizing
+ if (!empty($this->col_auto_width[$col]))
+ {
+ $length = strlen($num);
+ if (empty($this->col_widths[$col]) || $length > $this->col_widths[$col])
+ {
+ $this->col_widths[$col] = $length;
+ }
+ }
+
$record = 0x0203; // Record identifier
$length = 0x000E; // Number of bytes to follow

@@ -1463,6 +1517,17 @@ class Spreadsheet_Excel_Writer_Worksheet
*/
function writeString($row, $col, $str, $format = 0)
{
+ // record the string length, if using auto column sizing
+ if (!empty($this->col_auto_width[$col]))
+ {
+ $length = strlen($str);
+
+ if (empty($this->col_widths[$col]) || $length > $this->col_widths[$col])
+ {
+ $this->col_widths[$col] = $length;
+ }
+ }
+
if ($this->_BIFF_version == 0x0600) {
return $this->writeStringBIFF8($row, $col, $str, $format);
}
@@ -1813,6 +1878,16 @@ class Spreadsheet_Excel_Writer_Worksheet
*/
function writeUrl($row, $col, $url, $string = '', $format = 0)
{
+ // record the string length, if using auto column sizing
+ if (!empty($this->col_auto_width[$col]))
+ {
+ $length = strlen($url);
+ if (empty($this->col_widths[$col]) || $length > $this->col_widths[$col])
+ {
+ $this->col_widths[$col] = $length;
+ }
+ }
+
// Add start row and col to arg list
return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format));
}