1

I am currently using PHPExcel to output an order's information to Excel.

I have the following foreach-statements that ultimate generate my data such as the city in which the order was made in, the restaurant name, etc.

Pardon the nested foreach-loops - it was the only way I could nest through all that many cities and legal entities and restaurants to generate the data the customer wanted.

Using an answer posted here which dynamically generates the row and column integer, I tried it in my code.

$row = 1; // 1-based index
$col = 0;

foreach ($this->data['total_by_city'] as $city_id => $total_city){
    $city_name = '';
    foreach ($this->data['total_by_legal_entities'] as $legal_entity_id => $total_legal_entity) {
        $legal_entity_name = '';


        foreach ($this->data['restaurant_by_legal_entities'][$legal_entity_id] as $restaurant_id) {
            $orders = $this->data['order_by_restaurants'][$restaurant_id];
            $restaurant_name = '';
            if ($orders)
            {
                foreach ($orders as $order_id => $order)
                {
                        $restaurant_name = $order['restaurant_name'];
                        $legal_entity_name = $order['legal_entity'];
                        $city_name = $order['city'];
                        echo $row . ", ". $col . "<br>";
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['city']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['legal_entity']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['restaurant_name']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['payment_method']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['order_number']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['date_created']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['customer_name']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['order_type']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['amount']);
                        $row++;
                        echo $row . ", ". $col . "<br>";
                        $col = 0;
                }
            }
        }
    }
}

When I ran my code, and attempted to open the .xlsx file, I received a "corrupted Excel spreadsheet" error in Excel.

To make sure that my rows and column (respectively) indices are correct, I printed them out:

1, 0
2, 8
2, 0
3, 8
3, 0
4, 8
4, 0
5, 8
5, 0
6, 8
6, 0
7, 8
7, 0
8, 8
8, 0
9, 8
9, 0
10, 8
10, 0
11, 8
11, 0
12, 8
12, 0
13, 8
13, 0
14, 8
14, 0
15, 8
15, 0
16, 8

From that observation, I see that the rows and columns indices I am using are incorrect. They are both in the wrong location and reset/incremented incorrectly.

My question is - how do I increment and reset my column and row indices correctly?

Shahnewaz
  • 360
  • 4
  • 12
theGreenCabbage
  • 5,197
  • 19
  • 79
  • 169
  • ... or take a look at http://stackoverflow.com/a/37972341/4719732 to convert index values to column letters. I don't know which way is faster ... – Glaubule Jun 22 '16 at 15:43

3 Answers3

3

You have forgot to increment Column index I guess

try following 

    $row = 1; // 1-based index
    $col = 0;

    foreach ($this->data['total_by_city'] as $city_id => $total_city){
        $city_name = '';
        foreach ($this->data['total_by_legal_entities'] as $legal_entity_id => $total_legal_entity) {
            $legal_entity_name = '';


            foreach ($this->data['restaurant_by_legal_entities'][$legal_entity_id] as $restaurant_id) {
                $orders = $this->data['order_by_restaurants'][$restaurant_id];
                $restaurant_name = '';
                if ($orders)
                {
                    foreach ($orders as $order_id => $order)
                    {
                        $restaurant_name = $order['restaurant_name'];
                        $legal_entity_name = $order['legal_entity'];
                        $city_name = $order['city'];
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['city']);
                         $col++; // Increment for each Cell
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['legal_entity']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['restaurant_name']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['payment_method']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['order_number']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['date_created']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['customer_name']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['order_type']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['amount']);

                    }
                    $col=0;
                    $row++;
                }
            }
        }

Note- in excel each Cell should have unique RowID and ColumnID

Kavindu Dodanduwa
  • 12,193
  • 3
  • 33
  • 46
  • Hello @KCdod! I did what you asked, and here are the new data about the rows and columns: http://puu.sh/dHDrB/796147a5aa.png I think we are forgetting to reset the column again. – theGreenCabbage Dec 24 '14 at 10:54
  • yes after inner most insert we need to reset $col=0; – Kavindu Dodanduwa Dec 24 '14 at 10:57
  • Hi KCdod. I have updated my code with the updated rows and columns. That seems more "correct", in that the rows and columns are consistent and incremented correctly. However, I still receive the same corrupted file error. – theGreenCabbage Dec 24 '14 at 11:09
  • Excel cells need to have unique Row index and a column index. Since you have many for each loops check whether you update indexes properly :) – Kavindu Dodanduwa Dec 24 '14 at 14:25
  • I got it! Please check my answer =). We needed to reset the column immediately. – theGreenCabbage Dec 24 '14 at 16:46
2

Using KCdod's answer, I got it to work:

$row = 1; // 1-based index
$col = 0;

foreach ($this->data['total_by_city'] as $city_id => $total_city){
    $city_name = '';
    foreach ($this->data['total_by_legal_entities'] as $legal_entity_id => $total_legal_entity) {
        $legal_entity_name = '';


        foreach ($this->data['restaurant_by_legal_entities'][$legal_entity_id] as $restaurant_id) {
            $orders = $this->data['order_by_restaurants'][$restaurant_id];
            $restaurant_name = '';
            if ($orders)
            {
                foreach ($orders as $order_id => $order)
                {
                        $restaurant_name = $order['restaurant_name'];
                        $legal_entity_name = $order['legal_entity'];
                        $city_name = $order['city'];
                        echo $row . ", ". $col . "<br>";
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['city']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['legal_entity']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['restaurant_name']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['payment_method']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['order_number']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['date_created']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['customer_name']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['order_type']);
                        $col++;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $order['amount']);
                        $row++;
                        echo $row . ", ". $col . "<br>";
                        $col = 0;
                }
            }
        }
    }
}
theGreenCabbage
  • 5,197
  • 19
  • 79
  • 169
1

I am using from_array:

$arrayData = array(
array(NULL, 2010, 2011, 2012),
array('Q1',   12,   15,   21),
array('Q2',   56,   73,   86),
array('Q3',   52,   61,   69),
array('Q4',   30,   32,    0),); 
$objPHPExcel->getActiveSheet()->fromArray(
    $arrayData,  // The data to set
    NULL,        // Array values with this value will not be set
    'C3' );        // Top left coordinate of the worksheet range where we want to set these values (default is A1)

In a project with PHP, I assigned values in a multidimensional array, and this function works fine.

Thanks.

Cristian Betancur Beltran
  • 11
  • 2