phpExcel使用方法

2

使用方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
include 'PHPExcel.php';
include 'PHPExcel/RichText.php'; //用于输出.xls/.pdf的
 
//创建一个excel 
$objPHPExcel = new PHPExcel();
 
//保存excel
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save("xxx.xlsx");
 
//输出XLS到浏览器
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
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');
 
//输出PDF到浏览器
$rendererName = PHPExcel_Settings::PDF_RENDERER_TCPDF;
$rendererLibrary = 'TCPDF';
$rendererLibraryPath = './Lib/tcpdf/';
 
PHPExcel_Settings::setPdfRenderer(
    $rendererName,
    $rendererLibraryPath
);
 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->setSheetIndex(0);
 
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/pdf");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header('Content-Disposition:attachment;filename="resume.pdf"');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');

设置excel的属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//创建人 
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
//最后修改人
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
//标题
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
//题目
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
//描述
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
//关键字
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
//种类
$objPHPExcel->getProperties()->setCategory("Test result file");

其他常用设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
//设置默认字体和大小
$objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
 
//设置当前的sheet 
$objPHPExcel->setActiveSheetIndex(0); 
//设置sheet的name 
$objPHPExcel->getActiveSheet()->setTitle('Simple'); 
//设置单元格的值 
$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)'); 
//合并单元格 
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22'); 
//分离单元格 
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
//保护cell 
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
//设置格式 
// Set cell number formats 
echo date('H:i:s') . " Set cell number formatsn";
$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); 
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' ); 
//设置宽width 
// Set column widths 
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); 
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12); 
//设置高度
$objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight(30);
//设置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); 
//设置align 
$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
$objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); 
$objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY); 
//垂直居中 
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
//设置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()); 
//超链接
$objPHPExcel->getActiveSheet()->getCell('J9')->getHyperlink()->setUrl('http://www.g.cn/');
//在默认sheet后,创建一个worksheet 
echo date('H:i:s') . " Create new Worksheet objectn"; 
$objPHPExcel->createSheet(); 
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5'); 
$objWriter-save('php://output');

tcpdf中文乱码的问题

下载字体包,解压至fonts目录
修改字体为

$pdf->SetFont('droidsansfallback', '', 12);

共 2 条评论

  1. 回复

    实用,mark..

  2. 回复

    确实不错,赞一个

回复 儒雅阁 X

您的邮箱不会公开,当您的评论有新的回复时,会通过您填写的邮箱向您发送评论内容。 必填字段 *

为何看不到我发布的评论?

正在提交, 请稍候...