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")

What isn't working with your code above? And did you see this [how to write to a new cell in python using openpyxl](https://stackoverflow.com/questions/31395058/how-to-write-to-a-new-cell-in-python-using-openpyxl) ? — QHarr, Jun 18 '18 at 10:22
I want to write the value of the variable 'game_type' to the cell Card13['A2'], which is not working for me. — Abhijit D, Jun 18 '18 at 10:25
@Vityata i wanted to see which is the active sheet that has been selected. — Abhijit D, Jun 18 '18 at 10:31
@AbhijitD - I see. So what are you expecting to have printed? Name, variable name or reference? Or you got an error on that line? — Vityata, Jun 18 '18 at 10:32
@Vityata i was expecting a name reference of the sheet and no i did not get any error on that line. — Abhijit D, Jun 18 '18 at 10:34
@AbhijitD - What is "name reference"? A name like "Sheet1" or an address in the memory, something like `0x23af4234`? — Vityata, Jun 18 '18 at 10:46
@AbhijitD - and did you get it? You seem to be working with `python 2.X`, I cannot replicate... — Vityata, Jun 18 '18 at 10:51
In your code, how are you initializing the class `private_table`? — Vityata, Jun 18 '18 at 10:55

Vityata · Answer 1 · 2018-06-18T10:54:42.937

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).

score 0 · Accepted Answer · answered Jun 18 '18 at 14:48

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")

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

2 Answers2