PhpExcel中文帮助手册|PhpExcel使用方法三

001

<p>PHPExcel基本操作：<br />

002	定义EXCEL实体<br />

003	即定义一个PHPEXCEL对象，并设置EXCEL对象内显示内容</p>

004

<div>

005	<pre class="prebrush">

006	// Excel开始

007	// 准备EXCEL的包括文件

008	// Error reporting

009	error_reporting(0);

010	// PHPExcel

011	require_once dirname(__FILE__) . 'PHPExcel.php';

012	// 生成新的excel对象

013	$objPHPExcel = new PHPExcel();

014	// 设置excel文档的属性

015	$objPHPExcel->getProperties()->setCreator("Sam.c")

016	->setLastModifiedBy("Sam.c Test")

017	->setTitle("Microsoft Office Excel Document")

018	->setSubject("Test")

019	->setDescription("Test")

020	->setKeywords("Test")

021	->setCategory("Test result file");

022	// 开始操作excel表

023	// 操作第一个工作表

024	$objPHPExcel->setActiveSheetIndex(0);

025	// 设置工作薄名称

026	$objPHPExcel->getActiveSheet()->setTitle(iconv('gbk', 'utf-8', 'phpexcel测试'));

027	// 设置默认字体和大小

028	$objPHPExcel->getDefaultStyle()->getFont()->setName(iconv('gbk', 'utf-8', '宋体'));

029	$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);

030

031

</pre>

032

</div>

033	<p>三、输出文件</p>

034

<div>

035	<pre class="prebrush">

036	// 如果需要输出EXCEL格式

037	if($m_exportType=="excel"){

038	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

039	// 从浏览器直接输出$filename

040	header("Pragma: public");

041	header("Expires: 0");

042	header("Cache-Control:must-revalidate, post-check=0, pre-check=0");

043	header("Content-Type:application/force-download");

044	header("Content-Type: application/vnd.ms-excel;");

045	header("Content-Type:application/octet-stream");

046	header("Content-Type:application/download");

047	header("Content-Disposition:attachment;filename=".$filename);

048	header("Content-Transfer-Encoding:binary");

049	$objWriter->save("php://output");

050

}

051	// 如果需要输出PDF格式

052	if($m_exportType=="pdf"){

053	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');

054	$objWriter->setSheetIndex(0);

055	header("Pragma: public");

056	header("Expires: 0");

057	header("Cache-Control:must-revalidate, post-check=0, pre-check=0");

058	header("Content-Type:application/force-download");

059	header("Content-Type: application/pdf");

060	header("Content-Type:application/octet-stream");

061	header("Content-Type:application/download");

062	header("Content-Disposition:attachment;filename=".$m_strOutputPdfFileName);

063	header("Content-Transfer-Encoding:binary");

064	$objWriter->save("php://output");

065

}

066

067

</pre>

068

</div>

069	<p>设置一列的宽度：<br />

070

</p>

071

<div>

072	<pre class="prebrush">

073	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);

074

</pre>

075

</div>

076	<p>设置一行的高度：<br />

077

</p>

078

<div>

079	<pre class="prebrush">

080	$objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight(30);

081

</pre>

082

</div>

083	<p>合并单元格：<br />

084

</p>

085

<div>

086	<pre class="prebrush">

087	$objPHPExcel->getActiveSheet()->mergeCells('A1:P1');

088

</pre>

089

</div>

090	<p>设置A1单元格加粗，居中：<br />

091

</p>

092

<div>

093	<pre class="prebrush">

094	$styleArray1 = array(

095	'font' => array(

096	'bold' => true,

097	'size'=>12,

098	'color'=>array(

099	'argb' => '00000000',

100

),

101

),

102	'alignment' => array(

103	'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,

104

),

105

);

106	// 将A1单元格设置为加粗，居中

107	$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray1);

108

109	$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);

110

111

</pre>

112

</div>

113	<p>给特定单元格中写入内容：<br />

114

</p>

115

<div>

116	<pre class="prebrush">

117	$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello Baby');

118

</pre>

119

</div>

120	<p>设置单元格样式（水平/垂直居中）：<br />

121	&nbsp;</p>

122

<div>

123	<pre class="prebrush">

124	$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

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

126

</pre>

127

</div>

128	<p>设置单元格样式（黑色字体）：<br />

129

</p>

130

<div>

131	<pre class="prebrush">

132	$objPHPExcel->getActiveSheet()->getStyle('H5')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK); // 黑色

133

</pre>

134

</div>

135	<p>设置单元格格式（背景）：<br />

136

</p>

137

<div>

138	<pre class="prebrush">

139	$objPHPExcel->getActiveSheet()->getStyle('H5')->getFill()->getStartColor()->setARGB('00ff99cc'); // 将背景设置为浅粉色

140

</pre>

141

</div>

142	<p>设置单元格格式（数字格式）：<br />

143

</p>

144

<div>

145	<pre class="prebrush">

146	$objPHPExcel->getActiveSheet()->getStyle('F'.$iLineNumber)->getNumberFormat()->setFormatCode('0.000');

147

</pre>

148

</div>

149	<p>给单元格中放入图片：<br />

150

</p>

151

<div>

152	<pre class="prebrush">

153	// 将数据中心图片放在J1单元格内

154	$objDrawing = new PHPExcel_Worksheet_Drawing();

155	$objDrawing->setName('Logo');

156	$objDrawing->setDescription('Logo');

157	$objDrawing->setPath('test.jpg');

158	$objDrawing->setWidth(400);

159	$objDrawing->setHeight(123);

160	$objDrawing->setCoordinates('J1');

161	$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

162

</pre>

163

</div>

164

<p><br />

165	在单元格中设置超链接：<br />

166

</p>

167

<div>

168	<pre class="prebrush">

169	$objPHPExcel->getActiveSheet()->setCellValue('H8', iconv('gbk', 'utf-8', '燕南天'));

170	$objPHPExcel->getActiveSheet()->getCell('H8')->getHyperlink()->setUrl('http://www.bitsCN.com/');

171

</pre>

172

</div>

173	<p>设置单元格边框</p>

174

<div>

175	<pre class="prebrush">

176	$styleThinBlackBorderOutline = array(

177	'borders' => array (

178	'outline' => array (

179	'style' => PHPExcel_Style_Border::BORDER_THIN,  //设置border样式

180	//'style' => PHPExcel_Style_Border::BORDER_THICK, 另一种样式

181	'color' => array ('argb' => 'FF000000'),     //设置border颜色

182

),

183

),

184

);

185	$objPHPExcel->getActiveSheet()->getStyle( 'A4:E10')->applyFromArray($styleThinBlackBorderOutline);

186

187	//添加一个新的worksheet

188	$objExcel->createSheet();

189	$objActSheet = $objExcel->getSheet($s);

190	$objActSheet->setTitle('表'.$GSheet);

191

</pre>