Hướng dẫn create sheet phpexcel - tạo trang tính phpexcel
|
$objPHPExcel->getActiveSheet()->mergeCells('C4:C5');//Gộp 2 cột hoặc 2 hàng lại với nhau objPHPExcel->getActiveSheet()->mergeCells('C4:C5');//Gộp 2 cột hoặc 2 hàng lại với nhau $highestRow=$objWorksheet->getHighestRow();// e.g. 10 Hàng cuối cùnghighestRow=$objWorksheet->getHighestRow(); // e.g. 10 Hàng cuối cùng $highestColumn=$objWorksheet->getHighestColumn();// e.g 'F' Cột cuối cùnghighestColumn=$objWorksheet->getHighestColumn();// e.g 'F' Cột cuối cùng $highestColumnIndex=PHPExcel_Cell::columnIndexFromString($highestColumn);// e.g. 5 Cột cuối cùnghighestColumnIndex= PHPExcel_Cell::columnIndexFromString($highestColumn);// e.g. 5 Cột cuối cùng $objPHPExcel->getActiveSheet()->getStyle('A1:A4')->getFont()->setBold(true);//Tô đậm chữobjPHPExcel->getActiveSheet()->getStyle('A1:A4')->getFont()->setBold(true); //Tô đậm chữ $objPHPExcel->getActiveSheet()->getStyle('A1:A4')->getFont()->setItalic(true);//In nghiêng chữobjPHPExcel->getActiveSheet()->getStyle('A1:A4')->getFont()->setItalic(true);//In nghiêng chữ $objPHPExcel->getActiveSheet()->getStyle('A1:A4')->getFont()->setName('Times New Roman');//Font chữobjPHPExcel->getActiveSheet()->getStyle('A1:A4')->getFont()->setName('Times New Roman');//Font chữ $objPHPExcel->getActiveSheet()->getStyle('A1:A4')->getFont()->setSize(12);//Cỡ chữobjPHPExcel->getActiveSheet()->getStyle('A1:A4')->getFont()->setSize(12);//Cỡ chữ $objPHPExcel->getActiveSheet()->getStyle('A1:C5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//Canh chữ ở giữaobjPHPExcel->getActiveSheet()->getStyle('A1:C5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //Canh chữ ở giữa $objPHPExcel->getActiveSheet()->getStyle('A1:C5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//Canh chữ ở giữaobjPHPExcel->getActiveSheet()->getStyle('A1:C5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //Canh chữ ở giữa $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(14);//Mặc định độ rộng cộtobjPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(14);//Mặc định độ rộng cột $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(14);//Mặc định độ cao hàngobjPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(14); //Mặc định độ cao hàng $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(5);//Độ rộng cộtobjPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(5);//Độ rộng cột $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(5);//Độ cao hàngobjPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(5); //Độ cao hàng $objPHPExcel->getActiveSheet()->freezePane('A6');//Cố định hàngobjPHPExcel->getActiveSheet()->freezePane('A6');//Cố định hàng $objPHPExcel->getActiveSheet()->setTitle('TEN');//Tên sheetobjPHPExcel->getActiveSheet()->setTitle('TEN'); //Tên sheet $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1); //Căn vừa chiều rộngobjPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1); //Căn vừa chiều rộng $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);//Căn vừa chiều caoobjPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0); //Căn vừa chiều cao $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.2);//Margin bên phảiobjPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.2);//Margin bên phải $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.4);//Margin bên tráiobjPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.4); //Margin bên trái $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.4);//Margin bên trênobjPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.4);//Margin bên trên $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0);//Margin bên dướiobjPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0); //Margin bên dưới $objPHPExcel->getActiveSheet()->getPageMargins()->setFooter(0);//Margin FooterobjPHPExcel->getActiveSheet()->getPageMargins()->setFooter(0);//Margin Footer $objPHPExcel->getActiveSheet()->getStyle('E20:F20')->getNumberFormat()->setFormatCode("#,##0_);[Black](#,##0)");//Kiểu tiền tệobjPHPExcel->getActiveSheet()->getStyle('E20:F20')->getNumberFormat()->setFormatCode("#,##0_);[Black](#,##0)"); //Kiểu tiền tệ //Thêm hình ảnh $objDrawing=newPHPExcel_Worksheet_HeaderFooterDrawing();objDrawing=newPHPExcel_Worksheet_HeaderFooterDrawing(); $objDrawing->setName('Image');objDrawing->setName('Image'); $objDrawing->setPath('Đường dẫn hình');objDrawing->setPath('Đường dẫn hình'); $objDrawing->setHeight(50);objDrawing->setHeight(50); $objDrawing->setWidth(130);objDrawing->setWidth(130); $objDrawing->setCoordinates('G3');//Chèn hìnhobjDrawing->setCoordinates('G3'); //Chèn hình $objPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing,PHPExcel_Worksheet_HeaderFooter::IMAGE_FOOTER_LEFT);// Chèn hình vào heaher footer trái/giữa/phảiobjPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing,PHPExcel_Worksheet_HeaderFooter::IMAGE_FOOTER_LEFT);// Chèn hình vào heaher footer trái/giữa/phải $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&');// Chèn hình vào heaher footer trái/giữa/phảiobjPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&');// Chèn hình vào heaher footer trái/giữa/phải $objPHPExcel->getActiveSheet()->getStyle('A4:C5')->applyFromArray(objPHPExcel->getActiveSheet()->getStyle('A4:C5')->applyFromArray( array(array( 'borders'=>array('borders'=>array( 'allborders'=>array('allborders'=>array( 'style'=>PHPExcel_Style_Border::BORDER_THIN'style'=> PHPExcel_Style_Border::BORDER_THIN )) )) ) );//Kẻ border toàn khung kiểu mảnh ;//Kẻ border toàn khung kiểu mảnh $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray( array(array( 'font'=>array('font'=>array( 'underline'=>PHPExcel_Style_Font::UNDERLINE_SINGLE,'underline'=>PHPExcel_Style_Font::UNDERLINE_SINGLE, )) ) );//Kẻ border toàn khung kiểu mảnh ; //Gạch chân $objPHPExcel->getActiveSheet()->getStyle('A19:G19')->applyFromArray(objPHPExcel->getActiveSheet()->getStyle('A19:G19')->applyFromArray( array(array( 'font'=>array('fill'=> array( 'underline'=>PHPExcel_Style_Font::UNDERLINE_SINGLE,'type'=>PHPExcel_Style_Fill::FILL_SOLID, );//Gạch chân'color'=>array('rgb'=>'c6f2c8') 'fill'=>array(), )) 'type'=>PHPExcel_Style_Fill::FILL_SOLID,;//Đổ màu cho ô 'color'=>array('rgb'=>'c6f2c8') if(pathinfo($_FILES['import']['name'],PATHINFO_EXTENSION)=="xls"){(pathinfo($_FILES['import']['name'],PATHINFO_EXTENSION) =="xls"){ $objReader=PHPExcel_IOFactory::createReader('Excel5');objReader=PHPExcel_IOFactory::createReader('Excel5'); ), elseif(pathinfo($_FILES['import']['name'],PATHINFO_EXTENSION)=="xlsx"){if (pathinfo($_FILES['import']['name'],PATHINFO_EXTENSION)=="xlsx"){ $objReader=PHPExcel_IOFactory::createReader('Excel2007');objReader= PHPExcel_IOFactory::createReader('Excel2007'); ), $objReader->setReadDataOnly(false);objReader->setReadDataOnly(false); );//Đổ màu cho ôobjPHPExcel=$objReader->load($_FILES['import']['tmp_name']); //File hoặc đường dẫn $objWorksheet=$objPHPExcel->getActiveSheet();objWorksheet=$objPHPExcel->getActiveSheet(); for($row=2;$row($row=2;$row<= $highestRow;++$row){ $val=array();val=array(); for($col=0;$col($col=0; $col<$highestColumnIndex;++$col){ $cell=$objWorksheet->getCellByColumnAndRow($col,$row);cell=$objWorksheet->getCellByColumnAndRow($col, $row); //Đọc file excel foreach($objWorksheet->getMergeCells()as$cells){($objWorksheet->getMergeCells()as$cells){ if($cell->isInRange($cells)){($cell->isInRange($cells)) { $currMergedCellsArray=PHPExcel_Cell::splitRange($cells);currMergedCellsArray=PHPExcel_Cell::splitRange($cells); $cell=$objWorksheet->getCell($currMergedCellsArray[0][0]);cell=$objWorksheet->getCell($currMergedCellsArray[0][0]); break;; ), ), );//Đổ màu cho ô $val[]=is_numeric($cell->getCalculatedValue())?round($cell->getCalculatedValue()):$cell->getCalculatedValue();val[]=is_numeric($cell->getCalculatedValue())? round($cell->getCalculatedValue()):$cell->getCalculatedValue(); //Đọc file excel } ), ), |
