How to Write to an Excel Spreadsheet Using Python

How to write to an Excel spreadsheet using Python?

import xlwt

def output(filename, sheet, list1, list2, x, y, z):
    book = xlwt.Workbook()
    sh = book.add_sheet(sheet)

    variables = [x, y, z]
    x_desc = 'Display'
    y_desc = 'Dominance'
    z_desc = 'Test'
    desc = [x_desc, y_desc, z_desc]

    col1_name = 'Stimulus Time'
    col2_name = 'Reaction Time'

    #You may need to group the variables together
    #for n, (v_desc, v) in enumerate(zip(desc, variables)):
    for n, v_desc, v in enumerate(zip(desc, variables)):
        sh.write(n, 0, v_desc)
        sh.write(n, 1, v)

    n+=1

    sh.write(n, 0, col1_name)
    sh.write(n, 1, col2_name)

    for m, e1 in enumerate(list1, n+1):
        sh.write(m, 0, e1)

    for m, e2 in enumerate(list2, n+1):
        sh.write(m, 1, e2)

    book.save(filename)

for more explanation:
https://github.com/python-excel

How to write a data into an exist sheet of excel file by Python?

This is minmal example:

import openpyxl

wbkName = 'New.xlsx'
wbk = openpyxl.load_workbook(wbkName)
wks = wbk['test1']
someValue = 1337
wks.cell(row=10, column=1).value = someValue
wbk.save(wbkName)
wbk.close

The saving with the explicit name of the workbook seems to be quite important - wbk.save(wbkName), because only wbk.save does not do the job completely, but does not throw an error.

How can we write new data to existing Excel spreadsheet?

Please notes that Testing Append Process.xlsx file has to be created before running this code.

from openpyxl import load_workbook
import pandas as pd

data = {'Name': ['Tom', 'Joseph', 'Krish', 'John'], 'Age': [20, 21, 19, 18]}
df = pd.DataFrame(data)

filename = "Testing Append Process.xlsx"
workbook = load_workbook(filename)
writer = pd.ExcelWriter(filename, engine='openpyxl')
writer.book = workbook
writer.sheets = {ws.title: ws for ws in workbook.worksheets}

df.to_excel(writer, startrow=writer.sheets['Sheet1'].max_row, index = False, header= False)

writer.close()

Returns the following if you will run the code twice.

How to write a list of list into excel using python?

Here is one way to do it using XlsxWriter:

import xlsxwriter

new_list = [['first', 'second'], ['third', 'four'], [1, 2, 3, 4, 5, 6]]

with xlsxwriter.Workbook('test.xlsx') as workbook:
    worksheet = workbook.add_worksheet()

    for row_num, data in enumerate(new_list):
        worksheet.write_row(row_num, 0, data)

Output:

writing to excel sheet in python

How about rearranging columns within data frame df as needed, and then write to excel. That way you don't have to worry about excel end.

NOTE: Values in columns are fictitious.

>>> df = df[['Seq Label', 'TP', 'TN', 'FP', 'FN']]
>>> df
  Seq Label  TP  TN  FP  FN
0   if >= 1   1   1   1   1
1   if >= 2   2   2   2   2
2   if >= 3   3   3   3   3
3   if >= 4   4   4   4   4
4   if >= 5   5   5   5   5
5   if >= 6   6   6   6   6
6   if >= 7   7   7   7   7
>>> df.to_excel('Book10.xlsx', sheet_name='sheet1', index=False)

Result

Writing output to excel file in python

It looks that your input is some collections of lists. You can create an empty Dataframe and add each list as new rows separately. See below:

import pandas as pd

df=pd.DataFrame(columns=['Name', 'Amount', 'Spendings'])

for x in your_lists:
    for i in range(len(x[1])):
        df.loc[len(df)]=[x[0][0], x[1][i], x[2][i]]

df.set_index('Name', inplace=True)

>>> print(df)

     Amount  Spendings
Name
Alex     23       Food
Alex     40    Clothes
Andy     10  Telephone
Andy     23      Games

using python openpyxl to write to an excel spreadsheet (string searches)

I'd recommend creating an empty list and as you iterate through the column store each of the values in there with .append(), that should help your code scale a bit better, although i'm sure there will be other more efficient solutions.

I'd also recommend moving away from using == to check for equality and try using is, this link goes into detail about the differences: https://dbader.org/blog/difference-between-is-and-equals-in-python

So your code should look like this:

...
business_list = ['world wide data', 'other_businesses', 'etc']
for i in range(2, 94000):
    if(sheet.cell(row=i, column=6).value is not None):
        if(sheet.cell(row=i, column=6).value.lower() in business_list:
            sheet.cell(row=i, column=7).value = "World Wide Data"
...

Hope that helps

Edit to answer comments below

So to answer your question in comment 2, the business_list = [...] that we created will store anything that you want to check for. ie. if WWD, World Wide Data, 2467, etc. appear then you can check through this list, and if a match is found - which uses the in function - you can then write whatever you like into column 7. (final line of code).

If you want Machine operations or HumanResources or any of these other strings to appear there are a couple of methods that you can complete this. A simple way is to write a check for them like so:

...
business_list = ['world wide data', 'other_businesses', '2467',
                 'central operations', 'humanresources']
for i in range(2, 50000):
    if(sheet.cell(row=i, column=6).value is not None):
        if(sheet.cell(row=i, column=6).value.lower() in business_list:
            if business_list[i].lower() == "humanresources":
                sheet.cell(row = i, column = 7).value = "HumanResources"
            if business_list[i].lower() == "machine operations":
                sheet.cell(row = i, column = 7).value = "Machine Operations"
            else:
                 sheet.cell(row = i, column = 7).value = "World Wide Data"
...

So to explain what is happening here, a list is created with the values that you want to check for, called business_list. you are then iterating through your columns and checking that the cell is not empty with not None:. From here you do an initial check to see if the value of the cell is something that you want to even check for - in business_list: and if it is - you use the index of what it found to identify and update the cell value.

This ensures that you aren't checking for something that might not be there by checking the list first. Since the values that you suggested are one for one, ie. HumanResources for humanresources, Machine Operations for machine operations.

As for scaling, it should be easy to add new checks by adding the new company name to the list, and then a 2 line statement of if this then cell = this.

I use a similar system for a sheet that is roughly 1.2m entries and performance is still fast enough for production, although I don't know how complex yours is. There may be other more efficient means of doing it but this system is simple to maintain in the future as well, hopefully this makes a bit more sense for you. let me know if not and i'll help if possible

EDIT: As for your last comment, I wouldn't assume something like that without doing a check since it could lead to false positives!

---

Related Topics