python2读取excel中文处理,【Python】【源码】利用Python读取Excel文件-续
[Python] 純文本查看 復制代碼"""
**************************************************
*** Created by WangLuxiang at 1/23/2021 1:09 PM on PyCharm.
*** MainWindow.py
*** TODO 下一版本增加掃描子目錄支持
*** TODO 下一版本添加表格可視化選擇
**************************************************
"""
from PyQt5.QtWidgets import QMainWindow, QFileDialog, QMessageBox, QWidget, QTableWidget, QMessageBox
import xlrd
import os
from itertools import groupby
import openpyxl
import time
from PyQt5.QtWidgets import QApplication
import sys
# import pysnooper
# import Table
from PyQt5 import QtCore, QtGui, QtWidgets
class Ui_Windows(object):
def setupUi(self, Windows):
Windows.setObjectName("Windows")
Windows.setEnabled(True)
Windows.resize(641, 300)
sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Fixed, QtWidgets.QSizePolicy.Fixed)
sizePolicy.setHorizontalStretch(0)
sizePolicy.setVerticalStretch(0)
sizePolicy.setHeightForWidth(Windows.sizePolicy().hasHeightForWidth())
Windows.setSizePolicy(sizePolicy)
Windows.setMinimumSize(QtCore.QSize(641, 300))
Windows.setMaximumSize(QtCore.QSize(641, 300))
icon = QtGui.QIcon()
icon.addPixmap(QtGui.QPixmap("img/圖標.png"), QtGui.QIcon.Normal, QtGui.QIcon.Off)
Windows.setWindowIcon(icon)
Windows.setWindowOpacity(0.9)
Windows.setAutoFillBackground(True)
self.verticalLayoutWidget = QtWidgets.QWidget(Windows)
self.verticalLayoutWidget.setGeometry(QtCore.QRect(20, 0, 401, 191))
self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
self.verticalLayout = QtWidgets.QVBoxLayout(self.verticalLayoutWidget)
self.verticalLayout.setSizeConstraint(QtWidgets.QLayout.SetDefaultConstraint)
self.verticalLayout.setContentsMargins(0, 0, 0, 0)
self.verticalLayout.setObjectName("verticalLayout")
self.horizontalLayout_3 = QtWidgets.QHBoxLayout()
self.horizontalLayout_3.setObjectName("horizontalLayout_3")
self.file_dir_label = QtWidgets.QLabel(self.verticalLayoutWidget)
palette = QtGui.QPalette()
brush = QtGui.QBrush(QtGui.QColor(0, 0, 0))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Active, QtGui.QPalette.WindowText, brush)
brush = QtGui.QBrush(QtGui.QColor(0, 0, 0))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Inactive, QtGui.QPalette.WindowText, brush)
brush = QtGui.QBrush(QtGui.QColor(120, 120, 120))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Disabled, QtGui.QPalette.WindowText, brush)
self.file_dir_label.setPalette(palette)
self.file_dir_label.setObjectName("file_dir_label")
self.horizontalLayout_3.addWidget(self.file_dir_label)
self.file_dir = QtWidgets.QLineEdit(self.verticalLayoutWidget)
self.file_dir.setStyleSheet("")
self.file_dir.setObjectName("file_dir")
self.horizontalLayout_3.addWidget(self.file_dir)
self.browse_file_dir = QtWidgets.QPushButton(self.verticalLayoutWidget)
self.browse_file_dir.setStyleSheet("")
self.browse_file_dir.setObjectName("browse_file_dir")
self.horizontalLayout_3.addWidget(self.browse_file_dir)
self.verticalLayout.addLayout(self.horizontalLayout_3)
self.horizontalLayout_5 = QtWidgets.QHBoxLayout()
self.horizontalLayout_5.setObjectName("horizontalLayout_5")
self.sheet_name_label = QtWidgets.QLabel(self.verticalLayoutWidget)
self.sheet_name_label.setObjectName("sheet_name_label")
self.horizontalLayout_5.addWidget(self.sheet_name_label)
self.sheet_name = QtWidgets.QComboBox(self.verticalLayoutWidget)
self.sheet_name.setObjectName("sheet_name")
self.horizontalLayout_5.addWidget(self.sheet_name)
self.verticalLayout.addLayout(self.horizontalLayout_5)
self.horizontalLayout_6 = QtWidgets.QHBoxLayout()
self.horizontalLayout_6.setObjectName("horizontalLayout_6")
self.cell_pos_label = QtWidgets.QLabel(self.verticalLayoutWidget)
self.cell_pos_label.setObjectName("cell_pos_label")
self.horizontalLayout_6.addWidget(self.cell_pos_label)
self.cell_pos = QtWidgets.QLineEdit(self.verticalLayoutWidget)
self.cell_pos.setObjectName("cell_pos")
self.horizontalLayout_6.addWidget(self.cell_pos)
self.verticalLayout.addLayout(self.horizontalLayout_6)
self.horizontalLayoutWidget_5 = QtWidgets.QWidget(Windows)
self.horizontalLayoutWidget_5.setGeometry(QtCore.QRect(320, 180, 321, 111))
self.horizontalLayoutWidget_5.setObjectName("horizontalLayoutWidget_5")
self.buttons = QtWidgets.QHBoxLayout(self.horizontalLayoutWidget_5)
self.buttons.setContentsMargins(0, 0, 0, 0)
self.buttons.setObjectName("buttons")
self.cancel = QtWidgets.QPushButton(self.horizontalLayoutWidget_5)
self.cancel.setStyleSheet("")
self.cancel.setObjectName("cancel")
self.buttons.addWidget(self.cancel)
self.begin_read_data = QtWidgets.QPushButton(self.horizontalLayoutWidget_5)
self.begin_read_data.setStyleSheet("")
self.begin_read_data.setCheckable(False)
self.begin_read_data.setObjectName("begin_read_data")
self.buttons.addWidget(self.begin_read_data)
self.label = QtWidgets.QLabel(Windows)
self.label.setGeometry(QtCore.QRect(0, 0, 641, 300))
self.label.setMinimumSize(QtCore.QSize(261, 141))
self.label.setText("")
self.label.setPixmap(QtGui.QPixmap("img/背景.jpeg"))
self.label.setObjectName("label")
self.label_2 = QtWidgets.QLabel(Windows)
self.label_2.setGeometry(QtCore.QRect(10, 270, 181, 16))
self.label_2.setObjectName("label_2")
self.file_num = QtWidgets.QLabel(Windows)
self.file_num.setGeometry(QtCore.QRect(440, 30, 171, 16))
self.file_num.setText("")
self.file_num.setObjectName("file_num")
self.progressBar = QtWidgets.QProgressBar(Windows)
self.progressBar.setGeometry(QtCore.QRect(20, 220, 281, 23))
self.progressBar.setProperty("value", 24)
self.progressBar.setObjectName("progressBar")
self.label.raise_()
self.verticalLayoutWidget.raise_()
self.horizontalLayoutWidget_5.raise_()
self.label_2.raise_()
self.file_num.raise_()
self.progressBar.raise_()
self.retranslateUi(Windows)
QtCore.QMetaObject.connectSlotsByName(Windows)
def retranslateUi(self, Windows):
_translate = QtCore.QCoreApplication.translate
Windows.setWindowTitle(_translate("Windows", "Excel提取工具"))
self.file_dir_label.setText(_translate("Windows", "Excel文件目錄 "))
self.browse_file_dir.setText(_translate("Windows", "選擇文件夾"))
self.sheet_name_label.setText(_translate("Windows", "Excel表名 "))
self.cell_pos_label.setText(_translate("Windows", "Excel單元格位置"))
self.cancel.setText(_translate("Windows", "退出"))
self.begin_read_data.setText(_translate("Windows", "開始提取數據"))
self.label_2.setText(_translate("Windows", "by WangLuxiang on 1/22/2021"))
class MainWindow(QMainWindow, Ui_Windows, QWidget):
def __init__(self):
super(MainWindow, self).__init__()
self.setupUi(self)
# self.begin_read_data.setEnabled(False)
self.event_band()
self.files = []
self.file = []
self.file_dir_path = ""
self.sheet = None
self.xlsx = None
self.rows = 0
self.columns = 0
self.table_head = []
self.data = []
self.progressBar.setValue(0)
# QMessageBox.information(self, '信息提示對話框','前方右拐到達目的地',QMessageBox.Ok)
def event_band(self):
self.browse_file_dir.clicked.connect(self.choose_file_dir)
# self.file_type.currentIndexChanged.connect(self.choose_file_type)
self.file_dir.textChanged.connect(self.file_dir_changed)
self.sheet_name.currentIndexChanged.connect(self.get_sheet)
self.cell_pos.textChanged.connect(self.get_position)
self.begin_read_data.clicked.connect(self.get_data)
self.cancel.clicked.connect(self.close)
# self.pushButton.clicked.connect(self.open_table)
# @pysnooper.snoop()
def file_dir_changed(self):
text = self.file_dir.text()
if text != "":
try:
self.file_dir_path = text
self.files = os.listdir(text)
# print(self.files)
for i in self.files:
# print("tt2")
if i[:2] != "~$" and i[:6] != "output":
# print("tt3")
# print(os.path.join(self.file_dir_path, i))
if os.path.isfile(os.path.join(text, i)):
# print("tt1")
if i[-5:] == ".xlsx":
self.file.append(i)
except OSError:
self.file_num.setText("不存在的目錄")
else:
# self.file_num.setText(f"該目錄共有{len(self.files)}個文件和目錄")
if len(self.file) > 0:
self.file_num.setText(f"該目錄共有{len(self.file)}個xlsx文件")
self.add_sheet_name()
else:
self.file_num.setText("該目錄下沒有xlsx文件")
else:
self.file_num.setText("目錄為空")
def choose_file_dir(self):
self.file_dir_path = QFileDialog.getExistingDirectory(caption="選擇文件夾")
if self.file_dir_path != "":
self.file_dir.setText(self.file_dir_path)
self.files = os.listdir(self.file_dir_path)
if len(self.files) == 0:
QMessageBox.warning(self, "空目錄", "所選目錄為空目錄，請重新選擇目錄")
self.choose_file_dir()
# @pysnooper.snoop()
# def choose_file_type(self):
# # print("tt2")
# current_file_type = self.file_type.currentText()
# if current_file_type == "":
# self.file_type_num.setText("請選擇文件類型")
# else:
# # self.file_type_num.setText("")
# type_num = 0
# # print("tt1")
# for i in self.files:
# if i[:2] != "~$" and i[:6] != "output" and os.path.isfile(
# os.path.join(self.file_dir_path, i)):
# if current_file_type == "xlsx" or current_file_type == "xlsx和xls":
# if i[-len("xlsx"):] == "xlsx":
# type_num += 1
# self.file.append(i)
# if current_file_type == "xls" or current_file_type == "xlsx和xls":
# if i[-len("xls"):] == "xls":
# type_num += 1
# self.file.append(i)
# # print("tt3")
# self.file_type_num.setText(f"該目錄共有{type_num}個{current_file_type}文件")
# self.add_sheet_name()
# # print("tt4")
# def choose_file_type(self):
# for i in self.files:
# if i[:2] != "~$" and i[:6] != "output" and os.path.isfile(os.path.join(self.file_dir_path, i)):
# self.file.append(i)
# self.add_sheet_name()
def add_sheet_name(self):
try:
xlsx = xlrd.open_workbook(os.path.join(self.file_dir_path, self.file[0]))
for i in xlsx.sheet_names():
self.sheet_name.addItem(i)
except Exception as err:
# print(err)
pass
def get_sheet(self):
self.xlsx = xlrd.open_workbook(os.path.join(self.file_dir_path, self.file[0]))
self.sheet_name_ = self.sheet_name.currentText()
self.sheet = self.xlsx.sheet_by_name(self.sheet_name.currentText())
# print(f"rows: {self.sheet.nrows}, cols: {self.sheet.ncols}")
self.rows = self.sheet.nrows
self.columns = self.sheet.ncols
self.table_head = self.sheet.row_values(4)
# print(self.table_head)
def get_position(self):
try:
self.position = [''.join(list(g)) for k, g in groupby(self.cell_pos.text(), key=lambda x: x.isdigit())]
self.row = int(self.position[1])
if len(self.position[0]) > 1:
p = len(self.position[0]) - 1
count = 0
for x in range(len(self.position[0])):
count += (26 ** x) * (ord(self.position[0][p - x]) - 64)
# write_data(get_data(file_path, sheet, row, column))
self.column = count
else:
self.column = ord(self.position[0].upper()) - 64
except Exception as err:
# print(err)
pass
# self.get_data()
def get_data(self):
try:
self.get_position()
def open_work_book(filename, sheet, row, column):
work_book = xlrd.open_workbook(filename)
# print(work_book.sheet_names())
work_book_sheet = work_book.sheet_by_name(sheet)
return work_book_sheet.cell_value(row - 1, column - 1)
# print(f"Thread {threading.get_ident()} begin to read")
# print(get_data_files)
for file in self.file:
self.data.append([os.path.join(self.file_dir_path, file), open_work_book(os.path.join(self.file_dir_path, file), self.sheet_name_, self.row, self.column)])
self.progressBar.setValue(len(self.data) / len(self.file) * 100)
# print(f"file: {file_path + file} read complete! in {threading.get_ident()}")
# print(f"Thread {threading.get_ident()} finished!")
# print(self.data)
self.write_data()
except Exception as err:
# print(err)
pass
def write_data(self):
write_data_data = sorted(self.data, key=lambda x: x[0])
def write_work_book(write_work_book_data):
sheet.append(write_work_book_data)
workbook = openpyxl.Workbook()
sheet = workbook.active
for data in write_data_data:
write_work_book(data)
file_name = "output " + str(time.ctime()).replace(":", ".") + ".xlsx"
workbook.save(file_name)
# QMessageBox.information(self, '完成',f'命令成功完成，輸出文件名：{file_name}',QMessageBox.Yes | QMessageBox.No,QMessageBox.Yes)
QMessageBox.information(self, '完成',f'命令成功完成，輸出文件名：{file_name}',QMessageBox.Ok)
# print("tt1")
self.close()
if __name__ == "__main__":
app = QApplication(sys.argv)
ui = MainWindow()
ui.show()
sys.exit(app.exec_())
總結
以上是生活随笔為你收集整理的python2读取excel中文处理,【Python】【源码】利用Python读取Excel文件-续的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 真友商！vivo小米OPPO：一键换机新
- 下一篇： 4980元！黑烬EL3P正式发布 3寸主