Skip to content

Instantly share code, notes, and snippets.

@archy-bold
Created June 29, 2017 10:47
Show Gist options
  • Save archy-bold/a994f4ae332e5efe81862d42be458cb6 to your computer and use it in GitHub Desktop.
Save archy-bold/a994f4ae332e5efe81862d42be458cb6 to your computer and use it in GitHub Desktop.
<?php
class Php_ExcelWriter implements ExcelWriterInterface
{
protected $spreadsheet;
protected $writer;
protected $templateSheet = -1;
public function __construct()
{
$this->spreadsheet = new \ExcelBook('User', 'key', true);
}
public function setTemplate($path)
{
$this->spreadsheet->loadFile($path);
$this->currentSheet = $this->spreadsheet->getSheet(0);
}
public function setTemplateSheet($index)
{
$this->templateSheet = $index;
}
public function selectSheet($index)
{
$this->currentSheet = $this->spreadsheet->getSheet($index);
$this->spreadsheet->activeSheet($index);
}
public function setCreator($creator)
{
//TODO
// $properties = $this->spreadsheet->getProperties();
// $properties->setCreator($creator);
// $properties->setLastModifiedBy("Maarten Balliauw");
// $properties->setTitle("Office 2007 XLSX Test Document");
// $properties->setSubject("Office 2007 XLSX Test Document");
// $properties->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
// $properties->setKeywords("office 2007 openxml php");
// $properties->setCategory("Test result file");
}
public function setCellValue($cell, $value)
{
$pos = $this->currentSheet->addrToRowCol($cell);
$this->currentSheet->write($pos['row'], $pos['column'], $value);
}
public function removeSheet($index)
{
$this->spreadsheet->deleteSheet($index);
}
public function addImage($path, $cell, $name, $height, $width)
{
$imageId = $this->spreadsheet->addPictureFromFile($path);
$pos = $this->currentSheet->addrToRowCol($cell);
$this->currentSheet->addPictureDim($pos['row'], $pos['column'], $imageId, $width, $height);
}
public function createNewFromTemplate()
{
if ($this->templateSheet > -1){
$this->currentSheet = $this->spreadsheet->copySheet('', $this->templateSheet);
$sheetCount = $this->spreadsheet->sheetCount();
$this->spreadsheet->insertSheet($sheetCount, '', $this->currentSheet);
}
else{
$this->spreadsheet->addSheet('');
}
}
public function insertRowsBefore($row, $number = 1)
{
$firstRow = $row - 1;
$lastRow = $number + $row - 2;
$retval = $this->currentSheet->insertRow($firstRow, $lastRow);
$formats = array();
for ($i = 0; $i < 20; $i++) {
$formats[] = $this->currentSheet->cellFormat($firstRow - 1, $i);
}
for ($i = $firstRow; $i < $lastRow + 1; $i++){
for ($j = 0; $j < 20; $j++) {
$this->currentSheet->setCellFormat($i, $j, $formats[$j]);
}
}
}
public function fromArray($data, $cell)
{
$pos = $this->currentSheet->addrToRowCol($cell);
$length = count($data);
for ($i = 0; $i < $length; $i++) {
$row = $data[$i];
if ($pos['column'] > 0){
for ($j = 0; $j < $pos['column']; $j++) {
array_unshift($row, '');
}
}
$this->currentSheet->writeRow($pos['row'] + $i, $row);
}
}
public function setCellsCurrency($cells, $currencySymbol = null, $before = true)
{
// Setup a new format
$formatCode = '#,##0.00';
if (!is_null($currencySymbol)){
if ($before){
$formatCode = $currencySymbol . $formatCode;
}
else{
$formatCode .= ' ' . $currencySymbol;
}
}
// Create the object
$formatId = $this->spreadsheet->addCustomFormat($formatCode);
if (strpos($cells, ':') !== false){
$cellRange = explode(':', $cells);
$firstRange = $this->currentSheet->addrToRowCol($cellRange[0]);
$secondRange = $this->currentSheet->addrToRowCol($cellRange[1]);
// So we don't lose formatting, base this on the format of the first cell.
$format = $this->currentSheet->cellFormat($firstRange['row'], $firstRange['column']);
$format->numberFormat($formatId);
// Loop through the range.
for ($i = $firstRange['row']; $i < $secondRange['row'] + 1; $i++) {
$this->currentSheet->setCellFormat($i, $firstRange['column'], $format);
}
}
else{
$pos = $this->currentSheet->addrToRowCol($cells);
$format = $this->currentSheet->cellFormat($pos['row'], $pos['column']);
$format->numberFormat($formatId);
$this->currentSheet->setCellFormat($pos['row'], $pos['column'], $format);
}
}
public function setCellsPercent($cells)
{
if (strpos($cells, ':') !== false){
$cellRange = explode(':', $cells);
$firstRange = $this->currentSheet->addrToRowCol($cellRange[0]);
$secondRange = $this->currentSheet->addrToRowCol($cellRange[1]);
// So we don't lose formatting, base this on the format of the first cell.
$format = $this->currentSheet->cellFormat($firstRange['row'], $firstRange['column']);
$format->numberFormat(\ExcelFormat::NUMFORMAT_PERCENT);
// Loop through the range.
for ($i = $firstRange['row']; $i < $secondRange['row'] + 1; $i++) {
$this->currentSheet->setCellFormat($i, $firstRange['column'], $format);
}
}
else{
$pos = $this->currentSheet->addrToRowCol($cellRange[0]);
$format = $this->currentSheet->cellFormat($pos['row'], $pos['column']);
$format->numberFormat($formatId);
$this->currentSheet->setCellFormat($pos['row'], $pos['column'], $format);
}
}
public function setSheetTitle($title)
{
$this->currentSheet->setName($title);
}
public function writeFile($fullpath)
{
// Delete the template sheet
$this->spreadsheet->deleteSheet($this->templateSheet);
$this->spreadsheet->save($fullpath);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment