欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-15 11:33:00
要使用纯php创建或编辑excel电子表格,我们将使用phpexcel库,它可以读写许多电子表格格式,包括xls,xlsx,ods和csv。在我们继续之前,仔细检查您的服务...

要使用纯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');