记录与分享 › 学习交流 › phpexcel 加黑色边框

Anonymous 发表于 2022-3-17 21:11:08

phpexcel 加黑色边框

$styleThinBlackBorderOutline = array(
      'borders' => array(
            'allborders' => array( //设置全部边框
                'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
            ),

      ),
    );
    $objPHPExcel->getActiveSheet()->getStyle( 'A1:K12')->applyFromArray($styleThinBlackBorderOutline);完整实例
<?php
$ym = $_SERVER['DOCUMENT_ROOT'];
include ($ym."/db.php");
//$flid = $_GET['id'];
//接受日期数据
$dates = $_GET['date'];
$zhbh = $_GET['zhbh'];

//用时间戳转换数字日期为年，拆分日期
$n = date("Y",strtotime("$dates"));
//转换为月
$y = date("m",strtotime("$dates"));


$s_n=date('Y', strtotime('-1 month')); //2022
$s_y=date('m', strtotime(date('Y-m-d') ));   //01
$zh = "select * from cxsj_zhsj where zhbh= '$zhbh' ";
$zh_rs = mysqli_query($db,$zh);
$zh_r = mysqli_fetch_assoc($zh_rs);

if (!isset($dates)) {

       $sql = "select * from cxsj_jgjs_files where year(endtime)='$s_n' and month(endtime)='$s_y' and zhbh='$zhbh' order by id asc ";
    $rs = mysqli_query($db,$sql);
    $row=mysqli_fetch_all($rs,MYSQLI_ASSOC);

    $htid = "select * from cxsj_zhsj where zhbh='{$zhbh}' order by id desc limit 1 ";
    $htidrs = mysqli_query($db,$htid);
    $htidrow = mysqli_fetch_assoc($htidrs);

$sql1="SELECT COUNT(*) AS count1 FROM cxsj_jgjs_files where year(endtime)='$s_n' and month(endtime)='$s_y' and zhbh='$zhbh'";
$rs1=mysqli_fetch_array(mysqli_query($db,$sql1));
$count1=$rs1['count1'];

   
       
}else{

$sql = "select * from cb_er_jfqk where year(jtsj)='$n' and month(jtsj)='$y' and zhbh='$zhbh' order by id asc";
$rs = mysqli_query($db,$sql);
$row=mysqli_fetch_all($rs,MYSQLI_ASSOC);

   }

/**给重要信息屏蔽
   * 添加星号
   * @param string $str 目标字符串
   * @param int $l 左侧留存长度
   * @param int $r 右侧留存长度
   * @param int $chr_len 星号数目
   * @param string $chr 星号或者其他自定义的字符
   * @return mixed 返回
   */

// function gr_asterisk($str = '',$l = 3,$r = 3,$chr_len = 6,$chr = '*'){
//         if(empty($str)){
//             return false;
//         }
//         $len_min = $l + $r;
//         if(mb_strlen($str,'utf-8') <= $len_min){
//             return str_repeat($chr,$chr_len);
//         }

//         $new_str = mb_substr($str,0,$l,'utf-8') . str_repeat($chr,$chr_len) . mb_substr($str,mb_strlen($str,'utf-8') - $r,$r);
//         return $new_str;
//   }

//gr_asterisk('1388888888',3,3,6); 导出方法
// var_dump($row);exit;
//$dir = dirname(__FILE__);//找到当前php文件所在路径
require $ym.'/phpexcel/PHPExcel.php';//引入类文件




$objPHPExcel = new PHPExcel();//实例化phpexcel，等同于在桌面上新建了一个表格
   

$objSheet = $objPHPExcel->getActiveSheet();

//$objSheet->getStyle('A1:L1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFFF0000'); //设置标题背景颜色
$objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->
setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置水平垂直居中
$objSheet->getRowDimension('1')->setRowHeight(30); //设置行高
$objSheet->getRowDimension('2')->setRowHeight(25); //设置行高
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);//字体加粗
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);   //设置字体大小

$objPHPExcel->getActiveSheet()->getStyle('A1:L1'); //水平居中                                                
$objPHPExcel->getActiveSheet()->mergeCells('A1:L1');//合并单元格
$xlsTitle = iconv('utf-8', 'gb2312', $zh_r['sqdw']."IDC机柜结算清单".$s_n.'-'.$s_y);//文件名称
$objPHPExcel->getDefaultStyle()->getFont()->setName('微软雅黑');// 设置字体
$objPHPExcel->getDefaultStyle()->getFont()->setSize(11);   // 设置字号
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);#设置单元格行高
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);#设置单元格宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(7);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(7);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(7);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(7);





$objSheet -> setCellValue("A1", $zh_r['sqdw']."IDC机柜结算清单".$s_n.'-'.$s_y);//字体大小;
for ($i=0; $i <=9 ; $i++) {
       

$objSheet = $objPHPExcel -> getActiveSheet();//获得当前sheet的操作对象
$objSheet -> setTitle($zh_r['sqdw']."IDC机柜结算清单".$dates);//给文件起名字


$objSheet -> setCellValue("A2","楼号")->setCellValue("B2","房间" )->setCellValue("C2","机柜")->setCellValue("D2","上柜时间")->setCellValue("E2","初始计费日期")->setCellValue("F2","本次计费日期")->setCellValue("G2","本次计费截止日期")->setCellValue("H2","计费月数")->setCellValue("I2","机柜千瓦数")->setCellValue("J2","单价")->setCellValue("K2","费用小计")->setCellValue("L2","状态");   

$j = 3;
foreach ($row as $k => $v) {

   $objSheet -> setCellValue("A".$j,$v['lou'])->setCellValue("B".$j,$v['fj'])->setCellValue("C".$j,$v['jg'])->setCellValue("D".$j,$v['sgsj'])->setCellValue("E".$j,$v['jfcs'])->setCellValue("F".$j,$v['bccs'])->setCellValue("G".$j,$v['bcjz'])->setCellValue("H".$j,$v['jfys'])->setCellValue("I".$j,$v['jgkws'])->setCellValue("J".$j,$v['dj'])->setCellValue("K".$j,$v['fyxj'])->setCellValue("L".$j,$v['zt']);

        $j++;
}


}
//黑色边框
$styleThinBlackBorderOutline = array(
      'borders' => array(
            'allborders' => array( //设置全部边框
                'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
            ),

      ),
    );
    $objPHPExcel->getActiveSheet()->getStyle( 'A1:L'.$j)->applyFromArray($styleThinBlackBorderOutline);

    //

//$objSheet->setCellValue('K57', "=SUM(K3:K56)");//计数原型
$objSheet->setCellValue('A'.$j, "合计");//计数原型
$objSheet->setCellValue('K'.$j, "=SUM(K3:K".($j-1).")");
$objSheet->setCellValue('D'.$j, $count1);

$objSheet->setCellValue('A'.($j), "合计")->getRowDimension($j)->setRowHeight(30);
$objSheet->setCellValue('C'.($j), "机柜数")->getRowDimension($j)->setRowHeight(30);
$objSheet->setCellValue('J'.($j), "金额")->getRowDimension($j)->setRowHeight(30);

$objSheet->setCellValue('A'.($j+1), "填报人")->getRowDimension($j+1)->setRowHeight(30);
$objSheet->setCellValue('C'.($j+1), "填报日期")->getRowDimension($j+1)->setRowHeight(30);
$objSheet->setCellValue('f'.($j+1), "部门负责人签字")->getRowDimension($j+1)->setRowHeight(30);


ob_end_clean();
ob_start();
// 下载这个表格，在浏览器输出
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;charset=utf-8;filename="'.$xlsTitle.'.xlsx"');

header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

$wenjian=PHPExcel_IOFactory::createWriter($objPHPExcel,"excel2007");
//$wenjian -> save("../demo.xlsx");
$wenjian->save('php://output'); //到浏览器
// alert('保存成功','../admin/product_list.php?page=1');

?>

查看完整版本: phpexcel 加黑色边框