DRF formatting XLSX content

Question

I am trying to set a different color on every second row in XLSX file. From the documentation I see that I can pass some conditions using body property or get_body() method, but this only allows me to set somewhat "static" conditions. Here is the ViewSet config responsible for rendering the XLSX file:

class MyViewSet(XLSXFileMixin, ModelViewSet):
    def get_renderers(self) -> List[BaseRenderer]:
        if self.action == "export":
            return [XLSXRenderer()]
        else:
            return super().get_renderers()

    @action(methods=["GET"], detail=False)
    def export(self, request: Request) -> Response:
        serializer = self.get_serializer(self.get_queryset(), many=True)
        return Response(serializer.data)

    # Properties for XLSX
    column_header = {
        "titles": [
            "Hostname", "Operating System", "OS name", "OS family", "OS version", "Domain", "Serial number",
            "Available patches",
        ],
        "tab_title": "Endpoints",
        "style": {
            "font": {
                "size": 14,
                "color": "FFFFFF",
            },
            "fill": {
                "start_color": "3F803F",
                "fill_type": "solid",
            }
        }
    }

    body = {
        "style": {
            "font": {
                "size": 12,
                "color": "FFFFFF"
            },
            "fill": {
                "fill_type": "solid",
                "start_color": "2B2B2B"
            },
        }
    }

score 1 · Accepted Answer · answered Aug 29 '19 at 12:51

OK. I got the answer after some digging through the source code. The render method of XLSXRenderer has this piece of code:

for row in results:
    column_count = 0
    row_count += 1
    flatten_row = self._flatten(row)
    for column_name, value in flatten_row.items():
        if column_name == "row_color":
            continue
        column_count += 1
        cell = ws.cell(
            row=row_count, column=column_count, value=value,
        )
        cell.style = body_style
    ws.row_dimensions[row_count].height = body.get("height", 40)
    if "row_color" in row:
        last_letter = get_column_letter(column_count)
        cell_range = ws[
            "A{}".format(row_count): "{}{}".format(last_letter, row_count)
        ]
        fill = PatternFill(fill_type="solid", start_color=row["row_color"])
        for r in cell_range:
            for c in r:
                c.fill = fill

So when I added a field row_color in my serializer as SerializerMethodField I was able to define a function that colors rows:

def get_row_color(self, obj: Endpoint) -> str:
    """
    This method returns color value for row in XLSX sheet.
    (*self.instance,) extends queryset to a list (it must be a queryset, not a single Endpoint).
    .index(obj) gets index of currently serialized object in that list.
    As the last step one out of two values from the list is chosen using modulo 2 operation on the index.
    """
    return ["353535", "2B2B2B"][(*self.instance,).index(obj) % 2]

DRF formatting XLSX content

1 Answers1