0

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');
?>
2
Contributors
1
Reply
4
Views
5 Years
Discussion Span
Last Post by LastMitch
0

@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) { }

Edited by LastMitch: grammer

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.