程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
您现在的位置: 程式師世界 >> 編程語言 >  >> 更多編程語言 >> Python

6000 words long, take you to complete in Python Excel (split) merger of various operations!

編輯:Python


一、概述


其實Excel合並這個需求,應該是一個極為普遍的需求了.今天我們就利用Python完成“Excel合並(拆分)” 操作,具體如下:

  • ① 將多個Excel表,合並到一個Excel中(每個Excel中只有一個sheet表);
  • ② 將多個Excel表,合並到一個Excel中(每個Excel中不只一個sheet表);
  • ③ 將一個Excel表中的多個sheet表合並,並保存到同一個excel;
  • ④ 將一個Excel表,按某一列拆分成多張表;

二、知識點講解


其實完成這些操作,涉及到了太多的知識點,因此在講述上述這個知識點以前,我們要帶大家復習一些常用的知識點.

  • ① os模塊常用知識點講解;
  • ② pandas模塊常用知識點講解;
  • ③ xlsxwriter模塊常用知識點講解;
  • ④ xlrd常用知識點講解;

1. os模塊知識點講解

對於os模塊,我們主要講述os.walk()、os.path.join()等知識點.

1.1 os.walk()

對於這個知識點,我們需要說明以下幾點:

  • os.walk()的返回值是一個生成器(generator),我們需要循環遍歷它,來獲取其中的內容;
  • 每次遍歷,返回的都是一個三元組(path, dirs, files);
  • path:返回的是當前正在遍歷的這個文件夾的,本身路徑地址;
  • dirs:返回的是該文件夾中所有目錄的名字(不包含子目錄),有多少個都以“列表”返回;
  • files:返回的是該文件夾中所有的文件(不包含子目錄下的文件),有多少個都以“列表”返回;

如果說,有一個如圖所示的文件夾.


利用下方的代碼,我們可以得到什麼結果呢?

pwd
=
"G:\\a"

