def new_excel_writer(self):
"""
:return: 返回一个写入Excel的对象
"""
wb = workbook()
ew = ExcelWriter(workbook=wb)
return ew
# get names of all sheets, no paras
sheets = workbook.get_sheet_names()
# print (sheets) will result in sheet1
# let's suppose we get a sheet named sheet1, let's work with it
cellOrColumnName = sheet1['firstColumnName']
# [] bracket in sheet gets call objects
# cell objects have value member variable
# with all the contents of that cell.
store = str(cellOrColumnName.value)
# cell() method retusn a Cell object from a sheet
# EDITING EXCEL SPREADSHEETS
wb = openpyxl.workbook()
# create a workbook object
wb.get_sheet_names()
# ['Sheet'] -> default name prolly.
# lets select this sheet and work with it
sheet = wb.get_sheet_by_name('Sheet')
sheet['A1'].value == None
# returns true
sheet['A1'] = 42
sheet['A2'] = 'Hello'
# save the sheet to some other directory.
This script is written to take a list of mail recipients, outputted from the
NationBuilder CRM system, and to prepare this list for efficient mailing by
postal mailing. The *main goal* of this is to prevent multiple pieces of mail
from being sent to the same address -- this is costly and inefficient. Instead,
names at one address are combined in a way that is consistent with natural
language, and letters are address appropriately to multiple recipients.
"""
import os
os.chdir("INSERT PATH")
import openpyxl
wb = openpyxl.load_workbook('INSERT FILE NAME')
wb2 = openpyxl.workbook()
sheetA = wb.get_sheet_by_name('Sheet')
sheetB = wb2.get_sheet_by_name('Sheet1')
print('Open worked')
households = {}
names = ""
def xstr(s):
if s is None:
return ''
return str(s)
def xint(i):
from openpyxl import workbook
wb = workbook()
ws = wb.active
# 셀 합치기
ws.merge_cells("B2:D2") #B2부터 D2까지 합치겠음
ws["B2"].value = "Merged Cell"
wb.save("sample_merge.xlsx")
print(assign_dict)
student_number_tracker = [0,0,0,0,0,0,0,0,0,0]
print(total_student)
for item in total_student:
group = item[2]
group_name = assign_dict[group]
total_student_by_group[group_name]["student_"+str(student_number_tracker[group-1]+1)] = item
student_number_tracker[group-1] += 1
print(total_student_by_group)
print(student_number_tracker)
my_writting_wb = workbook()
for i in range(10):
write_ws = my_writting_b.create_sheet("jo"+str(i+1))
for i in range(10):
load_ws = my_writting_wb["jo"+str(i+1)]
load_ws.append(["stu_num","name","group_id","git"])
templist = list(total_student_by_group["group"+str(i+1)].values())
print(templist)
for j in range(4):
try:
load_ws.append(templist[j])
except:
from openpyxl import workbook
import time
book = workbook()
sheet = book.active
sheet['A1'] = 56
sheet['A2'] = 43
now = time.strftime('%x')
sheet['A3'] = now
book.save('Ashu.xlsx')
for i in xrange(1):
vals = np.genfromtxt(fns[i], delimiter=',', skip_header=1)[:, 1]
r = csv.reader(open(fns[i]))
line1 = r.next()
line1 = line1[0] + line1[1]
hdrs = [int(s) for s in line1.split() if s.isdigit()]
wsd = wb.create_sheet(title=str(hdrs[1]) + ', ' + str(hdrs[0]))
for irow in xrange(int(vals.size)):
wsd.append([vals[irow]])
wb.save('file.xlsx')
from openpyxl.cell import get_column_letter
wb = xl.workbook()
dest_filename = r'empty_book.xlsx'
ws = wb.active
ws.title = "range names"
for col_idx in xrange(1, 40):
col = get_column_letter(col_idx)
for row in xrange(1, 600):
ws.cell('%s%s' % (col, row)).value = '%s%s' % (col, row)
ws = wb.create_sheet()
ws.title = 'Pi'
ws['F5'] = 3.14
wb.save(filename=dest_filename)
# -*- coding: utf-8 -*-
import openpyxl
from openpyxl.compat import range
from openpyxl.utils import get_column_letter
#接下来做一些工作的简便:
wb = openpyxl.workbook(path)#这个是用来管理工作蒲(workbook)的
ws = wb.active#用来访问对应工作蒲的工作表.
ws1 = wb.create_sheet('file_name')#这个用来创建一个新的工作表(wooksheet).
ws.title = 'New Title'#这个是用来改工作表的名称的.
print(wb.sheetnames)#以列表输出文件的工作表的名称.
for sheet in wb:
print(sheet.title)#这里对工作表做了循坏.
source = wb.active
target = wb.copy_worksheet(source)#这里是将一个工作表整体给复制了一个副本.
c = ws['A4']#c指的是单元格(cell).这里是访问工作表ws的A4单元格的内容.
d = ws.cell(row = 4,column = 2,value = 10)#这个函数可以提供对特定单元格的访问,
# 其中row(行)column(列)value(值)
for i in range(0,101):
for j in range(1,101):
ws.cell(row = i,column = j)#这样做的话会直接在内存里创造出100*100的单元格,并什么都没有做.
cell_range = ws['A1':'C2']#这里是对单元格进行批量访问,使用的是切片的方法.
colC = ws['C']
col_range = ws['C':'D']#这里类似的对行的范围进行了获取
row10 = ws['10']
row_range = ws['10':'14']#这里是对列;
for row in ws.iter_rows(min_row = 2,max_col = 5,max_row = 5):#这里对行和列都选取了范围,并对这个范围内的单元格进行循坏.
for cell in row:
print(cell)
vals = np.genfromtxt(fns[i], delimiter=",", skip_header=1)[:, 1]
r = csv.reader(open(fns[i]))
line1 = r.next()
line1 = line1[0] + line1[1]
hdrs = [int(s) for s in line1.split() if s.isdigit()]
wsd = wb.create_sheet(title=str(hdrs[1]) + ", " + str(hdrs[0]))
for irow in xrange(int(vals.size)):
wsd.append([vals[irow]])
wb.save("file.xlsx")
from openpyxl.cell import get_column_letter
wb = xl.workbook()
dest_filename = r"empty_book.xlsx"
ws = wb.active
ws.title = "range names"
for col_idx in xrange(1, 40):
col = get_column_letter(col_idx)
for row in xrange(1, 600):
ws.cell("%s%s" % (col, row)).value = "%s%s" % (col, row)
ws = wb.create_sheet()
ws.title = "Pi"
ws["F5"] = 3.14
wb.save(filename=dest_filename)
print(row)
# 利用for循环，按列读取的方式，读取.xlsx文件的所有内容，并在控制台输出
for j in range(ncols2):
col = sh1.col_values(j)
print(col)
# 利用for循环，按单元格读取的方式，读取.xlsx文件的所有内容，并在控制台输出
for i in range(nrows2):
for j in range(ncols2):
cell = sh1.cell(i, j).value
print(cell)
# 二、openpyxl练习题：
# 1：使用openpyxl创建一个新的.xlsx文件，把如下信息写入文件中，并保存为userinfo.xlsx：
name2Age = [['张飞', 38], ['赵云', 27], ['许褚', 36], ['典韦', 38], ['关羽', 39],
['黄忠', 49], ['徐晃', 43], ['马超', 23]]
#book = openpyxl.load_workbook("userinfo.xlsx")
book = openpyxl.workbook() #新建一个文件
sheet1 = book["Sheet1"]
#sheet1 = book.active
print(len(name2Age))
# for i in range(len(name2Age)):
# sheet1.append(name2Age[i])
for i in name2Age:
sheet1.append(i)
book.save("userinfo.xlsx")
# 2：用openpyxl读取.xlsx文件(文件自行准备)中的内容，要求如下：
# 利用for循环，按行读取的方式，读取.xlsx文件的所有内容，并在控制台输出，使用sh1.cell(1,2).value的方式读取
# for row in sheet1.iter_rows(max_row=len(name2Age)):
# for cell in row:
# print(cell.value, end="\t")
# print()
rows = sheet1.max_row
from openpyxl import load_workbook, workbook
wb = load_workbook('base.xlsx')
sheet = wb['Sheet1']
wx = workbook('base.xlse')
print(type(wx))
c = sheet.cell(row=1, column=1)
print(c.value)
c1 = sheet['B4'].value
print(sheet.max_column)
for row in range(1, sheet.max_row + 1):
for col in range(1, sheet.max_column + 1):
print(sheet.cell(row, column=col).value, end=" ")
print()