Chinaunix首页 | 论坛 | 博客

晓飞的博客chenxf.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

magentoboy

  • 博客访问： 273320
  • 博文数量： 59
  • 博客积分： 1368
  • 博客等级： 中尉
  • 技术积分： 1071
  • 用 户 组： 普通用户
  • 注册时间： 2012-02-02 06:06
文章分类

全部博文（59）

文章存档

2012年（59）

我的朋友
最近访客
推荐博文
相关博文
php csv excel

分类： 系统运维

2012-02-16 14:55:41

1 使用fgetcsv函数
array fgetcsv ( resource $handle [, int $length = 0 [, string $delimiter = ',' [, string $enclosure = '"' [, string $escape = '\\' ]]]] )
  1. <?php
  2. $row = 1;
  3. if (($handle = fopen("test.csv", "r")) !== FALSE) {
  4.     while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
  5.         $num = count($data);
  6.         echo "

    $num fields in line $row:

    \n"    ;
  7.         $row++;
  8.         for ($c=0; $c < $num; $c++) {
  9.             echo $data[$c] . "
    \n"    ;
  10.         }
  11.     }
  12.     fclose($handle);
  13. }
  14. ?>
2 phpExcelReader

3 PHPExcel

3.1 使用PHPExcel_IOFactory读取
  1. <?php
  2. require_once 'PHPExcel/IOFactory.php';
  3. $objPHPExcel = PHPExcel_IOFactory::load("test.xlsx");
  4. //$sheet = $objPHPExcel->getSheet(0);

  6. foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
  7.     $worksheetTitle = $worksheet->getTitle();
  8.     $highestRow = $worksheet->getHighestRow(); // e.g. 10
  9.     $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
  10.     $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
  11.     $nrColumns = ord($highestColumn) - 64;
  12.     echo "
    The worksheet "    .$worksheetTitle." has ";
  13.     echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
  14.     echo ' and ' . $highestRow . ' row.';
  15.     echo '
    Data: ';
  16.     for ($row = 1; $row <= $highestRow; ++ $row) {
  17.         echo '
    18. ';
  18.         for ($col = 0; $col < $highestColumnIndex; ++ $col) {
  19.             $cell = $worksheet->getCellByColumnAndRow($col, $row);
  20.             $val = $cell->getValue();
  21.             $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
  22.             echo '
    23. ';
  23.         }
  24.         echo '
    25. ';
  25.     }
  26.     echo '
    27. ' . $val . '
    (Typ '     . $dataType . ')
    '    ;
  27. }
3.2使用PHPExcel_Reader_Excel2007读取
  1. <?php
  2. include 'PHPExcel.php';
  3. include 'PHPExcel/Reader/Excel2007.php';
  4. include 'PHPExcel/Reader/Excel5.php';
  5. $fileName = 'test.xlsx';

  7. $PHPExcel = new PHPExcel();

  9. $PHPReader = new PHPExcel_Reader_Excel2007();
  10. if (!$PHPReader->canRead($fileName))
  11. {
  12.     $PHPReader = new PHPExcel_Reader_Excel5();
  13.     if (!$PHPReader->canRead($fileName))
  14.     {
  15.         echo 'no Excel';
  16.         exit;
  17.     }
  18. }

  20. $PHPExcel = $PHPReader->load($fileName);
  21. $currentSheet = $PHPExcel->getSheet(0);
  22. $allColumn = $currentSheet->getHighestColumn();
  23. $allRow = $currentSheet->getHighestRow();

  25. for($currentRow = 2; $currentRow<=$allRow; $currentRow++)
  26. {
  27.     for($currentColumn='A'; $currentColumn<=$allColumn; $currentColumn++)
  28.     {
  29.         $address = $currentColumn.$currentRow;
  30.         echo $currentSheet->getCell($address)->getValue()."\t";
  31.     }
  32.     echo "
    "    ;
  33. }
