PHP Import Excel to MySQL Database ช่วยดุ Error ให้หน่อยค่ะ import ไม่เข้า
Code Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft Excel<br/><b>Description:</b> Microsoft Excel cannot access the file 'D:\AppServ\www\asset\admin\excel\asset_cnr53.xls'. There are several possible reasons: � The file name or path does not exist. � The file is being used by another program. � The workbook you are trying to save has the same name as a currently open workbook.' in D:\AppServ\www\asset\admin\ImportExcelToDatabase_cnr53.php:19 Stack trace: #0 D:\AppServ\www\asset\admin\ImportExcelToDatabase_cnr53.php(19): variant->Open('D:\AppServ\www\...') #1 {main} thrown in D:\AppServ\www\asset\admin\ImportExcelToDatabase_cnr53.php on line 19
Code (PHP) <?
session_start();
ob_start();
date_default_timezone_set("Asia/Bangkok");
require_once("top.inc.php");
require_once("utility.php");
if(isset($_SESSION["login"]) || isset($_COOKIE["remember"])){
}else{
redirect("login.php");
}
?>
<?
//*** Get Document Path ***//
$strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"]))); // C:/AppServ/www/myphp
$OpenFile = "excel/asset_cnr53.xls";
//*** Create Exce.Application ***//
$xlApp = new COM("Excel.Application");
$xlBook = $xlApp->Workbooks->Open($strPath."/".$OpenFile);
//$xlBook = $xlApp->Workbooks->Open(realpath($OpenFile));
$xlSheet1 = $xlBook->Worksheets(1);
//*** Insert to MySQL Database ***//
require_once("config.inc.php");
echo $conn;
for($i=2;$i<=5;$i++){
If(trim($xlSheet1->Cells->Item($i,1)) != "")
{
$strSQL = "";
$strSQL .= "INSERT INTO tb_asset_cnr53";
$strSQL .= "(receive_date,class,as_code,des,owner_ship,occupant,register_car,purchase_price,sale_price,date_sold,notes)";
$strSQL .= "VALUES ";
$strSQL .= "('".$xlSheet1->Cells->Item($i,1)."','".$xlSheet1->Cells->Item($i,2)."' ";
$strSQL .= ",'".$xlSheet1->Cells->Item($i,3)."','".$xlSheet1->Cells->Item($i,4)."' ";
$strSQL .= ",'".$xlSheet1->Cells->Item($i,5)."','".$xlSheet1->Cells->Item($i,6)."') ";
$strSQL .= ",'".$xlSheet1->Cells->Item($i,7)."','".$xlSheet1->Cells->Item($i,8)."') ";
$strSQL .= ",'".$xlSheet1->Cells->Item($i,9)."','".$xlSheet1->Cells->Item($i,10)."') ";
$strSQL .= ",'".$xlSheet1->Cells->Item($i,11)."' ";
mysql_query($strSQL);
}
}
//*** Close MySQL ***//
@mysql_close($conn);
//*** Close & Quit ***//
$xlApp->Application->Quit();
$xlApp = null;
$xlBook = null;
$xlSheet1 = null;
?>
</body>
</html>
Tag PHP, MySQL
Date :
2014-06-24 14:57:59By :
nottpooView :
1892Reply :
7
คือเจ้าตัว Excel.Application มักจะมีปัญหากับ Office Version ใหม่ ๆ ครับ และเรื่องสภาพแวดล้อมของ Web Server ด้วยครับ แนะนำให้ไปใช้ PHPExcel
Date :
2014-06-25 08:23:38By :
mr.win
พี่วิน ทำไมเอาโค้ดมาแก้ แล้ว มัน Error Code (PHP) 503 Service Unavailable
Failed to resolve the name of server www.localhost.com
Code (PHP) <?php
/** PHPExcel */
require_once 'Classes/PHPExcel.php';
/** PHPExcel_IOFactory - Reader */
include 'Classes/PHPExcel/IOFactory.php';
$inputFileName = "asset.xls";
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1',null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
$dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,null, true, true, true);
if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
++$r;
foreach($headingsArray as $columnKey => $columnHeading) {
$namedDataArray[$r][$columnHeading] = $dataRow[$row][$columnKey];
}
}
}
//echo '<pre>';
//var_dump($namedDataArray);
//echo '</pre><hr />';
//*** Connect to MySQL Database ***//
include("../config.inc.php");
$i = 0;
foreach ($namedDataArray as $result) {
$i++;
$strSQL = "";
$strSQL .= "INSERT INTO tb_asset ";
$strSQL .= "(as_id,receive_date,year,class,as_type,as_code,sn,des,price,po,department,occupant,owner_ship,register_car,purchase_price,sale_price,date_sold,place,as_company,buy_from,notes)";
$strSQL .= "VALUES ";
$strSQL .= "('".$result["as_id"]."','".$result["receive_date"]."' ";
$strSQL .= ",'".$result["year"]."','".$result["class"]."' ";
$strSQL .= ",'".$result["as_type"]."','".$result["as_code"]."') ";
$strSQL .= ",'".$result["sn"]."','".$result["des"]."') ";
$strSQL .= ",'".$result["price"]."','".$result["po"]."') ";
$strSQL .= ",'".$result["department"]."','".$result["occupant"]."') ";
$strSQL .= ",'".$result["owner_ship"]."','".$result["register_car"]."') ";
$strSQL .= ",'".$result["purchase_price"]."','".$result["sale_price"]."') ";
$strSQL .= ",'".$result["date_sold"]."','".$result["place"]."') ";
$strSQL .= ",'".$result["as_company"]."','".$result["buy_from"]."') ";
$strSQL .= ",'".$result["notes"]."' ";
mysql_query($strSQL) or die(mysql_error());
echo "Row $i Inserted...<br>";
}
mysql_close($conn);
?>
</body>
</html>
Date :
2014-06-25 14:07:14By :
nottpoo
ได้เปิดพวก extension ของ zip ด้วยหรือเปล่าครับ
Date :
2014-06-26 07:49:03By :
mr.win
เปิดแล้ว แล้วก็รีสตาร์ท Apache แล้วค่ะ ก็ยังไม่ได้Code (PHP) ;extension=php_bz2.dll
extension=php_curl.dll
;extension=php_dba.dll
;extension=php_dbase.dll
extension=php_exif.dll
;extension=php_fdf.dll
extension=php_gd2.dll
;extension=php_gettext.dll
;extension=php_gmp.dll
;extension=php_ifx.dll
;extension=php_imap.dll
;extension=php_interbase.dll
;extension=php_ldap.dll
extension=php_mbstring.dll
;extension=php_mcrypt.dll
;extension=php_mhash.dll
;extension=php_mime_magic.dll
;extension=php_ming.dll
;extension=php_msql.dll
;extension=php_mssql.dll
extension=php_mysql.dll
extension=php_mysqli.dll
;extension=php_oci8.dll
;extension=php_openssl.dll
extension=php_pdo.dll
;extension=php_pdo_firebird.dll
;extension=php_pdo_mssql.dll
;extension=php_pdo_mysql.dll
;extension=php_pdo_oci.dll
;extension=php_pdo_oci8.dll
;extension=php_pdo_odbc.dll
;extension=php_pdo_pgsql.dll
extension=php_pdo_sqlite.dll
;extension=php_pgsql.dll
;extension=php_pspell.dll
;extension=php_shmop.dll
;extension=php_snmp.dll
;extension=php_soap.dll
extension=php_sockets.dll
extension=php_sqlite.dll
;extension=php_sybase_ct.dll
;extension=php_tidy.dll
;extension=php_xmlrpc.dll
extension=php_xsl.dll
extension=php_zip.dll
Date :
2014-06-26 14:12:30By :
nottpoo
สรุปว่า ปูก็หาสาเหตุ ไม่ได้ ว่าเพราะอะไร สุดท้ายเลยตัดสินใจ เปลี่ยนมาใช้ import CSV to Mysql Code (PHP) <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
Code (PHP) $conn=mysql_connect($servername,$username,$password) or die("can't Connect MySQL");
mysql_query("SET NAME UTF8");
ประวัติการแก้ไข
Date :
2014-06-26 15:50:06By :
nottpoo
ได้แล้วค่ะ Code (PHP) <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
Date :
2014-06-26 16:51:30By :
nottpoo
Date :
2014-07-09 11:02:08By :
mr.win
Server 05
Load balance : Server 05
