我正在使用PHPExcel来更新eshop的商品价格和库存,将数据传递给mysql查询并更新数据库。我对某些包含格式为 123,45 的数字的单元格(使用逗号作为小数分隔符)有问题。尽管所有单元格都包含相同的格式,但只有一些单元格被正确读取。请自己看看,让我知道我错过了什么。
- Excel 示例数据:http://dl.dropbox.com/u/25322282/data.xlsx
- 从循环访问数据并打印出它正在读取的内容的日志,您可以看到价格仅在第 66 行之后读取。 http://dl.dropbox.com/u/25322282/log.htm
最后,我的代码:
$objPHPExcel = PHPExcel_IOFactory::load($path);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
$rowsadded=0;
$begin_row=2; // 1st line of data in excel file
for ($row = $begin_row; $row <= $highestRow; ++ $row) {
$val=array();
for ($col=0; $col < $highestColumnIndex; $col++) {
$cell = $objWorksheet->getCellByColumnAndRow($col, $row);
$val[] = $cell->getCalculatedValue();
}
if (($val[0]<>'') && ($val[6]>0)) { //check that row contains data before inserting
$rowsadded++;
$sql = sprintf("update productsizes set price=%s, stock=%s where auxcode=%s",
GetSQLValueString($val[6], "float"),
GetSQLValueString($val[4], "int"),
GetSQLValueString($val[0], "text"));
$result = mysql_query($sql) or die(mysql_error());
} //end of checking that row contains data before inserting
} echo '<h2>'.$rowsadded. ' rows updated successfully</h2>';
我没有
使用过 PHPExcel,但你为什么不更改这个$val[] = $cell->getCalculatedValue();
来获取字符串值,然后用.
替换,
,并将变量转换为整数并将其存储在另一个变量/数组中?我敢打赌这会起作用。
$rowsadded=0;
$begin_row=2; // 1st line of data in excel file
for ($row = $begin_row; $row <= $highestRow; ++ $row) {
$val = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,NULL,TRUE,FALSE,TRUE);
var_dump($val);
$val = $val[$row];
if (($val['A']<>'') && ($val['G']>0)) { //check that row contains data before inserting
$rowsadded++;
// other code goes in here
} //end of checking that row contains data before inserting
} echo '<h2>'.$rowsadded. ' rows updated successfully</h2>';
var_dump() 将在加载时显示每行的值和数据类型。将其用于调试目的