def save_excel(rank):
wb = load(SAVE_DIR)
ws = wb.create_sheet('rank')
idx = 1
try:
for k, v in rank[:15]:
ws['A' + str(idx)] = "{}({})".format(k, v)
idx += 1
wb.save(SAVE_DIR)
except Exception as e:
print(e)
finally:
wb.close()
def save_excel(rank) : # rank호출해서 처리함
wb = load(SAVE_DIR)
ws = wb.create_sheet('rank')
idx = 1 # A행에 쓸 인덱스
# 외부자원사용하기에 에러발생시 대처해야함
try :
for k, v in rank[:15] :
# A행의 셀에 저장, 문자열(인덱스)
ws['A' + str(idx)] = "{}({})".format(k,v)
idx += 1
wb.save(SAVE_DIR)
except Exception as e :
print(e)
finally :
wb.close()
def __init__(self, workbook_filepath, sheet_name):
self.wb = None
self.sheet = None
self.workbook_filepath = workbook_filepath
self.sheet_name = sheet_name
try:
##set workbook as file object
self.wb = load(self.workbook_filepath, data_only=True)
except IOError:
self.wb = None
print("Could not load: " + self.workbook_filepath)
try:
##get sheet as file object
self.sheet = self.wb[self.sheet_name]
except:
print("Sheet not found " + self.sheet_name + " in workbook " +
self.workbook_filepath)
def read_excel(file_name, num_entries):
wb = oxl.load(file_name) #Gets the excel file
sheet = wb.get_sheet_names()[
0] #Loads sheet from excel file into a dataframe
survival_times = []
survival_probability = [] #Creates empty arrays to populate
temp_survival_prob = 1
iterator = 1
for i in range(2, numentries + 2):
#Uses formula for survival probability then appends to list
temp_survival_prob *= 1 - iterator / (num_entries - iterator)
iterator += 1
survival_probability.append(temp_survival_prob)
survivial_times.append(sheet.cell(colum=2, row=i))
return survival_times, survival_probability #returns two lists for plotting
def test():
wb = load('Personal Information.xlsx')
info, keys = parse(wb)
img = np.random.randint(0, 255, (100, 100), dtype=np.uint8)
data = {
'Name': 'YX Dragon',
'Photo': img,
'Sex': 'Male',
'Age': '30',
'Height': 170,
'Weight': 75.0,
'Like_Sport': True
}
fill_value(wb, info, data)
repair(wb)
from tkinter import filedialog
import os.path as osp
direc = filedialog.askdirectory()
wb.save(osp.join(direc, 'person.xlsx'))
def makeNameList(self):
q0 = 'create table if not exists namelist (name varchar(200) primary key ,loc varchar(100))'
q1 = 'delete from namelist'
q2 = 'insert into namelist(name,loc) values(?,?)'
dupCounter = 0
dataFile = load('./data/files/Search.xlsx')
con = sql.connect(self.db, isolation_level=None)
cur = con.cursor()
cur.execute(q0)
cur.execute(q1)
for sheet in range(len(dataFile.sheetnames)):
dataFile._active_sheet_index = sheet
dataSheet = dataFile.active
nameSet = dataSheet['D'][1:]
locSet = dataSheet['C'][1:]
for name, loc in zip(nameSet, locSet):
if name.value is None:
break
try:
cur.execute(q2, (name.value, loc.value))
except sql.IntegrityError:
dupCounter += 1
print(dupCounter, name.value, loc.value)
pass
def save_excel(get_tags) :
wb = load(SAVE_DIR)
ws = wb.create_table('title')
wb.save(SAVE_DIR)
wb.close()
from openpyxl import load_workbook as load
DIR = 'D:\작업\CODE\Python3\code09\main.xlsx'
wb = load(DIR)
# ws = wb.create_sheet('test')
ws = wb['test']
# ws['A1'] = "제목1"
# ws['A2'] = "제목2"
a1 = ws['A2'].value
a2 = ws['B2'].value
# wb.save(DIR)
wb.close()
#Lab1.2 Создание графика из таблици XL
from matplotlib import pyplot
from openpyxl import load_workbook as load
#Загружаем XL файл
XL = load('data_analysis_lab.xlsx')
#Открываем нужную унигу по названию Data
List = XL['Data']
#Создаем фунецию извлечения значения из ячейки
def val(x):
return x.value
#Создаем строку из значений в столбцах A,C,D
Year = list(map(val, List['A'][1:]))
Temp = list(map(val, List['C'][1:]))
Active = list(map(val, List['D'][1:]))
#Создаем 2 графика соотнашения столбцов А-С и А-D
pyplot.plot(Year, Temp, label="Метка")
pyplot.plot(Year, Active, label="Метка")
#Вывод графика
pyplot.show()
DDdict = {}
def ignore(list_of_names, ignore_terms):
for term in ignore_terms:
for name_part in list_of_names:
if term in name_part:
return True
return False
workbook = "../2019/Gift Aid Analysis 2019_blank.xlsx"
csv_file = "../2019/AlexForExport02_01_20_ABC"
try:
wb = load(workbook, data_only=True)
except PermissionError:
print("You currently have the excel workbook open")
raise
"""search through excel names for match in name from sage"""
def xl_nm_indx_fndr(sage_name_parts, sheet, num_excel_names=82):
name_index = 0
while name_index <= num_excel_names:
excel_name = str(sheet.cell(column=3, row=(name_index + 5)).value)
#attempts to match name in excel file...
#with name in csv (all lower case)
for name_part in sage_name_parts:
if excel_name.lower() == name_part:
return name_index
from openpyxl import load_workbook as load
path = r'D:\Docs\Works\InProgress\Thesis\\'
src = path + 'data.xlsx'
dst = path + 'converted.xlsx'
wb1 = load(src)
ws1 = wb1.active
wb2 = load(dst)
ws2 = wb2.active
# 14
prob = {'不可能': 1, '不太可能': 2, '中立': 3, '可能': 4, '很大可能': 5}
# 6, 10, 11, 12, 13
agr = {'强烈反对': 1, '不同意': 2, '中性': 3, '同意': 4, '非常同意': 5}
# 7, 8
yn = {'否': 0, '是的': 1}
# 9
apr = {'强烈反对': 1, '不是很赞赏': 2, '中立': 3, '赞赏': 4, '非常赞赏': 5}
# 2
age = {
'18岁以下': 1,
'18~24岁': 2,
'25~30岁': 3,
'31~40岁': 4,
'41~50岁': 5,
def save_excel(get_tags):
wb = load(SAVE_DIR)
ws = wb.create_sheet('rank')
wb.save(SAVE_DIR)
wb.close()