python 3操作excel

良いpythonがexcel方式の対比文章を読み書きすることを見ます:PythonでExcelファイルを読み書きします
他のバージョンのexcelについては、彼が提供したリンクチュートリアルで学ぶことができます.
XlsxWriter:
https://github.com/jmcnamara/XlsxWriter
http://xlsxwriter.readthedocs.org
openpyxl: http://openpyxl.readthedocs.io/en/default/
Microsoft excel API:https://msdn.microsoft.com/en-us/library/fp179694.aspx
概要
xlrdはexcelファイルを読み込むために使用され、xlwtはexcelファイルを書くために使用され、excelを操作するために協力します.
公式ドキュメント:http://www.python-excel.org/
xlrdの公式紹介:https://pypi.python.org/pypi/xlrd/1.0.0
xlwt公式紹介:https://pypi.python.org/pypi/xlwt/1.1.2
xlutilsの公式紹介:https://pypi.python.org/pypi/xlutils
http://xlutils.readthedocs.io/en/latest/
1.xlrdについて:

Library for developers to extract data from Microsoft Excel (tm) spreadsheet files

Extract data from Excel spreadsheets (.xls and .xlsx, versions 2.0 onwards) on any platform. Pure Python (2.6, 2.7, 3.2+). Strong support for Excel dates. Unicode-aware.

翻訳してまとめると、
xlrdが任意のプラットフォームで読み取ることができるexcelは:xlsおよび.xlsx .
xlrdでサポートされているpythonバージョンは、2.6,2.7,3.2+です.
2.xlwtについて:

Library to create spreadsheet files compatible with MS Excel 97/2000/XP/2003 XLS files, on any platform, with Python 2.6, 2.6, 3.3+
This is a library for developers to use to generate spreadsheet files compatible with Microsoft Excel versions 95 to 2003.

翻訳してまとめると、
xlwtでサポートされているexcelバージョンは、Microsoft excelバージョン95～2003、すなわちxlsファイルです.
xlwtでサポートされているpythonバージョンは2.6,3.3+.
3.xlutilsについて:

This package provides a collection of utilities for working with Excel files. Since these utilities may require either or both of the xlrd and xlwt packages, they are collected together here, separate from either package.

Currently available are:

xlutils.copy
Tools for copying xlrd.Book objects to xlwt.Workbook objects.
xlutils.display
Utility functions for displaying information about xlrd-related objects in a user-friendly and safe fashion.
xlutils.filter
A mini framework for splitting and filtering Excel files into new Excel files.
xlutils.margins
Tools for finding how much of an Excel file contains useful data.
xlutils.save
Tools for serializing xlrd.Book objects back to Excel files.
xlutils.styles
Tools for working with formatting information expressed in styles.

翻訳してまとめると、
xlrdとxlwtの間でインタラクションが必要な場合、例えばxlrdからxlwtをコピーするにはxlutilsが必要です.
現在、copy、display、filter、margins、Save、stylesのいくつかの関数が提供されています.
 
xlrdとxlwtのインストール

pip install xlrd
pip install xlwt
pip install xlutils
pip list

xlrd (1.0.0) 
xlutils (2.0.0) 
xlwt (1.1.2)

使用
1.excelファイルを新規作成(xlwt)

#coding='utf-8'

import xlwt
from datetime import  datetime

def set_style(font_name,font_height,bold=False):
    style=xlwt.XFStyle()
    
    font=xlwt.Font()
    font.name=font_name         # 'Times New Roman'
    font.height=font_height
    font.bold=bold
    font.colour_index=4
    
    borders=xlwt.Borders()
    borders.left=6
    borders.right=6
    borders.top=6
    borders.bottom=6
    
    style.font=font
    style.borders=borders
    return style

