通过phpexcel导入和导出excel表格

淡淡的烟草味﹌ 2022-09-26 02:55 275阅读 0赞

首先是导入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

![Image 1][]

[Image 1]: