Python利用openpyxl/xlrd/xlwt和pandas操作Excel

pip install openpyxl

from openpyxl import workbook, load_workbook
# 创建新工作簿
wb = workbook()
ws = wb.active  # 获取活动工作表
ws.title = "sheet1"  # 设置工作表名称
# 写入数据
ws['a1'] = "姓名"
ws['b1'] = "年龄"
ws['a2'] = "张三"
ws['b2'] = 25
# 保存文件
wb.save("example.xlsx")
# 读取已有文件
wb = load_workbook("example.xlsx")
ws = wb.active
# 读取数据
for row in ws.iter_rows(values_only=true):
    print(row)
# 修改数据
ws['b2'] = 26
wb.save("example_modified.xlsx")

pip install pandas openpyxl

import pandas as pd

# 创建dataframe
data = {
    '姓名': ['张三', '李四', '王五'],
    '年龄': [25, 30, 35],
    '城市': ['北京', '上海', '广州']
}
df = pd.dataframe(data)

# 写入excel
df.to_excel("pandas_example.xlsx", index=false, sheet_name='员工信息')

# 读取excel
df_read = pd.read_excel("pandas_example.xlsx", sheet_name='员工信息')
print(df_read)

# 修改并保存
df_read['年龄'] = df_read['年龄'] + 1
df_read.to_excel("pandas_example_modified.xlsx", index=false)

pip install xlrd xlwt

import xlrd
import xlwt

# 写入xls文件
workbook = xlwt.workbook()
sheet = workbook.add_sheet('sheet1')

sheet.write(0, 0, '姓名')
sheet.write(0, 1, '年龄')
sheet.write(1, 0, '张三')
sheet.write(1, 1, 25)

workbook.save('old_example.xls')

# 读取xls文件
book = xlrd.open_workbook('old_example.xls')
sheet = book.sheet_by_index(0)

for row in range(sheet.nrows):
    print(sheet.row_values(row))

from openpyxl.styles import font, alignment, border, side

wb = workbook()
ws = wb.active

# 设置单元格样式
ws['a1'].font = font(bold=true, color="ff0000")
ws['a1'].alignment = alignment(horizontal="center")
ws['a1'].border = border(left=side(style='thin'), 
                        right=side(style='thin'), 
                        top=side(style='thin'), 
                        bottom=side(style='thin'))

wb.save("styled_example.xlsx")

with pd.excelwriter('multi_sheet.xlsx') as writer:
    df1.to_excel(writer, sheet_name='sheet1')
    df2.to_excel(writer, sheet_name='sheet2')

# 高性能读取
from openpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', read_only=true)
ws = wb.active
for row in ws.iter_rows(values_only=true):
    process(row)
    
# 高性能写入
from openpyxl import workbook
wb = workbook(write_only=true)
ws = wb.create_sheet()
for row in data:
    ws.append(row)
wb.save('large_output.xlsx')

pip install pyqt5 pandas openpyxl xlrd

import sys
from pyqt5.qtwidgets import (qapplication, qmainwindow, qtableview, 
                             qfiledialog, qvboxlayout, qwidget, 
                             qpushbutton, qmessagebox)
from pyqt5.qtcore import qt
from pyqt5.qtgui import qstandarditemmodel, qstandarditem
import pandas as pd
class excelviewer(qmainwindow):
    def __init__(self):
        super().__init__()
        self.setwindowtitle("excel查看器")
        self.setgeometry(100, 100, 800, 600)
        self.initui()
        self.current_file = none
    def initui(self):
        # 主窗口部件
        self.central_widget = qwidget()
        self.setcentralwidget(self.central_widget)
        # 布局
        self.layout = qvboxlayout()
        # 按钮
        self.btn_open = qpushbutton("打开excel文件")
        self.btn_open.clicked.connect(self.open_file)
        self.btn_save = qpushbutton("保存修改")
        self.btn_save.clicked.connect(self.save_file)
        self.btn_save.setenabled(false)
        # 表格视图
        self.table_view = qtableview()
        self.model = qstandarditemmodel()
        self.table_view.setmodel(self.model)
        # 添加到布局
        self.layout.addwidget(self.btn_open)
        self.layout.addwidget(self.btn_save)
        self.layout.addwidget(self.table_view)
        self.central_widget.setlayout(self.layout)
    def open_file(self):
        # 打开文件对话框
        file_path, _ = qfiledialog.getopenfilename(
            self, "打开excel文件", "", 
            "excel files (*.xlsx *.xls);;all files (*)"
        )
        if file_path:
            try:
                # 使用pandas读取excel文件
                self.df = pd.read_excel(file_path)
                self.current_file = file_path
                # 清除旧数据
                self.model.clear()
                # 设置表头
                self.model.sethorizontalheaderlabels(self.df.columns)
                # 填充数据
                for row in range(self.df.shape[0]):
                    for col in range(self.df.shape[1]):
                        item = qstandarditem(str(self.df.iloc[row, col]))
                        item.setflags(item.flags() ^ qt.itemiseditable)  # 默认不可编辑
                        self.model.setitem(row, col, item)
                self.btn_save.setenabled(true)
            except exception as e:
                qmessagebox.critical(self, "错误", f"无法打开文件:\n{str(e)}")
    def save_file(self):
        if not self.current_file:
            return
        try:
            # 从模型中获取修改后的数据
            rows = self.model.rowcount()
            cols = self.model.columncount()
            data = []
            for row in range(rows):
                row_data = []
                for col in range(cols):
                    item = self.model.item(row, col)
                    row_data.append(item.text())
                data.append(row_data)
            # 创建dataframe并保存
            new_df = pd.dataframe(data, columns=self.df.columns)
            new_df.to_excel(self.current_file, index=false)
            qmessagebox.information(self, "成功", "文件保存成功!")
        except exception as e:
            qmessagebox.critical(self, "错误", f"保存失败:\n{str(e)}")
if __name__ == "__main__":
    app = qapplication(sys.argv)
    viewer = excelviewer()
    viewer.show()
    sys.exit(app.exec_())

import sys
from pyqt5.qtwidgets import (qapplication, qmainwindow, qtableview, qfiledialog, 
                             qvboxlayout, qhboxlayout, qwidget, qpushbutton, 
                             qmessagebox, qlabel, qlineedit, qcombobox, qmenu, 
                             qaction, qinputdialog)
