<?php
require_once dirname(__FILE__).'/../function/PHPExcel_1.7.8/Classes/PHPExcel.php';
require_once dirname(__FILE__).'/../function/PHPExcel_1.7.8/Classes/PHPExcel/Writer/Excel5.php';
require_once dirname(__FILE__).'/../function/PHPExcel_1.7.8/Classes/PHPExcel/IOFactory.php';
require_once dirname(__FILE__).'/../function/PHPExcel_1.7.8/Classes/PHPExcel/Reader/Excel5.php';
class Excel
{
/**
* 构造一个PHPExcel对象,并设置Excel文件的格式等
*
* @param string $sheetName 表单名
* @param string $title 标题
* @param integer $columnCount 列数
* @param array $headers 各列标头
* @return PHPExcel 构造的PHPExcel对象
*/
public static function newPHPExcel($sheetName, $title, $columnCount, $headers)
{
$objExcel = new PHPExcel();
$objExcel->setActiveSheetIndex(0);
$objActSheet = $objExcel->getActiveSheet();
$objActSheet->setTitle($sheetName);
// 文件属性
$objProps = $objExcel ->getProperties();
$objProps->setCreated(gmdate("d M Y H:i:s"));
$objProps->setModified(gmdate("d M Y H:i:s"));
// 标题栏
$objActSheet->mergeCellsByColumnAndRow(0, 1, $columnCount-1, 1);
$objActSheet->setCellValue('A1', $title);
$objStyleA1 = $objActSheet->getStyle('A1');
$objAlignA1 = $objStyleA1->getAlignment();
$objAlignA1->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objAlignA1->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objFontA1 = $objStyleA1->getFont();
$objFontA1->setSize(24);
$objFontA1->setBold(true);
// 表头行颜色
$endColumn = chr(ord('A') + $columnCount-1);
$objStyleA2 = $objActSheet->getStyle('A2:'.$endColumn.'2');
$objFillA2 = $objStyleA2->getFill();
$objFillA2->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objFillA2->getStartColor()->setARGB('FFC0C0C0');// 浅灰色
$clOffset = 0;
// 表头文字等
for ($clOffset = 0; $clOffset < $columnCount; $clOffset++)
{
$cl = chr(ord('A') + $clOffset).'2';
// 文字
$objActSheet->setCellValue($cl, $headers[$clOffset]);
// 对齐
$objStyleHeader = $objActSheet->getStyle($cl);
$objAlignHeader = $objStyleHeader->getAlignment();
$objAlignHeader->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objAlignHeader->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
// 边框
$objStyleHeader = $objActSheet->getStyle($cl);
// 字体
$objFontHeader = $objStyleHeader->getFont();
$objFontHeader->setBold(true);
}
$objStyleBorder = $objStyleA2->getBorders();
$objStyleBorder->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objStyleBorder->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objStyleBorder->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objStyleBorder->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objStyleBorder->getTop()->getColor()->setARGB('FFA9A9A9');// 深灰色
$objStyleBorder->getBottom()->getColor()->setARGB('FFA9A9A9');
$objStyleBorder->getLeft()->getColor()->setARGB('FFA9A9A9');
$objStyleBorder->getRight()->getColor()->setARGB('FFA9A9A9');
// 列宽和自动换行
for ($clOffset = 0; $clOffset < $columnCount; $clOffset++)
{
$cl = chr(ord('A') + $clOffset);
$headerText = $objActSheet->getCell($cl.'2')->getValue();
$len = mb_strlen($headerText, 'UTF-8');
$objActSheet->getColumnDimension($cl)->setWidth($len + 20);
$objStyleCL = $objActSheet->getStyle($cl);
$objAlignCL = $objStyleCL->getAlignment();
$objAlignCL->setWrapText(true);
}
return $objExcel;
}
/**
* 向Excel文件中填充一行数据
*
* @param PHPExcel $objExcel PHPExcel对象
* @param array $rowData 一行数据,以数组存储
* @param integer $rowNum 存储到第几行
*/
public static function fillExcelRow($objExcel, $rowData, $rowNum)
{
$objActSheet = $objExcel->getActiveSheet();
$cl = 0;
foreach ($rowData as $cell)
{
$objActSheet->setCellValueExplicitByColumnAndRow($cl++, $rowNum + 3, $cell, PHPExcel_Cell_DataType::TYPE_STRING);// 设置文本格式
}
$letterArr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
$c = count($rowData);
for($i=1;$i<=$c;$i++)
{
$endColumn = chr(ord($letterArr[$i])-1);
$objStyleBorder = $objActSheet->getStyle($endColumn.($rowNum+3))->getBorders();
$objStyleBorder->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objStyleBorder->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objStyleBorder->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objStyleBorder->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objStyleBorder->getTop()->getColor()->setARGB('000');// 深灰色
$objStyleBorder->getBottom()->getColor()->setARGB('000');
$objStyleBorder->getLeft()->getColor()->setARGB('000');
$objStyleBorder->getRight()->getColor()->setARGB('000');
}
}
/**
* 输出Excel文件
*
* @param PHPExcel $objExcel PHPExcel对象
* @param string $fileName 文件名
*/
public static function makeExcelFile($objExcel, $fileName)
{
$objWriter = new PHPExcel_Writer_Excel5($objExcel);
// 输出文件
$outputFileName = $fileName.'.xls';
$encodedFilename = urlencode($outputFileName);
$encodedFilename = str_replace("+", "%20", $encodedFilename);
// 浏览器弹出保存对话框,并解决文件名中文乱码问题
$ua = $_SERVER["HTTP_USER_AGENT"];
if (preg_match("/MSIE/", $ua)) {
header('Content-Disposition: attachment; filename="' . $encodedFilename . '"');
} else if (preg_match("/Firefox/", $ua)) {
header('Content-Disposition: attachment; filename*="utf8\'\'' . $outputFileName . '"');
} else {
header('Content-Disposition: attachment; filename="' . $outputFileName . '"');
}
header("Content-Type: application/force-download");// 强制下载
header("Content-Type: application/octet-stream"); // 文件的mime类型
header("Content-Transfer-Encoding: binary");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");// 设置一个过去的时间,使浏览器不缓存
header("Last-Modified: " . date("D, d M Y H:i:s") . " GMT");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$objWriter->save('php://output');// php://output输出流
}
/**
* 导入EXCEL文件
* @param $filename 文件名
* @param $tempname 临时文件名
* @param $uploadfilepath 上传后的文件地址
* @return array $result 返回读取excel后生成的数组
*/
public static function getExcelFile($filename, $tempname, $uploadfilepath)
{
$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
$objPHPExcel = $objReader->load($uploadfilepath);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow(); //取得总行数
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
$excelInfo=array();
for ($row = 1;$row <= $highestRow;$row++)
{
$strs=array();
//注意highestColumnIndex的列数索引从0开始
for ($col = 0;$col < $highestColumnIndex;$col++)
{
$strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
$excelInfo[] = $strs;
}
return $excelInfo;
}
}
// 导出示例
// $sheetName = '表名';
// $title = '标题';
// $columnCount = 10;
// $headers = array('列1', '列2', '列一二三四五一二三四五', '列4', '列5', '列6', '列7', '列8', '列9', '列10');
// $objExcel = Excel::newPHPExcel($sheetName, $title, $columnCount, $headers);
// $data = array(array('一行一列', '一行二列', '一行三列', '一行四列', '一行五列'),
// array('二行一列', '一行二列', '一行三列', '一行四列', '一行五列'),
// array('三行一列', '一行二列', '一行三列', '一行四列', '一行五列'),
// array('四行一列', '一行二列', '一行三列', '一行四列', '一行五列'));
// for ($i = 0; $i < count($data); $i++)
// {
// Excel::fillExcelRow($objExcel, $data[$i], $i);
// }
// Excel::makeExcelFile($objExcel, $title);
?>
PHPExcel用法
最新推荐文章于 2023-07-02 13:35:19 发布