Combining multiple .xlsx files into one big workbook

Topics: Developer Forum
Oct 3, 2008 at 3:27 PM
Does anyone have sample code for doing this? I'd like to read in a number of Excel files (xlsx format), each with multiple worksheets and named data ranges, and append the sheets to a larger file so that in the end I have one large workbook with multiple sheets - preserving the sheetnames and named data ranges.  

Thanks in advance,
Marisa
Developer
Oct 4, 2008 at 4:58 PM
Edited Jun 8, 2009 at 12:57 PM

You may try out the following:

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

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

$reader = new PHPExcel_Reader_Excel2007();

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 = new PHPExcel_Writer_Excel2007($bigExcel);
$writer->save('2007-write.xlsx');

Note that if some of the sheet have identical names, then addExternalSheet() will throw an exception.

Edit: Corrected addSheet($sheet) to addExternalSheet($sheet) so that styles will not be messed up in PHPExcel 1.7.0.

Jul 16, 2009 at 9:27 PM
Edited Jul 16, 2009 at 9:35 PM

Sorry to be a thread Necro, but I'm having a bit of a problem with this when trying to combine multiple sheets each with 10,000 records.

What I'm doing to save my server memory is creating these sheets one at a time using the exec() function so that the memory gets cleared every time one is completed. Before when I didn't do that and just creating each one in a for loop it wasn't releasing the memory and it was getting up to 1.8gigs going through 40,000 rows in a query.  Now it takes up just ~480megs per 10,000 records which is fine.  The way I'm trying to combine these is looping through the sheets and combining them into one another one at a time. This works if there are only a couple but when it gets to around 3 or 4 sheets, it starts sucking up tons of memory since two sheets have to be loaded into memory for one of them to be inserted into the other.

My question is: Is there a way to load an excel file without having to put all the rows into memory and just save a sheet from another excel file into it, also without having to load all those rows as well?  Loading two excel files into memory one with 30,000 rows and the other with 10,000 rows is just a bit much for my server to handle heh.

I appreciate any response.  Thank you!

Developer
Jul 17, 2009 at 5:42 PM

@meenie

>> Is there a way to load an excel file without having to put all the rows into memory
>> and just save a sheet from another excel file into it, also without having to load all
>> those rows as well?


The reader can be set to load only specific sheets. There is an example here where only a single sheet is loaded:
http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=62528

You can also decide to load specific cells only. Check out 24readfilter.php in the Tests folder.

>> Now it takes up just ~480megs per 10,000 records which is fine.

This sounds like very high memory usage. Are you using PHPExcel 1.6.7 or the latest source code? Latest source code is likely to give you much better results.

Jul 17, 2009 at 9:39 PM

Okay, loading one sheet when loading up the main workgroup spreadsheet would probably save a lot of memory when it starts to have multiple sheets in it :).  The thing is when I do that, it keeps on writing over the second sheet.  So if I have 5 sheets to combine it does the first one, then the next time it only loads the first one and puts the second one in the second slot, then the next time it only loads the first one an puts the third one in the second slot overwriting the second sheet and so on.  So at the end I only have sheets 1 and 6 in the workbook heh.  This is the code I'm using:

    function create_workbook()
    {
        ini_set('max_execution_time', 300);
        ini_set("memory_limit", "512M");
        $this->load->library('excel');
        
        $sheet = $_GET['sheet'];
        $workbook = $_GET['workbook'];
        $format = $_GET['format'];
        
        $workbook = $this->excel->load_one_sheet($workbook, $format, 'Subscribers Sheet 1');
        $sheet = $this->excel->load($sheet, $format);
            
        $workbook->addExternalSheet($sheet->getSheet(0));
        
        $extension = $format == 'Excel2007' ? '.xlsx' : '.xls';
        $filename = BASEPATH . 'cache/' . time() . $extension;
        
        $excelWriter = PHPExcel_IOFactory::createWriter($workbook, $format);
        $excelWriter->save($filename);
        
        echo $filename;
    }

