Geek Answers Handbook

PHPEXCEL to store records in an array and display

Here is a snippet of my code that displays excel records and stores in an array.

First, the logic determines which record is invalid, marks it, then for another valid record, check if it is a duplicate, if there is one, mark it.

In the end, review the entire sheet again, extract all entries that are not on this list. (duplicate or invalid)

Problems:

1) When I show the table, although it may be displayed, it warns me that "datatables warning: request an unknown parameter '0' from the data source for row 0"

2) When I store in an array, it can only store the first row of a string

So, I would like to know if there is any kind of error in my loop logic? and if I used PHPEXCEL to read the spreadsheet correctly? Thanks.

$reader = PHPExcel_IOFactory::createReader($readerType); $PHPExcel = $reader->load($file); $sheet = $PHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); $pattern="/^[\w-]+(?:\.[\w-]+)*@(?:[\w-]+\.)+[a-zA-Z]{2,7}$/"; for ($row = 1; $row <= $highestRow; $row++){ for ($head = 0; $head < $highestColumn; $head++){ $testMail = $sheet->getCellByColumnAndRow($head, $row)->getValue(); if (preg_match($pattern,$testMail)) $mailColumn=$head; }} if(!isset($mailColumn)) {die('No email column detected, please check your file and import again.');} $invaild[] = NULL ; $email[] = NULL ; $duplicate[] = NULL ; for ($row = 1; $row <= $highestRow; $row++) { for ($y = 0; $y < $highestColumn; $y++) { $val = $sheet->getCellByColumnAndRow($y, $row)->getValue(); if ($y == $mailColumn && !preg_match($pattern,$val)) {$invaild[]=$row;} elseif ($y == $mailColumn && in_array($val,$email)) {$duplicate[]=$val; $duplicate[]=$row;} //elseif (!in_array($row,$duplicate) && !in_array($row,$invaild) ) //{echo $val;} if ($y == $mailColumn) {$email[]=$val; $email=array_unique($email);} } } $invaild=array_unique($invaild); foreach ($invaild as $c) {echo $c;} echo "<br>"; foreach ($duplicate as $d) {echo $d;} ?> <div id="stylized" class="view"> <h1><?echo $file.' Result';?></h1> <p>Import from spreadsheet files</p> <div id="container"> <table cellpadding="0" cellspacing="0" border="0" class="display" id="viewImport"> <thead> <tr> <? for ($head = 0; $head < $highestColumn; $head++){ if ($head==$mailColumn) echo "<th field='col'$head> Email address </th>"; else echo "<th field='col'$head> Unname coloum $head </th>"; } ?> </tr> </thead> <? for ($row = 1; $row <= $highestRow; $row++) { echo "<tr>"; for ($y = 0; $y < $highestColumn; $y++) { if (!in_array($row,$duplicate) && !in_array($row,$invaild)){ $val = $sheet->getCellByColumnAndRow($y, $row)->getValue(); echo "<td>"; if (!$val) echo "-"; else echo $val; echo "</td>";} } echo "</tr>"; } ?> </table> </div> I work on this these day but still get this display error thank you ![enter image description here][1]

enter image description here

+7
source share
1 answer

Here is my tested code that uses ur logic. I transferred the excel file. (test.xlsx)

 <?php /** PHPExcel */ include_once('PHPExcel.php'); /** PHPExcel_Writer_Excel2007 */ include_once('PHPExcel/Writer/Excel2007.php'); include_once('PHPExcel/Reader/Excel2007.php'); //$objPHPExcel = new PHPExcel(); $file = 'test.xlsx'; $readerType = 'Excel2007'; $reader = PHPExcel_IOFactory::createReader($readerType); $PHPExcel = $reader->load($file); $sheet = $PHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); $pattern="/^[\w-]+(?:\.[\w-]+)*@(?:[\w-]+\.)+[a-zA-Z]{2,7}$/"; for ($row = 1; $row <= $highestRow; $row++){ for ($head = 0; $head < $highestColumn; $head++){ $testMail = $sheet->getCellByColumnAndRow($head, $row)->getValue(); if (preg_match($pattern,$testMail)) $mailColumn=$head; }} if(!isset($mailColumn)) {die('No email column detected, please check your file and import again.');} $invaild[] = NULL ; $email[] = NULL ; $duplicate[] = NULL ; for ($row = 1; $row <= $highestRow; $row++) { for ($y = 0; $y < $highestColumn; $y++) { $val = $sheet->getCellByColumnAndRow($y, $row)->getValue(); if ($y == $mailColumn && !preg_match($pattern,$val)) {$invaild[]=$row;} elseif ($y == $mailColumn && in_array($val,$email)) {$duplicate[]=$val; $duplicate[]=$row;} //elseif (!in_array($row,$duplicate) && !in_array($row,$invaild) ) //{echo $val;} if ($y == $mailColumn) {$email[]=$val; $email=array_unique($email);} } } $invaild=array_unique($invaild); /*foreach ($invaild as $c) {echo $c;} echo "<br>"; foreach ($duplicate as $d) {echo $d;}*/ ?> <div id="stylized" class="view"> <h1><?php echo $file.' Result';?></h1> <p>Import from spreadsheet files</p> <div id="container"> <table cellpadding="0" cellspacing="0" border="0" class="display" id="viewImport"> <thead> <tr> <?php for ($head = 0; $head < $highestColumn; $head++){ if ($head==$mailColumn) echo "<th field='col' $head> Email address </th>"; else echo "<th field='col' $head> Unname coloum $head </th>"; } ?> </tr> </thead> <?php for ($row = 1; $row <= $highestRow; $row++) { //Here I have added condition if (!in_array($row,$duplicate) && !in_array($row,$invaild)){ echo "<tr>"; } for ($y = 0; $y < $highestColumn; $y++) { if (!in_array($row,$duplicate) && !in_array($row,$invaild)){ $val = $sheet->getCellByColumnAndRow($y, $row)->getValue(); echo "<td>"; if (!$val) echo "-"; else echo $val; echo "</td>";} } //Here I have added condition if (!in_array($row,$duplicate) && !in_array($row,$invaild)){ echo "</tr>"; } } ?> </table> </div>

Here is the source of the output.

 <div id="stylized" class="view"> <h1>test.xlsx Result</h1> <p>Import from spreadsheet files</p> <div id="container"> <table cellpadding="0" cellspacing="0" border="0" class="display" id="viewImport"> <thead> <tr> <th field='col' 0> Unname coloum 0 </th><th field='col' 1> Unname coloum 1 </th><th field='col' 2> Unname coloum 2 </th><th field='col' 3> Unname coloum 3 </th><th field='col' 4> Unname coloum 4 </th><th field='col' 5> Unname coloum 5 </th><th field='col' 6> Email address </th> </tr> </thead> <tr><td>1</td><td>REP_10072011_1</td><td>Project </td><td>N/A</td><td>Me</td><td>TEST1</td><td> kamal.joshi@mail.com </td></tr><tr><td>2</td><td>TEST</td><td>Test</td><td>Te</td><td>TEE</td><td>TEST</td><td> joshi.kamal@mail.com </td></tr> </table> </div>

Well, I tried jQuery plugin for dataTable and it works fine. Take a look at the link http://datatables.net/forums/discussion/1283/warning-unexpected-number-of-td-elements./p1

Well I have attached image of my output ..

My suggestion: go through your integration with jQuery data plugins. Make sure you include the required jquery in the excel sheet output page with the appropriate class or id selector, e.g.

 $(document).ready(function() { $('#viewImport').dataTable(); } );

Edit: check this out ...

 <?php /** PHPExcel */ include_once('PHPExcel.php'); /** PHPExcel_Writer_Excel2007 */ include_once('PHPExcel/Writer/Excel2007.php'); include_once('PHPExcel/Reader/Excel2007.php'); //$objPHPExcel = new PHPExcel(); $file = 'test.xlsx'; $readerType = 'Excel2007'; $reader = PHPExcel_IOFactory::createReader($readerType); $PHPExcel = $reader->load($file); $sheet = $PHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); $pattern="/^[\w-]+(?:\.[\w-]+)*@(?:[\w-]+\.)+[a-zA-Z]{2,7}$/"; for ($row = 1; $row <= $highestRow; $row++){ for ($head = 0; $head < $highestColumn; $head++){ $testMail = $sheet->getCellByColumnAndRow($head, $row)->getValue(); if (preg_match($pattern,$testMail)) $mailColumn=$head; }} if(!isset($mailColumn)) {die('No email column detected, please check your file and import again.');} $invaild[] = NULL ; $email[] = NULL ; $duplicate[] = NULL ; for ($row = 1; $row <= $highestRow; $row++) { for ($y = 0; $y < $highestColumn; $y++) { $val = $sheet->getCellByColumnAndRow($y, $row)->getValue(); if ($y == $mailColumn && !preg_match($pattern,$val)) {$invaild[]=$row;} elseif ($y == $mailColumn && in_array($val,$email)) {$duplicate[]=$val; $duplicate[]=$row;} //elseif (!in_array($row,$duplicate) && !in_array($row,$invaild) ) //{echo $val;} if ($y == $mailColumn) {$email[]=$val; $email=array_unique($email);} } } $invaild=array_unique($invaild); /*foreach ($invaild as $c) {echo $c;} echo "<br>"; foreach ($duplicate as $d) {echo $d;}*/ ?> <script type="text/javascript" src="jquery.js"></script> <script type="text/javascript" src="jquery.dataTables.js"></script> <script type="text/javascript"> $(document).ready(function() { $('#viewImport').dataTable(); } ); </script> <div id="stylized" class="view"> <h1><?php echo $file.' Result';?></h1> <p>Import from spreadsheet files</p> <div id="container"> <table cellpadding="0" cellspacing="0" border="0" class="display" id="viewImport"> <thead> <tr> <?php for ($head = 0; $head < $highestColumn; $head++){ if ($head==$mailColumn) echo "<th field='col' $head> Email address </th>"; else echo "<th field='col' $head> Unname coloum $head </th>"; } ?> </tr> </thead> <?php for ($row = 1; $row <= $highestRow; $row++) { if (!in_array($row,$duplicate) && !in_array($row,$invaild)){ echo "<tr>"; } for ($y = 0; $y < $highestColumn; $y++) { if (!in_array($row,$duplicate) && !in_array($row,$invaild)){ $val = $sheet->getCellByColumnAndRow($y, $row)->getValue(); echo "<td>"; if (!$val) echo "-"; else echo $val; echo "</td>";} } if (!in_array($row,$duplicate) && !in_array($row,$invaild)){ echo "</tr>"; } } ?> </table> </div>
+2
source

More articles:


All Articles