Community Learn Tools Library Leisure
search
English
Home > Backend Development > PHP Tutorial > php pack导出数据倒excel表中文乱码问题

php pack导出数据倒excel表中文乱码问题

WBOY
Release： 2016-06-06 20:06:11
Original
1111 people have browsed it

需要用php将数据导出倒excel表中，用的是php的pack函数将字符打包成二进制，然后写入Excel表格文件，英文字符导入正常，现在中文字符全部乱码了，不知道哪里设置的问题呢？

<code><?php /**
 * Simple excel writer class with no external dependencies, drop it in and have fun
 * @author Matt Nowack
 * @link https://gist.github.com/ihumanable/929039/edit
 * @license Unlicensed
 * @version 1.0
 */
class Excel {
  private $col;
  private $row;
  private $data;
  private $title;
  
  /**
   * Safely encode a string for use as a filename
   * @param string $title The title to use for the file
   * @return string The file safe title
   */
  static function filename($title) {
    $result = strtolower(trim($title));
    $result = str_replace("'", '', $result);
    $result = preg_replace('#[^a-z0-9_]+#', '-', $result);
    $result = preg_replace('#\-{2,}#', '-', $result);
    return preg_replace('#(^\-+|\-+$)#D', '', $result);
  }
  
  /**
   * Builds a new Excel Spreadsheet object
   * @return Excel The Spreadsheet
   */
  function __construct($title) {
    $this->title = $title;
    $this->col = 0;
    $this->row = 0;
    $this->data = '';
    $this->bofMarker();
  }
  
  /**
   * Transmits the proper headers to cause a download to occur and to identify the file properly
   * @return nothing
   */
  function headers() {
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header("Content-Type: application/vnd.ms-excel; charset=iso-8859-1");
    header("Content-Disposition: attachment;filename=" . Excel::filename($this->title) . ".xls ");
    header("Content-Transfer-Encoding: binary ");
  }
  
  function send() {
    $this->eofMarker();
    // $this->headers();
    echo $this->data;
  }
  
  /**
   * Writes the Excel Beginning of File marker
   * @see pack()
   * @return nothing
   */
  private function bofMarker() { 
    $this->data .= pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
  }
  
  /**
   * Writes the Excel End of File marker
   * @see pack()
   * @return nothing
   */
  private function eofMarker() { 
    $this->data .= pack("ss", 0x0A, 0x00); 
  }
  
  /**
   * Moves internal cursor left by the amount specified
   * @param optional integer $amount The amount to move left by, defaults to 1
   * @return integer The current column after the move
   */
  function left($amount = 1) {
    $this->col -= $amount;
    if($this->col col = 0;
    }
    return $this->col;
  }
  
  /**
   * Moves internal cursor right by the amount specified
   * @param optional integer $amount The amount to move right by, defaults to 1
   * @return integer The current column after the move
   */
  function right($amount = 1) {
    $this->col += $amount;
    return $this->col;
  }
  
  /**
   * Moves internal cursor up by amount
   * @param optional integer $amount The amount to move up by, defaults to 1
   * @return integer The current row after the move
   */  
  function up($amount = 1) {
    $this->row -= $amount;
    if($this->row row = 0;
    }
    return $this->row;
  }
  
  /**
   * Moves internal cursor down by amount
   * @param optional integer $amount The amount to move down by, defaults to 1
   * @return integer The current row after the move
   */
  function down($amount = 1) {
    $this->row += $amount;
    return $this->row;
  }
  
  /**
   * Moves internal cursor to the top of the page, row = 0
   * @return nothing
   */
  function top() {
    $this->row = 0;
  }
  
  /**
   * Moves internal cursor all the way left, col = 0
   * @return nothing
   */
  function home() {
    $this->col = 0;
  }
  
  /**
   * Writes a number to the Excel Spreadsheet
   * @see pack()
   * @param integer $value The value to write out
   * @return nothing
   */
  function number($value) { 
    $this->data .= pack("sssss", 0x203, 14, $this->row, $this->col, 0x0); 
    $this->data .= pack("d", $value); 
  }
  
