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