Spreadsheets have become an essential part of keeping, organizing, and analyzing the data. Since automated solutions are more in business these days, the trend of creating and manipulating Excel documents (XLS/XLSX) has emerged and growing at a huge pace. In accordance with the above-mentioned scenario, this article covers how to create Excel XLSX or XLS files in PHP based web applications.
- PHP Excel Automation API
- Create Excel XLS or XLSX Files using PHP
- Write Data to Existing Excel File using PHP
- Create Charts or Graphs in an Excel File using PHP
- Create a Table in an Excel File using PHP
PHP API to Create Excel Files
In order to create and manipulate Excel files in PHP based web applications, we will use Aspose.Cells for PHP via Java. It is a powerful API that provides a wide range of features for Excel automation. You can download the API package from here.
Usage
The following are the prerequisites that you need to fulfill in order to use Aspose.Cells for PHP via Java.
Once you have completed the prerequisites, follow the below steps to execute the example.php file for testing.
- Place Java.inc file in the root folder of the API’s package that you have downloaded.
- Run JavaBridge.jar using the below commands in the command prompt:
- > cd aspose.cells
- > %JAVA_HOME%\bin\java -Djava.ext.dirs=lib -jar JavaBridge.jar SERVLET_LOCAL:8080
- Run example.php from the API’s root folder using the below command:
- > php example.php
Create Excel XLS or XLSX Files using PHP
The following are the steps to create an Excel XLSX/XLS file using PHP.
- Create an object of Workbook class.
- Access the WorksheetCollection using $workbook->getWorksheets() method.
- Access Cells collection of the desired worksheet using $worksheets->get(index)->getCells() method.
- Insert value into the desired cell using $cells->get(“A1”)->putValue(“Hello world!”) method.
- Save the Excel workbook using $workbook->save(“output.xlsx”, cells\SaveFormat::XLSX) method.
The following code sample shows how to create an Excel XLSX file using PHP.
Write Data to Existing Excel File using PHP
In the previous section, we created an Excel file from scratch. Now, let’s edit an existing Excel file and insert data into it. The following are the steps to write data to an XLSX file using Aspose.Cells for PHP via Java.
- Create an object of Workbook class and initialize it with the path to the Excel file.
- Access the WorksheetCollection of the Excel file using $workbook->getWorksheets() method.
- Access Cells collection of the desired worksheet using $worksheets->get(index)->getCells() method.
- Insert value into the desired cell using $cells->get(“A1”)->putValue(“Hello world!”) method.
- Save the Excel workbook using $workbook->save(“output.xlsx”, cells\SaveFormat::XLSX) method.
The following code sample shows how to modify an Excel file and write data into it using PHP.
Create Charts in an Excel File using PHP
The following are the steps to create charts in an Excel file using PHP.
- Create a new Excel file or load an existing one using the Workbook class.
- Add data to the worksheet if a new workbook is created.
- Get the chart collection of the worksheet using the $worksheet->getCharts() method.
- Add a new chart using $worksheet->getCharts()->add() method.
- Get the newly created Chart from the collection.
- Specify the cells’ range to set NSeries for the chart.
- Save the workbook as an Excel .xlsx file using $workbook->save(“output.xlsx”, cells\SaveFormat::XLSX) method.
The following code sample shows how to create charts in Excel files in PHP.
Create a Pivot Table in an Excel File using PHP
Pivot tables in Excel worksheets are used for adding filters to the data, computing totals, summarizing data, etc. Pivot tables can be created using the range of the cells in the worksheet. The following are the steps to create a pivot table in an Excel worksheet using PHP.
- Create a new Excel file or load an existing one using the Workbook class.
- Insert data into the worksheet.
- Access the pivot table collection using $worksheet->getPivotTables() method.
- Add a new pivot table in the worksheet using $worksheet->getPivotTables()->add() method.
- Provide data to the pivot table.
- Save the workbook using $workbook->save(“output.xlsx”, cells\SaveFormat::XLSX) method.
The following code sample shows how to create a pivot table in Excel using PHP.
Get a Free API License
You can use the API for free without evaluation limitations using a temporary license.
Conclusion
In this article, you have learned how to create Excel files from scratch using PHP. Furthermore, you have seen how to write data to an existing Excel file and generate charts or tables. You can explore more about the PHP Excel API using the documentation. In case you would have any questions, feel free to let us know via our forum.
