That's what the conversation will go in this article:
* Introduction to PEAR :: Spreadsheet_Excel_Writer
* Continue to work: Introduction to the API
* Dobavlenie cell formatting
* Add a function Excel: C1 + D1 = 2!
It is understood that you have a basic knowledge of Excel, but nothing too serious. You do not even have to have a copy of all the examples work just as well with OpenOffice Calc.
Introduction to PEAR :: Spreadsheet_Excel_Writer
Excel - th are those who work with finances and money. In other words, the audit department, which has not paid the amount of your time, use it. Make life easier for the auditors and they will tell you the same.
Would not it be better if you could give your customers access to downloadable data sheets in the form of Excel? It's good news is that you can do it using PEAR :: Spreadsheet_Excel_Writer.
"Impossible!" You say. "Excel file format uses Microsoft. It is impossible to do".
Yes, yes it is possible. Spreadsheet_Excel_Writer generates the "real thing", with the functions of Excel, the formatting and everything else. No, here we are not talking about faylax separated by commas or ispolzuyuschix COM extension (or lyubie other extensions).
It is written using a simple PHP, and will run on Unix server as well as on Windows servers In short, the PEAR :: Spreadsheet_Excel_Writer, together with additional features of PEAR :: OLE "understands" the format of Microsoft Excel.
Let's take off their hats before Xavier Noguer, who did an amazing job to implement it in PHP, with Mika Tuupola for Spreadsheet_Excel_Writer.
Now, without further noise and armed complete knowledge management package PEAR, which you have to be installed, start the download library. Open your command prompt and type in the following:
That's it. We are ready!
Important Note: The examples in this article, I used PEAR :: OLE version 0.5, and PEAR :: Spreadsheet_Excel_Writer version 0.7. I warn you that something - that may change in future versions.
File name: example_1.php
Open the script in your browser (assuming that he is "familiar" with Excel or OpenOffice Calc), and it will display an Excel spreadsheet with the numbers from 0 to 10 in binary form.
Saving files
In this case, the list is created dynamically - on the server nothing remains.Esli you xotite instead, create a file, you can drop the part to create a worksheet that has not changed, for the designer to simply sending the file name and path, and this should be supervised to avoid sending HTTP headings:
File name: example_2.php
If you are using a system of family Unix, do not forget to change the permissions to the folder in which you best stored lists of data that PHP was able to add the data in them.
API Overview
It is well, we understand the basic concepts. To get the most out of PEAR :: Spreadsheet_Excel_Writer, you need to know a little bit more about the API.
Documentation API, which is available at PEAR is now outdated (it is much more, with tex has been created as a version of the documentation).
Thanks to the authors, who added a lot of documentation directly in the code, you can create your own documentation API, if you download the phpDocumentor, and enter it in the folder that contains all the codes isxodnye Spreadsheet_Excel_Writer.
The main class from which you will always start work - Spreadsheet_Excel_Writer, is a point of access to all other classes in the library. It provides two methods (which are defined in the parent class Spreadsheet_Excel_Writer_Workbook :)
* AddWorksheet () - returns the case Spreadsheet_Excel_Writer_Worksheet. Most of the work done with the case of this class, allowing you to enter data into cells of a leaf.
* AddFormat () - returns the case Spreadsheet_Excel_Writer_Format, which is used to add visual formatting cells.
The library also contains all three classes that need to be afraid of, though they are rarely used.
* Spreadsheet_Excel_Writer_Validator makes it possible to add validation rules for cells. Now for this class there is no documentation. It is as if the experimental code, so I will not discuss it here. Basically, it provides an opportunity to test vvedennyx Policy in the cell end-user. More complex validation rules can be established by expanding the class. The class provides a method Spreadsheet_Excel_Writer_Workbook addValidator () to create a test case, while Spreadsheet_Excel_Writer_Worksheet allows you to assign validation rules to cells by the method setValidation ()
* Spreadsheet_Excel_Writer_Parser, which is Parser - ohm for sheets Policy Excel, and helps you check whether the function is the correct syntax Excel.
* And finally - Spreadsheet_Excel_Writer_BIFFwriter - used to create a file format to save files Excel. If you are interested in hacking Excel, it will be interesting to explore what he does, but if not, then you will not need to worry about it, as the library completely hides the class.
Confusion of zero index.
One method of note - Spreadsheet_Excel_Writer_Worksheet :: write (), which we saw in the above example, you use a lot of time to add the policy in the cell. This method is a little confusing at stavneniyu with the same method in Excel.
The first argument of function write () is the row number. Number of the first row in the tables PEAR :: Spreadsheet_Excel_Writer is 0, not 1, as it is in Excel.
The second argument is the column number. Now, the columns in Excel, identified by letters of the alphabet rather than numbers, so you'll just have to get used to translate between the two. The letter F is the second in the alphabet, so that the second argument ... 5 (of course) - the leftmost column - 0 (zero) in PEAR :: Spreadsheet_Excel_Writer, so you have to subtract to get the column number.
The third argument to write () are the data to be inserted into the cell, there is also a fourth argument is optional and is used for visual formatting cells.
There are many methods in the class Spreadsheet_Excel_Writer_Worksheet, such as to "freeze" or "melting" parts of the sheet to format the list as a whole for the press, etc. On nix, I will explain later, but most of you will have to examine ourselves.
Adding formatting cells.
So what about the finer sheets? We can achieve this by using the PEAR :: Spreadsheet_Excel_Writer function addFormat () to convert the object to Spreadsheet_Excel_Writer_Format. We apply the formatting to this object, using the methods it provides, and then pass it by write () function Spreadsheet_Excel_Writer_Worksheet, to add the formatting of the cell, we have added.
For example, the "real world", let's say I want to give their customers online store phpPetstore.com check the possibility of downloading things they have acquired in a Book (Workbook) containing a single sheet (Worksheet).
I start my usual sheet of material.
First, note that I have received a formatting object by calling addFormat () through the object $ xls, which represents the current page. I then applied to the object of a specific format (the methods called setBold () speak for themselves - for more information, see the documentation API).
When formatting is complete, I call write () for the object $ cart, to be added to the cell by passing the object as a fourth argument.
The only non-standard course, I did here, is the union of the four cells. Calling setAlign ('merge') for the formatting object (usually you use for this 'left', 'right' or 'center'), I ordered Spreadsheet_Excel_Writer, he must unite all the cells to which this format. That's why I created three empty cells, and the formatting applied to them.
Using setRow () allows me to change the row height, making it more than the installation height of a row Excel by default.
This method has many additional arguments formatted to allow you, for example, to apply a formatting object to the current line.
Like setColumn () I can set the column width and apply further formatting.
The difference is that setRow () applies only to a single line when setColumn () is applied to many columns.
Now I need to add data to the worksheet. To keep things simple example (adding a database), I'll use an indexed array of associative arrays, which supposedly is a result of the selection of SQL.
"The columns in the database" are the keys of the array - 'description', 'price' and 'quantity', the second thing we need to do is add the column headings with the additional title of 'Total', which we will soon be used:
You've seen the formatting. You have not seen a method writeRow (). This method does the same as the write (), but allows you to add a data set from left to right, starting from a specific line number or column. This method significantly reduces the code.
I also want to make the column headers are always visible when we scroll the page. In Excel - e it can be done by "freezing" - selecting a block of cells that are visible when the user will scroll through the list, allowing him to see the column headings (in this case), which explain that predstvalvyut these data. The same is possible in the PEAR :: Spreadsheet_Excel_Writer:
Note that the "freezing" was applied directly to the object $ cart, and not by the formatting object, as it has been applied to several cells. On the other hand, the formatting has been applied to individual cells.
Finally I walk through objects in "the bag", adding data to the list:
That's basically it. If you are new to OOP in PHP, at first glance it may seem a little daunting, but you might have noticed that all the methods are very clearly identified and you can understand their meaning only by looking at them.
The idea of attraction of one object to others may be new to you, but when you think about it, it seems that you are creating a Worksheet object by calling addWorksheetSheet (), and that you add objects to the cell formatting when you write () (write) in the Worksheet.
Adding a function Excel.
Now you know how to create nice-looking spreadsheets, but as any al Excel-a say, a simple display of data is not so helpful. Life becomes more interesting when you use Excel functions to calculate data and transform them into something more interesting.
Now I'm not ac Excel - a (and this is not a guide to Excel), but it is clear that my check needs to be more intelligent, so I need to add some calculations based on data that I have made ??to the table. For each row I want to show the "total item cost" (sum of items purchased) - these contain the unit price and the number of purchased products:
"total item cost" = "unit price" * "number of items purchased"
Translated into terms Excel, to produce the sum of the fifth row to write a formula like the following:
[Cell D5] =PRODUCT(B5:C5)
To achieve this with PEAR :: Spreadsheet_Excel_Writer, I just need podpravitt code that carries out a passage through the data:
Add a formula is not so hard - we just use the method writeFormula (). But most importantly, as I mentioned earlier - that Excel starts counting rows from the unit, while the PEAR :: Spreadsheet_Excel_Writer start from scratch.
This means that when you create the function I need to remember this, otherwise I will refer to abnormal cells. That's why I created the variable $ excelRow, that it represents $ currentRow + 1.
You might think that this design flaw on behalf of the authors, but remember that in PHP, as well as in many programming languages??, arrays are indexed starting with zero index.
Now my shows amounts for each line. But what about all the sums add up to the visitor to know about the magnitude of the amount that will be on the receipt of a credit card? It's enough to add up all the amounts and display the result in some sort of cell.
Terms Excel, I need to use the SUM (), to add all amounts shown in column D.
[Grand Total Cell] =SUM(D5:D7)
To place this in the list, after passage through the data is finished, I add the following:
Finally, I end up creating a check list by sending directly to the browser.
That's it. Leaf is ready for download.