0

I am having some trouble finding the best way to write a list to a loaded excel sheet and then saving the result as an xlsx. What I want my code to do is to take the lists that I have created and put them in specific columns of the loaded workbook. I think there is probably an easier way to do this using dataframes, but I do not know how. Ideally, I would like to save the formatting of the loaded workbook. My code does not work as it stands. I am using Spyder from Anaconda.

col_test = [1L, 2L, 3L, 4L, 5L]

My code is this

import xlrt
from xlrd import open_workbook
rb = open_workbook("Excel FDT Master_01_update.xlsx")
s = rb.sheet_by_name('INPUT')
r = 5
for test in col_test:
    s.cell(row = r, column = 1).value = test
    r += 1
rb.save('didthiswork.xlsx')
nekomatic
  • 5,988
  • 1
  • 20
  • 27
boulderj
  • 11
  • 1
  • 5
  • It looks to me like you need Col 1 of row 5-9 assigned to each value in the list. Is that correct? – Adam Dec 07 '17 at 20:43
  • Yes, that is correct. Thanks. – boulderj Dec 07 '17 at 20:49
  • "does not work" - what happens? Is `xlrt` a typo for `xlrd`? As far as I know xlrd is only for the .xls format, not .xlsx, for which you [should use openpyxl](https://groups.google.com/forum/#!msg/python-excel/P6TjJgFVjMI/g8d0eWxTBQAJ). – nekomatic Dec 08 '17 at 09:08

1 Answers1

0

I'd use pandas

import pandas as pd

col_test = [1L, 2L, 3L, 4L, 5L]
data = pd.read_excel('workbook.xlsx', 'INPUT')

# Write to specific column name
start_row = 5
for i, val in enumerate(col_test):
    data.iloc[start_row + i, 'COL_NAME'] = val


writer = pd.ExcelWriter('output.xlsx')
data.to_excel(writer, 'NewSheet')

Assigning values to particular cell

read_excel

to_excel

Adam
  • 3,992
  • 2
  • 19
  • 39
  • ValueError: Can only index by location with a [integer, integer slice (START point is INCLUDED, END point is EXCLUDED), listlike of integers, boolean array] This is what I get when I try that. Thank you for the help. – boulderj Dec 07 '17 at 20:58
  • I am using 'B' as the COL_NAME – boulderj Dec 07 '17 at 21:02
  • Um. I would do `data.head()` to show the first 5 rows and verify that 'B' is the proper column name – Adam Dec 07 '17 at 21:03
  • can you explain what data.head() will do? I do not understand what you mean, apologies. – boulderj Dec 07 '17 at 21:45
  • Do `print(data.head())` and it will show you the first five rows with column names so you can get the actual column name to put into the code I provided in the answer – Adam Dec 07 '17 at 22:13