-- 创造无限可能

PHPExcel使用

2022-07-12 15:21:28
636 人浏览 2 人点赞
有用，点赞支持一下

安装 - phpexcel

composer require phpoffice/phpexcel

1.设置excel属性

1.创建人 

$objPHPExcel->getProperties()->setCreator("HuangJu");

2.最后修改人 

$objPHPExcel->getProperties()->setLastModifiedBy("JJ");

3.标题

$objPHPExcel->getProperties()->setTitle("office2007 document");

4.题目

$objPHPExcel->getProperties()->setSubject("office2007 document");

4.题目

$objPHPExcel->getProperties()->setDescription("office2007 document");

5.关键字

$objPHPExcel->getProperties()->setKeywords("office2007 document");

5.种类

$objPHPExcel->getProperties()->setCategory("office2007 document");

5.设置当前sheet

$objPHPExcel->setActiveSheetIndex(0);

5.设置sheet的name

$objPHPExcel->getActiveSheet()->setTitle('name1');

6.设置单元格的值 

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Str');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 12);
$objPHPExcel->getActiveSheet()->setCellValue('A3', true);
$objPHPExcel->getActiveSheet()->setCellValue('B1', '=SUM(C2:C4)');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '=MIN(B1:B2)');

7.合并单元格

$objPHPExcel->getActiveSheet()->mergeCells("A1:E22");

8.分离单元格 

$objPHPExcel->getActiveSheet()->unmergeCells('A28:E29');

9.保护cell 

// Needs to be set to true in order to enable any worksheet protection！
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');

10.设置格式 

// Set cell number formats
echo date('H:i:s') . " Set cell number formats";
$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13');

11.设置宽width 

//Set column width
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

12.设置font 

$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

13.设置align 

$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
//垂直居中
$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

14.设置column的border

$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

14.设置border的color

$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getLeft()->getColor()->setRGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getLeft()->getColor()->setARGB('FF993300');

15.设置填充颜色 

$objPHPExcel->getActiveSheet()->getStyle('A4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A4')->getFill()->getStartColor()->setARGB('FF808080');

16.加图片

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./test.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Paid');
$objDrawing->setDescription('Paid');
$objDrawing->setPath('./test.png');
$objDrawing->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

17.处理中文输出问题
需要将字符串转化UTF-8编码，才能正常输出，否则中文字符输出空白，如下处理:
$str = iconv(‘gb2312’, ‘utf-8’, $str);
或者可以写一个函数专门处理中文字符

function convertUTF($str){
    if(empty($str)) return '';
    return iconv('gb2312', 'utf-8', $str); 
}

从数据库输出数据处理方式 — 读取如下 

$dbconfig = array();
$db = new Mysql($dbconfig);
$sql = "SELECT * FROM tabel_name";
$row = $db->GetAll($sql);  // $row 为二维数组

$count = count($row);
for ($i = 2; $i <= $count + 1; $i++){
    $objPHPExcel->getActiveSheet()->setCellValue('A', $i, convert_uuencode($row[$i-2][1])); // 
    $objPHPExcel->getActiveSheet()->setCellValue('B', $i, convert_uuencode(date('Y-m-d', $row[$i-2][2])));
}

使用PHPEXcel判别和格式化Excel中的日期格式的例子
phpexcel导出的颜色与网页中颜色不一致的解决方法
CI中使用PHPExcel导出数据到Excel

echo date('H:i:s') . 'Create new Worksheet object\n';
$objPHPExcel->createSheet();

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter-save('php://output');

实例

include 'PHPExcel.php';
include 'PHPExcel/Wrter/Excel2007.php';
// 或者include 'PHPExcel/Writer/Excel5.php'; 用于输出 .xls 的
// 创建一个excel
$objPHPExcel = new PHPExcel();

// 保存excel-2007格式
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
// 或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
$objWriter->save('xxx.xlsx');

// 直接输出到浏览器
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
header('Pragma:public');
header('Expires: 0');
header('Cache-Control:must-revalidate.post-check=0,pre-check=0');
header('Content-Type:application/force-download');
header('Content-Type:application/vnd.ms-execl');
header('Content-Type:application/octet-stream');
header('Content-Type:application/download');
header('Content-Disposition:attachment;filename="resume.xls"');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');

2.读取

1.读取文件

$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);

2.特定类读取

$objReader = new PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($inputFileName);

3.创建读取类

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

读取类型有 Excel5、Excel2007、Excel2003XML、OOCalc、SYLK、Gnumeric、CSV
4.鉴别读取类

$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileType);

5.只读数据，忽略格式（对Excel读取，有很大优化）

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objReader->load($inputFileName);

6.加载Excel所有工作表

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadAllSheets(); //加载所有的工作表
$objPHPExcel = $objReader->load($inputFileName);
$objPHPExcel->getSheetCount();  // 获取工作表个数
$objPHPExcel->getSheetNames(); // 获取所有工作表名字组

7.加载单个命名工作表

$sheetname = 'Data Sheet #2';  // 单个工作表，传入字符串
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetsOnly($sheetname);
$objPHPExcel = $objReader->load($inputFileName);

8.加载多个命名的工作表

$sheetnames = array('Data Sheet 1', 'Data Sheet 2');
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetOnly($sheetnames);
$objPHPExcel = $objReader->load($inputFileName);

9.自定义一读取过滤器

class MyReadFilter implements PHPExcel_Reader_IReadFilter{          
    public function readCell($column, $row, $worksheetName = '') {
        // 只读去1-7⾏&A－E列中的单元格              
        if ($row >= 1 && $row <= 7) {                  
            if (in_array($column,range('A','E'))) {                      
                return true;                  
            }              
        }              
        return false;          
    }      
}      

$filterSubset = new MyReadFilter();      
$objReader = PHPExcel_IOFactory::createReader($inputFileType);      
$objReader->setReadFilter($filterSubset);        
// 设置实例化的过滤器对象      
$objPHPExcel = $objReader->load($inputFileName);

10.同样是自定义一个读取过滤器,但可配置读取的行和列范围

class MyReadFilter implements PHPExcel_Reader_IReadFilter{          
    private $_startRow = 0;     
    // 开始⾏          
    private $_endRow = 0;       
    // 结束⾏          
    private $_columns = array();    
    // 列跨度          
    public function __construct($startRow, $endRow, $columns) {              
        $this->_startRow = $startRow;              
        $this->_endRow       = $endRow;              
        $this->_columns      = $columns;          
    }          
    public function readCell($column, $row, $worksheetName = '') {              
        if ($row >= $this->_startRow && $row <= $this->_endRow) {                  
            if (in_array($column,$this->_columns)) {                      
                return true;                  
            }              
        }              
        return false;          
    }
}

相关文章