/**
* 将十进制数字转化为26进制用A-Z来表示
* @param type $n
* @return string
*/
function AZ26($n) {
$letter = range('A', 'Z', 1);
$s = '';
while ($n > 0) {
$m = $n % 26;
if ($m == 0)
$m = 26;
$s = $letter[$m - 1] . $s;
$n = ($n - $m) / 26;
}
return $s;
}
/**
* phpexcal传入数组导出excal文件
* $arr 数据数组(二维)
* $fileName 导出的文件名
* $fieldArr 字段名称数组(一维))
* $fontSize字体大小
* $fontFamily 字体
* $lineWidth 行宽 (英文字符数)
* $lineHeight 行高 (象素)
*/
function downloadExcal($arr, $fileName = "output", $fieldArr, $fontSize = 12, $fontFamily = '宋体', $lineWidth = 20, $lineHeight = 15) {
import('ORG.Excel.PHPExcel');
$objExcel = new PHPExcel(); // 创建一个处理对象实例
$objWriter = new PHPExcel_Writer_Excel5($objExcel); // 创建文件格式写入对象实例, uncomment
//设置文档基本属性
$objProps = $objExcel->getProperties();
$objProps->setCreator("Zeal Li");
$objProps->setLastModifiedBy("Zeal Li");
$objProps->setTitle("Office XLS Test Document");
$objProps->setSubject("Office XLS Test Document, Demo");
$objProps->setDescription("Test document, generated by PHPExcel.");
$objProps->setKeywords("office excel PHPExcel");
$objProps->setCategory("Test");
//设置当前的sheet索引,用于后续的内容操作。
//一般只有在使用多个sheet的时候才需要显示调用。
//缺省情况下,PHPExcel会自动创建第一个sheet被设置SheetIndex=0
$objExcel->setActiveSheetIndex(0);
$objActSheet = $objExcel->getActiveSheet();
$objActSheet->setTitle('Sheet1'); //设置当前活动sheet的名称
$hang = 1; //行
if (is_array($fieldArr)) {//判断是否有字段名称行
$lie = 1; //列
foreach ($fieldArr as $k => $v) {
$objActSheet->setCellValue(AZ26($lie) . $hang, strval($v)); // 字符串内容
//设置格式为PHPExcel_Style_NumberFormat::FORMAT_NUMBER,避免某些大数字
//被使用科学记数方式显示,配合下面的 setAutoSize 方法可以让每一行的内容
//都按原始内容全部显示出来。
$objStyleA5 = $objActSheet->getStyle(AZ26($lie) . $hang);
//$objStyleA5->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
//设置字体
$objFontA5 = $objStyleA5->getFont();
$objFontA5->setName($fontFamily); //字体类型'Courier New'
$objFontA5->setSize($fontSize); //字体大小
$objFontA5->setBold(true); //是否加粗
//$objFontA5->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);//文字下面带有下划线
//$objFontA5->getColor()->setARGB('FFFF0000');
//$objFontA5->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
//$objFontA5->getFont()->setColor(PHPExcel_Style_Color::COLOR_RED);
//设置对齐方式
$objAlignA5 = $objStyleA5->getAlignment();
//$objAlignA5->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);//右对齐
$objAlignA5->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中对齐
//$objAlignA5->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置边框
//$objBorderA5 = $objStyleA5->getBorders();
//$objBorderA5->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//$objBorderA5->getTop()->getColor()->setARGB('FFFF0000'); // 边框color
//$objBorderA5->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//$objBorderA5->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//$objBorderA5->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//设置CELL填充颜色
//$objFillA5 = $objStyleA5->getFill();
//$objFillA5->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
//$objFillA5->getStartColor()->setARGB('FFEEEEEE');//背景颜色
$lie++;
}
$hang++; //行
}
foreach ($arr as $k => $v) {
$lie = 1; //列
foreach ($v as $k1 => $v1) {
$objActSheet->setCellValue(AZ26($lie) . $hang, strval($v1)); // 字符串内容
$lie++;
}
$hang++;
}
//设置单元格内容 由PHPExcel根据传入内容自动判断单元格内容类型
//$objActSheet->setCellValue('A1', '字符串内容字符串内容'); // 字符串内容
//$objActSheet->setCellValue('A2', 26); // 数值
//$objActSheet->setCellValue('A3', true); // 布尔值
//$objActSheet->setCellValue('A4', '=SUM(A2:A2)'); // 公式
//$objActSheet->setCellValueExplicit('A5', '8757584', PHPExcel_Cell_DataType::TYPE_STRING); //显式指定内容类型
//添加一个新的worksheet
$objExcel->createSheet();
$objExcel->getSheet(1)->setTitle('Sheet2');
$objExcel->createSheet();
$objExcel->getSheet(2)->setTitle('Sheet3');
//显示网格线:
$objExcel->getActiveSheet()->setShowGridlines(true);
//默认列宽
$objExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth($lineWidth); //默认行宽
$objExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight($lineHeight); //默认行高
//worksheet 默认style 设置 (和默认不同的需单独设置)
$objExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName($fontFamily); //默认字体Arial
$objExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize($fontSize); //默认字体大小
$objExcel->getActiveSheet()->getDefaultStyle()->getAlignment();
$objExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//输出内容
$outputFileName = $fileName . ".xls";
//$objWriter->save($outputFileName); //保存到文件
//输出到浏览器
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="' . $outputFileName . '"');
header("Content-Transfer-Encoding: binary");
header("Last-Modified: " . gmdate("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');
}
/**
* phpexcel导入(传入excal文件路径,返回二维数组)
* $excel_path excal文件路径
* $workarea 工作区默认第一个 从0开始
* $workarea = 1;//所有文件的第二个工作区(从0开始)
* $workarea = null;所有文件的所有工作区
* $workarea = array(1, 2, 3); //所有文件的1,2,3工作区
* $rwpath 拥有读写权限的目录(上传文件所放的地方)
* $is_del_file 是否删除原文件 默认删除
*/
function uploadExcel2($rwpath = './Uploads', $workarea = NULL, $is_del_file = 0) {
import('ORG.Excel.PHPExcel');
$return = array();
foreach ($_FILES as $k => $v) {
if ($v['error'] !== 0) {
continue;
}
$savePath = $rwpath . '/' . $k . time() . '.' . pathinfo($v['name'], PATHINFO_EXTENSION);
move_uploaded_file($v['tmp_name'], $savePath);
$objPHPExcel = PHPExcel_IOFactory::load($savePath);
if (is_array($workarea)) {
foreach ($workarea as $k1 => $v1) {
$return[$k][$v1] = $objPHPExcel->getSheet($v1)->toArray();
}
} else if ($workarea === NULL) {
$sheet = $objPHPExcel->getSheetCount(); //获取工作区个数
for ($i = 0; $i < $sheet; $i++) {
$return[$k][$i] = $objPHPExcel->getSheet($i)->toArray();
}
} else {
$return[$k][$workarea] = $objPHPExcel->getSheet($workarea)->toArray();
}
if ($is_del_file === 0) {
unlink($savePath);
}
}
return empty($return) ? FALSE : $return;
}
/**
* 利用phpexcel读取excel文件
* 此文件放在thinkphp的model中
*/
class ExcelModel extends BaseModel {
public function __construct($path) {
parent::__construct();
$this->path = $path;
$this->extension = pathinfo($this->path, PATHINFO_EXTENSION); //文件后缀名
$this->excel_type = $this->extension == "xlsx" ? "Excel2007" : "Excel5"; //文档类型
import('ORG.Excel.PHPExcel');
$this->objReader = PHPExcel_IOFactory::createReader($this->excel_type); //use excel2007 for 2007 format
$this->objPHPExcel = $this->objReader->load($this->path);
}
/**
* 用phpexcel读取一行数据
* $workArea 要读取哪个工作区 (区是从0开始的)
* $line 要获取哪一行的数据 (行是从一开始的)
*/
function getExcelLine($workArea, $line) {
$workArea = $workArea ? $workArea : 0;
$line = $line ? $line : 1;
$highestColumn = $this->objPHPExcel->getSheet($workArea)->getHighestColumn(); // 取得总列数
$lie = reAZ26($highestColumn); //将列转为10进制
$return = array();
for ($i = 1; $i <= $lie; $i++) {
$column = AZ26($i); //将列转为字母表示
$return[$column] = $this->objPHPExcel->getSheet($workArea)->getCell($column . $line)->getValue();
$return[$column] = $return[$column] === NULL ? '' : $return[$column];
}
return $return;
}
/**
* 用phpexcel读取一个工作区的数据(注意数据太大[大于2M]不推荐使用此方法)
* $workArea 工作区编号 默认0
*/
function getExcelArea($workArea) {
$workArea = $workArea ? $workArea : 0;
$highestRow = $this->objPHPExcel->getSheet($workArea)->getHighestRow(); // 取得总行数
$return = array();
for ($i = 1; $i < $highestRow; $i++) {
$return[$i] = $this->getExcelLine($workArea, $i);
}
return $return;
}
/**
* 获取excel文档某工作区有多少行
* $workArea 工作区编号 默认0
*/
function getLine($workArea) {
$workArea = $workArea ? $workArea : 0;
return $this->objPHPExcel->getSheet($workArea)->getHighestRow(); // 取得总行数
}
/**
* 获取excel文档某工作区有多少列
* $workArea 工作区编号 默认0
*/
function getColumn($workArea) {
$workArea = $workArea ? $workArea : 0;
return $this->objPHPExcel->getSheet($workArea)->getHighestColumn(); // 取得总列数
}
/**
* 将十进制数字转化为26进制用A-Z来表示
* @param type $n
* @return string
*/
function AZ26($n) {
$letter = range('A', 'Z', 1);
$s = '';
while ($n > 0) {
$m = $n % 26;
if ($m == 0) {
$m = 26;
}
$s = $letter[$m - 1] . $s;
$n = ($n - $m) / 26;
}
return $s;
}
/**
* 将A-Z表标的26进制数转为10进制数
* @param string $az 大写字母
*/
function reAZ26($az) {
$letter = array(1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E', 6 => 'F', 7 => 'G', 8 => 'H', 9 => 'I', 10 => 'J', 11 => 'K', 12 => 'L', 13 => 'M', 14 => 'N', 15 => 'O', 16 => 'P', 17 => 'Q', 18 => 'R', 19 => 'S', 20 => 'T', 21 => 'U', 22 => 'V', 23 => 'W', 24 => 'X', 25 => 'Y', 26 => 'Z');
$letter = array_flip($letter); //交换数组中的键和值
$len = strlen($az);
$raz = strrev($az);
$return = 0;
for ($i = 0; $i < $len; $i++) {
$return += $letter[$raz[$i]] * $this->xPowerN(26, $i);
}
return $return;
}
/**
* x的n次幂
*/
function xPowerN($x, $n) {
$return = $x;
if ($n == 0) {
return 1;
}
for ($i = 1; $i < $n; $i++) {
$return*=$x;
}
return $return;
}
}