How to Assign Value From a Variable to an Excel Cell with Python

Question

I want to assign the value of the variable game_type to excel cell but i am unable to do so:

import openpyxl
wb = openpyxl.load_workbook('Table1.xlsx')
Card13 = wb.active
print Card13
class private_table():
    def __init__(self, game):
        if game == 13:
            game_type = game
            Card13['A2'] = game_type
            wb.save('Table1.xlsx')
            print(game_type)
        elif game == '21':
            game_type = int(game)
            print(game_type)
        elif game == '27':
            game_type = int(game)
            print(game_type)
        else:
            print("Enter correct game type")
    def noc(self, cards):
        if cards == 13:
            game_type = Card13['A2'].value
            if game_type == '13':
                num_of_cards = cards
                print num_of_cards
                return num_of_cards
            else:
                print ("Please select the correct number of cards")
                return("Please select the correct number of cards")
        elif cards == '21':
            game_type = Card13['B1'].value
            if game_type == '21':
                num_of_cards = cards
                print num_of_cards
                return num_of_cards
            else:
                print ("Please select the correct number of cards")
                return("Please select the correct number of cards")
        elif cards == '27':
            game_type = Card13['B1'].value
            if game_type == '27':
                num_of_cards = cards
                print num_of_cards
                return num_of_cards
            else:
                print ("Please select the correct number of cards")
                return("Please select the correct number of cards")

Answer 1

Below is the solution that helped me insert the value of a variable in a excel sheet.

import openpyxl
wb = openpyxl.load_workbook('Table1.xlsx')
Card13 = wb.active
print Card13
class private_table():
    def __init__(self, game):
        if game == 13:
            game_type = game
            Card13['B1'] = game_type
            wb.save('Table1.xlsx')
            print(game_type)
        elif game == 21:
            game_type = (game)
            Card13['B1'] = game_type
            wb.save('Table1.xlsx')
            print(game_type)
        elif game == 27:
            game_type = (game)
            wb.save('Table1.xlsx')
            Card13['B1'] = game_type
            print(game_type)
        else:
            print("Enter correct game type")

Answer 2

From the documentation of openpyxl, this is how you write in Excel:

from openpyxl import Workbook
wb = Workbook()

# grab the active worksheet
ws = wb.active

# Data can be assigned directly to cells
ws['A1'] = 42

# Rows can also be appended
ws.append([1, 2, 3])

# Python types will automatically be converted
import datetime
ws['A2'] = datetime.datetime.now()

# Save the file
wb.save("sample.xlsx")

---

If you decide to use xlsxwriter, this is some working sample, working for Python 3. For Python 2, remove the parenthesis from the print () to look like this - print wks2.name.

import xlsxwriter
from xlsxwriter.utility import xl_rowcol_to_cell

wbk = xlsxwriter.Workbook('testing.xlsx')
wks = wbk.add_worksheet()
wks.write('A1', 'Hello world')
print (wks.name)
wks2 = wbk.add_worksheet()
print (wks2.name)

i = -1

for x in range(1, 1000, 11):
    i+=1
    cella = xl_rowcol_to_cell(i, 0) #0,0 is A1!
    cellb = xl_rowcol_to_cell(i, 1)
    cellc = xl_rowcol_to_cell(i, 2)
    print (cella)
    wks2.write(cella,x)
    wks2.write(cellb,x*3)
    wks2.write(cellc,x*4.5)
wbk.close()

You just need to have a workbook named testing.xlsx in the same directory with Python. Then you get this on the first sheet:

and this on the added sheet:

And this is what you get if you run the code with Python:

The name of the worksheets come from print(wks.name) and print(wks2.name).