PHP PHPExcel::getSheet方法代码示例

 /**
  * @param $fromRow
  * @throws \PHPExcel_Exception
  */
 private function loadData($fromRow)
 {
     $sheet = $this->phpExcel->getSheet(0);
     $highestRow = $sheet->getHighestRow();
     $highestColumn = $sheet->getHighestColumn();
     for ($row = $fromRow; $row <= $highestRow; $row++) {
         $data = $sheet->rangeToArray("A{$row}:{$highestColumn}" . $row, NULL, TRUE, FALSE);
         $this->data[] = $data[0];
     }
 }

 /**
  * Save PHPExcel to file
  *
  * @param 	string 		$pFileName
  * @throws 	Exception
  */
 public function save($pFilename = null)
 {
     // Fetch sheet
     $sheet = $this->_phpExcel->getSheet($this->_sheetIndex);
     // Open file
     $fileHandle = fopen($pFilename, 'w');
     if ($fileHandle === false) {
         throw new Exception("Could not open file {$pFilename} for writing.");
     }
     // Get cell collection
     $cellCollection = $sheet->getCellCollection();
     // Get column count
     $colCount = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
     // Write headers
     $this->_writeHTMLHeader($fileHandle);
     $this->_writeStyles($fileHandle, $sheet);
     $this->_writeTableHeader($fileHandle);
     // Loop trough cells
     $currentRow = -1;
     $rowData = array();
     foreach ($cellCollection as $cell) {
         if ($currentRow != $cell->getRow()) {
             // End previous row?
             if ($currentRow != -1) {
                 $this->_writeRow($fileHandle, $sheet, $rowData, $currentRow - 1);
             }
             // Set current row
             $currentRow = $cell->getRow();
             // Start a new row
             $rowData = array();
             for ($i = 0; $i < $colCount; $i++) {
                 $rowData[$i] = '';
             }
         }
         // Copy cell
         $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
         $rowData[$column] = $cell;
     }
     // End last row?
     if ($currentRow != -1) {
         $this->_writeRow($fileHandle, $sheet, $rowData, $currentRow - 1);
     }
     // Write footers
     $this->_writeTableFooter($fileHandle);
     $this->_writeHTMLFooter($fileHandle);
     // Close file
     fclose($fileHandle);
 }

 /**
  * Save PHPExcel to file
  *
  * @param 	string 		$pFileName
  * @throws 	Exception
  */
 public function save($pFilename = null)
 {
     // Fetch sheet
     $sheet = $this->_phpExcel->getSheet($this->_sheetIndex);
     // Open file
     $fileHandle = fopen($pFilename, 'w');
     if ($fileHandle === false) {
         throw new Exception("Could not open file {$pFilename} for writing.");
     }
     // Get cell collection
     $cellCollection = $sheet->getCellCollection();
     // Get column count
     $colCount = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
     // Loop trough cells
     $currentRow = -1;
     $rowData = array();
     foreach ($cellCollection as $cell) {
         if ($currentRow != $cell->getRow()) {
             // End previous row?
             if ($currentRow != -1) {
                 $this->_writeLine($fileHandle, $rowData);
             }
             // Set current row
             $currentRow = $cell->getRow();
             // Start a new row
             $rowData = array();
             for ($i = 0; $i < $colCount; $i++) {
                 $rowData[$i] = '';
             }
         }
         // Copy cell
         $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
         if ($cell->getValue() instanceof PHPExcel_RichText) {
             $rowData[$column] = $cell->getValue()->getPlainText();
         } else {
             if ($this->_preCalculateFormulas) {
                 $rowData[$column] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $sheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
             } else {
                 $rowData[$column] = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $sheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
             }
         }
     }
     // End last row?
     if ($currentRow != -1) {
         $this->_writeLine($fileHandle, $rowData);
     }
     // Close file
     fclose($fileHandle);
 }

 /**
  * Save PHPExcel to file
  *
  * @param 	string 		$pFileName
  * @throws 	Exception
  */
 public function save($pFilename = null)
 {
     if (!is_null($this->_spreadSheet)) {
         // Create new ZIP file and open it for writing
         $objZip = new ZipArchive();
         // Try opening the ZIP file
         if ($objZip->open($pFilename, ZIPARCHIVE::OVERWRITE) !== true) {
             throw new Exception("Could not open " . $pFilename . " for writing.");
         }
         // Add media
         for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); $i++) {
             for ($j = 0; $j < $this->_spreadSheet->getSheet($i)->getDrawingCollection()->count(); $j++) {
                 if ($this->_spreadSheet->getSheet($i)->getDrawingCollection()->offsetGet($j) instanceof PHPExcel_Worksheet_BaseDrawing) {
                     $imgTemp = $this->_spreadSheet->getSheet($i)->getDrawingCollection()->offsetGet($j);
                     $objZip->addFromString('media/' . $imgTemp->getFilename(), file_get_contents($imgTemp->getPath()));
                 }
             }
         }
         // Add phpexcel.xml to the document, which represents a PHP serialized PHPExcel object
         $objZip->addFromString('phpexcel.xml', $this->_writeSerialized($this->_spreadSheet, $pFilename));
         // Close file
         if ($objZip->close() === false) {
             throw new Exception("Could not close zip file {$pFilename}.");
         }
     } else {
         throw new Exception("PHPExcel object unassigned.");
     }
 }

 private function setData(\PHPExcel $ea, $data)
 {
     //Set up the header
     $ews = $ea->getSheet(0);
     $ews->setTitle('Data');
     $ews->setCellValue('a1', 'ID');
     $ews->setCellValue('b1', 'Season');
     $ews->setCellValue('c1', 'Teams');
     $ews->setCellValue('d1', 'Self Score');
     $ews->setCellValue('e1', 'Opponent Score');
     $ews->setCellValue('f1', 'Date Played');
     $ews->setCellValue('g1', 'Win/Lose');
     $ews->setCellValue('h1', 'Remark');
     //Fill data
     $ews->fromArray($data, ' ', 'A2');
     //Apply header some styles
     $header = 'a1:h1';
     $ews->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('00ffff00');
     $style = array('font' => array('bold' => true), 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
     $ews->getStyle($header)->applyFromArray($style);
     for ($col = ord('a'); $col <= ord('h'); $col++) {
         $ews->getColumnDimension(chr($col))->setAutoSize(true);
     }
     return $ews;
 }

 /**
  * Save PHPExcel to file
  *
  * @param 	string 		$pFileName
  * @throws 	Exception
  */
 public function save($pFilename = null)
 {
     // Fetch sheet
     $sheet = $this->_phpExcel->getSheet($this->_sheetIndex);
     $saveDebugLog = PHPExcel_Calculation::getInstance()->writeDebugLog;
     PHPExcel_Calculation::getInstance()->writeDebugLog = false;
     $saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType();
     PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE);
     // Open file
     $fileHandle = fopen($pFilename, 'w');
     if ($fileHandle === false) {
         throw new Exception("Could not open file {$pFilename} for writing.");
     }
     if ($this->_useBOM) {
         // Write the UTF-8 BOM code
         fwrite($fileHandle, "");
     }
     // Convert sheet to array
     $cellsArray = $sheet->toArray('', $this->_preCalculateFormulas);
     // Write rows to file
     foreach ($cellsArray as $row) {
         $this->_writeLine($fileHandle, $row);
     }
     // Close file
     fclose($fileHandle);
     PHPExcel_Calculation::setArrayReturnType($saveArrayReturnType);
     PHPExcel_Calculation::getInstance()->writeDebugLog = $saveDebugLog;
 }

 public function tablaDescarga()
 {
     if (isset($_POST['miHtml'])) {
         $htmlEntrada = $_POST['miHtml'];
         $titulo = $_POST['titulo'];
         $str = $htmlEntrada;
         $table = str_get_html($str);
         $rowData = array();
         foreach ($table->find('tr') as $row) {
             // initialize array to store the cell data from each row
             $flight = array();
             foreach ($row->find('td') as $cell) {
                 // push the cell's text to the array
                 $flight[] = trim($cell->plaintext);
             }
             $rowData[] = $flight;
         }
         error_reporting(E_ALL);
         ini_set('display_errors', TRUE);
         ini_set('display_startup_errors', TRUE);
         // date_default_timezone_set('Europe/London');
         if (PHP_SAPI == 'cli') {
             die('This example should only be run from a Web Browser');
         }
         $objPHPExcel = new PHPExcel();
         $objPHPExcel->getProperties()->setCreator(label('nombreSistema'))->setLastModifiedBy(label('nombreSistema'));
         // ->setSubject("Prueba de descarga de tabla de personas")
         // ->setDescription("Documento de prueba de descarga de tabla de excel desde PHP")
         // ->setKeywords("office 2007 openxml php")
         // ->setCategory("Documento de prueba");
         $hoja = $objPHPExcel->getSheet(0);
         $hoja->setTitle($titulo);
         $hoja->fromArray($rowData, '', 'A1');
         $cantidadColumnas = count(array_shift($rowData));
         $abcd = array('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's');
         $ultimaColumna = $abcd[$cantidadColumnas - 1];
         $header = 'a1:' . $ultimaColumna . '1';
         $hoja->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFFFFF00');
         $style = array('font' => array('bold' => true), 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
         $hoja->getStyle($header)->applyFromArray($style);
         for ($col = ord('a'); $col <= ord('z'); $col++) {
             $hoja->getColumnDimension(chr($col))->setAutoSize(true);
         }
         header('Content-Type: application/vnd.ms-excel');
         header('Content-Disposition: attachment;filename="' . $titulo . '.xls"');
         header('Cache-Control: max-age=0');
         header('Cache-Control: max-age=1');
         // header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
         header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
         // always modified
         header('Cache-Control: cache, must-revalidate');
         // HTTP/1.1
         header('Pragma: public');
         // HTTP/1.0
         $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
         $objWriter->save('php://output');
         exit;
     }
 }

/**
 * 
 * @param PHPExcel_Worksheet $hoja
 * @param int $pk representa el índice de la columna en el archivo de excel que está asociada con la llave primaria de la tabla
 * @return string rerpesenta el nombre la ruta del archivo creado. Si no se pudo crear el archivo se regresa otra cosa :p
 */
function prepararArchivo($hoja, $pk = false, $incluirPrimeraFila = false)
{
    $objetoExcel = new PHPExcel();
    $hojaInsertar = $objetoExcel->getSheet(0);
    $hojaInsertar->setTitle('Insertar');
    if ($objetoExcel->getSheetCount() > 1) {
        $hojaActualizar = $objetoExcel->getSheet(1);
        $hojaActualizar->setTitle('Actualizar');
    } else {
        $hojaActualizar = new PHPExcel_Worksheet();
        $hojaActualizar->setTitle('Actualizar');
        $objetoExcel->addSheet($hojaActualizar);
    }
    $rango = $hoja->calculateWorksheetDataDimension();
    if (!$incluirPrimeraFila) {
        $rango[1] = '2';
    }
    $contenidoExcel = $hoja->rangeToArray($rango);
    $datos = array();
    $datos['insertar'] = array();
    $datos['actualizar'] = array();
    if ($pk) {
        $db = new DbConnection();
        $db->abrirConexion();
        $llavePrimaria = $_SESSION['pk'];
        foreach ($contenidoExcel as $fila) {
            $existe = $db->existeRegistro($_SESSION['tabla'], $llavePrimaria, $fila[$pk]);
            if ($existe) {
                $datos['actualizar'][] = $fila;
            } else {
                $datos['insertar'][] = $fila;
            }
        }
        $db->cerrarConexion();
    } else {
        foreach ($contenidoExcel as $fila) {
            $datos['insertar'][] = $fila;
        }
    }
    $hojaInsertar->fromArray($datos['insertar'], null, 'A1', true);
    $hojaActualizar->fromArray($datos['actualizar'], null, 'A1', true);
    $escritorExcel = PHPExcel_IOFactory::createWriter($objetoExcel, 'Excel2007');
    $escritorExcel->save('excelTmp/tmp_import_upload.xlsx');
    return 'excelTmp/tmp_import_upload.xlsx';
}

 /**
  * Write data to a row
  *
  * @param array $data
  * @param bool  $finalize
  * @param null  $sheetIndex
  * @throws \Exception
  */
 public function writeRow(array $data, $finalize = true, $sheetIndex = null)
 {
     // get the sheet to write in
     if (null === $sheetIndex) {
         $sheet = $this->file->getActiveSheet();
     } else {
         $sheet = $this->file->getSheet($sheetIndex);
         $this->file->setActiveSheetIndex($sheetIndex);
     }
     foreach ($data as $value) {
         $coordinate = sprintf('%s%d', $this->currentColumn, $this->currentRow);
         $sheet->setCellValue($coordinate, $value);
         $this->nextColumn();
     }
     if ($finalize) {
         $this->nextRow();
     }
 }

 /**
  * Get the sheet by id or name, else get the active sheet
  *
  * @param callable|integer|string $sheetID
  *
  * @return \PHPExcel_Worksheet
  */
 protected function getSheetByIdOrName($sheetID)
 {
     // If is a string, return the sheet by name
     if (is_string($sheetID)) {
         return $this->excel->getSheetByName($sheetID);
     }
     // Else it should be the sheet index
     return $this->excel->getSheet($sheetID);
 }

 /**
  * Save PHPExcel to file
  *
  * @param 	string 		$pFileName
  * @throws 	Exception
  */
 public function save($pFilename = null)
 {
     // Fetch sheet
     $sheet = $this->_phpExcel->getSheet($this->_sheetIndex);
     // Open file
     $fileHandle = fopen($pFilename, 'w');
     if ($fileHandle === false) {
         throw new Exception("Could not open file {$pFilename} for writing.");
     }
     // Get cell collection
     $cellCollection = $sheet->getCellCollection();
     // Write headers
     $this->_writeHTMLHeader($fileHandle);
     $this->_writeStyles($fileHandle, $sheet);
     $this->_writeTableHeader($fileHandle);
     // Get worksheet dimension
     $dimension = explode(':', $sheet->calculateWorksheetDimension());
     $dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]);
     $dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1;
     $dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]);
     $dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1;
     // Loop trough cells
     $rowData = null;
     for ($row = $dimension[0][1]; $row <= $dimension[1][1]; $row++) {
         // Start a new row
         $rowData = array();
         // Loop trough columns
         for ($column = $dimension[0][0]; $column <= $dimension[1][0]; $column++) {
             // Cell exists?
             if ($sheet->cellExistsByColumnAndRow($column, $row)) {
                 $rowData[$column] = $sheet->getCellByColumnAndRow($column, $row);
             } else {
                 $rowData[$column] = '';
             }
         }
         // Write row
         $this->_writeRow($fileHandle, $sheet, $rowData, $row - 1);
     }
     // Write footers
     $this->_writeTableFooter($fileHandle);
     $this->_writeHTMLFooter($fileHandle);
     // Close file
     fclose($fileHandle);
 }

		public function save($pFilename = null) {
			// Fetch sheet
			$sheet = $this->_phpExcel->getSheet($this->_sheetIndex);
	
			$saveDebugLog = PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->getWriteDebugLog();
			PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog(FALSE);
			$saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType();
			PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE);
	
			// Open file
			$fileHandle = fopen($pFilename, 'wb+');
			if ($fileHandle === false) {
				throw new PHPExcel_Writer_Exception("Could not open file $pFilename for writing.");
			}
	
			if ($this->_excelCompatibility) {
				fwrite($fileHandle, "\xEF\xBB\xBF");	//	Enforce UTF-8 BOM Header
				$this->setEnclosure('"');				//	Set enclosure to "
				$this->setDelimiter(";");			    //	Set delimiter to a semi-colon
	            $this->setLineEnding("\r\n");
				fwrite($fileHandle, 'sep=' . $this->getDelimiter() . $this->_lineEnding);
			} elseif ($this->_useBOM) {
				// Write the UTF-8 BOM code if required
				fwrite($fileHandle, "\xEF\xBB\xBF");
			}
	
			//	Identify the range that we need to extract from the worksheet
			$maxCol = $sheet->getHighestDataColumn();
			$maxRow = $sheet->getHighestDataRow();
	
			// Write rows to file
			for($row = 1; $row <= $maxRow; ++$row) {
				// Convert the row to an array...
				$cellsArray = $sheet->rangeToArray('A'.$row.':'.$maxCol.$row,'', $this->_preCalculateFormulas);
				// ... and write to the file
				$this->_writeLine($fileHandle, $cellsArray[0]);
			}
	
			// Close file
			fclose($fileHandle);
	
			PHPExcel_Calculation::setArrayReturnType($saveArrayReturnType);
			PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog);
		}

 /**
  * Returns a PHPExcel_Worksheet object if it the sheet index
  * exists, false otherwise
  * @param $index
  * @return bool|\PHPExcel_Worksheet
  */
 private function getSheetByIndex($index)
 {
     try {
         $this->worksheet = $this->workbook->getSheet($index);
     } catch (\PHPExcel_Exception $e) {
         //sheet out of bounds
         return false;
     }
     return $this->worksheet;
 }

 /**
  * TODO: Write record to XLS.
  * @param Array $fields The elements of this array will be written into a line
  * of the current XLS file.
  */
 public function putRecord($fields)
 {
     foreach ($fields as $colNr => $field) {
         if (empty($this->columnWidths[$colNr]) || $this->columnWidths[$colNr] < strlen($field)) {
             $this->columnWidths[$colNr] = strlen($field);
             $this->phpExcelObj->getSheet($this->sheetNr)->getColumnDimensionByColumn($colNr)->setWidth($this->columnWidths[$colNr]);
         }
         $this->phpExcelObj->getSheet($this->sheetNr)->setCellValueByColumnAndRow($colNr, $this->nextRowNr, $field);
     }
     $this->nextRowNr++;
 }

function excel_writer($row_data_excel)
{
    $objPHPEXCEL = new PHPExcel();
    $row_len = count($row_data_excel);
    $row_count = 0;
    $myWorkSheet = new PHPExcel_WorkSheet($objPHPEXCEL, "班級");
    $objPHPEXCEL->addSheet($myWorkSheet, 0);
    $objPHPEXCEL->getSheet(0)->setCellValueByColumnAndRow(0, 1, "班級");
    $objPHPEXCEL->getSheet(0)->getColumnDimension('A')->setWidth(12);
    $column = 2;
    while ($row_count < $row_len) {
        $objPHPEXCEL->getSheet(0)->setCellValueByColumnAndRow(0, $column, $row_data_excel[$row_count]["class"]);
        $row_count++;
        $column++;
    }
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPEXCEL, 'Excel5');
    $objWriter->save("/var/www/sports/67/admin/excel/class.xls");
    $objPHPEXCEL->disconnectWorksheets();
    unset($objPHPEXCEL);
    header("Location: http://dpo.nttu.edu.tw/sports/67/admin/excel/class.xls");
}