spreadsheet_excel_validator

[Fendic]

Hi all,

I am using Pear's spreadsheet_excel_writer to create an Excel document. Everything works fine except that the validator is not fully developed.

I need to validate an excel cell between a list of strings. There is a function 'setList' in class spreadsheet_excel_validation_List that is commented out. I was trying to modify the code myself, but my knowledge is not enough to make it work. Could anyone have a look at this and give me a hand please? I'd really appreciate.

Here's my modified code:

in 'validator.php':

class Spreadsheet_Excel_Writer_Validation_List extends Spreadsheet_Excel_Writer_Validator
{
function Spreadsheet_Excel_Writer_Validation_list(&$parser)
{
parent::Spreadsheet_Excel_Writer_Validator($parser);
$this->_type = 0x00;
}

function setList($source, $incell = true)
{
$this->_incell = $incell;
$this->_fixedList = true;

$source = implode("\x00", $source);
$this->_formula1 = pack("CCC", 0x17, strlen($source), 0x0c) . $source;
}

function setRow($row, $col1, $col2, $incell = true)
{
$this->_incell = $incell;
//$this->_formula1 = ...;
}

function setCol($col, $row1, $row2, $incell = true)
{
$this->_incell = $incell;
//$this->_formula1 = ...;
}
}

in 'workbook.php':

function &addValidationList(){

include_once 'Spreadsheet/Excel/Writer/Validator.php';
$valid = new Spreadsheet_Excel_Writer_Validation_list($this->_parser);
return $valid;
}

in 'worksheet.php'

/**
* Store the DVAL and DVrecords.
*
* @access private
*/
function _storeDataValidity()
{
$record = 0x01b2; // Record identifier
$length = 0x0012; // Bytes to follow

$grbit = 0x0002; // Prompt box at cell, no cached validity data at DV records
$horPos = 0x00000000; // Horizontal position of prompt box, if fixed position
$verPos = 0x00000000; // Vertical position of prompt box, if fixed position
$objId = 0xffffffff; // Object identifier of drop down arrow object, or -1 if not visible

$header = pack('vv', $record, $length);
$data = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
count($this->_dv));
$this->_append($header.$data);

$record = 0x01be; // Record identifier
foreach ($this->_dv as $dv) {
$length = strlen($dv); // Bytes to follow
$header = pack("vv", $record, $length);
$this->_append($header . $dv);
}
}

/**
* FIXME: add comments
*/
function setValidation($row1, $col1, $row2, $col2, &$validator)
{
$this->_dv[] = $validator->_getData() .
pack("vvvvv", 1, $row1, $row2, $col1, $col2);
$this->_storeDataValidity();

}

and then I call it altogether:

require_once "Spreadsheet/Excel/Writer.php";


$xls =& new Spreadsheet_Excel_Writer();
$xls->send("myworkbook.xls");
$sheet1 =& $xls->addWorksheet('myworksheet');

$validator1 = & $xls->addValidationList();
$ary = array('lastname', 'email', 'phone');
$validator1->setList($ary);
$sheet1->setValidation(5,5,6,6, $validator1);


Many thanks!!!

Fendic