Come inserire righe in phpExcel, con formule correnti di upsumming dinamiche?

Ho una list di prezzo e mi piace inserire le righe (con il prezzo degli articoli) con phpExcel. Ho una formula nel file di Excel per calcolare il totale:

=SUMME(E$1:E11) 

Inserisco le righe (dei prezzi) con il command:

  $oSheet->insertNewRowBefore( $row + 1, 1 ); 

Dopo il rendering, tutto sembra buono, ma la formula è ancora la stessa:

  =SUMME(E$1:E11) 

Ma deve essere ad esempio (quando si aggiungono 3 righe):

  =SUMME(E$1:E13) 

Quindi ho il risultato errato di Total. Solo un elemento verrà calcolato invece di tutti gli elementi.

Come aggiungere una row, con l'aggiornamento automatico della formula? Stesso come in Excel?

Questa è la mia function PHP where sostituisce i segnaposti nel file di Excel:

 $aValueExcel = arrays(); $aHiddenExcel = arrays(); $aHiddenExcelRow = arrays(); define( "HideRow", "HideRow" ); define( "CreatePDF", "CreatePDF" ); define( "Hidden", "Hidden" ); /** Include PHPExcel_IOFactory */ require_once __DIR__ . '/../classs/PHPExcel/IOFactory.php'; /** Ersetzt die Variablen in Platzhaltern <variable> mit den Werten der Global Array-Variable $aValueExcel. * * Parameter: * $sFileTemplate Der Name des Excel-Files, das bearbeitet werden soll. * $sTitle Dummy Variable. * * Globale Variablen * $aValueExcel Die zu ersetzenden Werte als Assoziatives Array. * $aHiddenExcel Der Namen der Felder, die nicht angezeigt werden sollen. * $aHiddenExcelRow Der Namen der Felder, deren ganze Zeile nicht angezeigt werden soll. */ function CreateExcel( $sFileTemplate, $sTitle ) { global $aValueExcel; global $aHiddenExcel; global $aHiddenExcelRow; $sPathTemplate = $GLOBALS['DIR_KUNDEN'] ."/docs/templates/" . $sFileTemplate; if (!file_exists( $sPathTemplate )) { exit("File not Found"); } $objPHPExcel = PHPExcel_IOFactory::load( $sPathTemplate ); $objPHPExcel->setActiveSheetIndex(0); $oSheet = $objPHPExcel->getActiveSheet(); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); /** Wir definieren welche Spalten durchsucht werden sollen. Für eine Optimierte Geschwindigkeit, machen wir nur die Spalten von A bis Z. */ $chars = arrays(); $chars[] = "A"; $chars[] = "B"; $chars[] = "C"; $chars[] = "D"; $chars[] = "E"; $chars[] = "F"; $chars[] = "G"; $chars[] = "H"; $chars[] = "I"; $chars[] = "J"; $chars[] = "K"; $chars[] = "L"; $chars[] = "M"; $chars[] = "N"; $chars[] = "O"; $chars[] = "P"; /** Initialisieren. Die letzte nicht leere Spalte festhalten um die Dokumentgrösse richtig einstellen zu können. */ $lastFilledColumnIndex = 0; $lastFilledRowIndex = 0; for( $row = 1; $row < 100; $row++ ) { foreach( $chars as $char ) { $value = $oSheet->getCell( $char . $row )->getValue(); if( !$value ) continue; /** Leere Zeilen überspringen */ if( arrays_search( $char, $chars ) > $lastFilledColumnIndex ) { $lastFilledColumnIndex = arrays_search( $char, $chars ); } if( $row > $lastFilledRowIndex ) { $lastFilledRowIndex = $row; } $iCharPos = 0; $blnOpenTag = false; $aPlaceholder = arrays(); $iPlaceholder = 0; /** Nach Platzhalter suchen */ while( isset( $value[ $iCharPos ] ) ) { if( $blnOpenTag == true && $value[ $iCharPos ] != ">" ) { if( !isset($aPlaceholder[ $iPlaceholder ]) ) $aPlaceholder[ $iPlaceholder ] = ""; /** Initialisieren */ $aPlaceholder[ $iPlaceholder ] .= $value[ $iCharPos ]; } if( $value[ $iCharPos ] == "<" ) { $blnOpenTag = true; } elseif( $value[ $iCharPos ] == ">" ) { $blnOpenTag = false; ++$iPlaceholder; } ++$iCharPos; } /** Ersetzen */ if( count( $aPlaceholder ) > 0 ) { foreach( $aPlaceholder as $sPlaceholder ) { if( isset($aValueExcel[ $sPlaceholder ]) ) { if( is_arrays( $aValueExcel[ $sPlaceholder ] ) == true ) { /** Es ist ein Array. Hier werden alle Werte der Reihe nach in das Excel eingefügt und gegebenfalls neue Zeilen erstellt. */ $blnFirst = true; foreach( $aValueExcel[ $sPlaceholder ] as $rowValue ) { $charActual = $char; if( $blnFirst == false ) { /** Eine neue Zeile einfügen */ $oSheet->insertNewRowBefore( $row + 1, 1 ); ++$row; } else { $rowOriginal = $row; } foreach( $rowValue as $fieldValue ) { $oSheet->setCellValue( $charActual . $row, utf8_encode( $fieldValue ) ); $charActual = $chars[ arrays_search( $charActual, $chars ) + 1 ]; /** Einen Buchstaben weitergehen */ } /** Restliche Werte ausserhalb des Arrays vom Original übernehmen. */ foreach( $chars as $charKey => $charActual2 ) { if( $charKey < arrays_search( $charActual, $chars ) ) { /** Nur die Felder ausserhalb der Array-Daten von der Originalspalte übernehmen. */ continue; } /** Wert kopieren */ $valueCell = $oSheet->getCell( $charActual2 . $rowOriginal )->getValue(); if( $valueCell[0] == "=" ) { /** Es ist eine Formel. Korrigiere die Formel, sodass Sie fortlaufend ist. */ $valueCell = str_replace( $rowOriginal, $row, $valueCell ); } $oSheet->setCellValue( $charActual2 . $row, $valueCell ); } $blnFirst = false; } } else { /** Es ist kein Array */ $value = str_replace( "<". $sPlaceholder .">", $aValueExcel[ $sPlaceholder ], $value ); $oSheet->setCellValue( $char . $row, utf8_encode( $value ) ); } } else { /** Kein Wert für den Platzhalter gefunden. Wir blenden ihn aus, da wir davon ausgehen dass er nicht gebraucht wird */ $oSheet->setCellValue( $char . $row, "" ); } if( isset($aHiddenExcelRow[ $sPlaceholder ]) && $aHiddenExcelRow[ $sPlaceholder ] == true ) { /** Row unsichtbar machen */ $oSheet->getRowDimension( $row )->setVisible( false ); } elseif( isset($aHiddenExcel[ $sPlaceholder ]) && $aHiddenExcel[ $sPlaceholder ] == true ) { /** Verstecken */ $styleArray = arrays( 'font' => arrays( 'color' => arrays('rgb' => 'FFFFFF') )); $oSheet->getStyle( $char . $row )->applyFromArray($styleArray); } } } } } /** Unnötige Zeilen und Spalten ausblenden, damit keine leeren Seiten gedruckt werden. */ for( $row = 1; $row < 100; $row++ ) { foreach( $chars as $charKey => $char ) { if( $charKey > $lastFilledColumnIndex ) { $oSheet->getColumnDimension( $char )->setVisible(false); } } if( $row > $lastFilledRowIndex ) { $oSheet->getRowDimension( $row )->setVisible(false); } } $oSheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $oSheet->setBreak( $chars[ $lastFilledColumnIndex + 1 ] . "1" , PHPExcel_Worksheet::BREAK_COLUMN ); //$oSheet->setBreak( $lastFilledColumnIndex , PHPExcel_Worksheet::BREAK_ROW ); //$objPHPExcel->getActiveSheet()->setBreak( 'A20' , PHPExcel_Worksheet::BREAK_ROW ); /** Speichern */ $filename = time() ."_". str_replace('.php', '.xlsx', basename( $sPathTemplate )); $objWriter->save( $GLOBALS['DIR_KUNDEN'] ."/temp/". $filename ); return $filename; } $ objWriter = PHPExcel_IOFactory :: createWriter ($ objPHPExcel, 'Excel2007'); $aValueExcel = arrays(); $aHiddenExcel = arrays(); $aHiddenExcelRow = arrays(); define( "HideRow", "HideRow" ); define( "CreatePDF", "CreatePDF" ); define( "Hidden", "Hidden" ); /** Include PHPExcel_IOFactory */ require_once __DIR__ . '/../classs/PHPExcel/IOFactory.php'; /** Ersetzt die Variablen in Platzhaltern <variable> mit den Werten der Global Array-Variable $aValueExcel. * * Parameter: * $sFileTemplate Der Name des Excel-Files, das bearbeitet werden soll. * $sTitle Dummy Variable. * * Globale Variablen * $aValueExcel Die zu ersetzenden Werte als Assoziatives Array. * $aHiddenExcel Der Namen der Felder, die nicht angezeigt werden sollen. * $aHiddenExcelRow Der Namen der Felder, deren ganze Zeile nicht angezeigt werden soll. */ function CreateExcel( $sFileTemplate, $sTitle ) { global $aValueExcel; global $aHiddenExcel; global $aHiddenExcelRow; $sPathTemplate = $GLOBALS['DIR_KUNDEN'] ."/docs/templates/" . $sFileTemplate; if (!file_exists( $sPathTemplate )) { exit("File not Found"); } $objPHPExcel = PHPExcel_IOFactory::load( $sPathTemplate ); $objPHPExcel->setActiveSheetIndex(0); $oSheet = $objPHPExcel->getActiveSheet(); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); /** Wir definieren welche Spalten durchsucht werden sollen. Für eine Optimierte Geschwindigkeit, machen wir nur die Spalten von A bis Z. */ $chars = arrays(); $chars[] = "A"; $chars[] = "B"; $chars[] = "C"; $chars[] = "D"; $chars[] = "E"; $chars[] = "F"; $chars[] = "G"; $chars[] = "H"; $chars[] = "I"; $chars[] = "J"; $chars[] = "K"; $chars[] = "L"; $chars[] = "M"; $chars[] = "N"; $chars[] = "O"; $chars[] = "P"; /** Initialisieren. Die letzte nicht leere Spalte festhalten um die Dokumentgrösse richtig einstellen zu können. */ $lastFilledColumnIndex = 0; $lastFilledRowIndex = 0; for( $row = 1; $row < 100; $row++ ) { foreach( $chars as $char ) { $value = $oSheet->getCell( $char . $row )->getValue(); if( !$value ) continue; /** Leere Zeilen überspringen */ if( arrays_search( $char, $chars ) > $lastFilledColumnIndex ) { $lastFilledColumnIndex = arrays_search( $char, $chars ); } if( $row > $lastFilledRowIndex ) { $lastFilledRowIndex = $row; } $iCharPos = 0; $blnOpenTag = false; $aPlaceholder = arrays(); $iPlaceholder = 0; /** Nach Platzhalter suchen */ while( isset( $value[ $iCharPos ] ) ) { if( $blnOpenTag == true && $value[ $iCharPos ] != ">" ) { if( !isset($aPlaceholder[ $iPlaceholder ]) ) $aPlaceholder[ $iPlaceholder ] = ""; /** Initialisieren */ $aPlaceholder[ $iPlaceholder ] .= $value[ $iCharPos ]; } if( $value[ $iCharPos ] == "<" ) { $blnOpenTag = true; } elseif( $value[ $iCharPos ] == ">" ) { $blnOpenTag = false; ++$iPlaceholder; } ++$iCharPos; } /** Ersetzen */ if( count( $aPlaceholder ) > 0 ) { foreach( $aPlaceholder as $sPlaceholder ) { if( isset($aValueExcel[ $sPlaceholder ]) ) { if( is_arrays( $aValueExcel[ $sPlaceholder ] ) == true ) { /** Es ist ein Array. Hier werden alle Werte der Reihe nach in das Excel eingefügt und gegebenfalls neue Zeilen erstellt. */ $blnFirst = true; foreach( $aValueExcel[ $sPlaceholder ] as $rowValue ) { $charActual = $char; if( $blnFirst == false ) { /** Eine neue Zeile einfügen */ $oSheet->insertNewRowBefore( $row + 1, 1 ); ++$row; } else { $rowOriginal = $row; } foreach( $rowValue as $fieldValue ) { $oSheet->setCellValue( $charActual . $row, utf8_encode( $fieldValue ) ); $charActual = $chars[ arrays_search( $charActual, $chars ) + 1 ]; /** Einen Buchstaben weitergehen */ } /** Restliche Werte ausserhalb des Arrays vom Original übernehmen. */ foreach( $chars as $charKey => $charActual2 ) { if( $charKey < arrays_search( $charActual, $chars ) ) { /** Nur die Felder ausserhalb der Array-Daten von der Originalspalte übernehmen. */ continue; } /** Wert kopieren */ $valueCell = $oSheet->getCell( $charActual2 . $rowOriginal )->getValue(); if( $valueCell[0] == "=" ) { /** Es ist eine Formel. Korrigiere die Formel, sodass Sie fortlaufend ist. */ $valueCell = str_replace( $rowOriginal, $row, $valueCell ); } $oSheet->setCellValue( $charActual2 . $row, $valueCell ); } $blnFirst = false; } } else { /** Es ist kein Array */ $value = str_replace( "<". $sPlaceholder .">", $aValueExcel[ $sPlaceholder ], $value ); $oSheet->setCellValue( $char . $row, utf8_encode( $value ) ); } } else { /** Kein Wert für den Platzhalter gefunden. Wir blenden ihn aus, da wir davon ausgehen dass er nicht gebraucht wird */ $oSheet->setCellValue( $char . $row, "" ); } if( isset($aHiddenExcelRow[ $sPlaceholder ]) && $aHiddenExcelRow[ $sPlaceholder ] == true ) { /** Row unsichtbar machen */ $oSheet->getRowDimension( $row )->setVisible( false ); } elseif( isset($aHiddenExcel[ $sPlaceholder ]) && $aHiddenExcel[ $sPlaceholder ] == true ) { /** Verstecken */ $styleArray = arrays( 'font' => arrays( 'color' => arrays('rgb' => 'FFFFFF') )); $oSheet->getStyle( $char . $row )->applyFromArray($styleArray); } } } } } /** Unnötige Zeilen und Spalten ausblenden, damit keine leeren Seiten gedruckt werden. */ for( $row = 1; $row < 100; $row++ ) { foreach( $chars as $charKey => $char ) { if( $charKey > $lastFilledColumnIndex ) { $oSheet->getColumnDimension( $char )->setVisible(false); } } if( $row > $lastFilledRowIndex ) { $oSheet->getRowDimension( $row )->setVisible(false); } } $oSheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $oSheet->setBreak( $chars[ $lastFilledColumnIndex + 1 ] . "1" , PHPExcel_Worksheet::BREAK_COLUMN ); //$oSheet->setBreak( $lastFilledColumnIndex , PHPExcel_Worksheet::BREAK_ROW ); //$objPHPExcel->getActiveSheet()->setBreak( 'A20' , PHPExcel_Worksheet::BREAK_ROW ); /** Speichern */ $filename = time() ."_". str_replace('.php', '.xlsx', basename( $sPathTemplate )); $objWriter->save( $GLOBALS['DIR_KUNDEN'] ."/temp/". $filename ); return $filename; } $ styleArray = arrays ( $aValueExcel = arrays(); $aHiddenExcel = arrays(); $aHiddenExcelRow = arrays(); define( "HideRow", "HideRow" ); define( "CreatePDF", "CreatePDF" ); define( "Hidden", "Hidden" ); /** Include PHPExcel_IOFactory */ require_once __DIR__ . '/../classs/PHPExcel/IOFactory.php'; /** Ersetzt die Variablen in Platzhaltern <variable> mit den Werten der Global Array-Variable $aValueExcel. * * Parameter: * $sFileTemplate Der Name des Excel-Files, das bearbeitet werden soll. * $sTitle Dummy Variable. * * Globale Variablen * $aValueExcel Die zu ersetzenden Werte als Assoziatives Array. * $aHiddenExcel Der Namen der Felder, die nicht angezeigt werden sollen. * $aHiddenExcelRow Der Namen der Felder, deren ganze Zeile nicht angezeigt werden soll. */ function CreateExcel( $sFileTemplate, $sTitle ) { global $aValueExcel; global $aHiddenExcel; global $aHiddenExcelRow; $sPathTemplate = $GLOBALS['DIR_KUNDEN'] ."/docs/templates/" . $sFileTemplate; if (!file_exists( $sPathTemplate )) { exit("File not Found"); } $objPHPExcel = PHPExcel_IOFactory::load( $sPathTemplate ); $objPHPExcel->setActiveSheetIndex(0); $oSheet = $objPHPExcel->getActiveSheet(); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); /** Wir definieren welche Spalten durchsucht werden sollen. Für eine Optimierte Geschwindigkeit, machen wir nur die Spalten von A bis Z. */ $chars = arrays(); $chars[] = "A"; $chars[] = "B"; $chars[] = "C"; $chars[] = "D"; $chars[] = "E"; $chars[] = "F"; $chars[] = "G"; $chars[] = "H"; $chars[] = "I"; $chars[] = "J"; $chars[] = "K"; $chars[] = "L"; $chars[] = "M"; $chars[] = "N"; $chars[] = "O"; $chars[] = "P"; /** Initialisieren. Die letzte nicht leere Spalte festhalten um die Dokumentgrösse richtig einstellen zu können. */ $lastFilledColumnIndex = 0; $lastFilledRowIndex = 0; for( $row = 1; $row < 100; $row++ ) { foreach( $chars as $char ) { $value = $oSheet->getCell( $char . $row )->getValue(); if( !$value ) continue; /** Leere Zeilen überspringen */ if( arrays_search( $char, $chars ) > $lastFilledColumnIndex ) { $lastFilledColumnIndex = arrays_search( $char, $chars ); } if( $row > $lastFilledRowIndex ) { $lastFilledRowIndex = $row; } $iCharPos = 0; $blnOpenTag = false; $aPlaceholder = arrays(); $iPlaceholder = 0; /** Nach Platzhalter suchen */ while( isset( $value[ $iCharPos ] ) ) { if( $blnOpenTag == true && $value[ $iCharPos ] != ">" ) { if( !isset($aPlaceholder[ $iPlaceholder ]) ) $aPlaceholder[ $iPlaceholder ] = ""; /** Initialisieren */ $aPlaceholder[ $iPlaceholder ] .= $value[ $iCharPos ]; } if( $value[ $iCharPos ] == "<" ) { $blnOpenTag = true; } elseif( $value[ $iCharPos ] == ">" ) { $blnOpenTag = false; ++$iPlaceholder; } ++$iCharPos; } /** Ersetzen */ if( count( $aPlaceholder ) > 0 ) { foreach( $aPlaceholder as $sPlaceholder ) { if( isset($aValueExcel[ $sPlaceholder ]) ) { if( is_arrays( $aValueExcel[ $sPlaceholder ] ) == true ) { /** Es ist ein Array. Hier werden alle Werte der Reihe nach in das Excel eingefügt und gegebenfalls neue Zeilen erstellt. */ $blnFirst = true; foreach( $aValueExcel[ $sPlaceholder ] as $rowValue ) { $charActual = $char; if( $blnFirst == false ) { /** Eine neue Zeile einfügen */ $oSheet->insertNewRowBefore( $row + 1, 1 ); ++$row; } else { $rowOriginal = $row; } foreach( $rowValue as $fieldValue ) { $oSheet->setCellValue( $charActual . $row, utf8_encode( $fieldValue ) ); $charActual = $chars[ arrays_search( $charActual, $chars ) + 1 ]; /** Einen Buchstaben weitergehen */ } /** Restliche Werte ausserhalb des Arrays vom Original übernehmen. */ foreach( $chars as $charKey => $charActual2 ) { if( $charKey < arrays_search( $charActual, $chars ) ) { /** Nur die Felder ausserhalb der Array-Daten von der Originalspalte übernehmen. */ continue; } /** Wert kopieren */ $valueCell = $oSheet->getCell( $charActual2 . $rowOriginal )->getValue(); if( $valueCell[0] == "=" ) { /** Es ist eine Formel. Korrigiere die Formel, sodass Sie fortlaufend ist. */ $valueCell = str_replace( $rowOriginal, $row, $valueCell ); } $oSheet->setCellValue( $charActual2 . $row, $valueCell ); } $blnFirst = false; } } else { /** Es ist kein Array */ $value = str_replace( "<". $sPlaceholder .">", $aValueExcel[ $sPlaceholder ], $value ); $oSheet->setCellValue( $char . $row, utf8_encode( $value ) ); } } else { /** Kein Wert für den Platzhalter gefunden. Wir blenden ihn aus, da wir davon ausgehen dass er nicht gebraucht wird */ $oSheet->setCellValue( $char . $row, "" ); } if( isset($aHiddenExcelRow[ $sPlaceholder ]) && $aHiddenExcelRow[ $sPlaceholder ] == true ) { /** Row unsichtbar machen */ $oSheet->getRowDimension( $row )->setVisible( false ); } elseif( isset($aHiddenExcel[ $sPlaceholder ]) && $aHiddenExcel[ $sPlaceholder ] == true ) { /** Verstecken */ $styleArray = arrays( 'font' => arrays( 'color' => arrays('rgb' => 'FFFFFF') )); $oSheet->getStyle( $char . $row )->applyFromArray($styleArray); } } } } } /** Unnötige Zeilen und Spalten ausblenden, damit keine leeren Seiten gedruckt werden. */ for( $row = 1; $row < 100; $row++ ) { foreach( $chars as $charKey => $char ) { if( $charKey > $lastFilledColumnIndex ) { $oSheet->getColumnDimension( $char )->setVisible(false); } } if( $row > $lastFilledRowIndex ) { $oSheet->getRowDimension( $row )->setVisible(false); } } $oSheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $oSheet->setBreak( $chars[ $lastFilledColumnIndex + 1 ] . "1" , PHPExcel_Worksheet::BREAK_COLUMN ); //$oSheet->setBreak( $lastFilledColumnIndex , PHPExcel_Worksheet::BREAK_ROW ); //$objPHPExcel->getActiveSheet()->setBreak( 'A20' , PHPExcel_Worksheet::BREAK_ROW ); /** Speichern */ $filename = time() ."_". str_replace('.php', '.xlsx', basename( $sPathTemplate )); $objWriter->save( $GLOBALS['DIR_KUNDEN'] ."/temp/". $filename ); return $filename; }