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_())