Page 1 of 1

compare excel sheets and copy matching records into another

Posted: Tue May 27, 2008 1:35 pm
by navjyotiu
Hi ppl

i want to compare excel sheets on the basis of first column and write common records to another file...earlier i was uploading two excel files and reading first column of both files in arrays and comparing and writing common items in one file and unmatched column value in another file.i was using reader and writer classes for it.now problem is i need to write complete record into file corresponding to matched column value.
Please help, following is the code i was using::

Code: Select all

<? 
//application varables
$GLOBALS ['export_file1'] = "xlsfile://Program Files/EasyPHP1-8/www/PHP-Login/uploads/abc.xls";
$GLOBALS ['commonItemListA'] = "files\commonItemListA.xls";
$GLOBALS ['ItemonListAonly'] = "files\ItemonListAonly.xls";
$GLOBALS ['commonItemListB'] = "files\commonItemListB.xls";
$GLOBALS ['ItemonListBonly'] = "files\ItemonListBonly.xls";
$GLOBALS ['MsgZeroItemMatched'] = "zero items Matched";
$GLOBALS ['MsgAllListBItemonListA'] = "All the items of List B are on List A, ";
 
$uploadFile = $_POST['uploadFile'];
// start for loop
$i = 0;
for($x=1;$x<=$uploadFile;$x++){
 
//?heck that we have a file
if((!empty( $_FILES['uploaded_file'. $x])) && ( $_FILES['uploaded_file'. $x]['error'] == 0)) {
 $filename = basename( $_FILES['uploaded_file'. $x]['name']);
     $ext = substr($filename, strrpos($filename, '.') + 1);
  //Check if the file is xls
   if (($ext == "xls") && ($_FILES['uploaded_file'. $x]["type"] == "application/vnd.ms-excel")) {
    //Determine the path to which we want to save this file
      $newname = dirname(__FILE__).'\\uploads\\'.$filename;
      //Check if the file with the same name is already exists on the server
      if (!file_exists($newname)) {
        //Attempt to move the uploaded file to it's new place
        if ((move_uploaded_file($_FILES['uploaded_file'. $x]['tmp_name'],$newname))) {
           echo "File Upload Successsful! File saved on Location:: ".$newname."<br>";
        } else {
           echo "Error: A problem occurred during file upload!<br>";
        }
      } else {
         echo "Error: File ".$_FILES['uploaded_file'. $x]["name"]." already exists<br>";
      }
  } else {
     echo "Error: Only .xls files are accepted for upload<br>";
          }
} else {
     echo "Error: No file uploaded<br>";
        }
        if ($i == 0)
    $GLOBALS ['listA'] = $newname;
    else
    $GLOBALS ['listB'] = $newname;
        $i++;
} // end of loop
 
//Connect to mysql server
    $link=mysql_connect("localhost","root","")or die('Failed to connect to server: ' . mysql_error());
