前回の記事からの続きです。
はじめに
私は現在、機械設計に関わる仕事をしてます。設計では計算シートから応力や強度を算出して材料や寸法を決めています。
計算シートはエクセルで作られており、お客様に提出するシートだけでも最大で100枚を超えることがあります。
またISOに沿ったツリー構成をしているため、ファイル数だけでも100個ほどあります。(sharepointを活用すればいいじゃんはいずれ....)
業務の効率化のためにツリービュー構造かつエクセルへアクセスできるようなアプリを作れば仕事の効率化できるのでは？と思い作りました。
1.追加内容
✅ Excel連携機能（Windows限定）
ツール > 「Excelを読み込む」チェックボックス追加
チェック時のみ win32com.client で 起動中のExcelアプリケーションからブックとシートを取得
タブ操作が可能にコピーや切り取り等にも対応
EXCEL_APP = "Ket.Application"
#EXCEL_APP = ""Excel.Application""
を切り替えることでサードパーティー製のエクセル互換ソフトにも対応
※筆者はWPSを使用してます
✅ 写真
2.実行方法
tool_view_excel.py
3.ライセンス
生成AIによって作成したものをwin32に関する項目を加筆
個人で使用するには場合は構いませんが、
仕事で使用する場合はコメントで知らせてください。
4.プログラム
tool_view_excel.py
import sys
import unicodedata
import os
import subprocess
import shutil
import pythoncom
import win32com.client
from PyQt5.QtWidgets import (
QApplication, QMainWindow, QWidget, QVBoxLayout, QLabel, QMenuBar,
QAction, QFileDialog, QInputDialog, QPushButton, QHBoxLayout,
QMessageBox, QTreeView, QFileSystemModel, QMenu
)
from PyQt5.QtGui import QStandardItemModel, QStandardItem
from PyQt5.QtCore import Qt, QDir, QPoint
FAVORITES_FILE = ".favorites.txt"
#EXCEL_APP = "Ket.Application"
EXCEL_APP = ""Excel.Application""
def get_east_asian_width_count(text):
count = 0
for c in text:
if unicodedata.east_asian_width(c) in 'FWA':
count += 2
else:
count += 1
return count
class FileExplorer(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("Py File Explorer")
self.setGeometry(0, 0, 200, 900)
self.current_path = QDir.homePath()
self.favorites = []
self.always_on_top = False
self.excel_enabled = True
self.excel_openflag = False
self.excel_tabs_visible = False
self.excel_cut_string = None
self.excel_copy_string = None
self.clipboard_path = None
self.clipboard_cut = False
self.excel_app = None
self.current_workbook = None
central = QWidget()
self.setCentralWidget(central)
self.layout = QVBoxLayout(central)
top_bar = QHBoxLayout()
self.back_button = QPushButton("⬅ 上に戻る")
self.back_button.setFixedWidth(120)
self.back_button.clicked.connect(self.go_up)
self.path_label = QLabel()
top_bar.addWidget(self.back_button)
top_bar.addWidget(self.path_label)
self.layout.addLayout(top_bar)
self.model = QFileSystemModel()
self.model.setRootPath(self.current_path)
self.tree = QTreeView()
self.tree.setModel(self.model)
self.tree.setRootIndex(self.model.index(self.current_path))
self.tree.setColumnWidth(0, 300)
self.tree.setContextMenuPolicy(Qt.CustomContextMenu)
self.tree.customContextMenuRequested.connect(self.show_context_menu)
self.tree.clicked.connect(self.on_tree_clicked)
self.tree.doubleClicked.connect(self.on_tree_double_clicked)
self.layout.addWidget(self.tree)
self.menu_bar = QMenuBar()
self.setMenuBar(self.menu_bar)
self.setup_menus()
start_path = os.getcwd()
self.model.setRootPath(start_path)
self.tree.setRootIndex(self.model.index(start_path))
self.current_path = start_path
self.ensure_favorites_file()
self.load_favorites()
self.update_path_label()
def setup_menus(self):
file_menu = self.menu_bar.addMenu("ファイル")
open_dir = QAction("別のディレクトリを開く", self)
open_dir.triggered.connect(self.select_directory)
file_menu.addAction(open_dir)
add_fav = QAction("お気に入りに追加", self)
add_fav.triggered.connect(self.add_to_favorites)
file_menu.addAction(add_fav)
exit_action = QAction("終了", self)
exit_action.triggered.connect(self.close)
file_menu.addAction(exit_action)
self.favorite_menu = self.menu_bar.addMenu("お気に入り")
tool_menu = self.menu_bar.addMenu("ツール")
font_size_action = QAction("文字サイズを変更", self)
font_size_action.triggered.connect(self.change_font_size)
tool_menu.addAction(font_size_action)
topmost_action = QAction("常に前面に表示", self, checkable=True)
topmost_action.setChecked(True)
topmost_action.triggered.connect(self.toggle_always_on_top)
tool_menu.addAction(topmost_action)
self.toggle_always_on_top(Qt.Checked)
excel_toggle = QAction("Excelを読み込む", self, checkable=True)
excel_toggle.setChecked(True)
excel_toggle.triggered.connect(self.toggle_excel)
tool_menu.addAction(excel_toggle)
def dropEvent(self, event):
print("s")
def update_path_label(self):
max_width = self.width() - 150 # ボタン等を考慮した余白
metrics = self.path_label.fontMetrics()
elided = metrics.elidedText(f"📁 : {self.current_path}", Qt.ElideLeft, max_width)
self.path_label.setText(elided)
def go_up(self):
if self.excel_tabs_visible:
self.populate_tree()
self.excel_tabs_visible = False
else:
parent = os.path.dirname(self.current_path)
if os.path.exists(parent):
self.current_path = parent
self.tree.setRootIndex(self.model.index(self.current_path))
self.update_path_label()
if(self.excel_openflag):
self.excel_openflag = False
#self.excel_app.Quit() #エクセルを消したいときは
self.excel_app = None
def on_tree_clicked(self, index):
self.tree.expand(index)
print("1click")
if (self.excel_openflag and self.excel_app):
try:
target_sheet = self.current_workbook.Sheets(index.data()) # または wb.Sheets(2) など
target_sheet.Activate() # これで画面上の表示もそのシートに切り替わる
self.show_excel_tabs(self.current_workbook)
except Exception as e:
QMessageBox.warning(self, "Excelエラー", f"Excelファイルの処理中にエラーが発生しました:\n{e}")
self.go_up()
def on_tree_double_clicked(self, index):
print("2click")
if(not self.excel_openflag and not self.excel_app):
path = self.model.filePath(index)
if os.path.isdir(path):
self.current_path = path
self.tree.setRootIndex(self.model.index(path))
self.update_path_label()
else:
ext = os.path.splitext(path)[1].lower()
if self.excel_enabled and ext in [".xls", ".xlsx"]:
self.handle_excel_file(path)
else:
self.open_with_default_app(path)
def handle_excel_file(self, path):
try:
pythoncom.CoInitialize()
# すでに起動している場合接続
self.excel_app = win32com.client.GetActiveObject(EXCEL_APP)
self.excel_openflag = True
except Exception:
pythoncom.CoInitialize()
# 起動していなければ新しく起動
self.excel_app = win32com.client.Dispatch(EXCEL_APP)
self.excel_openflag = True
try:
wb = None
for book in self.excel_app.Workbooks:
if os.path.abspath(book.FullName) == os.path.abspath(path):
wb = book
break
if wb is None:
wb = self.excel_app.Workbooks.Open(path)
self.excel_app.Visible = True
self.current_workbook = wb
self.show_excel_tabs(wb)
except Exception as e:
QMessageBox.warning(self, "Excelエラー", f"Excelファイルの処理中にエラーが発生しました:\n{e}")
def show_excel_tabs(self, workbook):
model = QStandardItemModel()
model.setHorizontalHeaderLabels(["Excelシート"])
for sheet in workbook.Sheets:
item = QStandardItem(sheet.Name)
item.setData(sheet.Name, Qt.UserRole)
model.appendRow(item)
self.tree.setModel(model)
self.excel_tabs_visible = True
def activate_excel_sheet(self, index):
if not self.current_workbook:
return
sheet_name = index.data()
try:
self.current_workbook.Sheets(sheet_name).Activate()
except Exception as e:
QMessageBox.warning(self, "シート切り替えエラー", str(e))
def open_with_default_app(self, path):
try:
if sys.platform == 'win32':
os.startfile(path)
elif sys.platform == 'darwin':
subprocess.Popen(['open', path])
else:
subprocess.Popen(['xdg-open', path])
except Exception as e:
QMessageBox.warning(self, "エラー", f"ファイルを開けませんでした:\n{e}")
def select_directory(self):
dir_path = QFileDialog.getExistingDirectory(self, "ディレクトリを選択", self.current_path)
if dir_path:
self.current_path = dir_path
self.tree.setRootIndex(self.model.index(self.current_path))
self.update_path_label()
def change_font_size(self):
size, ok = QInputDialog.getInt(self, "フォントサイズ変更", "新しいフォントサイズ:", 10, 6, 40)
if ok:
font = self.tree.font()
font.setPointSize(size)
self.tree.setFont(font)
self.path_label.setFont(font)
def toggle_always_on_top(self, checked):
self.always_on_top = checked
flags = self.windowFlags()
if self.always_on_top:
self.setWindowFlags(flags | Qt.WindowStaysOnTopHint)
else:
self.setWindowFlags(flags & ~Qt.WindowStaysOnTopHint)
self.show()
def toggle_excel(self, checked):
self.excel_enabled = checked
def ensure_favorites_file(self):
if not os.path.exists(FAVORITES_FILE):
with open(FAVORITES_FILE, 'w', encoding='utf-8') as f:
pass
def add_to_favorites(self):
folder = QFileDialog.getExistingDirectory(self, "お気に入りに追加するフォルダを選択", self.current_path)
if folder and folder not in self.favorites:
self.favorites.append(folder)
self.save_favorites()
self.refresh_favorite_menu()
def load_favorites(self):
self.favorites.clear()
try:
with open(FAVORITES_FILE, 'r', encoding='utf-8') as f:
for line in f:
folder = line.strip()
if os.path.isdir(folder):
self.favorites.append(folder)
except Exception:
pass
self.refresh_favorite_menu()
def save_favorites(self):
with open(FAVORITES_FILE, 'w', encoding='utf-8') as f:
for path in self.favorites:
f.write(path + '\n')
def refresh_favorite_menu(self):
self.favorite_menu.clear()
for folder in self.favorites:
open_action = QAction(folder, self)
open_action.triggered.connect(lambda checked=False, path=folder: self.open_favorite(path))
self.favorite_menu.addAction(open_action)
del_action = QAction(f"❌ {folder} を削除", self)
del_action.triggered.connect(lambda checked=False, path=folder: self.remove_favorite(path))
self.favorite_menu.addAction(del_action)
def open_favorite(self, path):
if os.path.isdir(path):
self.current_path = path
self.tree.setModel(self.model)
self.tree.setRootIndex(self.model.index(path))
self.update_path_label()
def remove_favorite(self, path):
confirm = QMessageBox.question(self, "削除確認", f"{path} をお気に入りから削除しますか？",
QMessageBox.Yes | QMessageBox.No)
if confirm == QMessageBox.Yes:
if path in self.favorites:
self.favorites.remove(path)
self.save_favorites()
self.refresh_favorite_menu()
def populate_tree(self):
self.tree.setModel(self.model)
self.tree.setRootIndex(self.model.index(self.current_path))
self.update_path_label()
self.tree.doubleClicked.connect(self.on_tree_double_clicked)
def show_context_menu(self, position):
if (not self.excel_openflag and not self.excel_app):
index = self.tree.indexAt(position)
if not index.isValid():
return
path = self.model.filePath(index)
menu = QMenu()
if os.path.isfile(path):
rename_action = QAction("名前の変更", self)
rename_action.triggered.connect(lambda: self.rename_file(path))
menu.addAction(rename_action)
open_action = QAction("開く", self)
open_action.triggered.connect(lambda: self.open_with_default_app(path))
menu.addAction(open_action)
if os.path.isfile(path):
delete_action = QAction("削除", self)
delete_action.triggered.connect(lambda: self.delete_file(path))
menu.addAction(delete_action)
menu.exec_(self.tree.viewport().mapToGlobal(position))
elif (self.excel_openflag and self.excel_app):
index = self.tree.indexAt(position)
if not index.isValid():
return
menu = QMenu()
rename_action = QAction("名前の変更", self)
rename_action.triggered.connect(lambda: self.rename_excel_tab(index))
menu.addAction(rename_action)
menu.addSeparator()
add_action = QAction("新規シート追加", self)
add_action.triggered.connect(lambda: self.add_excel_tab())
menu.addAction(add_action)
paste_action = QAction("貼付", self)
paste_action.triggered.connect(lambda: self.paste_excel_tab(index))
if(not self.excel_cut_string and not self.excel_copy_string):
paste_action.setEnabled(False)
else:
paste_action.setEnabled(True)
menu.addAction(paste_action)
move_action = QAction("切り取り", self)
move_action.triggered.connect(lambda: self.move_excel_tab(index))
menu.addAction(move_action)
copy_action = QAction("コピー", self)
copy_action.triggered.connect(lambda: self.copy_excel_tab(index))
menu.addAction(copy_action)
delete_action = QAction("削除", self)
delete_action.triggered.connect(lambda: self.delete_excel_tab(index))
menu.addAction(delete_action)
menu.addSeparator()
save_action = QAction("上書き保存", self)
save_action.triggered.connect(lambda: self.save_excel_tab())
menu.addAction(save_action)
newsave_action = QAction("別名で保存", self)
newsave_action.triggered.connect(lambda: self.newsave_excel_tab())
menu.addAction(newsave_action)
menu.addSeparator()
exit_action = QAction("excelを閉じる", self)
exit_action.triggered.connect(lambda: self.exit_excel_tab())
menu.addAction(exit_action)
menu.exec_(self.tree.viewport().mapToGlobal(position))
def delete_file(self, path):
try:
os.remove(path)
QMessageBox.information(self, "削除完了", f"{os.path.basename(path)} を削除しました")
self.model.refresh()
except Exception as e:
QMessageBox.warning(self, "削除失敗", str(e))
def rename_file(self, path):
new_name, ok = QInputDialog.getText(self, "名前の変更", "新しい名前:", text=os.path.basename(path))
if ok and new_name:
new_path = os.path.join(os.path.dirname(path), new_name)
try:
os.rename(path, new_path)
QMessageBox.information(self, "名前変更", f"{path} → {new_path}")
self.model.refresh()
except Exception as e:
QMessageBox.warning(self, "名前変更失敗", str(e))
def rename_excel_tab(self, index):
new_name, ok = QInputDialog.getText(self, "タブの名前の変更", "タブの新しい名前:", text=index.data())
if(get_east_asian_width_count(new_name)>32):
QMessageBox.warning(self, "タブは半角文字31文字全角、全角文字15文字までです。")
return
if ok and new_name:
try:
ws = self.current_workbook.Sheets(index.data())
ws.Name = new_name
self.show_excel_tabs(self.current_workbook)
except Exception as e:
QMessageBox.warning(self, "名前変更失敗", str(e))
def copy_excel_tab(self, index):
self.excel_cut_string = None
self.excel_copy_string = index.data()
print("copy:",self.excel_copy_string," cut:",self.excel_cut_string)
def move_excel_tab(self, index):
self.excel_cut_string = index.data()
self.excel_copy_string = None
print("copy:",self.excel_copy_string," cut:",self.excel_cut_string)
def paste_excel_tab(self, index):
try:
print("copy:",self.excel_copy_string," cut:",self.excel_cut_string)
if(self.excel_cut_string and not self.excel_copy_string):
worksheet = self.current_workbook.Sheets(self.excel_cut_string)
worksheet.Move(Before=None,After=self.current_workbook.Sheets(index.data()))
self.show_excel_tabs(self.current_workbook)
self.excel_cut_string = None
self.excel_copy_string = None
self.show_excel_tabs(self.current_workbook)
elif(not self.excel_cut_string and self.excel_copy_string):
worksheet = self.current_workbook.Sheets(self.excel_copy_string)
worksheet.Copy(Before=None,After=self.current_workbook.Sheets(index.data()))
self.show_excel_tabs(self.current_workbook)
self.excel_cut_string = None
except Exception as e:
QMessageBox.warning(self, "名前変更失敗", str(e))
def add_excel_tab(self):
new_name, ok = QInputDialog.getText(self, "新規シート挿入", "タブの新しい名前:", text="Sheet1")
if(get_east_asian_width_count(new_name)>32):
QMessageBox.warning(self, "タブは半角文字31文字全角、全角文字15文字までです。")
return
try:
sheet_name = self.current_workbook.ActiveSheet.Name
# 一番左のシートを取得
ws = self.current_workbook.Sheets(sheet_name)
# 一番左にシートを追加する
self.current_workbook.Sheets.Add(Before=ws)
ws.Name = new_name
self.show_excel_tabs(self.current_workbook)
except Exception as e:
QMessageBox.warning(self, "追加失敗", str(e))
self.show_excel_tabs(self.current_workbook)
def delete_excel_tab(self,index):
try:
ws = self.current_workbook.Sheets(index.data())
ws.Delete()
self.show_excel_tabs(self.current_workbook)
except Exception as e:
QMessageBox.warning(self, "削除失敗", str(e))
self.show_excel_tabs(self.current_workbook)
def save_excel_tab(self):
try:
self.current_workbook.Save()
except Exception as e:
QMessageBox.warning(self, "上書き保存失敗", str(e))
def newsave_excel_tab(self):
try:
oldfullpath = self.excel_app.ActiveWorkbook.FullName
newfullpath = show_save_dialog(self.current_workbook.Name)
self.current_workbook.SaveAs(newfullpath)
self.show_excel_tabs(self.current_workbook)
self.swich_UI(newfullpath,oldfullpath)
except Exception as e:
QMessageBox.warning(self, "新規保存失敗", str(e))
def swich_UI(self,path,oldpath):
buttonReply = QMessageBox.question(self, "メッセージ", "新規保存したシートにスイッチしますか？", QMessageBox.Yes | QMessageBox.No, QMessageBox.No)
if buttonReply == QMessageBox.Yes:
self.handle_excel_file(path)
else:
self.exit_excel_tab()
self.handle_excel_file(oldpath)
def exit_excel_tab(self):
self.excel_app.Quit()
self.go_up()
def show_save_dialog(defult_name):
window_saveas = QWidget()
# 名前を付けて保存ダイアログを表示
file_path, _ = QFileDialog.getSaveFileName(
parent=window_saveas,
caption="名前を付けて保存",
directory=defult_name,
filter="Excelファイル (*.xlsx *.xls);;CSVファイル (*.csv);;すべてのファイル (*.*)"
)
if file_path:
return file_path
else:
return None
if __name__ == "__main__":
app = QApplication(sys.argv)
window = FileExplorer()
window.show()
sys.exit(app.exec_())
終わりに
追加しようと思っている機能（ドラッグ＆ドロップ）はあるんですがなかなか上手くいきませんでした。