前回の記事からの続きです。

前回の記事

はじめに

私は現在、機械設計に関わる仕事をしてます。設計では計算シートから応力や強度を算出して材料や寸法を決めています。
計算シートはエクセルで作られており、お客様に提出するシートだけでも最大で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_())

終わりに

追加しようと思っている機能（ドラッグ＆ドロップ）はあるんですがなかなか上手くいきませんでした。