Suppose we need this sign:

http://i.stack.imgur.com/6mN0i.jpg

Additionally, you need filters in the header, I do everything like this:

$ objPHPExcel = new PHPExcel ();

$objPHPExcel->setActiveSheetIndex(0); $active_sheet = $objPHPExcel->getActiveSheet(); $active_sheet->setCellValue('A3', '№'); $active_sheet->setCellValue('B3', 'Товар'); $active_sheet->setCellValue('C3', 'Цена'); $active_sheet->setAutoFilter('A3:C3'); $active_sheet->setCellValue('A4', '1'); $active_sheet->setCellValue('B4', 'Товар 1'); $active_sheet->setCellValue('C4', '200'); $active_sheet->setCellValue('A5', '2'); $active_sheet->setCellValue('B5', 'Товар 2'); $active_sheet->setCellValue('C5', '500'); $active_sheet->setCellValue('A6', '3'); $active_sheet->setCellValue('B6', 'Товар 3'); $active_sheet->setCellValue('C6', '120'); $active_sheet->setCellValue('A7', '4'); $active_sheet->setCellValue('B7', 'Товар 4'); $active_sheet->setCellValue('C7', '245'); $active_sheet->setCellValue('A8', '5'); $active_sheet->setCellValue('B8', 'Товар 5'); $active_sheet->setCellValue('C8', '130'); $active_sheet->setCellValue('A9', 'Итог'); $active_sheet->setCellValue('C9', '=SUM(C4:C8)'); header("Content-Type:application/vnd.ms-excel"); header("Content-Disposition:attachment;filename=simple.xls"); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit();

Get the table:

http://i.stack.imgur.com/b5zod.jpg

Everything is good, but when choosing a filter:

i.stack.imgur.com/go3G6.png

The final line also falls under the filtering, and if you apply a filter for the price for example, the amount in the final line does not change ..

How to implement a label with filters and a string of totals?

  • With the recalculation of the results filtering will not cope. Make a web application with filtering and recalculation. You have php in hand. And I will delete my answer - tutankhamun
  • But after all in usual Excel it can be done .. - entermix
  • For sure. Use SUBTOTAL instead of SUM and will only consider filtered tutankhamun
  • Thank you very much! I tried to do it manually — it works, but when generating with PHPExcel: $ active_sheet-> setCellValue ('C9', '= SUBTOTAL (9; C4: C8)') ;, or so $ active_sheet-> setCellValue ('C9', ' = SUBTOTAL (109; C4: C8) '); the file is not generated, an error occurs when you try to download it: “File not found”, if you remove this line - everything is OK, can you tell me what could be the problem? - entermix
  • Look for errors in the logs - tutankhamun

1 answer 1

Still, I will restore the answer in connection with the new circumstances.

I'm afraid this is a limitation of Excel and Calc. Try to bring up the result above the filter (in the sense it will work out, but I don’t know if it is permissible in your case). I also add information from the commentary: "In order to SUBTOTAL() only filtered values, you need to use the SUBTOTAL() function instead of SUM() ."

However, look at the PHPExcel source code:

  'SUBTOTAL' => array( 'category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG, 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUBTOTAL', 'argumentCount' => '2+' ), 'SUM' => array( 'category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG, 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUM', 'argumentCount' => '1+' ),

Particularly interested in the string 'argumentCount' => '2+' . (I myself was surprised when I found out). The first argument needs to specify how to calculate the total (for the amount ignoring hidden values, it should be 109 9 ). Read more in the documentation (I apologize for being in English, but in Russian many functions look awful)

  • There is nothing special about transferring the function number; in Excel itself, too. I tried to run: $ active_sheet-> setCellValue ('C9', '= SUBTOTAL (109; C4: C8)'); but when I try to upload the file for download, it is not generated, and if I try to generate it in Excel2007, this error occurs : savepic.ru/7940447.jpg , and cell C9 contains the digit "0" - entermix
  • Dug deeper: a) 109 - PHPExcel is not supported (you need to use 9); b) in functions inside PHPExcel, the argument separator is a comma - tutankhamun
  • It worked, thank you very much for the help! :) - entermix
  • > b) in functions inside PHPExcel, the argument separator is a comma - oh, thanks, and I was tormented) - Almaz Vildanov