Magento 2: how can I remove columns in exported csv from order grid?

Question

I want remove columns while exporting order grid data in magento 2. I google it but didn't find solution. Like I want to remove these fields from exported csv.

1. Payment Method
2. Total Refunded
3. Customer Group
4. Grand Total (Base)
5. Purchase Point

Answer 1

Let me do that for you with step by step.  First Step: Register your export button in your: view/adminhtml/ui_component/[grid-view-id].xml

<exportButton name="export_button"  class="Namespace\AdvancedReport\Component\ExportButton">
    <argument name="data" xsi:type="array">
        <item name="config" xsi:type="array">
            <item name="component" xsi:type="string">Magento_Ui/js/grid/export</item>
            <item name="displayArea" xsi:type="string">dataGridActions</item>
            <item name="options" xsi:type="array">
                <item name="cvs" xsi:type="array">
                    <item name="value" xsi:type="string">csv</item>
                    <item name="label" xsi:type="string" translate="true">CSV</item>
                    <item name="url" xsi:type="string">my_advancedreport/export/gridToCsv</item>
                </item>
            </item>
        </item>
    </argument>
</exportButton>

Of course, you need a controller to do this thing: 

namespace Namespace\AdvancedReport\Controller\Adminhtml\Export;

class GridToCsv extends \Magento\Ui\Controller\Adminhtml\Export\GridToCsv
{
    const ADMIN_RESOURCE = 'My_AdvancedReport::advanced_invoice_report';
}

Second Step: Modify your controller class to use custom Export Model. /code/Namespace/AdvancedReport/etc/di.xml (scope global or Adminhtml)

<type name="Namespace\AdvancedReport\Model\Export\ConvertToCsv">
    <arguments>
        <argument name="metadataProvider" xsi:type="object">Namespace\AdvancedReport\Model\Export\MetadataProvider</argument>
    </arguments>
</type>
<type name="Namespace\AdvancedReport\Controller\Adminhtml\Export\GridToCsv">
    <arguments>
        <argument name="converter" xsi:type="object">\Namespace\AdvancedReport\Model\Export\ConvertToCsv</argument>
    </arguments>
</type>

Custom data provider to get what are selected columns user have selected on Admin. app/code/Namespace/AdvancedReport/Model/Export/MetadataProvider.php about the idea I have used \Magento\Ui\Model\BookmarkManagement. to get stored grid for Admin User while User Edit selected or sort-ordered columns of the Report Grid.

namespace Namespace\AdvancedReport\Model\Export;


use Magento\Framework\View\Element\UiComponentInterface;
use Magento\Ui\Component\MassAction\Filter;
use Magento\Framework\Locale\ResolverInterface;
use Magento\Framework\Stdlib\DateTime\TimezoneInterface;

class MetadataProvider extends \Magento\Ui\Model\Export\MetadataProvider
{
    /**
     * @var \Magento\Ui\Model\BookmarkManagement
     */
    protected $_bookmarkManagement;

    /**
     * MetadataProvider constructor.
     * @param Filter $filter
     * @param TimezoneInterface $localeDate
     * @param ResolverInterface $localeResolver
     * @param string $dateFormat
     * @param \Magento\Ui\Model\BookmarkManagement $bookmarkManagement
     * @param array $data
     */
    public function __construct(
        Filter $filter,
        TimezoneInterface $localeDate,
        ResolverInterface $localeResolver,
        \Magento\Ui\Model\BookmarkManagement $bookmarkManagement,
        $dateFormat = 'M j, Y H:i:s A',
        array $data = [])
    {
        parent::__construct($filter, $localeDate, $localeResolver, $dateFormat, $data);
        $this->_bookmarkManagement = $bookmarkManagement;
    }

    protected function getActiveColumns($component){
        $bookmark = $this->_bookmarkManagement->getByIdentifierNamespace('current', $component->getName());

        $config = $bookmark->getConfig();
        $columns = $config['current']['columns'];
        $_activeColumns = [];
        foreach ($columns as $column => $config){
            if(true === $config['visible'] && $column != 'ids'){
                $_activeColumns[] = $column;
            }
        }
        return $_activeColumns;
    }
    /**
     * @param UiComponentInterface $component
     * @return UiComponentInterface[]
     * @throws \Exception
     */
    protected function getColumns(UiComponentInterface $component)
    {
        if (!isset($this->columns[$component->getName()])) {

            $activeColumns = $this->getActiveColumns($component);

            $columns = $this->getColumnsComponent($component);
            foreach ($columns->getChildComponents() as $column) {
                if ($column->getData('config/label') && $column->getData('config/dataType') !== 'actions') {
                    if(in_array($column->getName(), $activeColumns)) {
                        $this->columns[$component->getName()][$column->getName()] = $column;
                    }
                }
            }
        }
        return $this->columns[$component->getName()];
    }
}

