PHPExcel操作xls文件，

PHPExcel操作xls文件，

读取中文的xls、csv文件会有问题，网上找了下资料，发现PHPExcel类库好用，官网地址：http://phpexcel.codeplex.com/ 

1、读取xls文件内容

<?<span>php
    
    </span><span>//</span><span>读取xls</span>
    
    <span>header</span>("Content-Type:text/html;charset=utf-8"<span>);
    </span><span>include</span> 'Classes/PHPExcel.php'<span>;            
    </span><span>include</span> 'Classes/PHPExcel/IOFactory.php'<span>;

    </span><span>function</span> readxls(<span>$file</span>, <span>$type</span><span>) {
        </span><span>$xlsReader</span> = PHPExcel_IOFactory::createReader(<span>$type</span><span>);  
        </span><span>$xlsReader</span>->setReadDataOnly(<span>true</span><span>);
        </span><span>$xlsReader</span>->setLoadSheetsOnly(<span>true</span><span>);
        </span><span>$sheets</span> = <span>$xlsReader</span>->load(<span>$file</span><span>);
        </span><span>$content</span> = <span>$sheets</span>->getSheet(0)->toArray(); <span>//</span><span>读取第一个工作表(注意编号从0开始) 如果读取多个可以做一个循环0,1,2,3....
        //得到二维数组,每个小数组是excel表格内容的一行 里面包含此行的每列的数据  </span>
        <span>return</span> <span>$content</span><span>;
    }
    
    </span><span>//</span><span>$type = 'Excel2007'; //设置要解析的Excel类型 Excel5(2003或以下版本)或Excel2007</span>
    <span>$type</span> = 'Excel5'<span>;
    </span><span>$content</span> = readxls('data.xls', <span>$type</span><span>);
    </span><span>echo</span> '<pre class="brush:php;toolbar:false">'<span>;
    </span><span>var_dump</span>(<span>$content</span><span>);
    </span><span>echo</span> '

'; ?>

2、向xls文件写内容

 

<?<span>php

    </span><span>//</span><span>向xls文件写入内容</span>
    
    <span>error_reporting</span>(<span>E_ALL</span><span>);
    </span><span>ini_set</span>('display_errors', <span>TRUE</span><span>);
    
    </span><span>include</span> 'Classes/PHPExcel.php'<span>;            
    </span><span>include</span> 'Classes/PHPExcel/IOFactory.php'<span>;
    
    </span><span>//</span><span>$data:xls文件内容正文
    //$title:xls文件内容标题
    //$filename:导出的文件名
    //$data和$title必须为utf-8码，否则会写入FALSE值</span>
    <span>function</span> write_xls(<span>$data</span>=<span>array</span>(), <span>$title</span>=<span>array</span>(), <span>$filename</span>='report'<span>){
        </span><span>$objPHPExcel</span> = <span>new</span><span> PHPExcel();
        </span><span>//</span><span>设置文档属性，设置中文会产生乱码，待完善...
        // $objPHPExcel->getProperties()->setCreator("云舒")
                             // ->setLastModifiedBy("云舒")
                             // ->setTitle("产品URL导出")
                             // ->setSubject("产品URL导出")
                             // ->setDescription("产品URL导出")
                             // ->setKeywords("产品URL导出");</span>
        <span>$objPHPExcel</span>->setActiveSheetIndex(0<span>);
        
        </span><span>$cols</span> = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'<span>;
        </span><span>//</span><span>设置标题</span>
        <span>for</span>(<span>$i</span>=0,<span>$length</span>=<span>count</span>(<span>$title</span>); <span>$i</span><<span>$length</span>; <span>$i</span>++<span>) {
            </span><span>//</span><span>echo $cols{$i}.'1';</span>
            <span>$objPHPExcel</span>->getActiveSheet()->setCellValue(<span>$cols</span>{<span>$i</span>}.'1', <span>$title</span>[<span>$i</span><span>]);
        }
        </span><span>//</span><span>设置标题样式</span>
        <span>$titleCount</span> = <span>count</span>(<span>$title</span><span>);
        </span><span>$r</span> = <span>$cols</span>{0}.'1'<span>;
        </span><span>$c</span> = <span>$cols</span>{<span>$titleCount</span>}.'1'<span>;
        </span><span>$objPHPExcel</span>->getActiveSheet()->getStyle("<span>$r</span>:<span>$c</span>")-><span>applyFromArray(
            </span><span>array</span><span>(
                </span>'font'    => <span>array</span><span>(
                    </span>'bold'      => <span>true</span><span>
                )</span>,
                'alignment' => <span>array</span><span>(
                    </span>'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,<span>
                )</span>,
                'borders' => <span>array</span><span>(
                    </span>'top'     => <span>array</span><span>(
                        </span>'style' => PHPExcel_Style_Border::<span>BORDER_THIN
                    )
                )</span>,
                'fill' => <span>array</span><span>(
                    </span>'type'       => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                    'rotation'   => 90,
                    'startcolor' => <span>array</span><span>(
                        </span>'argb' => 'FFA0A0A0'<span>
                    )</span>,
                    'endcolor'   => <span>array</span><span>(
                        </span>'argb' => 'FFFFFFFF'<span>
                    )
                )
            )
        );
        
        </span><span>for</span>(<span>$i</span>=0,<span>$length</span>=<span>count</span>(<span>$data</span>); <span>$i</span><<span>$length</span>; <span>$i</span>++<span>) {
            </span><span>$j</span> = 0<span>;
            </span><span>foreach</span>(<span>$data</span>[<span>$i</span>] <span>as</span> <span>$v</span>) {   <span>//</span><span>这里用foreach,支持关联数组和数字索引数组</span>
                <span>$objPHPExcel</span>->getActiveSheet()->setCellValue(<span>$cols</span>{<span>$j</span>}.(<span>$i</span>+2), <span>$v</span><span>);
                </span><span>$j</span>++<span>;
            }
        }
        </span><span>//</span><span> 生成2003excel格式的xls文件</span>
        <span>header</span>('Content-Type: application/vnd.ms-excel'<span>);
        </span><span>header</span>('Content-Disposition: attachment;filename="'.<span>$filename</span>.'.xls"'<span>);
        </span><span>header</span>('Cache-Control: max-age=0'<span>);

        </span><span>$objWriter</span> = PHPExcel_IOFactory::createWriter(<span>$objPHPExcel</span>, 'Excel5'<span>);
        </span><span>$objWriter</span>->save('php://output'<span>);
    }
    
    </span><span>$array</span> = <span>array</span><span>(
        </span><span>array</span>(1111,'名称','品牌','商品名','http://www.baidu.com'),
        <span>array</span>(1111,'名称','品牌','商品名','http://www.baidu.com'),
        <span>array</span>(1111,'名称','品牌','商品名','http://www.baidu.com'),
        <span>array</span>(1111,'名称','品牌','商品名','http://www.baidu.com'),
        <span>array</span>(1111,'名称','品牌','商品名','http://www.baidu.com'),<span>
    );
    write_xls(</span><span>$array</span>,<span>array</span>('商品id','供应商名称','品牌','商品名','URL'),'report'<span>);
    
