NameError:

[azizrasul]

I get an error

NameError: name 'add_row_data_by_cell_coordinate' is not defined

in the following snippet of code.

# insert data in work_sheet_1.
row_title_list = ('Student Name', 'Class Room', 'Score')
row_data_list = (('Mike', 'Class Room 1', 100), ('John', 'Class Room 2', 99), ('Steve', 'Class Room 3', 100))
add_row_data_by_cell_coordinate(work_sheet_1, row_title_list, row_data_list)

[Larz60+]

I don't see 'add_row_data_by_cell_coordinate' in your code.
So error is justified.
you need to add (or import) function 'add_row_data_by_cell_coordinate'

[azizrasul]

Tried

from openpyxl import add_row_data_by_cell_coordinate

but I get the error

ImportError: cannot import name 'add_row_data_by_cell_coordinate' from 'openpyxl' (D:\Power BI & Python\venv\lib\site-packages\openpyxl\__init__.py)

[rob101]

Tried

from openpyxl import add_row_data_by_cell_coordinate

but I get the error

ImportError: cannot import name 'add_row_data_by_cell_coordinate' from 'openpyxl' (D:\Power BI & Python\venv\lib\site-packages\openpyxl\__init__.py)

Where did the object name add_row_data_by_cell_coordinate come from? How do you know that name? It's not (so far as I can see) a part of the openpyxl library.

[azizrasul]

It comes from a website, wasn't sure whether I can mention it here according to the rules.

[rob101]

It comes from a website, wasn't sure whether I can mention it here according to the rules.

Okay, that's fair enough.

Looking at this line of code
add_row_data_by_cell_coordinate(work_sheet_1, row_title_list, row_data_list)
... I'd say that it's custom function, so maybe you can find that from said website. If not, then the function will have to be reverse engineered.

[azizrasul]

This is the whole code given on the web page.

'''
Created on Sep 23, 2020

@author: songzhao
'''

import openpyxl

from openpyxl import Workbook

from openpyxl import load_workbook

from openpyxl.styles import PatternFill

import os

import string

default_excel_file_name = 'test_default_excel.xlsx'

excel_file_name = 'test_excel.xlsx'


'''
   This function will create a default excel file. The excel file has three work sheet (Sheet, Sheet1, Sheet2).
   
   Sheet1 has 
   
'''
def create_default_excel_file(file_name=default_excel_file_name):
    
    # create a Workbook object.
    work_book = Workbook()
    
    # invoke Workbooc.active property to create the initial work sheet.
    initial_work_sheet = work_book.active
    
    # work book has a initial work sheet.
    initial_sheet_names = work_book.sheetnames
    print(initial_sheet_names)
    
    # create 2 work sheet object.
    work_sheet_1 = work_book.create_sheet()
    work_sheet_2 = work_book.create_sheet()
    
    print("Initial work sheet title :", initial_work_sheet.title)
    
    print("Work sheet 1 title :", work_sheet_1.title)
    
    print("Work sheet 2 title :", work_sheet_2.title)
    
    
    # insert data in work_sheet_1.
    row_title_list = ('Student Name', 'Class Room', 'Score')
    row_data_list = (('Mike', 'Class Room 1', 100),('John', 'Class Room 2', 99),('Steve', 'Class Room 3', 100))
    add_row_data_by_cell_coordinate(work_sheet_1, row_title_list, row_data_list)
    
    
    # insert data in work_sheet_2
    row_title_list = ('Teacher Name', 'Teacher Salary')
    row_data_list = (('Tom', 9000),('Jerry', 18000))
    add_row_data_by_cell_name(work_sheet_2, row_title_list, row_data_list)
    
    # save the Workbook object to a local excel file.
    work_book.save(file_name)   
    
    
'''
   This function will create another excel file, and put the work sheet as your required order. 
'''    
def create_excel_file(file_name=excel_file_name):
    
    # create a Workbook object.
    work_book = openpyxl.Workbook()
    
    # get the initial work sheet object.
    initial_work_sheet = work_book.active
    # set the initial work sheet title.    
    initial_work_sheet.title = 'User Info'   
    # set the initial work sheet tab color. 
    initial_work_sheet.sheet_properties.tabColor = '00FF00'
    
    # create the second work sheet with title and put it as the first work sheet..   
    work_sheet_1 = work_book.create_sheet("User Account", 0)
    # set the second work sheet tab color.
    work_sheet_1.sheet_properties.tabColor = "FF0000"    
    
    # add data to the second work sheet.
    row_title_list = ('User Name', 'Password', 'Email')
    row_data_list = (('Jerry', '12345678', '[email protected]'), ('Tom', '987', '[email protected]'))
    add_row_data_by_append(work_sheet_1, row_title_list, row_data_list)
        
    # create the third work sheet, set it position at one before the last work sheet.     
    work_sheet_2 = work_book.create_sheet("User Salary", -1)
    # set the third work sheet tab color.
    work_sheet_2.sheet_properties.tabColor = "0000FF"
    
    work_book.save(file_name)
   
   
    
'''
    Insert excel data use Worksheet's append() method.
'''  
def add_row_data_by_append(work_sheet, row_title_list, row_data_list):
    
    # insert excel table title.
    work_sheet.append(row_title_list)
        
    for row_data in row_data_list:
            
        print(row_data)
        
        work_sheet.append(row_data)
        
         
