phpExcel Chinese Help Manual (Knowledge Points)-PHP Tutorial-php.cn

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'String');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 12);
$ objPHPExcel->getActiveSheet()->setCellValue('A3', true);
$objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');
$ objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');

Copy code

Merge cells

$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');

Copy code

Separate cells

$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');

Copy code

Protect cell

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

Copy code

Set format

// Set cell number formats
echo date('H:i:s') . " Set cell number formatsn";
$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

// Set column widths
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension ('D')->setWidth(12);

Copy code

Set font

$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()-> ;getStyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold( true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()-> getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E1')-> getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true );
$objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);

Copy code

Set align

$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiv eSheet()- >getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()- >setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);

Copy Code

//Vertically centered

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

Copy code

设置column的border

$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

复制代码

设置border的color

$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');

复制代码

设置填充颜色

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');

复制代码

加图片

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Paid');
$objDrawing->setDescription('Paid');
$objDrawing->setPath('./images/paid.png');
$objDrawing->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

复制代码

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

function convertUTF8($str)
{
if(empty($str)) return '';
return iconv('gb2312', 'utf-8', $str);
}

复制代码

//Output data processing method from database

Read data from the database such as:

$db = new Mysql($dbconfig);
$sql = "SELECT * FROM table name";
$row = $db->GetAll($sql); // $row is a two-dimensional array
$count = count($row);
for ($i = 2; $i <= $count+1; $i++) {
$objPHPExcel ->getActiveSheet()->setCellValue('A' . $i, convertUTF8($row[$i-2][1]));
$objPHPExcel->getActiveSheet()->setCellValue('B ' . $i, convertUTF8($row[$i-2][2]));
$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertUTF8($row[$i- 2][3]));
$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertUTF8($row[$i-2][4]));
$objPHPExcel-> ;getActiveSheet()->setCellValue('E' . $i, convertUTF8(date("Y-m-d", $row[$i-2][5])));
$objPHPExcel->getActiveSheet()-> ;setCellValue('F' . $i, convertUTF8($row[$i-2][6]));
$objPHPExcel->getActiveSheet()->setCellValue(' G' . $i, convertUTF8($row[$i-2][7]));
$objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertUTF8($row[$i -2][8]));
}

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

echo date('H:i:s') . " Create new Worksheet objectn";
$objPHPExcel->createSheet();
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
$objWriter-save('php://output');

Copy code

Attached, a few commonly used ones How to use.

include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
//or include 'PHPExcel/Writer/Excel5.php';
Create an excel for outputting . objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); Non-2007 format
$objWriter->save("xxx.xlsx");
Output directly to the browser
$objWriter = new PHPExcel_Writer_Excel5($ objPHPExcel);
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="resume.xls"');
header("Content-Transfer-Encoding:binary");
$ objWriter->save('php://output');
?>
Copy code