Phpexcel - Reading one excel sheet to write to another

[Nell]

Hello PhpFreaks!

I'm trying to use Phpexcel to extract data from one excel sheet to be able to write it to a template excel sheet. But I can't seem to get the entire thing working.

 

For example; taking the value of the cell "C4" in the excel Spreadsheet "Firstsheet.xls". And to write it to a new excel sheet, "Template.xls" in the cell "D2".
 

I'm currently trying to use this:

$objPHPExcel = PHPExcel_IOFactory::load("FirstSheet.xls");
$objPHPExcel2 = PHPExcel_IOFactory::load("Template.xls");

$objPHPExcel2->getActiveSheet()->setCellValue('D2',$objPHPExcel->getActiveSheet()->GetCell('C4')->getValue()); 

 

However, opening up two sources at the same time is seen as "bad coding"; a possible solution might be to either to open up the first excel sheet and store it to a multiple dimension array, close it. Then to open up the second sheet and access the data of the first sheet from the array.

But I'm not really sure how to do so.

--
 

 

This is my code so far, that needs fixing:

 

<?php

 

// includes PHPExcel library //

 require_once "Classes/PHPExcel/IOFactory.php";

 

// Loads Excel sheets //

$objTpl = PHPExcel_IOFactory::load("template.xls");

$objTpl2 = PHPExcel_IOFactory::load("template.xls");

$objTpl->setActiveSheetIndex();  //set first sheet as active

 

// Set data to the template cells //

$objTpl->getActiveSheet()->setCellValue('F3', date('Y-m-d'));  //set C1 to current date

//**Insert any other cells that need to be changed here**

 

// Prepares Download //

$filename='FinalResult.xls'; //Sets the Filename

header('Content-Type: application/vnd.ms-excel');

header('Content-Disposition: attachment;filename="'.$filename.'"');

header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objTpl, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)

 

 

// done.. exiting //

exit;

?>

 

 

 

Thanks,

Nell

Edited June 5, 2013 by Nell

[DavidAM]

However, opening up two sources at the same time is seen as "bad coding"; ...

Who ever told you that? First of all, you are not opening two sources you have one source and one target. Second of all, there are countless well written programs that open multiple files. If you need data from one file to put in another file, there is nothing wrong with having both open at the same time. It can be more efficient and less likely to run out of memory should the source file grow unexpectedly.

 

I've never used this library, but I see a couple of issues with the posted code.

1) Please use

tags (not quote tags) when posting code

2) You are opening the same filename for both excel objects

3) You are not passing a value to the setActiveSheetIndex() method. I would expect it to take an integer (or perhaps a string of the "tab name").

 

You didn't really tell us what the problem is. "not working" is not a valid problem statement. Tell us what it does or does not do that is should not or should do. Also turn on error reporting and tell us what error messages you get.

[Nell]

The program sends corrupted data to the download file. Something in my code is corrupting the sent data.

[DavidAM]

It may be generating errors. Turn on error reporting at the beginning of the script

error_reporting(E_ALL);
ini_set('display.errors', 1);

Then exit just before the first header. See if any errors or warnings are displayed.

 

Make sure there is no output, even whitespace, before the headers and the object writing. Anything that gets sent will become part of the stream and the file will be invalid. Use the View->Source feature of the browser to see if there is any hidden output.

[teynon]

Have you looked at the examples included with the library? You need to output that writer.

$objWriter->save("phpCOLONSLASHSLASHoutput");

 

 

Found a bug with this post too... The code "phpCOLONSLASHSLASHoutput" causes the line to be erased. (guessing you did put it there and it got wiped as well...)

 

I would suggest commenting out the header to see what the output is rather than exiting before the header. PHPExcel's writers are where most of the Excel conversion / actions take place.

Edited June 6, 2013 by teynon

[DavidAM]

I would suggest commenting out the header to see what the output is rather than exiting before the header. PHPExcel's writers are where most of the Excel conversion / actions take place.

Yeah, that would have been the next step. I suggested before the headers because the first two he has are going to force a file download which would make it more difficult to see the error messages.

[teynon]

You should autoload the main PHPExcel class as well. PHPExcel will set up an autoloader for the rest of the files.

require_once 'PHPExcel/PHPExcel.php';

[Nell]

I tried the following code, and exited before the first header -  it gives me a blank page (no output).
 

error_reporting(E_ALL);
ini_set('display.errors', 1);

--
Yes teynon, i'm using:
 

$objWriter->save("phpCOLONSLASHSLASHoutput");

But you say that i have to remove it because it wipes out the line?

--

I commented out the headers and the result is corrupted data printed to the web broswer: (I shortened the entire output)
 

ÐÏࡱá;þÿ	þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ Ààõÿ Ààõÿ Ààõÿ Ààõÿ Ààõÿ Ààõÿ Ààõÿ Àà ÀàÈàÈàÈà ÈàÈàÈàÈàÌàÌàÀàÈàÈàÈàÈàèàÈàèà¤ìà%ìà¥ìàèàèàèà¥ìà%ìà¦ÌàÈà§ìà¦Ìà§ìà§ìŠà§ìŠàè‹àè‹à	 Èà ÈàÈàØàÈàÈàÀàè‹àè‹àè‹àȉàÈàÈà	Ø“€ÿ’â8ÿÿÿîì᥶Êÿÿ}$	}ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ

--
And finally I added the :
 

require_once 'PHPExcel/PHPExcel.php';

And i'm getting the same corrupted data as i posted above.

--

This is my entire code:

// processing form submitted //
 if (!isset($_POST['btnSubmit'])) exit;

error_reporting(E_ALL);
ini_set('display.errors', 1);


//include PHPExcel library//
require_once "Classes/PHPExcel/IOFactory.php";
require_once "Classes/PHPExcel.php";


// Load Files //
$objTpl = PHPExcel_IOFactory::load("template.xlsx");
$objTpl->setActiveSheetIndex();  //set first sheet as active
$objTpl2 = PHPExcel_IOFactory::load("file.xls");

// Set the Cells with values //
$objTpl->getActiveSheet()->getStyle('C2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //C1 is right-justified

$objTpl->getActiveSheet()->setCellValue('F3', date('Y-m-d'));  //set C1 to current date
 
$objTpl->getActiveSheet()->getStyle('C4')->getAlignment()->setWrapText(true);  //set wrapped for some long text message
 
$objTpl->getActiveSheet()->getColumnDimension('C')->setWidth(40);  //set column C width

$objTpl->getActiveSheet()->getRowDimension('4')->setRowHeight(120);  //set row 4 height

$objTpl->getActiveSheet()->getStyle('A4:C4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP); //A4 until C4 is vertically top-aligned

$objTpl->getActiveSheet()->setCellValue('D3', $objTpl2->getActiveSheet(1)->GetCell('B1')->getValue());

$objTpl->getActiveSheet()->setCellValue('B1',$objTpl2->getActiveSheet()->GetCell('B1')->getValue()); 



// Prepare Download //
$filename='goodFile.xlsx'; //Sets the Filename
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');


 
$objWriter = PHPExcel_IOFactory::createWriter($objTpl, 'Excel5');
$objWriter->save("phpCOLONSLASHSLASHoutput");
 
exit; //done.. exiting!

Edited June 8, 2013 by Nell