Pythonを使用してexcelテーブルを処理する(openpyxl)チュートリアル

簡単なフィルタ機能であっても、大量のデータを処理する必要がある場合、時間がかかってしまいます。より効率的な方法は、Pythonでスクリプトを作成してバッチ処理します。 pythonの外部パッケージopenpyxlは、excelファイルを簡単に読み書きできます。次には操作方法を説明します。

1、pipまたはeasy_installからopenpyxlをインストールできます。 openpyxl公式ウェブサイト:

https://openpyxl.readthedocs.org/en/latest/

インストールコメント:

pip install openpyxl (オンラインでインストール)または easy_install openpyxl

2、openpyxlを使用してxlsx形式のワークブックを読み取り、openpyxlはxlsx形式のみ対応できるることに注意してください。古いxls形式では、他の開き方で開く必要があります。

wb = load_workbook(filename = r'tj.xlsx')

各シートの名前を取得します

sheetnames = wb.get_sheet_names()

最初のシートを取得します

ws = wb.get_sheet_by_name(sheetnames[0])

セルのデータを取得します

c = ws['A4']

或は

c = ws.cell('A4')

或は

d = ws.cell(row = 4, column = 2)

一回に複数のセルのデータを取得します

cell_range = ws['A1':'C2']

或は

tuple(ws.iter_rows('A1:C2'))

或は

for row in ws.iter_rows('A1:C2'):  
for cell in row:  
<span style="white-space:pre">    </span>print cell

或は

data_dic = []  
for rx in range(0,ws.get_highest_row()):  
temp_list = []   
money = ws.cell(row = rx,column = 1).value  
kind  = ws.cell(row = rx,column = 2).value  
temp_list = [money , kind]  
#print temp_list  
data_dic.append(temp_list)  
for l in data_dic:  
print l[0],l[1]

3、xlsxに書き込みます。例えば、上記で定義したdata_dicに保存します。

out_filename = r'result.xlsx'  
outwb = Workbook()  
ew = ExcelWriter(workbook = outwb)  
ws = outwb.worksheets[0]  
ws.title = "res"  
i=1  
for data_l in data_dic:  
for x in range(0,len(data_l)):  
#col = get_column_letter(x)  
ws.cell(column = x+1 , row = i , value = "%s" % data_l[x])  
i+=1  
ew.save(filename = out_filename)

コンテンツを書くために新規シートを作成します

ws2 = outwb.create_sheet(title = 's2')  
for data_l in data_dic:  
for x in range(0,len(data_l)):  
ws2.cell(column = x+1 , row = i , value = "%s" % data_l[x])  
i+=1  
ew.save(filename = out_filename)

4、Openpyxlは、日本語のコーディングの値をさまざまなタイプに自動的に変換します。一部のテーブルには日本語が表示されるため、それに応じてトランスコードする必要があります。トランスコードするための関数を作成し、必要の場合に呼び出すことができます。

def gbk2utf(in_data , tag):  
if 1 == tag:  
return in_data.encode('jis').decode('jis')  
elif 0 == tag:  
return in_data.encode('jis').decode('jis').encode('utf8')

元のexcelファイルがjisコーディングである場合、tag=0で処理する必要があります。

読み取り後はjisコーディングであるため、jisにエンコードし、unicodeにデコードしてから、utf8にエンコードする必要があります。これより表示できます。

実例

次には処理するフォームを説明します。これは報酬に関するフォームです。各月も含まれます。書式は固定されています。Pythonを使用してスクリプトを記述し、独自のニーズを実現できます。これより、コマンドラインを押すだけで、必要なフォームを生成し、時間を大幅に節約できます。報酬は大きな種目に分かれ、大きな種目には小さな種目がたくさんあります。やることは大きな種目にある各小さな種目の金額を合計し、新しいテーブルに記述します。テーブルには各大きな種目の合計で、これよりチャートを作成します。

ソースコードをここに直接貼り付けます。機密情報が含まれている可能性があるため、元のコメントがXXXXに置き換えられ、画像がモザイクで表示されこともあります。

