Excel file viewer in php
SecurityXML-based formats such as OfficeOpen XML, Excel2003 XML, OASIS and Gnumeric are susceptible to XML External Entity Processing (XXE) injection attacks when reading spreadsheet files. This can lead to: Show
To prevent this, by default every XML-based Reader looks for XML entities declared inside the DOCTYPE and if any is found an exception is raised. Read more about of XXE injection. Loading a Spreadsheet FileThe simplest way to load a workbook file is to let PhpSpreadsheet's IO Factory identify the file type and load it, calling the static
See The The method makes an initial guess at the loader to instantiate based on the file extension; but will test the file before actually executing the load: so if (for example) the file is actually a CSV file or contains HTML markup, but that has been given a .xls extension (quite a common practise), it will reject the Xls loader that it would normally use for a .xls file; and test the file using the other loaders until it finds the appropriate loader, and then use that to read the file. If you know that this is an
While easy to implement in your code, and
you don't need to worry about the file type; this isn't the most efficient method to load a file; and it lacks the flexibility to configure the loader in any way before actually reading the file into a Creating a Reader and Loading a Spreadsheet FileIf you know the file type of the spreadsheet file that you need to load, you can instantiate a new reader object for that file type, then use the reader's
See Alternatively, you can use the IO Factory's
See If you're uncertain of the filetype, you can use the
See As with the IOFactory
You can also use this to confirm that a file is what it claims to be:
Spreadsheet Reader OptionsOnce you have created a reader object for the workbook that you want to load, you have the opportunity to set additional options before executing the Reading Only Data from a Spreadsheet FileIf you're only interested in the cell values in a workbook, but don't need any of the cell formatting information, then you can set the reader to read only the data values and any formulae from each cell using the
See It is important to note that most Workbooks (and PhpSpreadsheet) store dates and times as simple numeric values: they can only be distinguished from other numeric values by the format mask that is applied to that cell. When setting read data only to true, PhpSpreadsheet doesn't read the cell format masks, so it is not possible to differentiate between dates/times and numbers. The Gnumeric loader has been written to read the format masks for date values even when read data only has been set to true, so it can differentiate between dates/times and numbers; but this change hasn't yet been implemented for the other readers. Reading Only Data from a Spreadsheet File applies to Readers:
Reading Only Named WorkSheets from a FileIf your workbook contains a number of worksheets, but you are only interested in reading some of those, then you can use the To read a single sheet, you can pass that sheet name as a parameter to the
See If you want
to read more than just a single sheet, you can pass a list of sheet names as an array parameter to the
See To reset this option to the default, you can call the
See Reading Only Named WorkSheets from a File applies to Readers:
Reading Only Specific Columns and Rows from a File (Read Filters)If you are only interested in reading part of a worksheet, then you can write a filter class that identifies whether or not individual cells should be read by the loader. A read filter must implement the
See This example is not particularly useful, because it can only be used in a very specific circumstance (when you only want cells in the range A1:E7 from your worksheet. A generic Read Filter would probably be more useful:
See This can be particularly useful for conserving memory, by allowing you to read and process a large workbook in "chunks": an example of this usage might be when transferring data from an Excel worksheet to a database. Read Filtering does not renumber cell rows and columns. If you filter to read only rows 100-200, cells that you read will still be numbered A100-A200, not A1-A101. Cells A1-A99 will not be loaded, but if you then try to call Methods such as
See Using Read Filters applies to:
Combining Multiple Files into a Single Spreadsheet ObjectWhile you can limit the number of worksheets that are read from a workbook file using the
See Note that using the same sheet index for multiple sheets won't append files into the same sheet, but overwrite the results of the previous load. You cannot load multiple CSV files into the same worksheet. Combining Multiple Files into a Single Spreadsheet Object applies to:
Combining Read Filters with the setSheetIndex() method to split a large CSV file across multiple WorksheetsAn Xls BIFF .xls file is limited to 65536 rows in a worksheet, while the Xlsx Microsoft Office Open XML SpreadsheetML .xlsx file is limited to 1,048,576 rows in a worksheet; but a CSV file is not limited other than by available disk space. This means
that we wouldn’t ordinarily be able to read all the rows from a very large CSV file that exceeded those limits, and save it as an Xls or Xlsx file. However, by using Read Filters to read the CSV file in "chunks" (using the ChunkReadFilter Class that we defined in the above section, and the
See This code will read 65,530 rows at a time from the CSV file that we’re loading, and store each "chunk" in a new worksheet. The For example, if the filter returned true for cells in the range B2:C3, then with setContiguous set to false (the default) these would be loaded as B2:C3 in the Splitting a single loaded file across multiple worksheets applies to:
Pipe or Tab Separated Value FilesThe CSV loader will attempt to auto-detect the separator used in the file. If it cannot auto-detect, it will default to the comma. If this does not fit your use-case, you can manually specify a separator by using the
See In addition to the delimiter, you can also use the following methods to set other attributes for the data load:
Setting CSV delimiter applies to:
Reading formatted Numbers from a CSV FileUnfortunately, numbers in a CSV file may be formatted as strings. If that number is a simple integer or float (with a decimal (Assuming that our server is configured with German locale settings: otherwise it may be necessary to call
This will attempt to load those formatted numeric values as numbers, based on the server's locale settings. If you want to load those
values as numbers, but also to retain the formatting as a number format mask, then you can pass a boolean If your Csv file includes other formats for numbers (currencies, scientific format, etc); then you should probably also use the Advanced Value Binder to handle these cases. Applies to:
A Brief Word about the Advanced Value BinderWhen loading data from a file that contains no formatting information, such as a CSV file, then data is read either as strings or numbers (float or integer). This means that PhpSpreadsheet does not automatically recognise dates/times (such as A Value Binder is a class that implement the So using a Value Binder allows a great deal more flexibility in the loader logic when reading unformatted text files.
See Loading using a Value Binder applies to:
Error HandlingOf course, you should always apply some error handling to your scripts as well. PhpSpreadsheet throws exceptions, so you can wrap all your code that accesses the library methods within Try/Catch blocks to trap for any problems that are encountered, and deal with them in an appropriate manner. The PhpSpreadsheet Readers throw a
See Helper MethodsYou can retrieve a list of worksheet names contained in a file without loading the whole file by using the Reader’s listWorksheetNamesThe
See listWorksheetInfoThe
See |