开启今日学习之旅~

用Python读取Excel表，看看有些什么方法呢

我们先来看一下Excel表长什么样

 

然后我们来读取文件

import pandas as pd
books=pd.read_excel('E:/PycharmProjects/pythonProject/Hellopython/Books.xlsx')
print(books)

运行结果：

 

读出来的好像不是我们想要的，那么如何处理呢，就需要我们对参数进行设置

import pandas as pd
books=pd.read_excel('E:/PycharmProjects/pythonProject/Hellopython/Books.xlsx',usecols="C:F",skiprows=3)
print(books)

运行结果：

 

 增加了两个参数，首先跳过前面三行，然后使用C D E F列

可以看一下ID这列的数据类型

print(books['ID'])

运行结果：

 

 可以看到ID是float类型，因为表中为空的地方系统会自动填充为NaN，NaN为float类型，所以如果想要填充整数，可以将dtype设为int，但是系统会报错，不允许float转换为int，先将float转换为str。

import pandas as pd
from datetime import date,timedelta //导入时间
books=pd.read_excel('E:/PycharmProjects/pythonProject/Hellopython/Books.xlsx',usecols="C:F",skiprows=3,dtype={'ID':str,'InStore':str,'Date':str})
start=date(2018,1,1)
for i in books.index:
    books['ID'].at[i]=i+1
    books['InStore'].at[i]='Yes' if i%2==0 else 'No'
    books['Date'].at[i]=start+timedelta(days=i)//timedelta中只有days没有年月
print(books)

运行结果：

 

这里的Date是将天数加一，接下来看一下如何将年加一呢

import pandas as pd
from datetime import date,timedelta
books=pd.read_excel('E:/PycharmProjects/pythonProject/Hellopython/Books.xlsx',usecols="C:F",skiprows=3,dtype={'ID':str,'InStore':str,'Date':str})
print(books['ID'])
start=date(2018,1,1)
for i in books.index:
    books['ID'].at[i]=i+1
    books['InStore'].at[i]='Yes' if i%2==0 else 'No'
    books['Date'].at[i]=date(start.year+i,start.month,start.day)
print(books)

运行结果：

 

 

import pandas as pd
from datetime import date,timedelta
books=pd.read_excel('E:/PycharmProjects/pythonProject/Hellopython/Books.xlsx',usecols="C:F",skiprows=3,dtype={'ID':str,'InStore':str,'Date':str})
def add_month(d,md):
    yd=md//12
    m=d.month+md%12
    if m!=12:
        yd+=m//12
        m=m%12
    return date(d.year+yd,m,d.day)   //月增加时的小算法
start=date(2018,1,1)
for i in books.index:
    books['ID'].at[i]=i+1
    books['InStore'].at[i]='Yes' if i%2==0 else 'No'
    books['Date'].at[i]=add_month(start,i)
print(books)

运行结果：

 

 

books.set_index('ID',inplace=True)
books.to_excel('E:/PycharmProjects/pythonProject/Hellopython/output.xlsx')
print('done!')

输出新的Excel表格

 

 

 好啦，今天的学习之旅结束啦~