Method of Reading and Writing excel of xls File Using PHPExcel in php

  • 2021-07-18 07:42:57
  • OfStack

This article describes the example of PHP using PHPExcel to read and write excel (xls) file method, very practical. Share it with you for your reference. The specific methods are as follows:

Many PHP class libraries have problems when reading Chinese xls and csv files. After searching for the following information on the Internet, it is found that PHPExcel class libraries are easy to use. The address in official website is: http://phpexcel.codeplex.com/. The methods for PHPExcel to read and write Excel are described as follows:

1. Read the contents of xls file


<?php
  // Toward xls File write content 
  error_reporting(E_ALL);
  ini_set('display_errors', TRUE);  
  include 'Classes/PHPExcel.php';      
  include 'Classes/PHPExcel/IOFactory.php'; 
  //$data:xls Text of document content 
  //$title:xls File content title 
  //$filename: File name exported 
  //$data And $title Must be utf-8 Code, otherwise it will be written to FALSE Value 
  function write_xls($data=array(), $title=array(), $filename='report'){
    $objPHPExcel = new PHPExcel();
    // Setting document attributes, setting Chinese will produce garbled codes, which need to be converted to utf-8 Format! ! 
    // $objPHPExcel->getProperties()->setCreator(" Yun Shu ")
               // ->setLastModifiedBy(" Yun Shu ")
               // ->setTitle(" Products URL Export ")
               // ->setSubject(" Products URL Export ")
               // ->setDescription(" Products URL Export ")
               // ->setKeywords(" Products URL Export ");
    $objPHPExcel->setActiveSheetIndex(0);
    
    $cols = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    // Settings www.ofstack.com Title 
    for($i=0,$length=count($title); $i<$length; $i++) {
      //echo $cols{$i}.'1';
      $objPHPExcel->getActiveSheet()->setCellValue($cols{$i}.'1', $title[$i]);
    }
    // Set the title style 
    $titleCount = count($title);
    $r = $cols{0}.'1';
    $c = $cols{$titleCount}.'1';
    $objPHPExcel->getActiveSheet()->getStyle("$r:$c")->applyFromArray(
      array(
        'font'  => array(
          'bold'   => true
        ),
        'alignment' => array(
          'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
        ),
        'borders' => array(
          'top'   => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN
          )
        ),
        'fill' => array(
          'type'    => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
          'rotation'  => 90,
          'startcolor' => array(
            'argb' => 'FFA0A0A0'
          ),
          'endcolor'  => array(
            'argb' => 'FFFFFFFF'
          )
        )
      )
    );
    
    $i = 0;
    foreach($data as $d) { // Used here foreach, Supports associative arrays and numerically indexed arrays 
      $j = 0;
      foreach($d as $v) {  // Used here foreach, Supports associative arrays and numerically indexed arrays 
        $objPHPExcel->getActiveSheet()->setCellValue($cols{$j}.($i+2), $v);
        $j++;
      }
         $i++;
    }
    //  Generate 2003excel Format xls Documents 
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
  }
  $array = array(
    array(1111,' Name ',' Brand ',' Trade name ','https://www.ofstack.com'),
    array(1111,' Name ',' Brand ',' Trade name ','https://www.ofstack.com'),
    array(1111,' Name ',' Brand ',' Trade name ','https://www.ofstack.com'),
    array(1111,' Name ',' Brand ',' Trade name ','https://www.ofstack.com'),
    array(1111,' Name ',' Brand ',' Trade name ','https://www.ofstack.com'),
  );
  write_xls($array,array(' Commodity id',' Name of Supplier ',' Brand ',' Trade name ','URL'),'report');
  
?>

2. Write content to xls file


<?php
  // Get database data (mysqli Pretreatment learning )
  $config = array(
    'DB_TYPE'=>'mysql',
    'DB_HOST'=>'localhost',
    'DB_NAME'=>'test',
    'DB_USER'=>'root',
    'DB_PWD'=>'root',
    'DB_PORT'=>'3306',
  );
  function getProductIdByName($name) {
    global $config;
    $id = false;
    
    $mysqli = new mysqli($config['DB_HOST'], $config['DB_USER'], $config['DB_PWD'], $config['DB_NAME']);
    if(mysqli_connect_error()) {  // Compatible  < php5.2.9 OO way:$mysqli->connect_error
      die(" Connection failed with error code: ".mysqli_connect_errno()." Error message: ".mysqli_connect_error());
    }
    // Set the code for connecting to the database, and don't forget to set 
    $mysqli->set_charset("gbk");
    // The coding of Chinese characters should be related to the database 1 If it is not set, the result is null
    $name = iconv("utf-8", "gbk//IGNORE", $name);
    if($mysqli_stmt = $mysqli->prepare("select id from 137_product where name like ?")) {
      $mysqli_stmt->bind_param("s", $name);
      $mysqli_stmt->execute();
      $mysqli_stmt->bind_result($id);
      $mysqli_stmt->fetch();
      $mysqli_stmt->close();
    }
    $mysqli->close(); 
    return $id;  // What I get is gbk Code (same as database code) 
  }  
  $id = getProductIdByName('% Inai Sanitary Ware Inai Split Toilet %');
  var_dump($id);
?>

I hope this article is helpful to everyone's PHP programming


Related articles: