CODEWITHSUNDEEP
pythonexcelpython-module
Jatinder Singh Brar
Jatinder Singh Brar

Reputation: 431

how to handle excel file (xlsx,xls) with excel formulas(macros) in python

I need to pass inputs from Input_data.xls in iteration to existing xls file which have special function at various cells using python3.6. These function change primary data in existing xls as per inputs. But when xlrd open the file it doesn't import the xls cell function and save file file with modification. And write object name instead of its value

Python code:

import xlrd
import xlwt
import xlutils 
from xlrd import open_workbook
from xlutils.copy import copy 
import os.path

book = xlrd.open_workbook('input_data.xlsx')
sheet0 = book.sheet_by_index(0)
for i in range (sheet0.nrows):
    st1=sheet0.row_values(i+1)
    TIP=[st1[0]]
    OOIPAN_IP=[st1[1]]
    NM=[st1[2]]
    book1 = xlrd.open_workbook('primary_data.xls')
    wb=copy(book1)
    w_sheet=wb.get_sheet(0)
    w_sheet.write(1,0,'TIP')
    w_sheet.write(1,1,'OIP')
    w_sheet.write(1,2,'NM')
    wb.save('ipsectemp.xls')

write object name in cells instead of object's vlaue

input 1 input 2 input 3

st1[0]  st1[1]  st1[2]

which module can help to open/read/write workbook with its excel functions (macros) in python.

Upvotes: 1

Views: 271

Answers (1)

Jatinder Singh Brar
Jatinder Singh Brar

Reputation: 431

Luckly, i found below code that can fetch excel macros, openpyxl module does good work using cell values

    book = load_workbook('primary_data.xlsx') #open ipsec file with desired inputs
    sheet0 = book.get_sheet_by_name('Sheet1')
    for row in range(2,sheet0.max_row+1):  
        for column in "A":  #Here add or reduce the columns
            cell_name = "{}{}".format(column, row)
            textlt=sheet0[cell_name].value
            print(textlt)

information extracted from this answer openpyxl - read only one column from excel file in python? used information other way

Upvotes: 1

Related Questions