def write_to_excel_xlwt():
    '''Write content to a new excel'''
    new_workbook=xlwt.Workbook()
    new_sheet=new_workbook.add_sheet("SheetName_test")
    new_sheet.write(0,0,"hello") 
    #write cell with style
    new_sheet.write(0,1,"world",set_style("Times New Roman", 220, True))  
    
    style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',num_format_str='#,##0.00')
    style1 = xlwt.easyxf(num_format_str='D-MMM-YY')
    new_sheet.write(1, 0, 1234.56, style0)
    new_sheet.write(1, 1, datetime.now(), style1)
    
    #write cell with formula
    new_sheet.write(2,0,5)
    new_sheet.write(2,1,8)
    new_sheet.write(3,0, xlwt.Formula("A3+B3"))

    new_workbook.save(r"NewCreateWorkbook.xls")         #if change to xlsx,then open failed
    
if __name__=="__main__":
    write_to_excel_xlwt()

コード実行後、現在のパスの下でexcelファイル「NewCreateWorkbook.xls」を生成します.内容は次のとおりです.

2.excelファイルの読み込み(xlrd)

#coding='utf-8'

import xlrd
    
def read_excel_xlrd():
    '''Read Excel with xlrd'''
    #file
    TC_workbook=xlrd.open_workbook(r"NewCreateWorkbook.xls")

    #sheet
    all_sheets_list=TC_workbook.sheet_names()
    print("All sheets name in File:",all_sheets_list)
    
    first_sheet=TC_workbook.sheet_by_index(0)
    print("First sheet Name:",first_sheet.name)
    print("First sheet Rows:",first_sheet.nrows)
    print("First sheet Cols:",first_sheet.ncols)
    
    second_sheet=TC_workbook.sheet_by_name("SheetName_test")
    print("Second sheet Rows:",second_sheet.nrows)
    print("Second sheet Cols:",second_sheet.ncols)
    
    first_row=first_sheet.row_values(0)
    print("First row:",first_row)
    first_col=first_sheet.col_values(0)
    print("First Column:",first_col)
    
    # cell
    cell_value=first_sheet.cell(1,0).value
    print("The 1th method to get Cell value of row 2 & col 1:",cell_value)
    cell_value2=first_sheet.row(1)[0].value
    print("The 2th method to get Cell value of row 2 & col 1:",cell_value2)
    cell_value3=first_sheet.col(0)[1].value
    print("The 3th method to get Cell value of row 2 & col 1:",cell_value3)
    
if __name__=="__main__":
    read_excel_xlrd()

実行後、コンソールの出力は次のとおりです.

All sheets name in File: ['SheetName_test']
First sheet Name: SheetName_test
First sheet Rows: 4
First sheet Cols: 2
Second sheet Rows: 4
Second sheet Cols: 2
First row: ['hello', 'world']
First Column: ['hello', 1234.56, 5.0, '']
The 1th method to get Cell value of row 2 & col 1: 1234.56
The 2th method to get Cell value of row 2 & col 1: 1234.56
The 3th method to get Cell value of row 2 & col 1: 1234.56

3.既存のexcelへの書き込み(xlrd&xlwt&xlutils)

#coding='utf-8'

import xlrd
import xlwt
from xlutils.copy import copy
    
def write_to_existed_file():
    '''Write content to existed excel file with xlrd&xlutils&xlwt'''
    rb = xlrd.open_workbook(r"NewCreateWorkbook.xls",formatting_info=True)

    wb = copy(rb)
    ws = wb.get_sheet(0)
    
    font=xlwt.Font()
    font.name="Times New Roman"
    font.height=220
    font.bold=False
    
    borders = xlwt.Borders()
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    
    pattern = xlwt.Pattern()
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = 2
    
    cell_style = xlwt.XFStyle()
    cell_style.font = font
    cell_style.borders = borders
    cell_style.pattern = pattern
    
    ws.write(6,7,"hello world",cell_style)
    wb.save(r"NewCreateWorkbook.xls")
    
if __name__=="__main__":
    write_to_existed_file()

実行後、excelファイルの内容は次のとおりです.