核心代碼：
|
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
|
//引入PHPExcelvendor('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'); |
