Create and Edit Excel Files in PHP

Excel spreadsheets are a vital tool for businesses and individuals alike when it comes to data management, analysis, and reporting. Creating and manipulating Excel files programmatically can be a common requirement in various web applications. In this blog post, we will explore how to create Excel files in PHP leveraging Aspose.Cells for PHP - a powerful and feature-rich library for Excel automation.

PHP Library to Create Excel Files
Create an Excel Files in PHP
Modify an Excel File in PHP
Create Excel File with Charts or Graphs
Create a Table in an Excel File
Create Excel Files Online

PHP Excel Library

Aspose.Cells for PHP is a robust PHP library that enables developers to create, manipulate, and convert Excel files programmatically. It offers a wide range of features, including the ability to generate Excel worksheets, format cells, insert charts, and perform advanced data analysis. With Aspose.Cells for PHP, you can automate Excel-related tasks within your PHP application.

You can download the library package from here.

Usage

The following are the prerequisites that you need to fulfill in order to use Aspose.Cells for PHP.

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 library’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 library’s root folder using the below command:
- php example.php

Create an Excel File in PHP

The following are some easy steps to create an Excel file in a PHP application.

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 in PHP.

	require_once("http://localhost:8080/JavaBridge/java/Java.inc");
	require_once("aspose.cells.php");

	use aspose\cells;

	// Create an object of workbook class
	$workbook = new cells\Workbook();

	// Access the worksheets
	$sheets = $workbook->getWorksheets();

	// Access the cells of desired worksheet
	$cells = $sheets->get(0)->getCells();

	// Insert value to the cell
	$cells->get("A1")->putValue("Hello world!");

	// Save the Excel file
	$workbook->save("output.xlsx", cells\SaveFormat::XLSX);

view raw create-excel-file.php hosted with ❤ by GitHub

Edit an Excel File in 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 in PHP.

	// Create an object of workbook class to load Excel file
	$workbook = new cells\Workbook("workbook.xlsx");

	// Access the worksheets
	$sheets = $workbook->getWorksheets();

	// Access the cells of desired worksheet
	$cells = $sheets->get(0)->getCells();

	// Insert value to the cell
	$cells->get("A1")->putValue("Hello world!");

	// Save the Excel file
	$workbook->save("updated.xlsx");

view raw modify-excel-file.php hosted with ❤ by GitHub

Add a Chart in an Excel File

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.

	// Load the Excel file
	$workbook = new Workbook("Book2.xlsx");

	// Obtain the reference of the first worksheet
	$worksheets = $workbook->getWorksheets();
	$sheet = $worksheets->get(0);

	// Add some sample value to cells
	$cells = $sheet->getCells();
	$cell = $cells->get("A1");
	$cell->setValue(50);
	$cell = $cells->get("A2");
	$cell->setValue(100);
	$cell = $cells->get("A3");
	$cell->setValue(150);
	$cell = $cells->get("B1");
	$cell->setValue(4);
	$cell = $cells->get("B2");
	$cell->setValue(20);
	$cell = $cells->get("B3");
	$cell->setValue(50);

	// Get charts in worksheet
	$charts = $sheet->getCharts();

	// Add a chart to the worksheet
	$chartIndex = $charts->add(ChartType::PYRAMID, 5, 0, 15, 5);
	$chart = $charts->get($chartIndex);

	// Add NSeries (chart data source) to the chart ranging from "A1"
	// cell to "B3"
	$serieses = $chart->getNSeries();
	$serieses->add("A1:B3", true);

	// Write the Excel file
	$workbook.save("Excel_with_Chart.xlsx");

view raw create-chart-in-excel.php hosted with ❤ by GitHub

Add a Pivot Table in an Excel File in 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.

	// Load document to be converted
	$workbook = new Workbook("Book2.xlsx");

	// Obtain the reference of the first worksheet
	$worksheets = $workbook->getWorksheets();
	$sheet = $worksheets->get(0);

	// Add some sample value to cells
	$cells = $sheet->getCells();
	$cell->setValue("Sport");
	$cell = $cells->get("B1");
	$cell->setValue("Quarter");
	$cell = $cells->get("C1");
	$cell->setValue("Sales");

	$cell = $cells->get("A2");
	$cell->setValue("Golf");
	$cell = $cells->get("A3");
	$cell->setValue("Golf");
	$cell = $cells->get("A4");
	$cell->setValue("Tennis");
	$cell = $cells->get("A5");
	$cell->setValue("Tennis");
	$cell = $cells->get("A6");
	$cell->setValue("Tennis");
	$cell = $cells->get("A7");
	$cell->setValue("Tennis");
	$cell = $cells->get("A8");
	$cell->setValue("Golf");

	$cell = $cells->get("B2");
	$cell->setValue("Qtr3");
	$cell = $cells->get("B3");
	$cell->setValue("Qtr4");
	$cell = $cells->get("B4");
	$cell->setValue("Qtr3");
	$cell = $cells->get("B5");
	$cell->setValue("Qtr4");
	$cell = $cells->get("B6");
	$cell->setValue("Qtr3");
	$cell = $cells->get("B7");
	$cell->setValue("Qtr4");
	$cell = $cells->get("B8");
	$cell->setValue("Qtr3");

	$cell = $cells->get("C2");
	$cell->setValue(1500);
	$cell = $cells->get("C3");
	$cell->setValue(2000);
	$cell = $cells->get("C4");
	$cell->setValue(600);
	$cell = $cells->get("C5");
	$cell->setValue(1500);
	$cell = $cells->get("C6");
	$cell->setValue(4070);
	$cell = $cells->get("C7");
	$cell->setValue(5000);
	$cell = $cells->get("C8");
	$cell->setValue(6430);

	// Get pivot tables
	$pivotTables = $sheet->getPivotTables();

	// Add a PivotTable to the worksheet
	$index = $pivotTables->add("=A1:C8", "E3", "PivotTable2");

	// Access the instance of the newly added PivotTable
	$pivotTable = $pivotTables->get($index);

	// Unshow grand totals for rows
	$pivotTable->setRowGrand(false);

	// Drag the first field to the row area
	$pivotTable->addFieldToArea(PivotFieldType::ROW, 0);

	// Drag the second field to the column area
	$pivotTable->addFieldToArea(PivotFieldType::COLUMN, 1);

	// Drag the third field to the data area
	$pivotTable->addFieldToArea(PivotFieldType::DATA, 2);

	// Write the Excel file
	$workbook.save("Excel_with_Table.xlsx");

view raw create-excel-pivot-table.php hosted with ❤ by GitHub

Online Excel Editor

We also provide an online Excel editor for you to create and edit Excel files. You can use this powerful spreadsheet editor without creating an account.

Get Free PHP Excel Library

You can create Excel files in PHP for free without evaluation limitations using a free temporary license.

Conclusion

Aspose.Cells for PHP is a powerful library that simplifies Excel file generation and manipulation in PHP applications. In this blog post, we’ve covered the basics of creating Excel files and adding charts and table using Aspose.Cells for PHP. Furthermore, you have seen how to write data to an existing Excel file.

This library offers numerous advanced features, such as performing numerical operations, data analysis, and Excel file conversion, making it a versatile choice for any PHP project that involves Excel automation. To explore further, refer to the official Aspose documentation. In case you would have any questions, feel free to let us know via our forum.

PHP Excel Library#

Usage#

Create an Excel File in PHP#

Edit an Excel File in PHP#

Add a Chart in an Excel File#

Add a Pivot Table in an Excel File in PHP#

Online Excel Editor#

Get Free PHP Excel Library#

Conclusion#

See Also#