ethan lee ethan lee - 4 months ago 34
Python Question

How to I make a new line every use in openpyxl?

I am trying to set up an easy way to mark my business' records through a python program and I am using the module openpyxl for the excel sheets part and I am wondering how I can make it so every time I use the program it uses the next line in the excel sheet. Thank you!

from openpyxl import Workbook
wb = Workbook()

# grab the active worksheet
ws = wb.active
item = raw_input('Item: ')
sold = raw_input('Sold for: ')
percentage = raw_input('Percentage (in decimals): ')
date = raw_input('Date of Sale: ')
customer = raw_input('Customer: ')
# Data can be assigned directly to cells
ws['B2'] = item
ws['C2'] = sold
ws['D2'] = percentage
ws['E2'] = date
ws['F2'] = customer
wb.save("sample.xlsx")

Answer

You can use ws.max_row here. Also make sure you load the previously saved file instead of opening up a new file each time.

import openpyxl 
wb = openpyxl.load_workbook('sample.xlsx')

# grab the active worksheet
ws = wb.active
item = raw_input('Item: ')
sold = raw_input('Sold for: ')
percentage = raw_input('Percentage (in decimals): ')
date = raw_input('Date of Sale: ')
customer = raw_input('Customer: ')
# Data can be assigned directly to cells
input_row = ws.max_row + 1
ws['B{}'.format(input_row)] = item
ws['C{}'.format(input_row)] = sold
ws['D{}'.format(input_row)] = percentage
ws['E{}'.format(input_row)] = date
ws['F{}'.format(input_row)] = customer
wb.save("sample.xlsx")

You also might consider implementing a while loop here:

import openpyxl 

enter_more = 'y'
while enter_more == 'y':
    wb = openpyxl.load_workbook('sample.xlsx')

    # grab the active worksheet
    ws = wb.active
    item = raw_input('Item: ')
    sold = raw_input('Sold for: ')
    percentage = raw_input('Percentage (in decimals): ')
    date = raw_input('Date of Sale: ')
    customer = raw_input('Customer: ')
    # Data can be assigned directly to cells
    input_row = ws.max_row + 1
    ws['B{}'.format(input_row)] = item
    ws['C{}'.format(input_row)] = sold
    ws['D{}'.format(input_row)] = percentage
    ws['E{}'.format(input_row)] = date
    ws['F{}'.format(input_row)] = customer
    wb.save("sample.xlsx")
    enter_more = raw_input('Enter "y" to enter more data...').lower()