ValueError: Cannot Convert to Excel

Question

I am trying to run a program that gathers data then writes it to an existing excel file. I am running into an unexpected issue. My code is below:

import good_morning as gm
import numpy
fd = gm.FinancialsDownloader()
fd_frames = fd.download('AAPL')
wb = UpdateWorkbook(r'C:\Users\vince\Project\Spreadsheet.xlsx', worksheet=1)
df_2 = fd_frames['income_statement']
df_2.set_index('title', inplace=True)
df_2 = df_2.drop('parent_index', axis=1)
df_2 = df_2.loc[['Revenue','Operating expenses']] #Add all the names you   want from income statement
df_2 = df_2/(10**9)
wb['M6:N6'] = df_2.values
wb.save()

Here is the output of df_2.values:

array([[ 156.508,  170.91 ,  182.795,  233.715,  215.639,  220.457],
   [  13.421,   15.305,   18.034,   22.396,   24.239,   25.364]])

I keep getting an error that states:

ValueError: Cannot convert [ 156.508  170.91   182.795  233.715  215.639  220.457] to Excel

I am just trying to write these values to specific cells. I am so close to finishing the project, but ran into this unexpected error. Does anyone know how to fix this problem? Any help is greatly appreciated. Thank you

Here is the rest of my code:

class UpdateWorkbook(object):
    def __init__(self, fname, worksheet=0):
        self.fname = fname
        self.wb = load_workbook(fname)
        self.ws = self.wb.worksheets[worksheet]

    def save(self):
        self.wb.save(self.fname)

    def __setitem__(self, _range, values):
    """
     Assign Values to a Worksheet Range
    :param _range:  String e.g ['M6:M30']
    :param values: List: [row 1(col1, ... ,coln), ..., row n(col1, ... ,coln)]
    :return: None
    """

        def _gen_value():
            for value in values:
                yield value

            if not isinstance(values, (list, numpy.ndarray)):
                raise ValueError('Values Type Error: Values have to be "list": values={}'.
                                  format(type(values)))
            if isinstance(values, numpy.ndarray) and values.ndim > 1:
                raise ValueError('Values Type Error: Values of Type numpy.ndarray must have ndim=1; values.ndim={}'.
                              format(values.ndim))

        from openpyxl.utils import range_boundaries
        min_col, min_row, max_col, max_row = range_boundaries(_range)
        cols = ((max_col - min_col)+1)
        rows = ((max_row - min_row)+1)
        if cols * rows != len(values):
        raise ValueError('Number of List Values:{} does not match Range({}):{}'.
                         format(len(values), _range, cols * rows))

        value = _gen_value()
        for row_cells in self.ws.iter_rows(min_col=min_col, min_row=min_row,
                                       max_col=max_col, max_row=max_row):
            for cell in row_cells:
                cell.value = value.__next__()

The contents in the spreadsheet are complicated, however I just need replace the existing data in the spreadsheet. The code above should be able to do it. I am just running into that error whenever I use loc. I have gotten it to work when I set wb['M6:N6'] = df_2.values[0].

Solution

Try replacing the line wb['M6:N6'] = df_2.values with this nested for-loop:

r = 1  # start at first row
c = 13 # column 'M'
for l in df_2.values.tolist():
    for item in l:
        wb.ws.cell(row=r, column=c).value = item
        c += 1 # Column 'N'
    c = 13
    r += 1

and see it if works.

I stole the logic from this answer How to write a list to xlsx using openpyxl

Comments

[vdub32]

Yeah that worked!!!! However, for some reason my data has been transposed. I need the data to have 2 rows and 6 columns. Right now my data has 2 columns and 6 rows. Do you know how to fix this? Why did this happen? Sorry I just noticed this error @downshift

[vdub32]

any idea how to fix my problem in my previous comment? Any more help would be greatly appreciated, I am almost done with my project and just need this last bit of advice. Thank you for your time

[chickity china chinese chicken]

Yes of course no worries, I'm sorry that happened, I thought wb['M6:N6'] in your original code meant display the data on two columns, my mistake, I'll update the answer. If you want any more changes just let me know. Glad to help. :)