And this is the code I'm using to call that function ($filenames is the array of filenames from when the individual sheet's where created.):

 

        $workbook = '';
        $count = 1;
        $num_of_sheets = count($filenames);
        $first = array_shift($filenames);
        foreach ($filenames as $filename)
        {
            if ($workbook == '')
            {
                $workbook = str_replace("</div>", '', exec("php index.php c=subscribers m=create_workbook sheet={$filename} format={$format} workbook={$first}"));
            }
            else
            {
                $workbook = str_replace("</div>", '', exec("php index.php c=subscribers m=create_workbook sheet={$filename} format={$format} workbook={$workbook}"));
            }
            $count++;
            
            unlink($filename);
            
            if ($count != $num_of_sheets)
            {
                $delete_workbooks[] = $workbook;
            }
        }
        foreach ($delete_workbooks as $delete_workbook)
        {
            unlink($delete_workbook);
        }
        
        $ext = $format == 'Excel2007' ? '.xlsx' : '.xls';
        $this->send_to_browser($workbook, $ext);
Jul 17, 2009 at 9:43 PM

And to answer your second question, I'm creating a sheet with 19 columns and each sheet has 10000 rows.

To see if I was doing something wrong I ran one of your test scripts called 06largscale.php, made it so it would do 19 columns and 10,000 rows and it came out to using the same amount of RAM.
And yes, I am using source code: phpexcel-28167.zip.

Developer
Jul 18, 2009 at 3:28 AM

In the development snapshot, there seemed to be a bug in PHPExcel_Reader_Excel5 which could cause the weirdness you are describing when reading multiple xls files with the same instance of the reader.

Can you try with the next source code release?
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Jul 19, 2009 at 11:57 AM

I've tried the newest release but no, it's still taking up ~450megs of RAM to create a sheet of 10,000 rows with 19 columns.

Also when trying to combine the sheets, each sheet that gets loaded adds ~450megs.  So if I open the first one and the second one and add the second one to the first one it takes ~900megs.  Then when opening the workbook with the first and second sheets to add a third sheet it takes around ~1.35 gigs.  Then when adding a forth sheet to the first 3, it takes ~1.8 gigs.  This is not good heh

If I use the method you were talking about above about only opening one sheet, at the end I end up with a workbook that has the first sheet and the fifth sheet only.

So ya...I wish there was an easier way to combine sheets into workbooks without having to load them all into memory.

Developer
Jul 20, 2009 at 12:24 AM

>> If I use the method you were talking about above about only opening one sheet, at the end I end up with a workbook that has the first sheet and the fifth sheet only.

Is it possible that you can provide a mini test case for this problem. I tried to reconstruct the error, but I couldn't.

For example, I cannot see what the load_one_sheet() method does. Not sure either if you are reading xls or xlsx files.

Jul 20, 2009 at 12:42 AM

Sure :) - I'm using CodeIgniter as my framework and an class that interfaces with phpexcell and codeigniter. I'm trying to create Excell2007 files, not having much luck with Excell5 at the moment, but I'm not too worried until I can get the Excell2007 working correctly.  I've created a function called 'load_one_sheet' like this:

    function load_one_sheet($file, $format, $sheet_name)
    {
        $reader = PHPExcel_IOFactory::createReader($format);
        $reader->setReadDataOnly(TRUE);
        $reader->setReadFiltersetLoadSheetsOnly($sheet_name);
        return $reader->load($file);
    }

My other load function just called 'load' is this:
    function load($file, $format)
    {
        $reader = PHPExcel_IOFactory::createReader($format);
        $reader->setReadDataOnly(TRUE);
        return $reader->load($file);
    }
