def species_names_to_excel(destination=None, force_update=False):
"""
Create a excel workbook with all the species name dumped in the first column of the first worksheet.
Calling this function will trigger an update of the species table from the herbie KMI web service if
table is empty or the force_update=True
:param destination: the destination path
:param force_update: if true update the species table from the herbie KMI web service
:return:
"""
# update of the species table
if force_update or Species.objects.count() == 0:
Species.objects.update_herbie_hbvspecies()
# export to excel
if destination is None:
destination = './local/species_names.xlsx'
dir_ = os.path.dirname(destination)
if not os.path.exists(dir_):
os.makedirs(dir_)
wb = Workbook(write_only=True)
ws = wb.create_sheet(title='Species')
species_names = [species.species_name for species in Species.objects.all()]
for name in species_names:
ws.append([name])
wb.save(destination)
return destination
def create_file(self, data, header, path_name):
path_name = path_name.split('.')
devices = [row for row in data.items()]
ctr = 1
for key,val in devices:
device_id = key
wb = Workbook()
del wb['Sheet']
for k,v in val.items():
sensor_code = k
row_data = v
ws = wb.create_sheet(sensor_code)
ws.append(header)
ws.title = sensor_code
for k1,v1 in row_data.iteritems():
ctr += 1
ws.append(v1)
print '{}. {}'.format(ctr,v1)
parsed_dir = 'parsed/%s' % (path_name[0])
if not os.path.exists(parsed_dir):
os.makedirs(parsed_dir)
wb.save('parsed/%s/%s.xlsx' % (path_name[0],device_id) )
def excel_diffexpression(parameters, excel_file):
"""
Create excel file with all contrasts
parameters
- parameters: analysis parameters dictionary
- excel_file: excel file name
"""
wb = Workbook(guess_types=True)
ws = wb.active
ws.title = "Dataset"
ws.cell(column=1, row=1,
value="http://www.ncbi.nlm.nih.gov/geo/query/acc.cgi?acc={}".format(parameters["gse"]))
methods = os.path.join(base_dir(), "R", "methods.txt")
excel_add_tsv(methods, "Methods", wb)
diffexp_files = glob.glob('DiffExpression/diffexp.*.txt')
for diffexp_file in diffexp_files:
print('.', end=' ')
contrast = os.path.basename(diffexp_file)
contrast = re.sub(r"diffexp.GSE\w*.", "", contrast)
contrast = re.sub(r".txt", "", contrast)
excel_add_tsv(diffexp_file, contrast, wb)
images_files = glob.glob('Figures/*.png')
ws = wb.create_sheet(title="Figures")
linenum = 1
for image_file in images_files:
print('.', end=' ')
img = Image(image_file)
ws.add_image(img, "A{}".format(linenum))
linenum += 28
wb.save(excel_file)
def saveROPXlsxFromCKCSV(ckcsvFile, xlsx_file, top_num):
# write it into xlsx file
firstColDict, spc_total_dict, spc_indiv_dict = getROPFromCKCSV(ckcsvFile)
for species_string in spc_indiv_dict:
spc_rxn_flux_sorted = sorted(spc_indiv_dict[species_string], \
key=lambda tup: -abs(max(tup[1].min(), tup[1].max(), key=abs)))
spc_indiv_dict[species_string] = spc_rxn_flux_sorted[:top_num]
wb = Workbook()
ws = wb.active
for i, header in enumerate(firstColDict):
ws.cell(row=1, column=i+1).value = header
for j, cellValue in enumerate(firstColDict[header]):
ws.cell(row=j+2, column=i+1).value = cellValue
column_idx = len(firstColDict)
for species_string in spc_total_dict:
ws.cell(row=1, column=column_idx+1).value = spc_total_dict[species_string][0]
for j, cellValue in enumerate(spc_total_dict[species_string][1]):
ws.cell(row=j+2, column=column_idx+1).value = cellValue
column_idx += 1
try:
for tup in spc_indiv_dict[species_string]:
ws.cell(row=1, column=column_idx+1).value = tup[0]
for j, cellValue in enumerate(tup[1]):
ws.cell(row=j+2, column=column_idx+1).value = cellValue
column_idx += 1
except KeyError:
print "{} does not exist in spc_indiv_dict!".format(species_string)
wb.save(xlsx_file)
def write_test():
wb = Workbook() # 1.creat a work book
wb_sheet0 = wb.create_sheet("student") # 2.create a sheet in the work book
datas_str = ""
datas_dict = {}
with open(path_read, "r") as f:
lines = f.readlines()
# process the data from txt file.I make it be a dict
for line in lines:
if len(line.strip()) > 1:
key, value = line.split(':')
key = key.strip().strip("\"")
value = value.strip().strip(",").strip("[").strip("]")
value_list = value.split(",")
# print(len(value_list))
datas_dict[key] = value_list
for i in range(1, 4): # 3.assign value to cell of the sheet
for j in range(1, 5):
cell = wb_sheet0.cell(row=i, column=j + 1)
cell.value = datas_dict[str(i)][j - 1]
# print(cell.value)
cell = wb_sheet0.cell(row=i, column=1)
cell.value = i
'''
for row in wb_sheet0.iter_rows():
for cell in row:
print(cell.value)
'''
wb.save(path_save) # 4.save the file
print('success')
def test_read_style_iter(tmpdir):
'''
Test if cell styles are read properly in iter mode.
'''
tmpdir.chdir()
from openpyxl import Workbook
from openpyxl.styles import Font
FONT_NAME = "Times New Roman"
FONT_SIZE = 15
ft = Font(name=FONT_NAME, size=FONT_SIZE)
wb = Workbook()
ws = wb.worksheets[0]
cell = ws.cell('A1')
cell.font = ft
xlsx_file = "read_only_styles.xlsx"
wb.save(xlsx_file)
wb_iter = load_workbook(xlsx_file, read_only=True)
ws_iter = wb_iter.worksheets[0]
cell = ws_iter['A1']
assert cell.font == ft
def loadCouchDB(db_name, view_name, excel_name):
tweet_dict = {}
couch_server = Server("http://115.146.94.12:5984")
couch_server.resource.credentials = ('admin', 'admin')
couch_server.config()
db = couch_server[db_name]
wb = Workbook()
ws = wb.active
ws.title = "range names"
rowid = 1
for row in db.view(view_name):
coordinate = re.sub(r"\[|\]", "", str(row.key))
# write coordinate
col = get_column_letter(1)
ws.cell('%s%s'%(col, rowid)).value = coordinate
#write polarity
col = get_column_letter(2)
ws.cell('%s%s'%(col, rowid)).value = getPolarity(row.value)
#write text
col = get_column_letter(3)
ws.cell('%s%s'%(col, rowid)).value = row.value
rowid += 1
ws = wb.create_sheet()
ws.title = 'coordinate'
wb.save(filename = excel_name)
return tweet_dict
def record(self, data):
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
for row in range(1, 40):
ws1.append([1,2,3,4,5])
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
_ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
print(ws3['AA10'].value)
wb.save(filename = dest_filename)
def getWordsCount(in_file,out_file):
# 读取excel文件
# lib = load_workbook(file, use_iterators = True)
# 创建新的excel文件
wt_wb = Workbook(write_only=True)
wt_ws = wt_wb.create_sheet()
word_all=''
fenci_list=[]
dis_list = []
words_sum=[]
lib = csv.reader(file(in_file, 'rb'))
# 文档分词
for row in lib:
fenci_list_p = list(pseg.cut(row[0]))
for w in fenci_list_p:
word = [w.word,w.flag]
# print word
words_sum.append(word)
#生成字典
for word in words_sum:
if word not in dis_list:
dis_list.append(word)
#计算词频
wt_ws.append(['words','notes','count'])
for word in dis_list:
count = words_sum.count(word)
word_c = [word[0],word[1],count]
wt_ws.append(word_c)
# 保存excel
wt_wb.save(out_file)
def test_clean_output(self):
test = Workbook().active
test.append(("# Items / # Man-hours / NPR", "Completion Date", "not_either"))
test.append(('1','6/16/90','etc'))
res = etl.clean_output(test)
self.assertTrue(isinstance(res.rows[1][0].value,int))
self.assertTrue(res.rows[1][1].value == '16/06/1990')
def test_add_vals_row(self):
ws = Workbook().active
ws.append(("head1", "head2", "head3"))
ws.append(("vals", "morevals", "allthevals"))
r = etl.add_vals(ws, ['new','header'], 'D1','r')
rh = etl.get_values(r.rows[0])
self.assertEqual(rh, ['head1', 'head2', 'head3', 'new','header'])
def store_xlsx(data,high,width):
accout=getpass.getuser()
excel=Workbook()
excel_writer=ExcelWriter(workbook = excel)
excel_outpath= r'/home/%s/mapdata/test1.xlsx'%accout
sheet = excel.create_sheet()
sheet=excel.worksheets[0]
sheet.title='testmap'
for row in range (1,(high+1)):
for col in range (1,(width+1)):
column = get_column_letter(col)
sheet.cell('%s%s'%(column,row)).value='%s'%data[row-1][col-1]
for col in range(1,width+1):
column = get_column_letter(col)
sheet.column_dimensions['%s'%column].width = 2.6
for row in range(1,high+1):
sheet.row_dimensions[row].height =14.15
sheet = excel.create_sheet()
sheet.title='parameters'
sheet.cell('A1').value='parameters'
sheet.cell('A2').value='This represents a 2-D grid map, in which each cell represents the probability of occupancy.'
sheet.cell('A7').value='Map width [cells]'
sheet.cell('A8').value='%s'%width
sheet.cell('C7').value='Map height [cells]'
sheet.cell('C8').value='%s'%high
excel.save(filename=excel_outpath)
print 'saving process done'
return 'ok'
def test_keep_dict(self):
"""row is new value, dict is old"""
db = Workbook().active
db.append(('Activity Status', 'Completion Date\n (Actual or Planned)', 'Start date \n(Actual or Planned)'))
db.append(('Completed', '12/15/2015','12/10/2015'))
d_v = ('Completed', '12/15/2015','12/12/2015')
self.assertTrue(etl.keep_dict(db.rows[1], d_v, db))
def getWordsCount(file,sheets_name,anlysis_row_nm,out_file):
# 读取excel文件
lib = load_workbook(file, use_iterators = True)
# 创建新的excel文件
wt_wb = Workbook(write_only=True)
wt_ws = wt_wb.create_sheet()
word_all=''
fenci_list=[]
dis_list = []
words_sum=[]
# 文档分词
for row in lib[sheets_name].iter_rows():
# print row[1].value
fenci_list = list(jieba.cut(row[anlysis_row_nm].value, cut_all=False))
fenci_list_p = list(pseg.cut(row[anlysis_row_nm].value))
for w in fenci_list_p:
word = [w.word,w.flag]
# print word
words_sum.append(word)
#生成字典
for word in words_sum:
if word not in dis_list:
dis_list.append(word)
#计算词频
wt_ws.append(['words','notes','count'])
for word in dis_list:
count = words_sum.count(word)
word_c = [word[0],word[1],count]
wt_ws.append(word_c)
# 保存excel
wt_wb.save(out_file)
def write_excel(joblist, filename):
"""
write Excel with Workbook
:param joblist:
:param filename:
:return:
"""
mkdirs_if_not_exists(EXCEL_DIR)
wb = Workbook()
ws = wb.active
ws.title = u"职位信息"
ws.cell(row=1, column=1).value = u'职位编码'
ws.cell(row=1, column=2).value = u'职位名称'
ws.cell(row=1, column=3).value = u'所在城市'
ws.cell(row=1, column=4).value = u'发布日期'
ws.cell(row=1, column=5).value = u'薪资待遇'
ws.cell(row=1, column=6).value = u'公司编码'
ws.cell(row=1, column=7).value = u'公司名称'
ws.cell(row=1, column=8).value = u'公司全称'
rownum = 2
for each_job in joblist:
ws.cell(row=rownum, column=1).value = each_job.positionId
ws.cell(row=rownum, column=2).value = each_job.positionName
ws.cell(row=rownum, column=3).value = each_job.city
ws.cell(row=rownum, column=4).value = each_job.createTime
ws.cell(row=rownum, column=5).value = each_job.salary
ws.cell(row=rownum, column=6).value = each_job.companyId
ws.cell(row=rownum, column=7).value = each_job.companyName
ws.cell(row=rownum, column=8).value = each_job.companyFullName
rownum += 1
wb.save(EXCEL_DIR + filename + '.xlsx')
logging.info('Excel生成成功!')
def xlsx_coordinates(input_file, sheet_name, address_column, output_file, ignore_first_row = True, api_key = None):
"""
Read the xlsx input_file, and add two new columns in the beginning of it with latitude and longitude
Input file must be a xls* file
"""
wb_read = load_workbook(input_file)
sh = wb_read.get_sheet_by_name(sheet_name)
if ignore_first_row:
#Ignore first line of the file
first = 1
else:
first = 0
out = []
for row in sh.rows[first:]:
coord = coordinates(row[address_column].value, api_key)
new_row = [coord['lat'],coord['lng']]
for c in row:
new_row.append(c.value)
out.append(new_row)
wb_write = Workbook(optimized_write=True)
sheet = wb_write.create_sheet(sheet_name,0)
for row in out:
sheet.append(row)
wb_write.save(output_file)
def save_sheet_workbook(filename, sheetname, headers, list_to_save, overwrite=False):
if os.path.isfile(filename):
wb = load_workbook(filename)
if overwrite and sheetname in wb:
wb.remove_sheet(wb.get_sheet_by_name(sheetname))
if sheetname in wb:
ws = wb.get_sheet_by_name(sheetname)
else:
ws = wb.create_sheet()
else:
wb = Workbook()
ws = wb.active
ws.title = sheetname
ws.append(headers)
for col in range(1, ws.max_column + 1):
cell = ws.cell(column=col, row=1)
cell.font = Font(bold=True)
for row in list_to_save:
ws.append(row)
# # Automatically adjust width of columns to its content
# # TODO add width adaptation, now it breaks on datetime
# dims = {}
# for row in ws.rows:
# for cell in row:
# if cell.value:
# dims[cell.column] = max((dims.get(cell.column, 0), len(str(cell.value))))
# for col, value in dims.items():
# ws.column_dimensions[col].width = value
wb.save(filename)
def render_to_response(self, context, **response_kwargs):
if self.render_format == 'xlsx':
from openpyxl import Workbook
from openpyxl.writer.excel import save_virtual_workbook
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'Inventaire'
# Headers
headers = ['Matériel (salle %s, armoire %s)' % (self.object.room.number, self.object.code), 'Quantité', 'Unité']
for col_idx, header in enumerate(headers, start=1):
ws.cell(row=1, column=col_idx).value = header
ws.cell(row=1, column=col_idx).style.font.bold = True
# Data
for row_idx, tr in enumerate(context['quant_items'], start=2):
ws.cell(row=row_idx, column=1).value = str(tr.material)
ws.cell(row=row_idx, column=2).value = tr.quantity
ws.cell(row=row_idx, column=3).value = tr.material.unit
ws.cell(row=row_idx+2, column=1).value = "État au %s" % date.today()
ws.cell(row=row_idx+2, column=1).style.font.italic = True
ws.column_dimensions['A'].width = 60
response = HttpResponse(save_virtual_workbook(wb), content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=exportation_%s_%s.xlsx' % (
self.object.code.replace(' ', '_'), date.strftime(date.today(), '%Y-%m-%d'))
return response
else:
return super(StorageView, self).render_to_response(context, **response_kwargs)
def get(self, request, *args, **kwargs):
productos = Producto.objects.filter(estado=True).order_by('codigo')
wb = Workbook()
ws = wb.active
ws['B1'] = 'REPORTE DE PRODUCTOS'
ws.merge_cells('B1:J1')
ws['B3'] = 'CODIGO'
ws['C3'] = 'DESCRIPCION'
ws['D3'] = 'DESCR_ABREV'
ws['E3'] = 'GRUPO'
ws['F3'] = 'UNIDAD'
ws['G3'] = 'MARCA'
ws['H3'] = 'MODELO'
ws['I3'] = 'PRECIO'
ws['J3'] = 'CREADO'
cont=4
for producto in productos:
ws.cell(row=cont,column=2).value = producto.codigo
ws.cell(row=cont,column=3).value = producto.descripcion
ws.cell(row=cont,column=4).value = producto.desc_abreviada
ws.cell(row=cont,column=5).value = producto.grupo_productos.descripcion
ws.cell(row=cont,column=6).value = producto.unidad_medida.descripcion
ws.cell(row=cont,column=7).value = producto.marca
ws.cell(row=cont,column=8).value = producto.modelo
ws.cell(row=cont,column=9).value = producto.precio
ws.cell(row=cont,column=9).number_format = '#.00000'
ws.cell(row=cont,column=10).value = producto.created
ws.cell(row=cont,column=10).number_format = 'dd/mm/yyyy hh:mm:ss'
cont = cont + 1
nombre_archivo ="ListadoProductos.xlsx"
response = HttpResponse(content_type="application/ms-excel")
contenido = "attachment; filename={0}".format(nombre_archivo)
response["Content-Disposition"] = contenido
wb.save(response)
return response
def consolidate(baseline_ret, wsl, src):
"""consolidate Distributions and Trainings"""
ret = Workbook()
ret.remove_sheet(ret.get_sheet_by_name('Sheet'))
ret.create_sheet(1,'Distributions')
ret.create_sheet(2,'Trainings')
template = get_template(src, True)
dist_template = template.get_sheet_by_name('Distributions')
train_template = template .get_sheet_by_name('Trainings')
print 'Getting metadata for Dist'
dist = ret.get_sheet_by_name('Distributions')
dist = copy_sheet_vals(dist,consolidate_specfic(baseline_ret, wsl, 'Distributions').get_sheet_by_name('Distributions'))
# dist = copy_sheet_styles(dist, dist_template)
dist = add_metadata(dist, src)
# dist = add_meta_colors(dist, dist_template)
print 'Getting metadata for Training'
train = ret.get_sheet_by_name('Trainings')
train = copy_sheet_vals(train, consolidate_specfic(baseline_ret, wsl, 'Trainings').get_sheet_by_name('Trainings'))
# train = copy_sheet_styles(train, train_template)
train = add_metadata(train, src)
# train = add_meta_colors(train, train_template)
return ret
def PlanilhaExport(col, tabela, nomeArq):
wb = Workbook()
ws = wb.active
try:
dados = eval(tabela + ".objects.all()")
except:
print("Tabela nao encontrada")
try:
colunas = col
letra = (
"A",
"B",
"C",
"D",
"E",
"F",
"G",
"H",
"I",
"J",
"K",
"L",
"M",
"N",
"O",
"P",
"Q",
"R",
"S",
"T",
"U",
"V",
"W",
"X",
"Y",
"Z",
)
qtdColunas = len(colunas) + 1
qtdLinhas = len(dados) + 1
for i in range(1, qtdColunas):
coluna = letra[i - 1] + str(1)
ws[coluna] = colunas[(i - 1)]
for j in range(1, qtdLinhas):
for e in range(1, qtdColunas):
coluna = letra[e - 1] + str(j + 1)
ws[coluna] = getattr(dados[j - 1], colunas[e - 1])
response = HttpResponse(content_type="application/vnd.ms-excel")
response["Content-Disposition"] = "attachment; filename=%s.xls" % nomeArq
wb.save(response)
return response
except:
print("Coluna nao encontrada")
def img2ExcelAscii(imgFile, excelFile, columns):
''' make a Excel ascii picture from image '''
try:
img = Image.open(imgFile)
except IOError:
print 'cannot open', imgFile
quit()
# make small image
orgWidth, orgHight = img.size
smlWidth = columns
smlHeight = smlWidth*orgHight/orgWidth
smlImg = img.resize((smlWidth, smlHeight), Image.ANTIALIAS)
# make excel file
wb = Workbook() # new excel, TODO: error check
ws1 = wb.active
ws1.title = excelFile
for y in range(0, smlHeight):
for x in range(0, smlWidth):
r, g, b = smlImg.getpixel((x, y))
c = "{0:0>2X}{1:0>2X}{2:0>2X}".format(r, g, b) # hex, ex)FF88CC
ws1.cell(row=y+1, column=x+1, value=" ").fill = \
PatternFill(fill_type='solid', start_color=c, end_color=c)
# n2A1 : column num to A1 style : 1->'A',2->'B'... 27->'AA',28->'AB'
def n2A1(n): return (chr(((n-1)//26)+64) if n > 26 else '') + chr((n-1) % 26+65)
for n in range(1, smlWidth+1):
ws1.column_dimensions[n2A1(n)].width = 2.5 # set column width, TODO: better way ???
wb.save(excelFile)
def translate_file(filename):
print("Converting " + filename)
wb = load_workbook(filename=filename, read_only=True)
toWb = Workbook(write_only=True)
sheet_names = wb.get_sheet_names()
for sheet in sheet_names:
count = 0
ws = wb[sheet]
max_rows = ws.get_highest_row()
toWs = toWb.create_sheet()
toWs.title = ws.title
for row in ws.rows:
newRow = []
for cell in row:
if isinstance(cell.value, str):
newRow.append(process_line(cell.value))
else:
newRow.append(cell.value)
toWs.append(newRow)
if count % 100 == 0:
# sys.stdout.write("\rProcessing " + sheet + ": row:" + str(count))
progress_bar(count,max_rows,filename + ":" + sheet)
count += 1
print("")
fp = os.path.splitext(filename)
fn = fp[0] + "_converted" + fp[1]
print("Saving file: " + fn)
toWb.save(fn)
def open_xls_as_xlsx(filename):
# first open using xlrd
ob = xlrd.open_workbook(filename, formatting_info=True)
nb = Workbook()
nb.remove_sheet(nb.worksheets[0])
index = 0
nsheets = ob.nsheets
while index < ob.nsheets:
os = ob.sheet_by_index(index)
nrows = os.nrows
ncols = os.ncols
# prepare a xlsx sheet
ns = nb.create_sheet(index)
ns.title = os.name
#print ns.title,os.name
for row in xrange(1, nrows+1):
for col in xrange(1, ncols+1):
nc = ns.cell(row=row, column=col)
oc = os.cell(row-1, col-1)
nc.value = oc.value
old_format = ob.format_map[ob.xf_list[oc.xf_index].format_key].format_str
nc.number_format = old_format
index += 1
#nb.save("c:\\users\\nikil\\Desktop\\sample1234.xlsx")
return nb
def write_tables_to_excel(tables, excel_output, fields=[]):
"""It gets a list of tables (a table being a list of lists) and a path for
excel file, and returns excel file with tables in separated sheets."""
# create new workbook
wb = Workbook()
# iterate through all tables
num_table = 0
for tabla in tables:
# create new sheet for each table
ws = wb.create_sheet()
# iterate rows
for i in xrange(len(tabla)):
# iterate columns
for j in xrange(len(tabla[i])):
# copy cell content in the spreadsheet
ws.cell(column=j + 1, row=i + 2).value = tabla[i][j]
# if fields are passed, copy to first row
if len(fields) > 0:
for k in xrange(len(fields[num_table])):
ws.cell(row=1, column=k + 1).value = fields[num_table][k]
# increment num_table
num_table += 1
wb.save(excel_output)
def write_excel_output2(excelfile, fieldnames, rows):
"""
This relies on xlsxwriter module
"""
workbook = Workbook(excelfile)
worksheet = workbook.add_worksheet()
# First add the header
header_col = 0
header_row = 0
for header in fieldnames:
worksheet.write(header_row, header_col, header)
header_col += 1
# Next write the actual data rows
header_row = 1
for row in rows:
header_col = 0
for field in fieldnames:
data = row.get(field, '')
worksheet.set_column(header_row, header_col, len(data))
worksheet.write(header_row, header_col, data)
header_col += 1
header_row += 1
workbook.close()
def main():
args = parser.parse_args()
soup = BeautifulSoup(args.filename.read(), 'html.parser')
filename = args.filename.name
report_title = get_report_title(soup)
report_host_fieldnames = get_host_fieldnames(soup)
report_host_rows = get_host_data(soup, report_host_fieldnames)
# Next get service summary info from the HTML file
report_service_fieldnames = get_service_fieldnames(soup)
report_service_rows = get_service_data(soup, report_service_fieldnames)
if args.csv_output:
# First write the host summary output
write_csv_output(args.csv_output, report_host_fieldnames,
report_host_rows)
write_csv_output(args.csv_output, report_service_fieldnames,
report_service_rows)
if args.excel_output:
wb = Workbook()
ws1 = wb.active
ws1.title = 'Host summary'
write_excel_output(ws1, report_host_fieldnames, report_host_rows)
ws2 = wb.create_sheet('Service summary')
write_excel_output(ws2, report_service_fieldnames, report_service_rows)
wb.save(args.excel_output)
def save_students(stu_list, filename):
wb = Workbook()
wb.create_sheet(0, "student")
ws = wb.active
for stu in stu_list:
ws.append(stu.into_row())
wb.save(filename)
def create_excel():
hosts = info.Server.objects.all()
print len(hosts)
# for i in Server.objects.all():
wb = Workbook()
ws = wb.active
hosts_info = [['计算机名', 'IP地址', '应用名', '应用角色', '操作系统', '硬盘容量', 'CPU核心数量', '内存', '服务类别']]
for host in hosts:
host_info = [host.hostname, host.ip, host.appname, host.appRole, host.OS, host.HardDisk, host.CPU, host.Mem, host.type]
hosts_info.append(host_info)
# print hosts_info
for row in hosts_info:
ws.append(row)
'''
chart = AreaChart()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "A10")
'''
filename = str(datetime.datetime.now()).split('.')[0].replace(' ', '_').replace(':', '-')
wb.save("d:/tmp/%s.xlsx" % filename)
def write_array_to_xlsx_using_xlwings(ar, file, sheet):
wb = Workbook(file)
Sheet(sheet).activate()
for i, j, val in iterate_over_array(ar):
Range(sheet, (i, j)).value = val
wb.save()
currentDate = time.strftime("%d_%m_%y")
if (os.path.exists('./reports.xlsx')):
wb = load_workbook(
filename="reports.xlsx") # as workbook contains multiple sheets
sheet = wb['IT2016']
#print(type(currentDate))
if sheet.cell(
row=1, column=3
).value != currentDate: #when u have already created the worksheet then we have to update the attendance
sheet['C1'].value = currentDate
#saving the file
wb.save(filename="reports.xlsx")
else: # here you are appending all the database to spreadsheet
wb = Workbook()
dest_filename = 'reports.xlsx'
c.execute("select * from students order by Roll ASC"
) # that will all rows sorted by roll number
#creating worksheet and giving names to column
ws1 = wb.active
ws1.title = "IT2016" # this would be the title of the worksheet
ws1.append(('Roll Number', 'Name', currentDate)) # name of the columns
#ws1.append(('','',''))
#entering students information from the database
while True:
row = c.fetchone()
if row == None:
break
else:
ws1.append((row[2], row[1])) #roll number #name
record[j] = None
for k in perc:
try:
my_str = record[k]
my_perc = float(my_str[:-1])
my_float = my_perc / 100
record[k] = my_float
except ValueError:
record[k] = None
result = []
result.append(input[0])
for record in input:
try:
if record[4] == "17031":
cleanse(record)
result.append(record)
except IndexError:
pass
from openpyxl import Workbook
data_rows = [fields for fields in result]
wb = Workbook()
ws = wb.active
ws.title = "Temperature data"
for row in data_rows:
ws.append(row)
wb.save("temp_data.xlsx")
max_row=sheet.max_row,
max_col=sheet.max_column):
if len(row) > 0:
usl = row[1].value # Чтение столбца
if usl is not None:
usl_data = [cell.value
for cell in row] # Непосредственное чтение строк
if usl not in data: # Создание массива данных по услуге
data[usl] = []
data[usl].append(usl_data)
wb.close()
# Создание отчетов по каждой услуге и сохранение в файл
for usl in data:
wb = Workbook()
ws = wb.active
ws.title = 'Услуги'
ws.append(shapka)
for row in data[usl]:
ws.append(row)
# Форматирование шапки
for i in range(1, 7):
zagl = ws.cell(row=1, column=i)
zagl.alignment = Alignment(horizontal='center', vertical='center')
zagl.fill = PatternFill(fill_type='solid',
start_color='5a61f0',
end_color='5a61f0')
zagl.font = Font(bold=True, italic=True, color='ffffff', size='16')
from openpyxl import Workbook
from openpyxl.chart import (
AreaChart,
Reference,
)
wb = Workbook()
ws = wb.active
rows = [
['Number', 'Batch 1', 'Batch 2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 10],
[6, 25, 5],
[7, 50, 10],
]
for row in rows:
ws.append(row)
chart = AreaChart()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
def douban_spider_ver2(keyword, file_name="douban_book.xlsx"):
"""
用Requests库请求目标API接口
注意反爬虫策略
:param keyword:
:param filename:
"""
target_url = "https://api.douban.com/v2/book/search?q={0}".format(keyword)
print("api url: %s " % target_url)
r = requests.get(target_url) # requests.models.Response
ebook_dict = r.json() # dict
count = ebook_dict["count"] # 本页书籍
total = ebook_dict["total"] # 总共书籍数
print("每页展示 %d 本书的信息" % count)
print("总计搜索到 %d 本书的信息" % total)
print("开始将相关书籍信息写入Excel")
print("Please wait (Maybe a long time...)")
# 创建一个excel工作区
wb = Workbook()
# 获取sheet名称
# print(wb.get_sheet_names())
# 激活当前工作簿 worksheet
ws = wb.active
ws.append(["书名", "作者", "描述", "出版社", "价格"])
# 从第2页开始，获取其他书籍信息
for start in range(1, int(total / count) + 1):
print("开始解析第{0}页数据".format(start))
url = "https://api.douban.com/v2/book/search?q=%s&start=%d" % (keyword,
start)
try:
response = requests.get(url)
sleep(5)
except KeyboardInterrupt:
print("没耐心了，那就不要爬了")
break
except Exception as e:
print("抛出异常，自己看堆栈报错信息:")
print(e)
break
ebook_dict = response.json() # dict
# 输出书籍信息
for book in ebook_dict["books"]:
ws.append([
book["title"], ",".join(book["author"]), book["summary"],
book["publisher"], book["price"]
])
# 保存为excel文件
wb.save(file_name)
sleep(1) # 暂时使用增加等待时间的方案，以防被封ip，最好要用动态UA和IP代理
print("完成写入第{0}页数据".format(start))
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type):
wb = Workbook()
# todo
ws = wb.active
ws.title = "MeterCarbonDioxideEmissions"
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Arial', size=15, bold=True)
title_font = Font(name='Arial', size=15, bold=True)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000')
)
b_border = Border(
bottom=Side(border_style='medium', color='00000000'),
)
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
ws.add_image(img, 'A1')
# Title
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'] = name
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'] = period_type
ws['B4'].alignment = b_r_alignment
ws['B4'] = 'Reporting Start Datetime:'
ws['C4'].border = b_border
ws['C4'].alignment = b_c_alignment
ws['C4'] = reporting_start_datetime_local
ws['D4'].alignment = b_r_alignment
ws['D4'] = 'Reporting End Datetime:'
ws['E4'].border = b_border
ws['E4'].alignment = b_c_alignment
ws['E4'] = reporting_end_datetime_local
if "reporting_period" not in report.keys() or \
"values" not in report['reporting_period'].keys() or len(report['reporting_period']['values']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
####################################################################################################################
has_carbon_data_flag = True
if "values" not in report['reporting_period'].keys() or len(report['reporting_period']['values']) == 0:
has_carbon_data_flag = False
if has_carbon_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + 'Reporting Period Carbon Dioxide Emissions'
reporting_period_data = report['reporting_period']
category = report['meter']['energy_category_name']
ca_len = len(category)
ws.row_dimensions[7].height = 60
ws['B7'].fill = table_fill
ws['B7'].border = f_border
ws['B8'].font = title_font
ws['B8'].alignment = c_c_alignment
ws['B8'] = 'Carbon Dioxide Emissions'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = 'Increment Rate'
ws['B9'].border = f_border
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = report['meter']['energy_category_name'] + " (" + report['meter']['unit_of_measure'] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['total_in_category'], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[col + '9'].border = f_border
# TCE TCO2E
end_col = col
# TCE
tce_col = chr(ord(end_col) + 1)
ws[tce_col + '7'].fill = table_fill
ws[tce_col + '7'].font = name_font
ws[tce_col + '7'].alignment = c_c_alignment
ws[tce_col + '7'] = 'Ton of Standard Coal (TCE)'
ws[tce_col + '7'].border = f_border
ws[tce_col + '8'].font = name_font
ws[tce_col + '8'].alignment = c_c_alignment
ws[tce_col + '8'] = round(reporting_period_data['total_in_kgce'] / 1000, 2)
ws[tce_col + '8'].border = f_border
ws[tce_col + '9'].font = name_font
ws[tce_col + '9'].alignment = c_c_alignment
ws[tce_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[tce_col + '9'].border = f_border
# TCO2E
tco2e_col = chr(ord(end_col) + 2)
ws[tco2e_col + '7'].fill = table_fill
ws[tco2e_col + '7'].font = name_font
ws[tco2e_col + '7'].alignment = c_c_alignment
ws[tco2e_col + '7'] = 'Ton of Carbon Dioxide Emissions (TCO2E)'
ws[tco2e_col + '7'].border = f_border
ws[tco2e_col + '8'].font = name_font
ws[tco2e_col + '8'].alignment = c_c_alignment
ws[tco2e_col + '8'] = round(reporting_period_data['total_in_kgco2e'] / 1000, 2)
ws[tco2e_col + '8'].border = f_border
ws[tco2e_col + '9'].font = name_font
ws[tco2e_col + '9'].alignment = c_c_alignment
ws[tco2e_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[tco2e_col + '9'].border = f_border
else:
for i in range(6, 9 + 1):
ws.rows_dimensions[i].height = 0.1
####################################################################################################################
has_carbon_detail_flag = True
reporting_period_data = report['reporting_period']
category = report['meter']['energy_category_name']
ca_len = len(category)
times = reporting_period_data['timestamps']
parameters_names_len = len(report['parameters']['names'])
parameters_data = report['parameters']
parameters_parameters_datas_len = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
parameters_parameters_datas_len += 1
if "values" not in reporting_period_data.keys() or len(reporting_period_data['values']) == 0:
has_carbon_detail_flag = False
if has_carbon_detail_flag:
start_detail_data_row_number = 13 + (parameters_parameters_datas_len + ca_len) * 6
ws['B11'].font = title_font
ws['B11'] = name + 'Detailed Data'
ws.row_dimensions[start_detail_data_row_number].height = 60
ws['B' + str(start_detail_data_row_number)].fill = table_fill
ws['B' + str(start_detail_data_row_number)].font = title_font
ws['B' + str(start_detail_data_row_number)].border = f_border
ws['B' + str(start_detail_data_row_number)].alignment = c_c_alignment
ws['B' + str(start_detail_data_row_number)] = 'Datetime'
time = times
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = start_detail_data_row_number + len(time)
if has_data:
end_data_row_number = start_detail_data_row_number
for i in range(0, len(time)):
col = 'B'
end_data_row_number += 1
row = str(end_data_row_number)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = time[i]
ws[col + row].border = f_border
ws['B' + str(end_data_row_number + 1)].font = title_font
ws['B' + str(end_data_row_number + 1)].alignment = c_c_alignment
ws['B' + str(end_data_row_number + 1)] = 'Total'
ws['B' + str(end_data_row_number + 1)].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(start_detail_data_row_number)].fill = table_fill
ws[col + str(start_detail_data_row_number)].font = title_font
ws[col + str(start_detail_data_row_number)].alignment = c_c_alignment
ws[col + str(start_detail_data_row_number)] = \
report['meter']['energy_category_name']+" (" + report['meter']['unit_of_measure'] + ")"
ws[col + str(start_detail_data_row_number)].border = f_border
time = times
time_len = len(time)
for j in range(0, time_len):
row = str(start_detail_data_row_number + 1 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['values'][j], 2)
ws[col + row].border = f_border
ws[col + str(end_data_row_number + 1)].font = title_font
ws[col + str(end_data_row_number + 1)].alignment = c_c_alignment
ws[col + str(end_data_row_number + 1)] = round(reporting_period_data['total_in_category'], 2)
ws[col + str(end_data_row_number + 1)].border = f_border
line = LineChart()
line.title = 'Reporting Period Carbon Dioxide Emissions - ' + report['meter']['energy_category_name'] + \
" (" + report['meter']['unit_of_measure'] + ")"
line_data = Reference(ws, min_col=3, min_row=start_detail_data_row_number, max_row=max_row)
line.series.append(Series(line_data, title_from_data=True))
labels = Reference(ws, min_col=2, min_row=start_detail_data_row_number + 1, max_row=max_row)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.height = 8.25
line.width = 24
ws.add_chart(line, "B12")
else:
for i in range(11, 43 + 1):
ws.row_dimensions[i].height = 0.0
####################################################################################################################
has_parameters_names_and_timestamps_and_values_data = True
# 12 is the starting line number of the last line chart in the report period
time_len = len(reporting_period_data['timestamps'])
current_sheet_parameters_row_number = 12 + ca_len * 6
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
################################################################################################################
# new worksheet
################################################################################################################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
file_name = (re.sub(r'[^A-Z]', '', ws.title))+'_'
parameters_ws = wb.create_sheet(file_name + 'Parameters')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12 + parameters_names_len * 3):
parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
parameters_ws.add_image(img, 'A1')
# Title
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'] = name
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'] = period_type
parameters_ws['B4'].alignment = b_r_alignment
parameters_ws['B4'] = 'Reporting Start Datetime:'
parameters_ws['C4'].border = b_border
parameters_ws['C4'].alignment = b_c_alignment
parameters_ws['C4'] = reporting_start_datetime_local
parameters_ws['D4'].alignment = b_r_alignment
parameters_ws['D4'] = 'Reporting End Datetime:'
parameters_ws['E4'].border = b_border
parameters_ws['E4'].alignment = b_c_alignment
parameters_ws['E4'] = reporting_end_datetime_local
parameters_ws_current_row_number = 6
parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' ' + 'Parameters'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 2
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(table_current_row_number)].border = f_border
parameters_ws[col + str(table_current_row_number)].font = title_font
parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(table_current_row_number)].border = f_border
parameters_ws[col + str(table_current_row_number)].font = title_font
parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = table_current_col_number + 3
################################################################################################################
# parameters chart and parameters table
################################################################################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' ' + 'Parameters'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = 'Parameters - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number))
line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
"""
테이블 형태로 만들어 준다.
"""
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
wb = Workbook()
ws = wb.active
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
]
# add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
ws.append(row)
tab = Table(displayName="Table1", ref="A1:E5")
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
def tocsv(self, datalines, filename, key='Mysheet'):
print('[+] Export to %s...' % (filename))
if os.path.isfile(filename):
book = load_workbook(filename=filename)
book.remove(book[key])
else:
book = Workbook()
book.remove(book.active)
if key not in book.sheetnames:
ws = book.create_sheet(key)
else:
ws = book[key]
# ws = book.get_sheet_by_name(key)
title_table = {
'id': u'序号',
'target': u'目标',
'host': u'主机',
'port': u'端口',
'status': u'状态',
'title': u'标题',
'header': u'返回信息',
'service': u'服务',
'is_self': u'是否为自有地址',
'is_smp': u'若为自有地址，网站是否已添加至SMP',
'domain': u'IP对应的域名',
'is_maintenance': u'是否为维护类网站',
'is_pro': u'若为专业公司地址，请明确归属网站的处置情况',
'backup_time': u'网站备案时间',
'business': u'网站归属业务',
'department': u'网站归属部门',
'contacts': u'统一接口人',
'contacts_way': u'联系方式',
}
titleList = [
'id', 'target', 'host', 'port', 'service', 'status', 'title',
'header', 'is_self', 'is_smp'
]
titleList += [
_key for _key in title_table.keys() if _key not in titleList
]
i = 0
for key in titleList:
if key in title_table.keys():
_key = title_table[key]
else:
_key = key
i += 1
ws.cell(row=1, column=i).value = _key
i = 1
for line in datalines:
i = i + 1
for key in line.keys():
try:
if line[key] == None or line[key] == '':
ws.cell(row=i,
column=titleList.index(key) + 1).value = ""
else:
ws.cell(row=i, column=titleList.index(key) +
1).value = str(line[key])
except UnicodeEncodeError as e:
ws.cell(row=i,
column=titleList.index(key) + 1).value = u"导出对象错误"
book.save(filename)
print('{+] Exported to %s successful!' % (filename))
min-count：设置最低频率，默认是5，如果一个词语在文档中出现的次数小于5，那么就会丢弃；
workers：是训练的进程数（需要更精准的解释，请指正），默认是当前运行机器的处理器核数。这些参数先记住就可以了。
sg ({0, 1}, optional) – 模型的训练算法: 1: skip-gram; 0: CBOW
alpha (float, optional) – 初始学习率
iter (int, optional) – 迭代次数，默认为5
'''
if __name__ == '__main__':
input1 = "./Data/task2_train_reformat_cleaned.xlsx"
output1 = "./Data/word2vec.model"
output2 = "./Data/vector.txt"
wb = load_workbook(input1)
ws = wb['sheet1']
max_row = ws.max_row
wb1 = Workbook()
sentences = []
for i in range(max_row - 1):
line = i + 2
text = ws.cell(line, 1).value
# with open(input1, 'r', encoding='utf8', errors='ignore') as f:
# for line in f:
# if " " in line:
sentences.append(list(text))
model = Word2Vec(size=300, window=5, min_count=5,
workers=4) # 定义word2vec 对象
model.build_vocab(sentences) # 建立初始训练集的词典
model.train(sentences,
total_examples=model.corpus_count,
def __init__(self):
self.wb = Workbook() # 类实例化
self.ws = self.wb.active # 激活工作表
self.ws.append(['书名', '链接']) # 添加表头
def main():
# Get project directly
PROJECT_ROOT = os.path.abspath(os.path.dirname(__file__))
# Build directory
filedir = os.path.join(PROJECT_ROOT, 'OEC2021_-_School_Record_Book_.xlsx')
while True:
file = input(
'Enter a file (with directory) to import. Leave blank to use default or \'e\' to exit: '
)
if file == 'e':
return
elif not file:
break
elif path.exists(
file) and file.find('.xlsx') != -1 and file.find('\\') != -1:
directory = file
break
else:
print("File does not exist!")
# Empty lists to store students, teachers, and teacher's assistants
students = list()
teachers = list()
tas = list()
# Get workbook from directory
wb = load_workbook(read_only=True, filename=filedir)
# Get the student info sheet from the workbook
student_info = wb[wb.sheetnames[0]]
# Iterate through each row in the student workbook
for student in student_info.iter_rows():
# If the row is empty
if not any(student):
# Break from loop
break
# Check for valid student number
if isinstance(student[0].value, float) and student[0].value > 0:
# Get classes for the student ordered by period 1-4
classes = [
student[4].value, student[5].value, student[6].value,
student[7].value
]
# Check for extracurriculars
if student[9].value != 'N/A' and student[9].value is not None:
# Get first extracurriculars from the comma delimited list.
extra_c = student[9].value.split(',')[0]
else:
# If no extracurriculars then set value to None
extra_c = None
# Check for health problems and if not none and NA then set value to true
health_problem = student[
8].value is not None and student[8].value != 'N/A'
# Append student object to students list
students.append(
Student(int(student[0].value),
student[2].value, student[1].value,
int(student[3].value), health_problem, classes,
extra_c))
# Get teacher sheet
teacher_info = wb[wb.sheetnames[1]]
# Iterate through each row in the teacher sheet
for teacher in teacher_info.iter_rows():
if not any(teacher):
# If the row is empty (aka eof reached) then break
break
# Check for valid teacher number
if isinstance(teacher[0].value, float) and teacher[0].value > 0:
# Append new teacher object to teachers
teachers.append(
Teacher(int(teacher[0].value), teacher[2].value,
teacher[1].value, teacher[3].value))
# Get the teacher assistant sheet
ta_info = wb[wb.sheetnames[2]]
# Iterate through the rows in the TA sheet
for index, ta in enumerate(ta_info.iter_rows()):
# If the row is empty (aka eof reached) then break
if not any(ta):
break
if index == 0:
continue
# Get classes for the TA ordered by period 1-4
classes = [ta[2].value, ta[3].value, ta[4].value, ta[5].value]
# Append new TA to the TA list
tas.append(TeachingAssistant(ta[1].value, ta[0].value, classes))
# Get infected people sheet
infected_info = wb[wb.sheetnames[3]]
# Iterate through all infected people
for infected in infected_info.iter_rows():
# If row is empty (aka eof) then break
if not any(infected):
break
# Check for ID. If ID exists then it is a student
if isinstance(infected[0].value, float) and infected[0].value > 0:
# Get student from ID (index = student# -1)
student = students[int(infected[0].value) - 1]
# Validate that the name matches the ID
if student.first == infected[2].value and student.last == infected[
1].value:
student.chanceOfDisease = 1
else:
# Raise exception if they do not match
raise Exception(
'Name does not match existing student with specified ID')
#If Student # is N/A then linearly search TA List and set infected accordingly.
elif infected[0].value == 'N/A':
for ta in tas:
#Match TA from firstname / lastname
if ta.first == infected[2].value and ta.last == infected[
1].value:
ta.chanceInfected = 1
break
wb.close()
directory = Directory(teachers, students, tas)
directory.reducePeriod(1)
directory.reducePeriod(2)
directory.reducePeriod('Lunch')
directory.reducePeriod(3)
directory.reducePeriod(4)
directory.reducePeriod('Extra')
while True:
name = input(
'Type a name or \'e\' to exit. Type \'f\' to output results to excel: '
)
person = None
if name == 'e':
break
elif name == 'f':
newfile = Workbook()
ws1 = newfile.create_sheet('Students', 0)
ws1['A1'] = 'Student Number'
ws1['B1'] = 'Last Name'
ws1['C1'] = 'First Name'
ws1['D1'] = 'Infectivity'
stud_count = 2
for student in students:
ws1[f'A{stud_count}'] = student.id
ws1[f'B{stud_count}'] = student.last
ws1[f'C{stud_count}'] = student.first
ws1[f'D{stud_count}'] = student.chanceOfDisease
stud_count += 1
ws2 = newfile.create_sheet('Teachers', 1)
ws2['A1'] = 'Teacher Number'
ws2['B1'] = 'Last Name'
ws2['C1'] = 'First Name'
ws2['D1'] = 'Infectivity'
teach_count = 2
for teacher in teachers:
ws2[f'A{teach_count}'] = teacher.id
ws2[f'B{teach_count}'] = teacher.last
ws2[f'C{teach_count}'] = teacher.first
ws2[f'D{teach_count}'] = teacher.chanceInfected
teach_count += 1
assist_count = 2
ws3 = newfile.create_sheet('Teaching Assistants', 2)
ws3['A1'] = 'Last Name'
ws3['B1'] = 'First Name'
ws3['C1'] = 'Infectivity'
for ta in tas:
ws3[f'A{assist_count}'] = ta.last
ws3[f'B{assist_count}'] = ta.first
ws3[f'C{assist_count}'] = ta.chanceInfected
assist_count += 1
basedir = filedir[0:filedir.rindex('\\')]
filename = f'Result{random.randint(0,100000)}.xlsx'
newfile.save(os.path.join(basedir, filename))
else:
for student in students:
if f'{student.first} {student.last}' == name:
person = student
print(
f'{name} has a chance of infection of {person.chanceOfDisease}'
)
break
for teacher in teachers:
if f'{teacher.first} {teacher.last}' == name:
person = teacher
print(
f'{name} has a chance of infection of {person.chanceInfected}'
)
break
for ta in tas:
if f'{ta.first} {ta.last}' == name:
person = ta
print(
f'{name} has a chance of infection of {person.chanceInfected}'
)
break
if not person:
print(f'Sorry {name} was not found')
def index(request):
global search_count
if request.method == "POST":
unit = request.POST.get("unit")
str_date_From = request.POST.get("date_From")
str_date_To = request.POST.get("date_To")
tmp_date_From = datetime.strptime(str_date_From, '%d.%m.%Y')
tmp_date_To = datetime.strptime(str_date_To, '%d.%m.%Y')
if (os.name == "nt"):
date_From = tmp_date_From.strftime('%Y-%m-%d')
date_To = tmp_date_To.strftime('%Y-%m-%d')
else:
date_From = tmp_date_From.strftime('%Y-%-m-%-d')
date_To = tmp_date_To.strftime('%Y-%-m-%-d')
# age = request.POST.get("age") # получение значения поля age
#return HttpResponse("<h2>Hello, {0}</h2>".format(name))
#return HttpResponse("<h2>date_From, {0}</h2>".format(date_From))
#return HttpResponse(getContent(unit,date_From, date_To))
userform = UserForm({
'unit': unit,
'date_From': str_date_From,
'date_To': str_date_To
})
table, arr = getContent(unit, date_From, date_To)
if 'SaveData' in request.POST:
#print(arr)
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
)
response[
'Content-Disposition'] = 'attachment; filename={date}-movies.xlsx'.format(
date=datetime.now().strftime('%Y-%m-%d'), )
workbook = Workbook()
# Get active worksheet/tab
worksheet = workbook.active
worksheet.title = 'Movies'
columns = [
'ID',
'Тип документа',
'Содержание',
'Период',
'Авторы',
'Дата документа',
'Архив',
'Фонд',
'Опист',
'Дело',
'Документ',
]
row_num = 1
# Assign the titles for each cell of the header
for col_num, column_title in enumerate(columns, 1):
cell = worksheet.cell(row=row_num, column=col_num)
cell.value = column_title
for rec in arr:
row_num += 1
row = [
rec.get('cnt'),
rec.get('document_type'),
rec.get('document_name'),
rec.get('period'),
rec.get('authors'),
rec.get('document_date_f'),
rec.get('archive'),
rec.get('fond'),
rec.get('opis'),
rec.get('delo'),
rec.get('link'),
]
for col_num, cell_value in enumerate(row, 1):
cell = worksheet.cell(row=row_num, column=col_num)
cell.value = cell_value
workbook.save(response)
return response
#return
else:
return render(
request, "search/index.html", {
"form": userform,
'search_count': 'Найдено: ' + str(search_count),
"table_content": table
})
else:
userform = UserForm()
return render(request, "search/index.html", {"form": userform})
# go through each cell and create the addresses in a list
for row in range(1, sheet.max_row + 1):
# Concat each line of the address with a tab
# Basing each addressee off of "TO THE PARENTS OF:"
if str(sheet['A' + str(row)].value).strip() == 'TO THE PARENTS OF:':
addresses += 1
data.append(str(sheet['A' + str(row)].value).strip())
else:
data[addresses - 1] += "\t"
data[addresses - 1] += str(sheet['A' + str(row)].value).strip()
tabCount = data[addresses - 1].count("\t")
# maxColumns = tabCount if tabCount > maxColumns else maxColumns
# Create a new workbook to enter data into
newName = ' '.join(args) + ' Revised.xlsx'
newBook = Workbook()
ws = newBook.active
ws.title = 'Converted From Labels'
ws["A1"] = "Parents of"
ws["B1"] = "Name"
ws["C1"] = "Address 1"
ws["D1"] = "Address 2"
ws["E1"] = "CSZ"
# Initialize and loop through data adding it to workbook sheet
x = 0
addressee = []
blanks = ['', '', '', '', '']
a, b, c, d, e = blanks
setattr(instrument, driver, pod)
pprint(instrument)
if instrument.SpectrumAnalyser[0].query(':INSTrument:SELect?') != 'SA':
instrument.SpectrumAnalyser[0].write(':INSTrument:SELect SA')
time.sleep(3) # Loading delay?
instrument.SpectrumAnalyser[0].write(':SENSe:ROSCillator:OUTPUT:STATe ON')
instrument.SpectrumAnalyser[0].write(':CALibration:AUTO OFF')
instrument.SpectrumAnalyser[0].frequencyspan = 1e3
instrument.SpectrumAnalyser[0].resolutionbandwidth = 1e3
instrument.SignalGenerator[0].amplimit = 10
rfpath = input('RF Path (Ref, UUT) : ')
wb = Workbook()
ws = wb.active
# ws.title = input("Sheetname --> ")
ws.title = rfpath
ws.append(["Frequency", "Mean dBm", "list dBm"])
instrument.SignalGenerator[0].amplimit = 0
instrument.SignalGenerator[0].amplitude = 0
instrument.SignalGenerator[0].output = True
EstimatedTime = ETC(
(18e9 - 1e9) / 100e6) # CALCulate number of steps in test
try:
for frequency in np.arange(
1e9, 18e9 + 1,
100e6): # arange is upto but not including max value, thus + 1
print(frequency)
def __init__(self):
self._styles = {}
self.column_dimensions = DimensionHolder(self)
self.parent = Workbook()
def generator(group, student, task_code):
# Генерация случайных (n, k) так, что k < n
while True:
n = lc.randint(min_n, max_n)
k = lc.randint(min_k, max_k)
if k < n and (n - k) >= min_r:
break
r = n - k
# print(f'Введите нижнюю границу кодового расстояния (n, k)-кода ({n}, {k})')
d_recomend = lc.get_recomend_code_distance(n, k)
# print(f'Рекомендуется не более {d_recomend}')
#try:
# d_low_bound = int(input())
#except:
# d_low_bound = 2
print(f'Подождите идет подбор порождающей матрицы G с кодовым \
расстоянием не ниже {d_recomend}...')
G, _ = lc.gen_matrix(n, k, d_recomend)
H = lc.get_check_matrix(G)
d = lc.get_code_distance(H, True)
Gsh, *_ = lc.shuffle_matrix(G, n, True, [])
print('Порождающая матрица G в систематической форме')
pp(G)
print('Матрица G после тасовки')
pp(Gsh)
a = lc.get_rand_bits(k)
s = lc.mult_v(a, Gsh)
print('Кодовое расстояние dк')
pp(d)
print('Выбранный кодовый вектор s')
pp(s)
qi = (d - 1) // 2 # Целевая кратность ошибки - кратность исправления
p = 1. * qi / n # Средняя кратность случайной величины q = np
e = lc.get_error_vector(n, p)
q = lc.hamming_weight(e) # Получившаяся кратность ошибки
print('Выпавший вектор ошибки e')
pp(e)
print('Кратность ошибки q')
pp(q)
v = lc.xor(s, e)
wb = Workbook()
ws = wb.active
ws.title = 'Main'
hf = Font(name='Calibri', bold=True)
ws['A1'].font = hf
ws['A1'] = f'Порождающая матрица G (n, k)-кода ({n}, {k})'
for g_r in Gsh:
ws.append(g_r)
ws.append(['Принятый кодовый вектор v'])
ws.append(v)
wsC = wb.create_sheet('Check')
wsC.append(['Введите ответы:'])
wsC.cell(row=wsC.max_row, column=1).font = hf
wsC.append(['Проверочная матрица H:'])
for _ in range(r):
wsC.append(lc.get_rand_bits(n))
wsC.append(['Кодовое расстояние кода dк:'])
wsC.append([0])
wsV = wb.create_sheet('CodeVector')
wsV.append(['Введите ответы:'])
wsV.cell(row=wsV.max_row, column=1).font = hf
wsV.append(['Декодированный кодовый вектор s:'])
wsV.append(lc.get_rand_bits(n))
wsV.append(['Информационный вектор a:'])
wsV.append(lc.get_rand_bits(k))
wb.save(f'{student}_{task_code}_{group}.xlsx')
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from openpyxl import Workbook
book = Workbook()
sheet = book.active
sheet['A1'] = 1
sheet.cell(row=2, column=2).value = 2
book.save('write2cell.xlsx')
from openpyxl import Workbook
from openpyxl import load_workbook
wb = Workbook()
wb = load_workbook(filename='rawData.xlsx')
ws = wb.active
for i in range(1, 60):
for j in range(1, 60):
d = ws.cell(row=i, column=j).value
if type(d) is str:
if "plusORMinus" in d:
ind = d.find("p")
ws.cell(row=i, column=j).value = d[:ind - 1]
wb.save(filename="fixed.xlsx")
def __init__(self):
self.wb = Workbook()
self.ws = self.wb.active
self.ws.append(['时间', '来源', '标题', '内容', '编辑', '链接'])
def worksheet():
from openpyxl import Workbook
wb = Workbook()
return wb.active
from openpyxl import Workbook
wb = Workbook()
# wb.active
ws = wb.create_sheet() # 새로운 Sheet 기본 이름으로 생성
ws.title = "MySheet" # Sheet 이름 변경
ws.sheet_properties.tabColor = "ff66ff" # RGB 형태로 값을 넣어주면 탭 색상 변경
# Sheet, MySheet, YourSheet
ws1 = wb.create_sheet("YourSheet") # 주어진 이름으로 Sheet 생성
ws2 = wb.create_sheet("NewSheet", 2) # 2번째 index에 Sheet 생성
new_ws = wb["NewSheet"] # Dict 형태로 sheet에 접근
print(wb.sheetnames) # 모든 Sheet 이름 확인
# Sheet 복사
new_ws["A1"] = "Test"
target = wb.copy_worksheet(new_ws)
target.title = "Copied Sheet"
wb.save("sample.xlsx")
# -*- coding: utf-8 -*- # @Time : 2018/4/18 8:57 # @Author : zgh # @Email : [email protected] # @File : class_excel_rw.py # @Software: PyCharm #引入的模块 from openpyxl import load_workbook #专门用来打开已经存在的Excel from openpyxl import Workbook #专门新建的Excel #对Excel的操作，总的来说有2种，一种是excel存在，一种是excel不存在要新建 #新建Excel的过程 workbook = Workbook() workbook.create_sheet('python5') workbook.save('python_3.xlsx') #读写的操作 可以同时进行 #打开Excel workbook_rw = load_workbook("python5.xlsx") sheet_rw = workbook_rw.get_sheet_by_name('python5') #读操作 read_result = sheet_rw.cell(row=4, column=2).value print(read_result) #写操作===保存 sheet_rw.cell(row=4, column=2).value = '小小豆' workbook_rw.save("prthon5.xlsx")
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws['A1'] = 42
ws.append([1, 2, 3])
import datetime
ws['A2'] = datetime.datetime.now()
wb.save("sample.xlsx")
wb.close()
from openpyxl import load_workbook
wb = load_workbook(filename="sample.xlsx")
for sheet in wb:
print(sheet)
sheet = wb['Sheet']
print(sheet['A1'].value)
def create_workbook(title):
wb = Workbook()
ws = wb.active
ws.title = title
return [wb, ws]
from bs4 import BeautifulSoup
import requests
from openpyxl import Workbook
import time
from selenium import webdriver
excel_name = "checkweb.xlsx"
wb = Workbook()
ws1 = wb.active
ws1.title = '权力清单'
def get_data(url, post):
"""
根据请求地址获取数据
:param url:
:return:
"""
header = {
'User-Agent':
'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:47.0) Gecko/20100101 Firefox/47.0'
}
resp = requests.post(url, post, [], verify=False, timeout=10).content
return resp
import json
def main():
name_list = []
'''
创建excel文件
Date:2020-1-6
'''
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
workbook = Workbook()
sheet = workbook.active
data = [[1001, '白元芳', '男', '13123456789'], [1002, '白洁', '女', '13233445566']]
sheet.append(['学号', '姓名', '性别', '电话'])
for row in data:
sheet.append(row)
tab = Table(displayName="Table1", ref="A1:E5")
tab.tableStyleInfo = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
sheet.add_table(tab)
workbook.save('./res/全班学生数据.xlsx')
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Class")
ws1['A1'] = 'nursery'
ws1['A2'] = 'jkg'
ws1['A3'] = 'skg'
for r in range(4, 15):
for c in range(1, 5):
if c == 1:
section = str(r - 3)
elif c == 2:
section = "A"
elif c == 3:
section = "B"
else:
section = "C"
d = ws1.cell(row=r, column=c, value=section)
# ws2 = wb.create_chartsheet("Name")
__author__ = 'vincent'
# -*- coding:utf-8 -*-
from openpyxl import Workbook
import urllib
from pyquery import PyQuery as Pq
from selenium import webdriver
import time
import StrUtil
w = Workbook()
#创建excel工作薄
ws = w.create_sheet(0)
#创建Excel工作表
def get_page_content_str(url):
time.sleep(1)
try:
print("现在开始抓取" + url)
headers = {
'User-Agent':
'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:23.0) Gecko/20100101 Firefox/23.0'
}
#伪装浏览器
request = urllib.request.Request(url=url, headers=headers)
#构造请求
m_fp = urllib.request.urlopen(request, timeout=500)
#访问网站获取源码
html_str = m_fp.read().decode('utf-8')
#读取源码，该网站使用的编码方式是utf-8
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd
import os
path = "C:/Work/test/"
xls_file = "merged.xlsx"
workbook = Workbook()
sheet = workbook.active
workbook.remove(sheet)
for dirpath, dirs, files in os.walk(path):
for filename in files:
if filename.endswith('.csv'):
csv_file = os.path.join(dirpath, filename)
print(csv_file)
base = os.path.basename(csv_file)
print(base)
new_sheet = workbook.create_sheet(base, 0)
df = pd.read_csv(csv_file)
for row in dataframe_to_rows(df, index=False, header=True):
# print(row)
new_sheet.append(row)
workbook.save(filename=xls_file)
# @Time : 2020/5/6 20:37
# @Author : tongyue
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
listData = ['a','b','c']
for row in range(1, 40):
ws1.append(range(600))
# ws1.append(listData)
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
ws3.cell(column=col, row=row, value=get_column_letter(col))
print(ws3['AA10'].value)
wb.save(filename = dest_filename)
