PHP PHPExcel_Worksheet类代码示例

 /**
  * @return \PHPExcel
  */
 protected function mockWorkbook()
 {
     $workbook = new \PHPExcel();
     $workbook->disconnectWorksheets();
     $workbook->getProperties()->setTitle('mocked');
     $sheet = new \PHPExcel_Worksheet($workbook);
     $sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]);
     $workbook->addSheet($sheet);
     $sheet = new \PHPExcel_Worksheet($workbook);
     $sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]);
     $workbook->addSheet($sheet);
     $workbook->setActiveSheetIndex(0);
     return $workbook;
 }

 /**
  * @return \PHPExcel_Worksheet
  */
 protected function mockSheet()
 {
     $workbook = new \PHPExcel();
     $workbook->disconnectWorksheets();
     $sheet = new \PHPExcel_Worksheet($workbook);
     $sheet->setTitle('mocked');
     $sheet->fromArray([['a1', 'b1'], ['a2', 'b2']]);
     return $sheet;
 }

 /**
  * @return \PHPExcel
  */
 protected function mockRow()
 {
     $workbook = new \PHPExcel();
     $workbook->disconnectWorksheets();
     $sheet = new \PHPExcel_Worksheet($workbook);
     $sheet->fromArray([['a1', 'b1', 'c1']]);
     $row = new \PHPExcel_Worksheet_Row($sheet, 1);
     return $row;
 }

 /**
  * Convert the height of a cell from user's units to pixels. By interpolation
  * the relationship is: y = 4/3x. If the height hasn't been set by the user we
  * use the default value. If the row is hidden we use a value of zero.
  *
  * @param PHPExcel_Worksheet $sheet The sheet
  * @param integer $row The row index (1-based)
  * @return integer The width in pixels
  */
 public static function sizeRow($sheet, $row = 1)
 {
     // default font of the workbook
     $font = $sheet->getParent()->getDefaultStyle()->getFont();
     $rowDimensions = $sheet->getRowDimensions();
     // first find the true row height in pixels (uncollapsed and unhidden)
     if (isset($rowDimensions[$row]) and $rowDimensions[$row]->getRowHeight() != -1) {
         // then we have a row dimension
         $rowDimension = $rowDimensions[$row];
         $rowHeight = $rowDimension->getRowHeight();
         $pixelRowHeight = (int) ceil(4 * $rowHeight / 3);
         // here we assume Arial 10
     } elseif ($sheet->getDefaultRowDimension()->getRowHeight() != -1) {
         // then we have a default row dimension with explicit height
         $defaultRowDimension = $sheet->getDefaultRowDimension();
         $rowHeight = $defaultRowDimension->getRowHeight();
         $pixelRowHeight = PHPExcel_Shared_Drawing::pointsToPixels($rowHeight);
     } else {
         // we don't even have any default row dimension. Height depends on default font
         $pointRowHeight = PHPExcel_Shared_Font::getDefaultRowHeightByFont($font);
         $pixelRowHeight = PHPExcel_Shared_Font::fontSizeToPixels($pointRowHeight);
     }
     // now find the effective row height in pixels
     if (isset($rowDimensions[$row]) and !$rowDimensions[$row]->getVisible()) {
         $effectivePixelRowHeight = 0;
     } else {
         $effectivePixelRowHeight = $pixelRowHeight;
     }
     return $effectivePixelRowHeight;
 }

 protected function getCellValue(\PHPExcel_Worksheet $sheet, $coord)
 {
     $cell = $sheet->getCell($coord);
     if ($cell) {
         return $cell->getValue();
     }
 }

 /**
  * @param \PHPExcel_Worksheet $sheet
  */
 public function exportAttributeNames($sheet)
 {
     $row = 1;
     foreach ($this->_standardAttributes as $name => $standardAttribute) {
         $sheet->setCellValue($standardAttribute->column . $row, $name);
     }
 }

 /**
  * Write drawings to XML format
  *
  * @param 	PHPExcel_Worksheet				$pWorksheet
  * @return 	string 								XML Output
  * @throws 	Exception
  */
 public function writeDrawings(PHPExcel_Worksheet $pWorksheet = null)
 {
     // Create XML writer
     $objWriter = null;
     if ($this->getParentWriter()->getUseDiskCaching()) {
         $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK);
     } else {
         $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY);
     }
     // XML header
     $objWriter->startDocument('1.0', 'UTF-8', 'yes');
     // xdr:wsDr
     $objWriter->startElement('xdr:wsDr');
     $objWriter->writeAttribute('xmlns:xdr', 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing');
     $objWriter->writeAttribute('xmlns:a', 'http://schemas.openxmlformats.org/drawingml/2006/main');
     // Loop trough images and write drawings
     $i = 1;
     $iterator = $pWorksheet->getDrawingCollection()->getIterator();
     while ($iterator->valid()) {
         $this->_writeDrawing($objWriter, $iterator->current(), $i);
         $iterator->next();
         $i++;
     }
     $objWriter->endElement();
     // Return
     return $objWriter->getData();
 }

 /**
  * Create worksheet stringtable
  *
  * @param     PHPExcel_Worksheet     $pSheet                Worksheet
  * @param     string[]                 $pExistingTable     Existing table to eventually merge with
  * @return     string[]                 String table for worksheet
  * @throws     PHPExcel_Writer_Exception
  */
 public function createStringTable($pSheet = null, $pExistingTable = null)
 {
     if ($pSheet !== null) {
         // Create string lookup table
         $aStringTable = array();
         $cellCollection = null;
         $aFlippedStringTable = null;
         // For faster lookup
         // Is an existing table given?
         if ($pExistingTable !== null && is_array($pExistingTable)) {
             $aStringTable = $pExistingTable;
         }
         // Fill index array
         $aFlippedStringTable = $this->flipStringTable($aStringTable);
         // Loop through cells
         foreach ($pSheet->getCellCollection() as $cellID) {
             $cell = $pSheet->getCell($cellID);
             $cellValue = $cell->getValue();
             if (!is_object($cellValue) && $cellValue !== null && $cellValue !== '' && !isset($aFlippedStringTable[$cellValue]) && ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_STRING || $cell->getDataType() == PHPExcel_Cell_DataType::TYPE_STRING2 || $cell->getDataType() == PHPExcel_Cell_DataType::TYPE_NULL)) {
                 $aStringTable[] = $cellValue;
                 $aFlippedStringTable[$cellValue] = true;
             } elseif ($cellValue instanceof PHPExcel_RichText && $cellValue !== null && !isset($aFlippedStringTable[$cellValue->getHashCode()])) {
                 $aStringTable[] = $cellValue;
                 $aFlippedStringTable[$cellValue->getHashCode()] = true;
             }
         }
         return $aStringTable;
     } else {
         throw new PHPExcel_Writer_Exception("Invalid PHPExcel_Worksheet object passed.");
     }
 }

 /**
  * @param \PHPExcel_Worksheet $sheet
  * @param integer $offset
  * @param array $values
  */
 public function addLine($sheet, $offset, $values)
 {
     $column = 'A';
     foreach ($values as $value) {
         $cellCoordinates = $column++ . (string) $offset;
         $sheet->setCellValue($cellCoordinates, $value);
     }
 }

 protected function addSheet($name)
 {
     $sheet = new PHPExcel_Worksheet($this->excel, $name);
     $sheet->getSheetView()->setZoomScale(75);
     $this->excel->addSheet($sheet);
     $this->excel->setActiveSheetIndex($this->excel->getSheetCount() - 1);
     $this->sheet = $sheet;
 }

 protected function prepare(\PHPExcel_Worksheet $templateSheet)
 {
     $this->output = new \PHPExcel();
     $outputSheet = $this->output->getActiveSheet();
     $outputSheet->setTitle('Report');
     $this->templateSheet = $this->output->addExternalSheet($templateSheet);
     foreach ($this->templateSheet->getColumnDimensions() as $col => $columnDimension) {
         $outputSheet->getColumnDimension($col)->setWidth($columnDimension->getWidth());
     }
 }

