9

I am using PHPExcel to generate an xl using php. I am not loading an xl sheet but creating new sheets using

$phpExcel = new PHPExcel();
$phpExcel->getActiveSheet()->setTitle("My Sheet");

I want to set active sheet using phpExcel using $phpExcel->setActiveSheetIndexByName("2");

but im getting an error setActiveSheetIndexByName not defined function.

Please help

Simone
  • 636
  • 2
  • 8
  • 25

4 Answers4

32

You do, of course, need to create/add additional worksheets to be able to change the active sheet: using new PHPExcel() will only create a workbook containing a single sheet.

You can set the active sheet using either the sheet index (sheets are indexed from 0); 

$objPHPExcel->setActiveSheetIndex(2);

or by name

$objPHPExcel->setActiveSheetIndexByName('My Second Sheet');

Adding a new sheet using either the createSheet() or addSheet() methods will automatically set that new worksheet to the active worksheet. By default, any new worksheet will be given a name comprising the word "Worksheet" and a number until you use setTitle() to change it.

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • ya i added 3 sheets using addSheet()but i want to set the 1st sheet to active .. is it possible? – Simone Aug 16 '13 at 12:15
  • Of course it's possible: indexed from 0, the first sheet will be indexed at 0, so `$objPHPExcel->setActiveSheetIndex(0);` – Mark Baker Aug 16 '13 at 12:18
  • Alternatively, `$objPHPExcel->setActiveSheetIndexByName($objPHPExcel->getSheetNames()[0]);` if you're running a version of PHP that allows array dereferencing – Mark Baker Aug 16 '13 at 12:20
  • ya i tried $objPHPExcel->setActiveSheetIndex(0); but still getting the error.. function nt defined :( – Simone Aug 16 '13 at 12:24
  • If you're getting function not defined, then there's something severely wrong with the installation, I'm surprised you can even instantiate a PHPExcel object... I can't understand how it's possible to create the object without PHP also providing access to all the public methods of that object. It suggests that PHP itself is snafu – Mark Baker Aug 16 '13 at 13:06
  • If PHP can't find a method for an object, then you should be getting an error message like: `Fatal error: Call to undefined method PHPExcel::setActiveSheetIndexByName() in /myscriptFilename.php on line xx` What is the exact error message you're getting? – Mark Baker Aug 16 '13 at 13:11
  • ya mark im getting this error only: Fatal error: Call to undefined method PHPExcel::setActiveSheetIndexByName() in my filename line no – Simone Aug 17 '13 at 03:10
  • I'M out of ideas.... I can't understand how an object can be instantiated, but PHP doesn't recognise the existence of its public methods... it goes against every rule of PHP coding, so I'm completely at a loss: look at the PHPExcel class file yourself for the setActiveSheetIndexByName() method, and see what I mean: Unless your version of the code has been edited and that method removed, it should be there, it should be public, it shouldn't give that error – Mark Baker Aug 17 '13 at 10:10
4

Add below function into Excel.php class file:

function setActiveSheet($sheetnumber) {
        $this->objPHPExcel->setActiveSheetIndex($sheetnumber);
    }

then call that function like this :

$phpExcel->setActiveSheet(0);
heena
  • 101
  • 5
0

You shouldn't need ByName. Try just setActiveSheetIndex(2);.

DevlshOne
  • 8,357
  • 1
  • 29
  • 37
0

If you are directly manipulating the xml in workbook.xml

$replace = '#activeTab="\d"#i';
$with = 'activeTab="0"';
$newxmlfile=preg_replace($replace,$with,$newxmlfile); // force activetab to sheet 1
zzapper
  • 4,743
  • 5
  • 48
  • 45