上传并插入xls文件到mysql? [英] Upload and insert xls file to mysql?

查看:81
本文介绍了上传并插入xls文件到mysql?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在努力寻找一个教程,向我展示如何编写php脚本代码,该脚本将允许我上传xls(而非CSV)并将其插入mysql.我有fgetcsv这个php脚本,一切正常.但是我需要包括对xls的支持.

I´m strugeling to find a tutorial to show me how to code a php script that will let me upload and insert xls (not CSV) into mysql. I have this php script with fgetcsv and it works all fine. But i need to include support for xls.

我的PHP

if(isset($_POST["Import"])){
    echo $filename=$_FILES["file"]["tmp_name"];
    if($_FILES["file"]["size"] > 0)
     {
     $file = fopen($filename, "r");
     while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
     {
    //It wiil insert a row to our subject table from our csv file`
           $sql = "INSERT into excell_import (`objekt`, `element_nummer`, `element_hojd`,  `element_langd`,COURSE_ID, `AY`, `SEMESTER`) 
                 values('$emapData[1]','$emapData[2]','$emapData[3]','$emapData[4]','$emapData[5]','$emapData[6]','$em apData[7]')";
         //we are using mysql_query function. it returns a resource on true else False on error
          $result = mysql_query( $sql, $conn );
            if(! $result )
            {
                echo "<script type=\"text/javascript\">
                        alert(\"Invalid File:Please Upload CSV File.\");
                        window.location = \"index.php\"
                    </script>";
            }
}
         fclose($file);
         //throws a message if data successfully imported to mysql database from excel file
         echo "<script type=\"text/javascript\">
                    alert(\"CSV File has been successfully Imported.\");
                    window.location = \"index.php\"
                </script>";
        //close of connection
        mysql_close($conn); 

}    
?>       

推荐答案

用于从xls文件收集数据并将其插入mysql的工作脚本.

Working script for collect data from xls file and insert to mysql.

<?php
//include the following 2 files for phpexcel
require 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

//Establish connection to mysql
$conn=mysql_connect($host,$username,$password) or die("Could not connect");
mysql_select_db($dbname,$conn) or die("could not connect database");

//Load file
$path = "atbl.xls";
$objPHPExcel = PHPExcel_IOFactory::load($path);

//Loop threw file to get data
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle     = $worksheet->getTitle();
$highestRow         = 20; //$worksheet->getHighestRow(); // e.g. 10
$highestColumn      = 'G'; //worksheet->getHighestColumn(''); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
echo "<br>The worksheet ".$worksheetTitle." has ";
echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
echo ' and ' . $highestRow . ' row.';
echo '<br>Data: <table border="1"><tr>';
for ($row = 11; $row <= $highestRow; ++ $row) {
    echo '<tr>';
    for ($col = 0; $col < $highestColumnIndex; ++ $col) {
        $cell = $worksheet->getCellByColumnAndRow($col, $row);
        $val = $cell->getCalculatedValue();
        //$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
        echo '<td>' . $val . '<br></td>';
    }
    echo '</tr>';
}
echo '</table>';
}

for ($row = 11; $row <= $highestRow; ++ $row) {
$val=array();
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val[] = $cell->getValue();
}
//Insert data from file to mysql 
$sql="INSERT INTO phpexcel(objekt_nr, objekt_rev, element_nr, element_langd, element_bredd,     element_hojd)
VALUES ('".$val[1] . "','" . $val[2] . "','" . $val[3]. "','" . $val[4]. "','" . $val[5]. "','" .     $val[6]. "')";
//echo $sql."\n";
mysql_query($sql) or die('Invalid query: ' . mysql_error());
}
?>

这篇关于上传并插入xls文件到mysql?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