Shulou(Shulou.com)06/03 Report--

This article mainly explains "python reads data in Excel and how to write input into Excel". The content in the article is simple and clear, and it is easy to learn and understand. Please follow Xiaobian's train of thought to study and learn "python reads data in Excel and writes input into Excel".

I. brief introduction

Sometimes we need to write data to the Excel containing VBA code, but can not affect the normal VBA code execution, at first I use python's openpyxl module function to write the data into the xlsm file, after writing the data, I found that the button to execute the VBA code disappeared, so by looking for the reason, I found that it is because openpyxl is not friendly to VBA support, while the friendly support for VBA is the xlwings module.

Second, a brief introduction to the xlwings module

1. Read the data in Excel

Note that you should read:

By default, cells with numbers are read as float, cells with dates are read as datetime.datetime, and empty cells are converted into None; data reads that can be read in a format specified by the option operation.

Import xlwings as xwimport os# creates APP application app=xw.App (visible=True,add_book=False) # visible indicates whether the program is visible when the program is running Excel,True means visible and False means invisible Add_book indicates whether to create a new workbook file = "data writing V1.xlsm" wb=app.books.open (file) # Open the specified file ws = wb.sheets ["Sheet1"] # worksheet reference # ws.activate () temp_value = ws ["B2"] .value # read the value of B2 by default Read the value of B3 by default for floating-point print (type (temp_value)) print (temp_value) temp_n = ws ["B3"] .value #, where non-null values should display Noneprint (type (temp_n)) print (temp_n) temp_value1 = ws ["B2"] .options (numbers=int). Value # sets B2 to the integer print (type (temp_value1) print (temp_value1)

# run result

100.0

None

one hundred

> > >

2. Import xlwings as xwimport osapp=xw.App (visible=True,add_book=False) file = "data written to V1.xlsm" wb=app.books.open (file) # Open the specified file ws = wb.sheets ["Sheet1"] print (ws.range ('B2'). Value) # another way to read the value of B2 # run result 100.0, write the data to Excel

Import xlwings as xwimport os# creates APP application app=xw.App (visible=True Add_book=False) file = "data writing V1.xlsm" wb=app.books.open (file) # Open the specified file # worksheet reference ws = wb.sheets ["Sheet1"] a = 6799b = 2345c = 1000info = ws.used_range#print (info) nrows = info.last_cell.row # get the largest row print (nrows) if ws ['B'+str (nrows) in the sheet table )] = None: ws ['B'+str (int (nrows)-1)] .value=a ws [' C'+str (int (nrows)-1)] .value=b ws ['D'+str (int (nrows)-1)] .value=celse: ws [' B'+str (int (nrows) + 1)]. Value=a ws ['C'+str (int (nrows) + 1)]. Value=b ws [' D'+str (int (nrows) + 1)]. Value=c wb.save () # Save data wb.close () # close workbook app.quit ()

After writing

Thank you for your reading, the above is the content of "python reading data in Excel and the method of writing input into Excel". After the study of this article, I believe you have a deeper understanding of the problem of python reading data in Excel and the method of writing input into Excel, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.