3.3 使用RowIterator和CellIterator迭代读取
  1. <?php

  3. error_reporting(E_ALL);
  4. ini_set('include_path', ini_get('include_path').';./Classes/');

  6. include 'PHPExcel.php';
  7. include 'PHPExcel/Reader/Excel2007.php';

  9. $objReader = new PHPExcel_Reader_Excel2007();
  10. $objReader->setReadDataOnly(true);
  11. $objPHPExcel = $objReader->load('test.xlsx');
  12. $rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
  13. $sheet = $objPHPExcel->getActiveSheet();
  14. $array_data = array();

  16. foreach($rowIterator as $row){
  17.     $cellIterator = $row->getCellIterator();
  18.     $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
  19.     if(1 == $row->getRowIndex ()) continue;//skip first row
  20.     $rowIndex = $row->getRowIndex ();
  21.     $array_data[$rowIndex] = array('A'=>'', 'B'=>'','C'=>'','D'=>'');
  22.  
  23.     foreach ($cellIterator as $cell) {
  24.         if('A' == $cell->getColumn()){
  25.             $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
  26.         } else if('B' == $cell->getColumn()){
  27.             $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
  28.         } else if('C' == $cell->getColumn()){
  29.             $array_data[$rowIndex][$cell->getColumn()] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'YYYY-MM-DD');
  30.         } else if('D' == $cell->getColumn()){
  31.             $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
  32.         }
  33.     }
  34.     /*
  35.     $rowIndex = $row->getRowIndex ();
  36.     $array_data[$rowIndex] = array('A'=>'', 'B'=>'','C'=>'','D'=>'');
  37.  
  38.     $cell = $sheet->getCell('A' . $rowIndex);
  39.     $array_data[$rowIndex]['A'] = $cell->getCalculatedValue();
  40.     $cell = $sheet->getCell('B' . $rowIndex);
  41.     $array_data[$rowIndex]['B'] = $cell->getCalculatedValue();
  42.     $cell = $sheet->getCell('C' . $rowIndex);
  43.     $array_data[$rowIndex]['C'] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'YYYY-MM-DD');
  44.     $cell = $sheet->getCell('D' . $rowIndex);
  45.     $array_data[$rowIndex]['D'] = $cell->getCalculatedValue();*/
  46. }
  47. var_dump($array_data);
3.4 使用PHPExcel_Writer_Excel2007写入
  1. <?php

  3. error_reporting(E_ALL);
  4. ini_set('include_path', ini_get('include_path').';./Classes/');

  6. include 'PHPExcel.php';
  7. include 'PHPExcel/Writer/Excel2007.php';
  8. //include 'PHPExcel/Writer/Excel5.php';

  10. $objPHPExcel = new PHPExcel();
  11. /* set properties */
  12. //$objPHPExcel->getProperties()->setCreator("Maarten Balliauw"); // 作者
  13. //$objPHPExcel->getProperties()->setLastModifiedBy("Jone Smith");
  14. //$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document"); //标题
  15. //$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document"); //主题
  16. //$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes."); //备注
  17. //$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php"); //关键字
  18. //$objPHPExcel->getProperties()->setCategory("Test result file"); //类别

  20. /* set content */
  21. //$objPHPExcel->setActiveSheetIndex(0);
  22. //$objPHPExcel->getActiveSheet()->setTitle('Simple');
  23. //$objPHPExcel->getActiveSheet()->setCellValue('A1', 'String');
  24. //$objPHPExcel->getActiveSheet()->setCellValue('A2', 12);
  25. //$objPHPExcel->getActiveSheet()->setCellValue('A3', true);
  26. //$objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');
  27. //$objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');
  28. //$objPHPExcel->getActiveSheet()->mergeCells('A18:E22'); //合并单元格
  29. //$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28'); //分离单元格
  30. //$objPHPExcel->getActiveSheet()->setCellValueExplicit('A5', '847475847857487584', PHPExcel_Cell_DataType::TYPE_STRING);

  32. /* 保护cell */
  33. //$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
  34. //$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');

  36. /* cell 数字格式 */
  37. //$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
  38. //$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
  39. //$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );

  41. /* column width */
  42. //$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
  43. //$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
  44. //$ojStyleA5 = $objPHPExcel->getActiveSheet()->getStyle('A5');

  46. /* set font */
  47. //$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
  48. //$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
  49. //$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
  50. //$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
  51. //$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
  52. //$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
  53. //$objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
  54. //$objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);

  56. /* set align */
  57. //$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  58. //$objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  59. //$objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  60. //$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
  61. //$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

  63. /* set column border */
  64. //$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB(’FF993300′);
  65. //$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB(’FF993300′);
  66. //$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB(’FF993300′);
  67. //$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB(’FF993300′);
  68. //$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB(’FF993300′);
  69. //$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB(’FF993300′);
  70. //$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  71. /*
  72. $default_border = array(
  73. 'style' => PHPExcel_Style_Border::BORDER_THIN,
  74. 'color' => array('rgb'=>'1006A3')
  75. );
  76. $style_header = array(
  77. 'borders' => array(
  78. 'bottom' => $default_border,
  79. 'left' => $default_border,
  80. 'top' => $default_border,
  81. 'right' => $default_border,
  82. ),
  83. 'fill' => array(
  84. 'type' => PHPExcel_Style_Fill::FILL_SOLID,
  85. 'color' => array('rgb'=>'E1E0F7'),
  86. ),
  87. 'font' => array(
  88. 'bold' => true,
  89. )
  90. );

  92. $sheet->getStyle('A1:A2')->applyFromArray( $style_header );
  93. $sheet->getStyle('B1:B2')->applyFromArray( $style_header );
  94. */

  96. /* fill color */
  97. //$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  98. //$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB(’FF808080′);
  99. //$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  100. //$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB(’FF808080′);

  102. /* copy style */
  103. //$objPHPExcel->getActiveSheet()->duplicateStyle($objStyleA5, 'B1:C22');

  105. /* add image */
  106. /*
  107. $objDrawing = new PHPExcel_Worksheet_Drawing();
  108. $objDrawing->setName(’Logo’);
  109. $objDrawing->setDescription(’Logo’);
  110. $objDrawing->setPath(’./images/officelogo.jpg’);
  111. $objDrawing->setHeight(36);
  112. $objDrawing->setCoordinates('B15');
  113. $objDrawing->setOffsetX(110);
  114. $objDrawing->setRotation(25);
  115. $objDrawing->getShadow()->setVisible(true);
  116. $objDrawing->getShadow()->setDirection(45);
  117. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
  118. */

  120. /* add a new worksheet */
  121. //$objPHPExcel->createSheet();
  122. //$objPHPExcel->getSheet(1)->setTitile('sheet2');
  123. //$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
  124. //$objWriter->save('php://output');

  126. /* add summary/total row *//
  127. /*
  128. //Adding total column
  129. $ranged_total_coordinate = chr($starting_pos+$index_pos) . '1:' . chr($starting_pos+$index_pos) . '2';
  130. $sheet->setCellValue(chr($starting_pos+$index_pos) . '1', 'Total');
  131. $sheet->mergeCells($ranged_total_coordinate);
  132. $sheet->getStyle(chr($starting_pos+$index_pos) . '1')->getAlignment()
  133. ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
  134. ->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
  135. $sheet->getStyle($ranged_total_coordinate)->applyFromArray( $style_header );
  136. $rowcounter = 2;
  137. foreach($array_data_user as $k1=>$v1){
  138. $rowcounter++;
  139. $sheet->setCellValue(chr($starting_pos+$index_pos) . $rowcounter, "=SUM(C{$rowcounter}:".chr(($starting_pos+($index_pos-1)))."{$rowcounter})");
  140. $sheet->getStyle(chr($starting_pos+$index_pos) . $rowcounter)->applyFromArray( $style_body_content );
  141. }
  142. */

  144. /* parsing formula */
  145. //$sheet->getCell('K3')->getValue() . " -> " . $sheet->getCell('K3')->getCalculatedValue();

  147. $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
  148. //$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

  150. /* create empty Excel */
  151. $objWriter->save('xxx.xlsx');

  153. /* force download */
  154. /*
  155. header('Pragma: public');
  156. header('Expires: 0');
  157. header('Cache-Control:must-revalidate, post-check=0, pre-check=0');
  158. header('Content-Type:application/force-download');
  159. header('Content-Type:application/vnd.ms-execl');
  160. header('Content-Type:application/octet-stream');
  161. header('Content-Type:application/download');;
  162. header('Content-Disposition:attachment;filename="resume.xlsx"');
  163. header('Content-Transfer-Encoding:binary');
  164. $objWriter->save('php://output');*/


