python openpyxl模块从excel读取数据

第一步：安装openyxl模块

pip install openyxl

第二步：读写数据

from openpyxl import load_workbook
# 工作簿 workbook 表单 sheet 单元格 cell
#打开指定工作簿
wb=load_workbook('test_data.xlsx')
# 定位到指定的表单
sheet=wb['test_data']
#读取指定单元格
cell_values = sheet.cell(2,3).value
print(cell_values)
# 写入单元格
sheet.cell(2,3).value = '赋值给单元格'
#获取最大的行数
row=sheet.max_row
# 获取最大列数
col=sheet.max_column
print(row,col)
# 修改Excel之后，保存
wb.save('test_data.xlsx')
 

注意：

1.from 它只能识别xlsx结尾的Excel

2.尽量不要直接修改文件后缀名

3.避免直接在pycham里面新建后缀名为xlsx的文件，防止出错

4.最好就是新建好xlsx文件，直接拖进pycharm

5.excel表中的行列数，从1开始计数

6.保存之前，要删除excle下面的空白行，肉眼看不到，但是程序有时候会把空白的行算进去。

=====================================================================================================

创建一个工作簿

---

• 使用openpyxl没有必要先在系统中新建一个.xlsx，我们需要做的只需要引入Workbook这个类，接着开始调用它。

>>> from openpyxl import Workbook
>>> wb = Workbook()

• 一个工作簿(workbook)在创建的时候同时至少也新建了一张工作表(worksheet)。你可以通过openpyxl.workbook.Workbook.active()调用得到正在运行的工作表。

>>> ws = wb.active

注意：该函数调用工作表的索引(_active_sheet_index)，默认是0。除非你修改了这个值，否则你使用该函数一直是在对第一张工作表进行操作。

• 使用openpyxl.workbook.Workbook.create_sheet()新建一张表

>>> ws1 = wb.create_sheet() #默认插在工作簿末尾
# or
>>> ws2 = wb.create_sheet(0) # 插入在工作簿的第一个位置

• 在创建工作表的时候系统自动命名。他们按照序列依次命名 (Sheet, Sheet1, Sheet2, ...)。你可以通过调用下面的属性修改工作表的名称：

ws.title = "New Title"

• 标签栏的背景色默认为白色。你可以通过提供一个RRGGBB颜色码改变标签栏的字体颜色：

ws.sheet_properties.tabColor = "1072BA"

 

修改颜色效果.png

操作数据-使用一个单元格

c = ws.cell('A4')

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

使用多个单元格

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


数据存储

>>> wb = Workbook(guess_types=True)
>>> c.value = '12%'
>>> print(c.value)
0.12
>>> import datetime
>>> d.value = datetime.datetime.now()
>>> print d.value
datetime.datetime(2010, 9, 10, 22, 25, 18)
>>> c.value = '31.50'
>>> print(c.value)
31.5

保存到文件

>>> wb = load_workbook('document.xlsx')
>>> wb.save('document_template.xltx', as_template=True)

从文件中导入

>>> from openpyxl import load_workbook
>>> wb2 = load_workbook('test.xlsx')
>>> print wb2.get_sheet_names()
['Sheet2', 'New Title', 'Sheet1']

===============================================================================================================
代码：

from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.styles.colors import BLACK
from collections import namedtuple


