Home > Article > Backend Development > PHPExcel export Excel file_PHP tutorial
PHPExcel export Excel file_PHP tutorial
- WBOYOriginal
- 2016-07-13 10:28:111016browse
error_reporting(0);
require_once 'phpexcel/PHPExcel.php';
require_once 'phpexcel/PHPExcel/IOFactory.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("BillHong")
->setLastModifiedBy("BillHong")//创建人 ->setTitle("export to Borwser")//标题
->setSubject("export to Borwser")//题目
->setDescription("export to Borwser")//描述
->setKeywords("export to Borwser")//关键字
->setCategory("BillHong export to Borwser");//种类
/*设置单元格的标题*/
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:G1');//合并单元格
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '杭州佳和电气有限公司南京分公司用电量周报表');
//设置font
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setSize(14);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
//水平居中===垂直居中
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A2:G2');//合并单元格
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', '计量时间段2012-03-18 00:00:00--2012-03-25 00:00:00');
//设置font
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getFont()->setSize(10);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
//水平居中===垂直居中
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
/*设置特别表格的宽度*/
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(5);//表示设置A这一列的宽度,以下一样
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(40);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(8);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(5);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(10);
/*设置第3行的内容居中，字体颜色等*/
for ($i = 0; $i <= 6; $i++)
{
$n = 65;
$col = chr($n+$i).'3';
$objPHPExcel->setActiveSheetIndex(0)->getStyle($col)->getFont()->setSize(11);
$objPHPExcel->setActiveSheetIndex(0)->getStyle($col)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->setActiveSheetIndex(0)->getStyle($col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle($col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
// 以下实现生成Excel单元格坐标。感谢php缘来缘去的Q群大力技术支持
$abc = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$firstChar = 0;
$flag = false; // 是否已经变成AA模式。
$d = 65;
$titleLength = count($titles[1]);
for ($i = 0; $i < $titleLength; $i++)
{
// 当已经超过'Z'字母的时候。
if ($d == 91)
{
$d = 65; // 又将$d设置为初始化值65。
if ($flag) {
$firstChar++;
$offset = $abc{$firstChar} . chr($d);
} else {
$flag = true;
$offset = $abc{$firstChar} . chr($d);
}
}
// 当循环没有超过'Z'的时候。
else {
if ($flag) {
$offset = $abc{$firstChar} . chr($d);
} else {
$offset = chr($d);
}
}
// 设置单元格的值。
$offsets = $offset . '3';
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue($offsets, $titles[1][$i]);
$d++;
}
// 以下按照对应坐标将值放进去。以下实现生成Excel单元格坐标。
$k = 4;
foreach ($rowset as $rows)
{
$abc_ = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$firstChar_ = 0;
$flag_ = false;
$d_ = 65;
$rowsetLength = count($rowset);
// 回调函数。
if($backfunc) {
$rows = $backfunc($rows);
}
$rowsLength = count($rows);
for ($a = 0; $a < $rowsLength; $a++)
{
// 当已经超过'Z'字母的时候。
if ($d_ == 91)
{
$d_ = 65; // 又将$d设置为初始化值65。
if ($flag_) {
$firstChar_++;
$offset_ = $abc_{$firstChar_} . chr($d_);
} else {
$flag_ = true;
$offset_ = $abc_{$firstChar_} . chr($d_);
}
}
// 当循环没有超过'Z'的时候。
else {
if ($flag_) {
$offset_ = $abc_{$firstChar_} . chr($d_);
} else {
$offset_ = chr($d_);
}
}
$offsets_ = $offset_ . $k;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue($offsets_, $rows[$a]);
$d_++;
}
$k++;
}
$objPHPExcel->getActiveSheet()->setTitle('zenlove test');
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header("Content-Disposition:attachment;filename=$filename.xls");
header("Content-Type:application/octet-stream");
header("Content-Transfer-Encoding:binary");
header("Pragma:no-cache");
$objWriter->save('php://output');
exit();
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：PHP window platform simulates the checkdnsrr function to detect whether the email actually exists_PHP tutorialNext article：PHP window platform simulates the checkdnsrr function to detect whether the email actually exists_PHP tutorial