/**
* @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;
}
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;
}
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());
}
}
public function render($data)
{
$column = 'A';
foreach ($data as $value) {
$cellCoordinates = $column++ . $this->offset;
$this->sheet->setCellValue($cellCoordinates, $value);
}
$this->offset++;
return '';
}
/**
* 行を完全コピーする
*
* 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);
}
}
/**
* @param $columnNumber
* @param $columnIdentifier
* @param $type
*/
protected function doCellStyling($columnNumber, $columnIdentifier, $type)
{
$excelSettings = $this->getExcelSettingsByColumnIdentifier($columnIdentifier);
if (!is_array($excelSettings[$type])) {
return;
}
$settings = $excelSettings[$type];
if ($settings['dataType']) {
$this->activeSheet->getCellByColumnAndRow($columnNumber, $this->rowNumber)->setDataType($settings['dataType']);
}
if ($settings['wrapText']) {
$this->activeSheet->getStyleByColumnAndRow($columnNumber, $this->rowNumber)->getAlignment()->setWrapText($settings['wrapText']);
}
if ($settings['vertical']) {
$this->activeSheet->getStyleByColumnAndRow($columnNumber, $this->rowNumber)->getAlignment()->setVertical($settings['vertical']);
}
if ($settings['shrinkToFit']) {
$this->activeSheet->getStyleByColumnAndRow($columnNumber, $this->rowNumber)->getAlignment()->setShrinkToFit($settings['shrinkToFit']);
}
if ($type == 'body') {
if (!array_key_exists($columnIdentifier, $this->bodyCellStyleCache)) {
$this->bodyCellStyleCache[$columnIdentifier] = $this->buildStyleArray($settings);
}
$this->activeSheet->getStyleByColumnAndRow($columnNumber, $this->rowNumber)->applyFromArray($this->bodyCellStyleCache[$columnIdentifier]);
} else {
$this->activeSheet->getStyleByColumnAndRow($columnNumber, $this->rowNumber)->applyFromArray($this->buildStyleArray($settings));
}
}
/**
* Проверить не является ли строка заголовком, т.к. ТОРГ12 может содержать несколько заголовков
*
* @param int $rowNumber Номер строки
* @param array $currentRow Содержимое строки
* @return bool
*/
private function validateRow($rowNumber, $currentRow)
{
$row = [];
$key = 1;
for ($col = 0; $col <= $this->highestColumn; $col++) {
$currentCell = $this->normalizeCellValue($this->worksheet->getCellByColumnAndRow($col, $rowNumber)->getValue());
// запишем непустые значения в массив для текущей строки
if ($currentCell) {
$row[$key++] = $currentCell;
}
}
// пропускаем строку с номерами столбцов
if (count($row) > 2 && ($row[1] == 1 && $row[2] == 2 && $row[3] == 3)) {
return false;
}
// пропускаем строку без порядкового номера
if (!intval($currentRow['num'])) {
return false;
}
// пропускаем повторные заголовки (достаточно, если в двух столбцах будет заголовок)
if (in_array($currentRow['code'], $this->settingsRow['code']) || in_array($currentRow['num'], $this->settingsRow['num'])) {
return false;
}
return true;
}
/**
*
* @throws PhpExcelException
*/
private function mapHeaders()
{
$keys = array_keys($this->loweredFields);
$columns = array_fill_keys($keys, null);
$this->loweredFieldNameToExcelColumnMap = [];
$lastRow = $this->activeSheet->getHighestRow();
for ($i = $this->headerRow; $i <= $lastRow; $i++) {
foreach ($this->excelColumnsRange as $columnIndex) {
$value = $this->activeSheet->getCell($columnIndex . $i)->getCalculatedValue();
$text = $this->lowerHeaderCellText($value);
if (array_key_exists($text, $columns)) {
$columns[$text] = $columnIndex;
}
}
$this->loweredFieldNameToExcelColumnMap = array_filter($columns);
if (count($this->loweredFieldNameToExcelColumnMap) > 0) {
$this->firstDataRow = $i + 1;
break;
}
}
$missingColumns = array_diff_key($this->loweredToOriginalKeysMap, $this->loweredFieldNameToExcelColumnMap);
if (count($missingColumns) > 0) {
throw new PhpExcelException('Missing columns: ' . implode(', ', $missingColumns));
}
}
/**
* Set Hyperlink
*
* @param PHPExcel_Cell_Hyperlink $pHyperlink
* @throws Exception
* @return PHPExcel_Cell
*/
public function setHyperlink(PHPExcel_Cell_Hyperlink $pHyperlink = null)
{
if (!isset($this->_parent)) {
throw new Exception('Cannot set hyperlink for cell that is not bound to a worksheet');
}
$this->_parent->setHyperlink($this->getCoordinate(), $pHyperlink);
return $this->notifyCacheController();
}
protected function writeData(\PHPExcel_Worksheet $worksheet)
{
foreach ($this->activeDataProvider->getModels() as $row => $model) {
foreach ($this->columns as $col => $column) {
$columnIndex = \PHPExcel_Cell::stringFromColumnIndex($col) . ($row + 2);
switch ($column->format) {
case Column::FormatRaw:
$worksheet->setCellValue($columnIndex, $column->getValue($model));
break;
case Column::FormatUri:
$worksheet->setCellValue($columnIndex, $column->getValue($model));
$worksheet->getCell($columnIndex)->getHyperlink()->setUrl('"' . $column->getValue($model) . '"');
break;
}
}
}
}
/**
* format the sums row if there are any sums
* (borders and background color)
*/
protected function formatSums()
{
if (!$this->anySumsWereAdded) {
return;
}
$this->sheet->getStyle(self::excelRange(0, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr), count($this->activeColumns_arr) - 1, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr)))->applyFromArray(array('font' => array('bold' => 'true'), 'borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000')))));
$this->sheet->getStyle(self::excelRange(0, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr), count($this->activeColumns_arr) - 1, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr)))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFEEEEEE');
}
/**
* Set header row number
*
* @param integer $rowNumber Number of the row that contains column header names
*/
public function setHeaderRowNumber($rowNumber)
{
$rowNumber++;
$this->headerRowNumber = $rowNumber;
$res = $this->worksheet->rangeToArray(sprintf('A%d:%s%d', $rowNumber, $this->maxColumn, $rowNumber));
$this->setColumnHeaders(current($res));
$this->pointer = $rowNumber;
}
/**
*
* @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';
}
/**
*
*/
protected function _styleWorkSheet()
{
$highestColumn = $this->_mainSheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
for ($column = $this->_startCol; $column < $highestColumnIndex; $column++) {
$this->_mainSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column))->setAutoSize(true);
}
$this->_mainSheet->getColumnDimension('B')->setWidth(3);
}
private function buildBody()
{
$column_index = 0;
$row_index = $this->row_index;
$column_name = Utility::getNameFromNumber($column_index);
foreach ($this->analyze->getAlternativesNames() as $alternative_name) {
$this->sheet->setCellValue($column_name . $row_index, $alternative_name);
$row_index++;
}
$column_index = 1;
$column_name = Utility::getNameFromNumber($column_index);
foreach ($this->analyze->getData() as $alternatives) {
$row_index = $this->row_index;
foreach ($alternatives as $value) {
$this->sheet->setCellValue($column_name . $row_index++, $value);
}
$column_name = Utility::getNameFromNumber(++$column_index);
}
}
/**
*
* @param PHPExcel_Worksheet $sheet
*/
private function loadSheet($sheet)
{
$name = $sheet->getTitle();
$entity_name = "Entity_{$name}";
if (!class_exists($entity_name, true)) {
print "entity class not found. skip. {$entity_name}";
return;
}
// ヘッダ
$rowIterator = $sheet->getRowIterator();
if (!$rowIterator->valid()) {
print "no data. skip. {$entity_name}";
return;
}
$col = array();
$row = $rowIterator->current();
$cellIterator = $row->getCellIterator();
while ($cellIterator->valid()) {
$cell = $cellIterator->current();
$col[] = trim($cell->getValue());
$cellIterator->next();
}
$rowIterator->next();
print "load {$entity_name} .... ";
// データを登録
while ($rowIterator->valid()) {
$row = $rowIterator->current();
$entity = $this->c->getEntity($entity_name);
$cellIterator = $row->getCellIterator();
$i = 0;
while ($cellIterator->valid()) {
$cell = $cellIterator->current();
$prop = $col[$i];
$entity->{$prop} = trim($cell->getValue());
$cellIterator->next();
$i++;
}
$entity->insert();
$rowIterator->next();
}
print $sheet->getHighestRow() - 1 . " rows loaded.\n";
return;
}
/**
* Set the title of the current sheet.
* @param string $title Title of a sheet
* @return Export $this
*/
public function setNameOfSheet($title)
{
$title = str_replace(\PHPExcel_Worksheet::getInvalidCharacters(), '', $title);
if (strlen($title) >= 31) {
$title = substr($title, 0, 28);
$last_space = strrpos($title, " ");
$title = substr($title, 0, $last_space) . "...";
}
$this->currentSheet->setTitle($title);
return $this;
}
/**
* Populates the PHPExcel sheet with the headers from the result query
*
* @param SMWQueryResult $res The query result
*/
protected function populateDocumentWithHeaders(SMWQueryResult $res)
{
$this->colNum = 0;
foreach ($res->getPrintRequests() as $pr) {
$header = $pr->getLabel();
if ($this->showLabel($header)) {
$this->sheet->setCellValueByColumnAndRow($this->colNum, self::HEADER_ROW_OFFSET, $header)->getStyleByColumnAndRow($this->colNum, self::HEADER_ROW_OFFSET)->getFont()->setBold(true);
$this->colNum++;
}
}
}
private function _calculateTopTenValue($columnID, $startRow, $endRow, $ruleType, $ruleValue)
{
$range = $columnID . $startRow . ':' . $columnID . $endRow;
$dataValues = PHPExcel_Calculation_Functions::flattenArray($this->_workSheet->rangeToArray($range, null, true, false));
$dataValues = array_filter($dataValues);
if ($ruleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) {
rsort($dataValues);
} else {
sort($dataValues);
}
return array_pop(array_slice($dataValues, 0, $ruleValue));
}