我有一个带有栏的MS-excel文件：地址
还有一个MySQL数据库表，其列为：Add1，Add2，Add3
通过PHP上传MSExcel文件后，是否可以在Excel文件的“地址”列中拆分数据并将其分为Add1，Add2，Add3（每列限制为40个字符）？
我的代码用于在PHP中上传文件：
if(isset($_POST['submit'])) {
if(isset($_FILES['uploadFile']['name']) && $_FILES['uploadFile']['name'] != "") {
$allowedExtensions = array("xls","xlsx","csv");
$ext = pathinfo($_FILES['uploadFile']['name'], PATHINFO_EXTENSION);
if(in_array($ext, $allowedExtensions)) {
$file_size = $_FILES['uploadFile']['size'] / 1024;
if($file_size < 50) {
$file = "../excelfile/".$_FILES['uploadFile']['name'];
$isUploaded = copy($_FILES['uploadFile']['tmp_name'], $file);
if($isUploaded) {
include("../Classes/PHPExcel/IOFactory.php");
try {
//Load the excel(.xls/.xlsx/.csv) file
$objPHPExcel = PHPExcel_IOFactory::load($file);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME). '": ' . $e->getMessage());
}
//An excel file may contains many sheets, so you have to specify which one you need to read or work with.
$sheet = $objPHPExcel->getSheet(0);
//It returns the highest number of rows
$total_rows = $sheet->getHighestRow();
//It returns the highest number of columns
$total_columns = $sheet->getHighestColumn();
$sheet_data = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
用于将地址插入表中的查询是：
$query = "insert into address_table (Add1, Add2, Add3)
values(
'".$_POST['address1']."',
'".$_POST['address2']."',
'".$_POST['address3']."')
我需要做一些估算，因为我没有您的Excel文件。
想象一下你$sheet_data现在的样子
Array
(
[0] => Array
(
[0] => Street1
)
[1] => Array
(
[0] => Street2
)
[2] => Array
(
[0] => Street3 with a long name, more than 40 characters
)
)
第一级中的每个Array-Element代表一条线-第二级中的每个元素包含相应的列。
foreach ($sheet_data as $line){
//maybe you need to skip the first line because it could contain just some headers
//in my estimation $line[0] contains the address
$address = $line[0];
//split address after 40 characters
$split = str_split($address, 40);
//$conn - mysqli connection
$stmt = $conn->prepare("INSERT INTO address_table (Add1, Add2, Add3)
VALUES (?, ?, ?)");
$addr1=""; $addr2=""; $addr3="";
if(isset($split[0]) $addr1 = $split[0];
if(isset($split[1]) $addr2 = $split[1];
if(isset($split[2]) $addr3 = $split[2];
//fill query params params with values
$stmt->bind_param("sss", $addr1, $addr2, $addr3);
$stmt->execute();
}
对于第三行-表条目将填充为
Add1 => Street3 with a long name, more than 40 c
Add2 => haracters
您仍然需要检查一件事-如果地址的字符数超过120个，则其他字符会丢失atm。
并且请像我的示例一样使用准备好的语句-当您直接用变量填充查询时，您很容易受到SQL注入的攻击
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句