can't reference other sheets in equations when writing Biff8

[themolecule]

If I have Sheet1 and Sheet2, if a field in Sheet1 has an equation like '=Sheet2!A1', workbook creation will fail, stating 'Unknown sheet Sheet2'. This only applies to Biff8.

This seems related to the iconv() naming of sheets, and possibly the improper creation of some elements in Biff8.

I was able to get it to publish by adding:

    $sheetName2 = iconv('UTF-8','UTF-16LE',$sheetName);
    if (isset($this->extSheets[$sheetName2]))
            return $this->extSheets[$sheetName2];

to line 508 of FormulaParser, but the resulting file doesn't read properly.

[themolecule]

It would seem that storeExternsheetBiff8() is never called, so those references are missing in the resulting file.

[MAXakaWIZARD]

@themolecule Thanks for your feedback! I'll check this out and fix.

[MAXakaWIZARD]

@themolecule When you write formula in first sheet, second sheet is not yet created, so Sheet2 is unknown. You need create both sheets first and then write formulas.

[themolecule]

I thought that, but I'm pretty sure it's not the case.

This should work, but it doesn't seem to:

require( dirname ( __FILE__ ) . "/include/xls-writer-master/vendor/autoload.php" ) ; 

use Xls\Workbook; 
use Xls\Biff5; 
use Xls\Biff8; 

$workbook = new Xls\Workbook ( Biff8::VERSION ) ; 

$sheets = array () ; 

$s1 =& $workbook -> addWorksheet ( "S1" ) ; 
$ sheets [ "S1" ] = $ s1 ; 

$s2 =& $workbook -> addWorksheet ( "S2" ) ; 
$sheets [ "S2" ] = $s2 ; 

$s1 -> write ( 3 , 3 ,"=S2!C3 ", null ) ; 
$s2 -> write ( 3 , 3 ,"5678 ", null ) ; 

header ( "Content-type: application/vnd.ms-excel" ) ; 
header ( "Content-Disposition: attachment; filename= \"test.xls\"" ) ; 
header ( "Expires: 0" ) ; 
header ( "Cache-Control: must-revalidate, post-check=0,pre-check=0" ) ; 
header ( "Pragma: public" ) ; 

$workbook -> save ( 'php://output' ) ; 

I've also noticed that the supbook isn't written, and that the self-reference code is inverted (should be 0x0401, not 0x0104):

Record/Supbook.php:19 should be:

$data = pack ( "vv", count ( $worksheets ) , 0x0401 ) ; 

...because we're in LE packing.

So I've added to Workbook:192

$this -> storeExternbookBiff8 () ; 
$this -> storeExternsheetBiff8 () ; 

and created the storeExternbookBiff8() function as well.

Which doesn't quite do the trick!

So, from another library I pulled an xls reader to decode the biff chunks, and they all seem to line up with a valid xls file that was manually created... plus or minus some summary information and additional formatting.

Still on the hunt, though.

Thanks!

[MAXakaWIZARD]

@themolecule I'm working on version 2.0.0 (master branch). It will support only BIFF8. Thanks for tips about Supbook (I already noticed the bug with 0x0401). Also, I have reproduced the bug with formulas with other sheet references. So I'm looking forward to fix that (though I don't know the reason it does not work - xls can be opened by Excel, but formula does not work properly).

[themolecule]

I'm fairly certain that a problem exists in the boundsheet offset calculation... the position of the BOF in field 0. Checking that now.

[MAXakaWIZARD]

@themolecule I fixed this issue in 2.0.0-beta1. Check it out, if still interested)

[themolecule]

Dude! Thanks!

You beat me to it. I had identified a few minor bugs in the code, and had fixed the offset calculation problem. I was chasing down an index offset bug when you sent this.

Nice job! Running the new code against the snippet I posted works. Thanks much!

[MAXakaWIZARD]

@themolecule Thank you too! I think we can close this issue cause there is a test for it and it passes.

[themolecule]

Arg! Now I'm noticing number formats don't work.

----- Original Message -----

@themolecule Thank you too! I think we can close this issue cause there is a test for it and it passes. — Reply to this email directly or view it on GitHub .

The Molecule 594 Broadway Suite 906 New York, NY 10012 917.977.1122 (m) http://www.themolecule.com

[MAXakaWIZARD]

@themolecule Could you please open another issue with code sample? Thanks.