OCS Inventory 匯出到 Google 試算表的程式是從網路上其他高手分享出來(
參考資料出處)，並依個人需求調整。
如要取用請注意，因程式是用 python 寫的，程式段落可能會移位導致無法正常運作。
下列程式調整為依電腦設備寫入同算表不同分頁，過濾掉電腦上所安裝不想列出的系統修補軟體。
程式內容:
#coding=utf-8
#-*- coding: utf-8 -*-
import MySQLdb as mysqldb
import sys
import gspread
import string
def DB_return(exec_cmd):
connection = mysqldb.connect('localhost', 'ocs', 'ocspasswd', 'ocsdb',charset='utf8');
with connection:
cursor = connection.cursor()
cursor.execute(exec_cmd)
rows_info = cursor.fetchall()
return rows_info
def GSheet_write(GSpreadSheet,GDriveJSON,tab_data,data,worksheet_name):
a_list=[]
for i, element in enumerate(tab_data):
a_list.append(tab_data[i])
a_tup = tuple(a_list)
start_row = 1
start_letter = 'A'
end_len = len(data[0]) - 1
if end_len > 26:
len_info = end_len - 26
len_a='A'
else:
len_info = end_len
len_a=''
end_letter = string.uppercase[len_info]
end_letter = len_a+end_letter
end_row = len(data)
range = "%s%d:%s%d" % (start_letter, start_row+1 , end_letter, end_row+1)
range_tab = "%s%d:%s%d" % (start_letter, start_row , end_letter ,start_row)
from oauth2client.service_account import ServiceAccountCredentials as SAC
try:
scope = ['https://spreadsheets.google.com/feeds']
key = SAC.from_json_keyfile_name(GDriveJSON, scope)
gc = gspread.authorize(key)
workbook = gc.open(GSpreadSheet)
if worksheet_name in [sheet.title for sheet in workbook.worksheets()]:
workbook.del_worksheet(workbook.worksheet(worksheet_name))
wks = workbook.add_worksheet(worksheet_name,end_row+1,end_len+1)
else:
wks = workbook.add_worksheet(worksheet_name,end_row+1,end_len+1)
except Exception as ex:
print('connect google fail ', ex)
sys.exit(1)
cell_list = wks.range(range)
try:
idx = 0
for (start_row , rowlist) in enumerate(data):
for (colnum, value) in enumerate(rowlist):
cell_list[idx].value = value
idx += 1
if idx >= len(cell_list):
break
wks.update_cells(cell_list)
except:
print "Exception"
cell_list_tab = wks.range(range_tab)
try:
idx = 0
for (start_row , value) in enumerate(a_tup):
cell_list_tab[idx].value = value
idx += 1
if idx >= len(cell_list_tab):
break
wks.update_cells(cell_list_tab)
except:
print "Exception"
GDriveJSON = '
GoogleAuthKey.json'
GSpreadSheet = '
試算表檔名'
rows_item = ['HardwareID', 'UserName', 'Computer', 'CPU', 'RAM(MB)', 'HD_Model', 'HD_Size(MB)', 'Monitor', 'Software']
connection = mysqldb.connect('localhost', 'ocs', 'ocspasswd', 'ocsdb',charset='utf8');
with connection:
cursor = connection.cursor()
cursor.execute("select * from accountinfo where TAG not like '%出售%' and TAG not like '%報廢%'")
numrows = int(cursor.rowcount)
for i in range(numrows):
row = cursor.fetchone()
GsheetName = row[1]
reload(sys)
sys.setdefaultencoding('utf-8')
rows_accest = DB_return("SELECT DISTINCT \
A.HARDWARE_ID,A.TAG,H.NAME,H.PROCESSORT,H.MEMORY,S.NAME,S.DISKSIZE,M.CAPTION,SF.NAME \
from accountinfo as A \
join hardware as H \
join monitors as M \
join storages as S \
join softwares as SF \
where \
A.HARDWARE_ID=H.ID \
and A.HARDWARE_ID=S.HARDWARE_ID \
and S.TYPE='Fixed hard disk media' \
and A.HARDWARE_ID=M.HARDWARE_ID \
and A.HARDWARE_ID=SF.HARDWARE_ID \
and A.HARDWARE_ID=H.ID \
and SF.NAME not like '%Hotfix%' \
and SF.NAME not like '%Update%' \
and SF.NAME not like '%安全性更新%' \
and SF.NAME not like '%更新%' \
and SF.NAME not like '%驅動程式%' \
and A.TAG like '" + GsheetName + "' \
ORDER BY H.NAME")
GSheet_write(GSpreadSheet,GDriveJSON,rows_item,rows_accest,GsheetName)