  /**
   * Writes a string (or label) to the Excel Spreadsheet
   * @see pack()
   * @param string $value The value to write out
   * @return nothing
   */
  function label($value) { 
    $length = strlen($value);
    // ob_iconv_handler()
    // $value = iconv('UTF-8','gbk',$value);
    // file_put_contents('a.txt', 'test:    '.)
    $this->data .= pack("ssssss", 0x204, 8 + $length, $this->row, $this->col, 0x0, $length); 
    $this->data .= $value; 
  }
}</code>
Copy after login
Copy after login

下面是我的调用测试代码：

<code><?php // iconv_set_encoding('output_encoding','gbk');
    require_once("excel.php");

    $xls = new Excel('Report');

    $colors = array(
        array("red", "blue", "green", "yellow", "orange", "purple"),
        array('过以上','以','c','过','目录','f')
    );

    foreach ($colors as $key => $value) {
        $xls->home();
        foreach ($value as  $row) {
          // $xls->home();
          $xls->label($row);
          $xls->right();
           // $xls->label($row);
        }
        $xls->down();
    }
    
    ob_start();
    $xls->send();
    $data = ob_get_clean();
    file_put_contents(__DIR__ .'/report.xls', $data);
</code>
Copy after login
Copy after login

回复内容：

需要用php将数据导出倒excel表中，用的是php的pack函数将字符打包成二进制，然后写入Excel表格文件，英文字符导入正常，现在中文字符全部乱码了，不知道哪里设置的问题呢？

<code><?php /**
 * Simple excel writer class with no external dependencies, drop it in and have fun
 * @author Matt Nowack
 * @link https://gist.github.com/ihumanable/929039/edit
 * @license Unlicensed
 * @version 1.0
 */
class Excel {
  private $col;
  private $row;
  private $data;
  private $title;
  
  /**
   * Safely encode a string for use as a filename
   * @param string $title The title to use for the file
   * @return string The file safe title
   */
  static function filename($title) {
    $result = strtolower(trim($title));
    $result = str_replace("'", '', $result);
    $result = preg_replace('#[^a-z0-9_]+#', '-', $result);
    $result = preg_replace('#\-{2,}#', '-', $result);
    return preg_replace('#(^\-+|\-+$)#D', '', $result);
  }
  
  /**
   * Builds a new Excel Spreadsheet object
   * @return Excel The Spreadsheet
   */
  function __construct($title) {
    $this->title = $title;
    $this->col = 0;
    $this->row = 0;
    $this->data = '';
    $this->bofMarker();
  }
  
  /**
   * Transmits the proper headers to cause a download to occur and to identify the file properly
   * @return nothing
   */
  function headers() {
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header("Content-Type: application/vnd.ms-excel; charset=iso-8859-1");
    header("Content-Disposition: attachment;filename=" . Excel::filename($this->title) . ".xls ");
    header("Content-Transfer-Encoding: binary ");
  }
  
  function send() {
    $this->eofMarker();
    // $this->headers();
    echo $this->data;
  }
  
  /**
   * Writes the Excel Beginning of File marker
   * @see pack()
   * @return nothing
   */
  private function bofMarker() { 
    $this->data .= pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
  }
  
  /**
   * Writes the Excel End of File marker
   * @see pack()
   * @return nothing
   */
  private function eofMarker() { 
    $this->data .= pack("ss", 0x0A, 0x00); 
  }
  
  /**
   * Moves internal cursor left by the amount specified
   * @param optional integer $amount The amount to move left by, defaults to 1
   * @return integer The current column after the move
   */
  function left($amount = 1) {
    $this->col -= $amount;
    if($this->col col = 0;
    }
    return $this->col;
  }
  
  /**
   * Moves internal cursor right by the amount specified
   * @param optional integer $amount The amount to move right by, defaults to 1
   * @return integer The current column after the move
   */
  function right($amount = 1) {
    $this->col += $amount;
    return $this->col;
  }
  
