Shulou(Shulou.com)05/31 Report--

This article "Python how to achieve Excel data read and write" most people do not understand, so the editor summarized the following content, detailed, clear steps, with a certain reference value, I hope you can get something after reading this article, let's take a look at this "Python how to achieve Excel data read and write" article it.

1. Functional analysis

1. Load all Excel data in the folder

two。 Production contribution analysis chart (showing tabular data in bar chart)

3. Mention the specified column data in the Excel table

4. Data needed for directional filtering

5. Statistical ranking of multi-table data

6. Merge multi-table data with new excel files.

two。 System development environment

Anaconda3, in conda, window has the same function as python in ubuntu.

Pycharm .

3. Install dependent libraries

These dependent packages should be installed.

Import osimport xlrd2 # xlrd: read-related operations on Excel import xlwt # xlwt: write-related operations on Excel, and can only create a new Excel and then write and save. Import numpyimport matplotlibfrom prettytable import PrettyTable # PrettyTable is a third-party library in python that can be used to generate a beautiful table from matplotlib import pyplot as plt4 in ASCII format. Main function design

The main function main () of the Excel data analyst is mainly used to implement the main interface of the system. In the main function main (), first call the get_files_name () function to get the file name.

The code for the get_files_name () function is as follows:

# Import file def get_files_name (): "" to get the file name: return: return a list of file names "file_list = os.listdir ('. / data') return file_list"

Then call the load_data () function to read the excel file and save it in a dictionary.

# Save production excel table def load_data (file_list): "" used to read the specified file and save it to the dictionary data structure: param file_list: list of files to be loaded: return: dictionary with saved file contents "dictory = {} for file in file_list: # get table file book = xlrd2.open_ Workbook ('. / data/'+file) # get all the sheet names in the table = book.sheet_names () # get the first sheet sheet = book.sheet_by_index (0) # get the number of rows in the current table rows = sheet.nrows # get the number of columns in the current table cols = sheet.ncols # get the header file That is, the first row of the table head = sheet.row_values (0) for row in range (rows-1): # if the city is not in the current dictionary, create an if not sheet.cell_value (row+1, 0) in dictory.keys (): dictory [sheet.cell _ value (row+1) 0)] = {} for col in range (cols-1): dictory [sheet.cell _ value (row+1, 0)] [head [col + 1]] = float (sheet.cell_value (row+1, col+1)) return dictory

Then call the menu () function to generate the function selection menu.

The code for the menu () function is as follows:

# print menu def menu (): print ("- Excel data analyst -") print ("{:

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.