Formation of Excel-documents using PHP

The possibility of creating Excel-documents in general has already been described on Habré , but I could not get complete information from these articles. I had to do my own research, the results of which I would like to share with you. This article will be useful for both beginners and professionals who are faced with the problem of dynamic formation of Excel-documents.

This is just the first part of a series of articles that I would like to share with the public. In future articles, some methods and nuances of unloading xls files will be discussed in more detail.

Introduction

I do not want to cover the topic of why you need to upload to Excel. It seems to me that this is quite obvious: in MS Excel format, it is quite convenient to provide the user with downloadable data. These can be price lists, catalogs, or any such official, statistical or other kind of information.

Just want to make a reservation that the article deals with working with documents through COM objects. This method only works on Windows platforms. In other words, if you prefer * nix hosting, this method does not suit you.

Syntax

Creating a COM object is as follows:

$my_com_object = new COM($object_class);

where

$ my_com_object - new COM object;

$ object_class - id-class of the required object.

')

To create Excel documents, the $ object_class variable must be set to “Excel.Application” or “Excel.sheet”.

$xls = new COM("Excel.Application");



After creating a new COM object, you can refer to its properties and methods:

<?php

$xls = new COM("Excel.Application"); //

$xls->Application->Visible = 1; //

$xls->Workbooks->Add(); // ( )



$rangeValue = $xls->Range("A1");

$rangeValue->Value = " : , , ";

$rangeValue = $xls->Range("A2");

$rangeValue->Value = " : 14";

$rangeValue = $xls->Range("A3");

$rangeValue->Value = " : Arial";



$range=$xls->Range("A1:J10"); //

$range->Select(); //

$fontRange=$xls->Selection(); //



//

$fontRange->Font->Bold = true; //

$fontRange->Font->Italic = true; //

$fontRange->Font->Underline = true; //

$fontRange->Font->Name = "Arial"; //

$fontRange->Font->Size = 14; //

?>



Opening, recording, closing a document

General features

In Excel using PHP you can:

• create a new document
• open previously created document
• save open document
• close document

Creating a new document

Creating a new document takes place in three steps.

1. create a “link” between PHP and Excel (a descriptor is created, as when working with files)
2. indicate whether the program will be visually open or not
3. we indicate to the program through the descriptor that we need to open a new document

To create a descriptor, you need to use Excel access via a COM object:

$xls = new COM("Excel.Application");

Now through the $ xls descriptor we can access all Excel properties and methods.

Whether Excel will be displayed or not is specified in the Visible () property of the Application () object.

If we assign this property a value of 1, then the program will be displayed, if 0, then no:

$xls->Application->Visible = 1;

And finally, you can add a new document using the Workbooks object's Add () method:

$xls->Workbooks->Add();

That is, in order to simply run using PHP Excel you need to run the following code:

<?php

$xls = new COM("Excel.Application"); // COM-

$xls->Application->Visible = 1; //

$xls->Workbooks->Add(); //

?>

The first two lines of this example, of course, are always used when working with Excel through PHP.

Opening a previously created document

Opening a document can be done using the Open () method of the Workbooks object ().

In the parameter passed to the Open () method, you must specify the name of the file being opened:

<?php

$xls = new COM("Excel.Application"); // COM-

$xls->Application->Visible = 1; //

$xls->Workbooks->Open("C:\my_doc.xls"); //

?>

Attention! If you specify not a full, but a relative path, then the search for the file being opened will not be performed on the server, but on the user's computer . By default, this is the My Documents folder.

Saving an open document

Saving an open document is done using the SaveAs () method of the Workbooks () object:

<?php

$xls = new COM("Excel.Application"); // COM-

$xls->Application->Visible = 1; //

$xls->Workbooks->Add();

$range=$xls->Range("A1"); // A1

$range->Value = " "; //



//

$xls->Workbooks[1]->SaveAs("my_doc.xls");



$xls->Quit(); //

$xls->Release(); //

$xls = Null;

$range = Null;

?>

I want to separately note that the release of objects is very good and correct. Yes, those who think otherwise will burn in the sacred cleansing flame of the fires of the Inquisition.

Closing the document

The document is closed using the Quit () method.

<?php

$xls = new COM("Excel.Application"); // COM-

$xls->Application->Visible = 1; //

$xls->Workbooks->Add();

$range=$xls->Range("A1"); // A1

$range->Value = "- "; //



//

$xls->Workbooks[1]->SaveAs("my_doc.xls");



$xls->Quit(); //

$xls->Release(); //

$xls = Null;

$range = Null;

?>

Conclusion

If the article turned out to be useful and interesting for you, I will be happy to prepare a continuation, where methods of working with sheets of documents, cells and borders will be considered in more detail.

Of course, if it is interesting and necessary for someone to work.