/**
 * 行を完全コピーする
 *
 * http://blog.kotemaru.org/old/2012/04/06.html より
 * @param PHPExcel_Worksheet $sheet
 * @param int $srcRow
 * @param int $dstRow
 * @param int $height
 * @param int $width
 * @throws PHPExcel_Exception
 */
function copyRows(PHPExcel_Worksheet $sheet, $srcRow, $dstRow, $height, $width)
{
    for ($row = 0; $row < $height; $row++) {
        // セルの書式と値の複製
        for ($col = 0; $col < $width; $col++) {
            $cell = $sheet->getCellByColumnAndRow($col, $srcRow + $row);
            $style = $sheet->getStyleByColumnAndRow($col, $srcRow + $row);
            $dstCell = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($dstRow + $row);
            $sheet->setCellValue($dstCell, $cell->getValue());
            $sheet->duplicateStyle($style, $dstCell);
        }
        // 行の高さ複製。
        $h = $sheet->getRowDimension($srcRow + $row)->getRowHeight();
        $sheet->getRowDimension($dstRow + $row)->setRowHeight($h);
    }
    // セル結合の複製
    // - $mergeCell="AB12:AC15" 複製範囲の物だけ行を加算して復元。
    // - $merge="AB16:AC19"
    foreach ($sheet->getMergeCells() as $mergeCell) {
        $mc = explode(":", $mergeCell);
        $col_s = preg_replace("/[0-9]*/", "", $mc[0]);
        $col_e = preg_replace("/[0-9]*/", "", $mc[1]);
        $row_s = (int) preg_replace("/[A-Z]*/", "", $mc[0]) - $srcRow;
        $row_e = (int) preg_replace("/[A-Z]*/", "", $mc[1]) - $srcRow;
        // 複製先の行範囲なら。
        if (0 <= $row_s && $row_s < $height) {
            $merge = $col_s . (string) ($dstRow + $row_s) . ":" . $col_e . (string) ($dstRow + $row_e);
            $sheet->mergeCells($merge);
        }
    }
}

 public function __construct(\PHPExcel_Worksheet $worksheet, $parent_filename)
 {
     $this->name = $this->genName($parent_filename, $worksheet->getTitle());
     $row_it = $worksheet->getRowIterator();
     $this->header = new \excel2sql\SqlHeader($row_it->current());
     $this->entries = array();
     for ($row_it->next(); $row_it->valid(); $row_it->next()) {
         $rowindex = $row_it->current()->getRowIndex();
         $this->entries[] = new \excel2sql\SqlEntry($rowindex, $this->header, $worksheet);
     }
 }

 private function buildFooter()
 {
     $column_index = 0;
     foreach ($this->responseTableView->getFooterData() as $footer) {
         $column_name = Utility::getNameFromNumber($column_index++);
         $this->sheet->setCellValue($column_name . $this->row_index, $footer);
         $this->sheet->getStyle($column_name . $this->row_index)->getFont()->setBold(true);
     }
 }

 public function getNextDataset()
 {
     $dataset = new Dataset();
     for ($col = 0; $col < $this->highestColumnIndex; ++$col) {
         $cell = $this->worksheet->getCellByColumnAndRow($col, $this->rowPointer);
         $dataset->add($this->header[$col], $cell->getValue());
     }
     $this->rowPointer++;
     return $dataset;
 }