<?php

if ( ! defined( 'ABSPATH' ) ) { exit; }

class AIEO_DMM_XLSX_Reader {

	
	public static function read( $path ) {
		if ( ! class_exists( 'ZipArchive' ) || ! is_readable( $path ) ) {
			return null;
		}
		$zip = new ZipArchive();
		if ( true !== $zip->open( $path ) ) {
			return null;
		}

		
		$shared = array();
		$ss     = $zip->getFromName( 'xl/sharedStrings.xml' );
		if ( false !== $ss ) {
			$sx = self::load_xml( $ss );
			if ( $sx ) {
				foreach ( $sx->si as $si ) {
					$shared[] = self::si_text( $si );
				}
			}
		}

		
		$sheet = $zip->getFromName( 'xl/worksheets/sheet1.xml' );
		if ( false === $sheet ) {
			for ( $i = 0; $i < $zip->numFiles; $i++ ) {
				$n = (string) $zip->getNameIndex( $i );
				if ( 0 === strpos( $n, 'xl/worksheets/' ) && '.xml' === substr( $n, -4 ) ) {
					$sheet = $zip->getFromName( $n );
					break;
				}
			}
		}
		$zip->close();
		if ( ! $sheet ) {
			return null;
		}

		$x = self::load_xml( $sheet );
		if ( ! $x || ! isset( $x->sheetData ) ) {
			return null;
		}

		$rows = array();
		foreach ( $x->sheetData->row as $row ) {
			$cells = array();
			$max   = -1;
			foreach ( $row->c as $c ) {
				$ref  = (string) $c['r'];
				$col  = self::col_index( preg_replace( '/[0-9]+/', '', $ref ) );
				$type = (string) $c['t'];
				if ( 's' === $type ) {
					$idx = isset( $c->v ) ? (int) $c->v : -1;
					$val = isset( $shared[ $idx ] ) ? $shared[ $idx ] : '';
				} elseif ( 'inlineStr' === $type ) {
					$val = isset( $c->is->t ) ? (string) $c->is->t : '';
				} else {
					$val = isset( $c->v ) ? (string) $c->v : '';
				}
				$cells[ $col ] = $val;
				if ( $col > $max ) {
					$max = $col;
				}
			}
			$dense = array();
			for ( $i = 0; $i <= $max; $i++ ) {
				$dense[ $i ] = isset( $cells[ $i ] ) ? $cells[ $i ] : '';
			}
			$rows[] = $dense;
		}
		return $rows;
	}

	private static function load_xml( $s ) {
		$prev = libxml_use_internal_errors( true );
		$x    = simplexml_load_string( (string) $s );
		libxml_clear_errors();
		libxml_use_internal_errors( $prev );
		return $x ? $x : null;
	}

	
	private static function si_text( $si ) {
		if ( isset( $si->t ) ) {
			return (string) $si->t;
		}
		$out = '';
		if ( isset( $si->r ) ) {
			foreach ( $si->r as $r ) {
				if ( isset( $r->t ) ) {
					$out .= (string) $r->t;
				}
			}
		}
		return $out;
	}

	
	private static function col_index( $letters ) {
		$letters = strtoupper( (string) $letters );
		$n       = 0;
		$len     = strlen( $letters );
		for ( $i = 0; $i < $len; $i++ ) {
			$c = ord( $letters[ $i ] );
			if ( $c < 65 || $c > 90 ) {
				continue;
			}
			$n = $n * 26 + ( $c - 64 );
		}
		return $n - 1;
	}
}