'''
   Insert cell data by cell name.

'''         
def add_row_data_by_cell_name(work_sheet, row_title_list, row_data_list):

    # add excel row title.
    row_title_list_len = len(row_title_list)    

    if row_title_list_len > 0:
        
        for i in range(row_title_list_len):
            
            # get cell name('A1', 'B1', 'C1'...... etc. )
            col_name = string.ascii_uppercase[i] + str(1)
            
            # get cell value.
            col_value = row_title_list[i]
            
            # assign cell value to related cell by cell name.
            work_sheet[col_name] = col_value 
                        
            # Fill cell with blue color.
            #https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.0/openpyxl/styles/fills.py
            blue_fill = PatternFill(start_color='0000FFFF', end_color='FFFF0000', fill_type='solid')
            work_sheet[col_name].fill = blue_fill

     
     
    
    # add excel row data.
    row_data_list_len = len(row_data_list)    

    if row_data_list_len > 0:
        
        for i in range(row_data_list_len):
            
            row_data = row_data_list[i]
            
            col_len = len(row_data)
            
            
            for j in range(col_len):
            
                col_name = string.ascii_uppercase[j] + str(i + 2)
            
                col_value = row_data[j]
            
                work_sheet[col_name] = col_value
                
                
'''
   Insert row data in to excel sheet use cell's coordinate.
'''
def add_row_data_by_cell_coordinate(work_sheet, row_title_list, row_data_list):

    # add excel row title.
    row_title_list_len = len(row_title_list)    

    if row_title_list_len > 0:
        
        for i in range(row_title_list_len):
            
            # get cell row and column number.
            cell_row = 1
            cell_col = i + 1
            
            # get cell value.
            cell_value = row_title_list[i]
            
            # insert cell value at special row and column.
            cell = work_sheet.cell(row=cell_row, column=cell_col, value=cell_value)           
            
            # Fill cell with red color.
            #https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.0/openpyxl/styles/fills.py
            red_fill = PatternFill(start_color='FFFF0000', end_color='0000FFFF', fill_type='solid')
            cell.fill = red_fill

     
     
    
    # add excel row data.
    row_data_list_len = len(row_data_list)    

    if row_data_list_len > 0:
        
        for i in range(row_data_list_len):
            
            row_data = row_data_list[i]
            
            col_len = len(row_data)
            
            
            for j in range(col_len):
                
                cell_row = i + 2
                
                cell_col = j + 1
                        
                cell_value = row_data[j]
            
                work_sheet.cell(row=cell_row, column = cell_col, value=cell_value)
                     
             
'''
   Load an exist excel file and read the excel row data.
'''    
def load_excel_file(file_name):
    
    # check whether the excel exist or not.
    if os.path.exists(file_name):
        
        print('Excel file ', file_name, ' exist. ')
        
        # load the excel file and return the Workbook object.
        work_book = load_workbook(file_name)
        
        # get all excel sheet name list.
        work_sheet_names = work_book.sheetnames
        
        print('Excel file ', file_name,' has following work sheet :')
        
        print(work_sheet_names)

        # loop in each excel sheet.
        for sheet_name in work_sheet_names:
            
            
            print("********* Excel Sheet Name : ", sheet_name, ' *********')
            
            # loop excel sheet rows.
            for row in work_book[sheet_name].iter_rows():
                
                row_str = ''
                
                # loop row cells.
                for cell in row:
                    
                    # add each cell value to the row string.
                    row_str = row_str + cell.value + ', ' 
                    
                print(row_str)    
     
    else:
        
        print('Excel file ', file_name, ' do not exist. ')

if __name__ == '__main__':
    
    create_default_excel_file()
    
    create_excel_file()
    
    load_excel_file(excel_file_name)

[rob101]

Okay, well this is the function, so far as I can see:

def add_row_data_by_cell_coordinate(work_sheet, row_title_list, row_data_list):
 
    # add excel row title.
    row_title_list_len = len(row_title_list)    
 
    if row_title_list_len > 0:
         
        for i in range(row_title_list_len):
             
            # get cell row and column number.
            cell_row = 1
            cell_col = i + 1
             
            # get cell value.
            cell_value = row_title_list[i]
             
            # insert cell value at special row and column.
            cell = work_sheet.cell(row=cell_row, column=cell_col, value=cell_value)           
             
            # Fill cell with red color.
            #https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.0/openpyxl/styles/fills.py
            red_fill = PatternFill(start_color='FFFF0000', end_color='0000FFFF', fill_type='solid')
            cell.fill = red_fill
 
      
      
     
    # add excel row data.
    row_data_list_len = len(row_data_list)    
 
    if row_data_list_len > 0:
         
        for i in range(row_data_list_len):
             
            row_data = row_data_list[i]
             
            col_len = len(row_data)
             
             
            for j in range(col_len):
                 
                cell_row = i + 2
                 
                cell_col = j + 1
                         
                cell_value = row_data[j]
             
                work_sheet.cell(row=cell_row, column = cell_col, value=cell_value)

[deanhystad]

By itself you cannot run this:

# insert data in work_sheet_1.
row_title_list = ('Student Name', 'Class Room', 'Score')
row_data_list = (('Mike', 'Class Room 1', 100), ('John', 'Class Room 2', 99), ('Steve', 'Class Room 3', 100))
add_row_data_by_cell_coordinate(work_sheet_1, row_title_list, row_data_list)

Somewhere else in the same file you need this:

def add_row_data_by_cell_coordinate(work_sheet, row_title_list, row_data_list):

    # add excel row title.
    row_title_list_len = len(row_title_list)
    ...
                work_sheet.cell(row=cell_row, column=cell_col, value=cell_value)

This defines the function so it can be used elsewhere.

[rob101]

Is there a reason that you're picking out particular parts, rather than using the entire script?