Thursday, November 1, 2012

PHPExcel Script/CakePHP

 I have some code that reads a Excel workbook and drops data from the workbook into my database. The thing is I am having some trouble implementing my code into my website. I am using CakePHP 1.3 in my website, and the code I used to write my script for reading my workbooks is wrote in PHP 5. Is there any way someone could look at my code and give me some pointers as to how I should go about implementing this code into my site. I don't know how because I am fairly new to Cake. Any input would be greatly appreciated! Thanks in advance! Here is my code....

<?php

$hostName = "localhost";
$dbName = "import";
$dbUser = "root";
$dbPass = "";

define("HOST_NAME", $hostName);
define("DB_NAME", $dbName);
define("DB_USER", $dbUser);
define("DB_PASS", $dbPass);


$db = mysql_connect (HOST_NAME, DB_USER, DB_PASS);
mysql_select_db (DB_NAME, $db);

function pr($array) {
   echo "<xmp>";
   if (!isset($array)) echo "pr(): value not set";
   elseif (is_object($array))  print_r($array);
   elseif (!is_array($array)) echo "pr() string: $array";
   elseif (!count($array)) echo "pr(): array empty";
   else print_r($array);
   echo "</xmp>";
}

function date2Unix($excelDate) {
    return ($excelDate - 25569) * 86400;
}

$arrColumns = array(0=>'A',1=>'B',2=>'C',3=>'D',4=>'E',5=>'F',6=>'G',7=>'H',8=>'I',9=>'J',10=>'K',11=>'L',12=>'M',13=>'N',14=>'O',15=>'P',16=>'Q',17=>'R',18=>'S',19=>'T',20=>'U',21=>'V',22=>'W',23=>'X',24=>'Y',25=>'Z',26=>'AA',27=>'AB',28=>'AC',29=>'AD',30=>'AE',31=>'AF',32=>'AG',33=>'AH',34=>'AI',35=>'AJ',36=>'AK',37=>'AL',38=>'AM',39=>'AN',40=>'AO',41=>'AP',42=>'AQ',43=>'AR',44=>'AS',45=>'AT',46=>'AU',47=>'AV',48=>'AW',49=>'AX',50=>'AY',51=>'AZ',52=>'BA',53=>'BB',54=>'BC',55=>'BD',56=>'BE',57=>'BF',58=>'BG',59=>'BH',60=>'BI',61=>'BJ',62=>'BK',63=>'BL',64=>'BM',65=>'BN',66=>'BO',67=>'BP',68=>'BQ',69=>'BR',70=>'BS',71=>'BT',72=>'BU',73=>'BV',74=>'BW',75=>'BX',76=>'BY',77=>'BZ');

include 'C:\wamp\www\PHPExcel\Classes\PHPExcel.php';
error_reporting(E_ALL & ~E_NOTICE);

include_once 'C:\wamp\www\PHPExcel\Classes\PHPExcel\IOFactory.php';

date_default_timezone_set('America/Chicago');


$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

$locale = 'en_us';
$validLocale = PHPExcel_Settings::setLocale($locale);
if (!$validLocale) {
    die ("No valid locale ".$locale);
}

$step = $_GET['step'] ? $_GET['step'] : 1;