ConvertToCsv class

namespace Namespace\AdvancedReport\Model\Export;
use \Magento\Ui\Model\Export\ConvertToCsv as DefauleConvertToCsv;

class ConvertToCsv extends DefauleConvertToCsv
{

    /**
     * Returns CSV file
     *
     * @return array
     * @throws LocalizedException
     */
    public function getCsvFile()
    {
        $component = $this->filter->getComponent();

        $name = md5(microtime());
        $file = 'export/'. $component->getName() . $name . '.csv';

        $this->filter->prepareComponent($component);
        $this->filter->applySelectionOnTargetProvider();
        $dataProvider = $component->getContext()->getDataProvider();
        $fields = $this->metadataProvider->getFields($component);
        $options = $this->metadataProvider->getOptions();

        $this->directory->create('export');
        $stream = $this->directory->openFile($file, 'w+');
        $stream->lock();
        $stream->writeCsv($this->metadataProvider->getHeaders($component));
        $i = 1;
        $searchCriteria = $dataProvider->getSearchCriteria()
            ->setCurrentPage($i)
            ->setPageSize($this->pageSize);
        $totalCount = (int) $dataProvider->getSearchResult()->getTotalCount();
        while ($totalCount > 0) {
            $items = $dataProvider->getSearchResult()->getItems();
            foreach ($items as $item) {
                $this->metadataProvider->convertDate($item, $component->getName());
                $stream->writeCsv($this->metadataProvider->getRowData($item, $fields, $options));
            }
            $searchCriteria->setCurrentPage(++$i);
            $totalCount = $totalCount - $this->pageSize;
        }
        $total = $dataProvider->getTotalRow($searchCriteria);
        $writeTotal = [];
        foreach ($fields as $column) {
            $writeTotal[] =$total[$column];
        }
        $stream->writeCsv($writeTotal);

        $stream->unlock();
        $stream->close();

        return [
            'type' => 'filename',
            'value' => $file,
            'rm' => true  // can delete file after use
        ];
    }
}

Done.

Answer 2

Try following way:

Create Vendor/Module/view/adminhtml/ui_component/sales_order_grid.xml

<?xml version="1.0" encoding="UTF-8"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <listingToolbar name="listing_top">
        <exportButton name="export_button" class="Vendor\Module\Component\ExportButton" />
    </listingToolbar>
</listing>

And Create Vendor/Module/Component/ExportButton.php

namespace Vendor\Module\Component;

use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\UrlInterface;

class ExportButton extends \Magento\Ui\Component\AbstractComponent
{
    /**
     * Component name
     */
    const NAME = 'exportButton';

    /**
     * @var \Magento\Framework\UrlInterface
     */
    protected $urlBuilder;

    /**
     * @param ContextInterface $context
     * @param UrlInterface $urlBuilder
     * @param array $components
     * @param array $data
     */
    public function __construct(
        ContextInterface $context,
        UrlInterface $urlBuilder,
        array $components = [],
        array $data = []
    ) {
        parent::__construct($context, $components, $data);
        $this->urlBuilder = $urlBuilder;
    }

    /**
     * Get component name
     *
     * @return string
     */
    public function getComponentName()
    {
        return static::NAME;
    }

    /**
     * @return void
     */
    public function prepare()
    {
        $config = $this->getData('config');
        if (isset($config['options'])) {
            $config['options'] = [];
            $this->setData('config', $config);
        }
        parent::prepare();
    }
}

Clear cache.

Answer 3

Right now it is not possible to change columns for export. See: https://github.com/magento/magento2/issues/3079

Answer 4

Extend \Magento\Ui\Model\Export\ConvertToCsv file in to your custom module to extend model file create di.xml

<preference for="Magento\Ui\Model\Export\ConvertToCsv" type="Namespace\Modulename\Model\Export\ConvertToCsv" />

