问题描述
我使用从Excel工作表中读取数据并存储在mysql表中，直到现在可以上传.xls以及.xlsx文件，并在上传xls之后得到数据表结构
name start_date end_date city
one 11/25/2011 3:30:00 PM 11/29/2011 4:40:00 AM斋浦尔
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
现在我有一些问题，请建议我优化的方法
- 我们如何获取工作表名称（bcoz在一个excel有7个工作表）
现在我把这些
数据存储到db，下面是代码片段$ inputFileName =test.xls;
$ inputFileType = PHPExcel_IOFactory :: identify（$ inputFileName）;
$ objReader = PHPExcel_IOFactory :: createReader（$ inputFileType）;
$ objReader-> setReadDataOnly（true）;
/ **将$ inputFileName加载到PHPExcel对象** /
$ 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）;
并返回
数组
（
[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
] =>两个
[1] => 40568.645833333
[2] => 40570.5
[3] => Kota
[4] =& $ b）
[3] => Array
（
[0] => three
[1] => 40568.645833333
[2] => ; 40570.5
[3] => Bikaner
[4] =>
）
[4] => Array
] =>
[1] =>
[2] =>
[3] => Chandigarh
[4] =>
）
）
- 每个Excel工作表（即第一行）的标题变成数组的关键字（$ arr_data）和
- 数组的值。
- 时间更改为某个整数值，与excel表格相同
- 所有行数组的空白字段应该截断（此处为[4]）
- 如果Excel表单的第一个字段（或某些
字段上的组合条件）未满足，则不应将该行添加到数组
所需数组应类似于
数组
（
[0] =& （
[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操作将数据存储到我的数据库。
- 是否有任何其他简短方法来存储db中的上述数据
不要参考手册（真的很不好）...只是告诉我方法名称..
更新
@Mark感谢您的解决方案，它帮助了我很多，但仍然有一些问题有
- 如何处理空/
，而第60行是
foreach（$ headingsArray as $ columnKey => $ columnHeading）{
$ namedDataArray [$ r] [$ columnHeading] = $ dataRow [$ row] $ columnKey];
}
- 如何在检索数组之前设置条件数据即如果我想如果第一和第二列是空的/在任何行的空白，那么该行不应该添加在我们所需的数组
谢谢
查看PHPExcel_Shared_Date :: ExcelToPHP（$ excelDate）或PHPExcel_Shared_Date :: ExcelToPHPObject（$ excelDate）日期/时间值到PHP时间戳或DateTime对象
查看$ objPHPExcel-> getActiveSheet（） - > toArray（）方法，而不是循环遍历所有rwos和列自己。如果你想使用toArray与格式化的参数虽然，不要使用$ objReader-> setReadDataOnly（true）;否则PHPExcel不能区分数字和日期/时间。最新的SVN代码为工作表对象添加了一个rangeToArray（）方法，它允许您一次读取一行（或单元格块），例如。 $ objPHPExcel-> getActiveSheet（） - > rangeToArray（'A1：A4'）
其余的问题基本上都是PHP数组操作
EDIT
PS。
编辑2
p>使用最新的SVN代码来利用rangeToArray（）方法：
$ 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 />';
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
- how do we get the sheet name ( bcoz in one excel there are 7 sheets )
for now what i do to store thesedata 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);
and this returns
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] =>
)
)
i need that
- header of each excel sheet (i.e. first row) become key of array($arr_data) and
- rest become the value of array.
- time changed into some integer value, that shoud be same as in excel sheet
- blank field ( i.e.blank header column ) of array in all row should be truncated (here [4] )
- if first field of an excel sheet (or combined condition on somefields) is not fulfilled then that row should not be added into array
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
)
)
and after that i store data into my db using mysql action on desired array.
- is there any other short method to store above data in db
Note: Please do not refer manual( it is really really very bad )... just tell me the methods name..
update
@Mark Thanks for your solution, it helps me a lot, but still some problems are there
- how to handle empty/blank cell in excel sheet..bcoz when any cell are empty then it display a notice
whereas line 60 is
foreach($headingsArray as $columnKey => $columnHeading) {
$namedDataArray[$r][$columnHeading] = $dataRow[$row]$columnKey];
}
- how to set conditions before retrieving array of complete data i.e. if i want that if first and second column are empty/blank in any row then that row should not be added in our desired array
thanks
To get the current active sheet:
$sheetName = $objPHPExcel->getActiveSheet()->getTitle();
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
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')
The rest of your questions are basically PHP array manipulation
EDIT
PS. Instead of just telling us that the manual is really really very bad... tell us how we can improve it.
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读取器 - 需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！