通过phpexcel导入和导出excel表格

首先是导入excel表格：

<?php  
mysql_connect('localhost', 'root', 'root');
mysql_select_db('excel');
mysql_query('set names utf8');
require_once './Classes/PHPExcel.php';
require_once './Classes/PHPExcel/IOFactory.php';
require_once './Classes/PHPExcel/Reader/Excel5.php';


$objReader = PHPExcel_IOFactory::createReader('Excel5'); //use Excel5 for 2003 format    
//这里是导入excel2003 的xls格式，如果是2007格式可以把“Excel5”换成“excel2007"
$excelpath='test.xls';
$objPHPExcel = $objReader->load($excelpath); 
$sheet = $objPHPExcel->getSheet(0); 
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $sheet->getHighestRow();    //取得总行数 
$highestColumn = $sheet->getHighestColumn(); //取得总列数

$highestColumn =  ord($highestColumn)-65;
$excelData = array(); 

for($row = 1; $row <= $highestRow; $row++)
{ 
  for ($col = 0; $col <= $highestColumn; $col++)
  {
    $excelData[$row-1][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
  }
}


foreach ($excelData as $key => $value) 
{
  $sql = "INSERT INTO message (title) VALUES('$value[0]')";
  mysql_query($sql);
}

?>

接下来是从数据库导出excel文件

<?php  
mysql_connect('localhost', 'root', 'root');
mysql_select_db('excel');
mysql_query('set names utf8');
require_once './Classes/PHPExcel.php';
require_once './Classes/PHPExcel/IOFactory.php';
require_once './Classes/PHPExcel/Reader/Excel5.php';
//创建对象
$excel = new PHPExcel();
//Excel表格式,这里简略写了8列
$letter = array('A','B','C','D','E','F','F','G','H','I');
//表头数组
$tableheader = array('学号','姓名','性别','年龄','班级');
//填充表头信息
for($i = 0;$i < count($tableheader);$i++) {
$excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");
}

//表格数组
$data = array(
array('1','小成','男','21','100'),
array('2','小明','男','22','101'),
array('3','小张','女','23','102'),
array('4','小赵','女','24','103')
);
//填充表格信息
for ($i = 2;$i <= count($data) + 1;$i++) {
$j = 0;
foreach ($data[$i - 2] as $key=>$value) {
$excel->getActiveSheet()->setCellValue("$letter[$j]$i","$value");
$j++;
}
}
//创建Excel输入对象
$write = new PHPExcel_Writer_Excel5($excel);
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");;
header("Content-Disposition:attachment;filename='testdata.xls'");
header("Content-Transfer-Encoding:binary");
$write->save('php://output');

?>

最后附带phpexcel插件下载链接

http://phpexcel.codeplex.com/releases/view/119187