tp5 phpoffice 导入导出 实例 日期读取问题解决
1.composer安装phpoffice
命令如下
composer require phpoffice/phpexcel
2.读取demo.xsl示例
命名空间
use PhpOfficePhpSpreadsheetCellCoordinate;
use PhpOfficePhpSpreadsheetReaderXlsx;
use PhpOfficePhpSpreadsheetReaderXls;
use PhpOfficePhpSpreadsheetReaderCsv;
use PhpOfficePhpSpreadsheetSharedDate;
use thinkexceptionPDOException;
use thinkCache;
use thinkConfig;
use thinkDb;
A、读取xsl的数据成数组
//读取excel的数据为数组
public function haha()
{
/*$file = $this->request->request('file');
if (!$file) {
$this->error(__('Parameter %s can not be empty', 'file'));
}*/
$filePath ='E:/betahost/mini.ejiegd.com/public/demo.xls';
//$filePath = ROOT_PATH . DS . 'public' . DS . $file;
if (!is_file($filePath)) {
$this->error(__('No results were found'));
}
//实例化reader
$ext = pathinfo($filePath, PATHINFO_EXTENSION);
if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
$this->error(__('Unknown data format'));
}
if ($ext === 'csv') {
$file = fopen($filePath, 'r');
$filePath = tempnam(sys_get_temp_dir(), 'import_csv');
$fp = fopen($filePath, "w");
$n = 0;
while ($line = fgets($file)) {
$line = rtrim($line, "\n\r\0");
$encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
if ($encoding != 'utf-8') {
$line = mb_convert_encoding($line, 'utf-8', $encoding);
}
if ($n == 0 || preg_match('/^".*"$/', $line)) {
fwrite($fp, $line . "\n");
} else {
fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
}
$n++;
}
fclose($file) || fclose($fp);
$reader = new Csv();
} elseif ($ext === 'xls') {
$reader = new Xls();
} else {
$reader = new Xlsx();
}
//加载文件
$insert = [];
try {
if (!$PHPExcel = $reader->load($filePath)) {
$this->error(__('Unknown data format'));
}
$currentSheet = $PHPExcel->getSheet(0); //读取文件中的第一个工作表
$allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
$allRow = $currentSheet->getHighestRow(); //取得一共有多少行
$maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
$fields = [];
for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
$fields[] = $val;
}
}
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
$values = [];
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
//第9列为日期格式
if($currentColumn==9){
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
if(!empty($val)){
$val_data =Date::excelToTimestamp($val);
$val =date('Y-m-d H:i:s',$val_data);
}else{
$val ='';
}
}else{
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
}
$values[] = is_null($val) ? '' : $val;
}
$row = [];
$temp = array_combine($fields, $values);
foreach ($temp as $k => $v) {
$row[$k] = $v;
}
if ($row) {
$insert[] = $row;
}
}
//缓存数据
$excel_data=$insert;
cache('excel_data',$excel_data);
print_r($excel_data);
} catch (Exception $exception) {
$this->error($exception->getMessage());
}
}
B、数据转存为xsl文件并下载
public function out(){
$xlsData=cache('arr');
//实例化
$objExcel = new \PHPExcel();
//设置文档属性
$objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
//设置内容
$objActSheet = $objExcel->getActiveSheet();
$key = ord("A");
$letter = explode(',', "A,B,C,D,E,F,G,H,I,J");
$arrHeader = array('券ID', '商业ID', '手机号', '券码类型', '券码编号','网点编号','网点名称','使用时间','核销状态','是否核对');
//填充表头信息
$lenth = count($arrHeader);
for ($i = 0; $i < $lenth; $i++) {
$objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
};
//填充表格信息
foreach ($xlsData as $k => $v) {
$k += 2;
//表格内容
$objActSheet->setCellValue('A' . $k, $v['id']);
$objActSheet->setCellValue('B' . $k, $v['businessid']);
$objActSheet->setCellValue('C' . $k, $v['phone']);
$objActSheet->setCellValue('D' . $k, $v['couponType']);
$objActSheet->setCellValue('E' . $k, $v['couponNo']);
$objActSheet->setCellValue('F' . $k, $v['storeOuCode']);
$objActSheet->setCellValue('G' . $k, $v['storeOuName']);
$objActSheet->setCellValue('H' . $k, $v['usedTime']);
$objActSheet->setCellValue('I' . $k, $v['status']);
$objActSheet->setCellValue('J' . $k, $v['is_check']);
// 表格高度
$objActSheet->getRowDimension($k)->setRowHeight(20);
}
$width = array(20, 20, 15, 10, 10, 30, 10, 15,20);
//设置表格的宽度
$objActSheet->getColumnDimension('A')->setWidth($width[5]);
$objActSheet->getColumnDimension('B')->setWidth($width[1]);
$objActSheet->getColumnDimension('C')->setWidth($width[0]);
$objActSheet->getColumnDimension('D')->setWidth($width[5]);
$objActSheet->getColumnDimension('E')->setWidth($width[5]);
$objActSheet->getColumnDimension('F')->setWidth($width[5]);
$objActSheet->getColumnDimension('G')->setWidth($width[5]);
$objActSheet->getColumnDimension('H')->setWidth($width[5]);
$objActSheet->getColumnDimension('I')->setWidth($width[5]);
$objActSheet->getColumnDimension('J')->setWidth($width[5]);
$outfile = md5("核对结果" . time()) . ".xlsx";
ob_end_clean();
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="' . $outfile . '"');
header("Content-Transfer-Encoding: binary");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$objWriter->save('php://output');
}