<?php
namespace app\admin\tool;
use think\Loader;
Loader::import('PHPExcel.PHPExcel',EXTEND_PATH);
Loader::import('PHPExcel.PHPExcel.IOFactory',EXTEND_PATH);
class Excel
{
private $excelCopy = 'Excel5';
private $suffix = 'xls';
private $objPHPExcel = null;
public function __construct()
{
$this->objPHPExcel = new \PHPExcel();
$this->excelCopy = 'Excel5';
if ($this->excelCopy == 'Excel5') {
$this->suffix = '.xls';
} else if ($this->excelCopy == 'Excel2007') {
$this->suffix = '.xlsx';
}
}
public function export($fileName="Excel", $headerArr = array(), $data = array(),$sheet = 'Sheet1')
{
$this->objPHPExcel->setActiveSheetIndex(0);
$objSheet = $this->objPHPExcel->getActiveSheet();
$objSheet->setTitle($sheet);
$headCharArr = $this->getHeaderChar($headerArr);
foreach ($headCharArr as $k => $v) {
$objStyle = $objSheet->getStyle($headCharArr[$k] . '1');
$objAlign = $objStyle->getAlignment();
$objAlign->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objAlign->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objSheet->setCellValue($headCharArr[$k] . '1', $headerArr[$k]);
}
$j = 2;
foreach ($data as $k => $v) {
foreach ($headerArr as $k1 => $v1) {
$objStyle = $objSheet->getStyle($headCharArr[$k1] . $j);
$objAlign = $objStyle->getAlignment();
$objAlign->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objAlign->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$val = $v[$k1].'';
$objSheet->setCellValue($headCharArr[$k1] . $j, $val);
}
$j++;
}
$objWriter = \PHPExcel_IOFactory::createWriter($this->objPHPExcel, $this->excelCopy);
$fileName = $fileName . ($this->suffix);
$copy = $this->excelCopy;
$this->browser_export($copy, $fileName);
$objWriter->save('php://output');
}
private function getHeaderChar($data = array())
{
$index = 65;
$char = '';
$charArr = array();
foreach ($data as $k => $v) {
$charArr[$k] = $char . chr($index++);
if ($index == 91) {
$index = 65;
$char .= 'A';
}
}
return $charArr;
}
private function browser_export($copy, $fileName)
{
ob_end_clean();
if ($copy == 'Excel5') {
header('Content-Type: application/vnd.ms-excel;');
} else {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
}
header('Content-Disposition: attachment;filename="' . iconv('utf-8', 'gb2312', $fileName) . '"');
header('Cache-Control: max-age=0');
}
public function importByInput($format = array(),$inputname='excel', $sheetName = 'Sheet1')
{
$data = $this->importExcel($_FILES[$inputname]['tmp_name'], $sheetName);
return $this->dealImportData($format,$data);
}
public function import($format = array(),$filename='', $sheetName = 'Sheet1')
{
$data = $this->importExcel($filename, $sheetName);
return $this->dealImportData($format,$data);
}
private function dealImportData($format,$data){
if (!$format) {
return $data;
} else {
$newdata=array();
foreach($data as $k=>$v){
$row=array();
foreach($v as $k2=>$v2){
if($format[trim($k2)]){
$row[$format[trim($k2)]]=trim($v2);
}
}
$newdata[]=$row;
}
return $newdata;
}
}
public function importExcel($filename, $sheetName = 'Sheet1')
{
header("Content-Type:text/html;charset=utf-8");
$fileType = \PHPExcel_IOFactory::identify($filename);
$objReader = \PHPExcel_IOFactory::createReader($fileType);
$sheetName = array($sheetName);
$objReader->setLoadSheetsOnly($sheetName);
$objPHPExcel = $objReader->load($filename);
$key = array();
$value = array();
foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
foreach ($sheet->getRowIterator() as $row) {
$temp = array();
foreach ($row->getCellIterator() as $kk=> $cell) {
if ($row->getRowIndex() < 2) {
$key[$kk] = $cell->getValue();
} else {
$data = $cell->getValue();
$temp[$kk] = $data;
}
}
if (!empty($temp)) {
$value[] = $temp;
}
}
}
$data = array();
foreach ($value as $k => $v) {
$temp = array();
foreach ($v as $k1 => $v1) {
$temp[$key[$k1]] = $v1;
}
$data[] = $temp;
}
return $data;
}
}
设置单元格导出格式问文本
$objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$j,$result[1],PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->getStyle('B'.$j)->getNumberFormat()->setFormatCode("@");