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;
}

相关文章导读

这篇文章已有 53 位网友发表了评论  

  1. 荆川村委 说:

    来看看有更新没

    [回复]

    依洋 回复:

    谢谢,终于有了,最近好忙,都没时间更新博客

    [回复]

  2. 向楼主学习了,同时也希望博主有时间对小站多多指点www.jnzsyy.com

    [回复]

  3. 天天来学习,祝博主开心……

    [回复]

  4. 任鸟飞 说:

    PHP高手啊,我就懂点皮毛。好久没学习了。

    [回复]

  5. taobao 说:

    看看文章。。哈哈

    [回复]

  6. 崔可 说:

    太技术了,看不懂哇。

    [回复]

  7. 恩 忙的又不见人影了!

    [回复]

    依洋 回复:

    人影来了,哈哈

    [回复]

  8. 上海家具 说:

    第一次光顾,留下个脚印吧!

    [回复]

  9. 海藻面膜 说:

    看望朋友.只是一个轻轻的问候.

    却是一片浓浓的真情.

    淡淡的牵挂时时萦绕在我的心里.

    祝亲爱的朋友幸福快乐

    [回复]

  10. hi wats your myspace page

    [回复]

  11. 梦芭莎 说:

    好文章,顶。

    [回复]

  12. 博主这篇博文真的很有用 感谢了

    [回复]

  13. 弱电生活 说:

    到这里来进修,好东西哈。

    [回复]

  14. 绿森数码 说:

    看文章lvsen网 学到了。。哈哈

    [回复]

  15. 哈哈,刚配了个8线程的CPU,高兴之余,来爽爽。

    [回复]

  16. nikeairmax 说:

    技术性文章,支持了

    [回复]

  17. 比较好 说:

    大拿网你知道么?www.danawa.com.cn

    [回复]

    依洋 回复:

    又是一个购物网啊!

    [回复]

  18. 正品淘 说:

    不是wap页也祝你新年愉快,哈哈~~~

    [回复]

  19. nikeairmax 说:

    不会这个 看不懂了!

    [回复]

  20. 特意留言 说:

    特意回来留言!看了一个下午,还是博主的最贴切!谢谢!

    [回复]

  21. Lottie Vann 说:

    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.

    [回复]

  22. Alex Laird 说:

    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.

    [回复]

  23. 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.

    [回复]

  24. 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

    [回复]

  25. PLR 说:

    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!

    [回复]

  26. I have been checking out many of your articles and i must say clever stuff. I will surely bookmark your blog.

    [回复]

  27. nice y0u hit it 0n the dot will submit to twitter

    [回复]

  28. ipad kopen 说:

    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.

    [回复]

  29. Bookmarked, I love your blog! :)

    [回复]

  30. Just wanted to let you know that I enjoy reading your posts. Don’t have much to add, cheers!

    [回复]

  31. I like this post.. i often read your posts, but don’t leave any comments, may as well start from today. Mary

    [回复]

  32. This place is awesome, I like what you’re doing here. Keep them coming.

    [回复]

  33. 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.

    [回复]

  34. Just love commenting for the sake of it. It makes whoever posted the article feel like someone cares… and we do!

    [回复]

  35. 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.

    [回复]

  36. I have been checking out some of your stories and i can state pretty good stuff. I will definitely bookmark your website.

    [回复]

  37. Narcissistic 说:

    Not bad. Some more detail would be even better.

    [回复]

  38. Modesto Garan 说:

    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.

    [回复]

  39. a great post added to resource for one of my articles

    [回复]

  40. Elvis Heffley 说:

    I am very impress your this post, this is too much interesting. Thanks a lot…

    [回复]

  41. Reed Lobstein 说:

    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.

    [回复]

  42. Robb 说:

    We really do look forward to your posts, thanks to you!

    [回复]

  43. 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.

    [回复]

  44. thanks a lot!

    [回复]

    依洋 回复:

    You are welcome!

    [回复]

发表新的评论



注意:
1、本站启用了审核机制,你的留言可能稍后才会显示,请不要重复提交,谢谢。
2、留言时的头像是Gravatar提供的服务。想设置的看这里
3、评论者允许使用'@user空格'的方式将自己的评论通知另外评论者。