read existing xls, insert values and write it to xls

Topics: User Forum
Feb 21, 2011 at 8:19 AM
Edited Feb 21, 2011 at 9:17 AM

Hello,

I want to insert data into an existing XLS layout. For that reason I want to read an existing xls file, insert some data and write it to a destination file.
This is working fine so far except for one thing:

Every field that is not empty should have a border around the field. So I have insert a conditional formatting formula which is working fine. But this conditional formatting is not transfered from source to destination file (at destination the formula is not existing any longer). Is it possible to copy conditional formatting?

Then I have tried to do the same with VBA (Visual Basic for Applications). At source file this is working fine but the VBA code is not transfered to destination too. Is it possible to copy VBA code?

It would be great to use all formatting from source file because the user can change the file without programming php code.

 

//This function copy an excel sheet from source to destination filename and insert a given
//array at a (optional) given sheet and start row into destination file
function ArrayAddToExcel($sourceFilename, $destinationFilename, $arr, $sheetIndex = 0, $startAtRowNum = 1) {
	/** Error reporting */
	error_reporting(E_ALL);

	date_default_timezone_set('Europe/Berlin');

	/** PHPExcel_IOFactory */
	require_once '../Classes/PHPExcel/IOFactory.php';

	if (!file_exists($sourceFilename)) {
		exit("Can't find " . $sourceFilename . ".\n");
	}

	echo date('H:i:s') . " Load from Excel file\n";
	if (!isset($GLOBALS["objPHPExcel"])) {
		$GLOBALS["objPHPExcel"] = PHPExcel_IOFactory::load($sourceFilename);
	}

	//Fetch row-names and write to target xls
	$csvTableColumns = "";
	$countcol = 0;
	foreach ($arr[0] as $key => $value) {
				$GLOBALS["objPHPExcel"]->setActiveSheetIndex($sheetIndex)
							->setCellValue(chr(65 + $countcol) . $startAtRowNum, $key);
				$countcol++;
	}

	$countrow = 0;
	//Fetch all values
	foreach ($arr as $key => $arrRow) {
		$countcol = 0;
		foreach ($arrRow as $value) {
			$GLOBALS["objPHPExcel"]->setActiveSheetIndex($sheetIndex)
						->setCellValue(chr(65 + $countcol) . strval(($startAtRowNum + 1) + $countrow), $value);
			$countcol++;
		}
		$countrow++;
	}
	
	echo date('H:i:s') . " Write to Excel format\n";
	$objWriter = PHPExcel_IOFactory::createWriter($GLOBALS["objPHPExcel"], 'Excel5');
	$objWriter->save($destinationFilename);

	// Echo memory peak usage
	echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";

	// Echo done
	echo date('H:i:s') . " Done reading file.\r\n";
}

 

Thanks,
Pete