linux读取excel并导入mysql_php读取Excel xlsx 2007+并导入MySQL
最近做一个从从excel将数据导入到mysql中的程序，excel的文件名就是即将创建的表名。
class Excel2DbAction extends BaseAction
{
protected $objReader;
protected $PHPExcel;
protected $Excel;
protected $Doc;
protected static $total = 0;
/**
* 导入PHPExcel类库,并初始化
* @access public
*/
public function __construct()
{
vendor('PHPExcel.PHPExcel.IOFactory');
$this->objReader = PHPExcel_IOFactory::createReader('Excel2007');
$this->objReader->setReadDataOnly(TRUE);
}
/**
* 扫描目录，扫描xlsx文件,逐一读取,写进MySQL
* @access public
*/
public function index()
{
$dir = APP_PATH.'Data';
if(!is_dir($dir)){
exit('目录不存在');
}
//扫描并遍历文件
$fileList = scandir($dir);
foreach ($fileList as $v){
if(pathinfo($v, PATHINFO_EXTENSION) == 'xlsx'){
self::$total ++;
$temp[self::$total] = $dir.'/'.$v;
}
}
$num = (!empty($_GET['num'])) ? ($_GET['num']) : 1;
while ($num <= self::$total){
//读取excel
$this->readFile($temp[$num]);
//导表操作
$this->createTable($this->Doc['tableName'], $this->Doc['fields']);
$this->createData($this->Doc['tableName'], $this->Doc['fields'], $this->Doc['content']);
$num++;
//判断是否跳转到下一个文件
($num > self::$total) ? exit('Excel to MySQL:ok') : $this->redirect('Excel2Db/index',array('num'=>$num),3);
}
}
/**
* 执行解析xlsx文件
* @access public
* @param string $file 文件
* @return mix 返回解析结果
*/
public function readFile($file)
{
$this->PHPExcel = $this->objReader->load($file);
$this->PHPExcel->setActiveSheetIndex(0);
$this->Excel = $this->PHPExcel->getActiveSheet();
$tmpContent = $this->getContent();
for($i=2,$j=0;$i<=count($tmpContent);$i++,$j++)
{
$content[$j] = $tmpContent[$i];
}
$filename = pathinfo($file, PATHINFO_FILENAME);
$doc = array(
'tableName' => $filename,
'rows' => $this->getRows(),
'colums' => $this->getColumIndex(),
'fields' => $tmpContent['1'],
'content' => $content,
);
$this->Doc = $doc;
}
/**
* 取得Excel的行数
* @access public
* @return string
*/
public function getRows()
{
$rows = $this->Excel->getHighestRow();
return $rows;
}
/**
* 取得Excel的列数，字母模式
* @access public
* @return string
*/
public function getColums()
{
$colums = $this->Excel->getHighestColumn();
return $colums;
}
/**
* 取得Excel的列数，数字模式
* @access public
* @return int
*/
public function getColumIndex()
{
$columIndex = PHPExcel_Cell::columnIndexFromString($this->getColums());
return $columIndex;
}
/**
* 取得Excel的详细内容
* @access public
* @return array
*/
public function getContent()
{
$content = array();
for ($row = 1; $row <= $this->getRows(); $row++)
{
for ($col = 0; $col < $this->getColumIndex(); $col++)
{
$content[$row][$col] = $this->Excel->getCellByColumnAndRow($col, $row)->getCalculatedValue();
}
}
return $content;
}
/**
* 创建数据表,传入表名和字段信息自动创建一个表
* @param string $tableName
* @param type $fields
* @return boolean
*/
private function createTable($tableName,$fields)
{
$prefix = C('DB_PREFIX');
$tableName = $prefix.$tableName;
$sql = "CREATE TABLE IF NOT EXISTS `{$tableName}`(\n\r`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,";
foreach ($fields as $v)
{
$sql .= "\n\r{$v},";
}
$sql .= "\r\nPRIMARY KEY (`id`)";
$sql .= "\n\r) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
$db = new Model();
if($db->execute($sql)){
echo "数据表{$tableName}创建成功
";
return TRUE;
}else{
echo "数据表{$tableName}已经创建
";
$truncate = "TRUNCATE TABLE `{$tableName}`";
$db->execute($truncate);
echo "数据表{$tableName}已经清空
";
}
}
/**
* 导入数据到对应的表中
* @param type $tableName
* @param type $fields
* @param type $content
* @return boolean
*/
private function createData($tableName,$fields,$content)
{
$table = M($tableName);
$reg = '/`.*`/';
foreach ($fields as $k){
preg_match($reg, $k, $matches);
$field[] = str_replace('`', '', $matches[0]);
}
foreach ($content as $v){
$data[] = array_combine($field, $v);
}
if($table->addAll($data)){
echo "数据导入{$tableName}成功
";
return TRUE;
}else{
echo "数据导入{$tableName}失败",$table->getDbError().'
';
}
}
/*
* 析构方法，清空计数器
*/
public function __destruct() {
self::$total = NULL;
}
}