Table total row causes Excel error on opening

[wgstjf]

This is:

- [ x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Using (initally) code from https://github.com/PHPOffice/PhpSpreadsheet/blob/master/samples/Table/02_Table_Total.php we have tried to add a totals row to our table that uses SUBTOTAL formula to calculate the total for each column

What is the current behavior?

Excel declares "We found a problem with some content...." and offers to recover the workbook. Agreeing to this results in the file opening and the table displayed correctly (including total row).

The repairs window states that it has repaired records from "Table from /xl/tables/table1.xml part (Table)". The log file contents are:

_<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

error106280_02.xml

Errors were detected in file 'C:\Users\will\Downloads\test-spreadsheet.xlsx'

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.Repaired Records: Table from /xl/tables/table1.xml part (Table)_ If we don't include the totals row, or include the row but don't use formulas, the error goes away. The issue seems to be very much connected to the use of a formula as we have tried the following: - setValue('=SUBTOTAL(109,TransactionData[Total])'); ERRORS - setValue('=SUBTOTAL(109,TransactionData[L2:L37])'); ERRORS - setCellValueExplicit(L37, '=SUM(L2:L36)', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_FORMULA); ERRORS - setValue('Test'); NO ERROR ### What are the steps to reproduce? See above. We are outputting the file using ```php save('php://output'); die(); ``` If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading. ### What features do you think are causing the issue - [ ] Reader - [x ] Writer - [ ] Styles - [ ] Data Validations - [x ] Formula Calculations - [ ] Charts - [ ] AutoFilter - [ ] Form Elements ### Does an issue affect all spreadsheet file formats? If not, which formats are affected? ### Which versions of PhpSpreadsheet and PHP are affected? 1.28 8.0.28 Thanks in advance, Will

[oleibman]

Can you share the code that you are using to create the table and add it to the worksheet? (Roughly this as you've modified it from the sample you started with):

$table = new Table();
// your code 
$spreadsheet->getActiveSheet()->addTable($table);

[wgstjf]

Hi, thanks for replying.

We are dynamically creating rows from a database query. Each row is added as follows:

$j = 2;
   foreach ($event_transactions as $transaction) {
   $transaction_formatted = format_transaction(transaction);
   // Vars
   $last_col_number = $j;
   $col_letter = 'A';
   foreach ($fields as $field) {
      $value = (isset($transaction_formatted[$field])) ? $transaction_formatted[$field] : '';
      $worksheet->setCellValue($col_letter.$j, $value);
      ++$col_letter; 
   }
   $j++;
}

And for the table:

#-----------------------------------------------------------------
# Create Table
#-----------------------------------------------------------------
$table = new Table();
$table->setName('TransactionData');
$table->setShowTotalsRow(true);
$table->setRange('A1:'.$last_col_letter.$j); // Using $j so that we get +1 for the totals row

$spreadsheet->getActiveSheet()->addTable($table);

#-----------------------------------------------------------------
# Add Totals
#-----------------------------------------------------------------

$add_totals_row = true; // If set to false then no errors generated when opening xslx

if ($add_totals_row) {
  $spreadsheet->getActiveSheet()->getCell('H'.$j)->setValue('TOTALS');
  $spreadsheet->getActiveSheet()->getCell('I'.$j)->setValue('=SUBTOTAL(109,TransactionData[Sub Total])');
  $spreadsheet->getActiveSheet()->getCell('J'.$j)->setValue('=SUBTOTAL(109,TransactionData[Booking Fee])');
  $spreadsheet->getActiveSheet()->getCell('K'.$j)->setValue('=SUBTOTAL(109,TransactionData[Discount])');
  $spreadsheet->getActiveSheet()->getCell('L'.$j)->setValue('=SUBTOTAL(109,TransactionData[Refund])');
  $spreadsheet->getActiveSheet()->getCell('M'.$j)->setValue('=SUBTOTAL(109,TransactionData[Total])');
}

This outputs:

     +-----------+------------------+-------------------+---------------------+-----------------------+------------+------------+----------+-------------------------------------------+---------------------------------------------+------------------------------------------+----------------------------------------+---------------------------------------+---------+
     | A         | B                | C                 | D                   | E                     | F          | G          | H        | I                                         | J                                           | K                                        | L                                      | M                                     | N       |
+----+-----------+------------------+-------------------+---------------------+-----------------------+------------+------------+----------+-------------------------------------------+---------------------------------------------+------------------------------------------+----------------------------------------+---------------------------------------+---------+
|  1 | User Name | Participant Name | Horse Name        | Added               | Class Title           | Type       | Spaces     | Order ID | Sub Total                                 | Booking Fee                                 | Discount                                 | Refund                                 | Total                                 | Changes |
|  2 | Test Name | Test Participant | Jim, Penny, Giles | 2023-05-15 12:09:00 | 80cm - COST PER TEAM  | Ammendment | WHAT HERE? | 92       | 40                                        | 2                                           | 0                                        | 0                                      | 42                                    | Test    |
|  3 | Test Name | Test Participant | Jim, Penny, Giles | 2023-05-15 12:03:47 | 90cm cost per team    | Entry      | WHAT HERE? | 91       | 320                                       | 24                                          | 0                                        | 0                                      | 344                                   | Test    |
|  4 | Test Name | Test Participant | Jim, Penny, Giles | 2023-05-15 11:57:26 | 80cm - COST PER TEAM  | Ammendment | WHAT HERE? | 90       | 40                                        | 2                                           | 0                                        | 0                                      | 42                                    | Test    |
|  5 | Test Name | Test Participant | Jim, Penny, Giles | 2023-05-15 11:08:04 | 90cm cost per team    | Entry      | WHAT HERE? | 89       | 320                                       | 24                                          | 0                                        | 0                                      | 344                                   | Test    |
|  6 | Test Name | Test Participant |                   | 2023-04-20 11:16:17 | Spectator Ticket      | Entry      | WHAT HERE? | 77       | 0                                         | 1                                           | 0                                        | 0                                      | 1                                     | Test    |
|  7 | Test Name | Test Participant | Penny, Joy, Giles | 2023-04-20 10:32:19 | 90cm cost per team    | Entry      | WHAT HERE? | 76       | 320                                       | 24                                          | 0                                        | 0                                      | 350                                   | Test    |
|  8 | Test Name | Test Participant | Penny, Jim, Joy   | 2023-04-20 10:17:59 | 90cm cost per team    | Entry      | WHAT HERE? | 75       | 320                                       | 24                                          | 0                                        | 20                                     | 330                                   | Test    |
|  9 | Test Name | Test Participant |                   | 2023-04-20 10:15:19 | 90cm cost per team    | Entry      | WHAT HERE? | 74       | 320                                       | 24                                          | 0                                        | 0                                      | 350                                   | Test    |
| 10 | Test Name | Test Participant |                   | 2023-04-20 10:09:48 | 90cm cost per team    | Entry      | WHAT HERE? | 73       | 320                                       | 24                                          | 0                                        | 0                                      | 350                                   | Test    |
| 11 | Test Name | Test Participant | Penny, Jim, Jeff  | 2023-04-20 09:46:29 | 90cm cost per team    | Entry      | WHAT HERE? | 72       | 320                                       | 8                                           | 0                                        | 0                                      | 330                                   | Test    |
| 12 | Test Name | Test Participant | Jim               | 2023-04-20 09:46:29 | 80cm - COST PER RIDER | Entry      | WHAT HERE? | 72       | 80                                        | 2                                           | 0                                        | 0                                      | 84                                    | Test    |
| 13 | Test Name | Test Participant | Joy               | 2023-04-20 09:46:29 | 80cm - COST PER RIDER | Entry      | WHAT HERE? | 72       | 80                                        | 2                                           | 0                                        | 0                                      | 84                                    | Test    |
| 14 | Test Name | Test Participant | Penny             | 2023-04-20 09:46:29 | 80cm - COST PER RIDER | Entry      | WHAT HERE? | 72       | 80                                        | 2                                           | 0                                        | 0                                      | 84                                    | Test    |
| 15 | Test Name | Test Participant |                   | 2023-03-22 11:22:55 | 80cm - COST PER RIDER | Entry      | WHAT HERE? | 40       | 80                                        | 2                                           | 0                                        | 0                                      | 84                                    | Test    |
| 16 | Test Name | Test Participant | Giles             | 2023-03-21 16:31:26 | 80cm - COST PER RIDER | Entry      | WHAT HERE? | 31       | 80                                        | 2                                           | 0                                        | 80                                     | 2                                     | Test    |
| 17 | Test Name | Test Participant |                   | 2023-03-21 16:31:26 | Spectator Ticket      | Entry      | WHAT HERE? | 31       | 0                                         | 1                                           | 0                                        | 0                                      | 1                                     | Test    |
| 18 | Test Name | Test Participant | Penny             | 2023-03-08 12:48:48 | 80cm - COST PER RIDER | Entry      | WHAT HERE? | 28       | 80                                        | 2                                           | 0                                        | 0                                      | 82                                    | Test    |
| 19 | Test Name | Test Participant | Jim               | 2022-09-28 14:51:12 | Test per rider        | Entry      | WHAT HERE? | 8        | 0                                         | 2                                           | 0                                        | 0                                      | 14.5                                  | Test    |
| 20 | Test Name | Test Participant | Penny             | 2022-09-28 14:51:12 | Test per rider        | Entry      | WHAT HERE? | 8        | 0                                         | 2                                           | 0                                        | 0                                      | 14.5                                  | Test    |
| 21 | Test Name | Test Participant | Penny, Jim, Jeff  | 2022-09-23 12:13:43 | Test per class        | Entry      | WHAT HERE? | 7        | 0                                         | 1.5                                         | 0                                        | 0                                      | 23.56                                 | Test    |
| 22 | Test Name | Test Participant | Jim               | 2022-09-23 12:13:43 | Test per rider        | Entry      | WHAT HERE? | 7        | 0                                         | 2                                           | 0                                        | 0                                      | 14.5                                  | Test    |
| 23 | Test Name | Test Participant | Penny             | 2022-09-23 12:13:43 | Test per rider        | Entry      | WHAT HERE? | 7        | 0                                         | 2                                           | 0                                        | 0                                      | 16.5                                  | Test    |
| 24 | Test Name | Test Participant | Penny, Jim, Giles | 2022-09-22 15:41:40 | Test per rider        | Entry      | WHAT HERE? | 6        | 37.5                                      | 6                                           | 0                                        | 0                                      | 37.5                                  | Test    |
| 25 | Test Name | Test Participant | Penny, Jim        | 2022-09-22 15:20:01 | Test                  | Entry      | WHAT HERE? | 5        | 20.06                                     | 1.5                                         | 0                                        | 0                                      | 24.06                                 | Test    |
| 26 | Test Name | Test Participant | Penny, Jim, Giles | 2022-09-22 15:20:01 | Test 2                | Entry      | WHAT HERE? | 5        | 37.5                                      | 6                                           | 0                                        | 0                                      | 37.5                                  | Test    |
| 27 | Test Name | Test Participant |                   | 2022-09-22 15:20:01 | Spectator Ticket      | Entry      | WHAT HERE? | 5        | 0                                         | 1.5                                         | 0                                        | 0                                      | 0                                     | Test    |
| 28 | Test Name | Test Participant |                   | 2022-09-21 09:34:36 | Spectator Ticket      | Entry      | WHAT HERE? | 4        | 5                                         | 1.5                                         | 0                                        | 5                                      | 5                                     | Test    |
| 29 | Test Name | Test Participant |                   | 2022-09-20 16:09:29 | Spectator Ticket      | Entry      | WHAT HERE? | 3        | 5                                         | 1.5                                         | 0                                        | 0                                      | 5                                     | Test    |
| 30 | Test Name | Test Participant | Penny, Jim, Giles | 2022-09-20 16:09:29 | Test                  | Entry      | WHAT HERE? | 3        | 20.06                                     | 1.5                                         | 0                                        | 0                                      | 22.06                                 | Test    |
| 31 | Test Name | Test Participant |                   | 2022-09-20 15:46:31 | Spectator Ticket      | Entry      | WHAT HERE? | 2        | 5                                         | 1.5                                         | 0                                        | 0                                      | 5                                     | Test    |
| 32 | Test Name | Test Participant | Penny, Jim, Giles | 2022-09-20 15:46:31 | Test 2                | Entry      | WHAT HERE? | 2        | 37.5                                      | 6                                           | 0                                        | 0                                      | 37.5                                  | Test    |
| 33 | Test Name | Test Participant | Penny, Jim        | 2022-09-20 15:46:31 | Test                  | Entry      | WHAT HERE? | 2        | 20.06                                     | 1.5                                         | 0                                        | 0                                      | 20.06                                 | Test    |
| 34 | Test Name | Test Participant | Penny, Jim, Giles | 2022-09-16 12:22:23 | Test                  | Entry      | WHAT HERE? | 1        | 0                                         | 1.5                                         | 0                                        | 0                                      | 0                                     | Test    |
| 35 | Test Name | Test Participant |                   | 2022-09-16 12:22:23 | Spectator Ticket      | Entry      | WHAT HERE? | 1        | 0                                         | 1.5                                         | 0                                        | 0                                      | 0                                     | Test    |
| 36 | Test Name | Test Participant | Penny, Jim, Jeff  | 2022-09-16 12:22:23 | Test 2                | Entry      | WHAT HERE? | 1        | 0                                         | 6                                           | 0                                        | 0                                      | 0                                     | Test    |
| 37 |           |                  |                   |                     |                       |            |            | TOTALS   | =SUBTOTAL(109,TransactionData[Sub Total]) | =SUBTOTAL(109,TransactionData[Booking Fee]) | =SUBTOTAL(109,TransactionData[Discount]) | =SUBTOTAL(109,TransactionData[Refund]) | =SUBTOTAL(109,TransactionData[Total]) |         |
+----+-----------+------------------+-------------------+---------------------+-----------------------+------------+------------+----------+-------------------------------------------+---------------------------------------------+------------------------------------------+----------------------------------------+---------------------------------------+---------+

[MarkBaker]

Before setting the formula for the totals row cells, make sure that the table recognises that the cell has a totals formula

$table->getColumn('I')->setTotalsRowFunction('sum');

for each column that needs a total.

[wgstjf]

Outstanding Mark! Thank you for solving that headache, much appreciated.

[MarkBaker]

Tables are a relatively new feature for PhpSpreadsheet, and are gradually being improved and extended. The most recent improvement was actual support for Structured References within the Calculation Engine; but there is still work to do... particularly with styles and support in other file formats. I'll add automatically updating the totals row function to that list

[wgstjf]

Mark, one more query if I may?

I want to reference the Totals row of the table in a cell on another sheet. If I do so the resulting spreadsheet is corrupted and won't open. If I disable pre calculation of formulas then it does work but the values all display as zero until you enable editing

// Booking Fees
$worksheet->setCellValue('A15', 'Online Booking fees');
$worksheet->setCellValue('C15', '=TransactionData[[#Totals],Total]');

Any thoughts? Cheers, Will

[oleibman]

PR #3659, which was merged last week, should address that problem. Can you confirm by testing against master?

[Gawdl3y]

I am experiencing this issue even when setting totals row functions for each of the columns before overwriting the cell values.

// Get the highest row/column (+1 to the row since we're going to add a totals row)
$worksheet = $event->sheet->getDelegate();
$highest = $worksheet->getCellCollection()->getHighestRowAndColumn();
$highest['row']++;

// Set up the table
$table = new Table;
$table->setName('DepartmentSummary');
$table->setShowTotalsRow(true);
$table->setRange("A1:{$highest['column']}{$highest['row']}");

// Set up the totals row and add the table to the sheet
$table->getColumn('A')->setTotalsRowLabel('Total');
$table->getColumn('B')->setTotalsRowFunction('sum');
$table->getColumn('C')->setTotalsRowFunction('sum');
$table->getColumn('D')->setTotalsRowFunction('sum');
$worksheet->addTable($table);

// Overwrite the totals row cells since PhpSpreadsheet doesn't yet fully implement the functionality
$worksheet->getCell("A{$highest['row']}")->setValue('Total');
$worksheet->getCell("B{$highest['row']}")->setValue('=SUBTOTAL(109, DepartmentSummary[Hours])');
$worksheet->getCell("C{$highest['row']}")->setValue('=SUBTOTAL(109, DepartmentSummary[Volunteers])');
$worksheet->getCell("D{$highest['row']}")->setValue('=SUBTOTAL(109, DepartmentSummary[Time entries])');

With this code, when opening the spreadsheet, Excel gives the exact error as in the original issue description.

[oleibman]

I add the following lines near the end of 02_Table_Totals.php, on which you have based your program:

$newSheet = $spreadsheet->createSheet();
$newSheet->getCell('A1')->setValue('=SalesData[[#Totals],Sales]');

I think this is close to what you're trying to accomplish - getting the total for a specific column of a table located on a different sheet. When I run this code, I am able to open the result, and A1 on the second sheet shows the correct formula (it doesn't have a value for the formula in the Xml, but Excel fills that in with 9930, as it should). The same is more or less true if I enter the formula as =SalesData[[#Totals],[Sales]]. So, if you are doing approximately the same thing and winding up with a corrupt spreadsheet, is it possible to upload your spreadsheet to see if I can figure out what might be wrong with it?

[Gawdl3y]

In my case, the table that I'm adding the totals row to is on the same sheet - I'm not attempting to cross sheets at all, as I don't even have more than one sheet.

[oleibman]

Again, 02_Tables_Total adds the totals row on the same sheet and does not have a problem. I need to see your spreadsheet in order to attempt to figure out what is going wrong.

[vectorseKGS]

Gawdl3y, Try removing the space in front of 'DepartmentSummary' in your SUBTOTAL formula. I had the same issue. Removing any whitespace between the comma and the table name resolved the issue.