如何使用 php 生成.xlsx

2022-08-30 16:39:45

我需要生成一个扩展名为..xlsx

这是我的简单代码:

 $file = "test.xlsx";
 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
 header('Content-Disposition: attachment; filename='.$file);
 $content = "Col1\tCol2\tCol3\t\n";
 $content .= "test1\ttest1\ttest3\t\n";
 $content .= "testtest1\ttesttest2\ttesttest3\t\n";
 echo $content;

但是当我打开生成的文件时,我收到此错误:

Excel cannot open the file 'test.xlsx' because the file format or file extension is not valid.

有什么想法吗?


答案 1

SimpleXLSXGen

$books = [
    ['ISBN', 'title', 'author', 'publisher', 'ctry' ],
    [618260307, 'The Hobbit', 'J. R. R. Tolkien', 'Houghton Mifflin', 'USA'],
    [908606664, 'Slinky Malinki', 'Lynley Dodd', 'Mallinson Rendel', 'NZ']
];
$xlsx = Shuchkin\SimpleXLSXGen::fromArray( $books );
$xlsx->saveAs('books.xlsx');
//  $xlsx->downloadAs('books.xlsx');

答案 2

在尝试了一些选项后,我发现PHP_XLSX_Writer适合我的需求。

PHP_XLSX_Writer- ...设计为轻量级,内存使用量最小,以XLSX格式生成与Excel兼容的工作簿,并支持基本功能:

 - supports PHP 5.2.1+
 - takes 'UTF-8' characters (or encoded input)
 - multiple worksheets
 - supports currency/date/numeric cell formatting, simple formulas
 - supports basic cell styling
 - supports writing huge 100K+ row spreadsheets

(改编自图书馆的GitHub存储库


下面是一个工作示例,演示了 3 个工作表(选项卡)上的一些功能:

  1. 首先创建一个名为包含此处代码的文件xlsxwriter.class.php

  2. 创建另一个文件(在同一文件夹中),其中包含:PHP

     require('xlsxwriter.class.php');
    
     $fname='my_1st_php_excel_workbook.xlsx';
     $header1 = [ 'create_date' => 'date',
                  'quantity' => 'string',
                  'product_id' => 'string',
                  'amount' => 'money',
                  'description' => 'string' ];
     $data1 = [ ['2021-04-20', 1, 27, '44.00', 'twig'],
                ['2021-04-21', 1, '=C1', '-44.00', 'refund'] ];
     $data2 = [ ['2','7','ᑌᑎIᑕᗝᗪᗴ ☋†Ϝ-➑'],
                ['4','8','						

推荐