The way I'm creating my sheets is in my controller I'm doing this:
        $query = $this->db->query("SELECT `subscribers`.*
                                   FROM (`subscribers`)
                                   LEFT JOIN `users` as users ON `users`.`id` =
                                       `subscribers`.`user_id`
                                   WHERE `users`.`client_id` = '{$client_id}'");
        
        $limit = ROWS_PER_SHEET; //This is set at 10000
        $num_of_rows = $query->num_rows();
        
        $num_of_sheets = ceil($num_of_rows / $limit);

        for ($count = 1; $count <= $num_of_sheets; $count++)
        {
            $filenames[] = str_replace("</div>", '', exec("php index.php c=subscribers m=create_sheet count={$count} limit={$limit} client_id={$u->client_id} format={$format}"));
        }

The exec() is calling this function inside the controller (the reason I'm using exec is so that php will release the memory after each sheet is made, other wise it just keeps on sucking it all up until each sheet is made):
    function create_sheet()
    {
        ini_set('max_execution_time', 300);
        ini_set("memory_limit", "512M");
        $this->load->library('excel');
        $count = $_GET['count'];
        $limit = $_GET['limit'];
        $format = $_GET['format'];
        $client_id = $_GET['client_id'];

        $offset = ($count - 1) * $limit;
            
        $query = $this->db->query("SELECT `subscribers`.*
                               FROM (`subscribers`)
                               LEFT JOIN `users` as users ON `users`.`id` =
                                   `subscribers`.`user_id`
                               WHERE `users`.`client_id` = '{$client_id}'
                               LIMIT {$limit} OFFSET {$offset}");

        $sheet_name = "Subscribers Sheet {$count}";
        
        echo $this->excel->create_sheet($query, $format, $sheet_name, $count);
    }
And finally, the 'create_sheet' function I'm using in my class that interfaces between phpexcel and codeigniter is this:
    function create_sheet($query, $format, $sheet_name, $count)
    {
        $CI =& get_instance();
        
        $this->new_workbook();
  
        $this->workbook->setActiveSheetIndex(0)->setTitle($sheet_name)
        ->setCellValueExplicit('A1', 'Title')
        ->setCellValueExplicit('B1', 'First name')
        ->setCellValueExplicit('C1', 'Last Name')
        ->setCellValueExplicit('D1', 'Email')
        ->setCellValueExplicit('E1', 'Gender')
        ->setCellValueExplicit('F1', 'Address Line 1')
        ->setCellValueExplicit('G1', 'Address Line 2')
        ->setCellValueExplicit('H1', 'Suburb')
        ->setCellValueExplicit('I1', 'State')
        ->setCellValueExplicit('J1', 'Country')
        ->setCellValueExplicit('K1', 'Post Code')
        ->setCellValueExplicit('L1', 'Home Phone')
        ->setCellValueExplicit('M1', 'Work Phone')
        ->setCellValueExplicit('N1', 'Mobile Phone')
        ->setCellValueExplicit('O1', 'Fax')
        ->setCellValueExplicit('P1', 'Birthdate')
        ->setCellValueExplicit('Q1', 'Orginisation')
        ->setCellValueExplicit('R1', 'Comments')
        ->setCellValueExplicit('S1', 'ID');

        $i = 2;
        foreach ($query->result() as $subscriber)
        {
            $this->workbook->setActiveSheetIndex(0)->setCellValueExplicit('A'.$i, $subscriber->title)
            ->setCellValueExplicit('B'.$i, $subscriber->first_name)
            ->setCellValueExplicit('C'.$i, $subscriber->last_name)
            ->setCellValueExplicit('D'.$i, $subscriber->email)
            ->setCellValueExplicit('E'.$i, $subscriber->gender)
            ->setCellValueExplicit('F'.$i, $subscriber->address_line_1)
            ->setCellValueExplicit('G'.$i, $subscriber->address_line_2)
            ->setCellValueExplicit('H'.$i, $subscriber->suburb);
            $query = $CI->db->query("SELECT `states`.*
                                       FROM (`states`)
                                       LEFT JOIN `subscribers` as subscribers ON `states`.`id` =
                                           `subscribers`.`state_id`
                                       WHERE `subscribers`.`id` = '{$subscriber->id}'");
            $state = $query->row();
            $this->workbook->setActiveSheetIndex(0)->setCellValueExplicit('I'.$i, $state->name);
            $query = $CI->db->query("SELECT `countries`.`name`
                                       FROM (`countries`)
                                       LEFT JOIN `states` as states ON `countries`.`id` =
                                           `states`.`country_id`
                                       WHERE `states`.`id` = '{$state->id}'");
            $country = $query->row();
            $this->workbook->setActiveSheetIndex(0)->setCellValueExplicit('J'.$i, $country->name)
            ->setCellValueExplicit('K'.$i, $subscriber->post_code)
            ->setCellValueExplicit('L'.$i, $subscriber->home_phone)
            ->setCellValueExplicit('M'.$i, $subscriber->work_phone)
            ->setCellValueExplicit('N'.$i, $subscriber->mobile_phone)
            ->setCellValueExplicit('O'.$i, $subscriber->fax)
            ->setCellValueExplicit('P'.$i, date('d/m/Y', $subscriber->birthdate))
            ->setCellValueExplicit('Q'.$i, $subscriber->orginisation)
            ->setCellValueExplicit('R'.$i, $subscriber->comments)
            ->setCellValueExplicit('S'.$i, $subscriber->id);

            $i++;
        }
        $extension = $format == 'Excel2007' ? '.xlsx' : '.xls';
        $filename = BASEPATH . 'cache/' . time() . $extension;
        
        $excelWriter = PHPExcel_IOFactory::createWriter($this->workbook, $format);
        
        $excelWriter->save($filename); 
        unset($excelWriter);
        unset($this->workbook);

        return $filename;
    }

Those 3 functions get called just before the code I posted earlier that creates the workbook. Now if I don't use the 'load_one_sheet' function and just use 'load' it will take up that 1.8gigs of ram when working with 5 sheets. If I do use that function, I end up with a workbook that has Sheet 1 and Sheet 5 only.
Developer
Jul 21, 2009 at 4:18 AM

I looked at your code, there is quite a lot, but I am not able to see where things go wrong. For example I'm not sure what the setReadFiltersetLoadSheetsOnly() method does.

Instead of asking you to post more code I tried a simple script.

Let's say I have 3 Excel files: 'apple.xlsx', 'banana.xlsx', 'citrus.xlsx'.

apple.xlsx has 3 sheets: 'apple1', 'apple2', 'apple3'
banana.xlsx has 4 sheets: 'banana1', 'banana2', 'banana3', 'banana4'
citrus.xlsx has 2 sheets: 'citrus1', 'citrus2'

Here is how they look:
http://img404.imageshack.us/img404/6246/microsoftexcelapplexlsx.png
http://img404.imageshack.us/img404/5777/microsoftexcelbananaxls.png
http://img404.imageshack.us/img404/2972/microsoftexcelcitrusxls.png

I want to create a workbook 2007-write.xlsx containing sheets 'apple1', 'apple2', 'banana2', 'banana4', 'citrus2'

Here is my script that combines them (disregarding defined names):

$filenames = array('apple.xlsx', 'banana.xlsx', 'citrus.xlsx');

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

$reader = new PHPExcel_Reader_Excel2007();

foreach ($filenames as $filename) {
    switch ($filename) {
        case 'apple.xlsx':        $loadSheetsOnly = array('apple1', 'apple2');    break;
        case 'banana.xlsx':        $loadSheetsOnly = array('banana2', 'banana4');    break;
        case 'citrus.xlsx':        $loadSheetsOnly = array('citrus2');                break;
    }
    
    $reader->setReadDataOnly(true);
    $reader->setLoadSheetsOnly($loadSheetsOnly);
    $excel = $reader->load($filename);

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

$writer = new PHPExcel_Writer_Excel2007($bigExcel);
$writer->save('2007-write.xlsx');


Here is the result:
http://img404.imageshack.us/img404/2623/microsoftexcel2007write.png

As you can see the result is as expected. There are no missing sheets, or sheets going into wrong slots.

Can you try to compare the above code with your code and see if they differ?

Sep 23, 2009 at 10:55 AM

I am getting this

Exception: Workbook already contains a worksheet named 'Worksheet'. Rename the external sheet first. in /var/www/phpexcel/Classes/PHPExcel.php on line 398

while using the code given below. Please suggest if there is any change required in this?

 

$filenames = array('2006.xlsx', '2007.xlsx', '2008.xlsx');

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

$reader = new PHPExcel_Reader_Excel2007();

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 = new PHPExcel_Writer_Excel2007($bigExcel);
$writer->save('2007-write.xlsx');

Developer
Sep 23, 2009 at 12:09 PM

>> Please suggest if there is any change required in this?

I think you might have some identical sheet names in the workbooks you are combining. Remember that your combined workbook must not have two sheets with the same title. We should probably fix PHPExcel so it automatically renames the sheet, from, say, 'Sheet1' to 'Sheet1 (2)' when you add an external sheet having some name that is already taken by another worksheet.

For now, try to rename the sheet with some unique title before using adding it with addExternalSheet()

Sep 23, 2009 at 12:45 PM

Yes.  all the files having same work sheet with default name 'worksheet'.

how can I set the worksheet name while creating the excel file using PHPExcel?

 

 

 

 

Developer
Sep 23, 2009 at 12:54 PM

Here would be the place to rename the sheet:

foreach ($excel->getAllSheets() as $sheet) {
    $sheet->setTitle('unique title for the sheet in combined workbook'); // <--- rename sheet if necessary
 $bigExcel->addExternalSheet($sheet); }

Sep 24, 2009 at 6:01 AM

Thank you.

I have created the combined .xlsx file on the server. Now if I want to save this file at user end then what would be the header settings required

currentty I am using this

 

header("Pragma: public");

header("Expires: 0");

            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

            header("Content-Type: application/force-download");

            header("Content-Type: application/octet-stream");

            header("Content-Type: application/download");;

            header("Content-Disposition: attachment;filename=report.xlsx");

            header("Content-Transfer-Encoding: binary");

 

 

$bigExcel = new PHPExcel();
$bigExcel->removeSheetByIndex(0);
$reader = new PHPExcel_Reader_Excel2007();
foreach ($filenames as $filename) {
    $excel = $reader->load($filename);    
    foreach($excel->getAllSheets() as $sheet) {
     $sheet->setTitle($year); // <--- rename sheet if necessary
     $bigExcel->addExternalSheet($sheet);
    }
    
    foreach ($excel->getNamedRanges() as $namedRange) {
        $bigExcel->addNamedRange($namedRange);
    }    
    $year++;   
}
$writer = new PHPExcel_Writer_Excel2007($bigExcel);
$writer->save("php://output");

 

$filenames = array('2005.xlsx','2006.xlsx', '2007.xlsx','2008.xlsx', '2009.xlsx');

$year=2005;

$bigExcel = new PHPExcel();

$bigExcel->removeSheetByIndex(0);

$reader = new PHPExcel_Reader_Excel2007();

foreach ($filenames as $filename) {

    $excel = $reader->load($filename);    

    foreach($excel->getAllSheets() as $sheet) {

     $sheet->setTitle($year); // <--- rename sheet if necessary

     $bigExcel->addExternalSheet($sheet);

    }

 

    foreach ($excel->getNamedRanges() as $namedRange) {

        $bigExcel->addNamedRange($namedRange);

    }    

    $year++;   

}

$writer = new PHPExcel_Writer_Excel2007($bigExcel);

$writer->save("php://output");

 

 

but after this downloaded file is garbled


 

Coordinator
Sep 24, 2009 at 7:41 AM

HTTP headers

Example of a script redirecting an Excel 2007 file to the client's browser:

 

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="myfile.xlsx"');
header('Cache-Control: max-age=0');
 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

 

Pay particular attention to the Content-Type header

Sep 24, 2009 at 9:09 AM

I have tried with this code but the result is same garbled file.

Is this kind of download requires any change in apache's config file?

 

Coordinator
Sep 24, 2009 at 10:57 AM
Edited Sep 24, 2009 at 10:58 AM
vishalsahu wrote:

>> Is this kind of download requires any change in apache's config file?

No changes are required to Apache at all

vishalsahu wrote:

>> I have tried with this code but the result is same garbled file.

Does this garbled file appear in the browser window? Or does the browser pop up the "Open/Save" dialogue box?

Sep 24, 2009 at 11:07 AM

Browser pops up the  "Open/Save" dialogue box

Coordinator
Sep 24, 2009 at 11:39 AM
vishalsahu wrote:

Browser pops up the  "Open/Save" dialogue box

 If your script has error reporting set to E_ALL, do you get any errors (particularly headers already sent)

Sep 24, 2009 at 12:22 PM

I am providing the complete code for the script

include("../functions_and_variables.php");
$ceo = check_designation_id("C.E.O");
$svp1 = check_designation_id ("SENIOR VICE PRESIDENT (H.R)");
$svp2 = check_designation_id ("Senior Vice President (Technical)");			
$database = Select_db("users_db");
/** Error reporting */
error_reporting(E_ALL);
/** PHPExcel */
require_once '/var/www/phpexcel/Classes/PHPExcel.php';
/** PHPExcel_IOFactory */
require_once '/var/www/phpexcel/Classes/PHPExcel/IOFactory.php';
require_once '/var/www/phpexcel/Classes/PHPExcel/Reader/Excel2007.php';
require_once '/var/www/phpexcel/Classes/PHPExcel/Writer/Excel2007.php';
			
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Attendance Register")
->setLastModifiedBy("Attendance Register");
							 
			

$group = "Present";
$_REQUEST['location']="all";

for($yearCnt=2005; $yearCnt<=2009; $yearCnt++)
{   
	$objPHPExcel->getProperties()->setTitle("Office $yearCnt XLSX Test Document")
	->setTitle("Office $yearCnt XLSX Test Document")
					 ->setSubject("Office $yearCnt XLSX Test Document")
					 ->setDescription("Test document for Office $yearCnt XLSX, generated using PHP classes.")
					 ->setKeywords("office $yearCnt openxml php")
					 ->setCategory("Test result file $yearCnt");
	$startDate = "$yearCnt-01-01";	
    $endDate =  "$yearCnt-12-31";    
    if($group=="Present")
    {
    	$empSelectQuery = "select Admin_Tbl.uniqid, CONCAT(first_name,' ', middle_name, ' ',last_name) as fullname, emp_id, join_date, relieving_date, location
        from Admin_Tbl, users_tbl WHERE
        Admin_Tbl.uniqid=users_tbl.uniqid and (Admin_Tbl.relieving_date=\"0000-00-00\" || Admin_Tbl.relieving_date IS NULL || Admin_Tbl.relieving_date>=\"$endDate\" || (Admin_Tbl.relieving_date>=\"$startDate\" AND Admin_Tbl.relieving_date<=\"$endDate\")) and
        Admin_Tbl.join_date<=\"$endDate\"
        and Admin_Tbl.designation!=\"$ceo\" and Admin_Tbl.designation!=\"$svp1\" and Admin_Tbl.designation!=\"$svp2\" and
        users_tbl.user_type!=\"Admin\" ";
		$empSelectQuery .= " order by emp_id asc" ;
     }
             $records = get_query_result($empSelectQuery);

            if(rowsInResult($records)>0)
            {
            	
            	$objPHPExcel->setActiveSheetIndex(0);
            	 
				$objPHPExcel->getActiveSheet()->setCellValue('A1', "Emp ID");
				$objPHPExcel->getActiveSheet()->setCellValue('B1', "User Name");
				$objPHPExcel->getActiveSheet()->setCellValue('C1', "Location");
				$objPHPExcel->getActiveSheet()->setCellValue('D1', "Opening Balance As On ".formatDate($startDate));
				$objPHPExcel->getActiveSheet()->setCellValue('E1', "Credit During ".formatDate($startDate). " To " .formatDate($endDate));
                $objPHPExcel->getActiveSheet()->setCellValue('F1', "Utilized During ". formatDate($startDate). " To ". formatDate($endDate));
               	$objPHPExcel->getActiveSheet()->setCellValue('G1', "Closing Balance As On ".formatDate($endDate));
                
               	$cellcount=2;
                while($recordsRow = mysql_fetch_array($records))
                {
                    $uniqid = $recordsRow['uniqid'];
                    $employeeCurrentLocation = employee_location($database,$uniqid);
                    $filteredUsedid="";

                    if($_REQUEST['location']!="" && $_REQUEST['location']!="all")
                    {
                        $value =  CheckLocationOnDateWithGivenLocation($uniqid, $_REQUEST['location'], $startDate, $endDate);

                        if($value==1)
                        {
                           $filteredUsedid = $uniqid;
                        }

                    }
                    else
                    {
                        $filteredUsedid = $uniqid ;
                    }

                    if($filteredUsedid!="")
                    {
                        $location = $recordsRow['location'];                        
                        $leaveId = selectElLeaveId($yearCnt, $location);                         
                        $join_date = $recordsRow['join_date'];
                        $releivingDate = $recordsRow['relieving_date'];
                        $fname = str_replace('  ', ' ', ucwords(strtolower($recordsRow['fullname'])));

                        if($recordsRow['relieving_date']!="0000-00-00" && ReturnTimestamp($recordsRow['relieving_date'])>=ReturnTimestamp($startDate) &&  ReturnTimestamp($recordsRow['relieving_date'])<=ReturnTimestamp($endDate) && ($group=="Exits" || $group=="Present"))
                        {
                            $image = $cross;
                            $title ="Relieving Date: ".formatDate($releivingDate);
                        }
                        else if(ReturnTimestamp($recordsRow['join_date'])>=ReturnTimestamp($startDate) &&  ReturnTimestamp($recordsRow['join_date'])<=ReturnTimestamp($endDate) && ($group=="NewInductees" || $group=="Present"))
                        {

                            $image = $dot;
                            $title ="Joining Date: ".formatDate($join_date);
                        }
                        else
                        {

                            $image = $tick;
                            $title ="";
                        }
                        
                        $modifiedStartDate = returnDateWithinterval("Sub", $startDate, 1);
                		$modifiedStartYear = explode("-", $modifiedStartDate);
                		$modifiedYear = $modifiedStartYear[0];
                		$elBalanceOnStartDate = CalculateWokingDyasLeaveForMultipleYears($uniqid, "$modifiedYear-01-01", $modifiedStartDate);// - ElutilizedUptoSlecteddate($modifiedStartDate, "$modifiedYear-01-01", $modifiedYear, $uniqid, $modifiedLeaveId);
                		$elCreditedDuringthePeriod = CalculateWokingDyasLeaveWithoutPreBalance($uniqid, $yearCnt, $startDate, $endDate, $leaveId);
                		$elUtilizedDuringthePeriod = utilizedWorkingDaysLeave($leaveId, $startDate, $endDate, $yearCnt, $uniqid);
                		$elbalanceAtEndDate = $elBalanceOnStartDate + $elCreditedDuringthePeriod -$elUtilizedDuringthePeriod;

                        if($releivingDate!="0000-00-00" && strtotime($endDate)<=strtotime($releivingDate))
                        {
                            $printEl = $elbalanceAtEndDate;
                        }
                        else if($releivingDate=="0000-00-00" || $releivingDate==NULL)
                        {
                            $printEl = $elbalanceAtEndDate;
                        }
                         else
                        {
                            $printEl=0;
                        }
                        
                        $objPHPExcel->getActiveSheet()->setCellValue('A'.$cellcount, $recordsRow['emp_id']);
						$objPHPExcel->getActiveSheet()->setCellValue('B'.$cellcount, $fname);
						$objPHPExcel->getActiveSheet()->setCellValue('C'.$cellcount, ucwords(strtolower(printLocationName($database, $recordsRow['location']))));
						$objPHPExcel->getActiveSheet()->setCellValue('D'.$cellcount, $elBalanceOnStartDate);
						$objPHPExcel->getActiveSheet()->setCellValue('E'.$cellcount, $elCreditedDuringthePeriod);
                		$objPHPExcel->getActiveSheet()->setCellValue('F'.$cellcount, $elUtilizedDuringthePeriod);
               			$objPHPExcel->getActiveSheet()->setCellValue('G'.$cellcount, $printEl);
                        //print the result in the table format
                        $cellcount++;
                   }
                }
                $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
				$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
				$objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
				$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
				$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
				$objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);
				$objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
             }
             $objPHPExcel->getActiveSheet()->setAutoFilter('A1:G'.$cellcount);
             $objPHPExcel->setActiveSheetIndex(0);		
			 // 	Save Excel 2007 file			
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
			$objWriter->save($yearCnt.'.xlsx');
		}
$file = time()."_el.xlsx";
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename=$file");
header("Content-Transfer-Encoding: binary");		
$filenames = array('2005.xlsx','2006.xlsx', '2007.xlsx','2008.xlsx', '2009.xlsx');
$year=2005;
$bigExcel = new PHPExcel();
$bigExcel->removeSheetByIndex(0);
$reader = new PHPExcel_Reader_Excel2007();
foreach ($filenames as $filename) {
	
    $excel = $reader->load($filename);    
    foreach($excel->getAllSheets() as $sheet) {
    	$sheet->setTitle($year); // <--- rename sheet if necessary
    	$bigExcel->addExternalSheet($sheet);
    }    
    foreach ($excel->getNamedRanges() as $namedRange) {
        $bigExcel->addNamedRange($namedRange);
    }    
    $year++;   
}
$writer = PHPExcel_IOFactory::createWriter($bigExcel, 'Excel2007');
$writer->save("php://output");

 

I n this code if i don't use any header and use 

$writer->save($file);

then it creates the file I want. but during download it get corrupted

 

Developer
Sep 24, 2009 at 1:15 PM

>> In this code if i don't use any header and use $writer->save($file);then it creates the file I want. but during download it get corrupted

Try to open the files in a text editor, or perhaps a HEX editor. Compare them, they should be the same. Maybe you will notice some difference such as some white space at the beginning or end? For example, this can happen if you have spaces before/after the opening/closing <?php tags.

Sep 25, 2009 at 5:11 AM

Thank you.

The problem has been rectified. I have opened the file on textpad and got the problem

Some other functions were causing the warning message generation. Due to this the resultant file was getting corrupted.

Thanks again.