switch($step) {
    case 1:
        header('Content-Type: text/html; charset=utf-8');
        $html = '<form action="'.$_SERVER['PHP_SELF'].'?step=2" method="POST" enctype="multipart/form-data">';
        $html.= '<p>Select file: <input type="file" name="file" id="file"></p>';
        $html.= '<p><input id="submit" type="submit" value=" Save to DB "/></form></p>';
        
        echo $html;
    break;
    
    case 2:
    
        if ($_FILES['file']['error'] == 0) {
            $fileName = basename(str_replace("\\", "/", $_FILES['file']['name']));
            move_uploaded_file($_FILES['file']['tmp_name'], $fileName);
        }
        else
            exit('<h2>Error while sending file!</h2>');

$objPHPExcel = PHPExcel_IOFactory::load($fileName);
$objPHPExcel->setActiveSheetIndex(0);

$sheet1 = $objPHPExcel->getActiveSheet();

$countRows = $sheet1->getHighestRow();
$highestColumn = $sheet1->getHighestColumn();
$countCols = PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($i = 2; $i <= $countRows; $i++) {
    for ($y=0; $y<$countCols; $y++) {
        if ($sheet1->getCell($arrColumns[$y].$i)->getValue())
            $cells[$i][$y] = $sheet1->getCell($arrColumns[$y].$i)->getValue();
    }
}

echo '<h2>Saving the WorkSheet: Coverage</h2>';

echo '<p>Total rows: <strong>'.count($cells).'</strong></p>';
$cnt = 0;
foreach ($cells as $index=>$row) {
    $query = "INSERT INTO coverage (name, premium, effectiveDate, expirationDate, brokerFirstName, brokerMiddle, brokerLastName, brokerSuffix, brokerLicense, comments, created)
                VALUES (
                    '".str_replace("'", "`", $row[0])."',
                    '".str_replace("'", "`", $row[1])."',
                    '".date2Unix($row[2])."',
                    '".date2Unix($row[3])."',
                    '".str_replace("'", "`", $row[4])."',
                    '".str_replace("'", "`", $row[5])."',
                    '".str_replace("'", "`", $row[6])."',
                    '".str_replace("'", "`", $row[7])."',
                    '".str_replace("'", "`", $row[8])."',
                    '".str_replace("'", "`", $row[9])."',
                    '".date2Unix($row[10])."')";
    //pr($query);
    if ($sql = mysql_query($query))
        $cnt++;
}

echo '<p>Saved rows: <strong>'.$cnt.'</strong></p>';

echo '<form action="'.$_SERVER['PHP_SELF'].'?step=3" method="POST" enctype="multipart/form-data">';
echo '<input type="hidden" name="file" value="'.$fileName.'"><br />';
echo '<input type="submit" value=" Proceed to next WorkSheet ">';
echo '</form>';

    break;
    
    case 3:
    
$objPHPExcel = PHPExcel_IOFactory::load($_POST['file']);
$objPHPExcel->setActiveSheetIndex(1);
$sheet1 = $objPHPExcel->getActiveSheet();

$countRows = $sheet1->getHighestRow();
$highestColumn = $sheet1->getHighestColumn();
$countCols = PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($i = 2; $i <= $countRows; $i++) {
    for ($y=0; $y<$countCols; $y++) {
        if ($sheet1->getCell($arrColumns[$y].$i)->getValue())
            $cells[$i][$y] = $sheet1->getCell($arrColumns[$y].$i)->getValue();
    }
}

echo '<h2>Saving the WorkSheet: Policies</h2>';

echo '<p>Total rows: <strong>'.count($cells).'</strong></p>';
$cnt = 0;

foreach ($cells as $index=>$row) {
    $query = "INSERT INTO policies (policyNumber, premium, premiumLimit, producingState, filingState, endorsementDate, comments)
                VALUES (
                    '".str_replace("'", "`", $row[0])."',
                    '".str_replace("'", "`", $row[1])."',
                    '".str_replace("'", "`", $row[2])."',
                    '".str_replace("'", "`", $row[3])."',
                    '".str_replace("'", "`", $row[4])."',
                    '".date2Unix($row[5])."',
                    '".str_replace("'", "`", $row[6])."')";
    if ($sql = mysql_query($query))
        $cnt++;
}

echo '<p>Saved rows: <strong>'.$cnt.'</strong></p>';

echo '<form action="'.$_SERVER['PHP_SELF'].'?step=4" method="POST" enctype="multipart/form-data">';
echo '<input type="hidden" name="file" value="'.$_POST['file'].'"><br />';
echo '<input type="submit" value=" Proceed to next WorkSheet ">';
echo '</form>';

    break;
    
        case 4:
    
$objPHPExcel = PHPExcel_IOFactory::load($_POST['file']);
$objPHPExcel->setActiveSheetIndex(2);
$sheet1 = $objPHPExcel->getActiveSheet();

$countRows = $sheet1->getHighestRow();
$highestColumn = $sheet1->getHighestColumn();
$countCols = PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($i = 2; $i <= $countRows; $i++) {
    for ($y=0; $y<$countCols; $y++) {
        if ($sheet1->getCell($arrColumns[$y].$i)->getValue())
            $cells[$i][$y] = $sheet1->getCell($arrColumns[$y].$i)->getValue();
    }
}

echo '<h2>Saving the WorkSheet: Declinations</h2>';

echo '<p>Total rows: <strong>'.count($cells).'</strong></p>';
$cnt = 0;

foreach ($cells as $index=>$row) {
    $query = "INSERT INTO declinations (firstName, lastName, company, phoneNumber, reason, contactType, date)
                VALUES (
                    '".str_replace("'", "`", $row[0])."',
                    '".str_replace("'", "`", $row[1])."',
                    '".str_replace("'", "`", $row[2])."',
                    '".str_replace("'", "`", $row[3])."',
                    '".str_replace("'", "`", $row[4])."',
                    '".str_replace("'", "`", $row[5])."',
                    '".date2Unix($row[6])."')";
    if ($sql = mysql_query($query))
        $cnt++;
}

echo '<p>Saved rows: <strong>'.$cnt.'</strong></p>';

echo '<form action="'.$_SERVER['PHP_SELF'].'?step=5" method="POST" enctype="multipart/form-data">';
echo '<input type="hidden" name="file" value="'.$_POST['file'].'"><br />';
echo '<input type="submit" value=" Proceed to next WorkSheet ">';
echo '</form>';

    break;
    
    case 5:
    
$objPHPExcel = PHPExcel_IOFactory::load($_POST['file']);
$objPHPExcel->setActiveSheetIndex(3);
$sheet1 = $objPHPExcel->getActiveSheet();

$countRows = $sheet1->getHighestRow();
$highestColumn = $sheet1->getHighestColumn();
$countCols = PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($i = 2; $i <= $countRows; $i++) {
    for ($y=0; $y<$countCols; $y++) {
        if ($sheet1->getCell($arrColumns[$y].$i)->getValue())
            $cells[$i][$y] = $sheet1->getCell($arrColumns[$y].$i)->getValue();
    }
}

echo '<h2>Saving the WorkSheet: Endorsements</h2>';

echo '<p>Total rows: <strong>'.count($cells).'</strong></p>';
$cnt = 0;

foreach ($cells as $index=>$row) {
    $query = "INSERT INTO endorsements (endorsementNumber, endorsementType, premium, premiumLimit, producingState, filingState, endorsementDate, comments)
                VALUES (
                    '".str_replace("'", "`", $row[0])."',
                    '".str_replace("'", "`", $row[1])."',
                    '".str_replace("'", "`", $row[2])."',
                    '".str_replace("'", "`", $row[3])."',
                    '".str_replace("'", "`", $row[4])."',
                    '".str_replace("'", "`", $row[5])."',
                    '".date2Unix($row[6])."',
                    '".str_replace("'", "`", $row[7])."')";
if ($sql = mysql_query($query))
        $cnt++;
}

echo '<p>Saved rows: <strong>'.$cnt.'</strong></p>';

echo '<form action="'.$_SERVER['PHP_SELF'].'?step=6" method="POST" enctype="multipart/form-data">';
echo '<input type="hidden" name="file" value="'.$_POST['file'].'"><br />';
echo '<input type="submit" value=" Proceed to next WorkSheet ">';
echo '</form>';

    break;
    
    case 6:
    
$objPHPExcel = PHPExcel_IOFactory::load($_POST['file']);
$objPHPExcel->setActiveSheetIndex(4);
$sheet1 = $objPHPExcel->getActiveSheet();

$countRows = $sheet1->getHighestRow();
$highestColumn = $sheet1->getHighestColumn();
$countCols = PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($i = 2; $i <= $countRows; $i++) {
    for ($y=0; $y<$countCols; $y++) {
        if ($sheet1->getCell($arrColumns[$y].$i)->getValue())
            $cells[$i][$y] = $sheet1->getCell($arrColumns[$y].$i)->getValue();
    }
}

echo '<h2>Saving the WorkSheet: Underwriters</h2>';

echo '<p>Total rows: <strong>'.count($cells).'</strong></p>';
$cnt = 0;

foreach ($cells as $index=>$row) {
    $query = "INSERT INTO underwriters (name, highestInsuredAmount, premium, percent)
                VALUES (
                    '".str_replace("'", "`", $row[0])."',
                    '".str_replace("'", "`", $row[1])."',
                    '".str_replace("'", "`", $row[2])."',
                    '".str_replace("'", "`", $row[3])."')";
if ($sql = mysql_query($query))
        $cnt++;
}

echo '<p>Saved rows: <strong>'.$cnt.'</strong></p>';

echo '<form action="'.$_SERVER['PHP_SELF'].'?step=7" method="POST" enctype="multipart/form-data">';
echo '<input type="hidden" name="file" value="'.$_POST['file'].'"><br />';
echo '<input type="submit" value=" Proceed to next WorkSheet ">';
echo '</form>';

    break;

    case 7:
    
$objPHPExcel = PHPExcel_IOFactory::load($_POST['file']);
$objPHPExcel->setActiveSheetIndex(5);
$sheet1 = $objPHPExcel->getActiveSheet();

$countRows = $sheet1->getHighestRow();
$highestColumn = $sheet1->getHighestColumn();
$countCols = PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($i = 2; $i <= $countRows; $i++) {
    for ($y=0; $y<$countCols; $y++) {
        if ($sheet1->getCell($arrColumns[$y].$i)->getValue())
            $cells[$i][$y] = $sheet1->getCell($arrColumns[$y].$i)->getValue();
    }
}

echo '<h2>Saving the WorkSheet: RiskLocations</h2>';

echo '<p>Total rows: <strong>'.count($cells).'</strong></p>';
$cnt = 0;

foreach ($cells as $index=>$row) {
    $query = "INSERT INTO riskLocations (address1, address2, city, county, state, postalCode, value)
                VALUES (
                    '".str_replace("'", "`", $row[0])."',
                    '".str_replace("'", "`", $row[1])."',
                    '".str_replace("'", "`", $row[2])."',
                    '".str_replace("'", "`", $row[3])."',
                    '".str_replace("'", "`", $row[4])."',
                    '".str_replace("'", "`", $row[5])."',
                    '".str_replace("'", "`", $row[6])."')";
if ($sql = mysql_query($query))
        $cnt++;
}

echo '<p>Saved rows: <strong>'.$cnt.'</strong></p>';

unlink($_POST['file']);
echo '<h2>All Worksheets are saved in DB</h2>';

    break;
    
}

?>





--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
 
---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com.
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)