php excell multiple files

[Paulqvz]

Good day all.

I am looking for guidence.

I am using phpexcell to format an excell file. all working 100% What should i be looking to do when i want to edit and format multiple files in a directory. about 200 excell documents all the same and with same formatting?

Found this code for reading, but lets say row c3:i3 i want bold and size 14 in all the excell files

.
.
.

$path = "..."; ///the folder path
		$theFilePath = "";		
		
		$theFileName = glob ($path . "*.xls");
		
		
		//Read more than one file here
		for($j = 0; $j< count($theFileName); $j++) {

		$theFilePath = $theFileName[$j];
		
		$excel = new Spreadsheet_Excel_Reader();
		$excel->setOutputEncoding('CP1251');
		$excel->setUTFEncoder('mb_convert_encoding');
		error_reporting(E_ALL ^ E_NOTICE);			
			
			//echo $theFileName[$j];; 
			
			echo "<br />";
			echo $theFilePath; 
			echo "<br />";
			 

			
			$excel->read($theFilePath);

                          .
                          .
                          .// Do what u want display here

                       }

 

[requinix]

Can you write code that will take one file and perform the adjustments you want? What is that code?

[Paulqvz]

hi yes - here is the code.

 

<?php
error_reporting(E_ALL);


date_default_timezone_set('Europe/London');
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');

include 'Classes/PHPExcel/IOFactory.php';


require_once('Classes/PHPExcel.php');

$phpExcel = PHPExcel_IOFactory::load('/var/www/html/default/csvex/xls/aaa.xls');


function cellColor($cells,$color){
    global $objPHPExcel;

    $objPHPExcel->getActiveSheet()->getStyle($cells)->getFill()->applyFromArray(array(
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'startcolor' => array(
             'rgb' => $color
        )
    ));
}

cellColor('B5', 'F28A8C');
cellColor('G5', 'F28A8C');
cellColor('A7:I7', 'F28A8C');
cellColor('A17:I17', 'F28A8C');
cellColor('A30:Z30', 'F28A8C');
$writer->save('baaa.xls');

 

[requinix]

Please post the code you actually have that is currently working.

[Psycho]

1. Create a function that takes the excel file as an input and performs all the operations you want to do to the excel files

2. Use a process to loop through all the excel files: scandir(), readdir(), . . . ???

3. Upon each iteration of the loop call the function in step #1 passing the current file as a parameter

Edited June 17, 2021 by Psycho

[Paulqvz]

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
set_time_limit(0);


$directory = "/var/www/html/default/csvex/xls/";
$formatdirectory = "/var/www/html/default/csvex/toformat/";
$finishedformatdirectory = "/var/www/html/default/csvex/formatted/";

$files = scandir($directory, SCANDIR_SORT_DESCENDING);
$newest_file = $files[0];
echo $newest_file;
echo "<br>";
$oldfile = $directory.$newest_file;
$newfile = $formatdirectory.$newest_file;
$changedfile = $finishedformatdirectory.$newest_file;
echo $oldfile;
echo "<br>";
echo $newfile;

echo "<br>";
if( !rename($oldfile, $newfile) ) {  
    echo "File can't be moved!";  
}  
else {  
    echo "File has been moved!";  
} 
chmod($newfile, 755);  
//format the file


date_default_timezone_set('Europe/London');
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');

include 'PHPExcel/IOFactory.php';

$fileType = 'Excel5';
$fileName = $newfile;

// Read the file
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objPHPExcel = $objReader->load($fileName);


$objPHPExcel->getActiveSheet()->getStyle('d1:d9')->applyFromArray(
	array('fill' 	=> array(
								'type'		=> PHPExcel_Style_Fill::FILL_SOLID,
								'color'		=> array('argb' => 'ffffffff')
							),
		 )
	);

// Change the file
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('d1', 'booya')
            ->setCellValue('d9', 'ddddddddddddddddddd!');

// Write the file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $fileType);
$objWriter->save($fileName);

?>