PHPExcel - php操作excel 导入mysql 导出到excel

  • Cate: 笔记
  • 本文发布较早，其内容可能过时，阅读时请注意甄别。

PHP操作excel是常用的功能，PHPExcel还是比较强大的，简单记录下常用功能。下载PHPExcel后将PHPExcel目录和PHPExcel.php文件放到项目中即可。

php写入excel

<?php
include "./PHPExcel.php";

$phpexcel = new PHPExcel();
//初始化phpwriter类对象
$phpwriter = PHPExcel_IOFACTORY::createWriter($phpexcel, 'excel2007');

//创建多个工作表
//第一种创建工作表的方式
$sheet = new PHPExcel_Worksheet($phpexcel, '测试');
$phpexcel->addSheet($sheet);

//第二种创建工作表的方法
// $phpexcel->createSheet();

//数据插入

//设置当前工作表
$phpexcel->setActiveSheetIndex(1);

//得到当前工作表对象
$currentSheet = $phpexcel->getActiveSheet();

//数据插入  支持链式操作  调用excel函数
$currentSheet->setCellValue('A1','1')->setCellValue('B1','2')->setCellValue('C1','=sum(A1:B1)');

//样式设置
$currentSheet->getStyle('A1')->getFont()->setBold(true)->setItalic(true)->setColor(new PHPExcel_Style_Color (PHPExcel_Style_Color::COLOR_DARKGREEN))->setName('隶书')->setSize(20);

//设置列宽
$currentSheet->getColumnDimension('C')->setWidth(30); //setAutoSize(true); //对中文支持并不好
//设置行高
$currentSheet->getRowDimension('8')->setRowHeight(130);

