//PHPExcel写入数据，输出到浏览器
$dir=dirname(__file__);
require $dir."./PHPExcel/PHPExcel_1.8.0_doc/PHPExcel.php";
require $dir."./config.php";
require $dir."./DB.class.php";
$pe= new PHPExcel();//实例化PHPExcel类，相当于创建表格，默认建立并激活了一个sheet.
$peSheet=$pe->getActiveSheet();//获得当前活动sheet的操作对象
$peSheet->setTitle('Demosheet');//给当前活动表设置表名Title
//向单元格写入数据
$peSheet->setCellValue("A1","姓名")->setCellValue("B1","分数");
$peSheet->setCellValue("A2","张三")->setCellValue("B2","99");
//使用数组形式搭配fromArray方法写入数据，每一个子数组为一行
$array = array(
array("姓名","分数","年龄"),//第一行
array("张三","55","23"),//第2行
array("刘四","66","20"),//第3行
array("王五","77","16"),
array("赵六","88","15")
);
$peSheet->fromArray($array);//数据量大的话不建议使用数据块
$objWrite=PHPExcel_IOFactory::createWriter($pe,'Excel5');// 写入格式设定
function browseroutput($type,$filename){
if($type=='Excel5'){
header('Content-Type: application/vnd.ms-excel');
}else{
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
}
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
}
$filename='outputtobrowser.xlsx';
$objWrite->save('php://output');
browseroutput('Excel5',$filename);
$objWrite->save('php://output');
exit;
//*****************************************************************************//
// //PHPExcel从数据库读取数据，写入到excel文件，写入时分循环逐个单元格写入%E5%92%8C%E4%B8%80%E6%AC%A1%E6%80%A7%E5%86%99%E5%85%A5%E4%BA%8C%E7%BB%B4%E6%95%B0%E7%BB%84%E5%86%99%E5%85%A5)
pSheet->fromArray($res);
$db= new DB();
// $sql='select * from users';
// $res=$db->query($sql);
// $res=$db->getRow('users',1);
$res=$db->getAll('users');
echo '<pre>';
print_r($res);
echo '<hr>';
echo count($res);
echo '</pre>';
$phpExcel= new PHPExcel();
$pSheet=$phpExcel->getActiveSheet();
$pSheet->setTitle('用户表');//给当前活动表设置表名Title
/**逐行写入数据到表格**/
// $j=2;
//设置表头
// $pSheet->setCellValue('A1','序号')->setCellValue('B1','用户名')->setCellValue('C1','密码')->setCellValue('D1','角色');
//**循环写入行数据**//
// for($i=0;$i<count($res);$i++){
// $pSheet->setCellValue('A'.$j,$res[$i]['id'])->setCellValue('B'.$j,$res[$i]['username'])->setCellValue('C'.$j,$res[$i]['password'])->setCellValue('D'.$j,$res[$i]['role']);
// $j++;
// };
/**对于数据量不大的内容，也可使用fromArray($res)写入数据到表格更单便捷**/
$pSheet->fromArray($res);
$objWriter=PHPExcel_IOFactory::createWriter($phpExcel,'Excel5');
$objWriter->save($dir."/user2.xls");
//PHPExcel读取excel表格中的数据，读取整个工作薄中全部表中的内容
//
header("content-type:text/html;charset=utf-8");
function getAllFromExcel(){
$dir=dirname(__FILE__);//当前脚本所在路径
require $dir."/PHPExcel/PHPExcel_1.8.0_doc/PHPExcel/IOFactory.php";//读取依赖加载
$filename = $dir."/readtest.xls";//要读取的文件
$objPHPExcel = PHPExcel_IOFactory::load($filename);//加载文件****全部加载*****，不管文件中有多少个表都加载
echo '测试一下,循环输出读取的数据<br>';
foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
//循环读取表
foreach ($sheet->getRowIterator() as $row) {//循环读取行
if($row->getRowIndex()<3){
continue;//跳过前两行，从第三行开始读取
}
foreach ($row->getCellIterator() as $cell) {
$data = $cell->getValue();//循环读取单元格
echo $data;
}
echo '<br>';
}
echo '<hr>';
}
};//待完善
//PHPExcel读取excel表格中的数据，按需读取内容，先在数组中指定要读取的表名
function getPartFromExcel(){//待完善
$dir=dirname(__FILE__);//当前脚本所在路径
require $dir."/PHPExcel/PHPExcel_1.8.0_doc/PHPExcel/IOFactory.php";//读取依赖加载
$filename = $dir."/readtest.xls";//要读取的文件
$filetype = PHPExcel_IOFactory::identify($filename);//自动识别获取文件的类型.
$objReader = PHPExcel_IOFactory::createReader($filetype);//创建reader对象.
// $sheetName = array('grade1','grade2','grade3');//指定要加载的表名
$sheetName = array('grade2');//根据需要指定要加载的表名
$objReader->setLoadSheetsOnly($sheetName);//只加载指定的sheet
$objPHPExcel = $objReader->load($filename);////加载文件****根据指定的表名加载*****，
echo '测试一下<br>';
foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
//循环读取表
echo '<table width="90%" id="mytab" border="1" class="t1">';
foreach ($sheet->getRowIterator() as $row) {//读取行
echo '<tr>';
if($row->getRowIndex()<3){
continue;//跳过前两行，从第三行开始读取
}
foreach ($row->getCellIterator() as $cell) {
$data = $cell->getValue();//读取单元格
if($data){};
echo '<td>';
echo $data;
echo '</td>';
}
echo '</tr>';
}
echo '</table>';
echo '<hr>';
}
};
//PHPExcel读取excel表格中的数据，按需读取内容，读取指定行列的数据
function getRange(){
$dir=dirname(__FILE__);//当前脚本所在路径
$inputFileName = $dir."/readtest.xls";//要读取的文件
$startRow=3;
$endRow=13;
$startColumn='A';
$endColumn='E';
require $dir."/PHPExcel/PHPExcel_1.8.0_doc/PHPExcel/IOFactory.php";//读取依赖加载
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
private $_startRow = 0; // 开始行
private $_endRow = 0; // 结束行
private $_columns = array(); // 列跨度
public function __construct($startRow, $endRow, $columns) {
$this->_startRow = $startRow;
$this->_endRow = $endRow;
$this->_columns = $columns;
}
public function readCell($column, $row, $worksheetName = '') {
if ($row >= $this->_startRow && $row <= $this->_endRow) {
if (in_array($column,$this->_columns)) {
return true;
}
}
return false;
}
}
$filterSubset = new MyReadFilter($startRow,$endRow,range($startColumn,$endColumn));
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$sheetName = array('grade2');
$objReader->setLoadSheetsOnly($sheetName);
$objReader->setReadFilter($filterSubset); // 设置实例化的过滤器对象
$objPHPExcel = $objReader->load($inputFileName);
foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
//循环读取表
echo '<table width="90%" id="mytab" border="1" class="t1">';
foreach ($sheet->getRowIterator() as $row) {//读取行
echo '<tr>';
// if($row->getRowIndex()<1){
// continue;//跳过前两行，从第三行开始读取
// }
foreach ($row->getCellIterator() as $cell) {
$data = $cell->getValue();//读取单元格
if($data){};
echo '<td>';
echo $data;
echo '</td>';
}
echo '</tr>';
}
echo '</table>';
echo '<hr>';
}
}
getRange();
DB类参考*******
class DB{
//定义私有属性
private $host;
private $port;
private $username;
private $password;
private $dbname;
private $charset;
private $dbtype;
private $pdo;
//定义构造函数自动加载配置文件
function __construct(){
//加载配置文件
// include_once('config.php');
$config=array(
'host'=>'localhost',
'port'=>3306,
'username'=>'root',
'password'=>'000000',
'dbname'=>'test',
'charset'=>'utf-8',
'dbtype'=>''
);
//给属性赋值
$this->host = $config['host'];
$this->username = $config['username'];
$this->password = $config['password'];
$this->charset = $config['charset'];
$this->port = $config['port'];
$this->dbname = $config['dbname'];
//pdo连接数据库
$this->pdo = new PDO('mysql:host='.$this->host.';dbname='.$this->dbname,$this->username,$this->password);
// $this->pdo = new PDO ( 'mysql:host=localhost;dbname=test' , $this->username, $this->password);
//发送编码
$this->pdo->query("set names $this->charset");
}
/**
* 定义执行查询sql语句的方法
* 参数： 查询sql语句
* 返回： 二维关联数组
*/
public function query($sql){
$res = $this->pdo->query($sql);
$res->setFetchMode(PDO::FETCH_ASSOC);
$arr = $res->fetchAll();
return $arr;
}
/**
* 查询一行记录的方法
* 参数：表名 条件(不包含where)
* 返回：一维关联数组
*/
public function getRow($tablename,$where){
//组装sql语句
$sql = "select * from $tablename where $where";
//查询
$res = $this->pdo->query($sql);
$res->setFetchMode(PDO::FETCH_ASSOC);
$arr = $res->fetch();
return $arr;
}
/**
* 查询全部记录
* 参数：表名
* 返回：二维关联数组
*/
public function getAll($tablename){
$res = $this->pdo->query("select * from $tablename");
$res->setFetchMode(PDO::FETCH_ASSOC);
$arr = $res->fetchAll();
return $arr;
}
/**
* 查询某个字段
* 参数： 字段名(多个的话用逗号隔开) 表名 条件（不含where）
* 返回： 二维关联数组
*/
public function getOne($column,$tablename,$where="1"){
//拼接sql语句
$sql = "select $column from $tablename where $where";
$rs = $this->pdo->query($sql);
$rs->setFetchMode(PDO::FETCH_ASSOC);
//$col = $rs->fetchColumn();
$col = $rs->fetchAll();
return $col;
}
/**
* 查询最后一次插入的数据
* 参数：表名
* 返回：数组
*/
public function getlastone($tablename){
$sql = "select * from $tablename where id=(select max(id) from $tablename)";
$res = $this->pdo->query($sql);
$res->setFetchMode(PDO::FETCH_ASSOC);
$arr = $res->fetch();
return $arr;
}
/**
* 向数据库中添加一条信息
* 参数：表名 一维关联数组
* 返回: 布尔值
*/
public function insert($tablename,$arr){
//拿到数组之后先处理数组 过滤字段
//取出表中的字段
$sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '$tablename' and table_schema ='$this->dbname'";
$columns = $this->pdo->query($sql);
$columns->setFetchMode(PDO::FETCH_ASSOC);
$columns = $columns->fetchAll();
$cols = array(); //存储表中的全部字段
foreach($columns as $key=>$val){
$cols[] = $val['COLUMN_NAME'];
}
//将要入库的数组进行键值分离
$keys = array();
$values = '';
foreach($arr as $k=>$v){
if(!in_array($k,$cols)){
unset($arr[$k]);
}else{
$keys[] = $k;
$values .= "'".$v."',";
}
}
$column = implode(',',$keys);
$values = substr($values,0,-1);
//拼接sql语句
$sql = "insert into $tablename($column) values ($values)";
$res = $this->pdo->exec($sql);
return $res;
}
/**
* 删除数据 其实就是改变字段值使之不被查询
* 参数：表名 条件(不含where)
* 返回：布尔
*/
public function delete($tablename,$where){
$sql = "update $tablename set is_del=1 where $where";
$res = $this->pdo->exec($sql);
return $res;
}
/**
* 修改数据
* 参数：表名 要修改的数据的数组
* 返回：布尔
*/
public function update($tablename,$arr,$where){
//处理传过来的数组
$str = "";
foreach($arr as $k=>$v){
$str .= "$k='".$v."',";
}
//截取字符串
$str = substr($str,0,-1);
//拼接sql语句
$sql = "update $tablename set $str where $where";
$res = $this->pdo->exec($sql);
return $res;
}
}
网友评论