public static function convertCellData(\PHPExcel_Cell $cell) { $ret = array(); $datatype = $cell->getDataType(); if ($datatype === \excel2sql\Type::EXCEL_NUMERIC) { $format = $cell->getStyle()->getNumberFormat()->getFormatCode(); if (array_key_exists($format, \excel2sql\Type::$numeric_convert_map)) { $format = \excel2sql\Type::$numeric_convert_map[$format]; } $ret['type'] = \excel2sql\Type::$numeric_to_sql_map[$format]; $ret['value'] = \PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), $format); if ($format === \excel2sql\Type::EXCEL_DATE || $format === \excel2sql\Type::EXCEL_DATETIME || $format === \excel2sql\Type::EXCEL_TIME) { $ret['value'] = "'{$ret['value']}'"; } } else { $ret['type'] = \excel2sql\Type::$excel_to_sql_map[$datatype]; if ($ret['value'] = $cell->getFormattedValue()) { if ($datatype === \excel2sql\Type::EXCEL_STRING || $datatype === \excel2sql\Type::EXCEL_STRING2) { $ret['value'] = "\"" . preg_replace("[\"]", "''", $ret['value']) . "\""; } } else { $ret['value'] = "null"; } } return $ret; }
/** * Bind value to a cell, preserving possible leading zeros * See http://stackoverflow.com/questions/12457610/reading-numbers-as-text-format-with-phpexcel * * @param PHPExcel_Cell $cell Cell to bind value to * @param mixed $value Value to bind in cell * @return boolean */ public function bindValue(PHPExcel_Cell $cell, $value = null) { // sanitize UTF-8 strings if (is_string($value)) { $value = PHPExcel_Shared_String::SanitizeUTF8($value); } // Preserve numeric string, including leading zeros, if it is a text format $format = $cell->getStyle()->getNumberFormat()->getFormatCode(); if ($format == PHPExcel_Style_NumberFormat::FORMAT_TEXT) { $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); return true; } // Not bound yet? Use default value parent... return parent::bindValue($cell, $value); }