Table total row causes Excel error on opening #3572
Comments
|
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); |
|
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]) | |
+----+-----------+------------------+-------------------+---------------------+-----------------------+------------+------------+----------+-------------------------------------------+---------------------------------------------+------------------------------------------+----------------------------------------+---------------------------------------+---------+ |
|
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. |
|
Outstanding Mark! Thank you for solving that headache, much appreciated. |
|
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 |
|
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 |
|
PR #3659, which was merged last week, should address that problem. Can you confirm by testing against master? |
|
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. |
|
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 |
|
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. |
|
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. |
|
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. |
|
Hi ! Secondly, whatever I do, except if I disable the table in the sheet, I have an error, and I don't see any reason why. I use this code. ` ` But as I said, even with just a new Table and addTable I get the error. |
|
@seyfcom Without the entire code, I can't be sure, but the following statement looks wrong to me: $table->setName('Participation')->setRange('A'. $r .':' . $maxColumn);I think you need to specify an ending row for the range. Your code looks like it will give you a range of, say, A2:C, but your range needs to be something like A2:C5. |
|
@oleibman $maxColumn is actually a bad variable name, it is a string like "R98", so it gives the full range. |
|
It is still difficult to figure out without all the missing code (arrayData, columnSumPresence, getLetterCodeFromInt, maybe others). However, I think the following code gives a good approximation of what you want to do: $spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$table = new Table();
$r = 1;
$totalRow = 8;
$sheet->fromArray([
['Column1', 'Presence', 'Reference', 'Difference'],
[null, 1, null, null],
[null, null, 2, null],
[null, null, null, 3],
[null, null, 4, null],
[null, 5, null, null],
]);
$maxColumn = "D$totalRow";
$table->setName('Participation')
->setRange('A'. $r .':' . $maxColumn);
$style = new TableStyle();
$style->setTheme(TableStyle::TABLE_STYLE_MEDIUM2)
->setShowRowStripes(true);
$table->setStyle($style);
$table->setShowHeaderRow(true)
->setAllowFilter(true)
->setShowTotalsRow(true);
// set column for SUM function
foreach (['B', 'C', 'D'] as $col) {
$table->getColumn($col)
->setTotalsRowFunction('sum');
}
$sheet->setCellValue('A' . $totalRow, 'Totals');
foreach (['B', 'C', 'D'] as $col) {
$sheet->setCellValue(
"$col$totalRow",
'=SUBTOTAL(109,Participation['
. $sheet->getCell($col . $r)->getValue()
. '])'
);
}
$sheet->addTable($table);
$writer = new XlsxWriter($spreadsheet);
$outfile = 'issue.3572.xlsx';
$writer->save($outfile);My output spreadsheet from this code opens without issue, and appears to be correct. Can you compare this to your code and see where there might be differences? |
|
Hi ! I finally found the error, it was on a merge cell, outside of the table, but it broke the table somehow... sorry and thanks a lot for your help ! |
This is:
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:
error106280_02.xml
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:
What are the steps to reproduce?
See above. We are outputting the file using
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
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
The text was updated successfully, but these errors were encountered: