Python generates the excel instance code and pythonexcel instance
This article provides an example of how to generate an excel file in python for your reference. The details are as follows:
# _ * _ Coding: UTF-8 _ * _ import MySQLdbimport xlwtfrom datetime import datetimedef get_data (SQL): # create a database connection. conn = MySQLdb. connect (host = '2017. 0.0.1 ', user = 'root', passwd = '000000', db = 'test', port = 123456, charset = 'utf8') # create a cursor cur = conn. cursor () # Run the query, cur.exe cute (SQL) # because the query statement returns only the number of affected records and does not return the actual value in the database, fetchall () is required here () to obtain all the content. Result = cur. fetchall () # close the cursor cur. close () # close the database connection conn. close # Return the result to the function caller. Return result def write_data_to_excel (name, SQL): # use SQL as a parameter to call get_data and assign the result to result (result is a nested tuples) result = get_data (SQL) # instantiate a Workbook () object (excel file) wbk = xlwt. workbook () # create an excel sheet named Sheet1. Here, cell_overwrite_ OK = True is used to repeat operations on the same cell. Sheet = wbk. add_sheet ('sheet1', cell_overwrite_ OK = True) # obtain the current date and obtain a datetime object such as :( 2016, 8, 9, 23, 12, 23,424 000) today = datetime. today () # obtain only the date of the datetime object, for example, 2016-8-9 today_date = datetime. date (today) # traverse no elements in result. For I in xrange (len (result): # traverse each sub-element of result, for j in xrange (len (result [I]): # Write each element of each row to the excel worksheet according to the row number I and column number j. Sheet. write (I, j, result [I] [j]) # Save with the name passed + current date as the excel name. Wbk.save(namedomainstr(today_date00000000'.xls ') # If the file is not imported, run the following code. If _ name _ = '_ main _': # defines a dictionary. The key is also used as the data type in excel, value is the query statement db_dict = {'test': 'select * from student '} # traverses the keys and values of each element in the dictionary. For k, v in db_dict.items (): # Call the write_data_to_excel function with every key and value in the dictionary. Write_data_to_excel (k, v)
The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.