在网站的管理后台经常会使用到报表的生成和读取,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;
}
来看看有更新没
谢谢,终于有了,最近好忙,都没时间更新博客
向楼主学习了,同时也希望博主有时间对小站多多指点www.jnzsyy.com
天天来学习,祝博主开心……
PHP高手啊,我就懂点皮毛。好久没学习了。
看看文章。。哈哈
太技术了,看不懂哇。
恩 忙的又不见人影了!
人影来了,哈哈
第一次光顾,留下个脚印吧!
看望朋友.只是一个轻轻的问候.
却是一片浓浓的真情.
淡淡的牵挂时时萦绕在我的心里.
祝亲爱的朋友幸福快乐
hi wats your myspace page
好文章,顶。
博主这篇博文真的很有用 感谢了
到这里来进修,好东西哈。
看文章lvsen网 学到了。。哈哈
哈哈,刚配了个8线程的CPU,高兴之余,来爽爽。
技术性文章,支持了
大拿网你知道么?www.danawa.com.cn
又是一个购物网啊!
不错不错
不是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.
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!
You are welcome!
3Q