from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook("C:/Users/Administrator/Desktop/報酬.xlsx")
ws = wb.get_sheet_by_name('報酬明細')
ws_rows_len = len(ws.rows)          #行数
ws_columns_len = len(ws.columns)    #列数
#xxxxxxx
shop_name_column = 5
#xxxxx
user_start = 8
user_end = 23
#xxxxx
terminal_start = 24
terminal_end   = 35
#xxxxx
infomation_start = 36
infomation_end   = 42
#xxxx
group_start = 43
group_end   = 45
#xxxx
commission_start = 46
commission_end   = 60
#xxxx
stimulate_start = 61
stimulate_end   = 65
#xxxx
net_start = 66
net_end   = 67
#xxxxxxx
agreement_start = 68
agreement_end   = 70
#データの有効行は4行目から始まります        
start_row = 4
temp_data = []
for row in range(start_row, ws_rows_len+1):
t = 0
temp_data.append(ws.cell(row=row,column=shop_name_column).value)
#xxxxxxの合計金額を計算します
for column in range(user_start,user_end+1):
t += ws.cell(row=row,column=column).value
temp_data.append(t)
t = 0
#xxxxの合計金額を計算します
for column in range(terminal_start,terminal_end+1):
t += ws.cell(row=row,column=column).value
temp_data.append(t)
t = 0
#计xxxxの合計金額を計算します
for column in range(infomation_start,infomation_end+1):
t += ws.cell(row=row,column=column).value
temp_data.append(t)
t = 0
#xxxxの合計金額を計算します
for column in range(group_start,group_end+1):
t += ws.cell(row=row,column=column).value
temp_data.append(t)
t = 0
#xxxxxの合計金額を計算します
for column in range(commission_start,commission_end+1):
t += ws.cell(row=row,column=column).value
temp_data.append(t)
t = 0
#xxxxxの合計金額を計算します
for column in range(stimulate_start,stimulate_end+1):
t += ws.cell(row=row,column=column).value
temp_data.append(t)
t = 0
#xxxxxの合計金額を計算します
for column in range(net_start,net_end+1):
t += ws.cell(row=row,column=column).value
temp_data.append(t)
t = 0
#xxxxxxxxの合計金額を計算します
for column in range(agreement_start,agreement_end+1):
t += ws.cell(row=row,column=column).value
temp_data.append(t)
#上記のコードは、報酬のローデータを取得して合計します
#次には合計金額を新しいデータテーブルに挿入します
reward_ws_hearder = ['xxxx','xxxxx','xxxx','xxxxx','xxxxxx','xxxx','xxxx','xxxx','xxxx']
reward_ws = wb.create_sheet(title='報酬分析')
reward_ws.append(reward_ws_hearder)
start_list = 0
list_step = 9
end_list = start_list + list_step
for i in range(0,ws_rows_len-start_row+1):
reward_ws.append(temp_data[start_list:end_list])
start_list += list_step
end_list = start_list + list_step
wb.save("C:/Users/Administrator/Desktop/報酬1.xlsx")  #ローデータを誤操作で破壊されないように、他の場所に名前を付けて保存します。

上記のコードは少々乱雑かもしれませんが、openpyxlを理解するための参考ものになります。上記のコードにより、合計したデータを他の場所に名前を付けて保存することを実現しました。(ローデータには多くの列がありますが、今は統合されています。)

モザイクになっている列Aは名前で、最初の行が業務の分類です。表示されるデータは合計金額です。スクリプト処理を使用しない場合、excelを使用して手動操作を実行するたびに、各大きい種目の小さい種目を合計し、手動で新しいテーブルに貼り付ける必要があります。(Excelにあまり詳しくないなら、描画操作の実行には時間がかかります。)今はコードを書くのに時間を費やしましたが、将来的には、テーブルの形式が変更されない限り、pyファイルを実行するだけで操作が自動的に実行し、1秒で完了します。

次の記事ではxlsxwriterライブラリを説明します。ここまで説明すると、openpyxlを使用して描画したかったのですが、openpyxlが需要を満たせません。そのため、データの書き込みと画像の描画にすごく便利なxlsxwriterを見つけました。これにより、ドキュメントのより豊富で完備し、。多くの例があります。

