核心代码：
|
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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
//引入PHPExcel
vendor('PHPExcel.PHPExcel');
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
//设置文档属性
$objPHPExcel->getProperties()
->setCreator("web100.cc")
->setLastModifiedBy("web100.cc")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
//设置当前活动的sheet
$objPHPExcel->setActiveSheetIndex(0);
//选定当前Sheet
$objActSheet = $objPHPExcel->11getActiveSheet();
//设置sheet名字
$objActSheet->setTitle('phpexcel demo');
//设置默认行高
$objActSheet->getDefaultRowDimension()->setRowHeight(15);
//由PHPExcel根据传入内容自动判断单元格内容类型
$objActSheet->setCellValue('A1', "Firstname");
$objActSheet->setCellValue('B1', "Lastname");
$objActSheet->setCellValue('C1', "Phone");
$objActSheet->setCellValue('D1', "Fax");
$objActSheet->setCellValue('E1', "Address");
$objActSheet->setCellValue('F1', "ZIP");
$objActSheet->setCellValue('G1', "DATE");
$objActSheet->setCellValueByColumnAndRow(0, 8, 'firstname');
$objActSheet->setCellValueByColumnAndRow(1, 8, 'lastname');
// 单元格填充值
$objActSheet->setCellValue('A2', "小风");
$objActSheet->setCellValue('B2', "wang");
//设置列宽
$objActSheet->getColumnDimension('C')->setWidth('20');
// 设置行高
$objActSheet->getRowDimension('9')->setRowHeight(20); //第9行
// 设置字体
$objActSheet->getStyle('A1')->getFont()->setName('宋体');
// 字号
$objActSheet->getStyle('A1')->getFont()->setSize(12);
// 加粗
$objActSheet->getStyle('A1')->getFont()->setBold(true);
//说明：$objActSheet->getStyle('A1:G10')，可以通过这种方式来选择一片单元格！
// 设置单元格格式
$objActSheet->getCell('C2')->setValueExplicit('861391327543258', PHPExcel_Cell_DataType::TYPE_NUMERIC);
// 日期
$objActSheet->setCellValue('G2', '2008-12-31');
$objActSheet->getStyle('G2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
// 时间戳
$time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
$objActSheet->setCellValue('G3', PHPExcel_Shared_Date::PHPToExcel($time));
$objActSheet->getStyle('G3')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
// url
$objActSheet->setCellValue('G11', 'www.spalybow.com');
$objActSheet->getCell('G11')->getHyperlink()->setUrl('http://www.spalybow.com');
// 另一个sheet
$objActSheet->setCellValue('G12', 'sheetb');
$objActSheet->getCell('G12')->getHyperlink()->setUrl("sheet://'sheetb'!A1");
// 水平居上
$objActSheet->getStyle('A9:B9')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
// 单元格换行
$objActSheet->getStyle('G2:G3')->getAlignment()->setWrapText(true);
// 合并
$objActSheet->mergeCells('A18:E22');
// 隐藏D列
$objActSheet->getColumnDimension('D')->setVisible(false);
//
$objActSheet->getColumnDimension('E')->setOutlineLevel(1);
$objActSheet->getColumnDimension('E')->setVisible(false);
$objActSheet->getColumnDimension('F')->setOutlineLevel(1);
$objActSheet->getColumnDimension('F')->setVisible(false);
$objActSheet->getColumnDimension('F')->setCollapsed(true);
// 固定第一行
$objActSheet->freezePane('A2');
// 保护工作表
$objActSheet->getProtection()->setPassword('PHPExcel');
$objActSheet->getProtection()->setSheet(true);
$objActSheet->getProtection()->setSort(true);
$objActSheet->getProtection()->setInsertRows(true);
$objActSheet->getProtection()->setFormatCells(true);
//设置边框
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle1->applyFromArray(
array('borders' => array(
'left' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
)
));
$objActSheet->setSharedStyle($sharedStyle1, "B1:B10");
// 创建一个新的工作表
$objWorksheet1 = $objPHPExcel->createSheet();
$objWorksheet1->setTitle('sheetb');
$objPHPExcel->setActiveSheetIndex(1);
// 创建一个图片
$gdImage = @imagecreatetruecolor(200, 20) or die('Cannot Initialize new GD image stream');
$textColor = imagecolorallocate($gdImage, 255, 255, 255);
imagestring($gdImage, 1, 5, 5, 'Created with PHPExcel (c1gstudio.com)', $textColor);
// 把创建的图片添加到工作表
$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setName('Sample image');
$objDrawing->setDescription('Sample image');
$objDrawing->setImageResource($gdImage);
$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objActSheet);
$objPHPExcel->setActiveSheetIndex(0);
// 保存
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('testexcel'.time().'.xls');
|