How to export to Excel Django object where every ForeignKey object a new column with import-export lib?

I have a model represents some item on stock:

(Code has been simplified)

class StockItem(models.Model):
    name = models.CharField()
    category = models.Charfield()
    stock = models.ForeignKey(Stock) # <- Here is few stocks
    quantity = models.IntegerField(default=0)

So every StockItem may exist on different stocks but with different q-ty remained. Is there a way to create ModelResource to export quantity remained for every stock?

Table example:

+----+--------+----------+---------+---------+
| ID |  Name  | Category | Stock A | Stock B |
+----+--------+----------+---------+---------+
|  1 | Item 1 | Cat 1    |       1 |       1 |
|  2 | Item 2 | Cat 2    |      10 |       8 |
|  3 | Item 3 | Cat 3    |      14 |      32 |
+----+--------+----------+---------+---------+

My ModelResource:

class StockItemsRemainingsResource(resources.ModelResource):

    class Meta:
        model = models.StockItem
        fields = ('name', 'category')

    def __init__(self):
            super().__init__()
            for item in self.get_queryset():
                stock_name = item.stock.name
                self.fields[f"quantity_{stock_name}"] = fields.Field(
                    attribute="quantity",
                    column_name=stock_name,
                    widget=widgets.ForeignKeyWidget(
                        models.Stock,
                        "pk"
                    )
                )

I was trying to override the __init__ method but probably did it wrong.

The after_export() method can be overridden to modify the dataset after the initial export. You could maybe append a column to the dataset as required.

For example, you can add something like this method to StockItemsRemainingsResource:

    def after_export(self, queryset, dataset, **kwargs):
        counts = []
        for d in dataset.dict:
            stock_count = Stock.objects.filter(d["name"]).count()
            counts.append(stock_count)
        dataset.append_col(counts, header="count")

You could play around with this to find the best way of doing this. This will be inefficient so probably won't work well with large datasets.

See tablib docs.

After hours of search I was able to achive such a structure overriding export_field() after exploring some library's code.

In my case I had to create a required fields overriding __init__ method first but using a normal fields.Field class to prepare these fields for manual population in export_field() method:

def __init__(self):
        super().__init__()
        stocks = models.Stock.objects.all()
        for stock in stocks:
            field_name = f'stock_{stock.id}'
            self.fields[field_name] = fields.Field(
                column_name=f'{stock.name}',
                attribute=field_name
            )

The final step I made is to override export_field(self, field, instance) which takes every instance on queryset iteration to every field of resource for processing. In my case I checked a field name and in case it matches with one of the stock fields I will count a q-ty of instance related to particular stock and return it (return super() otherwise). My code :

def export_field(self, field, instance):
        # overrided to populate dynamic fields (stock quantity)
        # If field is stock ("stock_{id}") then extract and ID for querying
        # an actual quantity
        if field.attribute.startswith("stock"):
            stock_id = field.attribute.split("_")[-1]
            stock = models.Stock.objects.filter(pk=stock_id).first()
            return instance.get_stock_quantity(stock) if stock else None
        else:
            return super().export_field(field, instance)

Probably not the best approach and I have a guess it's may be achived utilizing dehydrate_method which may be assigned to field.Field(dehydrate_method=some_method()) instance but wasn't search for a solution in that direction.

Back to Top