def __init__(self):
super().__init__()
self.filename = "coin.xlsx"
try:
self.wb = load_workbook(filename=self.filename)
self.current_columns = len(self.wb.active[1]) + 1
except FileNotFoundError:
wb = Workbook()
wb.save(self.filename)
self.wb = load_workbook(filename=self.filename)
self.current_columns = 1
def xlsx_grader(hw):
excel_files = []
all_files = os.listdir('.')
for file in all_files:
if file.endswith('.xlsx'):
excel_files.append(file)
elif not file.endswith('.py'):
print(f'{file} will not be opened')
key_folder = f'HW{hw}Key'
key_file = f'HW{hw}_key.xlsx'
key_sheets = {'file name': key_file, 'sheets': {}}
try:
wb = openpyexcel.load_workbook(os.path.join(key_folder, key_file),
data_only=True)
except FileNotFoundError:
helper_tools.input.exit_msg(
f'Please put {key_folder}/{key_file} inside {os.getcwd()}')
for sheet_name in wb.sheetnames:
arr = []
sheet = wb[sheet_name]
for row in sheet.iter_rows():
arr.append([])
for cell in row:
arr[-1].append(cell.value)
key_sheets['sheets'][sheet_name] = arr
student_sheets = []
for student_file in excel_files:
wb = openpyexcel.load_workbook(student_file, data_only=True)
student_sheet_builder = {}
for sheet_name in wb.sheetnames:
arr = []
sheet = wb[sheet_name]
for row in sheet.iter_rows():
arr.append([])
for cell in row:
arr[-1].append(cell.value)
student_sheet_builder[sheet_name] = arr
student_name = f'{student_file.split("_")[1]} {student_file.split("_")[0]}'
student_sheets.append({
'name': student_name,
'file name': student_file,
'sheets': student_sheet_builder
})
excel_data = helper_tools.student_results.ExcelResults()
excel_data.populate(key_sheets, student_sheets)
assets.xlsx_ui.xlsx_ui()
def test_legacy_drawing(datadir):
datadir.chdir()
wb = load_workbook("legacy_drawing.xlsm", keep_vba=True)
sheet1 = wb['Sheet1']
assert sheet1.legacy_drawing == 'xl/drawings/vmlDrawing1.vml'
sheet2 = wb['Sheet2']
assert sheet2.legacy_drawing == 'xl/drawings/vmlDrawing2.vml'
def delete_row(file,sheet_name,row_num,no_row_delete):
try:
workbook = openpyexcel.load_workbook(file)
sheet = workbook[sheet_name]
# sheet = workbook.get_sheet_by_name(sheet_name)
sheet.delete_rows(row_num,no_row_delete)
workbook.save(file)
except Exception as e:
print("In Delete_row method:",type(e).__name__)
def test_copy_worksheet(datadir, WorksheetCopy):
datadir.chdir()
wb = load_workbook('copy_test.xlsx')
ws1 = wb['original_sheet']
ws2 = wb.create_sheet('copy_sheet')
cp = WorksheetCopy(ws1, ws2)
cp.copy_worksheet()
for c1, c2 in zip(ws1['A'], ws2['a']):
assert compare_cells(c1, c2) is True
def readExcel(self, sheetname, rowno, colno):
try:
workbook = openpyexcel.load_workbook(
"D:/Python Projects/ExpressEfile/Data/ExpressEfileDataProvider.xlsx"
)
sheet = workbook.get_sheet_by_name(sheetname)
value = sheet.cell(row=rowno, column=colno).value
return str(value)
except Exception as ex:
print("Can not read excel data" + str(ex))
self.capture("Error" + str(random.random()))
def process_workbook(filename):
wb=excel.load_workbook(filename)
sheet=wb['Sheet 1']
for row in range(, sheet.max_row+1)
print(row)
sheet.cell(row,3)
corected_price=cell.value*.9
correct_price_cell=sheet.cell(row,4)
correct_price_cell.value=corrected_price
def getTestdata(test_case_name):
dict1 = {}
book = openpyexcel.load_workbook(
"D:\\Soni Data\\myfiles\\gacc\\New folder\\pyth\\vido\\pdfs\\datainexcel.xlsx"
)
sheet = book.active
for r in range(1, sheet.max_row + 1):
if sheet.cell(row=r, column=1).value == test_case_name:
for c in range(2, sheet.max_column + 1):
dict1[sheet.cell(row=1, column=c).value] = sheet.cell(
row=r, column=c).value
return [dict1]
def test_vba(self, datadir, Manifest):
datadir.chdir()
from openpyexcel import load_workbook
wb = load_workbook('sample.xlsm', keep_vba=True)
manifest = Manifest()
manifest._write_vba(wb)
partnames = set([t.PartName for t in manifest.Override])
expected = set([
'/xl/workbook.xml', '/xl/worksheets/sheet1.xml',
'/xl/worksheets/sheet2.xml', '/xl/worksheets/sheet3.xml',
'/xl/theme/theme1.xml', '/xl/styles.xml', '/docProps/core.xml',
'/docProps/app.xml', '/xl/sharedStrings.xml'
])
assert partnames == expected
def datgenrator():
wk = openpyexcel.load_workbook("D:/Python/dataTC2.xlsx")
sh= wk['Sheet1']
r = sh.max_row
li=[]
li1=[]
for i in range(1,r+1):
li1=[]
un =sh.cell(i,1)
pw = sh.cell(i,2)
li1.insert(0,un.value)
li1.insert(1,pw.value)
li.insert(i-1,li1)
#li= [['user1','*****@*****.**'],['user2','*****@*****.**'],['user3','*****@*****.**']]
print(li)
return li
def test_write_workbook(datadir, tmpdir):
datadir.chdir()
src = ZipFile("book1.xlsx")
orig_files = set(src.namelist())
src.close()
from openpyexcel import load_workbook
wb = load_workbook("book1.xlsx")
tmpdir.chdir()
wb.save("book1.xlsx")
src = ZipFile("book1.xlsx")
out_files = set(src.namelist())
src.close()
# remove files from archive that the other can't have
out_files.discard("xl/sharedStrings.xml")
orig_files.discard("xl/calcChain.xml")
assert orig_files == out_files
def process_workbook(filename):
wb = xl.load_workbook('filename')
sheet = wb['Sheet1']
cell = sheet['a1']
cell = sheet.cell(1, 1)
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
correct_price = cell.value * 0.9
correct_price_cell = sheet.cell(row, 4)
correct_price_cell.value = correct_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'e2')
wb.save(filename)
def test_merge_vba(ExcelWriter, archive, datadir):
from openpyexcel import load_workbook
datadir.chdir()
wb = load_workbook("vba+comments.xlsm", keep_vba=True)
writer = ExcelWriter(wb, archive)
writer._merge_vba()
assert set(archive.namelist()) == set([
'xl/vbaProject.bin',
'xl/drawings/vmlDrawing1.vml',
'xl/ctrlProps/ctrlProp3.xml',
'xl/ctrlProps/ctrlProp1.xml',
'xl/ctrlProps/ctrlProp10.xml',
'xl/ctrlProps/ctrlProp9.xml',
'xl/ctrlProps/ctrlProp4.xml',
'xl/ctrlProps/ctrlProp5.xml',
'xl/ctrlProps/ctrlProp6.xml',
'xl/ctrlProps/ctrlProp7.xml',
'xl/ctrlProps/ctrlProp8.xml',
'xl/ctrlProps/ctrlProp2.xml',
])
if (os.path.exists("ID.txt")):
print("Введите время задержки между запросами в секундах")
time_sleep = input()
while (time_sleep.isdigit() != True):
print("Введите время задержки между запросами в секундах")
time_sleep = input()
time_sleep = int(time_sleep)
if (time_sleep < 0): time_sleep = 0
ids = open("ID.txt", encoding='utf-8').readlines()
print("Всего объектов:" + str(len(ids)))
try:
wb = openpyexcel.load_workbook(filename='Итог.xlsx')
except:
wb = openpyexcel.Workbook()
try:
os.remove('Итог.csv')
except:
None
csv_ = open('Итог.csv', 'w', encoding='cp1251')
ws = wb.active
for row in ws:
for el in row:
el.value = ""
# < challenge2 >
# Stage4에서 완성한 네이버 뉴스기사 수집기를 통해 저장되는 엑셀양식에 "검색어"열을 추가합니다.
# 1. 파일의 헤더위치에 "검색어" 카테고리를 추가합니다.
# 2. "검색어" 카테고리에 입력한 키워드를 저장해줍니다.
# *Stage4에서 완성한 수집기 프로그램은 데이터가 누적되어 저장되므로 작동확인을 위해서 기존의 엑셀 파일을 삭제한 후 실행해주셔야합니다.
import requests
from bs4 import BeautifulSoup
import openpyexcel
keyword = input("검색어: ")
try: # 일단, week4-7 이란 파일 가져와보고, (없으면 except 실행)
wb = openpyexcel.load_workbook("navernews_v1.xlsx")
sheet = wb.active
print("=== 기존 파일을 불러옵니다 ===")
except: # 엑셀파일 존재하지 않는 경우 여기 실행
wb = openpyexcel.Workbook()
sheet = wb.active
sheet.append(["검색어", "제목", "언론사"]) # 제목|언론사 <--맨 첫 행에 추가
print("=== 새로운 파일이 생성되었습니다 ===")
for page in range(1,100,10):
raw = requests.get("https://search.naver.com/search.naver?where=news&sm=tab_jum&query="
+keyword+"&start="+str(page), headers={"User-Agent":"Mozila/5.0"})
html = BeautifulSoup(raw.text, "html.parser")
container = html.select("div.news ul.type01 > li")
for cont in container:
def getColCount(filepath, sheetname):
workbook = openpyexcel.load_workbook(filepath)
sheet = workbook[sheetname]
return sheet.max_column
import openpyexcel
wk = openpyexcel.load_workbook("D:/Python/dataTC2.xlsx")
sh = wk['Sheet1']
r = sh.max_row
li = []
li1 = []
for i in range(1, r + 1):
li1 = []
un = sh.cell(i, 1)
pw = sh.cell(i, 2)
li1.insert(0, un.value)
li1.insert(1, pw.value)
li.insert(i - 1, li1)
# li= [['user1','*****@*****.**'],['user2','*****@*****.**'],['user3','*****@*****.**']]
print(li)
def readData(filepath, sheetname, rowno, colno):
workbook = openpyexcel.load_workbook(filepath)
sheet = workbook[sheetname]
return sheet.cell(row=rowno, column=colno).value
def writeData(filepath, sheetname, rowno, colno, data):
print(data)
workbook = openpyexcel.load_workbook(filepath)
sheet = workbook[sheetname]
sheet.cell(row=rowno, column=colno).value = data
workbook.save(filepath)
def getRowcount (filepath, sheetname):
workbook = openpyexcel.load_workbook(filepath)
sheet = workbook[sheetname]
return (sheet.max_row)
import pptx
import openpyexcel as excel
import math
import pathlib
from pptx.util import Cm as cm
exl_book = excel.load_workbook('C:/Users/dingo/OneDrive/2020/20191230 박선우 전시/모델 아카이빙.xlsx')
exl_sheet = exl_book.worksheets[0]
exl_rows = list(exl_sheet.rows)
exl_cols = list(exl_sheet.columns)
exl_dict = {}
for i,col in enumerate(exl_cols):
exl_dict[col[1].value] = [c.value for c in col[2:]]
n = len(list(exl_dict.values())[0])
p = pptx.Presentation()
layout = p.slide_layouts[0]
a3_size = 42.0,29.7
p.slide_width = cm(a3_size[0])
p.slide_height = cm(a3_size[1])
tables = []
table = p.slides.new_slide(layout).shapes.new_table(6,3,cm(0),cm(0),width=cm(a3_size[0]/3), height=cm(a3_size[0]/3))
r1 = 0.5
r2 = 5
s = r1*5+r2
h1 = (r1/s)*(a3_size[1]/3)
h2 = (r2/s)*(a3_size[1]/3)
rows = list(table.table.rows)
def write_data(path, sheetname, rownum, colnum, data):
workbook = openpyexcel.load_workbook(path)
sheet = workbook[sheetname]
sheet.cell(row = rownum, column = colnum).value = data
workbook.save(path)
def get_col_count(path, sheetname):
workbook = openpyexcel.load_workbook(path)
sheet = workbook[sheetname]
return (sheet.max_column)
def read_data(path, sheetname, rownum, colnum):
workbook = openpyexcel.load_workbook(path)
sheet = workbook[sheetname]
return sheet.cell(row = rownum, column = colnum).value
def readData(file, sheetName, rownum, columnno):
workbook = openpyexcel.load_workbook(file)
sheet = workbook.get_sheet_by_name(sheetName)
return sheet.cell(row=rownum, column=columnno).value
def writeData(file, sheetName, rownum, columnno, data):
workbook = openpyexcel.load_workbook(file)
sheet = workbook.get_sheet_by_name(sheetName)
sheet.cell(row=rownum, column=columnno).value = data
workbook.save(file)
def getColumnCount(file, sheetName):
workbook = openpyexcel.load_workbook(file)
sheet = workbook.get_sheet_by_name(sheetName)
return (sheet.max_column)
import requests
from bs4 import BeautifulSoup
from openpyexcel import load_workbook
work_book = load_workbook('movie.xlsx')
work_sheet = work_book['movie']
print(work_sheet.cell(row=1, column=1).value)
work_sheet.cell(row=1, column=1, value='순위')
work_sheet.cell(row=1, column=2, value='영화제목')
work_sheet.cell(row=1, column=3, value='별점')
print(work_sheet.cell(row=1, column=1).value)
work_book.save('movie.xlsx')
def get_cell_data(rows, colns, filepath=excel_file_path):
workbook = openpyexcel.load_workbook(filepath)
sheet = workbook.active
print(sheet)
data = sheet.cell(rows, colns)
return data.value
def __init__(self, file1, sheet_name='Sheet1'):
self.file1 = file1
self.sheet_name = sheet_name
self.workbook = excel.load_workbook(file1)
self.sheet = self.workbook.active
