問題描述
我正在使用 PHPExcel 從 Excel 表中讀取數據并存儲在 mysql 表中，直到現在我能上傳 .xls 和 .xlsx 文件，上傳 xls 后，我得到了下面的數據表結構
I m using PHPExcel to read data from Excel sheet and store in mysql table, till now i m able to upload .xls as well as .xlsx file and after uploading the xls i got below table structure of data
name start_date end_date city
one 11/25/2011 3:30:00 PM 11/29/2011 4:40:00 AM Jaipur
two 10/22/2011 5:30:00 PM 10/25/2011 6:30:00 AM Kota
three 3/10/2011 2:30:00 PM 3/11/2011 12:30:00 AM Bikaner
chandigarh
現在我遇到了一些問題，請建議我優化的方法
now i have some problems, please suggest me the optimized method
- 我們如何獲得工作表名稱(bcoz 在一個 excel 中有 7 個工作表)
現在我如何存儲這些數據到db，下面是代碼片段
- how do we get the sheet name ( bcoz in one excel there are 7 sheets )
for now what i do to store these data into db, below is the code snippet
$inputFileName = "test.xls";
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);
$total_sheets=$objPHPExcel->getSheetCount(); // here 4
$allSheetName=$objPHPExcel->getSheetNames(); // array ([0]=>'student',[1]=>'teacher',[2]=>'school',[3]=>'college')
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0); // first sheet
$highestRow = $objWorksheet->getHighestRow(); // here 5
$highestColumn = $objWorksheet->getHighestColumn(); // here 'E'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // here 5
for ($row = 1; $row <= $highestRow; ++$row) {
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
$value=$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
if(is_array($arr_data) ) { $arr_data[$row-1][$col]=$value; }
}
}
print_r($arr_data);
然后返回
Array
(
[0] => Array
(
[0] => name
[1] => start_date
[2] => end_date
[3] => city
[4] =>
)
[1] => Array
(
[0] => one
[1] => 40568.645833333
[2] => 40570.5
[3] => Jaipur
[4] =>
)
[2] => Array
(
[0] => two
[1] => 40568.645833333
[2] => 40570.5
[3] => Kota
[4] =>
)
[3] => Array
(
[0] => three
[1] => 40568.645833333
[2] => 40570.5
[3] => Bikaner
[4] =>
)
[4] => Array
(
[0] =>
[1] =>
[2] =>
[3] => Chandigarh
[4] =>
)
)
我需要那個
- 每個excel表的標題(即第一行)成為數組的鍵($arr_data)和
- rest 成為數組的值.
- 時間變成了一些整數值，應該和excel表中的一樣
- 應截斷所有行中數組的空白字段(即空白標題列)(此處 [4] )
- 如果 Excel 表的第一個字段(或某些情況下的組合條件)字段)未滿足，則不應將該行添加到數組中
即所需的數組應該看起來像
i.e. desired array should look like
Array
(
[0] => Array
(
[name] => one
[start_date] => 11/25/2011 3:30:00 PM
[end_date] => 11/29/2011 4:40:00 AM
[city] => Jaipur
)
[1] => Array
(
[name] => two
[start_date] => 10/22/2011 5:30:00 PM
[end_date] => 10/25/2011 6:30:00 AM
[city] => Kota
)
[2] => Array
(
[name] => three
[start_date] => 3/10/2011 2:30:00 PM
[end_date] => 3/11/2011 12:30:00 AM
[city] => Bikaner
)
)
然后我在所需數組上使用 mysql 操作將數據存儲到我的數據庫中.
and after that i store data into my db using mysql action on desired array.
- 是否還有其他簡短的方法可以將上述數據存儲在 db 中
@Mark 感謝您的解決方案，它對我幫助很大，但仍然存在一些問題
@Mark Thanks for your solution, it helps me a lot, but still some problems are there
- 如何處理excel表中的空/空白單元格..bcoz，當任何單元格為空時，它會顯示一個通知
注意:未定義索引:C in C:xampphtdocsxls_readerTestsexcel2007.php在線 60
注意:未定義索引:D in C:xampphtdocsxls_readerTestsexcel2007.php在線60
Notice: Undefined index: C in C:xampphtdocsxls_readerTestsexcel2007.php on line 60
Notice: Undefined index: D in C:xampphtdocsxls_readerTestsexcel2007.php on line 60
而第 60 行是
foreach($headingsArray as $columnKey => $columnHeading) {
$namedDataArray[$r][$columnHeading] = $dataRow[$row]$columnKey];
}
- 如何在檢索完整數據數組之前設置條件，即如果我希望第一列和第二列在任何行中為空/空白，則不應將該行添加到我們想要的數組中
謝謝
推薦答案
我們如何獲得工作表名稱( bcoz in一個excel有7張)?
how do we get the sheet name ( bcoz in one excel there are 7 sheets )?
獲取當前活動的工作表:
To get the current active sheet:
$sheetName = $objPHPExcel->getActiveSheet()->getTitle();
<塊引用>
時間變成了一些整數值，應該和excel表一樣
查看 PHPExcel_Shared_Date::ExcelToPHP($excelDate) 或 PHPExcel_Shared_Date::ExcelToPHPObject($excelDate) 將日期/時間值轉換為 PHP 時間戳或 DateTime 對象
time changed into some integer value, that shoud be same as in excel sheet
查看 $objPHPExcel->getActiveSheet()->toArray() 方法，而不是自己遍歷所有 rwos 和列.但是，如果您想將 toArray 與格式化參數一起使用，請不要使用 $objReader->setReadDataOnly(true);否則 PHPExcel 無法區分數字和日期/時間.最新的 SVN 代碼在工作表對象中添加了 rangeToArray() 方法，該方法允許您一次讀取一行(或單元格塊)，例如$objPHPExcel->getActiveSheet()->rangeToArray('A1:A4')
Look at PHPExcel_Shared_Date::ExcelToPHP($excelDate) or PHPExcel_Shared_Date::ExcelToPHPObject($excelDate) to convert the date/time values to a PHP timestamp or DateTime object
你的其余問題基本上都是 PHP 數組操作
Have a look at the $objPHPExcel->getActiveSheet()->toArray() method rather than looping through all the rwos and columns yourself. If you want to use toArray with the formatted argument though, don't use $objReader->setReadDataOnly(true); otherwise PHPExcel can't distinguish between a number and a date/time. The latest SVN code has added a rangeToArray() method to the worksheet object, which allows you to read a row (or block of cells) at a time, e.g. $objPHPExcel->getActiveSheet()->rangeToArray('A1:A4')
編輯
附注.不要只是告訴我們手冊真的非常糟糕...告訴我們如何改進它.
EDIT
編輯 2
使用最新的SVN代碼利用rangeToArray()方法:
EDIT 2
Using the latest SVN code to take advantage of the rangeToArray() method:$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 />';
這篇關于PHPExcel 閱讀器——需要幫助的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！