CODEWITHSUNDEEP
phpphpexcel
user2430227
user2430227

Reputation: 303

Combine two or more xls files as worksheets PHPExcel

I've been looking everywhere on how to do this with two existing files, looks like all documentation is on creating new files. I'd like to take one of the files and add the second file to it as a new worksheet then save it to the server. I've been trying with no avail like this:

$file="test.xls";
$file2="test2.xls";
$outputFile = "final.xls";
$phpExcel = new PHPExcel($file);
$phpExcel->getActiveSheet();
$phpExcel->setActiveSheetIndex(0);
$phpExcel->addSheet($file2);
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$outputFile");
header("Cache-Control: max-age=0");
$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, "Excel5");
file_put_contents($outputFile, $objWriter);

Any help would be greatly appreciated. Very new to PHP.

Upvotes: 7

Views: 13747

Answers (3)

Dylan B
Dylan B

Reputation: 1010

// Combine all .csv files into one .xls file,
$cache_dir = "/home/user_name/public_html/";
$book1 = $cache_dir . "book1.csv";
$book2 = $cache_dir . "book2.csv";       
$outputFile = $cache_dir . "combined.xls";
$inputFileType = 'CSV'; 
$inputFileNames = array($book1,$book2); 

$objReader = new PHPExcel_Reader_CSV();

/**  Extract the first named file from the array list  **/ 
$inputFileName = array_shift($inputFileNames); 
/**  Load the initial file to the first worksheet in a PHPExcel Object  **/ 
$objPHPExcel = $objReader->load($inputFileName); 
/**  Set the worksheet title (to the filename that we've loaded)  **/ 
$objPHPExcel->getActiveSheet()
            ->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME)); 

/**  Loop through all the remaining files in the list  **/ 
foreach($inputFileNames as $sheet => $inputFileName) { 
     /**  Increment the worksheet index pointer for the Reader  **/ 
     $objReader->setSheetIndex($sheet+1); 
     /**  Load the current file into a new worksheet in PHPExcel  **/ 
     $objReader->loadIntoExisting($inputFileName,$objPHPExcel); 
     /**  Set the worksheet title (to the filename that we've loaded)  **/ 
     $objPHPExcel->getActiveSheet()->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME)); 

} 

$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save( $outputFile );

$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);

echo "DONE";

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212522

Doesn't anybody ever read documentation these days? There's a whole document in the folder called /Documentation about reading files to PHPExcel objects (it's called PHPExcel User Documentation - Reading Spreadsheet Files), together with dozens of examples (the /Documentation/Examples/Reader folder is a good place to look), and none of them use new PHPExcel($file). Nor do any of the examples or any of the documents say to use file_put_contents() when saving.

$file1="test.xls";
$file2="test2.xls";
$outputFile = "final.xls";

// Files are loaded to PHPExcel using the IOFactory load() method
$objPHPExcel1 = PHPExcel_IOFactory::load($file1);
$objPHPExcel2 = PHPExcel_IOFactory::load($file2);

// Copy worksheets from $objPHPExcel2 to $objPHPExcel1
foreach($objPHPExcel2->getAllSheets() as $sheet) {
    $objPHPExcel1->addExternalSheet($sheet)
}

// Save $objPHPExcel1 to browser as an .xls file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel1, "Excel5");
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$outputFile");
header("Cache-Control: max-age=0");
$objWriter->save('php://output');

Upvotes: 18

Vinod Joshi
Vinod Joshi

Reputation: 7862

    // update from office site

    $filenames = array('doc1.xlsx', 'doc2.xlsx');

    $bigExcel = new PHPExcel();
    $bigExcel->removeSheetByIndex(0);

    $reader = PHPExcel_IOFactory::createReader($input_file_type);

    foreach ($filenames as $filename) {
        $excel = $reader->load($filename);

        foreach ($excel->getAllSheets() as $sheet) {
            $bigExcel->addExternalSheet($sheet);
        }

        foreach ($excel->getNamedRanges() as $namedRange) {
            $bigExcel->addNamedRange($namedRange);
        }
    }

    $writer = PHPExcel_IOFactory::createWriter($bigExcel, 'Excel5');

    $file_creation_date = date("Y-m-d");

    // name of file, which needs to be attached during email sending
    $saving_name = "Report_Name" . $file_creation_date . '.xls';


    // save file at some random location    
    $writer->save($file_path_location . $saving_name);

    // More Detail : with different object: 
Merge multiple xls file into single one is explained here: 
I'm going to describe a bit different:

http://rosevinod.wordpress.com/2014/03/15/combine-two-or-more-xls-files-as-worksheets-phpexcel/

Upvotes: 4

Related Questions