Page MenuHomePhabricator

Support custom fields in 'Export to Excel'
Closed, ResolvedPublic

Subscribers
Tokens
"Like" token, awarded by clemvangelis."Like" token, awarded by franjesus."Like" token, awarded by rbalik."Like" token, awarded by stevex."Like" token, awarded by carlos.prado.vsys."Like" token, awarded by rftfaria."Like" token, awarded by Andrzej.Pasterczyk."Like" token, awarded by NLDK."Like" token, awarded by r0ots."Like" token, awarded by witrin."Like" token, awarded by lschabel.
Assigned To
Authored By
brenters, Jun 17 2014

Description

When exporting a set of Maniphest tasks to Excel, any custom fields added in maniphest.custom-field-definitions should also be included.

Event Timeline

brenters updated the task description. (Show Details)
brenters raised the priority of this task from to Normal.
brenters added a project: Maniphest.
brenters added a subscriber: brenters.

This would be a really useful feature.

chad added a subscriber: kefayati.Aug 23 2014, 3:07 PM

◀ Merged tasks: T5950.

timor added a subscriber: timor.Oct 6 2014, 2:08 PM
timor added a comment.Oct 6 2014, 2:10 PM

Without this, the excel export feature is unfortunately of no use for us here, since we need the information in custom fields for reporting purposes.
Specifically this concerns estimation and due date related information.

witrin awarded a token.Oct 6 2014, 2:40 PM
witrin added a subscriber: witrin.
witrin added a comment.Oct 6 2014, 2:45 PM

Could anybody give me a short code-snippet which shows how to get the value of a custom-field of a task? So I'm able to implement my own export-format in the meanwhile.

maniphest.info from conduit will give you a bunch of json including

"auxiliary"          : {
  "std:maniphest:mycorp:agile-buzzword" : "mycorp:story"
},

See the conduit docs for a little more info on how to use the api to build your own thing (which you will then have to support).

witrin added a comment.Oct 6 2014, 4:40 PM

maniphest.info from conduit will give you a bunch of json including

"auxiliary"          : {
  "std:maniphest:mycorp:agile-buzzword" : "mycorp:story"
},

See the conduit docs for a little more info on how to use the api to build your own thing (which you will then have to support).

Sorry for this misunderstanding. I meant the PHP API to get the stored value of a custom-field of a task, assuming I've already the task object fetched. To be more precisely I'm talking about how to use the class ManiphestCustomField.

Problem solved:

$custom_fields = PhabricatorCustomField::getObjectFields(
        $task,
        PhabricatorCustomField::ROLE_VIEW);
$custom_fields
        ->setViewer($user)
        ->readFieldsFromStorage($task);

$custom_fields->getFields()['std:maniphest:company:field']->getValueForStorage();
OCram added a subscriber: OCram.Mar 6 2015, 11:45 AM
r0ots awarded a token.May 11 2015, 1:25 PM
r0ots added a subscriber: r0ots.
angie added a subscriber: angie.Jan 4 2016, 7:25 PM
NLDK awarded a token.Feb 8 2016, 3:10 PM
NLDK added a subscriber: NLDK.
StuC added a subscriber: StuC.Feb 26 2016, 11:11 AM

is T5954 really a fully blocking Task of this feature? Forgive my ignorance as I haven't worked on or even looked at the codebase, but it seems that adding all task fields as additional columns to an Excel download would be relatively trivial?

If you don't want them they are easily deleted afterwards.

The API shows them as under an "auxiliary" key. Which would make it rather simple for us to do it from there, but it leaves us having to create a small App alongside Phabricator, specifically for Project managers to produce queries to download CSV/Excel from. Which isn't as tidy an implementation.

is T5954 really a fully blocking Task of this feature?

Yes.

When we could build a feature today, but know we would have to undo that implementation after planned work tomorrow, we wait until tomorrow.

You're welcome to do the work today locally by forking, if you believe the value of having those columns between now and the time we complete T5954 exceeds the cost of the work in your situation.

