Here is the extract of my code that display the excel entries and stores into array .
The logic is first, detect which entry is invalid , mark it down, then for other valid entry, check whether it is duplicate, if it is, mark it down.
Eventually , scan through the whole sheet again, retrieve all the entries that is not in that two list. (duplicate or invalid)
The problems of it are:
1) When i display the table, although it can display but it warns me that " datatables warning:Request unknown parameter '0' from the data source for row 0"
2) When i store into array, it can only store for the first line of row
So , i would like to know are there any mistake in my looping logic? and had i used the PHPEXCEL to read spreadsheet in a correct way? Thank you.
$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]
Here is my tried code that uses ur logic.. I have passed 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 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>[email protected]</td></tr><tr><td>2</td><td>TEST</td><td>Test</td><td>Te</td><td>TEE</td><td>TEST</td><td>[email protected]</td></tr>
</table>
</div>
Well I have tried dataTable jQuery Plugin and its working perfectly.. Have a look at the link http://datatables.net/forums/discussion/1283/warning-unexpected-number-of-td-elements./p1
My suggestion : please go thru your jquery dataTables plugin integration. Make sure that you are including required jquery in your output page of excel sheet with matching class or id selector like
$(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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With