Add bank files readers (OFX and QIF)

[EtienneDelepine]

Hi everyone,
Thank's a lot for this amazing and very useful library.
I needed to read files from bank accounts, with OFX and QIF formats. I wrote the readers according to the PhpSpreadsheet structure and added the extensions for automatic recognition and read.
It required changes in several PhpSpreadsheet files and I have to take care of all these changes when updating the library. I wonder if they can be included in the master code of the library.
I can give the code I made for that, if it helps.
Thank you

[MarkBaker]

A part of me is thinking "yes", another part thinking that they aren't strictly spreadsheet formats, but that it is useful to be able to import them into a spreadsheet... that latter part is thinking that it would be more useful as a separate library with a dependency on PHPSpreadsheet, or an extension to PHPSpreadsheet. For that, I'd need to look at how practical it was to have a "secondary" library adding new Readers and/or Writers to the core. The latter may have some benefits in allowing users to choose which formats they might want to include through composer, rather than having all formats bundled in the main library.

I'd certainly take a look if you submitted a PR (I'm also concerned with what changes you might have made to the core Spreadsheet itself); but won't make any guarantees about if and in what form it might be included.

I'd also be interested in seeing how many other users might find it useful adding support for reading these formats

[MarkBaker]

I thought that I'd run a Twitter poll to see if there was a significant number of people that would find this useful:

Poll

[EtienneDelepine]

Thank you for answering. The poll result seems to indicate that bank formats are not very useful for most of the people (and I'm happy with that, they probably have more interesting things to play with!)
The changes to PHPSpreadsheet core are:

• Add ofx and qif to the list IOFactory::$readers
• Add cases for extensions in IOFactory::getReaderTypeFromExtension()
• Add each class in PhpOffice\PhpSpreadsheet\Reader, extending BaseReader

It's simple. My readers are not fully developed for all data of those formats, only the ones I need.

For other reasons, I made few other changes.
To check the reader used in a spreadsheet, I added:

	/**
	 * @var string Full Class name of the reader used to create this spreadsheet
	 */
	public $readerType; 

Then in IOFactory, I also added the auto-detection of some encodings in load(), and the reader data:

	public static function load($pFilename) {
		$reader = self::createReaderForFile($pFilename);

		// Try to detect file encoding
		if (method_exists($reader, 'setInputEncoding')) {
			$encoding = mb_detect_encoding(file_get_contents($pFilename), "ASCII,UTF-8,ISO-8859-15");
			$reader->setInputEncoding($encoding);
		}

		// Add reader classname
		$spreadsheet = $reader->load($pFilename);
		$spreadsheet->readerType = get_class($reader);
		return $spreadsheet;
	}

Because I work with French files and without that, the encoding can be broke. I know this code is too specific to be used as is for all PHPSpreadsheet users, but perhaps something to work around.

Lastly, I made changes in Worksheet->rangeToArray() to allow specific Date formats export. Else French dates are badly write into the array (we use DD/MM/YYYY and not MM/DD/YYYY)

	/**
	 * Create array from a range of cells.
	 [...]
	 * @param string $dateFormat Format of the dates to print, replace format from cell styles (if not null). Default yyyy-mm-dd
	 *
	 * @return array
	 */
	public function rangeToArray($pRange, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false, $dateFormat = NumberFormat::FORMAT_DATE_YYYYMMDD) {
// [... beginning is the same] 
			if ($formatData) {
				// If the code is for a date, replace by the given method parameter
				$numberFormatCode = NumberFormat::FORMAT_GENERAL;
				$style = $this->parent->getCellXfByIndex($cell->getXfIndex());
				if ($style && $style->getNumberFormat()) {
					$numberFormatCode = $style->getNumberFormat()->getFormatCode();
					if ($dateFormat != null && Shared\Date::isDateTime($cell)) {
						$numberFormatCode = $dateFormat;
					}
				}
				$returnValue[$rRef][$cRef] = NumberFormat::toFormattedString(
						$returnValue[$rRef][$cRef],
						$numberFormatCode
				);
// [... end is the same] 

If it can help ;)

[MarkBaker]

Sadly not!

However, I might look at refactoring the way that Readers are loaded so that it's possible to create an extension library (with PhpSpreadsheet as a dependency) that would provide a mechanism to add new Readers without changing PhpSpreadsheet itself