In T5391#78626, @witrin wrote:

Problem solved:

$custom_fields = PhabricatorCustomField::getObjectFields(
        $task,
        PhabricatorCustomField::ROLE_VIEW);
$custom_fields
        ->setViewer($user)
        ->readFieldsFromStorage($task);
$custom_fields->getFields()['std:maniphest:company:field']->getValueForStorage();

Where u put that code (Filename) ?

I was almost a week using Phabricator i'm a totally newbie

@camilorojas I think I just added the following file

src/applications/maniphest/export/ManiphestExcelCustomFormat.php
<?php

final class ManiphestExcelCustomFormat extends ManiphestExcelFormat {

  public function getName() {
    return pht('Custom');
  }

  public function getFileName() {
    return 'maniphest_tasks_'.date('Ymd');
  }

  /**
   * @phutil-external-symbol class PHPExcel
   * @phutil-external-symbol class PHPExcel_IOFactory
   * @phutil-external-symbol class PHPExcel_Style_NumberFormat
   * @phutil-external-symbol class PHPExcel_Cell_DataType
   */
  public function buildWorkbook(
    PHPExcel $workbook,
    array $tasks,
    array $handles,
    PhabricatorUser $user) {

    $sheet = $workbook->setActiveSheetIndex(0);
    $sheet->setTitle(pht('Tasks'));

    $widths = array(
      null,
      15,
      null,
      10,
      15,
      15,
      60,
      30,
      20,
      100,
    );

    foreach ($widths as $col => $width) {
      if ($width !== null) {
        $sheet->getColumnDimension($this->col($col))->setWidth($width);
      }
    }

    $status_map = ManiphestTaskStatus::getTaskStatusMap();
    $pri_map = ManiphestTaskPriority::getTaskPriorityMap();

    $date_format = null;

    $rows = array();
    $rows[] = array(
      pht('ID'),
      pht('Owner'),
      pht('Status'),
      pht('Priority'),
      pht('Date Created'),
      pht('Date Updated'),
      pht('Title'),
      pht('Projects'),
      pht('URI'),
      pht('Description'),
      pht('Custom Field'),
    );

    $is_date = array(
      false,
      false,
      false,
      false,
      true,
      true,
      false,
      false,
      false,
      false,
      false,
    );

    $header_format = array(
      'font'  => array(
        'bold' => true,
      ),
    );

    foreach ($tasks as $task) {
      $task_owner = null;
      if ($task->getOwnerPHID()) {
        $task_owner = $handles[$task->getOwnerPHID()]->getName();
      }

      $custom_fields = PhabricatorCustomField::getObjectFields(
        $task,
        PhabricatorCustomField::ROLE_VIEW);
      $custom_fields
        ->setViewer($user)
        ->readFieldsFromStorage($task);
      $custom_fields = $custom_fields->getFields();

      $projects = array();
      foreach ($task->getProjectPHIDs() as $phid) {
        $projects[] = $handles[$phid]->getName();
      }
      $projects = implode(', ', $projects);

      $rows[] = array(
        'T'.$task->getID(),
        $task_owner,
        idx($status_map, $task->getStatus(), '?'),
        idx($pri_map, $task->getPriority(), '?'),
        $this->computeExcelDate($task->getDateCreated()),
        $this->computeExcelDate($task->getDateModified()),
        $task->getTitle(),
        $projects,
        PhabricatorEnv::getProductionURI('/T'.$task->getID()),
        phutil_utf8_shorten($task->getDescription(), 512),
        $custom_fields['std:maniphest:vendor:field']->getValueForStorage(),
      );
    }

    foreach ($rows as $row => $cols) {
      foreach ($cols as $col => $spec) {
        $cell_name = $this->col($col).($row + 1);
        $cell = $sheet
          ->setCellValue($cell_name, $spec, $return_cell = true);

        if ($row == 0) {
          $sheet->getStyle($cell_name)->applyFromArray($header_format);
        }

        if ($is_date[$col]) {
          $code = PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2;
          $sheet
            ->getStyle($cell_name)
            ->getNumberFormat()
            ->setFormatCode($code);
        } else {
          $cell->setDataType(PHPExcel_Cell_DataType::TYPE_STRING);
        }
      }
    }
  }

