Splitting an Excel document sheet by sheet

Topics: User Forum
Oct 1, 2011 at 8:05 AM

Hello,

I can't figure how to split an Excel document sheet by sheet,where each sheet is created as a new Excel document.

I also need to delete the empty lines in the new Excel documents produced.

What's the best way to do it ?

All your experiences are greatly appreciated.

Best regards.

Oct 3, 2011 at 4:18 AM
Edited Oct 3, 2011 at 4:19 AM

I have found the way of what I wanted.

Here is a solution (maybe not the best way, but it works fine enough) :   

    $file = $_POST['file'];
    $filename = pathinfo($file, PATHINFO_FILENAME);
 
    require_once 'phpexcel/Classes/PHPExcel.php';

    $xls = new PHPExcel();
    $xlsReader= new PHPExcel_Reader_Excel5();
    $xlsTemplate = $xlsReader->load($file);

    $sheet1 = $xlsTemplate->getSheetByName('Sheet1');
    $xls->addExternalSheet($sheet1,0);
    $xls->removeSheetByIndex(1);
    $xlsWriter = new PHPExcel_Writer_Excel5($xls);
    $xlsWriter->save($filename."_Sheet1.xls");

    $sheet2 = $xlsTemplate->getSheetByName('Sheet2');
    $xls->addExternalSheet($sheet2,0);
    $xls->removeSheetByIndex(1);
    $xlsWriter = new PHPExcel_Writer_Excel5($xls);
    $xlsWriter->save($filename."_Sheet2.xls");

    $sheet3 = $xlsTemplate->getSheetByName('Sheet3');
    $xls->addExternalSheet($sheet3,0);
    $xls->removeSheetByIndex(1);
    $xlsWriter = new PHPExcel_Writer_Excel5($xls);
    $xlsWriter->save($filename."_Sheet3.xls");

    $sheet4 = $xlsTemplate->getSheetByName('Sheet4');
    $xls->addExternalSheet($sheet4,0);
    $xls->removeSheetByIndex(1);
    $xlsWriter = new PHPExcel_Writer_Excel5($xls);
    $xlsWriter->save($filename."_Sheet4.xls");

    $sheet5 = $xlsTemplate->getSheetByName('Sheet5');
    $xls->addExternalSheet($sheet5,0);
    $xls->removeSheetByIndex(1);
    $xlsWriter = new PHPExcel_Writer_Excel5($xls);
    $xlsWriter->save($filename."_Sheet5.xls");

    $sheet6 = $xlsTemplate->getSheetByName('Sheet6');
    $xls->addExternalSheet($sheet6,0);
    $xls->removeSheetByIndex(1);
    $xlsWriter = new PHPExcel_Writer_Excel5($xls);
    $xlsWriter->save($filename."_Sheet6.xls");

Then, my original Excel file, containing 6 sheets is now splitted in 6 Excel files, as I wanted.

As you can see, it was not so hard to release, but the documentation is so confusing...

Hope this can help.