5 Ways to Create Excel Sheets with PHP

5 Ways to Create Excel Sheets with PHP

PHP, known for its simplicity and power in web development, can also be a robust tool for generating Excel spreadsheets. Whether you're dealing with large datasets, need to export data for analysis, or simply want to provide a user-friendly way for others to download your data, PHP offers several libraries and techniques to accomplish this task efficiently. Here are five ways to create Excel sheets using PHP:

1. Using PHPExcel Library

PHPExcel is one of the most popular libraries for handling Excel files with PHP. Here’s how you can use it:

• Installation: Use Composer to install PHPExcel or download it directly from its GitHub repository.
• Usage:

  require 'path/to/PHPExcel.php';
  
  $objPHPExcel = new PHPExcel();
  $objPHPExcel->setActiveSheetIndex(0);
  
  $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Hello Excel');
  
  $writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  $writer->save('helloworld.xlsx');
  

🗒 Note: PHPExcel has now been replaced by the Phpspreadsheet library which offers similar functionality with ongoing support.

2. With PhpSpreadsheet

PhpSpreadsheet is the successor to PHPExcel, providing better performance and more features. Here’s a simple example:

• Installation: Install via Composer or download from the repository.
• Example:

  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
  
  $spreadsheet = new Spreadsheet();
  $sheet = $spreadsheet->getActiveSheet();
  $sheet->setCellValue('A1', 'Hello World !');
  
  $writer = new Xlsx($spreadsheet);
  $writer->save('hello world.xlsx');
  

3. Utilizing Pear’s Spreadsheet_Excel_Writer

This is an older but still functional method for creating simple Excel files:

• Usage:

  require_once 'Spreadsheet/Excel/Writer.php';
  
  $workbook = new Spreadsheet_Excel_Writer('test.xls');
  $worksheet =& $workbook->addWorksheet('My first worksheet');
  
  $worksheet->write(0, 0, 'Hello Excel');
  
  $workbook->close();
  

4. Direct XML Formatting

Creating an Excel file from scratch involves creating an XML file formatted as an Excel spreadsheet:

• Generate XML: You manually craft the XML content that represents the structure of an Excel file.

  $xml = '
      
          
              
                      Hello Excel
                  
  
          
      ';
      $file = fopen('example.xml', 'w');
      fwrite($file, $xml);
      fclose($file);
  

• Note: This method requires knowledge of the Excel XML schema.

🗒 Note: This approach can be complex and is error-prone if the XML structure is not followed precisely.

5. CSV Export

Although CSV files are not exactly Excel sheets, they are easily opened by Excel and offer simplicity:

• Write to CSV:

  $file = fopen('data.csv', 'w');
  fputcsv($file, array('Name', 'Age'));
  fputcsv($file, array('John Doe', 30));
  fclose($file);
  

• Excel Compatibility: When opened with Excel, CSV files are directly converted to Excel format, retaining formatting and data.

In conclusion, the method you choose to generate an Excel file from PHP depends on your specific requirements like performance, feature set, and ease of setup. PHPExcel and PhpSpreadsheet are ideal for complex operations, while simpler CSV exports serve well for basic data listing. Each method has its unique advantages, making PHP a versatile language for data manipulation and export.

What is the difference between PHPExcel and PhpSpreadsheet?

+

PhpSpreadsheet is the newer and improved version of PHPExcel, with better performance, ongoing support, and additional features like support for more file formats.

Can PHP create Excel files directly or must I use a library?

+

While PHP can create XML formatted as an Excel file, for better functionality and ease, using libraries like PHPExcel or PhpSpreadsheet is highly recommended.

How do I set cell formats using PhpSpreadsheet?

+

You can set cell formats using the style() method in PhpSpreadsheet. For example, to set a cell’s background color, use: $spreadsheet->getActiveSheet()->getStyle(‘A1’)->getFill()->setFillType(PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB(‘FFFF0000’);