  private function col($n) {
    return chr(ord('A') + $n);
  }

}

Don't forget to update your class map:

$ cd webroot/phabricator
$ ../arcanist/bin/arc liberate src

@camilorojas I think I just added the following file

src/applications/maniphest/export/ManiphestExcelCustomFormat.php
<?php
final class ManiphestExcelCustomFormat extends ManiphestExcelFormat {
  public function getName() {
    return pht('Custom');
  }
  public function getFileName() {
    return 'maniphest_tasks_'.date('Ymd');
  }
  /**
   * @phutil-external-symbol class PHPExcel
   * @phutil-external-symbol class PHPExcel_IOFactory
   * @phutil-external-symbol class PHPExcel_Style_NumberFormat
   * @phutil-external-symbol class PHPExcel_Cell_DataType
   */
  public function buildWorkbook(
    PHPExcel $workbook,
    array $tasks,
    array $handles,
    PhabricatorUser $user) {
    $sheet = $workbook->setActiveSheetIndex(0);
    $sheet->setTitle(pht('Tasks'));
    $widths = array(
      null,
      15,
      null,
      10,
      15,
      15,
      60,
      30,
      20,
      100,
    );
    foreach ($widths as $col => $width) {
      if ($width !== null) {
        $sheet->getColumnDimension($this->col($col))->setWidth($width);
      }
    }
    $status_map = ManiphestTaskStatus::getTaskStatusMap();
    $pri_map = ManiphestTaskPriority::getTaskPriorityMap();
    $date_format = null;
    $rows = array();
    $rows[] = array(
      pht('ID'),
      pht('Owner'),
      pht('Status'),
      pht('Priority'),
      pht('Date Created'),
      pht('Date Updated'),
      pht('Title'),
      pht('Projects'),
      pht('URI'),
      pht('Description'),
      pht('Custom Field'),
    );
    $is_date = array(
      false,
      false,
      false,
      false,
      true,
      true,
      false,
      false,
      false,
      false,
      false,
    );
    $header_format = array(
      'font'  => array(
        'bold' => true,
      ),
    );
    foreach ($tasks as $task) {
      $task_owner = null;
      if ($task->getOwnerPHID()) {
        $task_owner = $handles[$task->getOwnerPHID()]->getName();
      }
      $custom_fields = PhabricatorCustomField::getObjectFields(
        $task,
        PhabricatorCustomField::ROLE_VIEW);
      $custom_fields
        ->setViewer($user)
        ->readFieldsFromStorage($task);
      $custom_fields = $custom_fields->getFields();
      $projects = array();
      foreach ($task->getProjectPHIDs() as $phid) {
        $projects[] = $handles[$phid]->getName();
      }
      $projects = implode(', ', $projects);
      $rows[] = array(
        'T'.$task->getID(),
        $task_owner,
        idx($status_map, $task->getStatus(), '?'),
        idx($pri_map, $task->getPriority(), '?'),
        $this->computeExcelDate($task->getDateCreated()),
        $this->computeExcelDate($task->getDateModified()),
        $task->getTitle(),
        $projects,
        PhabricatorEnv::getProductionURI('/T'.$task->getID()),
        phutil_utf8_shorten($task->getDescription(), 512),
        $custom_fields['std:maniphest:vendor:field']->getValueForStorage(),
      );
    }
    foreach ($rows as $row => $cols) {
      foreach ($cols as $col => $spec) {
        $cell_name = $this->col($col).($row + 1);
        $cell = $sheet
          ->setCellValue($cell_name, $spec, $return_cell = true);
        if ($row == 0) {
          $sheet->getStyle($cell_name)->applyFromArray($header_format);
        }
        if ($is_date[$col]) {
          $code = PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2;
          $sheet
            ->getStyle($cell_name)
            ->getNumberFormat()
            ->setFormatCode($code);
        } else {
          $cell->setDataType(PHPExcel_Cell_DataType::TYPE_STRING);
        }
      }
    }
  }
  private function col($n) {
    return chr(ord('A') + $n);
  }
}

