PHPOffice Spreadsheet to formatted excel data to array

44 Views Asked by At

I tried all the way to sort array but still not possible.

click here to find attached XLSX

format of data required.

array(
    ['GROCERY'] => array(
        [0] => array(
            'Vendor' => 'Jfc International',
            'UPC' => '076186000080',
            'Item Name' => '',
            'Regular Price' => $1.99,
        )
        [1] => array(
            'Vendor' => 'NY FANCY',
            'UPC' => '044082032412',
            'Item Name' => '',
            'Regular Price' => $8.49,
        )
        [2] => array(
            'Vendor' => 'Jfc International',
            'UPC' => '012546005463',
            'Item Name' => '(6 Pack)-Dentyne Ice Winter Chill-16 Pieces Each-Totalof 96 Pieces.',
            'Regular Price' => $1.75,
        )
        ... so on 
    )
    ['Category2'] => array(
        [0] => array(
            'Vendor' => 'ROYAL FOOD',
            'UPC' => '037695160344',
            'Item Name' => '037695160344',
            'Regular Price' => $1.99,
        )
        [1] => array(
            'Vendor' => 'DANIEL',
            'UPC' => '038101082502',
            'Item Name' => '038101082557',
            'Regular Price' => $3.49,
        )
        ... so on 
    ),
    ['Category3'] => array(
        [0] => array(
            'Vendor' => 'ROYAL FOOD',
            'UPC' => '840379101973',
            'Item Name' => '2 DARK CHOCOLATE CRISPY PEANUT BUTTER CUPS',
            'Regular Price' => $2.99,
        )
        [1] => array(
            'Vendor' => 'ROYAL FOOD',
            'UPC' => '894455000247',
            'Item Name' => '2 MILK CHOCOLATE PEANUT BUTTER CUPS',
            'Regular Price' => $3.49,
        )
        ... so on 
    ),
    ... so on
)
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('sheet.xlsx');
$sheetData = array(1,$spreadsheet->getActiveSheet()->toArray(null,true,true,true));

// Display the sheet content
$all_data = array();
$products = array();
$d=0;$x=0;
if(!empty($sheetData) && is_array($sheetData)){
    foreach ($sheetData[1] as $key => $value) {
        //echo "<pre>"; print_r($value);
        // $products = array();
        // $d = 0;
        
        $category = isset($value['A']) ? $value['A'] : "";

        if(!empty($value['C']) && !empty($value['D']) && !empty($value['F'])) {
            $all_data[$category][$d]['vendor'] = $value['B'];
            $all_data[$category][$d]['upc'] = $value['C'];
            $all_data[$category][$d]['item'] = $value['D'];
            $all_data[$category][$d]['price'] = $value['F'];
            $d++;
        }
    }
}

echo "<pre>"; print_r($all_data);exit;
0

There are 0 best solutions below