can I modify worksheet's title after "adding" this worksheet to workbook in spreadsheet_excel_writer?

Question

the problem is that I cant set Worksheet::setInputEncoding to a worksheet that isnt created, but I need to set name in utf=8 format to this worksheet.

so for now I got this result: some unreadable symbols in worksheet title.

any suggestions?

score 0 · Accepted Answer · answered Sep 13 '17 at 07:48

My (hopefully correct) findings:

UTF-8 in worksheet names is only possible in BIFF8 format.
Spreadsheet_Excel_Writer_Worksheet constructor hard-codes UTF-8 as input encoding for worksheet name.
There isn't a builtin method to rename a worksheet.

Thus need to be careful about this:

Set BIFF8 as format as soon as possible with \Spreadsheet_Excel_Writer_Workbook::setVersion() (there're added benefits like extended worksheet limits):
```
$workbook->setVersion(8);
```
If you aren't using UTF-8 (e.g. because it's a legacy project), convert worksheet name upon worksheet creation:
```
$name = iconv('Windows-1252', 'UTF-18', $name);
$worksheet = $workbook->addWorksheet($name);
```

If you need to rename a sheet, you need to compose your own method:

// Disclaimer: not fully tested code; shared for illustration purposes

/* @var $workbook Spreadsheet_Excel_Writer_Workbook */
/* @var $worksheet Spreadsheet_Excel_Writer_Worksheet */

$name = iconv($input_encoding, 'UTF-16LE', $name);

foreach($workbook->_worksheets as $c => $v){
    if($v->getName()===$name){
        throw new LogicException("New name is already in use: $name");
    }
}

$worksheet->name = $name;
$workbook->_sheetnames[$worksheet->index] = $name;          // Store EXTERNSHEET names
$workbook->_parser->setExtSheet($name, $worksheet->index);  // Register worksheet name with parser

score 0 · Answer 2 · answered Oct 18 '11 at 04:02

you might be out of luck there, are you sure biff format actually supports unicode chars in the sheet name. the sheet names are pretty restrictive

i have switched most of my excel sheet generation to use phpexcel now, maybe that will be suitable for you

can I modify worksheet's title after "adding" this worksheet to workbook in spreadsheet_excel_writer?

2 Answers2