phpexcel常用操作

谁践踏了优雅 2022-06-05 04:36 409阅读 0赞

## 一、创建多个工作表 ##

$objPHPExcel = new PHPExcel ();
    $objPHPExcel->setActiveSheetIndex(0);//新建第一个工作表
    $objPHPExcelActiveSheet = $objPHPExcel->getActiveSheet() ;
    $objPHPExcelActiveSheet->setTitle('装箱单');//工作表的名字
    $objPHPExcelActiveSheet->getTabColor()->setARGB(PHPExcel_Style_Color::COLOR_YELLOW); //工作表标签颜色
    
    $msgWorkSheet = new PHPExcel_Worksheet($objPHPExcel, '合同'); //新建第一个工作表
    $objPHPExcel->addSheet($msgWorkSheet);    
    $objPHPExcel->setActiveSheetIndex(1); //第二个工作表
    $objPHPExcelActiveSheet = $objPHPExcel->getActiveSheet() ;
    $objPHPExcelActiveSheet->getTabColor()->setARGB( PHPExcel_Style_Color::COLOR_BLUE); //工作表标签颜色

最后的效果图：

![Center][]

## 二、excel一些样式设置 ##

$objPHPExcelActiveSheet->getDefaultRowDimension()->setRowHeight(25);//设置默认行高
    
    $objPHPExcelActiveSheet->getStyle('A1')->getFont()->setBold(true)->setName('Calibri')->setSize(30);
    
    //setBold(true)设置加粗，setName设置字体，setSize设置字体大小
    
    
    $objPHPExcelActiveSheet->mergeCells('G3:I3');//合并单元格
    
    
    
    $objPHPExcelActiveSheet->getRowDimension(1)->setRowHeight(90);//设置指定行的行高
    
    
    $objPHPExcelActiveSheet->getColumnDimension('A')->setWidth(17);//设置列宽
    
    
    
    $objPHPExcelActiveSheet->getStyle('C1')->getAlignment()
    
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置水平对齐
    $objPHPExcelActiveSheet->getStyle('C1')->getAlignment()
    
    ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直对齐
    
    
    
    $styleArray = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
            ),
        ),
    );
    $objPHPExcelActiveSheet->getStyle('A13:I15')->applyFromArray($styleArray);//设置表格边框样式
    
    
    
    //设置打印格式
    $objPHPExcelActiveSheet->getPageSetup()
    
    ->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
    $objPHPExcelActiveSheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
    
    
    $objPHPExcelActiveSheet->getStyle('B4:B8')->getAlignment()->setWrapText(true);//自动换行
    
    
    $objPHPExcelActiveSheet->getStyle('A1:K1')->getFill()
    
    ->getStartColor()->setARGB('FFFFFF');//设置填充颜色
    
    
    $objPHPExcel->getActiveSheet()->setShowGridlines(true);//显示或隐藏网格线 /false
    
    
    $objPHPExcel->getActiveSheet()->freezePane('A2');//固定表头
    
    
    $objPHPExcel->getActiveSheet()->getRowDimension(1)->setVisible(false);//隐藏某一行
    
    $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);//设置自动填充到页面的宽度
    $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);

[Center]: /images/20220605/91f40b4ec11e41eca4df85eac55c9ef5.png