PHP中创建和编辑Excel表格的方法

要使用纯php创建或编辑excel电子表格，我们将使用phpexcel库，它可以读写许多电子表格格式，包括xls，xlsx，ods和csv。在我们继续之前，仔细检查您的服务器上是否有php 5.2或更高版本以及安装了以下php扩展：php_zip，php_xml和php_gd2。

创建电子表格

创建电子表格是php应用程序中最常见的用例之一，用于将数据导出到excel电子表格。查看以下代码，了解如何使用phpexcel创建示例excel电子表格：

// include phpexcel library and create its object
require('phpexcel.php');

$phpexcel = new phpexcel;

// set default font to arial
$phpexcel->getdefaultstyle()->getfont()->setname('arial');

// set default font size to 12
$phpexcel->getdefaultstyle()->getfont()->setsize(12);

// set spreadsheet properties – title, creator and description
$phpexcel ->getproperties()->settitle("product list");
$phpexcel ->getproperties()->setcreator("voja janjic");
$phpexcel ->getproperties()->setdescription("php excel spreadsheet testing.");

// create the phpexcel spreadsheet writer object
// we will create xlsx file (excel 2007 and above)
$writer = phpexcel_iofactory::createwriter($phpexcel, "excel2007");

// when creating the writer object, the first sheet is also created
// we will get the already created sheet
$sheet = $phpexcel ->getactivesheet();

// set sheet title
$sheet->settitle('my product list');

// create spreadsheet header
$sheet ->getcell('a1')->setvalue('product');
$sheet ->getcell('b1')->setvalue('quanity');
$sheet ->getcell('c1')->setvalue('price');

// make the header text bold and larger
$sheet->getstyle('a1:d1')->getfont()->setbold(true)->setsize(14);

// insert product data


// autosize the columns
$sheet->getcolumndimension('a')->setautosize(true);
$sheet->getcolumndimension('b')->setautosize(true);
$sheet->getcolumndimension('c')->setautosize(true);

// save the spreadsheet
$writer->save('products.xlsx');

如果要下载电子表格而不是将其保存到服务器，请执行以下操作：

header('content-type: application/vnd.ms-excel');
header('content-disposition: attachment;filename="file.xlsx"');
header('cache-control: max-age=0');
$writer->save('php://output');

编辑现有电子表格

在php中编辑电子表格与创建电子表格类似：

// include phpexcel library and create its object
require('phpexcel.php');

// load an existing spreadsheet
$phpexcel = phpexcel_iofactory::load('products.xlsx');

// get the first sheet
$sheet = $phpexcel ->getactivesheet();

// remove 2 rows starting from the row 2
$sheet ->removerow(2,2);

// insert one new row before row 2
$sheet->insertnewrowbefore(2, 1);

// create the phpexcel spreadsheet writer object
// we will create xlsx file (excel 2007 and above)
$writer = phpexcel_iofactory::createwriter($phpexcel, "excel2007");

// save the spreadsheet
$writer->save('products.xlsx');

准备电子表格进行打印

要准备电子表格进行打印，我们将设置纸张方向，尺寸和边距：

$sheet->getpagesetup()->setorientation(phpexcel_worksheet_pagesetup::orientation_landscape);
$sheet -> getpagesetup()->setpapersize(phpexcel_worksheet_pagesetup::papersize_a4); 
$sheet->getpagemargins()->settop(1);
$sheet ->getpagemargins()->setright(0.75);
$sheet ->getpagemargins()->setleft(0.75);
$sheet ->getpagemargins()->setbottom(1);

将phpexcel与laravel一起使用

phpexcel库也可以在laravel框架中使用。查看以下php包（此处）并通过composer安装它。完成安装步骤后，您可以使用以下代码将数据从数据库导出到excel电子表格中：

excel::create('products', function($excel) {

        // set the title
        $excel->settitle('product list');
  
        // set the creator
        $excel->setcreator('voja janjic');
  
        // set description
        $excel->setdescription('php excel spreadsheet testing');
  
        $excel->sheet('products', function($sheet) {
   
                // get data from the database
                $products = product::all(); 
  
                // generate header row
                $sheet->row(1, array(
                        'id',
                        'product',
                        'price',
                        'quantity',     
                ));
  
                // generate data rows 
                $i = 2; 
                foreach($products as $product) {    
                        $sheet->row($i, array(
                                   $product->product_id,
                                   $product->product_name,
                                   $product->price,
                                   $variety->quantity,    
                        ));
   
                        $i++;
                }

        });

})->export('xlsx');