本文实例讲述了PHP中使用PHPExcel读写excel(xls)文件的方法,非常实用。分享给大家供大家参考之用。具体方法如下:
很多PHP类库在读取中文的xls、csv文件时会有问题,网上找了下资料,发现PHPExcel类库好用,官网地址为:http://phpexcel.codeplex.com/。现将PHPExcel读写Excel的方法分别叙述如下:
1、读取xls文件内容
//向xls文件写入内容
error_reporting(E_ALL);
ini_set("display_errors", TRUE);
include "Classes/PHPExcel.php";
include "Classes/PHPExcel/IOFactory.php";
//$data:xls文件内容正文
//$title:xls文件内容标题
//$filename:导出的文件名
//$data和$title必须为utf-8码,否则会写入FALSE值
function write_xls($data=array(), $title=array(), $filename="report"){
$objPHPExcel = new PHPExcel();
//设置文档属性,设置中文会产生乱码,需要转换成utf-8格式!!
// $objPHPExcel->getProperties()->setCreator("云舒")
// ->setLastModifiedBy("云舒")
// ->setTitle("产品URL导出")
// ->setSubject("产品URL导出")
// ->setDescription("产品URL导出")
// ->setKeywords("产品URL导出");
$objPHPExcel->setActiveSheetIndex(0);
$cols = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
//设置www.yinxi.net标题
for($i=0,$length=count($title); $i
//echo $cols{$i}."1";
$objPHPExcel->getActiveSheet()->setCellValue($cols{$i}."1", $title[$i]);
}
//设置标题样式
$titleCount = count($title);
$r = $cols{0}."1";
$c = $cols{$titleCount}."1";
$objPHPExcel->getActiveSheet()->getStyle("$r:$c")->applyFromArray(
array(
"font" => array(
"bold" => true
),
"alignment" => array(
"horizontal" => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
),
"borders" => array(
"top" => array(
"style" => PHPExcel_Style_Border::BORDER_THIN
)
),
"fill" => array(
"type" => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
"rotation" => 90,
"startcolor" => array(
"argb" => "FFA0A0A0"
),
"endcolor" => array(
"argb" => "FFFFFFFF"
)
)
)
);
$i = 0;
foreach($data as $d) { //这里用foreach,支持关联数组和数字索引数组
$j = 0;
foreach($d as $v) { //这里用foreach,支持关联数组和数字索引数组
$objPHPExcel->getActiveSheet()->setCellValue($cols{$j}.($i+2), $v);
$j++;
}
$i++;
}
// 生成2003excel格式的xls文件
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment;filename="".$filename.".xls"");
header("Cache-Control: max-age=0");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
$objWriter->save("php://output");
}
$array = array(
array(1111,"名称","品牌","商品名","//www.yinxi.net"),
array(1111,"名称","品牌","商品名","//www.yinxi.net"),
array(1111,"名称","品牌","商品名","//www.yinxi.net"),
array(1111,"名称","品牌","商品名","//www.yinxi.net"),
array(1111,"名称","品牌","商品名","//www.yinxi.net"),
);
write_xls($array,array("商品id","供应商名称","品牌","商品名","URL"),"report");
?>
2、向xls文件写内容
//获取数据库数据(mysqli预处理学习)
$config = array(
"DB_TYPE"=>"mysql",
"DB_HOST"=>"localhost",
"DB_NAME"=>"test",
"DB_USER"=>"root",
"DB_PWD"=>"root",
"DB_PORT"=>"3306",
);
function getProductIdByName($name) {
global $config;
$id = false;
$mysqli = new mysqli($config["DB_HOST"], $config["DB_USER"], $config["DB_PWD"], $config["DB_NAME"]);
if(mysqli_connect_error()) { //兼容 < php5.2.9 OO way:$mysqli->connect_error
die("连接失败,错误码:".mysqli_connect_errno()."错误信息:".mysqli_connect_error());
}
//设置连接数据库的编码,不要忘了设置
$mysqli->set_charset("gbk");
//中文字符的编码要与数据库一致,若没设置,结果为null
$name = iconv("utf-8", "gbk//IGNORE", $name);
if($mysqli_stmt = $mysqli->prepare("select id from 137_product where name like ?")) {
$mysqli_stmt->bind_param("s", $name);
$mysqli_stmt->execute();
$mysqli_stmt->bind_result($id);
$mysqli_stmt->fetch();
$mysqli_stmt->close();
}
$mysqli->close();
return $id; //得到的是gbk码(同数据库编码)
}
$id = getProductIdByName("%伊奈卫浴伊奈分体座便器%");
var_dump($id);
?>
希望本文所述对大家的PHP程序设计有所帮助