Created
April 4, 2012 04:59
-
-
Save nobrinskii/2297890 to your computer and use it in GitHub Desktop.
D&Dで受け取ったパスのファイルに含まれるワークシートの保護を設定する。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!ruby -Ks | |
require 'win32ole' | |
wsh = WIN32OLE.new('WScript.Shell') | |
# | |
# ユーザーが使用する前に設定する項目 | |
# | |
SHEET_LOCK = true #true:保護設定 false:保護解除 | |
PROTECT_PASSWORD = "" #パスワード(必要なときのみ) | |
# | |
# === シートの保護を設定する | |
# | |
# param sheet:: 保護を設定するワークシート | |
# | |
def protectSheet(sheet) | |
if !sheet.ProtectContents then | |
sheet.Protect('DrawingObjects' => true, \ | |
'Contents' => true, \ | |
'Password' => PROTECT_PASSWORD, \ | |
'UserInterfaceOnly' => true, \ | |
'Structure' => true, \ | |
'Windows' => true, \ | |
'AllowFormattingCells' => true, \ | |
'AllowFormattingColumns' => true, \ | |
'AllowFormattingRows' => true, \ | |
'AllowInsertingColumns' => true, \ | |
'AllowInsertingRows' => true, \ | |
'AllowInsertingHyperlinks' => true, \ | |
'AllowDeletingColumns' => true, \ | |
'AllowDeletingRows' => true, \ | |
'AllowSorting' => true, \ | |
'AllowFiltering' => true, \ | |
'AllowUsingPivotTables' => true, \ | |
'Scenarios' => true \ | |
) | |
end | |
end | |
# | |
# === シートの保護を解除する | |
# | |
# param sheet:: 保護を解除するワークシート | |
# | |
def unprotectSheet(sheet) | |
if sheet.ProtectContents then | |
if PROTECT_PASSWORD == "" then | |
sheet.Unprotect | |
else | |
sheet.Unprotect('Password' => PROTECT_PASSWORD) | |
end | |
end | |
end | |
# | |
# === 各予算表シーと内の処理 | |
# ブックに含まれるワークシートを予算表シートか確認し、ロックのパラメータに従って | |
# 書込に使用する関数に割り振る。 | |
# | |
# param book:: ワークブック | |
# | |
def processInSheets(book) | |
sheets = book.Worksheets | |
sheets.each do |sheet| | |
if SHEET_LOCK then | |
protectSheet(sheet) | |
else | |
unprotectSheet(sheet) | |
end | |
end | |
end | |
#-- | |
# | |
#以下、処理部分 | |
# | |
#++ | |
excel = WIN32OLE.new('Excel.Application') | |
excel.visible = false | |
book = nil | |
path = nil | |
begin | |
ARGV.each do |path| | |
if FileTest::directory?(path) | |
next | |
end | |
book = excel.Workbooks.Open(path) | |
processInSheets(book) | |
book.close('SaveChanges' => true) | |
end | |
rescue => e | |
book.close('SaveChanges' => false) | |
puts path | |
puts e.message | |
puts e.backtrace | |
ensure | |
excel.visible = true | |
excel.quit | |
end | |
wsh.popup("完了しました。") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment