• php的Excel方法


    // 将base64图片保存为图片
            $imglist = [];
            for($i = 1; $i <= 3; $i++){
                if($info['piclist'.$i]){
                    $imglist[$i] = './uploads/jiaodi/imgs/'.date("His",time())."_".rand(1111,9999).'.png';
                    $base64_string = $info['piclist'.$i];
                    $base64_string= explode(',', $base64_string);
                    $data= base64_decode($base64_string[1]);
                    file_put_contents($imglist[$i], $data);
                }
            }
            
            // 导出excel
            $fileName = "技术交底.xls";
            import('PHPExcel/PHPExcel', EXTEND_PATH);
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->getProperties(); // 设置Excel的属性;
            $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); // 设置默认行高为 70
            $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(4); // 设置默认列宽
            $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(TRUE);// 设置默认自动换行
            $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('宋体')->setSize(10);// 
            // 设置默认样式水平居中
            $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            // 设置默认样式垂直居中
            $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            // 设置行高
            $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25);
            $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25);
            $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(35.5);
            $objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight(35.5);
            // 设置样式
            $objPHPExcel->getActiveSheet()->getStyle('A2:Z2')->getFont()->setSize(19);
            $objPHPExcel->getActiveSheet()->getStyle('A2:Z2')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('A1:Z1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            // 单元格赋值
            $objPHPExcel->getActiveSheet()->mergeCells('A1:Z1')->setCellValue('A1', 'LJA-C4-1-1');
            $objPHPExcel->getActiveSheet()->mergeCells('A2:Z2')->setCellValue('A2', '____________安全技术交底');
            $objPHPExcel->getActiveSheet()->mergeCells('A3:Z3')->setCellValue('A3', '');
            $objPHPExcel->getActiveSheet()->mergeCells('A4:D4')->setCellValue('A4', '单位工程名称');
            $objPHPExcel->getActiveSheet()->mergeCells('E4:J4')->setCellValue('E4', '');
            $objPHPExcel->getActiveSheet()->mergeCells('K4:N4')->setCellValue('K4', '施工单位');
            $objPHPExcel->getActiveSheet()->mergeCells('O4:T4')->setCellValue('O4', '');
            $objPHPExcel->getActiveSheet()->mergeCells('U4:V4')->setCellValue('U4', '日期');
            $objPHPExcel->getActiveSheet()->mergeCells('W4:Z4')->setCellValue('W4', ' 年 月 日');
    
            $objPHPExcel->getActiveSheet()->mergeCells('A5:D5')->setCellValue('A5', '施工部位');
            $objPHPExcel->getActiveSheet()->mergeCells('E5:J5')->setCellValue('E5', '');
            $objPHPExcel->getActiveSheet()->mergeCells('K5:N5')->setCellValue('K5', '施工内容');
            $objPHPExcel->getActiveSheet()->mergeCells('O5:Z5')->setCellValue('O5', '');
    
            $objPHPExcel->getActiveSheet()->mergeCells('A6:B19')->setCellValue('A6', '安全技术交底内容');
            $objPHPExcel->getActiveSheet()->mergeCells('C6:Z19')->setCellValue('C6', '交底内容:'.$info['content']);
    
            $objPHPExcel->getActiveSheet()->mergeCells('A20:E21')->setCellValue('A20', '总承包单位有关技术人员签名');
            $objPHPExcel->getActiveSheet()->mergeCells('F20:M21')->setCellValue('F20', '');
            $objPHPExcel->getActiveSheet()->mergeCells('N20:R21')->setCellValue('N20', '总承包单位专职安全生产管理人员签名');
            $objPHPExcel->getActiveSheet()->mergeCells('S20:Z21')->setCellValue('S20', $info['bname']);
    
            $objPHPExcel->getActiveSheet()->mergeCells('A22:B28')->setCellValue('A22', '分包单位工程项目相关技术人员签名');
            $objPHPExcel->getActiveSheet()->mergeCells('C22:Z28')->setCellValue('C22', $info['sname']);
    
            $objPHPExcel->getActiveSheet()->mergeCells('A29:Z35')->setCellValue('A29', '');
    
            // 绘制图像
            foreach($imglist as $key=>$vals){
                $wz = ord("B")+$key*5;
                $objDrawing = new PHPExcel_Worksheet_Drawing();
                $objDrawing->setPath($vals);
                $objDrawing->setHeight(80);
                $objDrawing->setWidth(80);
                $objDrawing->setCoordinates(chr($wz).'30');
                $objDrawing->setOffsetX(10);
                $objDrawing->setOffsetY(10);
                $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
            }
            
    
            $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表
            $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表
            ob_end_clean(); //会清除缓冲区的内容,并将缓冲区关闭,但不会输出内容。 
            ob_start(); //在服务器打开一个缓冲区来保存所有的输出。所以在任何时候使用echo ,输出都将被加入缓冲区中,直到程序运行结束或者使用ob_flush()来结束。然后在服务器中缓冲区的内容才会发送到浏览器,由浏览器来解析显示。 
            header('Content-Type: application/vnd.ms-excel');
            header("Content-Disposition: attachment;filename=$fileName");
            header('Cache-Control: max-age=0');
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output'); // 文件通过浏览器下载
          // 删除生成的图片
            foreach($imglist as $key=>$vals){
                unlink($vals);
            }

    exit();
    下面是总结的几个使用方法
    
    include 'PHPExcel.php';
    
    include 'PHPExcel/Writer/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');
    
    ——————————————————————————————————————–
    
    设置excel的属性:
    
    创建人
    
    $objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
    
    最后修改人
    
    $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
    
    标题
    
    $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
    
    题目
    
    $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
    
    描述
    
    $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX,generated using PHP classes.");
    
    关键字
    
    $objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
    
    种类
    
    $objPHPExcel->getProperties()->setCategory("Test result file");
    
    ——————————————————————————————————————–
    
    设置当前的sheet
    
    $objPHPExcel->setActiveSheetIndex(0);
    
    设置sheet的name
    
    $objPHPExcel->getActiveSheet()->setTitle('Simple');
    
    设置单元格的值
    
    $objPHPExcel->getActiveSheet()->setCellValue('A1', 'String');
    
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 12);
    
    $objPHPExcel->getActiveSheet()->setCellValue('A3', true);
    
    $objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');
    
    $objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');
    
    合并单元格
    
    $objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
    
    分离单元格
    
    $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
    
     
    
    保护
    
    cell$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); 
    
    // Needs to be set to true in order to enable any worksheet protection!
    
    $objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
    
    设置格式
    
    // Set cell number formatsecho 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' );
    
    设置宽width// Set column widths$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
    
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(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);
    
    $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
    
    $objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
    
    $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);
    
    设置
    
    align$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->
    
    setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    
    $objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->
    
    setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    
    $objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->
    
    setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    
    $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->
    
    setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
    
    //垂直居中
    
    $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->
    
    setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    
    设置column的border$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->
    
    setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle('B4')->
    
    getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    
    $objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->
    
    setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    
    $objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->
    
    setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    
    $objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->
    
    setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    
    设置border的color$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->
    
    getColor()->setARGB('FF993300');
    
    $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
    
    $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
    
    $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
    
    $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
    
    $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');
    
    设置填充颜色
    
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');
    
    $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    
    $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');
    
    加图片$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Logo');
    
    $objDrawing->setDescription('Logo');$objDrawing->setPath('./images/officelogo.jpg');
    
    $objDrawing->setHeight(36);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
    
    $objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Paid');
    
    $objDrawing->setDescription('Paid');$objDrawing->setPath('./images/paid.png');
    
    $objDrawing->setCoordinates('B15');$objDrawing->setOffsetX(110);
    
    $objDrawing->setRotation(25);$objDrawing->getShadow()->setVisible(true);
    
    $objDrawing->getShadow()->setDirection(45);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
    
    //处理中文输出问题需要将字符串转化为UTF-8编码,才能正常输出,否则中文字符将输出为空白,
    
    如下处理: $str  = iconv('gb2312', 'utf-8', $str);
    
    或者你可以写一个函数专门处理中文字符串:function convertUTF8($str)
    
    {   if(empty($str)) return '';   return  iconv('gb2312', 'utf-8', $str);
    
    }//从数据库输出数据处理方式从数据库读取数据如:
    
    $db = new Mysql($dbconfig);
    
    $sql = "SELECT * FROM  表名";
    
    $row = $db->GetAll($sql);  
    
    // $row 为二维数组
    
    $count = count($row);
    
    for ($i = 2; $i <= $count+1; $i++) { 
    
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertUTF8($row[$i-2][1])); 
    
    $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertUTF8($row[$i-2][2])); 
    
    $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertUTF8($row[$i-2][3])); 
    
    $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertUTF8($row[$i-2][4])); 
    
    $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertUTF8(date("Y-m-d", $row[$i-2][5]))); 
    
    $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertUTF8($row[$i-2][6])); 
    
    $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertUTF8($row[$i-2][7])); 
    
    $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertUTF8($row[$i-2][8]));
    
    }
    
      
    
    在默认sheet后,创建一个worksheetecho date('H:i:s') . " Create new Worksheet object
    ";
    
    $objPHPExcel->createSheet();
    
    $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
    
    $objWriter-save('php://output');

     现在php已经使用:

    composer中使用PhpSpreadsheet,原来的phpexcel已经不再更新;

     1 <?php
     2 
     3 use PhpOfficePhpSpreadsheetSpreadsheet;
     4 use PhpOfficePhpSpreadsheetIOFactory;
     5 use PhpOfficePhpSpreadsheetReaderXls;
     6 
     7 /**
     8      * 导出函数excel
     9      */
    10     protected function exported($fileName = '', $headArr = [], $data = []){
    11         $spreadsheet = new Spreadsheet();
    12         $sheet = $spreadsheet->getActiveSheet();
    13 
    14         // 数据处理
    15         $key = ord("A"); // 设置表头
    16         foreach ($headArr as $v) {
    17             $colum = chr($key);
    18             $sheet->setCellValue($colum . '1', $v);
    19             $key += 1;
    20         }
    21         $column = 2;
    22 
    23         foreach ($data as $key => $rows) { // 行写入
    24             $span = ord("A");
    25             foreach ($rows as $keyName => $value) { // 列写入
    26                 $sheet->setCellValue(chr($span) . $column, $value);
    27                 $span++;
    28             }
    29             $column++;
    30         }
    31 
    32         // 输出下载xls文件
    33         $fileName .= "_" . date("Y_m_d", time()) . ".xls";
    34         $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表
    35         ob_end_clean();
    36         ob_start();
    37         header('Content-Type: application/vnd.ms-excel');
    38         header("Content-Disposition: attachment;filename=$fileName");
    39         header('Cache-Control: max-age=0');
    40 
    41         $objWriter = IOFactory::createWriter($spreadsheet, 'Xls');
    42         $objWriter->save('php://output'); // 文件通过浏览器下载
    43     }
    44 
    45     /**
    46      * 导入excel
    47      */
    48     public function imports(){
    49         if(request()->file('file')){
    50             $file = request()->file('file')->getInfo();
    51         }else{
    52             $res['code']=1;
    53             $res['msg']='没有上传文件';
    54             return json($res);
    55         }
    56 
    57         $reader = new Xls();
    58         $spreadsheet = $reader->load($file['tmp_name']);
    59         $sheet = $spreadsheet->getActiveSheet();
    60         $highestRow         = $sheet->getHighestRow();    //获取总行数
    61 
    62         $data = [];
    63 
    64         for($i = 2; $i <=$highestRow; $i++){
    65             $arr = [];
    66             $arr['name'] = $sheet->getCell('A'.$i)->getValue();
    67             $zbget = $sheet->getCell('B'.$i)->getValue();
    68             $arr['gszhw'] = $sheet->getCell('C'.$i)->getValue();
    69             $sexget = $sheet->getCell('D'.$i)->getValue();
    70             $arr['phone'] = $sheet->getCell('E'.$i)->getValue();
    71             $arr['gname'] = $sheet->getCell('F'.$i)->getValue();
    72             $arr['address'] = $sheet->getCell('G'.$i)->getValue();
    73             $hyeget = $sheet->getCell('H'.$i)->getValue();
    74             $zhzmmget = $sheet->getCell('I'.$i)->getValue();
    75             $shhzhwget = $sheet->getCell('J'.$i)->getValue();
    76             $bzhangget = $sheet->getCell('K'.$i)->getValue();
    77             $rhtimeget = $sheet->getCell('L'.$i)->getValue();
    78             $arr['rhtime'] = strtotime($rhtimeget);
    79             $data[] = $arr;
    80         }
    81 
    82         // var_dump($data);
    83         $res = db('users')->insertAll($data);
    84     }
  • 相关阅读:
    CRM 2013 相关下载 / 2013-10-11
    四十,Importer extension 主要(/imports)
    三十九,GeoWebCache 播种和截断(/seed)
    三十八,GeoWebCache 重新加载(/reload)
    三十七,GeoWebCache 质量截断(/statistics)
    三十六,GeoWebCache 质量截断(/masstruncate)
    三十五,GeoWebCache 图层(/layers)
    三十四,GeoWebCache 主页(/index)
    三十三,GeoWebCache 网格集(/gridsets)
    三十二,GeoWebCache 全局配置(/global)
  • 原文地址:https://www.cnblogs.com/ziyandeyanhuo/p/11175828.html
Copyright © 2020-2023  润新知