class ParseExcel(object):
    """解析excel文件"""

    def __init__(self, filename):
        try:
            self.filename = filename
            self.__wb = load_workbook(self.filename)  #打开excel
        except FileNotFoundError as e:
            raise e

    def get_max_row_num(self, sheet_name):
        """获取最大行号"""
        max_row_num = self.__wb[sheet_name].max_row
        return max_row_num

    def get_max_column_num(self, sheet_name):
        """获取最大列号"""
        max_column = self.__wb[sheet_name].max_column
        return max_column

    def get_cell_value(self, sheet_name, coordinate=None, row=None, column=None):
        """获取指定单元格的数据"""
        if coordinate is not None:
            try:
                return self.__wb[sheet_name][coordinate].value
            except Exception as e:
                raise e
        elif coordinate is None and row is not None and column is not None:
            if isinstance(row, int) and isinstance(column, int):
                return self.__wb[sheet_name].cell(row=row, column=column).value
            else:
                raise TypeError('row and column must be type int')
        else:
            raise Exception("Insufficient Coordinate of cell!")

    def get_row_value(self, sheet_name, row):
        """获取某一行的数据"""
        column_num = self.get_max_column_num(sheet_name)
        row_value = []
        if isinstance(row, int):
            for column in range(1, column_num + 1):
                values_row = self.__wb[sheet_name].cell(row, column).value
                row_value.append(values_row)
            return row_value
        else:
            raise TypeError('row must be type int')

    def get_column_value(self, sheet_name, column):
        """获取某一列数据"""
        row_num = self.get_max_column_num(sheet_name)
        column_value = []
        if isinstance(column, int):
            for row in range(1, row_num + 1):
                values_column = self.__wb[sheet_name].cell(row, column).value
                column_value.append(values_column)
            return column_value
        else:
            raise TypeError('column must be type int')

    def get_all_value_1(self, sheet_name):
        """获取指定表单的所有数据(除去表头)"""
        max_row_num = self.get_max_row_num(sheet_name)
        max_column = self.get_max_column_num(sheet_name)
        values = []
        for row in range(2, max_row_num + 1):
            value_list = []
            for column in range(1, max_column + 1):
                value = self.__wb[sheet_name].cell(row, column).value
                value_list.append(value)
            values.append(value_list)
        return values

    def get_all_value_2(self, sheet_name):
        """获取指定表单的所有数据(除去表头)"""
        rows_obj = self.__wb[sheet_name].iter_rows(min_row=2, max_row=self.__wb[sheet_name].max_row, values_only=True)
        values = []
        for row_tuple in rows_obj:
            value_list = []
            for value in row_tuple:
                value_list.append(value)
            values.append(value_list)
        return values

    def get_excel_title(self, sheet_name):
        """获取sheet表头"""
        title_key = tuple(self.__wb[sheet_name].iter_rows(max_row=1, values_only=True))[0]
        return title_key

    def get_listdict_all_value(self, sheet_name):
        """获取所有数据，返回嵌套字典的列表"""
        sheet_title = self.get_excel_title(sheet_name)
        all_values = self.get_all_value_2(sheet_name)
        value_list = []
        for value in all_values:
            value_list.append(dict(zip(sheet_title, value)))
        return value_list

    def get_list_nametuple_all_value(self, sheet_name):
        """获取所有数据，返回嵌套命名元组的列表"""
        sheet_title = self.get_excel_title(sheet_name)
        values = self.get_all_value_2(sheet_name)
        excel = namedtuple('excel', sheet_title)
        value_list = []
        for value in values:
            e = excel(*value)
            value_list.append(e)
        return value_list

    def write_cell(self, sheet_name, row, column, value=None, bold=True, color=BLACK):
        if isinstance(row, int) and isinstance(column, int):
            try:
                cell_obj = self.__wb[sheet_name].cell(row, column)
                cell_obj.font = Font(color=color, bold=bold)
                cell_obj.value = value
                self.__wb.save(self.filename)
            except Exception as e:
                raise e
        else:
            raise TypeError('row and column must be type int')


if __name__ == '__main__':
    pe = ParseExcel('sheet1.xlsx')
    print(pe.get_all_value_2('division'))
    print(pe.get_list_nametuple_all_value('division'))
    column_row = pe.get_max_column_num('division')
    print('最大列号:', column_row)
    max_row = pe.get_max_row_num('division')
    print('最大行号:', max_row)
    cell_value_1 = pe.get_cell_value('division', row=2, column=3)
    print('第%d行, 第%d列的数据为: %s' % (2, 3, cell_value_1))
    cell_value_2 = pe.get_cell_value('division', coordinate='A5')  #coordinate单元格名称
    print('A5单元格的数据为: {}'.format(cell_value_2))
    value_row = pe.get_row_value('division', 3)
    print('第{}行的数据为:{}'.format(3, value_row))
    value_column = pe.get_column_value('division', 2)
    print('第{}列的数据为:{}'.format(2, value_column))
    values_1 = pe.get_all_value_1('division')
    print('第一种方式获取所有数据
', values_1)
    values_2 = pe.get_all_value_2('division')
    print('第二种方式获取所有数据
', values_2)
    title = pe.get_excel_title('division') #显示所有的title
    print('表头为
{}'.format(title))
    dict_value = pe.get_listdict_all_value('division')
    print('所有数据组成的嵌套字典的列表:
', dict_value)
    namedtuple_value = pe.get_list_nametuple_all_value('division')  #显示元祖列表
    print('所有数据组成的嵌套命名元组的列表:
', namedtuple_value)
    pe.write_cell('division', 1, 2, 'Tc_title')  #更换excel中的title