この例を通して、発見力や思考力を向上させ、そして以前に学んだ知識を新しいお仕事に活かしていきます。

附録

openpyxlライブラリの勉強

1、wb = Workbook()

2、シートの操作範囲を取得します

1）#ワークシートをアクティブ化し、ワークシートを取得し、デフォルトで

sheet1を取得します。ws = wb.active

2）# sisilast形式に名前を付け、最後に挿入します。

ws2 = wb.create_sheet("sisilast")

3）#sisilast形式に名前を付け、最初に挿入します

ws3=wb.create_sheet("sisifrist",0)

3、指定したセルに値を割り当てます

1）指定したセルに直接データを書き込みます

>>> ws ['A1'] = 100

2）openpyxl.worksheet.Worksheet.cell()このメソッドで、行と列の値を操作します

>>> ws.cell(row=6,column=5,value='happy')

3.1、注意事項

ワークシートをメモリに作成する場合、セルは含まれていません。セルは、初めてアクセスしたときに作成されます。

ループでメモリにセルを作成できます。値を指定しなくても、セルを作成します。(8×8セルを作成します)

>>> for i in range(1,9): 
for j in range(1,9):
ws.cell(row=i,column=j,value=i*j)

openpyxl.worksheet.Worksheet.iter_rows()

メソッドを使用することもできます:(行->行を指定し、締め切る列を指定する必要があります）

openpyxl.worksheet.Worksheet.iter_cols()

メソッドを使用することもできます:(列->列を指定し、締め切る行を指定する必要があります）

すべてのファイルの行または列を確認する必要がある場合は、openpyxl.worksheet.Worksheet.rows() プロパティー:

>>>tuple(ws.rows)

openpyxl.worksheet.Worksheet.columns() プロパティー:

>>>tuple(ws.columns)

4、最初の行から始まる行を追加します

ws.append([1,2,3])

5、#ファイルを指定した場所に保存します

wb.savewb.save("D:/test/test.xlsx")

6、シート名を変更します

作成されたシートの名前は自動的に作成され、sheet、sheet1、sheet2に従って自動的に命名され、titleプロパティーを使用して名前を変更できます。

>>> from openpyxl import Workbook
>>> wb = Workbook()>>> ws = wb.create_sheet("haha")>>> ws.title = "heihei">>> wb.save("D:/test/heihei.xlsx")

7、ワークブック内のすべてのワークシートの名前を表示します

openpyxl.workbook.Workbook.get_sheet_names()
>>> print(wb.sheetnames)['Sheet', 'heihei']

8、セルを検索します

>>> value1 = ws['A3']

9、openpyxl.load_workbook()既に存在しているworkbook：

10、式を使用する

openpyxlの基礎的な使用について

Pythonでexcelを操作するやり方

Excelを操作することに対応するpythonコーディングにはWin32 COMがありますが、著者ははっきり説明できません。クロスプラットフォームに対応できないかもしれないため、Linuxオペレーティングシステムに使用できるかどうかわかりません。次には他の処理モジュールを説明します。

xlrd :(excelを読み取り）テーブル、大きなテーブルを読み取るにはxlrdはopenpyxlよりも効率的です
xlwt :(excelを書き込み）テーブル

xlrdとxlwtバージョンの互換性はあまり良くなく、excelの多くの新しいバージョンには問題があります。

新しいバージョンのexcel処理

openpyxl(Excelシートの読み取りと書き込みは可能です。)は、Excel2007以降のバージョンで生成されたxlsxファイルを処理でき、xlsとxlsxの間で簡単に変換できます。

注意事項：文字コードが[gb2312]の場合、読み取り後に文字化けで表示されます。最初にUnicodeに変換してください。

openpyxlをインストールします

openpyxlモジュールをダウンロードします：

https://pypi.python.org/pypi/openpyxl

2、指定されたファイルディレクトリに解凍します：

tar -xzvf openpyxl.tar.gz

3、ディレクトリを展開し、setup.pyファイルを見つけて、次のコマンドを実行します。

pythonsetup.py install

エラーNo module named setuptools が表示された場合、コマンド“easy_install openpyxl”を実行します。setuptoolsが自動的にインストールされます。

easy_install for win32

自動的にインストールできない場合、基本的にpythonモジュールはコマンドpython モジュール名 .py installでインストールされます。setuptoolsモジュールが利用できない場合は、公式Webサイトからダウンロードし、前のコマンドに従ってインストールすればいいです。

画像の処理はをインストールする必要がありますpillow(PIL)

To be able to include images (jpeg, png, bmp,…) into an openpyxl file, you will also need the “pillow” library that can be installed with:

pip install pillow

openpyxlで複数のデータ形式を定義します

最も重要な3つの種類

NULL値：pythonのNoneに対応し、このセルにデータがないことを示します。
numberic：数値型。浮動小数点数に従って均一に処理されます。 pythonのfloatに対応します。
string：文字列型。Pythonのunicodeに対応します。

Excelファイルの3つのオブジェクト

workbook：ワークブック。excelファイルには複数のワークブックが含まれています。
sheet：シート。1つのワークブックに複数のシートがあり、[sheet1]、[sheet2]などです。
cell：セル、データのストレージオブジェクト

1）インポート

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, Color, Fill
from openpyxl.styles import colors
rom openpyxl.styles import Fill,fills
from openpyxl.formatting.rule import ColorScaleRule

2）ワークブックを開きます

wb = load_workbook('file_name.xlsx')

3）open sheet：

名前による

ws = wb["frequency"]
ws2 = wb.get_sheet_by_name('frequency')

この2つのコマンドの意味は同じです

コマンドws is ws2 is ws3 はTrueを出力します

名前が分からない場合はindexを使用します

sheet_names = wb.get_sheet_names()
ws = wb.get_sheet_by_name(sheet_names[index])# indexは0の場合は最初のテーブルです

或は

ws =wb.active
ws = wb.get_active_sheet()

この2つのコマンドの意味は同じです。 #_active_sheet_indexによって、読み取るテーブルを設定します。値が0の場合はデフォルトで最初のテーブルを読み取ります

シート名:

wb.get_active_sheet().title

4）新しいテーブルを作成します

ws1 = wb.create_sheet() #デフォルトで最後に挿入します
ws2 = wb.create_sheet(0) #最初に挿入します

テーブルが作成されると、sheet1、sheet2 …のように、デフォルト名が順番に表示されます。

ws.title = "New Title" #テーブル名を変更します

簡略化:

 ws2 = wb.create_sheet(title="Pi")

5）backgroud color of tab( be white by default)

ws.sheet_properties.tabColor = "1072BA" # set with RRGGBB color code

6）セルを使用します

c = ws['A4'] #read は c = ws.cell('A4') を同じです
ws['A4'] = 4 #write 
#ws.cell行番号と列番号は1から始まる2つの方法があります
d = ws.cell(row = 4, column = 2) #行列を読み書きます
d = ws.cell('A4')

セルの値を書き込みます

ws.cell(row = 4, column = 2).value = 'test'
ws.cell(row = 4, column = 2, value = 'test')

7）複数のセルを検索します

cell_range = ws['A1':'C2']

すべてのセルのデータを読み取ります

get_cell_collection()

8) 行と列に従って操作します

a)行ごとに読みます

ws.iter_rows(range_string=None, row_offset=0, column_offset=0): range-string(string)-セルの範囲：例えば、(‘A1:C4’) row_offset-行を追加します column_offset-列を追加しますジェネレータを返します。値を取るときはvalueを使用してください。例えば：

for row in ws.iter_rows('A1:C2'):
for cell in row:
print cell

指定された行と列を読み取ります:

rows=ws.rows#rowループが可能です 
columns=ws.columns#columnループが可能です

n行目のデータをプリントします

print rows[n]#使用する必要はありません.value 
print columns[n]#使用する必要はありません.value

b)行ずつ書きます

ws.append(iterable)

現在のシートの下部に行を追加します。iterableはlist,tuple,dict,range,generatorの形式である必要があります。 1.listの場合は、listを最初から最後まで順番に追加します。 2. dictの場合は、対応するキーに従って、対応するキー値を追加します。

append([‘This is A1’, ‘This is B1’, ‘This is C1’])
append({‘A’ : ‘This is A1’, ‘C’ : ‘This is C1’})
append({1 : ‘This is A1’, 3 : ‘This is C1’})

9) テーブルの数量を表示します