//设置边框样式
$currentSheet->getStyle('B2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$currentSheet->getStyle('B2')->getBorders()->getTop()->getColor()->setARGB('FF993300');

//设置填充色
$currentSheet->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$currentSheet->getStyle('A1')->getFill()->getStartColor()->setARGB('FF993300');

//单元格合并
$currentSheet->mergeCells('A2:C3');

//插入行
$currentSheet->insertNewRowBefore(2,2); //从第几行插 插入几行

//删除行
$currentSheet->removeRow(2,1); //从第几行删，删除几行

//插入列 删除列 insertNewColumnBefore('A',1);  removeColumn('A',1);

//插入图片
$img = new PHPExcel_Worksheet_Drawing();
$img->setPath('test.jpg');
$img->setWidth(100)->setHeight(100);
$img->setCoordinates('C4');
$img->setOffsetX(10);
$img->setRotation(15);
$img->setWorkSheet($currentSheet);

//保存 第一种保存方法
$phpwriter->save('excel.xlsx');

// 第二种保存方法 在浏览器中打开
/*ob_end_clean();//解决内容乱码问题
$name = date('YmdHi');
header('Content-Type: application/vnd.ms-excel;');
header('Content-Disposition: attachment;filename='.$name.'.xlsx');
header('Cache-Control: max-age=0');
$phpwriter->save('php://output');*/

//乱码解决
/*$filename = iconv('utf-8', 'gbk', '中文.xlsx');
$phpwriter->save($filename);*/

PHP读取excel

<?php 
include "./PHPExcel.php";
//初始化phpreader类
$phpreader = PHPExcel_IOFACTORY::createReader('excel2007');
//第二种调用方法
// $phpreader = new PHPExcel_Reader_Excel2007();

//文件加载
$path = "./1.xlsx";
$phpexcel = PHPExcel_IOFACTORY::load($path);

//获取工作表数量 工作表名
// echo $phpexcel->getSheetCount();
$arr = $phpexcel->getSheetNames();

//根据工作表名切换工作表
$phpexcel->setActiveSheetIndexByName($arr[0]);

// 获取excel内容
$content = $phpexcel->getActiveSheet()->toArray();
// var_dump($content);
// var_dump($phpreader->listWorkSheetInfo($path));
// var_dump($phpreader->listWorkSheetNames($path));

//获取最大行数 列数
// echo $phpexcel->getActiveSheet()->getHighestRow(); //getHighestColumn();

//获取单元格中的内容
echo $phpexcel->getActiveSheet()->getCell('A1')->getValue();

excel导入mysql

<?php 
header('Content-Type:text/html;charset=utf8');
include "./PHPExcel.php";
//初始化phpreader类
$phpreader = PHPExcel_IOFACTORY::createReader('excel2007');
//文件加载
$path = "./1.xlsx";
$phpexcel = PHPExcel_IOFACTORY::load($path);

$currentSheet = $phpexcel->getActiveSheet();

// 获取excel内容  第一种方法
// $content = $currentSheet->toArray();
//第二种获取内容
// 得到行数 列数
$rows = $currentSheet->getHighestRow();
$columns = $currentSheet->getHighestColumn();

$arr = array();
for ($i=1; $i <=$rows ; $i++) {
    $newArr = array();//每行 
    for ($j='A'; $j <=$columns ; $j++) { 
        $adr = $j.$i;
        $data = $currentSheet->getCell($adr)->getValue();
        $newArr[] = $data;
    }
    $arr[] = $newArr;
}

// var_dump($arr);

 ?>
<html>
<head>
    <title></title>
</head>
<body>
    <table border="1" cellpadding='0'>
        <tr>
        <?php for($k='A'; $k<=$columns; $k++): ?>
        <th><?php echo $k; ?></th>
        <?php endfor; ?>
        </tr>

        <?php foreach($arr as $n=>$v): ?>
        <tr>
            <th><?php echo $n+1; ?></th>
            <?php for($x=0; $x<count($arr[0]); $x++): ?>
            <td>
                <?php echo $arr[$n][$x]; ?>
            </td>
            <?php endfor; ?>
        </tr>
    <?php endforeach; ?>
    </table>

    <script type="text/javascript">
    $(function(){
        $('.btn').click(function(){
            $.ajax({
                type:'post',
                url : 'ajax_mysql.php',
                dataType: 'json',
                success:function(data){
                    if(data==1){
                        layer.msg('导入成功');
                    }else if(data==0){
                        layer.msg('导入失败');
                    }
                }
            })
        })        
    })
    </script>
</body>
</html>

ajax_mysql.php

<?php 
$mysql=new mysqli('localhost','root','','excel');
$mysql->query('SET NAMES utf8');

include "./PHPExcel.php";
//初始化phpreader类
$phpreader = PHPExcel_IOFACTORY::createReader('excel2007');
//文件加载
$path = "./1.xlsx";
$phpexcel = PHPExcel_IOFACTORY::load($path);

$currentSheet = $phpexcel->getActiveSheet();

// 获取excel内容  第一种方法
// $content = $currentSheet->toArray();
//第二种获取内容
// 得到行数 列数
$rows = $currentSheet->getHighestRow();
$columns = $currentSheet->getHighestColumn();

for ($i=1; $i <=$rows ; $i++) {
    $str = '';//每行 
    for ($j='A'; $j <=$columns ; $j++) { 
        $adr = $j.$i;
        $data = $currentSheet->getCell($adr)->getValue();
        $str .= "'$data',";
    }
    $str = rtrim($str,',');
    $sql = "insert into users value($str)";
    if(!$mysql->query($sql)){
        echo 0;
        die;
    }
    echo $str."<br />";
}
echo 1;
die;

Mysql导出到excel

<?php 
$mysql=new mysqli('localhost','root','','excel');
$mysql->query('SET NAMES utf8');

$sql = "select * from users";
$res = $mysql->query($sql);
$arr = array();

$newarr = array();
while ($fileds = $res->fetch_field()) {
    $newarr[] = $fileds->name;
}
$arr[] = $newarr;

while ($row = $res->fetch_row()) {
    $a=array();
    foreach ($row as $k => $v) {
        $a[] = $v;
    }
    $arr[] = $a;
}



 ?>
<!DOCTYPE HTML>
<html lang="en-US">
<head>
    <meta charset="UTF-8">
    <title></title>
</head>
<body>
    <table>
        <tr>
            <?php foreach($arr[0] as $fv): ?>
            <th><?php echo $fv; ?></th>
            <?php endforeach; ?>
        </tr>

        <?php for($i=1; $i<count($arr); $i++): ?>
        <tr>
            <?php foreach($arr[$i] as $iv): ?>
            <td><?php echo $iv; ?></td>
            <?php endforeach; ?>
        </tr>
        <?php endfor; ?>
    </table>
    
    <script type="text/javascript">
    $(function(){
        $('.btn').click(function(){
            $.ajax({
                type:'post',
                url : 'ajax_excel.php',
                dataType: 'json',
                success:function(data){
                    if(data==1){
                        layer.msg('导入成功');
                    }else if(data==0){
                        layer.msg('导入失败');
                    }
                }
            })
        })        
    })
    </script>
</body>
</html>

ajax_excel.php

<?php 
$mysql=new mysqli('localhost','root','','excel');
$mysql->query('SET NAMES utf8');

$sql = "select * from users";
$res = $mysql->query($sql);
$arr = array();

$newarr = array();
while ($fileds = $res->fetch_field()) {
    $newarr[] = $fileds->name;
}
$arr[] = $newarr;

while ($row = $res->fetch_row()) {
    $a=array();
    foreach ($row as $k => $v) {
        $a[] = $v;
    }
    $arr[] = $a;
}

include "./PHPExcel.php";

$phpexcel = new PHPExcel();
//初始化phpwriter类对象
$phpwriter = PHPExcel_IOFACTORY::createWriter($phpexcel, 'excel2007');
//得到当前工作表对象
$currentSheet = $phpexcel->getActiveSheet();

$chars = "ABCD";
for ($i=0; $i <count($arr) ; $i++) { 
    for ($j=0; $j <count($arr[0]) ; $j++) { 
        $adr = $chars[$j].($i+1);
        $val=$arr[$i][$j];
        if(!$currentSheet->setCellValue($adr,$val)){
            echo 0;
            die;
        }
    }
}

$phpwriter->save('excel.xlsx');

echo 1;
die;

更多功能参考PHPExcel文档、Example

Tags: PHP, 笔记

仅有一条评论

  1. IceCry

    PHPExcel 导出excel文件 打开显示“发现不可读取的内容”

    解决方法：调用$objWriter->save('php://output')之后一定要紧跟exit

    回复

添加新评论