Don't forget to update your class map:

$ cd webroot/phabricator
$ ../arcanist/bin/arc liberate src

Thanks a lot, do you know why i'm getting a

>>> UNRECOVERABLE FATAL ERROR <<<
 "Call to a member function getValueForStorage() on a non-object"

on this line of code

$custom_fields['std:maniphest:vendor:field']->getValueForStorage(),

@camilorojas That's because you haven't defined the custom field std:maniphest:vendor:field. Checkout the docs to see how to do that!

@camilorojas That's because you haven't defined the custom field std:maniphest:vendor:field. Checkout the docs to see how to do that!

i'm so dumb, i get it now, thanks guys.

it's working good.

@camilorojas You're welcome! PS: I like your avatar ... ^^

SpaceKees added a subscriber: SpaceKees.
rftfaria added a subscriber: rftfaria.
ysood added a subscriber: ysood.May 25 2016, 5:20 PM

@witrin your implementation is really useful. Just an addition for custom fields of storage type PhabricatorStandardCustomFieldSelect (dropdowns). The getValueForStorage() function only returns the index of the field's value.. I found this useful in getting the human readable format of the field's value

$rootCause = $custom_fields->getFields()['std:maniphest:fast:root-cause']->getProxy()->renderPropertyViewValue($handles);

The $handles array is needed in the call but not used in the function so can also be null.

@witrin Thanks for that contribution! I really need this.
I tried to set this up but I got the followeing error:

>>> UNRECOVERABLE FATAL ERROR <<<

Call to undefined function phutil_utf8_shorten()

/opt/bitnami/apps/phabricator/htdocs/src/applications/maniphest/export/ManiphestExcelCustomFormat.php:117


┻━┻ ︵ ¯\_(ツ)_/¯ ︵ ┻━┻

Is there any fix?
+ How can I get all the custom fields? just adding more of this lines:

$custom_fields['std:maniphest:vendor:field']->getValueForStorage(),
$custom_fields['std:maniphest:vendor:field2']->getValueForStorage(),
$custom_fields['std:maniphest:vendor:field3']->getValueForStorage(),

Thanks!

Sander added a subscriber: Sander.Sep 7 2016, 12:53 PM

If it helps anyone, I've been using this extension to allow exporting tasks with custom fields as well as the workboard column. Please read the comment at the top of it for full details:

https://github.com/neandrake/phab-utils/blob/master/extensions/ManiphestExcelDefaultIncludeCustomFieldsFormat.php

rrfeng added a subscriber: rrfeng.Mar 30 2017, 7:07 AM

Why the export does not contains the task author?

rbalik added a subscriber: rbalik.Nov 8 2017, 5:26 PM

We need something like this too because the exporter doesn't include points at the moment.

Seems like the ideal solution would be to have the user pick all the fields they want with some checkboxes.

rbalik awarded a token.Nov 8 2017, 5:27 PM
franjesus added a subscriber: franjesus.
epriestley closed this task as Resolved.Jan 29 2018, 11:08 PM
epriestley claimed this task.

Resolved by D18959 + D18960. See also T13049.

Not all custom field types have export support today, but the infrastructure is in place and additional support is generally straightforward.

tigzav added a subscriber: tigzav.May 9 2018, 1:29 PM
ysood added a comment.Mar 1 2019, 1:21 AM

Resolved by D18959 + D18960. See also T13049.
Not all custom field types have export support today, but the infrastructure is in place and additional support is generally straightforward.

This enhancement caused custom select fields from getting exported. As you mentioned, the infrastructure does exist but a minor enhancement is required for this. Please review this pull request to support the feature.
https://github.com/phacility/phabricator/pull/845