Start with a BB

Basically, this is the routine for the weekly paper I write every Friday.

Suddenly want to use Python intelligent office, modify Excel spreadsheet.

Forget about merging cells, changing table styles. Just do a simple read and write first.

The operation process

Install Python

If you want to do a good job, you must first sharpen your tools. The development environment is essential.

Download the installation package directly from the official website. I used version 3.6.5. After downloading and installing, configure environment variables.

Development tools, I will directly use vscode, installed a python plug-in.

To set up the Python select interpreter, press CTL + Alt + P.

Pandas official website

pandas.pydata.org/

Pandas

www.pypandas.cn/

Pandas installation package

The XLRD and OpenPyXL dependency packages are required for pandas to process Exce L

pip install pandas
pip install xlrd
pip install openpyxl
Copy the code

Learn to use

As the website shows, it’s as simple as 1, 2, 3

# 1$PIP install pandas #2$python -i #3, use Pandas >>>import pandas as pd
>>> df = pd.DataFrame() 
>>> print(df)

# 4Empty DataFrameColumns: []
Index: []
Copy the code

Create Excel and write data

import  pandas  as pd
from pandas importNdarray dic = {DataFrame = dic;'Header column 1': ['malena'.'morgan'].'Header column 2': [36.34]
       }
df = pd.DataFrame(dic)
df.to_excel('write_test.xlsx', index=False)
Copy the code

Execute the py file, write successfully, feel very nice.

The pandas DataFrame object is imported

import pandas as pd
from pandas import DataFrame
Copy the code

Write to Excel file, official example:

df.to_excel('foo.xlsx', sheet_name='Sheet1')
Copy the code

Read Excel file, official example:

 pd.read_excel('foo.xlsx'.'Sheet1', index_col=None, na_values=['NA'])
Copy the code

Here’s a little pit:

The reason is that XLRD has recently been updated to version 2.0.1, which only supports.xls files. So pandas. Read_excel (‘ xxx.xlsx ‘) will report an error.

You can install the old XLRD and run it in CMD:

pip uninstall xlrd
pip install xlrd==1.2. 0
Copy the code

You can also open.xlsx files with OpenPyxl instead of XLRD:

Df = pandas read_excel (' data. XLSX, engine = 'openpyxl)Copy the code

Read the Excel file and print it out

data = pd.read_excel('zmy-weekly.xlsx', sheet_name='march', engine='openpyxl')
print(data)
Copy the code

The print result is as follows:

Alter data by modifying the value of data.loc. Data. loc can be roughly understood as a two-dimensional array, corresponding to each row and each column of cells.

For example, change “Work item 2” to “Work item 5”, row 2, column 2, corresponding order number cell

 data.loc[3] [2] = 'Business Item 5';
Copy the code

The sheet_name can be set to “March”, for example.

 DataFrame(data).to_excel('new.xlsx', sheet_name='march', index=False, header=True)
Copy the code

The complete code is as follows:

import pandas as pd
from pandas import DataFrame

# 3.82.Def write_weekly(): data = pd.read_excel()'zmy-weekly.xlsx', sheet_name='march', engine='openpyxl')
    print(data)

    print(data.loc)

    data.loc[2] [0] = 'March 5 weeks';
    data.loc[2] [2] = 'Business Item 4';
    data.loc[3] [2] = 'Business Item 5';
    data.loc[4] [2] = 'Business Item 6';

    data.loc[2] [4] = 'Done';
    data.loc[3] [4] = 'Done';
    data.loc[4] [4] = 'Done';

    data.loc[5] [2] = 'Another temp job.';


    data.loc[6] [0] = 'Week of April 1';

    data.loc[6] [2] = 'Business Item 7';
    data.loc[7] [2] = 'Business 8';

    data.loc[6] [4] = 'in progress';
    data.loc[7] [4] = 'in progress'; # save DataFrame(data).to_excel('new.xlsx', sheet_name='march', index=False, header=True)


write_weekly();
Copy the code

The style is different, but it’s not a problem. Open last week’s weekly, select all, then format brush, click the new weekly, aha.

gain

Anyway, I’ve already opened Excel, so why don’t I just change it faster?

The basic operations for reading and writing Excel in Python are: From now on, from time to time.