  /**
   * Moves internal cursor up by amount
   * @param optional integer $amount The amount to move up by, defaults to 1
   * @return integer The current row after the move
   */  
  function up($amount = 1) {
    $this->row -= $amount;
    if($this->row row = 0;
    }
    return $this->row;
  }
  
  /**
   * Moves internal cursor down by amount
   * @param optional integer $amount The amount to move down by, defaults to 1
   * @return integer The current row after the move
   */
  function down($amount = 1) {
    $this->row += $amount;
    return $this->row;
  }
  
  /**
   * Moves internal cursor to the top of the page, row = 0
   * @return nothing
   */
  function top() {
    $this->row = 0;
  }
  
  /**
   * Moves internal cursor all the way left, col = 0
   * @return nothing
   */
  function home() {
    $this->col = 0;
  }
  
  /**
   * Writes a number to the Excel Spreadsheet
   * @see pack()
   * @param integer $value The value to write out
   * @return nothing
   */
  function number($value) { 
    $this->data .= pack("sssss", 0x203, 14, $this->row, $this->col, 0x0); 
    $this->data .= pack("d", $value); 
  }
  
  /**
   * Writes a string (or label) to the Excel Spreadsheet
   * @see pack()
   * @param string $value The value to write out
   * @return nothing
   */
  function label($value) { 
    $length = strlen($value);
    // ob_iconv_handler()
    // $value = iconv('UTF-8','gbk',$value);
    // file_put_contents('a.txt', 'test:    '.)
    $this->data .= pack("ssssss", 0x204, 8 + $length, $this->row, $this->col, 0x0, $length); 
    $this->data .= $value; 
  }
}</code>
Copy after login
Copy after login

下面是我的调用测试代码：

<code><?php // iconv_set_encoding('output_encoding','gbk');
    require_once("excel.php");

    $xls = new Excel('Report');

    $colors = array(
        array("red", "blue", "green", "yellow", "orange", "purple"),
        array('过以上','以','c','过','目录','f')
    );

    foreach ($colors as $key => $value) {
        $xls->home();
        foreach ($value as  $row) {
          // $xls->home();
          $xls->label($row);
          $xls->right();
           // $xls->label($row);
        }
        $xls->down();
    }
    
    ob_start();
    $xls->send();
    $data = ob_get_clean();
    file_put_contents(__DIR__ .'/report.xls', $data);
</code>
Copy after login
Copy after login

你中文是什么编码的啊？Excel默认是GB2312的。如果UTF-8就乱码了。

解决办法：要么转成GB2312再写进去。
要么在最后先输出BOM头

<code class="php">    ob_start();
    $xls->send();
    $data = "\xEF\xBB\xBF" . ob_get_clean();
    file_put_contents(__DIR__ .'/report.xls', $data);</code>
Copy after login
Related labels：
php
source：php.cn
Previous article：谁能解释下这段代码什么意思？ Next article：彩票的算法问题！
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
PHP arrays obtained from URL parameters do not behave as expected I have a URL parameter that contains the category ID and I want to treat it as an array li...
From 2024-04-06 22:09:02
0
1
1428
Where should I place CustomLog directive in apache I'm using php:7.2-apachedocker. I need to disable health check url login access log. Based...
From 2024-04-06 22:03:59
0
1
990
What is the format of the variables in the return value? I am a new learner of php. I found a piece of code: if($x<time()){return[false,'error']...
From 2024-04-06 21:55:20
0
1
778
Problems encountered when using opentbs to generate odt files: values ​​of the same key are displayed in the same row instead of separate columns. I'm using a library called OpenTbs to create odt using PHP, I'm using it because columns a...
From 2024-04-06 20:18:18
0
1
483
Group MySQL results by ID for looping over I have a table with flight data in mysql. I'm writing a php code that will group and displ...
From 2024-04-06 17:27:56
0
1
406
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template