Spreadsheet GPT

0.spreadsheet.py

import os,json,xlwt
import pandas as pd
from pprint import pprint
from openai import OpenAI

client = OpenAI()
client.api_key = os.getenv("OPENAI_API_KEY")

def chat(new_question, history):

    if len(history)>1 and history[-2]["content"]==new_question:
        print("SKIP: ",new_question)
        return history

    history.append({"role": "user", "content": new_question})
    
    completion = client.chat.completions.create(
        model="gpt-4-1106-preview",
        response_format={ "type": "json_object" },
        messages=[
            {"role": "system", "content": "you are a spreadsheet engine and communicate changes to the sheet as JSON in the form of { \"A1\": {\"value\":5}, \"B1\":{\"value\":7} } etc. When a calculation is requested you store the formula instead of the value. you can also handle formatting in xlwt.easyxf style"}] +
            history
    )
    
    assistant_response = completion.choices[0].message.content
    history.append({"role": "assistant", "content": assistant_response})
    return history


history_filename = "history.json"
history = json.loads(open(history_filename).read())

history = chat("Geef A1 een gele achtergrond en een zwarte border",history)

json.dump(history, open(history_filename,"w"), indent=2)

sheet = {}
for row in history:
    if row["role"]=="assistant":
        data = json.loads(row["content"])
        for key,value in data.items():
            for prop in ["value","formula","pattern","border"]:
                if prop in value:
                    if not key in sheet:
                        sheet[key] = {}
                    sheet[key][prop] = value[prop]
            
# remove None values
sheet = {key:value for key, value in sheet.items() if value is not None}

pprint(sheet)


# format = xlwt.easyxf('pattern: pattern solid, fore_colour red; font: bold on; align: wrap on, vert centre, horiz center')
# format = xlwt.easyxf('')

# save as Excel 97 file
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet1')
for cell, cell_data in sheet.items():
    col = ord(cell[0]) - 65
    row = int(cell[1:]) - 1

    formats = []
    for prop in ["pattern","border"]:
        if prop in cell_data:
            formats.append(prop + ": " + cell_data[prop])

    format_str = "; ".join(formats)
    print(format_str)
    format = xlwt.easyxf(format_str)

    if "formula" in cell_data:
        if isinstance(cell_data["formula"], str) and cell_data["formula"].startswith('='): #isinstance(cell_data["value"])
            worksheet.write(row, col, xlwt.Formula(cell_data["formula"][1:]), format)  # Verwijder '=' voor xlwt Formula
    if "value" in cell_data:
        worksheet.write(row, col, cell_data["value"], format)

filename = 'output.xls'
workbook.save(filename)

history.json

[
  {
    "role": "user",
    "content": "plaats in kolom A de volgende waarden: 5, 4, 3, 8, 12."
  },
  {
    "role": "assistant",
    "content": "{ \n  \"A1\": {\"value\":5}, \n  \"A2\": {\"value\":4}, \n  \"A3\": {\"value\":3}, \n  \"A4\": {\"value\":8}, \n  \"A5\": {\"value\":12} \n}"
  },
  {
    "role": "user",
    "content": "B1 is de som van kolom A"
  },
  {
    "role": "assistant",
    "content": "{\n  \"B1\": {\"formula\":\"=SUM(A1:A5)\"}\n}"
  },
  {
    "role": "user",
    "content": "voeg de titel 'resultaten' toe bovenaan kolom A en maak deze vet"
  },
  {
    "role": "assistant",
    "content": "{\n  \"A1\": {\"value\": \"Resultaten\", \"font-weight\": \"bold\"},\n  \"A2\": {\"value\": 5},\n  \"A3\": {\"value\": 4},\n  \"A4\": {\"value\": 3},\n  \"A5\": {\"value\": 8},\n  \"A6\": {\"value\": 12}\n}"
  },
  {
    "role": "user",
    "content": "verplaats alle waarden uit kolom A naar kolom C en zorg dat de formule in B1 geupdate wordt"
  },
  {
    "role": "assistant",
    "content": "{\n  \"C1\": {\"value\": \"Resultaten\", \"font-weight\": \"bold\"},\n  \"C2\": {\"value\": 5},\n  \"C3\": {\"value\": 4},\n  \"C4\": {\"value\": 3},\n  \"C5\": {\"value\": 8},\n  \"C6\": {\"value\": 12},\n\n  \"A1\": {\"value\": null},\n  \"A2\": {\"value\": null},\n  \"A3\": {\"value\": null},\n  \"A4\": {\"value\": null},\n  \"A5\": {\"value\": null},\n  \"A6\": {\"value\": null},\n\n  \"B1\": {\"formula\":\"=SUM(C2:C6)\"}\n}"
  },
  {
    "role": "user",
    "content": "voeg de getallen 1 t/m 10 toe aan kolom D"
  },
  {
    "role": "assistant",
    "content": "{\n  \"D1\": {\"value\": 1},\n  \"D2\": {\"value\": 2},\n  \"D3\": {\"value\": 3},\n  \"D4\": {\"value\": 4},\n  \"D5\": {\"value\": 5},\n  \"D6\": {\"value\": 6},\n  \"D7\": {\"value\": 7},\n  \"D8\": {\"value\": 8},\n  \"D9\": {\"value\": 9},\n  \"D10\": {\"value\": 10}\n}"
  },
  {
    "role": "user",
    "content": "zet op A1 het woord Hoera!"
  },
  {
    "role": "assistant",
    "content": "{\n  \"A1\": {\"value\": \"Hoera!\"}\n}"
  },
  {
    "role": "user",
    "content": "Geef A1 een gele achtergrond en een zwarte border"
  },
  {
    "role": "assistant",
    "content": "{\n  \"A1\": {\n    \"value\": \"Hoera!\",\n    \"pattern\": \"pattern solid\", \"pattern_fore_colour\": \"yellow\"\n  }\n}"
  }
]