from pyqt5.qtcore import qt, qmodelindex
from pyqt5.qtgui import qstandarditemmodel, qstandarditem, qkeysequence
import pandas as pd
class exceleditor(qmainwindow):
    def __init__(self):
        super().__init__()
        self.setwindowtitle("excel编辑器")
        self.setgeometry(100, 100, 1000, 700)
        self.current_file = none
        self.df = none
        self.initui()
    def initui(self):
        # 主窗口部件
        self.central_widget = qwidget()
        self.setcentralwidget(self.central_widget)
        # 主布局
        self.main_layout = qvboxlayout()
        # 顶部工具栏
        self.toolbar_layout = qhboxlayout()
        self.btn_open = qpushbutton("打开")
        self.btn_open.settooltip("打开excel文件 (ctrl+o)")
        self.btn_open.clicked.connect(self.open_file)
        self.btn_save = qpushbutton("保存")
        self.btn_save.settooltip("保存当前文件 (ctrl+s)")
        self.btn_save.clicked.connect(self.save_file)
        self.btn_save.setenabled(false)
        self.btn_save_as = qpushbutton("另存为")
        self.btn_save_as.settooltip("另存为新的excel文件")
        self.btn_save_as.clicked.connect(self.save_file_as)
        self.btn_save_as.setenabled(false)
        self.btn_add_row = qpushbutton("添加行")
        self.btn_add_row.settooltip("在表格末尾添加新行")
        self.btn_add_row.clicked.connect(self.add_row)
        self.btn_add_row.setenabled(false)
        self.btn_delete_row = qpushbutton("删除行")
        self.btn_delete_row.settooltip("删除选中的行")
        self.btn_delete_row.clicked.connect(self.delete_row)
        self.btn_delete_row.setenabled(false)
        self.file_label = qlabel("未打开文件")
        self.toolbar_layout.addwidget(self.btn_open)
        self.toolbar_layout.addwidget(self.btn_save)
        self.toolbar_layout.addwidget(self.btn_save_as)
        self.toolbar_layout.addwidget(self.btn_add_row)
        self.toolbar_layout.addwidget(self.btn_delete_row)
        self.toolbar_layout.addstretch()
        self.toolbar_layout.addwidget(self.file_label)
        # 表格视图
        self.table_view = qtableview()
        self.table_view.setselectionbehavior(qtableview.selectrows)
        self.table_view.setcontextmenupolicy(qt.customcontextmenu)
        self.table_view.customcontextmenurequested.connect(self.show_context_menu)
        self.model = qstandarditemmodel()
        self.table_view.setmodel(self.model)
        # 搜索栏
        self.search_layout = qhboxlayout()
        self.search_label = qlabel("搜索:")
        self.search_input = qlineedit()
        self.search_input.setplaceholdertext("输入搜索内容...")
        self.search_column = qcombobox()
        self.search_column.additem("所有列")
        self.search_btn = qpushbutton("搜索")
        self.search_btn.clicked.connect(self.search_data)
        self.search_layout.addwidget(self.search_label)
        self.search_layout.addwidget(self.search_input)
        self.search_layout.addwidget(self.search_column)
        self.search_layout.addwidget(self.search_btn)
        # 添加到主布局
        self.main_layout.addlayout(self.toolbar_layout)
        self.main_layout.addlayout(self.search_layout)
        self.main_layout.addwidget(self.table_view)
        self.central_widget.setlayout(self.main_layout)
        # 添加快捷键
        self.shortcut_open = qaction(self)
        self.shortcut_open.setshortcut(qkeysequence("ctrl+o"))
        self.shortcut_open.triggered.connect(self.open_file)
        self.addaction(self.shortcut_open)
        self.shortcut_save = qaction(self)
        self.shortcut_save.setshortcut(qkeysequence("ctrl+s"))
        self.shortcut_save.triggered.connect(self.save_file)
        self.addaction(self.shortcut_save)
        # 状态栏
        self.statusbar().showmessage("就绪")
    def open_file(self):
        file_path, _ = qfiledialog.getopenfilename(
            self, "打开excel文件", "", 
            "excel files (*.xlsx *.xls *.csv);;all files (*)"
        )
        if file_path:
            try:
                # 根据文件扩展名选择读取方式
                if file_path.endswith('.csv'):
                    self.df = pd.read_csv(file_path)
                else:
                    self.df = pd.read_excel(file_path)
                self.current_file = file_path
                self.file_label.settext(f"当前文件: {file_path}")
                # 更新搜索列下拉框
                self.search_column.clear()
                self.search_column.additem("所有列")
                self.search_column.additems(self.df.columns.tolist())
                # 加载数据到表格
                self.load_data_to_table()
                self.btn_save.setenabled(true)
                self.btn_save_as.setenabled(true)
                self.btn_add_row.setenabled(true)
                self.btn_delete_row.setenabled(true)
                self.statusbar().showmessage(f"成功加载文件: {file_path}", 3000)
            except exception as e:
                qmessagebox.critical(self, "错误", f"无法打开文件:\n{str(e)}")
                self.statusbar().showmessage(f"打开文件失败: {str(e)}", 5000)
    def load_data_to_table(self):
        # 清除旧数据
        self.model.clear()
        # 设置表头
        self.model.sethorizontalheaderlabels(self.df.columns)
        # 填充数据
        for row in range(self.df.shape[0]):
            row_items = []
            for col in range(self.df.shape[1]):
                value = self.df.iloc[row, col]
                item = qstandarditem(str(value) if not pd.isna(value) else "")
                item.seteditable(true)
                row_items.append(item)
            self.model.appendrow(row_items)
        # 调整列宽
        self.table_view.resizecolumnstocontents()
    def save_file(self):
        if not self.current_file:
            return
        try:
            self.update_data_from_table()
            # 根据文件扩展名选择保存方式
            if self.current_file.endswith('.csv'):
                self.df.to_csv(self.current_file, index=false)
            else:
                self.df.to_excel(self.current_file, index=false)
            qmessagebox.information(self, "成功", "文件保存成功!")
            self.statusbar().showmessage("文件保存成功", 3000)
        except exception as e:
            qmessagebox.critical(self, "错误", f"保存失败:\n{str(e)}")
            self.statusbar().showmessage(f"保存失败: {str(e)}", 5000)
    def save_file_as(self):
        if self.df is none:
            return
        file_path, _ = qfiledialog.getsavefilename(
            self, "另存为", "", 
            "excel files (*.xlsx);;csv files (*.csv);;all files (*)"
        )
        if file_path:
            try:
                self.update_data_from_table()
                if file_path.endswith('.csv'):
                    self.df.to_csv(file_path, index=false)
                else:
                    # 确保扩展名是.xlsx
                    if not file_path.endswith('.xlsx'):
                        file_path += '.xlsx'
                    self.df.to_excel(file_path, index=false)
                self.current_file = file_path
                self.file_label.settext(f"当前文件: {file_path}")
                qmessagebox.information(self, "成功", f"文件已保存到:\n{file_path}")
                self.statusbar().showmessage(f"文件已保存到: {file_path}", 3000)
            except exception as e:
                qmessagebox.critical(self, "错误", f"保存失败:\n{str(e)}")
                self.statusbar().showmessage(f"保存失败: {str(e)}", 5000)
    def update_data_from_table(self):
        # 从表格模型获取数据并更新dataframe
        rows = self.model.rowcount()
        cols = self.model.columncount()
        data = []
        for row in range(rows):
            row_data = []
            for col in range(cols):
                item = self.model.item(row, col)
                row_data.append(item.text() if item else "")
            data.append(row_data)
        self.df = pd.dataframe(data, columns=self.df.columns)
    def add_row(self):
        if self.df is none:
            return
        # 在末尾添加空行
        empty_row = [""] * len(self.df.columns)
        self.model.appendrow([qstandarditem(item) for item in empty_row])
        # 滚动到最后一行
        self.table_view.scrolltobottom()
        self.statusbar().showmessage("已添加新行", 2000)
    def delete_row(self):
        selected = self.table_view.selectionmodel().selectedrows()
        if not selected:
            qmessagebox.warning(self, "警告", "请先选择要删除的行")
            return
        # 从后往前删除以避免索引问题
        for index in sorted(selected, reverse=true):
            self.model.removerow(index.row())
        self.statusbar().showmessage(f"已删除 {len(selected)} 行", 3000)
    def search_data(self):
        search_text = self.search_input.text().strip().lower()
        if not search_text:
            return
        column = self.search_column.currenttext()
        column_idx = -1 if column == "所有列" else self.search_column.currentindex() - 1
        # 清除之前的高亮
        for row in range(self.model.rowcount()):
            for col in range(self.model.columncount()):
                item = self.model.item(row, col)
                if item:
                    item.setbackground(qt.white)
        found = false
        for row in range(self.model.rowcount()):
            for col in range(self.model.columncount()):
                if column_idx != -1 and col != column_idx:
                    continue
                item = self.model.item(row, col)
                if item and search_text in item.text().lower():
                    item.setbackground(qt.yellow)
                    found = true
                    # 滚动到第一个匹配项
                    if not found:
                        self.table_view.scrollto(self.model.index(row, col))
        if found:
            self.statusbar().showmessage(f"找到匹配 '{search_text}' 的内容", 3000)
        else:
            self.statusbar().showmessage(f"未找到匹配 '{search_text}' 的内容", 3000)
    def show_context_menu(self, position):
        if self.df is none:
            return
        menu = qmenu()
        edit_action = qaction("编辑单元格", self)
        edit_action.triggered.connect(self.edit_current_cell)
        menu.addaction(edit_action)
        add_row_action = qaction("添加行", self)
        add_row_action.triggered.connect(self.add_row)
        menu.addaction(add_row_action)
        delete_row_action = qaction("删除行", self)
        delete_row_action.triggered.connect(self.delete_row)
        menu.addaction(delete_row_action)
        menu.addseparator()
        copy_action = qaction("复制", self)
        copy_action.triggered.connect(self.copy_selection)
        menu.addaction(copy_action)
        menu.exec_(self.table_view.viewport().maptoglobal(position))
    def edit_current_cell(self):
        index = self.table_view.currentindex()
        if not index.isvalid():
            return
        old_value = self.model.itemfromindex(index).text()
        new_value, ok = qinputdialog.gettext(
            self, "编辑单元格", 
            f"输入新值 (原值: {old_value}):", 
            text=old_value
        )
        if ok and new_value != old_value:
            self.model.itemfromindex(index).settext(new_value)
    def copy_selection(self):
        selected = self.table_view.selectionmodel().selectedindexes()
        if not selected:
            return
        # 获取选中的最小和最大行列
        min_row = min(index.row() for index in selected)
        max_row = max(index.row() for index in selected)
        min_col = min(index.column() for index in selected)
        max_col = max(index.column() for index in selected)
        # 构建文本
        text = ""
        for row in range(min_row, max_row + 1):
            row_text = []
            for col in range(min_col, max_col + 1):
                index = self.model.index(row, col)
                item = self.model.itemfromindex(index)
                row_text.append(item.text() if item else "")
            text += "\t".join(row_text) + "\n"
        # 复制到剪贴板
        clipboard = qapplication.clipboard()
        clipboard.settext(text.strip())
        self.statusbar().showmessage("已复制选中内容到剪贴板", 2000)
if __name__ == "__main__":
    app = qapplication(sys.argv)
    editor = exceleditor()
    editor.show()
    sys.exit(app.exec_())

pip install pyinstaller
pyinstaller --onefile --windowed excel_editor.py