CODEWITHSUNDEEP
pythonexcelopenpyxl
vdub32
vdub32

Reputation: 203

ValueError: Cannot Convert to Excel

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

Upvotes: 5

Views: 33233

Answers (1)

chickity china chinese chicken
chickity china chinese chicken

Reputation: 8057

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

Upvotes: 3

Related Questions