Generating Excel files from PHP is a common requirement for web applications. Fortunately, there are libraries like PHPExcel that simplify this task. PHPExcel is a popular library for creating and managing Excel files in PHP. Here’s a step-by-step guide on how to create a basic Excel file using PHPExcel:

Include PHPExcel Library

First, you need to include the PHPExcel library in your PHP file. Download it from the PHPExcel website, and include it in your project like this:

require_once ‘PHPExcel.php’;

Create PHPExcel Instance

Now, create an instance of the PHPExcel class:

$excel = new PHPExcel();

Get the Active Worksheet

To work with worksheets in your Excel file, you need to get the active worksheet:

$sheet = $excel->getActiveSheet();

Set Cell Values

Set values for the cells in your worksheet:

$sheet->setCellValue(‘A1’, ‘Name’);
$sheet->setCellValue(‘B1’, ‘Age’);
$sheet->setCellValue(‘A2’, ‘John Doe’);
$sheet->setCellValue(‘B2′, ’30’);

In this example, we set values for cells A1 and B1 as “Name” and “Age,” respectively. Cells A2 and B2 are populated with “John Doe” and “30”.

Save as Excel File

To save the generated Excel file, create a writer object and configure the headers to prompt the user for download:

$writer = PHPExcel_IOFactory::createWriter($excel, ‘Excel2017’);
header(‘Content-Type: application/vnd.ms-excel’);
header(‘Content-Disposition: attachment;filename=”sample.xlsx”‘);
header(‘Cache-Control: max-age=0’);
$writer->save(‘php://output’);

Here’s what’s happening:

We use the createWriter method to create a writer object for our PHPExcel instance.

• Headers are set to specify the content type (application/vnd.ms-excel), indicate attachment (Content-Disposition), and provide the filename (sample.xlsx).
• We disable caching to ensure the file is not stored by the browser.
• Finally, we use the writer object to save the file to the output stream (php://output).

That’s the basic process of creating an Excel file in PHP using PHPExcel. You can further customize your Excel file by setting font styles, background colors, borders, adding formulas, charts, and images to the worksheet.

Advanced Excel File Manipulation with PHPExcel

While we’ve covered the basics of creating Excel files with PHPExcel, there are many advanced features and tasks you can perform with this powerful library. Let’s explore some advanced Excel file manipulation techniques:

Formulas

You can add formulas to Excel cells, which can reference other cells in the worksheet. For example:

$sheet->setCellValue(‘C1’, ‘=SUM(A2:B2)’);

Multiple Worksheets

You can create and manage multiple worksheets within a single Excel file:

$newSheet = new PHPExcel_Worksheet($excel, ‘Sheet 2’);
$excel->addSheet($newSheet);

Reading Excel Files

PHPExcel can not only create but also read Excel files. You can extract data, formulas, and styles from existing Excel files.

Styling and Formatting

You can apply various styles and formatting options to your Excel file, such as changing font styles, cell colors, borders, and alignment. Here’s an example of how to set cell formatting:

$style = $sheet->getStyle(‘A1:B2’);
$style->getFont()->setBold(true);
$style->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$style->getFill()->getStartColor()->setRGB(‘FFFF00’);
$style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

Related posts:

How to write vertically in Excel? How to create wattage calculator in Excel Kg to newtons and newtons to kg converter