wb.get_sheet_names()  
#テーブル名、行の数、列の数を表示します   
print ws.title  
print ws.max_row
print ws.max_column
ws.get_highest_row() #UserWarning: Call to deprecated function
ws.get_highest_column()# UserWarning: Call to deprecated function

10) 列Xの文字を取得しますcol = get_column_letter(x)、xは1から始まります

from openpyxl.utils import get_column_letter
for  x  in  range( 1, len(record)+ 1 ):  
col = get_column_letter(x)  
ws.cell( '%s%s' %(col, i)).value = x

列文字で複数のExcelデータブロックを取得します

cell_range = "E3:{0}28".format(get_column_letter(bc_col))
ws["A1"] = "=SUM(%s)"%cell_range

11）excelファイルはgbkエンコーディングです。読み取るときは、最初にgbkにエンコードし、次にユニコードにデコードしてから、utf8にエンコードする必要があります。

cell_value.encode('gbk').decode('gbk').encode('utf8')

12) 公式計算formulae

ws["A1"] = "=SUM(1, 1)"
ws["A1"] = "=SUM(B1:C1)"

コードの実例(直接変更して使用します)

from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.cell import get_column_letter
dest_filename = 'empty_book.xlsx'
wb = Workbook()
ws1 = wb.active
ws1.title = "range names"
for row in range(1, 40):
ws1.append(range(600))
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
_ = ws3.cell(column=col, row=row, value="%s" % get_column_letter(col))
print(ws3['AA10'].value)
wb.save(filename = dest_filename)
sheet_ranges = wb['range names']
print(sheet_ranges['D18'].value)
ws['A1'] = datetime.datetime(2010, 7, 21)
ws['A1'].number_format #出力'yyyy-mm-dd h:mm:ss'
rows = [
['Number', 'Batch 1', 'Batch 2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 10],
[6, 25, 5],
[7, 50, 10],
]
rows = [
['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
[date(2015,9, 1), 40, 30, 25],
[date(2015,9, 2), 40, 25, 30],
[date(2015,9, 3), 50, 30, 45],
[date(2015,9, 4), 30, 25, 40],
[date(2015,9, 5), 25, 35, 30],
[date(2015,9, 6), 20, 40, 35],
]
for row in rows:
ws.append(row)

excelの画像処理、PILモジュール

try:
from openpyxl.drawing import image
import PIL            
except ImportError, e:
print "[ERROR]",e
report_file = self.excel_path + "/frquency_report_%d.xlsx" %id
shutil.copyfile(configs.PATTEN_FILE, report_file)
if not os.path.exists(report_file):
print "generate file failed: ", report_file
sys.exit(1)
wb = load_workbook(report_file)
ws = wb.get_sheet_by_name('frequency')
img_f = configs.IMAGE_LOGO
if os.path.exists(img_f):
try:
img = image.Image(img_f)
ws.add_image(img, 'A1')
except Exception, e:
print "[ERROR]%s:%s" % (type(e), e)
ws['A1'] = "プログラマティック・マーケティング・プラットフォーム"
else:
ws['A1'] = "プログラマティック・マーケティング・プラットフォーム"	
font1 = Font(size=22)
ws['A1'].font = font1
ws['B4'] = ad_plan #はws.cell('B4') = ad_planと同じです
ws['B5'] = ad_names
ws['B6'] = str(start_d) + '  to  ' + str(end_d)
wb.save(report_file)
try:
wb = load_workbook(report_file)
ws = wb.get_sheet_by_name('frequency')            
row = 9
for it in query_result:
one_row = it.split('\t')
print one_row
if '10' == one_row[0]:
one_row[0] = '10+'
col = 1
for one_cell in one_row:
ws.cell(row = row, column = col).value = one_cell
col = col + 1
row = row + 1      
except Thrift.TException, tx:
print '[ERROR] %s' % (tx.message)
else:
wb.save(report_file)
finally:
pass