</span>?>
    

 

3、操作数据库获取要写入的内容 举个使用mysqli的预处理获取内容的例子：

<?<span>php

    </span><span>//</span><span>获取数据库数据(mysqli预处理学习)</span>
    <span>$config</span> = <span>array</span><span>(
        </span>'DB_TYPE'=>'mysql',
        'DB_HOST'=>'localhost',
        'DB_NAME'=>'test',
        'DB_USER'=>'root',
        'DB_PWD'=>'root',
        'DB_PORT'=>'3306',<span>
    );
    </span><span>function</span> getProductIdByName(<span>$name</span><span>) {
        </span><span>global</span> <span>$config</span><span>;
        </span><span>$id</span> = <span>false</span><span>;
        
        </span><span>$mysqli</span> = <span>new</span> mysqli(<span>$config</span>['DB_HOST'], <span>$config</span>['DB_USER'], <span>$config</span>['DB_PWD'], <span>$config</span>['DB_NAME'<span>]);
        </span><span>if</span>(<span>mysqli_connect_error</span>()) {   <span>//</span><span>兼容 < php5.2.9 OO way:$mysqli->connect_error</span>
            <span>die</span>("连接失败，错误码：".<span>mysqli_connect_errno</span>()."错误信息：".<span>mysqli_connect_error</span><span>());
        }
        </span><span>//</span><span>设置连接数据库的编码，不要忘了设置</span>
        <span>$mysqli</span>->set_charset("gbk"<span>);
        </span><span>//</span><span>中文字符的编码要与数据库一致，若没设置，结果为null</span>
        <span>$name</span> = <span>iconv</span>("utf-8", "gbk//IGNORE", <span>$name</span><span>);
        </span><span>if</span>(<span>$mysqli_stmt</span> = <span>$mysqli</span>->prepare("select id from 137_product where name like ?"<span>)) {
            </span><span>$mysqli_stmt</span>->bind_param("s", <span>$name</span><span>);
            </span><span>$mysqli_stmt</span>-><span>execute();
            </span><span>$mysqli_stmt</span>->bind_result(<span>$id</span><span>);
            </span><span>$mysqli_stmt</span>-><span>fetch();
            </span><span>$mysqli_stmt</span>-><span>close();
        }
        </span><span>$mysqli</span>-><span>close();
        
        </span><span>return</span> <span>$id</span><span>;
    }
    
    </span><span>$id</span> = getProductIdByName('%伊奈卫浴伊奈分体座便器%'<span>);
    </span><span>var_dump</span>(<span>$id</span><span>);
</span>?>

OK...

参考资料： http://blog.sina.com.cn/s/blog_44b3f96d0101cczo.html http://phpexcel.codeplex.com/