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);
}