3.5 合并多个excel文件至一个
  1. <?php

  3. include 'PHPExcel/IOFactory.php';

  5. $inputFileType = 'CSV';
  6. $inputFileNames = array('./example1.csv','./example2.csv','./example3.csv','./example4.csv');

  8. $objReader = PHPExcel_IOFactory::createReader($inputFileType);
  9. $inputFileName = array_shift($inputFileNames);
  10. $objPHPExcel = $objReader->load($inputFileName);
  11. $objPHPExcel->getActiveSheet()->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME));
  12. foreach($inputFileNames as $sheet => $inputFileName) {
  13.     $objReader->setSheetIndex($sheet+1);
  14.     $objReader->loadIntoExisting($inputFileName,$objPHPExcel);
  15.     $objPHPExcel->getActiveSheet()->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME));
  16. }


  19. $loadedSheetNames = $objPHPExcel->getSheetNames();
  20. foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
  21.     $objPHPExcel->setActiveSheetIndexByName($loadedSheetName);
  22.     echo $loadedSheetName,PHP_EOL;
  23.     $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
  24.     var_dump($sheetData);
  25.     echo PHP_EOL;
  26. }
3.6 格式转换
  1. <?php

  3. $cell = $objWorksheet->getCellByColumnAndRow(3, 5);
  4. $cell_value = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'hh:mm:ss');
  5. echo $cell_value;
3.5 格式转换
  1. <?php

  3. $objPHPExcel = PHPExcel_IOFactory::load("XMLTest.xml");
  4. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  5. $objWriter->save('covertedXml2Xlsx.xlsx');
阅读(1155) | 评论(0) | 转发(0) |
0

上一篇：SQLite 笔记

下一篇：php - 时间函数应用

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6