Been trying to loop through worksheets in an excel document. And remove "," in columns. Then save result in same excel document.

<?php
include'../Classes/PHPExcel.php';
include'../Classes/PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(false);
//$xlsxfiles=$_SESSION['file'];
//echo $xlsxfiles;
$objPHPExcel = $objReader->load('../upload/Test_0.xlsx');
$num=$objPHPExcel->getSheetCount() ;
$sheetnames=$objPHPExcel->getSheetNames() ;
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$sheetnum=$num-1;

for($sl=0;$sl<$sheetnum;$sl++) {
for($cl=0;$cl<$highestRow;$cl++){
$clean= $objPHPExcel->setActiveSheetIndex($sl)
           ->getCell('C'.$cl);
$cleandone=str_replace(',',' ',$clean);
$objWorksheet->setCellValue('C'.$cl,$cleandone);

$clcolvalD=$objWorksheet->getCell('D'.$cl);
$clcolvalD=str_replace(',',' ',$clcolvalD);
$objWorksheet->setCellValue('D'.$cl,$clcolvalD);

$clcolvalE=$objWorksheet->getCell('E'.$cl);
$clcolvalE=str_replace(',',' ',$clcolvalE);
$objWorksheet->setCellValue('E'.$cl,$clcolvalE);

$clcolvalF=$objWorksheet->getCell('F'.$cl);
$clcolvalF=str_replace(',',' ',$clcolvalF);
$objWorksheet->setCellValue('F'.$cl,$clcolvalF);

$clcolvalG=$objWorksheet->getCell('G'.$cl);
$clcolvalG=str_replace(',',' ',$clcolvalG);
$objWorksheet->setCellValue('G'.$cl,$clcolvalG);

$clcolvalH=$objWorksheet->getCell('H'.$cl);
$clcolvalH=str_replace(',',' ',$clcolvalH);
$objWorksheet->setCellValue('H'.$cl,$clcolvalH);

$clcolvalI=$objWorksheet->getCell('I'.$cl);
$clcolvalI=str_replace(',',' ',$clcolvalI);
$objWorksheet->setCellValue('I'.$cl,$clcolvalI);

$clcolvalJ=$objWorksheet->getCell('J'.$cl);
$clcolvalJ=str_replace(',',' ',$clcolvalJ);
$objWorksheet->setCellValue('J'.$cl,$clcolvalJ);

$clcolvalK=$objWorksheet->getCell('K'.$cl);
$clcolvalK=str_replace(',',' ',$clcolvalK);
$objWorksheet->setCellValue('K'.$cl,$clcolvalK);

$clcolvalL=$objWorksheet->getCell('L'.$cl);
$clcolvalL=str_replace(',',' ',$clcolvalL);
$objWorksheet->setCellValue('L'.$cl,$clcolvalL);

$clcolvalM=$objWorksheet->getCell('M'.$cl);
$clcolvalM=str_replace(',',' ',$clcolvalM);
$objWorksheet->setCellValue('M'.$cl,$clcolvalM);

$clcolvalN=$objWorksheet->getCell('N'.$cl);
$clcolvalN=str_replace(',',' ',$clcolvalN);
$objWorksheet->setCellValue('N'.$cl,$clcolvalN);
 }

}
 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('../upload/Test_0.xlsx');
?>
Member Avatar

@tapuwa2002

Been trying to loop through worksheets in an excel document. And remove "," in columns. Then save result in same excel document.

You know it be nice if you just stay on 1 thread so I can answer a couple of question that you ask.

Technically you don't need this:

$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$sheetnum=$num-1;

This is very confusing:

for($sl=0;$sl<$sheetnum;$sl++) {

You want a loop through different work sheets

in order to do that you need a foreach() function.

You need to get the name of the sheet:

$sheetnames = $objPHPExcel->getSheetByName('Sheet1');

This will check if the sheet exists:

$i=0;

$sheetnames = $objPHPExcel->getSheet(0);

This will get all of the sheets:

$sheetnames = $objPHPExcel->getAllSheets()

This how you set a cell:

$objWorkSheet->setCellValue('A1', 'tapuwa'.$i)->setCellValue('B2', '2002')

Now for loop:

foreach ($objPHPExcel->getWorksheetIterator() as $sheetnames) { }