Home > Backend Development > PHP Tutorial > php export and import Excel files

php export and import Excel files

WBOY
Release: 2016-07-25 09:09:32
Original
918 people have browsed it
PHPExcel is a very powerful MS Office Excel document generation class library. When you need to output data in a more complex format, PHPExcel is a good choice. However, its use is relatively complicated. List them to remember.
  1. //Set the include path of the PHPExcel class library
  2. set_include_path('.'. PATH_SEPARATOR .
  3. 'D:ZealPHP_LIBS' . PATH_SEPARATOR .
  4. get_include_path());
  5. /**
  6. * The following are usage examples. There are different optional methods for lines starting with ////. Please according to actual needs
  7. * Turn on the comments of the corresponding lines.
  8. * If you use Excel5, the output content should be GBK encoded.
  9. * /
  10. require_once 'PHPExcel.php';
  11. // uncomment
  12. ////require_once 'PHPExcel/Writer/Excel5.php'; // For other lower versions of xls
  13. // or
  14. ////require_once 'PHPExcel /Writer/Excel2007.php'; // Used for excel-2007 format
  15. // Create a processing object instance
  16. $objExcel = new PHPExcel();
  17. // Create a file format writing object instance, uncomment
  18. // //$objWriter = new PHPExcel_Writer_Excel5($objExcel); // For other version formats
  19. // or
  20. ////$objWriter = new PHPExcel_Writer_Excel2007($objExcel); // For 2007 format
  21. //$objWriter- >setOffice2003Compatibility(true);
  22. //************************************
  23. / /Set basic document properties
  24. $objProps = $objExcel->getProperties();
  25. $objProps->setCreator("Zeal Li");
  26. $objProps->setLastModifiedBy("Zeal Li");
  27. $objProps- >setTitle("Office XLS Test Document");
  28. $objProps->setSubject("Office XLS Test Document, Demo");
  29. $objProps->setDescription("Test document, generated by PHPExcel.");
  30. $objProps->setKeywords("office excel PHPExcel");
  31. $objProps->setCategory("Test");
  32. //******************* ******************
  33. //Set the current sheet index for subsequent content operations.
  34. //Generally, display calls are only needed when using multiple sheets.
  35. //By default, PHPExcel will automatically create the first sheet with SheetIndex=0
  36. $objExcel->setActiveSheetIndex(0);
  37. $objActSheet = $objExcel->getActiveSheet();
  38. / /Set the name of the current active sheet
  39. $objActSheet->setTitle('Test Sheet');
  40. //****************************** ************
  41. //Set cell content
  42. //
  43. //PHPExcel automatically determines the cell content type based on the incoming content
  44. $objActSheet->setCellValue('A1', ' String content'); // String content
  45. $objActSheet->setCellValue('A2', 26); // Value
  46. $objActSheet->setCellValue('A3', true); // Boolean value
  47. $ objActSheet->setCellValue('A4', '=SUM(A2:A2)'); // Formula
  48. //Explicitly specify the content type
  49. $objActSheet->setCellValueExplicit('A5', '847475847857487584',
  50. PHPExcel_Cell_DataType::TYPE_STRING);
  51. //Merge cells
  52. $objActSheet->mergeCells('B1:C22');
  53. //Separate cells
  54. $objActSheet->unmergeCells('B1:C22');
  55. //************************************
  56. //Set cell style
  57. / /
  58. //Set width
  59. $objActSheet->getColumnDimension('B')->setAutoSize(true);
  60. $objActSheet->getColumnDimension('A')->setWidth(30);
  61. $ objStyleA5 = $objActSheet->getStyle('A5');
  62. //Set the number format of the cell content.
  63. //
  64. //If PHPExcel_Writer_Excel5 is used to generate content,
  65. //It should be noted here that in the
  66. //various custom formatting methods defined by the const variable of the PHPExcel_Style_NumberFormat class, other types can be used normally, but When setFormatCode
  67. //is FORMAT_NUMBER, the actual effect is that the format is not set to "0". Need to
  68. //Modify the getXf($style) method in the PHPExcel_Writer_Excel5_Format class source code,
  69. //Add a
  70. //line of code before if ($this->_BIFF_version == 0x0500) { (near line 363):
  71. //if($ifmt === '0') $ifmt = 1;
  72. //
  73. //Set the format to PHPExcel_Style_NumberFormat::FORMAT_NUMBER to avoid certain large numbers
  74. //being displayed using scientific notation, with The setAutoSize method below can make the content of each row
  75. // be displayed according to the original content.
  76. $objStyleA5
  77. ->getNumberFormat()
  78. ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
  79. //设置字体
  80. $objFontA5 = $objStyleA5->getFont();
  81. $objFontA5->setName('Courier New');
  82. $objFontA5->setSize(10);
  83. $objFontA5->setBold(true);
  84. $objFontA5->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
  85. $objFontA5->getColor()->setARGB('FF999999');
  86. //设置对齐方式
  87. $objAlignA5 = $objStyleA5->getAlignment();
  88. $objAlignA5->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  89. $objAlignA5->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  90. //设置边框
  91. $objBorderA5 = $objStyleA5->getBorders();
  92. $objBorderA5->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  93. $objBorderA5->getTop()->getColor()->setARGB('FFFF0000'); // color
  94. $objBorderA5->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  95. $objBorderA5->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  96. $objBorderA5->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  97. //设置填充颜色
  98. $objFillA5 = $objStyleA5->getFill();
  99. $objFillA5->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  100. $objFillA5->getStartColor()->setARGB('FFEEEEEE');
  101. //从指定的单元格复制样式信息.
  102. $objActSheet->duplicateStyle($objStyleA5, 'B1:C22');
  103. //*************************************
  104. //添加图片
  105. $objDrawing = new PHPExcel_Worksheet_Drawing();
  106. $objDrawing->setName('ZealImg');
  107. $objDrawing->setDescription('Image inserted by Zeal');
  108. $objDrawing->setPath('./zeali.net.logo.gif');
  109. $objDrawing->setHeight(36);
  110. $objDrawing->setCoordinates('C23');
  111. $objDrawing->setOffsetX(10);
  112. $objDrawing->setRotation(15);
  113. $objDrawing->getShadow()->setVisible(true);
  114. $objDrawing->getShadow()->setDirection(36);
  115. $objDrawing->setWorksheet($objActSheet);
  116. //添加一个新的worksheet
  117. $objExcel->createSheet();
  118. $objExcel->getSheet(1)->setTitle('测试2');
  119. //保护单元格
  120. $objExcel->getSheet(1)->getProtection()->setSheet(true);
  121. $objExcel->getSheet(1)->protectCells('A1:C22', 'PHPExcel');
  122. //*************************************
  123. //输出内容
  124. //
  125. $outputFileName = "output.xls";
  126. //到文件
  127. ////$objWriter->save($outputFileName);
  128. //or
  129. //到浏览器
  130. ////header("Content-Type: application/force-download");
  131. ////header("Content-Type: application/octet-stream");
  132. ////header("Content-Type: application/download");
  133. ////header('Content-Disposition:inline;filename="'.$outputFileName.'"');
  134. ////header("Content-Transfer-Encoding: binary");
  135. ////header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
  136. ////header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
  137. ////header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  138. ////header("Pragma: no-cache");
  139. ////$objWriter->save('php://output');
  140. ?>
复制代码


Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template