experiement migrating dashboard with grid 18 to 24 and back using excel

migrate_18_to_24.py

import random
import subprocess
from openpyxl import *
from openpyxl.styles import Side, Border, PatternFill
from openpyxl.utils import get_column_letter

# set to true if you dont' want to draw box in forward migration,
# used to set going from 24 -> 18 only
NO_FORWARD = False

START_X_18 = 1
START_Y_18 = 1

START_X_24 = 20
START_Y_24 = 1

START_X_18_ROLLBACK = 45
START_Y_18_ROLLBACK = 1

COLORS = [
    "FF5733",
    "8B5CFF",
    "00E1FF",
    "FFDD00",
    "FF00A5",
    "00FF48",
    "FF8800",
    "00FFD6",
    "FF006B",
    "2BFF00",
    "FFD600",
    "00B2FF",
    "FF0033",
    "A500FF",
    "00FF89",
    "FFA200",
    "00FFFA",
    "FF0079",
    "40FF00",
    "FFC400",
]

def check_box_overlap(boxes):
    for i in range(len(boxes)):
        for j in range(i + 1, len(boxes)):
            box1 = boxes[i]
            box2 = boxes[j]

            # Check if boxes overlap in the x-axis
            if box1[0] < box2[0] + box2[2] and box1[0] + box1[2] > box2[0]:
                # Check if boxes overlap in the y-axis
                if box1[1] < box2[1] + box2[3] and box1[1] + box1[3] > box2[1]:
                    print("OVERLAPPED: ", i, j, box1, box2)
                    return True  # Overlapping boxes found
    return False  # No overlapping boxes found


def set_background_color(sheet, r, color):
    [x, y, width, height] = r
    x = x + 1
    y = y + 1

    colorFill = PatternFill(patternType="solid", fgColor=color, )
    for w in range(width):
        for h in range(height):
            sheet.cell(row=y+h, column = x + w).fill = colorFill

def fill_cell_border(sheet, row, column, top=None, left=None, right=None, bottom=None):
    cell_border = sheet.cell(row=row, column=column).border or Border()
    new_border = Border(top= top or cell_border.top,
                        bottom= bottom or cell_border.bottom,
                        left= left or cell_border.left,
                        right = right or cell_border.right)
    sheet.cell(row=row, column=column).border = new_border

def draw_box(sheet, box, color = "000000"):
    [x, y, w, h] = box
    x = x + 1
    y = y + 1
    thin = Side(border_style="thick", color=color)
    for wi in range(w):
        # draw top line
        if wi == 0:
            fill_cell_border(sheet, row=y, column = x + wi, top=thin, left=thin)
        elif wi == w -1:
            fill_cell_border(sheet, row=y, column=x + wi, top=thin, right=thin)
        else:
            fill_cell_border(sheet, row=y, column=x + wi, top = thin)

        # draw bottom line
        if wi == 0:
            fill_cell_border(sheet, row=y + h - 1, column=x + wi, bottom=thin, left = thin)
        elif wi == w-1:
            fill_cell_border(sheet, row=y + h - 1, column=x + wi, bottom=thin, right = thin)
        else:
            fill_cell_border(sheet, row=y + h - 1, column=x + wi, bottom=thin)

    for hi in range(0, h):
       # draw left line
        fill_cell_border(sheet, row=y+hi, column=x, left=thin)
        # draw right line
        fill_cell_border(sheet, row=y+hi, column=x + w - 1, right=thin)

def offset_xy_box(box, offset_x, offset_y):
    [x, y, w, h] = box
    return  [x + offset_x, y + offset_y, w, h]

def new_sheet():
    workbook = Workbook()
    sheet = workbook.active

    # set columns width
    for i in range(START_X_18, START_X_18 + 19):
        sheet.column_dimensions[get_column_letter(i)].width = 4

    for i in range(START_X_24, START_X_24 + 25):
        sheet.column_dimensions[get_column_letter(i)].width = 3

    for i in range(START_X_18_ROLLBACK, START_X_18_ROLLBACK + 19):
        sheet.column_dimensions[get_column_letter(i)].width = 4

    draw_box(sheet, [START_X_18, START_Y_18, 18, 200], "000000")
    draw_box(sheet, [START_X_24, START_Y_24, 24, 200], "000000")
    draw_box(sheet, [START_X_18_ROLLBACK, START_Y_18_ROLLBACK, 18, 200], "000000")

    return workbook

