PHPExcel生成和读取Excel文件［实例］
星期四,2010 九 2 16:03:52
在网站的管理后台经常会使用到报表的生成和读取，CSV和Excel都是常用的报表格式，CSV相对来说比较简单，如果大家有疑问我会相继发布一些CSV的实例，这里主要介绍用PHP 来生成和读取Excel文件。
要执行下面的函数，首先要引入一个类库：PHPExcel，PHPExcel是一个强大的PHP类库，用来读写不同的文件格式，比如说Excel 2007，PDF格式，HTML格式等等，这个类库是建立在Microsoft’s OpenXML和PHP 的基础上的，对Excel提供的强大的支持，比如设置工作薄，字体样式，图片以及边框等等，下面来看看它是如何读写Excel文件的：
首先来看如果生成Excel文件：
下面这代码中函数arrayToExcel的功能是把一个二维数组的数据生成一个excel文件，并且保存在服务器上。
require_once 'Classes/PHPExcel/Reader/Excel2007.php';
require_once 'Classes/PHPExcel/Reader/Excel5.php';
include 'Classes/PHPExcel/IOFactory.php';
function arrayToExcel($data){
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('firstsheet');
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
//add data
$i = 2;
foreach ($data as $line){
$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $line['From']);
$objPHPExcel->getActiveSheet()->getCell('A'.$i)->setDataType('n');
$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $line['To']);
$objPHPExcel->getActiveSheet()->getCell('B'.$i)->setDataType('n');
$i++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$file = 'excel.xls';
$objWriter->save($file);
}
如果你不希望保存在服务器上，希望生成以后直接下载到客户端，可以在输出文件的时候加入下面的代码，而不使用 $objWriter->save($file);
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="excel.xls"');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
接下来看一个读取Excel文件内容的实例：
下面这代码中函数excelToArray的功能是把一个excel里的内容重新整理放到一个数组了。
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
function excelToArray($file){
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($file);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$excelData = array();
for ($row = 2; $row <= $highestRow; ++$row) {
for ($col = 0; $col <= $highestColumnIndex; ++$col) { $excelData[$row][] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
return $excelData;
}
相关文章导读
下一篇：腾讯web2.0横空出世
来看看有更新没
[回复]
依洋 回复:
九月 3rd, 2010 at 12:48 上午
谢谢，终于有了，最近好忙，都没时间更新博客
[回复]
向楼主学习了，同时也希望博主有时间对小站多多指点www.jnzsyy.com
[回复]
天天来学习，祝博主开心……
[回复]
PHP高手啊，我就懂点皮毛。好久没学习了。
[回复]
看看文章。。哈哈
[回复]
太技术了，看不懂哇。
[回复]
恩 忙的又不见人影了！
[回复]
依洋 回复:
九月 7th, 2010 at 3:51 下午
人影来了，哈哈
[回复]
第一次光顾，留下个脚印吧！
[回复]
看望朋友.只是一个轻轻的问候.
却是一片浓浓的真情.
淡淡的牵挂时时萦绕在我的心里.
祝亲爱的朋友幸福快乐
[回复]
hi wats your myspace page
[回复]
好文章，顶。
[回复]
博主这篇博文真的很有用 感谢了
[回复]
到这里来进修，好东西哈。
[回复]
看文章lvsen网 学到了。。哈哈
[回复]
哈哈，刚配了个8线程的CPU，高兴之余，来爽爽。
[回复]
技术性文章，支持了
[回复]
大拿网你知道么?www.danawa.com.cn
[回复]
依洋 回复:
九月 9th, 2010 at 11:34 下午
又是一个购物网啊！
[回复]
不错不错
[回复]
不是wap页也祝你新年愉快，哈哈~~~
[回复]
不会这个 看不懂了！
[回复]
特意回来留言!看了一个下午,还是博主的最贴切!谢谢!
[回复]
give me now
[回复]
great one
[回复]
Most of the times i visit a blog I see that the construction is poor and the writting bad. Regarding your blog,I could honestly say that you writting is decent and your website solid.
[回复]
依洋 回复:
十一月 24th, 2010 at 11:50 上午
Thanks. Hope it can help you.
[回复]
This blog appears to get a great deal of visitors. How do you promote it? It gives a nice unique twist on things. I guess having something authentic or substantial to talk about is the most important thing.
[回复]
I found your blog using Google and I must say this is one of the most informative blogs I have read in a while. I will make sure I come back to read your future posts.
[回复]
Hi, you’ve wrote a very excellent article. Is it ok if I hire you to write some articles for me?
If you know any of these topics:
Is 2012 end of the world? Bill Clinton’s And The Nation, Should weapons be allowed? How To Get Rid of Acne , McDonald’s Dominance, Is Asia going broke?
With Regards from
Alice – alicebaker526@yahoo.com
[回复]
Hello! I don’t usually leave comments on weblogs but I thought I’d just say hi. It’s apparent that you are putting a lot of time into your weblog. So continue with the good work! I look forward to your other posts. Good luck!
[回复]
I have been checking out many of your articles and i must say clever stuff. I will surely bookmark your blog.
[回复]
nice y0u hit it 0n the dot will submit to twitter
[回复]
Thanks for sharing that, I guess this can go both sides. Kinda off-topic but the website looks great on my iPad but you should make a App for your blog i like iPad apps, the big buttons on your site come in handy on this thing, keep up the good work.
[回复]
Bookmarked, I love your blog! :)
[回复]
Just wanted to let you know that I enjoy reading your posts. Don’t have much to add, cheers!
[回复]
I like this post.. i often read your posts, but don’t leave any comments, may as well start from today. Mary
[回复]
This place is awesome, I like what you’re doing here. Keep them coming.
[回复]
Very good written article. It will be valuable to everyone who employess it, including me. Keep up the good work – can’r wait to read more posts.
[回复]
Just love commenting for the sake of it. It makes whoever posted the article feel like someone cares… and we do!
[回复]
This webpage has a lot good information on it, I check on it everyday. I wish other sites spent as much effort as this one does making information “to the point” to readers like myself. I recommend this site to all of my facebook friends. This webpage will make some massive passive profit I’m positive.
[回复]
I have been checking out some of your stories and i can state pretty good stuff. I will definitely bookmark your website.
[回复]
Not bad. Some more detail would be even better.
[回复]
This website has a lot good information on it, I check on it whenever I’m online. I wish other sites spent as much blood sweat and tears as this one does making information legible to readers like myself. I recommend this page to all of my facebook friends. This website will make some massive passive profit I’m positive.
[回复]
a great post added to resource for one of my articles
[回复]
I am very impress your this post, this is too much interesting. Thanks a lot…
[回复]
This domain appears to get a large ammount of visitors. How do you promote it? It gives a nice individual twist on things. I guess having something real or substantial to talk about is the most important thing.
[回复]
We really do look forward to your posts, thanks to you!
[回复]
You really make it seem so easy with your presentation but I find this topic to be really something which I think I would never understand.
[回复]
thanks a lot!
[回复]
依洋 回复:
三月 4th, 2011 at 12:32 下午
You are welcome!
[回复]
3Q
[回复]