and in your extended file add below conditions to remove unwanted columns and column value in order export CSV Format.

        if($component->getName()=='sales_order_grid') {             
            $fieldsVal = $this->metadataProvider->getFields($component);
            $removeFieldsArr = array('column names which you want to remove goes here ..');

            foreach($fieldsVal as $key=>$val){ 
                if(in_array($val,$removeFieldsArr)){
                     unset($fieldsVal[$key]);
                 }
            }
            $fields  = array_values($fieldsVal);

        }else{
            $fields = $this->metadataProvider->getFields($component);
        }

Answer 5

You need to overwrite the below file in your module " Magento\Ui\Model\Export\ConvertToCsv "

<preference for="Magento\Ui\Model\Export\ConvertToCsv" type="Namespace\Modulename\Model\Export\ConvertToCsv" />

Create ConvertToCsv.php in your module in the below path

app/code/Namespace\Modulename\Model\Export\ConvertToCsv.php

In your, ConvertToCsv.php add the below code to remove the column header and its value

$removeFieldsArr = array('entity_id', 'customer_lastname');

$removeheaderArr = array('Entity ID', 'Customer Lastname');

<?php
namespace Namespace\Modulename\Model\Export;

use Magento\Framework\Exception\LocalizedException;
use Magento\Framework\Filesystem;
use Magento\Ui\Component\MassAction\Filter;
use Magento\Ui\Model\Export\MetadataProvider;

/**
 * Class ConvertToCsv
 */
class ConvertToCsv extends \Magento\Ui\Model\Export\ConvertToCsv
{
    public function __construct(
        Filesystem $filesystem,
        Filter $filter,
        MetadataProvider $metadataProvider,
        $pageSize = 200
    )
    {
        parent::__construct($filesystem, $filter, $metadataProvider, $pageSize);
    }

    /**
     * Returns CSV file
     *
     * @return array
     * @throws LocalizedException
     */
    public function getCsvFile()
    {

        $component = $this->filter->getComponent();

        // md5() here is not for cryptographic use.
        // phpcs:ignore Magento2.Security.InsecureFunction
        $name = md5(microtime());
        $file = 'export/' . $component->getName() . $name . '.csv';

        $this->filter->prepareComponent($component);
        $this->filter->applySelectionOnTargetProvider();
        $dataProvider = $component->getContext()->getDataProvider();
        //$fields = $this->metadataProvider->getFields($component);
        if($component->getName() === 'coupon_report_listing') {
            $fieldsVal = $this->metadataProvider->getFields($component);
            $removeFieldsArr = array('entity_id', 'customer_lastname'); // add column name which you want to exclude in csv.

            foreach($fieldsVal as $key=>$val){
                if(in_array($val,$removeFieldsArr)){
                    unset($fieldsVal[$key]);
                }
            }
            $fields  = array_values($fieldsVal);
            $fields[] = "increment_id";
         
        }else{
            $fields = $this->metadataProvider->getFields($component);
        }
        $options = $this->metadataProvider->getOptions();

        $this->directory->create('export');
        $stream = $this->directory->openFile($file, 'w+');
        $stream->lock();
        //$stream->writeCsv($this->metadataProvider->getHeaders($component));
        if($component->getName() === 'coupon_report_listing') {
            $fieldsHeader = $this->metadataProvider->getHeaders($component);
            $removeheaderArr = array('Entity ID', 'Customer Lastname'); //// add column Header which you want to exclude in CSV.

            foreach($fieldsHeader as $key=>$val){
                if(in_array($val,$removeheaderArr)){
                    unset($fieldsHeader[$key]);
                }
            }
            $header  = array_values($fieldsHeader);
            $header[] = "Increment Id";
        }else{
            $header = $this->metadataProvider->getHeaders($component);
        }

        $stream->writeCsv($header);
        $i = 1;
        $searchCriteria = $dataProvider->getSearchCriteria()
            ->setCurrentPage($i)
            ->setPageSize($this->pageSize);
        $totalCount = (int)$dataProvider->getSearchResult()->getTotalCount();
        while ($totalCount > 0) {
            $items = $dataProvider->getSearchResult()->getItems();
            foreach ($items as $item) {

                $this->metadataProvider->convertDate($item, $component->getName());
                $stream->writeCsv($this->metadataProvider->getRowData($item, $fields, $options));
            }
            $searchCriteria->setCurrentPage(++$i);
            $totalCount = $totalCount - $this->pageSize;
        }
        $stream->unlock();
        $stream->close();

        return [
            'type' => 'filename',
            'value' => $file,
            'rm' => true  // can delete file after use
        ];
    }
}