def migrate_18_to_24(box):
    [col, row, size_x, size_y] = box
    new_col = col + (( col + 1 ) // 3)
    new_size_x = size_x + (( col + size_x + 1 ) // 3) - ((col + 1) // 3)

    return [new_col, row, new_size_x, size_y]

def migrate_24_to_18(box):
    [col, row, size_x, size_y] = box
    bp = 4
    new_size_x = size_x - ((size_x + col + 1) // bp - (col + 1) // bp) if size_x > 1 else size_x
    new_col = col - (col + 1) // bp if col > 1 else col

    return [new_col, row, new_size_x, size_y]

def forward_then_backward_boxes(boxes):
    return [migrate_24_to_18(migrate_18_to_24(box)) for box in boxes]

def draw_boxes(boxes, file_name):
    # Create a new workbook
    workbook = new_sheet()
    sheet = workbook.active

    for i, box in enumerate(boxes):
        color = COLORS[i % len(COLORS)]
        if not NO_FORWARD:
            draw_box(sheet, offset_xy_box(box, START_X_18, START_Y_18), color)
            draw_box(sheet, offset_xy_box(migrate_18_to_24(box), START_X_24, START_Y_24), color)
            draw_box(sheet, offset_xy_box(migrate_24_to_18(migrate_18_to_24(box)), START_X_18_ROLLBACK, START_Y_18_ROLLBACK), color)
        else:
            draw_box(sheet, offset_xy_box(box, START_X_24, START_Y_24), color)
            draw_box(sheet, offset_xy_box(migrate_24_to_18(box), START_X_18_ROLLBACK, START_Y_18_ROLLBACK), color)
            pass

    print("Initial has overlap?", check_box_overlap(boxes))
    print("Forward migration has overlap?", check_box_overlap(list(map(migrate_18_to_24, boxes))))
    print("Rollback migration has overlap?", check_box_overlap(forward_then_backward_boxes(boxes)))
    # Save the workbook
    workbook.save(file_name)

# order magic dashboard
boxes = []
if 0:
    boxes = [[0, 15, 12, 8],
            [12, 7, 6, 8],
            [5, 2, 5, 3],
            [0, 25, 7, 10,],
            [0, 2, 5, 3],
            [6, 7, 6, 8,],
            [7, 25 ,11 ,10],
            [0, 7, 6, 4,],
            [0, 23, 18, 2],
            [0, 5, 18, 2],
            [0, 0, 18 ,2],]


# Analytic events magic dashboard

if 1:
    boxes = [[0,47,6,4],[0,45,18,2],[6,41,6,4],[0,41,6,4],[0,39,18,2],[0,31,6,8],[12,27,6,8],[6,27,6,8],[0,23,6,8],[12,19,6,8],[6,19,6,8],[0,15,6,8],[12,11,6,8],[6,11,6,8],[0,11,6,4],[0,9,18,2],[0,5,18,4],[0,2,5,3],[0,0,18,2]]

# tests vertical migration
if 0:
    boxes = [
        # 4 boxes, with height from 1->4, stack on top of each other
        [0, 0, 2, 2], [0, 2, 2, 2], [0, 4, 2, 3], [0, 7, 2, 4],
        # 6 boxes, has 3 pair with the same height =1,2,3
        [3, 0, 2, 1], [3, 1, 2, 1], [3, 2, 2, 2], [3, 4, 2, 2], [3, 6, 2, 3], [3, 9, 2, 3]]

# test horizontal migration
if 0:
    boxes = []
    x = 0
    for w in [4, 4, 3, 3, 3, 1]:
        boxes.append([x, 0, w, 2])
        x += w

# randomly generated to test horizontal migration
if 0:
    boxes = []
    y = 0
    for h in range(1, 10):
        h = random.randint(0, 10)
        boxes.append([0, y, 3, h])
        y += h

# test horizontal migration for 24 -> 18 only
if 0:
    NO_FORWARD = True
    boxes = []
    x = 0
    for w in [5, 5, 4, 4, 6]:
        boxes.append([x, 0, w, 2])
        x += w

# randomly generated for 18 grid
if 1:
    boxes = []
    num_rows = 20
    y = 0
    for _ in range(num_rows):
        x = 0
        h = random.randint(1, 7)
        is_draw = random.gauss(1.2, 0.3) > 1
        w = random.randint(1, 10)
        while x + w < 18:
            if is_draw:
                boxes.append([x, y, w, h])
            x += w
            w = random.randint(1, 10)

        # if there is still room, add another box to fit the whole row
        if x < 18:
            boxes.append([x, y, 18 - x, h])
        y = y + h

# randomly generate for grid 24 only
if 1:
    NO_FORWARD = True
    boxes = []
    num_rows = 20
    y = 0
    for _ in range(num_rows):
        x = 0
        h = random.randint(1, 7)
        w = random.randint(1, 10)
        while x + w < 18:
            boxes.append([x, y, w, h])
            x += w
            w = random.randint(1, 25 - x)

        # if there is still room, add another box to fit the whole row
        if x < 24:
            boxes.append([x, y, 24 - x, h])
        y = y + h

# Tim's cases
if 0:
    boxes = [
        [0, 0, 5, 4],
        [5, 0, 2, 4],
        [9, 0, 4, 4],
        [13, 0, 4, 4],
        [17, 0, 1, 4]
    ]

fname = f"boxes{random.randint(0, 100)}.xlsx"
draw_boxes(boxes, fname)
subprocess.run(["open", fname])
print(fname)