import time


time.sleep(1)  # Pauzeert de uitvoering voor 5 seconden

import pandas as pd
import numpy as np
from datetime import datetime, timedelta
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment, Font
from openpyxl.chart.marker import Marker

time.sleep(10)

# Maak een nieuw werkboek en werkblad
wb = Workbook()
ws = wb.active
ws.title = "Blad1"
klant = "even testen"

# Basisstructuur toevoegen
data = [
    ["TEST RAPPORT", "", "", "", "", "", "", "", ""],
    ["Algemeen", "", "", "", "", "", "", "", ""],
    ["klant", klant, "", "", "", "", "", "", ""],
    ["ordernr", "", "", "", "", "", "", "", "getest door"],
    ["datum", "", "", "", "", "", "", "", "getest op"],
    ["", "", "", "", "", "", "", "", ""],
    ["Toestelinformatie", "", "", "", "", "", "", "", ""],
    ["pomp/motor", "", "", "", "", "", "", "", ""],
    ["serie nummer", "", "", "", "", "", "", "", ""],
    ["inhoud", "", "", "", "", "", "", "", ""],
    ["rotatie", "", "", "", "", "", "", "", ""],
    ["type circuit", "", "", "", "", "", "", "", ""],
    ["", "", "", "", "", "", "", "", ""],
    ["Testinformatie", "", "", "", "", "", "", "", ""],
    ["max druk", "", "", "", "", "", "", "", ""],
    ["max debiet", "", "", "", "", "", "", "", ""],
    ["boostdruk", "", "", "", "", "", "", "", ""],
    ["", "", "", "", "", "", "", "", ""],
    ["grafiekken", "", "", "", "", "", "", "", ""],
    ["timestamp", "druk (bar)", "", "", "", "", "", "", ""]
]

for row in data:
    ws.append(row)

# Titels centreren en vet maken over kolommen A tot I
for row_idx in [1, 2, 7, 14, 19]:
    ws.merge_cells(start_row=row_idx, start_column=1, end_row=row_idx, end_column=9)
    cell = ws.cell(row=row_idx, column=1)
    cell.alignment = Alignment(horizontal="center")
    cell.font = Font(bold=True)

# Willekeurige meetdata genereren
start_time = datetime.now()
time_stamps = [start_time + timedelta(minutes=i) for i in range(20)]
values = np.random.randint(0, 101, size=20)

for i, (time, value) in enumerate(zip(time_stamps, values)):
    ws.append([time.strftime("%Y-%m-%d %H:%M:%S"), value])

# Grafiek toevoegen
chart = LineChart()
chart.title = "Druk over tijd"
chart.x_axis.title = "Tijd"
chart.y_axis.title = "Druk (bar)"
chart.y_axis.majorGridlines = None  # Assen zichtbaar maken
chart.x_axis.majorTickMark = "out"
chart.y_axis.majorTickMark = "out"

# Data toevoegen
data = Reference(ws, min_col=2, min_row=20, max_row=39)
cats = Reference(ws, min_col=1, min_row=21, max_row=39)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

# Datapunten weergeven
series = chart.series[0]
series.marker = Marker('circle')
series.smooth = False

ws.add_chart(chart, "F5")

# Opslaan
wb.save("Test_Rapport.xlsx")