网站首页php
PHP导出导入excel类
发布时间：2016-03-24 01:47:22编辑：阅读（6879）
下载PHPExcel, From链接： http://phpexcel.codeplex.com/releases/view/119187
Excel导出数据组合函数：
/**
*生成execl内容
*@param $expTitle 标题
*@param $expCellName 列名
*@param $expTableData 数据
*@param $objExcel PHPExcel 对象
*
*Return $objExcel PHPExcel 对象
**/
function makeExcel($expTitle,$expCellName,$expTableData,$objExcel){
$objProps = $objExcel->getProperties();
$objProps->setCreator($expTitle);
$objProps->setTitle($expTitle);
$objProps->setSubject($expTitle);
$objExcel->setActiveSheetIndex(0);
$objActSheet = $objExcel->getActiveSheet();
$objActSheet->setTitle($expTitle);
$cellNum = count($expCellName);
$dataNum = count($expTableData);
$cellName = 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','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT',
'AU','AV','AW','AX','AY','AZ');
$objExcel->getActiveSheet()->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
$objExcel->getActiveSheet()->setCellValue('A1', $expTitle.' 生成时间:'.date('Y-m-d H:i:s'));//合并单元格生成标题
for($i=0;$i<$cellNum;$i++){
$objExcel->getActiveSheet()->setCellValueExplicit($cellName[$i].'2', $expCellName[$i][1],
PHPExcel_Cell_DataType::TYPE_STRING);
}
for($i=0;$i<$dataNum;$i++){
for($j=0;$j<$cellNum;$j++){
$objExcel->getActiveSheet()->setCellValueExplicit($cellName[$j].($i+3),
$expTableData[$i][$expCellName[$j][0]], PHPExcel_Cell_DataType::TYPE_STRING);
}
}
return $objExcel;
}3.在程序中调用导出excel：
public function actionexportExcel(){
require_once('Excel/PHPExcel.php');
require_once('Excel/PHPExcel/Writer/Excel5.php');
//require('Excel/PHPExcel/Writer/Excel2007.php'); //使用Excel2007导出.xlsx文件
$objExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel5($objExcel);
//$objWriter =new PHPExcel_Writer_Excel2007($objExcel);
$cellname = array(
array('id','ID'),
array('name','用户名'),
array('phone','手机'),
array('email','邮箱'),
array('addtime','注册时间'),
);
$dbo = new Db();
$dataset= $dbo->getAll("SELECT id,name,phone,email,additm from FROM w_user LIMIT 0,100");
$objExcel = makeExcel('导出人员', $cellname, $dataset, $objExcel);
$outputFilePath = "Upload/";
$outputFileName = "Members" . date('YmdHis') . ".xls";
//$outputFileName = "Members" . date('YmdHis') . ".xlsx"; //使用Excel2007导出.xlsx文件
$objWriter->save($outputFilePath . $outputFileName);
/**下载文件**/
Header("Content-type: application/octet-stream");
Header("Accept-Ranges: bytes");
Header("Accept-Length: ".filesize($outputFilePath . $outputFileName));
Header("Content-Disposition: attachment; filename=" . $outputFileName);
$file = fopen($outputFilePath . $outputFileName,"r");
echo fread($file,filesize($outputFilePath . $outputFileName));
fclose($file);
}4.excel导入数据组合函数
function excelToArray($filename, $encode='utf-8'){
require_once('Excel/PHPExcel.php');
$objExcel = new PHPExcel();
$objReader = PHPExcel_IOFactory::createReader('Excel5'); //or Excel2007
$objReader->setReadDataOnly(true);
$objPHPExcel= $objReader->load($filename);
$objWorksheet= $objPHPExcel->getActiveSheet();
$highestRow= $objWorksheet->getHighestRow();
$highestColumn= $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$excelData= array();
/***跳过第一行标题栏***/
for ($row = 2;$row<=$highestRow;$row++) {
for($col=0; $col<$highestColumnIndex; $col++) {
$excelData[$row][] = strval($objWorksheet->getCellByColumnAndRow($col, $row)->getValue());
}
}
return $excelData;
}
或者：
function excelToArray($path) {
$type = 'Excel2007';//设置为Excel5代表支持2003或以下版本，Excel2007代表2007版
$xlsReader = PHPExcel_IOFactory::createReader($type);
$xlsReader->setReadDataOnly(true);
$xlsReader->setLoadSheetsOnly(true);
$Sheets = $xlsReader->load($path);
//开始读取上传到服务器中的Excel文件，返回一个二维数组
$dataArray = $Sheets->getSheet(0)->toArray();
return $dataArray;
}5.在程序中处理上传excel文件，导入数据。
public function doimportjiaolianAction(){
if (! empty( $_FILES ['file_excel'] ['name'] ))
{
$tmp_file = $_FILES ['file_stu']['tmp_name'];
$file_types = explode ( ".", $_FILES ['file_stu']['name'] );
$file_type = $file_types [count ( $file_types ) - 1];
/*判别是不是.xls或者.xlsx文件，判别是不是excel文件*/
if (strtolower ( $file_type ) != "xls" && strtolower ( $file_type ) != "xlsx")
{
js_msg( '不是Excel文件，重新上传', 'history.back()' );
}
/*设置上传路径*/
$inputFilePath = $config['uploadpath'] . '/Excel/';
$inputFileName = "Order" . date('YmdHis') . $file_type;
/*是否上传成功*/
if (! copy ($tmp_file, $inputFilePath . $inputFileName ))
{
js_msg( '上传失败', 'history.back()' );
}
/*
* 对上传的Excel数据进行处理生成编程数据,这个函数会在下面第三步的ExcelToArray类中
* 注意：这里调用执行了第三步类里面的read函数，把Excel转化为数组并返回给$res,再进行数据库写入
*/
$res = $this->excelToArray( $inputFilePath . $inputFileName );
if(empty($res)){
js_msg('未找到数据', 'history.back()');
}
/*对生成的数组进行数据库的写入*/
foreach ( $res as $k => $v )
{
$name= $v[1];
$phone= $v[2];
$email= $v[3];
$addtime= $v[4];
$rows=array(
'name'=>$name,
'phone'=>$phone,
'email'=>$email,
'addtime'=>$addtime,
);
if (!(new Table('user'))->add($rows)) {
js_msg( '导入数据库失败' );
}
}
js_msg( '导入数据成功', '', '/user.html' );
}else{
js_msg( '上传未成功.', 'history.back()' );
}
}处理可以加上数据校验,及校验结果预览，确定无误后再入库。
-------------------------华丽丽的分割线------------------------
1, composer引入phpoffice
composer require phpoffice/phpspreadsheet
2, 导入导出方法封装
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\IOFactory;
private function importExcel($file, $sheet = 0, $jumpTo = 2) {
try {
if (!file_exists($file)) {
throw new \Exception('文件不存在!');
}
$objRead = IOFactory::createReader('Xlsx');
if (!$objRead->canRead($file)) {
$objRead = IOFactory::createReader('Xls');
if (!$objRead->canRead($file)) {
throw new \Exception('只支持导入Excel文件！');
}
}
$objRead->setReadDataOnly(true);
$obj = $objRead->load($file);
/* 获取指定的sheet表 */
$currSheet = $obj->getSheet($sheet);
if (isset($options['mergeCells'])) {
/* 读取合并行列 */
$options['mergeCells'] = $currSheet->getMergeCells();
}
/* 取得最大的列号 */
$columnH = $currSheet->getHighestColumn();
/* 兼容原逻辑，循环时使用的是小于等于 */
$columnCnt = Coordinate::columnIndexFromString($columnH);
/* 获取总行数 */
$rowCnt = $currSheet->getHighestRow();
$data = [];
/* 读取内容 */
for ($_row = $jumpTo; $_row <= $rowCnt; $_row++) {
$isNull = true;
for ($_column = 1; $_column <= $columnCnt; $_column++) {
$cellName = Coordinate::stringFromColumnIndex($_column);
$cellId = $cellName . $_row;
$cell = $currSheet->getCell($cellId);
if (isset($options['format'])) {
/* 获取格式 */
$format = $cell->getStyle()->getNumberFormat()->getFormatCode();
/* 记录格式 */
$options['format'][$_row][$cellName] = $format;
}
$data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());
if (!empty($data[$_row][$cellName])) {
$isNull = false;
}
}
if ($isNull) {
unset($data[$_row]);
}
}
return $data;
} catch (\Exception $e) {
dump($e->getMessage());
}
}
private function exportExcel($title, $data = [], $path = '') {
$spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$colcounter = count($title);
$idx = array_key_first($data);
$keys = $data ? array_keys($data[$idx]) : 0;
for ($i = 1; $i <= $colcounter; $i++) {
$sheet->setCellValue(Coordinate::stringFromColumnIndex($i) . '1', $title[$i - 1]);
}
$sheet->getStyle('A1:' . Coordinate::stringFromColumnIndex($i) . '1')->getFont()->setBold(true)->setColor((new Color)->setRGB('ff0000'));
foreach ($data as $index => $row) {
for ($j = 1; $j <= $colcounter; $j++) {
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($j) . ($index + 2), $row[$keys[$j - 1]], DataType::TYPE_STRING);
}
}
for ($k = 1; $k <= $colcounter; $k++) {
$sheet->getColumnDimension(Coordinate::stringFromColumnIndex($k))->setAutoSize(true);
}
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($path);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
}3, 使用方法
//使用方法
public function exportorderAction()
{
try {
$file = '/path/to/excel/file.xlsx';
$data = $this->importExcel($file, 0, 1);
dump($data);
$title = [
'ID', '编号', '类型', '用户', '地址', '总价', '...'
];
$newfile = '/path/to/excel/export.xlsx';
$this->exportExcel($title, $data, $newfile);
$result = [
'ret' => 0,
'msg' => '操作成功'
];
} catch (Exception $e) {
$result = [
'ret' => 30001,
'msg' => $e->getMessage(),
];
}
json($result);
}
评论