//Select database
    $db=mysql_select_db("temp") or die("Unable to select database");
    
    
     if ( isset($_POST["TestName"]))
        {
         $tName=$_POST["TestName"];
        } else $tName="";
        
   $date_now = date('Y-m-d G:i:s');
   
   $UFileId = md5($date_now);
      
   if ( isset($_POST["Desc"]))
      {
       $desc=$_POST["Desc"];
      }else $desc="";
      
    
    //Create query
    $qry=("INSERT INTO `ListInfo` (`ListA`,`ListB`,`TestName`,`DateTime`,`UFileId`,`Desc`) 
    VALUES('$listA','$listB','$tName', '$date_now', '$UFileId ', '$desc') ")or die(mysql_error());   
        mysql_query($qry);
        mysql_close();
    
    //Read excel files data
    
    require_once 'Excel/reader.php'; 
$data = new Spreadsheet_Excel_Reader(); 
$data->setOutputEncoding('CP1251'); 
$data->read("$listA"); 
$data1 = new Spreadsheet_Excel_Reader(); 
$data1->setOutputEncoding('CP1251'); 
$data1->read("$listB");
$page = 0; 
$GLOBALS ['cellCounter'] =1;
error_reporting(E_ALL ^ E_NOTICE);
 
//getting data in Ist column of List A in a string
for ($j = 1; $j <= $data->sheets[$page]['numCols']; $j++)
  { 
   for ($i =2; $i <= $data->sheets[$page]['numRows']; $i++)
 
   {   
     if($data->sheets[0]['cells'][$i][$j] != NULL) 
     {    
      $GLOBALS ['str']= $data->sheets[$page]['cells'][$i][$j];  
     if($j==1){
    $datastring= $datastring.','.$str ; 
        }
     $len= strlen($datastring);
     $GLOBALS ['newstring'] = substr($datastring, 1,$len);
    }
   }
 }
     $dataList = explode(',',$newstring); //getting data in array from string
     
 //getting sise of List A
    $listASize = count($dataList);
    print "Size of List A is $listASize<br>";
    
//getting data in Ist column of List B in a string  
for ($j = 1; $j <= $data1->sheets[$page]['numCols']; $j++)
  { 
   for ($i = 2; $i <= $data1->sheets[$page]['numRows']; $i++)
 
   {   
     if($data1->sheets[0]['cells'][$i][$j] != NULL) 
     {    
      $GLOBALS ['str']= $data1->sheets[$page]['cells'][$i][$j]; 
     if($j==1){
    $datastring1= $datastring1.','.$str ; 
        }
     $len= strlen($datastring1);
     $GLOBALS ['newstring1'] = substr($datastring1, 1,$len);
    }
   }   
  }
   $dataList1 = explode(',',$newstring1); //getting data in array from string
    //getting sise of List B
    $listBSize = count($dataList1);
    print "Size of List B is $listBSize<br>";
    
    //comparing list A with list B
    $GLOBALS ['countA'] = 0;
    foreach($dataList as $valueA)
    {
    foreach($dataList1 as $valueB)
      {
         $x = strcmp($valueA , $valueB );
        if($x == 0)
        {
         $countA++;
         $GLOBALS ['matchStr']= $valueA;
          $matchedString= $matchedString.','.$matchStr ; 
           $len= strlen($matchedString);
            $GLOBALS ['stringmatched'] = substr($matchedString, 1,$len);
        }
      }
    }
    
//getting matched items in array
 $matchedlist = explode(',',$stringmatched);
 $matchedlist1 = array_values(array_unique($matchedlist));
 $commonItemListAarray = array();
for ($i=0; $i<=count($matchedlist1); $i++)
{
$commonItemListAarray[$i]['Result'] = $matchedlist1[$i];
}
 
 $countmatchedlist = count($matchedlist1);
 
//calculating percentage
$percent = ($countmatchedlist / $listASize) * 100;
$percent = round($percent, 1);
echo "$percent percent of list A is on list B<br>";
echo "There are $countmatchedlist item of list A on list B<br>";
 
//generating message when Zero item matched between List A and List B
$msgZeroMatched = explode(',',$MsgZeroItemMatched);
$msgZeroMatchedarray = array();
for ($i=0; $i<=count($msgZeroMatched); $i++)
{
$msgZeroMatchedarray[$i]['Result'] = $msgZeroMatched[$i];
}
$GLOBALS ['count'] = 0;
foreach($dataList as $valueA)
    {
     if(!in_array($valueA, $matchedlist))
     {
     $count++;
     $GLOBALS ['itemNotInArrayA']= $valueA;
         $foundString= $foundString.','.$itemNotInArrayA ; 
            $len= strlen($foundString);
            $GLOBALS ['stringNotOnListB'] = substr($foundString, 1,$len);
     }
    }
//getting items not present in list B array
$array_item_Not_OnListB = explode(',',$stringNotOnListB); //getting data in array from string
$ItemonListAonlyarray = array();
for ($i=0; $i<=count($array_item_Not_OnListB); $i++)
{
$ItemonListAonlyarray[$i]['Result'] = $array_item_Not_OnListB[$i];
}
//comparing list B with list A
    //getting array of common items
    
foreach( $dataList1 as $valueB)
    {
    foreach($dataList as $valueA)
      {
         $x = strcmp($valueB , $valueA);
        if($x == 0)
        {
         $GLOBALS ['matchStrA']= $valueA;
          $matchedStringA= $matchedStringA.','.$matchStrA ; 
           $len= strlen($matchedStringA);
            $GLOBALS ['stringmatchedA'] = substr($matchedStringA, 1,$len);
        }
      }
    }
    
//getting matched items in array
 $matchedlistA = explode(',',$stringmatchedA);
 $matchedlist2 = array_values(array_unique($matchedlistA));
 $countmatchedlistA = count($matchedlist2);
 $commonItemListBarray = array();
for ($i=0; $i<=count($matchedlist2); $i++)
{
$commonItemListBarray[$i]['Result'] = $matchedlist2[$i];
}
 
$GLOBALS ['countB'] = 0;
    foreach($dataList1 as $valueB)
    {
    foreach($dataList as $valueA)
      {
         $x = strcmp($valueB , $valueA );
        if($x == 0)
        {
         $countB++;
         $GLOBALS ['matchStrB']= $valueA;
         $matchedStringB= $matchedStringB.','.$matchStrB ; 
           $len= strlen($matchedStringB);
            $GLOBALS ['stringmatchedB'] = substr($matchedStringB, 1,$len);
        }
      }
    }
    
//getting matched items of List B in array
 $matchedlistB = explode(',',$stringmatchedB);
 $matchedlistB = array_unique($matchedlistB);
 $countmatchedlistB = count($matchedlistB);
 
 //getting items not  matched in array
 
//calculating percentage
$percent = ($countmatchedlistB / $listBSize) * 100;
$percent = round($percent, 1);
echo "$percent percent of list B is on list A<br>";
  
if($countmatchedlistB){
echo "There are $countmatchedlistB item of list B on list A<br>";
$GLOBALS ['countingA'] = 0;
foreach($dataList1 as $valueB)
    {
     if(!in_array($valueB, $matchedlistB))
     {
     $countingA++;
     $GLOBALS ['itemNotInArrayB']= $valueB;
      $foundStringB= $foundStringB.','.$itemNotInArrayB ; 
         $len= strlen($foundStringB);
            $GLOBALS ['stringNotOnListA'] = substr($foundStringB, 1,$len);
      }
    }
    }else echo"zero items of list B are on list A<br>";
//generating message when All List A Item are on List B
$MsgAllListAItemonListB = "All the items of List A are on List B, ";
$msgA = explode(',',$MsgAllListAItemonListB);
$msgAarray = array();
for ($i=0; $i<=count($msgA); $i++)
{
$msgAarray[$i]['Result'] = $msgA[$i];
}
//$a=1;
if($count){
generateReport($ItemonListAonlyarray,$ItemonListAonly,$export_file1);
  }else 
  generateReport($msgAarray,$ItemonListAonly,$export_file1);    
//getting items not present in list A array
$array_item_Not_OnListA = explode(',',$stringNotOnListA);
$ItemonListBonlyarray = array();
for ($i=0; $i<=count($array_item_Not_OnListA); $i++)
{
$ItemonListBonlyarray[$i]['Result'] =$array_item_Not_OnListA[$i];
}
//generating report for items on both lists when List A is compared to List B
if($countmatchedlist){
generateReport($commonItemListAarray,$commonItemListA,$export_file1);
}else 
generateReport($msgZeroMatchedarray,$commonItemListA,$export_file1);
 
//generating report for items on both lists when List B is compared to List A
if($countmatchedlistA){
generateReport($commonItemListBarray,$commonItemListB,$export_file1);
}else 
generateReport($msgZeroMatchedarray,$commonItemListB,$export_file1);
//generating message when All List B Item are on ListA
$msgB = explode(',',$MsgAllListBItemonListA);
$msgBarray = array();
for ($i=0; $i<=count($msgB); $i++)
{
$msgBarray[$i]['Result'] = $msgB[$i];
}
//Generating report for items not present in list A array
if($countingA){
generateReport($ItemonListBonlyarray,$ItemonListBonly,$export_file1);
  }else 
   generateReport($msgBarray,$ItemonListBonly,$export_file1);
      
//write in temp file
    
function generateReport($array,$reportname,$export_file1)
{ 
  static $filecounter = 0;
 
 
require_once "excel1.php";
$export_file = $export_file1;
$copy_file = $reportname;
 
$fp = fopen($export_file, "wb");
if (!is_resource($fp))
{
    die("Cannot open $export_file");
}
 
fwrite($fp, serialize($array));
fclose($fp);
 
$file_old = $export_file;
$file_new = $copy_file;
 
    if(!copy($file_old, $file_new))
    {
    echo ("Rename failed<br>");
    }
switch ($filecounter) {
case 0:
    $text = "List of common Items in List A";
    break;
case 1:
    $text = "List of Items only on List A";
    break;
case 2:
    $text = "List of common Items in List B";
    break;
case 3:
    $text = "List of Items only on List B";
    break;
}
    print '<html>';
    print '<body>';
    print '<a href="';
    echo $file_new;
    print '">';
    echo $text; 
    print '</a>';
    echo "<br>";
    print '</body>';
        print '</html>';
         $filecounter++;
 
}
 
            
?>

Scottayy| Please use tags when posting PHP code in the forums.[/color]