print( os. walk( pwd))
for i in os. walk( pwd):
print( i)
for path, dirs, files in os. walk( pwd):
print( files)```
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

結果如下:

<
generator
object
walk
at
0x0000029BB5AEAB88
>

( 'G:\\a', [], [ 'aa.txt', 'bb.xlsx', 'cc.txt', 'dd.docx'])
[ 'aa.txt', 'bb.xlsx', 'cc.txt', 'dd.docx']
  • 1.
  • 2.
  • 3.
1.2 os.path.join()

這個函數,主要用於將多個路徑組合後返回,超級簡單,就不做過多闡述.

path1
=
'G:\\a'

path2 = 'aa.txt'
print( os. path. join( path1, path2))
  • 1.
  • 2.
  • 3.

結果如下:

G:\
a\
aa.
txt

  • 1.

2. pandas模塊知識點講解

由於是需要利用Pandas進行Excel的合並,因此我們要學會,如何利用Pandas進行數據的縱向合並.

我們先創建2個數據框(DataFrame):

import
numpy
as
np

xx = np. arange( 15). reshape( 5, 3)
yy = np. arange( 1, 16). reshape( 5, 3)
xx = pd. DataFrame( xx, columns =[ "語文", "數學", "外語"])
yy = pd. DataFrame( yy, columns =[ "語文", "數學", "外語"])
print( xx)
print( yy)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

效果如下:


接著,可以利用Pandas中的concat()函數,完成縱向拼接的操作.

  • pd.concat(list)中【默認axis=0】默認的是數據的縱向合並;
  • pd.concat(list)括號中傳入的是一個列表;
  • ignore_list=True表示忽略原有索引,重新生成一組新的索引;
  • 或者直接可以寫成z = pd.concat([xx,yy],ignore_list=True);
concat_list
= []

concat_list. append( xx)
concat_list. append( yy)
z = pd. concat( concat_list, ignore_list = True)
print( z)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

效果如下:

3. xlsxwriter模塊知識點講解

xlsxwriter模塊一般是和xlrd模塊搭配使用的,
xlsxwriter:負責寫入數據,
xlrd:負責讀取數據.
接下來,我們分別對這兩個庫的常見用法,進行介紹.

1)如何創建一個“工作簿”?
import
xlsxwriter


# 這一步相當於創建了一個新的"工作簿";
# "demo.xlsx"文件不存在,表示新建"工作簿";
# "demo.xlsx"文件存在,表示新建"工作簿"覆蓋原有的"工作簿";
workbook = xlsxwriter. Workbook( "demo.xlsx")

# close是將"工作簿"保存關閉,這一步必須有,否則創建的文件無法顯示出來.
workbook. close()
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
2)如何添加一個“Sheet工作表”

我們知道,一個Excel文件就是一個Excel工作簿,而每一個工作簿中,又有很多的“Sheet工作表”.接下來,我們如何用代碼實現這個操作呢?

import
xlsxwriter


workbook = xlsxwriter. Workbook( "cc.xlsx")
worksheet = workbook. add_worksheet( "2018年銷售量")
workbook. close()
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

效果如下:

3)如何向表中插入數據呢?
import
xlsxwriter


# 創建一個名為【demo.xlsx】工作簿;
workbook = xlsxwriter. Workbook( "demo.xlsx")

# 創建一個名為【2018年銷售量】工作表;
worksheet = workbook. add_worksheet( "2018年銷售量")

# 使用write_row方法,為【2018年銷售量】工作表,添加一個表頭;
headings = [ '產品', '銷量', "單價"]
worksheet. write_row( 'A1', headings)
# 使用write方法,在【2018年銷售量】工作表中插入一條數據;
# write語法格式:worksheet.write(行,列,數據)

data = [ "蘋果", 500, 8.9]
for i in range( len( headings)):
worksheet. write( 1, i, data[ i])
workbook. close()
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.

效果如下:

4. xlrd模塊知識點講解

這裡有一個工作簿“test.xlsx”,該文件中有兩個“Sheet工作表”,分別命名為“2018年銷售量”、“2019年銷售量”,如圖所示.

1)如何打開一個“工作簿”?—>open_workbook()
# 這裡所說的"打開"並不是實際意義上的打開,只是將該表加載到內存中打開.

# 我們並看不到"打開的這個效果"
import xlrd
file = r"G:\Jupyter\test.xlsx"
xlrd. open_workbook( file)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

結果如下:

<
xlrd.
book.
Book
at
0x29bb8e4eda0
>

  • 1.
2)如何獲取一個工作簿下,所有的“Sheet表”名?—>sheet_names()
import
xlrd

file = r"G:\Jupyter\test.xlsx"
fh = xlrd. open_workbook( file)
fh. sheet_names()
  • 1.
  • 2.
  • 3.
  • 4.

結果如下:

[
'2018年銷售量',
'2019年銷售量']

  • 1.
3)如何獲取所有“Sheet表”的對象列表?—>sheets()
import
xlrd

file = r"G:\Jupyter\test.xlsx"
fh = xlrd. open_workbook( file)
fh. sheets()
  • 1.
  • 2.
  • 3.
  • 4.

結果如下:

[
<
xlrd.
sheet.
Sheet
at
0x29bb8f07a90
>,
<
xlrd.
sheet.
Sheet
at
0x29bb8ef1390
>]

  • 1.

我們可以利用索引,獲取每一個sheet表的對象,然後可以針對每一個對象,進行操作.

fh.
sheets()[
0]

< xlrd. sheet. Sheet at 0x29bb8f07a90 >
fh. sheets()[ 1]
< xlrd. sheet. Sheet at 0x29bb8ef1390 >
  • 1.
  • 2.
  • 3.
  • 4.
4)如何獲取每個Sheet表的行列數?—>nrows和ncols屬性
import
xlrd

file = r"G:\Jupyter\test.xlsx"
fh = xlrd. open_workbook( file)
fh. sheets()
fh. sheets()[ 0]. nrows # 結果是:4
fh. sheets()[ 0]. ncols # 結果是:3
fh. sheets()[ 1]. nrows # 結果是:4
fh. sheets()[ 1]. ncols # 結果是:3
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
5)按行獲取,每個Sheet表中的數據—>row_values()
import
xlrd

file = r"G:\Jupyter\test.xlsx"
fh = xlrd. open_workbook( file)
sheet1 = fh. sheets()[ 0]
for row in range( fh. sheets()[ 0]. nrows):
value = sheet1. row_values( row)
print( value)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

效果如下:

三、案例講述

1. 將多個Excel表,合並到一個Excel中(每個Excel中只有一個sheet表)

有四張表,圖示中一目了然,就不做過多解釋.


實現代碼如下:

import
pandas
as
pd

import os
pwd = "G:\\b"
df_list = []
for path, dirs, files in os. walk( pwd):
for file in files:
file_path = os. path. join( path, file)
df = pd. read_excel( file_path)
df_list. append( df)
result = pd. concat( df_list)
print( result)
result. to_excel( 'G:\\b\\result.xlsx', index = False)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.

結果如下:

2. 將多個Excel表,合並到一個Excel中(每個Excel中不只一個sheet表)

有兩個工作簿,如圖所示.一個工作簿是pp.xlsx,一個工作簿是qq.xlsx.工作簿pp.xlsx下,有sheet1和sheet2兩個工作表.工作簿qq.xlsx下,也有sheet1和sheet2兩個工作表.


實現代碼如下:

import
xlrd

import xlsxwriter
import os

# 打開一個Excel文件,創建一個工作簿對象
def open_xlsx( file):
fh = xlrd. open_workbook( file)
return fh

# 獲取sheet表的個數
def get_sheet_num( fh):
x = len( fh. sheets())
return x

# 讀取文件內容並返回行內容
def get_file_content( file, shnum):
fh = open_xlsx( file)
table = fh. sheets()[ shnum]
num = table. nrows
for row in range( num):
rdata = table. row_values( row)
datavalue. append( rdata)
return datavalue

def get_allxls( pwd):
allxls = []
for path, dirs, files in os. walk( pwd):
for file in files:
allxls. append( os. path. join( path, file))
return allxls

# 存儲所有讀取的結果
datavalue = []
pwd = "G:\\d"
for fl in get_allxls( pwd):
fh = open_xlsx( fl)
x = get_sheet_num( fh)
for shnum in range( x):
print( "正在讀取文件:" + str( fl) + "的第" + str( shnum) + "個sheet表的內容...")
rvalue = get_file_content( fl, shnum)

# 定義最終合並後生成的新文件
endfile = "G:\\d\\concat.xlsx"
wb1 = xlsxwriter. Workbook( endfile)
# 創建一個sheet工作對象
ws = wb1. add_worksheet()
for a in range( len( rvalue)):
for b in range( len( rvalue[ a])):
c = rvalue[ a][ b]
ws. write( a, b, c)
wb1. close()
print( "文件合並完成")
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.

效果如下:

3. 將一個Excel表中的多個sheet表合並,並保存到同一個excel


實現代碼如下:

import
xlrd

import pandas as pd
from pandas import DataFrame
from openpyxl import load_workbook

excel_name = r"D:\pp.xlsx"
wb = xlrd. open_workbook( excel_name)
sheets = wb. sheet_names()

alldata = DataFrame()
for i in range( len( sheets)):
df = pd. read_excel( excel_name, sheet_name = i, index = False, encoding = 'utf8')
alldata = alldata. append( df)

writer = pd. ExcelWriter( r"C:\Users\Administrator\Desktop\score.xlsx", engine = 'openpyxl')
book = load_workbook( writer. path)
writer. book = book
# 必須要有上面這兩行,假如沒有這兩行,則會刪去其余的sheet表,只保留最終合並的sheet表

alldata. to_excel( excel_writer = writer, sheet_name = "ALLDATA")
writer. save()
writer. close()
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.

效果如下:

4. 將一個Excel表,按某一列拆分成多張表


實現代碼如下:

import
pandas
as
pd

import xlsxwriter
data = pd. read_excel( r"C:\Users\Administrator\Desktop\chaifen.xlsx", encoding = 'gbk')

area_list = list( set( data[ '店鋪']))

writer = pd. ExcelWriter( r"C:\Users\Administrator\Desktop\拆好的表1.xlsx", engine = 'xlsxwriter')
data. to_excel( writer, sheet_name = "總表", index = False)

for j in area_list:
df = data[ data[ '店鋪'] == j]
df. to_excel( writer, sheet_name = j, index = False)

writer. save() #一定要加上這句代碼,“拆好的表”才會顯示出來
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.

效果如下:



  1. 上一篇文章:
  2. 下一篇文章:
Copyright © 程式師世界 All Rights Reserved