How to write in Excel file using php?

Spreadsheets are simple files that help you manage and organize data. They are used everywhere and have become quite popular. You can find them by using Microsoft Excel, Open Office, or even an online alternative such as Google Docs.

Generating Spreadsheets

Since computers and automation have become second nature, spreadsheet generation isn’t out of the ordinary. With MySQL databases, it becomes even more imperative, as spreadsheets are perfect to represent the data located in these structures.

Upon searching for how to create excel spreadsheets using PHP, most of the results came back with libraries that do the job. This seemed to be too much of a hassle. That’s why I’ve created 3 easy steps to create excel spreadsheets without any libraries.

Step 1: The Content-Type

The first thing you have to do is add an application/vnd.ms-excel content-type to your PHP file:

header("Content-Type: application/vnd.ms-excel");

Easy enough? Let’s move on to the actual data.

Step 2: Adding the Data

Data is just as simple. Separate cells/columns with tabs (“\t” in PHP) and move to the next row with a newline (“\n” in PHP).

Here are a few PHP echo statements that will do the trick:

echo 'First Name' . "\t" . 'Last Name' . "\t" . 'Phone' . "\n";
echo 'John' . "\t" . 'Doe' . "\t" . '555-5555' . "\n";

I’ve separated the tabs (\t) and the new lines (\n) so you can easily see the structure. The above would create a spreadsheet that looks like this:

First Name     Last Name     Phone
John           Doe           555-5555

Step 3: Downloading the Spreadsheet

Now that you’ve set the content-type and created the data, just open up the PHP file in a browser. You’ll be asked to download the spreadsheet. If you would like to give a name to the spreadsheet, just add the following:

header("Content-disposition: attachment; filename=spreadsheet.xls");

All you need to do is change spreadsheet.xls to the name of your spreadsheet. Note that the above header also ensures that Internet Explorer will ask you to download the file rather than trying to display it.

Conclusion

That’s really all there is to create a spreadsheet. For those of you who are using Microsoft Excel 2007, you might see the following message when opening the spreadsheet:

The file you are trying to open, ‘filename.xls’, is in a different format than specified by the file extension… [message condensed]

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 and modify Excel files in PHP.

PHP Library to Create Excel Files - Free Download

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 and high-speed library that provides a wide range of features for Excel automation. 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 via Java.

• Java SE Development Kit 1.7 or higher
• PHP 7.0 or higher
• Java Bridge
• Java Inc

Once you have completed the prerequisites, follow the below steps to execute the example.php file for testing.

1. Place Java.inc file in the root folder of the library’s package that you have downloaded.
2. 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

3. Run example.php from the library’s root folder using the below command:

   • php example.php

Easy Steps to Create Excel File in PHP

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

• Create an object of Workbook class.
• Access the WorksheetCollection using method.
• Access Cells collection of the desired worksheet using method.
• Insert value into the desired cell using method.
• Save the Excel workbook using method.

The following code sample shows how to create an Excel XLSX file in PHP.

Modify 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 method.
• Access Cells collection of the desired worksheet using method.
• Insert value into the desired cell using method.
• Save the Excel workbook using method.

The following code sample shows how to modify an Excel file in PHP.

PHP: Create Excel File with Charts

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 method.
• Add a new chart using 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 method.

The following code sample shows how to create charts in Excel files in PHP.

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 method.
• Add a new pivot table in the worksheet using method.
• Provide data to the pivot table.
• Save the workbook using method.

The following code sample shows how to create a pivot table in Excel using PHP.

Aspose’ PHP Library to Create Excel Files - Get a Free License

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

Conclusion

In this article, you have learned how to create Excel XLS or XLSX files from scratch in PHP. Furthermore, you have seen how to write data to an existing Excel file and generate Excel files with charts or tables in PHP. You can explore more about the PHP Excel library using the documentation. In case you would have any questions, feel free to let us know via our forum.

How to write data in Excel file using php?

We can set the value to the cell by using the setCellValueByColumnAndRow(a,b,c) function. Write the created spreadsheet using the save() function. We can set the cell value using setCellValue() function() by passing cell index name like A1, A2, D3 etc. and value.

How to read and write Excel file in php?

Download the Code and Examples​.

Best PHP Libraries to Parse and Write Excel Files..

Install Box/Spout library..

Excel (XLSX) File Example..

Read an Excel File (XLSX).

Write an Excel File (XLSX).

Read a Large Excel File Efficiently while Using Low Memory..

Download the Code and Examples..

How to create dynamic Excel file in php?

Generate Dynamic Excel File Using PHP Excel Library In....

Download the codeigniter framework for codeigniter website and set up on local server. And config it..

Download the php excel library from here/(Download). ... .

Create a mysql table(countries). ... .

Create a controller (home. ... .

Create a view file (home..

How do I add a new line in a cell in Excel using php?

Write a newline character "\n" in a cell (ALT+"Enter") In Microsoft Office Excel you get a line break in a cell by hitting ALT+"Enter". When you do that, it automatically turns on "wrap text" for the cell. Read more about formatting cells using getStyle() elsewhere.