search

Home >Backend Development >PHP Tutorial >phpExcel Chinese Help Manual (Knowledge Points)

phpExcel Chinese Help Manual (Knowledge Points)

WBOY
WBOYOriginal
2016-07-25 09:04:141069browse
  2. $objPHPExcel->getActiveSheet()->setCellValue('A1', 'String');
  3. $objPHPExcel->getActiveSheet()->setCellValue('A2', 12);
  4. $ objPHPExcel->getActiveSheet()->setCellValue('A3', true);
  5. $objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');
  6. $ objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');
Copy code

Merge cells

  2. $objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
Copy code

Separate cells

  2. $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
Copy code

Protect cell

  2. $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
  3. $objPHPExcel-> getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
Copy code

Set format

  2. // Set cell number formats
  3. echo date('H:i:s') . " Set cell number formatsn";
  4. $objPHPExcel->getActiveSheet()->getStyle('E4') -& gt; getnumberformat ()-& gt; setformatCode (phpexcel_numberFormat :: Format_currency_eur_simple); )-& gt; duplicatestyle ($ Objphpexcel-& GT; getActiveSheet ()-& gt; getstyle ('e4'), ' E5:E13' );
Copy code
Set width

  1. // Set column widths
  2. $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
  3. $objPHPExcel->getActiveSheet()->getColumnDimension ('D')->setWidth(12);
Copy code
Set font

  1. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
  2. $objPHPExcel->getActiveSheet()-> ;getStyle('B1')->getFont()->setSize(20);
  3. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold( true);
  4. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
  5. $objPHPExcel->getActiveSheet()-> getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
  6. $objPHPExcel->getActiveSheet()->getStyle('E1')-> getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
  7. $objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true );
  8. $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);
Copy code
Set align

  1. $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  2. $objPHPExcel->getActiv eSheet()- >getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  3. $objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()- >setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  4. $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
Copy Code
//Vertically centered

  1. $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
Copy code

设置column的border

  2. $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  3. $objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  4. $objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  5. $objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  6. $objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
复制代码

设置border的color

  2. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
  3. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
  4. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
  5. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
  6. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
  7. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');
复制代码

设置填充颜色

  2. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  3. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');
  4. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  5. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');
复制代码

加图片

  3. $objDrawing = new PHPExcel_Worksheet_Drawing();

  4. $objDrawing->setName('Logo');
  5. $objDrawing->setDescription('Logo');
  6. $objDrawing->setPath('./images/officelogo.jpg');
  7. $objDrawing->setHeight(36);
  8. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

  9. $objDrawing = new PHPExcel_Worksheet_Drawing();

  10. $objDrawing->setName('Paid');
  11. $objDrawing->setDescription('Paid');
  12. $objDrawing->setPath('./images/paid.png');
  13. $objDrawing->setCoordinates('B15');
  14. $objDrawing->setOffsetX(110);
  15. $objDrawing->setRotation(25);
  16. $objDrawing->getShadow()->setVisible(true);
  17. $objDrawing->getShadow()->setDirection(45);
  18. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

复制代码

//处理中文输出问题 需要将字符串转化为UTF-8编码，才能正常输出，否则中文字符将输出为空白，如下处理： $str = iconv('gb2312', 'utf-8', $str); 或者可以写一个函数专门处理中文字符串：

  2. function convertUTF8($str)
  3. {
  4. if(empty($str)) return '';
  5. return iconv('gb2312', 'utf-8', $str);
  6. }
复制代码

//Output data processing method from database

Read data from the database such as:

  3. $db = new Mysql($dbconfig);

  4. $sql = "SELECT * FROM table name";
  5. $row = $db->GetAll($sql); // $row is a two-dimensional array

  6. $count = count($row);

  7. for ($i = 2; $i <= $count+1; $i++) {
  8. $objPHPExcel ->getActiveSheet()->setCellValue('A' . $i, convertUTF8($row[$i-2][1]));
  9. $objPHPExcel->getActiveSheet()->setCellValue('B ' . $i, convertUTF8($row[$i-2][2]));
  10. $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertUTF8($row[$i- 2][3]));
  11. $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertUTF8($row[$i-2][4]));
  12. $objPHPExcel-> ;getActiveSheet()->setCellValue('E' . $i, convertUTF8(date("Y-m-d", $row[$i-2][5])));
  13. $objPHPExcel->getActiveSheet()-> ;setCellValue('F' . $i, convertUTF8($row[$i-2][6]));

  14. $objPHPExcel->getActiveSheet()->setCellValue(' G' . $i, convertUTF8($row[$i-2][7]));

  15. $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertUTF8($row[$i -2][8]));
  16. }

Copy the code

After the default sheet, create a worksheet Insert a paragraph here. Previously Script Academy also introduced several articles about phpexcel to you. Friends who are interested can take a look: Example of using PHPExcel to identify and format date format in Excel Solution to the problem that the color of excel exported by phpexcel is inconsistent with the color in the webpage Use PHPExcel in CI to export data to Excel

  3. echo date('H:i:s') . " Create new Worksheet objectn";

  4. $objPHPExcel->createSheet();

  5. $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');

  6. $objWriter-save('php://output');

Copy code

Attached, a few commonly used ones How to use.

  3. include 'PHPExcel.php';

  4. include 'PHPExcel/Writer/Excel2007.php';
  5. //or include 'PHPExcel/Writer/Excel5.php';
  6. Create an excel for outputting . objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); Non-2007 format
  7. $objWriter->save("xxx.xlsx");

  8. Output directly to the browser

  9. $objWriter = new PHPExcel_Writer_Excel5($ objPHPExcel);
  10. header("Pragma: public");
  11. header("Expires: 0″);
  12. header("Cache-Control:must-revalidate, post-check=0, pre-check=0″);
  13. header("Content-Type:application/force-download");
  14. header("Content-Type:application/vnd.ms-execl");
  15. header("Content-Type:application/octet-stream");
  16. header("Content-Type:application/download");;
  17. header('Content-Disposition:attachment;filename="resume.xls"');
  18. header("Content-Transfer-Encoding:binary");
  19. $ objWriter->save('php://output');
  20. ?>

  24. Copy code
Statement：
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
Previous article：Usage of php auto_prepend_file and auto_append_fileNext article：Usage of php auto_prepend_file and auto_append_file

Related articles

See more