def createXML(dailysummary):
global floderName
global fileRoot
y = datetime.datetime.now().date()-timedelta(days=1)
dateStr = str(y)
filename = fileRoot + "_"+dateStr+".xlsx"
workbook = Workbook(floderName+"/"+filename)
sheet = workbook.add_worksheet()
sheet.write(0,0,"usercode")
sheet.write(0,1,"summary")
sheet.write(0,2,"loginCnt")
sheet.write(0,3,"zeroCnt")
sheet.write(0,4,"flowsize(MB)")
sheet.write(0,5,"visitcountry")
r =1
for summary in dailysummary:
sheet.write(r,0,summary[0])
sheet.write(r,1,summary[1])
sheet.write(r,2,summary[2])
sheet.write(r,3,summary[3])
sheet.write(r,4,summary[4])
sheet.write(r,5,summary[5])
r += 1
workbook.close()
def WorkBook_writeSheet(self, filename):
columns = ['Date', 'Month', 'ID #', 'Contact ID #', \
'Talked to Person X?', 'Closeness/Trust with X', \
'Connecting ID', 'Connector ID']
# Writes to csv file
with open(filename, 'w') as f:
writer = csv.writer(f)
writer.writerow(columns)
for row in self.sheet:
date = row[DATE_COLUMN]
month = row[MONTH_COLUMN]
connecting = row[CONNECTING_COLUMN]
connector = row[CONNECTOR_COLUMN]
talkVal = row[TALKED_WEIGHT]
closeVal = row[CLOSENESS_WEIGHT]
connectingID = row[CONNECTING_ID_COLUMN]
connectorID = row[CONNECTOR_ID_COLUMN]
row = [date, month, connecting, connector, talkVal, \
closeVal, connectingID, connectorID]
writer.writerow(row)
# Converts from the written csv file to xlsx
for csvfile in glob.glob(os.path.join('.', '*.csv')):
workbook = Workbook(csvfile[0:-4] + '.xlsx')
worksheet = workbook.add_worksheet()
with open(csvfile, 'rb') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, col)
workbook.close()
sys.exit()
def cvstran2xls(self, filename):
with open(filename, 'rb') as f:
reader = csv.reader(f)
cvscontent = []
#rownum=0
for row in reader:
#rownum = rownum + 1
#print rownum
# print len(cvscontent)
try:
cvscontent.append([x.decode('utf-8') for x in row])
#print '1',len(cvscontent)
except:
cvscontent.append([x.decode('gbk') for x in row])
#print '0',len(cvscontent)
'''
else:
cvscontent.append(row)
print '3', len(cvscontent)
'''
#file = xlwt.Workbook()
#table = file.add_sheet('sheet1', cell_overwrite_ok=True)
workbook = Workbook(os.path.splitext(filename)[0] + '.xlsx')
worksheet = workbook.add_worksheet()
#print 'content',len(cvscontent)
for row in range(len(cvscontent)):
#print len(cvscontent[row])
for col in range(len(cvscontent[row])):
worksheet.write(row, col, cvscontent[row][col])
#table.write(row, col, cvscontent[row][col])
#file.save(os.path.splitext(filename)[0] + '.xlsx')
workbook.close()
print os.path.splitext(filename)[0] + '.xlsx', 'OK'
def cvstran2xls(self, filename): #将csv文件转换为xlsx
with open(filename, 'rb') as f:
reader = csv.reader(f)
cvscontent = []
#rownum=0
for row in reader:
#rownum = rownum + 1
#print rownum
# print len(cvscontent)
try:
cvscontent.append([x.decode('utf-8') for x in row])
#print '1',len(cvscontent)
except:
cvscontent.append([x.decode('gbk') for x in row])
#print '0',len(cvscontent)
'''
else:
cvscontent.append(row)
print '3', len(cvscontent)
'''
workbook = Workbook(os.path.splitext(filename)[0] + '.xlsx')
worksheet = workbook.add_worksheet()
for row in range(len(cvscontent)):
for col in range(len(cvscontent[row])):
worksheet.write(row, col, cvscontent[row][col])
workbook.close()
print os.path.splitext(filename)[0] + '.xlsx', 'OK'
def write_to_excel(self,filename,items,fdir=DATA_DIR):
fpath=os.path.join(fdir,filename+'.xlsx')
workbook=Workbook(fpath)
worksheet=workbook.add_worksheet()
for i in range(len(items)):
for j in range(len(items.split(':::'))):
worksheet.write(i,j,items.split(':::')[j].decode('gbk'))
def createXML(type,record):
global floderName
y = datetime.datetime.now().date()-timedelta(days=1)
dateStr = str(y)
filename = type + "_"+dateStr+".xlsx"
workbook = Workbook(floderName+"/"+filename)
sheet = workbook.add_worksheet()
sheet.write(0,0,"imei")
sheet.write(0,1,"loginCnt")
sheet.write(0,2,"zeroCnt")
sheet.write(0,3,"flowsize")
sheet.write(0,4,"visitcountry")
sheet.write(0,5,"version")
r =1
for summary in record:
sheet.write(r,0,summary[0])
sheet.write(r,1,summary[1])
sheet.write(r,2,summary[2])
sheet.write(r,3,summary[3])
sheet.write(r,4,summary[4])
sheet.write(r,5,summary[5])
r += 1
workbook.close()
def publishQuery(period):
yesterQuery = """
SELECT * FROM
t_flowsummary_partner_adv
WHERE DATE(epochTime) = DATE_SUB(DATE(NOW()),INTERVAL 1 DAY)
"""
weeklyQuery = """
SELECT WEEK(NOW()) AS weeknum,iso2,partner,SUM(reserveup),SUM(reservedown),SUM(freeup),SUM(freedown),SUM(reservecnt),SUM(freecnt) FROM
t_flowsummary_partner_adv
WHERE DATE(epochTime) BETWEEN DATE_SUB(DATE(NOW()),INTERVAL 7 DAY) AND DATE_SUB(DATE(NOW()),INTERVAL 1 DAY)
GROUP BY iso2,partner
"""
rList = list()
con,cur = getCon("REMOTE","login_history")
if period == 'weekly':
cur.execute(weeklyQuery)
else:
cur.execute(yesterQuery)
tm = cur.fetchall()
for t in tm:
rList.append(t)
con.close()
foldername = createDailyFloder(period)
y = datetime.datetime.now().date()
dateStr = str(y)
filename = period + "_"+dateStr+".xlsx"
workbook = Workbook(foldername+"/"+filename)
sheet = workbook.add_worksheet()
date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})
if period == "daily":
sheet.write(0,0,u"日期".encode('utf-8'),date_format)
else:
sheet.write(0,0,u"周".encode('utf-8'))
sheet.write(0,1,u"国家".encode('utf-8'))
sheet.write(0,2,"partner")
sheet.write(0,3,u"包卡上行".encode('utf-8'))
sheet.write(0,4,u"包卡下行".encode('utf-8'))
sheet.write(0,5,u"非包卡上行".encode('utf-8'))
sheet.write(0,6,u"非包卡下行".encode('utf-8'))
sheet.write(0,7,u"包卡用户数".encode('utf-8'))
sheet.write(0,8,u"非包卡用户数".encode('utf-8'))
r =1
for row in rList:
sheet.write(r,0,row[0])
sheet.write(r,1,row[1])
sheet.write(r,2,row[2])
sheet.write(r,3,row[3])
sheet.write(r,4,row[4])
sheet.write(r,5,row[6])
sheet.write(r,6,row[5])
sheet.write(r,7,row[7])
sheet.write(r,8,row[8])
r += 1
workbook.close()
def __push_excel(self,file_name):
'''
Writes a set of data to an excel file
'''
# Open work book
workbook = Workbook(file_name)
worksheet = workbook.add_worksheet()
# start at top left corner and write headers
row, col = 0, 0
for header in self.excel['headers']:
worksheet.write(row,col, str(header))
col += 1
# start in second row and write data
row, col = 1, 0
for row_data in self.excel['data']:
for cell_data in row_data:
worksheet.write(row, col, str(cell_data))
col += 1
row += 1
col = 0
workbook.close()
# Empty memory (this usually happens every 4000 timesteps)
self.excel['header'] = []
self.excel['data'] = [[]]
def csvsToWorkbook( args ):
workbook = Workbook( args.workbook_filename )
icsv=0 # CSV file count
csv_filenames = args.csv_filenames.split( ',' )
for csv_filename in csv_filenames:
# Format name of sheet
print( "<"+csv_filename+">" )
basename = ntpath.basename( csv_filename )
namesplit = basename.split( '_' )
sheetname = ( "_".join( namesplit[3:] ) ).split( '.' )[0]
# Add the worksheet to the workbook
worksheet = workbook.add_worksheet( sheetname )
# Save data in the worksheet
with open( csv_filename, mode='r') as csv_file:
reader = csv.reader( csv_file )
for r, row in enumerate( reader ):
for c, col in enumerate( row ):
worksheet.write( r, c, col )
workbook.close()
def wrap_csvs(csvpath, outname):
directory_path = os.path.abspath(csvpath)
if outname is None:
filename = os.path.basename(directory_path + ".xlsx")
else:
filename = outname
workbook_name = os.path.join(directory_path, filename)
workbook = Workbook(workbook_name)
for c in glob.glob(os.path.join(csvpath, "*.csv")):
sheetname = os.path.basename(c[:c.rfind(".")])
print("Adding {} to {}".format(c, workbook_name))
worksheet = workbook.add_worksheet(sheetname)
with open(c, 'r') as f:
reader = csv.reader(f)
for rindex, row in enumerate(reader):
for cindex, col in enumerate(row):
try:
worksheet.write(rindex,cindex, float(col))
except ValueError:
worksheet.write(rindex, cindex, col)
workbook.close()
class XLSXWriter(BOMWriterBase):
def open(self):
try:
from xlsxwriter.workbook import Workbook
except ImportError:
raise ImportError("Please install xlsxwriter: `pip install xlsxwriter`")
self.workbook = Workbook(self.fname)
self.title_format = self.workbook.add_format({'bold': True, 'bg_color': '#999999'})
self.alter_format = self.workbook.add_format({'bg_color': '#dddddd'})
self.writer = self.workbook.add_worksheet()
self.writer.set_column(0, 0, 4.00)
self.writer.set_column(1, 1, 14.50)
self.writer.set_column(2, 2, 3.00)
self.writer.set_column(3, 3, 4.30)
self.writer.set_column(4, 5, 20.00)
self.writer.set_column(6, 7, 48.00)
self.line = 0
def writerow(self, row, header=False):
for col, cell in enumerate(row):
if header:
format = self.title_format
else:
if self.line % 2 == 0:
format = self.alter_format
else:
format = None
self.writer.write_string(self.line, col, unicode(cell), format)
self.line += 1
def close(self):
self.workbook.close()
def makedb(self):
conn = sqlite3.connect("registration.db")
c = conn.cursor()
table = "new_entries"
try:
query = "select * from %s" % table
c.execute(query)
workbook = Workbook("output.xlsx")
sheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})
sheet.set_column('A:A', 5)
sheet.set_column('B:B', 18)
sheet.set_column('C:C', 8)
sheet.set_column('D:D', 8)
sheet.set_column('E:E', 13)
sheet.set_column('F:F', 17)
sheet.set_column('G:G', 5)
sheet.write("A1", 'S.No', bold)
sheet.write("B1", 'Name', bold)
sheet.write("C1", 'Branch', bold)
sheet.write("D1", 'Section', bold)
sheet.write("E1", 'Phone no', bold)
sheet.write("F1", 'Email', bold)
sheet.write("G1", 'Size', bold)
for r, row in enumerate(c.fetchall()):
for c, col in enumerate(row):
sheet.write(r + 1, c, col)
workbook.close()
conn.close()
messagebox.showinfo(title="Registration", message="Successfully made excel file")
except:
messagebox.showerror(title="Registration", message="Database doesn't exist")
return
def download_replies(modeladmin, request, queryset):
output = BytesIO()
workbook = Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet('test')
question_columns = OrderedDict()
max_column = 0
row = 1
for reply in queryset:
for question in reply.answers.keys():
current_column = question_columns.get(question, max_column)
if current_column == max_column:
question_columns[question] = max_column
max_column += 1
sheet.write(row, current_column, reply.answers[question])
row += 1
responses = {}
for reply in queryset:
for question in reply.answers.keys():
if responses.get(question) is None:
responses[question] = []
responses[question].append(reply.answers[question])
for question in question_columns.keys():
sheet.write(0, question_columns[question], question)
workbook.close()
output.seek(0)
response = HttpResponse(output.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response['Content-Disposition'] = "attachment; filename=test.xlsx"
return response
def dataToXLSX(dataList, outFileName):
workbook = Workbook(outFileName)
worksheetData = workbook.add_worksheet('Data')
# Can we make this mapping automatic?
columnMap = {0: "A", 1: "B", 2: "C", 3: "D", 4: "E", 5: "F", 6: "G", 7: "H", 8: "I", 9: "J", 10: "K", 11: "L",
12: "M", 13: "N", 14: "O", 15: "P"}
bold = workbook.add_format({'bold': 1})
# Format the worksheet1 data cells that the chart will refer to
headings = dataList[0]
# Set column width. Note that it is not possible to "auto-fit" as this only happens at run-time in Excel
worksheetData.set_column('A:%s' % columnMap[len(dataList[0])], 15)
# Write headings row to workbook
worksheetData.write_row('A1', headings, bold) # note that alignment not applicable to the write_row method
# write data
rowCount = 2
for data in dataList[1:]:
columnCount = 0
for item in data:
if item[-1] == "C":
item = item[:-2]
worksheetData.write('%s%i' % (columnMap[columnCount], rowCount), float(item))
columnCount += 1
rowCount += 1
# add an auto filter and freeze pane on the headers row
worksheetData.autofilter('A1:%s%d' % (columnMap[len(dataList[0]) - 1], len(dataList)))
worksheetData.freeze_panes(1, 0)
workbook.close()
def getTask1():
global floderName
global fileRoot
# returnList=getAss()
bssList = getBss()
print bssList
y = datetime.datetime.now().date()-timedelta(days=1)
dateStr = str(y)
filename = fileRoot + "_"+dateStr+".xlsx"
workbook = Workbook(floderName+"/"+filename)
sheet = workbook.add_worksheet()
sheet.write(0,0,"visitcountry")
sheet.write(0,1,"imei")
sheet.write(0,2,"flowsize")
r =1
for bssResutle in bssList:
sheet.write(r,0,bssResutle[0])
sheet.write(r,1,bssResutle[1])
sheet.write(r,2,bssResutle[2])
r += 1
workbook.close()
def map_to_csv(file_name, track_info_map):
headers =["","acousticness","danceability","duration","energy","instrumentalness","key","liveness","loudness","mode",
"speechiness","tempo","time_signature","valence"]
csv_file = open(file_name,"wb")
writer = csv.writer(csv_file, quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(headers)
song_count = 0
for song_name in track_info_map.keys():
values = []
song_count += 1
for header in headers:
if header in track_info_map[song_name]:
values.append(track_info_map[song_name][header])
values.insert(0,song_name)
writer.writerow(values)
csv_file.close()
try:
work_book = xlrd.open_workbook("finalproject.xls")
work_sheet = work_book.get_sheet('datasheet1')
except:
base_name = os.path.splitext(file_name)[0]
work_book = Workbook(base_name + ".xlsx")
work_sheet = work_book.add_worksheet()
with open(file_name,'rb') as f:
reader = csv.reader(f)
for rindex, row in enumerate(reader):
for cindex, column in enumerate(row):
work_sheet.write(rindex,cindex,column)
work_book.close()
def create(input):
filename = common.pop_dict(input, "filename")
workbook = Workbook(filename)
formats = InputFactory.init_formats(input, workbook)
for sheet in input['sheets']:
XlsFactory.process_sheet(workbook, sheet, formats)
workbook.close()
return filename
def _create_excel(self):
output = StringIO.StringIO()
xls = Workbook(output, {"default_date_format": self.date_format})
sheet_name = self.sheet_name.strip()
if len(sheet_name) > 31:
sheet_name = sheet_name[0:31]
sheet = xls.add_worksheet(sheet_name)
return xls, sheet, output
def convert_tsv_xls(tsv):
name = tsv.strip("tsv$")
workbook = Workbook(name+"xls")
worksheet = workbook.add_worksheet()
tsv_reader = csv.reader(open(tsv, 'rb'), delimiter='\t')
for row, data in enumerate(tsv_reader):
worksheet.write_row(row, 0, data)
workbook.close()
def convert_csv_to_excel(self, csv_file, excel_file):
workbook = Workbook(excel_file)
worksheet = workbook.add_worksheet()
with open(csv_file, 'rb') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, col)
workbook.close()
def export_to_excel():
for csvfile in glob.glob(os.path.join('.', '*.csv')):
workbook = Workbook(csvfile.replace('.csv', '') + '.xlsx')
worksheet = workbook.add_worksheet()
with open(csvfile, 'rb') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, unicode(col, "utf8"))
workbook.close()
def to_xlsx(self, output):
''' Create a Excel spreadsheet based on the findings '''
fout = output if output is not None else 'FortifyReport.xlsx'
if not fout.endswith('.xlsx'):
fout += ".xlsx"
workbook = Workbook(fout)
self._write_xlsx_tabs(workbook)
self._write_xlsx_master(workbook)
print_info("Saved output to: " + BOLD + "%s\n" % fout)
workbook.close()
def write(file, col1, col2, title1, title2): out_file = Workbook(file) sheet = out_file.add_worksheet() sheet.write (0,0,title1) sheet.write (0,1,title2) for i, val in enumerate(col1): sheet.write (i+1,0,val) for i, val in enumerate(col2): sheet.write (i+1,1,val) out_file.close()
def cvsstran2xls(self, filename):
workbook = Workbook(os.path.splitext(filename)[0] + '.xlsx')
worksheet = workbook.add_worksheet()
with open(filename, 'rb') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, col)
workbook.close()
print os.path.splitext(filename)[0] + '.xls', 'OK'
def save_excel_multiple_sheets(dst_path_no_ext, tables, format='xlsx'):
try:
workbook = Workbook('{full_path}.{ext}'.format(full_path=dst_path_no_ext, ext=format))
for table in tables:
sheet=workbook.add_worksheet()
export_table_to_sheet(sheet, table)
workbook.close()
return True
except Exception as e:
print e
return False
def mergeToXlsx(fileList):
workbookPath = os.path.join(os.path.split(fileList[0])[0], os.path.split(os.path.split(fileList[0])[0])[1] + ".xlsx")
workbook = Workbook(workbookPath, {'strings_to_numbers': True})
for csvfile in fileList:
worksheet = workbook.add_worksheet(os.path.splitext(os.path.basename(csvfile))[0])
with open(csvfile, 'r') as file:
reader = csv.reader(file)
for rowNum, rowText in enumerate(reader):
for colNum, colText in enumerate(rowText):
worksheet.write(rowNum, colNum, colText)
workbook.close()
def export_exel(scenarios):
"Строит exel отчет по тесту"
metrics = [
"Общее кол-во запросов",
"Среднее время отклика, мс",
"median",
"90% line",
"Минимальное время отклика, мс",
"Максимальное время отклика, мс",
"Процент ошибок",
"Кол-во запросов в секунду",
"Пропускная способность, Kb/sev",
"Сред.квадратичное отклонение",
]
reports = (
["response_codes_per_second.png", "response_times_over_time.png", "response_times_vs_threads.png"],
[
"Изменение количества кодов в секунду в ходе испытания",
"Изменение времени ответа в ходе испытания",
"Изменение времени ответа в зависимости от кол-ва пользователей",
],
)
i = 0
test_number = 1
work_doc = Workbook("report.xlsx")
sheet = work_doc.add_worksheet(time.strftime("%d%m%y", time.localtime()))
sheet.set_column(0, 0, 35)
sheet.set_column(1, 1, 15)
for scenario in scenarios:
os.chdir(os.path.dirname(scenario))
with open("summary.csv", "r") as f:
reader = csv.reader(f, delimiter=",")
parameters = list(reader)
sheet.write(i, 0, "Тест №%s. %s" % (test_number, parameters[2][0]))
sheet.write(i + 2, 0, "Метрика")
sheet.write(i + 2, 1, "Результат")
for a in range(len(metrics)):
sheet.write(i + a + 3, 0, metrics[a])
sheet.write(i + a + 3, 1, parameters[2][a + 1])
i += 16
for a in range(len(reports[0])):
sheet.insert_image(i, 0, reports[0][a], {"x_scale": 0.5, "y_scale": 0.5})
sheet.write(i + 23, 0, "Рис.%s.%s. %s" % (test_number, a + 1, reports[1][a]))
i += 28
test_number += 1
sheet.write(i, 0, "Выводы")
work_doc.close()
def mergeToXlsx(directory, deliminator=','):
workbook = Workbook(os.path.join(directory, os.path.basename(directory)) + ".xlsx", {'strings_to_numbers': True})
for csvfile in glob.glob(os.path.join(directory, '*.csv')):
worksheet = workbook.add_worksheet(os.path.splitext(os.path.basename(csvfile))[0])
with open(csvfile, 'r') as file:
reader = csv.reader(file)
for rowNum, rowText in enumerate(reader):
splitLine = rowText[0].split(deliminator)
for colNum, colText in enumerate(splitLine):
worksheet.write(rowNum, colNum, colText)
workbook.close()
def creating_excel():
workbook = Workbook('/tmp/' + session_stamp + '.xlsx')
for csvfile in glob.glob(os.path.join('/tmp',session_stamp,'*.csv')):
sheet_name=get_org_name_from_orgid(os.path.basename(csvfile))
worksheet = workbook.add_worksheet(sheet_name)
with open(csvfile, 'rb') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(i.decode("utf-8", "replace") for i in row):
y = [ i.decode("utf-8", "replace") for i in row ]
print col
worksheet.write(r, c, col)
workbook.close()
def export_to_xlsx(data, chart={}, filename='export.xlsx', reverse=False):
""" export_to_xlsx(data, chart, filename): Exports data to an Excel Spreadsheet.
Data should be a dictionary with rows as keys; the values of which should be a dictionary with columns as keys; the value should be the value at the x, y coordinate.
Chart should be either True, in which case the values will be generated, or a dictionary with the following keys:
type: string, choose from: line, ...
series: list of dictionaries with the following: name, categories, values
title, xaxis_title, yaxis_title: strings
style: int
cell
x_offset
y_offset
Reverse is used to signal whether to reverse the row order or not (except for the header/first row)
"""
workbook = Workbook(filename)
worksheet = workbook.add_worksheet()
for (row, x) in enumerate([sorted(data.keys())[0]] + list(reversed(sorted(data.keys()[1:])))) if reverse is True else enumerate(sorted(data.iterkeys())):
for y in sorted(data[x].iterkeys()):
try:
if float(data[x][y]).is_integer():
worksheet.write(x if reverse is False else row, y, int(float(data[x][y])))
else:
worksheet.write(x if reverse is False else row, y, float(data[x][y]))
except ValueError:
worksheet.write(x if reverse is False else row, y, '' if data[x][y] == '---' else data[x][y])
if chart is not {}:
generated_chart = generate_chart(data)
for key in generated_chart.iterkeys():
if chart.get(key) is None:
chart[key] = generated_chart[key]
new_chart = workbook.add_chart({'type': chart['type']})
for each_series in chart['series']:
new_chart.add_series({'name': each_series['name'], 'categories': each_series['categories'], 'values': each_series['values']})
new_chart.set_title({'name': chart['title']})
new_chart.set_x_axis({'name': chart['xaxis_title']})
new_chart.set_y_axis({'name': chart['yaxis_title']})
new_chart.set_style(chart['style'])
worksheet.insert_chart(chart['cell'], new_chart, {'x_offset': chart['x_offset'], 'y_offset': chart['y_offset']})
workbook.close()
return
############################################################################### # # Example of how to use the XlsxWriter module to write hyperlinks # # Copyright 2013, John McNamara, [email protected] # from xlsxwriter.workbook import Workbook # Create a new workbook and add a worksheet workbook = Workbook('hyperlink.xlsx') worksheet = workbook.add_worksheet('Hyperlinks') # Format the first column worksheet.set_column('A:A', 30) # Add the standard url link format. url_format = workbook.add_format({'color': 'blue', 'underline': 1}) # Add a sample alternative link format. red_format = workbook.add_format({ 'color': 'red', 'bold': 1, 'underline': 1, 'size': 12, }) # Add an alternate description string to the URL. string = 'Python home' # Add a "tool tip" to the URL. tip = 'Get the latest Python news here.'
def do_rebuild(self):
self.env.cr.execute("""
drop view if exists vst_docs_apertura cascade;
create view vst_docs_apertura as (
SELECT a1.account_id,
a1.partner_id,
a1.type_document_it,
a1.nro_comprobante,
a2.date,
a1.date_maturity,
CASE
WHEN a3.name IS NULL THEN 'PEN'::text
ELSE 'USD'::text
END AS moneda,
CASE
WHEN a3.name IS NULL THEN a1.debit - a1.credit
ELSE NULL::numeric
END AS pventamn,
CASE
WHEN a3.name::text = 'USD'::text THEN a1.amount_currency
ELSE NULL::numeric
END AS pventame
FROM account_move_line a1
LEFT JOIN account_move a2 ON a2.id = a1.move_id
LEFT JOIN res_currency a3 ON a3.id = a1.currency_id
LEFT JOIN account_account a4 ON a4.id = a1.account_id
WHERE a2.fecha_special = true AND date_part('year'::text, a2.date) = (( SELECT main_parameter.fiscalyear
FROM main_parameter))::double precision AND date_part('month'::text, a2.date) <> '12'::double precision AND a4.internal_type::text = 'receivable'::text AND a2.state::text = 'posted'::text
)""")
self.env.cr.execute("""
drop view if exists saldos_doc_apertura cascade;
create view saldos_doc_apertura as (
SELECT a1.account_id,
a1.partner_id,
a1.type_document_it,
a1.nro_comprobante,
a1.date,
a1.date_maturity,
a1.moneda,
a1.pventamn,
a1.pventame,
sum(a2.debit - a2.credit) AS saldomn,
sum(a2.amount_currency) AS saldome,
max(a2.create_uid) AS propietario
FROM vst_docs_apertura a1
LEFT JOIN account_move_line a2 ON concat(a2.account_id, a2.partner_id, a2.type_document_it, a2.nro_comprobante) = concat(a1.account_id, a1.partner_id, a1.type_document_it, a1.nro_comprobante)
GROUP BY a1.account_id, a1.partner_id, a1.type_document_it, a1.nro_comprobante, a1.date, a1.date_maturity, a1.moneda, a1.pventamn, a1.pventame
)""")
self.env.cr.execute("""
drop view if exists vst_ctasctes_apertura cascade;
create view vst_ctasctes_apertura as (
SELECT a1.date AS fecha_emi,
a1.date_maturity AS fecha_ven,
partner.nro_documento,
partner.name AS cliente,
doc.name AS tdoc,
a1.nro_comprobante,
a1.pventamn,
a1.pventame,
CASE
WHEN a1.moneda = 'PEN'::text THEN a1.pventamn - a1.saldomn
ELSE NULL::numeric
END AS cancelamn,
CASE
WHEN a1.moneda = 'USD'::text THEN a1.pventame - a1.saldome
ELSE NULL::numeric
END AS cancelame,
a1.saldomn,
a1.saldome,
a1.moneda,
a1.propietario
FROM saldos_doc_apertura a1
LEFT JOIN res_partner partner ON partner.id = a1.partner_id
LEFT JOIN einvoice_catalog_01 doc ON doc.id = a1.type_document_it
)""")
self.env.cr.execute("""
drop view if exists vst_saldos_letras cascade;
create view vst_saldos_letras as (
SELECT a1.account_id,
a1.partner_id,
a1.type_document_it,
btrim(btrim(a1.nro_comprobante::text), chr(9)) AS nro_comprobante,
min(a1.invoice_id) AS id_factura,
CASE
WHEN a3.name IS NULL THEN 'PEN'::text
ELSE 'USD'::text
END AS moneda,
sum(a1.debit) AS debe,
sum(a1.credit) AS haber,
sum(a1.debit - a1.credit) AS saldomn,
sum(a1.amount_currency) AS saldome
FROM account_move_line a1
LEFT JOIN account_account a2 ON a2.id = a1.account_id
LEFT JOIN res_currency a3 ON a3.id = a2.currency_id
LEFT JOIN account_move a4 ON a4.id = a1.move_id
WHERE a2.internal_type::text = 'receivable'::text AND a2.centralized = true AND a4.state::text = 'posted'::text AND date_part('year'::text, a4.date) = (( SELECT main_parameter.fiscalyear
FROM main_parameter))::double precision
GROUP BY a1.account_id, a1.partner_id, a1.type_document_it, (btrim(btrim(a1.nro_comprobante::text), chr(9))), (
CASE
WHEN a3.name IS NULL THEN 'PEN'::text
ELSE 'USD'::text
END)
)""")
self.env.cr.execute("""
drop view if exists vst_ctasctes_letras cascade;
create view vst_ctasctes_letras as (
SELECT a7.fecha_canje AS fecha_emi,
a2.fecha_vencimiento AS fecha_ven,
a3.nro_documento,
a3.name AS cliente,
'LETRA'::text AS tdoc,
a1.nro_comprobante,
CASE
WHEN a1.moneda = 'PEN'::text THEN a1.debe
ELSE 0::numeric
END AS pventamn,
CASE
WHEN a1.moneda = 'USD'::text THEN a2.monto_divisa
ELSE 0::numeric
END AS pventame,
CASE
WHEN a1.moneda = 'PEN'::text THEN a1.haber
ELSE 0::numeric
END AS cancelamn,
CASE
WHEN a1.moneda = 'USD'::text THEN a2.monto_divisa - a1.saldome
ELSE 0::numeric
END AS cancelame,
CASE
WHEN a1.moneda = 'PEN'::text THEN a1.saldomn
ELSE 0::numeric
END AS saldomn,
CASE
WHEN a1.moneda = 'USD'::text THEN a1.saldome
ELSE 0::numeric
END AS saldome,
a1.moneda,
t10.user AS propietario
FROM vst_saldos_letras a1
INNER JOIN account_letras_payment_letra_manual a2 ON a2.nro_letra::text = a1.nro_comprobante
INNER JOIN res_partner a3 ON a3.id = a1.partner_id
INNER JOIN account_letras_payment a7 ON a7.id = a2.letra_payment_id and a7.partner_id = a3.id
LEFT JOIN (select letra_payment_id, max(ai.create_uid) as user from account_invoice ai
inner join account_letras_payment_factura alpf on alpf.invoice_id = ai.id
group by letra_payment_id) t10 on t10.letra_payment_id = a7.id
)""")
self.env.cr.execute("""
drop view if exists vst_saldos_factura cascade;
create view vst_saldos_factura as (
SELECT a1.account_id,
a1.partner_id,
a1.type_document_it,
btrim(btrim(a1.nro_comprobante::text), chr(9)) AS nro_comprobante,
min(a1.invoice_id) AS id_factura,
min(a1.id) AS id_apunte,
sum(a1.debit) AS debe,
sum(a1.credit) AS haber,
sum(a1.debit - a1.credit) AS saldomn,
sum(a1.amount_currency) AS saldome
FROM account_move_line a1
LEFT JOIN account_account a2 ON a2.id = a1.account_id
LEFT JOIN account_move a3 ON a3.id = a1.move_id
WHERE a2.internal_type::text = 'receivable'::text AND a3.state::text = 'posted'::text AND date_part('year'::text, a3.date) = (( SELECT main_parameter.fiscalyear
FROM main_parameter))::double precision AND COALESCE(a2.centralized, false) = false
GROUP BY a1.account_id, a1.partner_id, a1.type_document_it, (btrim(btrim(a1.nro_comprobante::text), chr(9)))
)""")
self.env.cr.execute("""
drop view if exists vst_ctasctes_facturas cascade;
create view vst_ctasctes_facturas as (
SELECT a2.date_invoice AS fecha_emi,
a2.date_due AS fecha_ven,
a3.nro_documento,
a3.name AS cliente,
a4.name AS tdoc,
a1.nro_comprobante,
CASE
WHEN a6.name::text = 'PEN'::text THEN a2.amount_total_company_signed
ELSE 0::numeric
END AS pventamn,
CASE
WHEN a6.name::text = 'USD'::text THEN a2.amount_total_signed
ELSE 0::numeric
END AS pventame,
CASE
WHEN a6.name::text = 'PEN'::text THEN a1.haber
ELSE 0::numeric
END AS cancelamn,
CASE
WHEN a6.name::text = 'USD'::text THEN a2.amount_total_signed - a1.saldome
ELSE 0::numeric
END AS cancelame,
CASE
WHEN a6.name::text = 'PEN'::text THEN a1.saldomn
ELSE 0::numeric
END AS saldomn,
CASE
WHEN a6.name::text = 'USD'::text THEN a1.saldome
ELSE 0::numeric
END AS saldome,
a6.name AS moneda,
a2.user_id AS propietario
FROM vst_saldos_factura a1
JOIN account_invoice a2 ON a2.id = a1.id_factura
LEFT JOIN res_partner a3 ON a3.id = a1.partner_id
LEFT JOIN einvoice_catalog_01 a4 ON a4.id = a1.type_document_it
LEFT JOIN res_currency a6 ON a6.id = a2.currency_id
)""")
self.env.cr.execute("""
drop view if exists vst_ctasctes_integral cascade;
create view vst_ctasctes_integral as (
SELECT vst_ctasctes_facturas.fecha_emi,
vst_ctasctes_facturas.fecha_ven,
vst_ctasctes_facturas.nro_documento,
vst_ctasctes_facturas.cliente,
vst_ctasctes_facturas.tdoc,
vst_ctasctes_facturas.nro_comprobante,
vst_ctasctes_facturas.pventamn,
vst_ctasctes_facturas.pventame,
vst_ctasctes_facturas.cancelamn,
vst_ctasctes_facturas.cancelame,
vst_ctasctes_facturas.saldomn,
vst_ctasctes_facturas.saldome,
vst_ctasctes_facturas.moneda,
vst_ctasctes_facturas.propietario
FROM vst_ctasctes_facturas
WHERE (vst_ctasctes_facturas.saldomn + vst_ctasctes_facturas.saldome) <> 0::numeric
UNION ALL
SELECT vst_ctasctes_letras.fecha_emi,
vst_ctasctes_letras.fecha_ven,
vst_ctasctes_letras.nro_documento,
vst_ctasctes_letras.cliente,
vst_ctasctes_letras.tdoc,
vst_ctasctes_letras.nro_comprobante,
vst_ctasctes_letras.pventamn,
vst_ctasctes_letras.pventame,
vst_ctasctes_letras.cancelamn,
vst_ctasctes_letras.cancelame,
vst_ctasctes_letras.saldomn,
vst_ctasctes_letras.saldome,
vst_ctasctes_letras.moneda,
vst_ctasctes_letras.propietario
FROM vst_ctasctes_letras
WHERE (vst_ctasctes_letras.saldomn + vst_ctasctes_letras.saldome) <> 0::numeric
UNION ALL
SELECT vst_ctasctes_apertura.fecha_emi,
vst_ctasctes_apertura.fecha_ven,
vst_ctasctes_apertura.nro_documento,
vst_ctasctes_apertura.cliente,
vst_ctasctes_apertura.tdoc,
vst_ctasctes_apertura.nro_comprobante,
vst_ctasctes_apertura.pventamn,
vst_ctasctes_apertura.pventame,
vst_ctasctes_apertura.cancelamn,
vst_ctasctes_apertura.cancelame,
vst_ctasctes_apertura.saldomn,
vst_ctasctes_apertura.saldome,
vst_ctasctes_apertura.moneda,
vst_ctasctes_apertura.propietario
FROM vst_ctasctes_apertura
WHERE (vst_ctasctes_apertura.saldomn + vst_ctasctes_apertura.saldome) <> 0::numeric)
""")
filtro = []
#usuario = self.env['res.users'].browse(self.env.uid)
#permisos = self.env['res.groups'].search([('name','=','Venta:Vendedor Normal')])[0]
#if self.env.uid in permisos.users.ids:
# filtro.append( ('propietario','=',self.env.uid) )
#permisos = self.env['res.groups'].search([('name','=','Venta:Vendedor Coorporativo')])[0]
#if self.env.uid in permisos.users.ids:
# filtro.append( ('propietario','=',self.env.uid) )
#permisos = self.env['res.groups'].search([('name','=','Venta:Jefe de Equipo')])[0]
#if self.env.uid in permisos.users.ids:
# contenedor = [self.env.uid]
# teams = self.env['crm.team'].search([('user_id','=',self.env.uid)])
# for team in teams:
# for i in team.member_ids:
# if i.id not in contenedor:
# contenedor.append(i.id)
# allpartner = self.env['res.users'].search([])
# for i in allpartner:
# if i.partner_id.team_id.id == usuario.partner_id.team_id.id:
# contenedor.append(i.id)
# filtro.append( ('propietario','in',contenedor ) )
#permisos = self.env['res.groups'].search([('name','=','Venta:Gerente')])[0]
#if self.env.uid in permisos.users.ids:
# filtro = []
self.env.cr.execute(
""" DROP VIEW IF EXISTS saldo_comprobante_periodo_propietario;
create or replace view saldo_comprobante_periodo_propietario as (
select row_number() OVER () AS id,* from
(
select * from vst_ctasctes_integral
) T
)
""")
import io
from xlsxwriter.workbook import Workbook
output = io.BytesIO()
########### PRIMERA HOJA DE LA DATA EN TABLA
#workbook = Workbook(output, {'in_memory': True})
direccion = self.env['main.parameter'].search([])[0].dir_create_file
workbook = Workbook(direccion + 'reporteperiodo.xlsx')
worksheet = workbook.add_worksheet("Saldo Comprobantes x Periodo")
#Print Format
worksheet.set_landscape() #Horizontal
worksheet.set_paper(9) #A-4
worksheet.set_margins(left=0.75, right=0.75, top=1, bottom=1)
worksheet.fit_to_pages(1, 0) # Ajustar por Columna
bold = workbook.add_format({'bold': True})
normal = workbook.add_format()
boldbord = workbook.add_format({'bold': True})
boldbord.set_border(style=2)
boldbord.set_align('center')
boldbord.set_align('vcenter')
boldbord.set_text_wrap()
boldbord.set_font_size(9)
boldbord.set_bg_color('#DCE6F1')
numbertres = workbook.add_format({'num_format': '0.000'})
numberdos = workbook.add_format({'num_format': '0.00'})
bord = workbook.add_format()
bord.set_border(style=1)
bord.set_text_wrap()
numberdos.set_border(style=1)
numbertres.set_border(style=1)
title = workbook.add_format({'bold': True})
title.set_align('center')
title.set_align('vcenter')
title.set_text_wrap()
title.set_font_size(20)
worksheet.set_row(0, 30)
x = 4
tam_col = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
tam_letra = 1.2
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
worksheet.merge_range(0, 0, 0, 11, u"Cuenta Corriente a la fecha",
title)
worksheet.write(3, 0, u"Propietario", boldbord)
worksheet.write(3, 1, u"Fecha Emision", boldbord)
worksheet.write(3, 2, u"Fecha Vencimiento", boldbord)
worksheet.write(3, 3, u"Nro Documento", boldbord)
worksheet.write(3, 4, u"Cliente", boldbord)
worksheet.write(3, 5, u"TD", boldbord)
worksheet.write(3, 6, u"Nro Comprobante", boldbord)
worksheet.write(3, 7, u"Precio Venta MN", boldbord)
worksheet.write(3, 8, u"Precio Venta ME", boldbord)
worksheet.write(3, 9, u"Cancela MN", boldbord)
worksheet.write(3, 10, u"Cancela ME", boldbord)
worksheet.write(3, 11, u"Saldo MN", boldbord)
worksheet.write(3, 12, u"Saldo ME", boldbord)
worksheet.write(3, 13, u"Moneda", boldbord)
for line in self.env['saldo.comprobante.periodo.propietario'].search(
filtro):
worksheet.write(
x, 0, line.propietario_name if line.propietario_name else '',
bord)
worksheet.write(x, 1, line.fecha_emi if line.fecha_emi else '',
bord)
worksheet.write(x, 2, line.fecha_ven if line.fecha_ven else '',
bord)
worksheet.write(x, 3,
line.nro_documento if line.nro_documento else '',
bord)
worksheet.write(x, 4, line.cliente if line.cliente else '', bord)
worksheet.write(x, 5, line.tdoc if line.tdoc else '', bord)
worksheet.write(
x, 6, line.nro_comprobante if line.nro_comprobante else '',
bord)
worksheet.write(x, 7, line.pventamn, numberdos)
worksheet.write(x, 8, line.pventame, numberdos)
worksheet.write(x, 9, line.cancelamn, numberdos)
worksheet.write(x, 10, line.cancelame, numberdos)
worksheet.write(x, 11, line.saldomn, numberdos)
worksheet.write(x, 12, line.saldome, numberdos)
worksheet.write(x, 13, line.moneda if line.moneda else '', bord)
x = x + 1
tam_col = [
15, 11, 14, 14, 14, 12, 13, 11, 10, 14, 14, 10, 14, 13, 14, 10, 16,
16, 20, 36
]
worksheet.set_column('A:A', tam_col[0])
worksheet.set_column('B:B', tam_col[1])
worksheet.set_column('C:C', tam_col[2])
worksheet.set_column('D:D', tam_col[3])
worksheet.set_column('E:E', tam_col[4])
worksheet.set_column('F:F', tam_col[5])
worksheet.set_column('G:G', tam_col[6])
worksheet.set_column('H:H', tam_col[7])
worksheet.set_column('I:I', tam_col[8])
worksheet.set_column('J:J', tam_col[9])
worksheet.set_column('K:K', tam_col[10])
worksheet.set_column('L:L', tam_col[11])
worksheet.set_column('M:M', tam_col[12])
worksheet.set_column('N:N', tam_col[13])
worksheet.set_column('O:O', tam_col[14])
worksheet.set_column('P:P', tam_col[15])
worksheet.set_column('Q:Q', tam_col[16])
worksheet.set_column('R:R', tam_col[17])
worksheet.set_column('S:S', tam_col[18])
worksheet.set_column('T:T', tam_col[19])
workbook.close()
f = open(direccion + 'reporteperiodo.xlsx', 'rb')
sfs_obj = self.pool.get('repcontab_base.sunat_file_save')
vals = {
'output_name': 'ReportePeriodo.xlsx',
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['export.file.save'].create(vals)
#import os
#os.system('c:\\eSpeak2\\command_line\\espeak.exe -ves-f1 -s 170 -p 100 "Se Realizo La exportación exitosamente Y A EDWARD NO LE GUSTA XDXDXDXDDDDDDDDDDDD" ')
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
def sage_xlsx(request, sage_batch_ref):
# Write to Excel
output = io.BytesIO()
workbook = Workbook(output, {'in_memory': True, 'remove_timezone': True})
worksheet = workbook.add_worksheet()
header = workbook.add_format({'bold': True})
header.set_bg_color('#F2F2F2')
header_a = workbook.add_format({'bold': True})
header_a.set_bg_color('#F2F2F2')
header_a.set_align('center')
header_b = workbook.add_format({'bold': True})
header_b.set_bg_color('#F2F2F2')
header_b.set_align('right')
date = workbook.add_format({'num_format': 'dd/mm/yyyy'})
date.set_align('center')
money = workbook.add_format({'num_format': '£#,##0.00'})
money.set_align('right')
center = workbook.add_format()
center.set_align('center')
italic_right = workbook.add_format()
italic_right.set_align('right')
italic_right.set_italic()
italic_center = workbook.add_format()
italic_center.set_align('center')
italic_center.set_italic()
worksheet.set_column('A:A', 14)
worksheet.set_column('B:B', 20)
worksheet.set_column('C:C', 20)
worksheet.set_column('D:D', 20)
worksheet.set_column('E:E', 14)
worksheet.set_column('F:F', 14)
worksheet.set_column('G:G', 30)
worksheet.set_column('H:H', 14)
worksheet.set_column('I:I', 14)
worksheet.set_column('J:J', 14)
worksheet.set_column('K:K', 14)
worksheet.set_column('L:L', 14)
worksheet.set_column('M:M', 14)
worksheet.set_column('N:N', 14)
worksheet.set_column('O:O', 14)
# Write Header
worksheet.write(0, 0, 'Type', header_a)
worksheet.write(0, 1, 'Account Reference', header_a)
worksheet.write(0, 2, 'Nominal A/C Ref', header_a)
worksheet.write(0, 3, 'Department Code', header_a)
worksheet.write(0, 4, 'Date', header_a)
worksheet.write(0, 5, 'Reference', header_a)
worksheet.write(0, 6, 'Details', header_a)
worksheet.write(0, 7, 'Net Amount', header_a)
worksheet.write(0, 8, 'Tax Code', header_a)
worksheet.write(0, 9, 'Tax Amount', header_a)
worksheet.write(0, 10, 'Exchange Rate', header_a)
worksheet.write(0, 11, 'Extra Reference', header_a)
worksheet.write(0, 12, 'User Name', header_a)
worksheet.write(0, 13, 'Project Refn', header_a)
worksheet.write(0, 14, 'Cost Code Refn', header_a)
n = 0
val_summary = 0
recs = SageBatchDetails.objects.filter(
sage_batch_ref_id=sage_batch_ref).order_by('-id')
transactions_for_batch_total = SageBatchTransactions.objects.filter(
sage_batch_ref_id=sage_batch_ref,
transactionsourceid__in=['GO1', 'GO3'],
remove=0).aggregate(Sum('sage_batch_netpayment'))
wip_transactions_for_batch = transactions_for_batch_total[
"sage_batch_netpayment__sum"]
sagebatchheader = SageBatchHeaders.objects.get(
sage_batch_ref=sage_batch_ref)
date = sagebatchheader.sage_batch_date.strftime('%d/%m/%Y')
transaction_total = 0
for transaction in recs:
n += 1
# val_summary += transaction.transnetpayment
if transaction.transactionsourceid == 'SP1' or transaction.transactionsourceid == 'SP2' or transaction.transactionsourceid == 'SP3'\
or transaction.transactionsourceid == 'GO1' or transaction.transactionsourceid == 'GO3':
worksheet.write(n, 0, 'JC', center)
worksheet.write(n, 1, '', italic_center)
worksheet.write(n, 2, transaction.nominal_account_ref)
worksheet.write(n, 3, '0')
worksheet.write(n, 4, '')
worksheet.write(n, 4, date)
worksheet.write(n, 5, '')
worksheet.write(n, 6, transaction.account_reference, center)
worksheet.write(n, 7, transaction.batch_detail_total, money)
worksheet.write(n, 8, transaction.tax_code, center)
if transaction.tax_code == 'T1':
transaction_wip = (transaction.batch_detail_total
or 0) * decimal.Decimal(0.2)
worksheet.write(n, 9, transaction_wip, money)
transaction_total = transaction_total + transaction_wip
else:
worksheet.write(n, 9, 0.00, money)
if wip_transactions_for_batch:
n += 1
worksheet.write(n, 0, 'JD', center)
worksheet.write(n, 1, '', italic_center)
worksheet.write(n, 2, 'XXXX')
worksheet.write(n, 3, '0')
worksheet.write(n, 4, '')
worksheet.write(n, 4, date)
worksheet.write(n, 5, '')
worksheet.write(n, 6, 'DD Call Debtor Control (XXXX)', center)
worksheet.write(n, 7, wip_transactions_for_batch, money)
worksheet.write(n, 8, '', center)
worksheet.write(n, 9, transaction_total, money)
# worksheet.write(n, 9, '=SUM(J2:J'+str(n)+')')
# if transaction.tax_code != 'T1':
# worksheet.write(n, 8, transaction.batch_detail_total*decimal(0.2), center)
# else:
# worksheet.write(n, 8, '0.00', money)
workbook.close()
output.seek(0)
filename = 'Sage Export '+ sage_batch_ref +' @ {date:%Y-%m-%d}.xlsx'\
.format(date=datetime.datetime.now())
response = HttpResponse(
output.read(),
content_type=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response['Content-Disposition'] = "attachment; filename=%s" % filename
output.close()
return response
def get_excel(self):
import io
from xlsxwriter.workbook import Workbook
try:
direccion = self.env['main.parameter.hr'].search(
[])[0].dir_create_file
except:
raise UserError(
'Falta configurar un directorio de descargas en el menu Configuracion/Parametros/Directorio de Descarga'
)
workbook = Workbook(direccion + 'prestamos.xlsx')
boldbord = workbook.add_format({'bold': True})
boldbord.set_border(style=2)
boldbord.set_align('center')
boldbord.set_align('vcenter')
boldbord.set_text_wrap()
boldbord.set_font_size(10)
boldbord.set_bg_color('#DCE6F1')
boldbord.set_font_name('Times New Roman')
especial1 = workbook.add_format()
especial1.set_align('center')
especial1.set_align('vcenter')
especial1.set_border(style=1)
especial1.set_text_wrap()
especial1.set_font_size(10)
especial1.set_font_name('Times New Roman')
especial3 = workbook.add_format({'bold': True})
especial3.set_align('center')
especial3.set_align('vcenter')
especial3.set_border(style=1)
especial3.set_text_wrap()
especial3.set_bg_color('#DCE6F1')
especial3.set_font_size(15)
especial3.set_font_name('Times New Roman')
numberdos = workbook.add_format({'num_format': '0'})
numberdos.set_border(style=1)
numberdos.set_font_size(10)
numberdos.set_font_name('Times New Roman')
dateformat = workbook.add_format({'num_format': 'd-m-yyyy'})
dateformat.set_border(style=1)
dateformat.set_font_size(10)
dateformat.set_font_name('Times New Roman')
hourformat = workbook.add_format({'num_format': 'hh:mm'})
hourformat.set_align('center')
hourformat.set_align('vcenter')
hourformat.set_border(style=1)
hourformat.set_font_size(10)
hourformat.set_font_name('Times New Roman')
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
##########ASISTENCIAS############
worksheet = workbook.add_worksheet("PRESTAMOS")
worksheet.set_tab_color('blue')
worksheet.merge_range(
1, 0, 1, 4,
"PRESTAMO %s %s" % (self.employee_id.name_related, self.date),
especial3)
worksheet.write(3, 0, "Empleado", boldbord)
worksheet.merge_range(3, 1, 3, 2, self.employee_id.name_related,
especial1)
worksheet.write(3, 3, "Fecha de Prestamo", boldbord)
worksheet.write(3, 4, self.date, especial1)
worksheet.write(5, 0, "Tipo de Prestamo", boldbord)
worksheet.merge_range(5, 1, 5, 2, self.loan_type_id.name, especial1)
worksheet.write(5, 3, "Numero de Cuotas", boldbord)
worksheet.write(5, 4, self.fees_number, especial1)
x = 7
worksheet.write(x, 0, "CUOTA", boldbord)
worksheet.write(x, 1, "MONTO", boldbord)
worksheet.write(x, 2, "FECHA DE PAGO", boldbord)
worksheet.write(x, 3, "DEUDA POR PAGAR", boldbord)
worksheet.write(x, 4, "VALIDACION", boldbord)
x = 8
for line in self.line_ids:
worksheet.write(x, 0, line.fee if line.fee else 0, numberdos)
worksheet.write(x, 1, line.amount if line.amount else 0, numberdos)
worksheet.write(x, 2, line.date if line.date else '', especial1)
worksheet.write(x, 3, line.debt if line.debt else 0, numberdos)
worksheet.write(
x, 4,
dict(line._fields['validation'].selection).get(line.validation)
if line.validation else '', especial1)
x += 1
tam_col = [12, 12, 12, 12, 12, 12]
worksheet.set_column('A:A', tam_col[0])
worksheet.set_column('B:B', tam_col[1])
worksheet.set_column('C:C', tam_col[2])
worksheet.set_column('D:D', tam_col[3])
worksheet.set_column('E:E', tam_col[4])
worksheet.set_column('F:F', tam_col[5])
workbook.close()
f = open(direccion + 'prestamos.xlsx', 'rb')
vals = {
'output_name': 'Prestamo - %s.xlsx' % (self.date),
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['planilla.export.file'].create(vals)
return {
"type": "ir.actions.act_window",
"res_model": "planilla.export.file",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
alldata = df.values.flatten()
alldata = alldata[~np.isnan(alldata)]
if 'bins' in kwargs:
h, b = np.histogram(alldata, bins=kwargs['bin'])
else:
h, b = np.histogram(alldata)
bins = b
bindf = {}
for colname, data in df.items():
data = data.dropna().values
h, b = np.histogram(data, bins=bins)
bindf[colname] = pandas.Series(h, index=[x for x in b[:-1]])
df = pandas.DataFrame(bindf)
worksheet = writeData(df, wb, sheetname, **kwargs)
params = {'type': 'column'}
if 'subtype' in kwargs:
params['subtype'] = kwargs['subtype']
chart = wb.add_chart(params)
__addAxisInfo(chart, kwargs)
kwargs['gap'] = 0
addSeries(df, chart, sheetname, **kwargs)
# Insert the chart into the worksheet (with an offset).
cell = __getLocation(df, kwargs)
worksheet.insert_chart(cell, chart, {'x_scale': 2.0, 'y_scale': 2.0})
if __name__ == "__main__":
wb = Workbook('test.xlsx')
df = pandas.DataFrame.from_csv('test_dates.csv')
plotLineChart(df, wb, 'test_dates', style=42)
def do_oexcel(self):
compania = ""
today = ""
import io
from xlsxwriter.workbook import Workbook
output = io.BytesIO()
########### PRIMERA HOJA DE LA DATA EN TABLA
#workbook = Workbook(output, {'in_memory': True})
direccion = self.env['main.parameter'].search([])[0].dir_create_file
workbook = Workbook(direccion + 'reporte_garantia.xlsx')
worksheet = workbook.add_worksheet("Kardex")
bold = workbook.add_format({'bold': True})
bold.set_font_size(8)
normal = workbook.add_format()
boldbord = workbook.add_format({'bold': True})
boldbord.set_border(style=2)
boldbord.set_align('center')
boldbord.set_align('vcenter')
boldbord.set_text_wrap()
boldbord.set_font_size(8)
boldbord.set_bg_color('#DCE6F1')
especial1 = workbook.add_format({'bold': True})
especial1.set_align('center')
especial1.set_align('vcenter')
especial1.set_text_wrap()
especial1.set_font_size(15)
especial4 = workbook.add_format({'bold': True})
especial4.set_align('center')
especial4.set_align('vcenter')
especial4.set_text_wrap()
especial4.set_font_size(15)
numbertres = workbook.add_format({'num_format': '0.000'})
numberdos = workbook.add_format({'num_format': '0.00'})
numberseis = workbook.add_format({'num_format': '0.000000'})
numberseis.set_font_size(8)
numberocho = workbook.add_format({'num_format': '0.00000000'})
numberocho.set_font_size(8)
bord = workbook.add_format()
bord.set_border(style=1)
bord.set_font_size(8)
bord2 = workbook.add_format({'align': 'right', 'border': 1})
bord2.set_font_size(8)
numberdos.set_border(style=1)
numberdos.set_font_size(8)
numbertres.set_font_size(8)
numbertres.set_border(style=1)
numberseis.set_border(style=1)
numberocho.set_border(style=1)
numberdosbold = workbook.add_format({
'num_format': '0.00',
'border': 1
})
numberdosbold.set_font_size(8)
numberseisbold = workbook.add_format({
'num_format': '0.000000',
'border': 1
})
numberseisbold.set_font_size(8)
formatMoneyWithBorder = workbook.add_format({
'valign':
'vcenter',
'align':
'right',
'border':
1,
'num_format':
'"S/." #,##0.00'
})
formatMoneyWithBorder.set_font_size(8)
formatCompraWithBorder = workbook.add_format({
'valign': 'vcenter',
'align': 'right',
'border': 1,
'num_format': '0.0000'
})
formatCompraWithBorder.set_font_size(8)
x = 10
y = 10
tam_col = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
tam_letra = 1.2
import datetime
today = datetime.datetime.today().strftime('%Y-%m-%d')
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
worksheet.merge_range(0, 1, 0, 6, "FORMATO : REPORTE DE TARIFAS",
especial1)
worksheet.write(3, 5, 'FECHA DE EMISION:', boldbord)
worksheet.write(3, 6, today)
#Categoria
worksheet.merge_range(8, 1, 9, 1, u"NOMBRE", boldbord)
worksheet.merge_range(8, 2, 9, 2, u"CANTIDAD MIN.", boldbord)
#default_code_producto
worksheet.merge_range(8, 3, 9, 3, u"ULTIMA COMPRA", boldbord)
worksheet.merge_range(8, 4, 9, 4, u"COSTO PROMEDIO", boldbord)
worksheet.merge_range(8, 5, 9, 5, u"FECHA DE INICIO", boldbord)
worksheet.merge_range(8, 6, 9, 6, u"FECHA FINAL", boldbord)
worksheet.merge_range(8, 7, 9, 7, u"PRECIO", boldbord)
#COSTO
#costo*stock
listas = self.env['product.pricelist.item'].search([('pricelist_id',
'=', self.id)])
print(list(listas))
for line in listas:
worksheet.write(x, 1, line.name if line.name else '', bord)
worksheet.write(x, 2,
line.min_quantity if line.min_quantity else 0,
bord)
worksheet.write(x, 3, line.last_cost if line.last_cost else 0,
bord)
worksheet.write(x, 4,
line.standart_price if line.standart_price else 0,
bord2)
worksheet.write(x, 5, line.date_start if line.date_start else '',
bord)
worksheet.write(x, 6, line.date_end if line.date_end else '', bord)
worksheet.write(x, 7, line.price if line.price else 0, bord)
x = x + 1
total_saldo = 0
total_saldo_acumulado = 0
i = 0
y = 0
p = 0
tam_col = [11, 50, 20, 14, 14, 14, 14, 40, 11, 11, 11, 11]
worksheet.set_column('A:A', tam_col[0])
worksheet.set_column('B:B', tam_col[1])
worksheet.set_column('C:C', tam_col[2])
worksheet.set_column('D:D', tam_col[3])
worksheet.set_column('E:E', tam_col[4])
worksheet.set_column('F:F', tam_col[5])
worksheet.set_column('G:G', tam_col[6])
worksheet.set_column('H:H', tam_col[7])
worksheet.set_column('I:I', tam_col[8])
worksheet.set_column('J:J', tam_col[9])
worksheet.set_column('K:K', tam_col[10])
worksheet.set_column('L:L', tam_col[11])
workbook.close()
f = open(direccion + 'reporte_garantia.xlsx', 'rb')
sfs_obj = self.pool.get('repcontab_base.sunat_file_save')
vals = {
'output_name': 'reporte_garantia.xlsx',
'output_file': base64.encodestring(''.join(f.readlines())),
}
mod_obj = self.env['ir.model.data']
act_obj = self.env['ir.actions.act_window']
sfs_id = self.env['export.file.save'].create(vals)
result = {}
#import pdb; pdb.set_trace()
#view_ref = mod_obj.get_object_reference('account_contable_book_it', 'export_file_save_action')
#view_id = view_ref and view_ref[1] or False
#result = act_obj.read( [view_id] )
print sfs_id
#import os
#os.system('c:\\eSpeak2\\command_line\\espeak.exe -ves-f1 -s 170 -p 100 "Se Realizo La exportación exitosamente Y A EDWARD NO LE GUSTA XDXDXDXDDDDDDDDDDDD" ')
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
def do_rebuild(self):
period_ini = self.period_ini
period_end = self.period_end
filtro = []
self.env.cr.execute("""
CREATE OR REPLACE view account_forth_category as (
SELECT *
FROM get_honorarios_1_1_1(periodo_num('""" + period_ini.code +
"""'),periodo_num('""" + period_end.code + """'))
)""")
#DSC_Exportar a CSV por el numero de filas
self.env.cr.execute("""select count(*) from account_forth_category""")
rows = self.env.cr.fetchone()
#if self.type_show == 'excel' and rows[0] > 1000:
# self.type_show = 'csv'
if self.type_show == 'pantalla':
return {
'domain': filtro,
'type': 'ir.actions.act_window',
'res_model': 'account.forth.category',
'view_mode': 'tree',
'view_type': 'form',
'views': [(False, 'tree')],
}
#DSC_
if self.type_show == 'csv':
direccion = self.env['main.parameter'].search(
[])[0].dir_create_file
docname = 'LibroHonorarios.csv'
#CSV
sql_query = """ COPY (SELECT * FROM account_forth_category )TO '""" + direccion + docname + """' WITH DELIMITER ',' CSV HEADER
"""
self.env.cr.execute(sql_query)
#Caracteres Especiales
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
f = open(direccion + docname, 'rb')
vals = {
'output_name': docname,
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['export.file.save'].create(vals)
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
if self.type_show == 'excel':
import io
from xlsxwriter.workbook import Workbook
output = io.BytesIO()
########### PRIMERA HOJA DE LA DATA EN TABLA
#workbook = Workbook(output, {'in_memory': True})
direccion = self.env['main.parameter'].search(
[])[0].dir_create_file
workbook = Workbook(direccion + 'tempo_cuartacategoria.xlsx')
worksheet = workbook.add_worksheet("Libro Honorarios")
bold = workbook.add_format({'bold': True})
normal = workbook.add_format()
boldbord = workbook.add_format({'bold': True})
boldbord.set_border(style=2)
boldbord.set_align('center')
boldbord.set_align('vcenter')
boldbord.set_text_wrap()
boldbord.set_font_size(9)
boldbord.set_bg_color('#DCE6F1')
numbertres = workbook.add_format({'num_format': '0.000'})
numberdos = workbook.add_format({'num_format': '0.00'})
bord = workbook.add_format()
bord.set_border(style=1)
bord.set_text_wrap()
numberdos.set_border(style=1)
numbertres.set_border(style=1)
x = 4
tam_col = [
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
]
tam_letra = 1.2
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
worksheet.write(0, 0, "Libro Honorarios:", bold)
tam_col[0] = tam_letra * len(
"Libro Honorarios:") if tam_letra * len(
"Libro Honorarios:") > tam_col[0] else tam_col[0]
worksheet.write(0, 1, self.period_ini.name, normal)
tam_col[1] = tam_letra * len(
self.period_ini.name) if tam_letra * len(
self.period_ini.name) > tam_col[1] else tam_col[1]
worksheet.write(0, 2, self.period_end.name, normal)
tam_col[2] = tam_letra * len(
self.period_end.name) if tam_letra * len(
self.period_end.name) > tam_col[2] else tam_col[2]
worksheet.write(1, 0, "Fecha:", bold)
tam_col[0] = tam_letra * len("Fecha:") if tam_letra * len(
"Fecha:") > tam_col[0] else tam_col[0]
#worksheet.write(1,1, total.date.strftime('%Y-%m-%d %H:%M'),bord)
import datetime
worksheet.write(1, 1, str(datetime.datetime.today())[:10], normal)
tam_col[1] = tam_letra * len(str(datetime.datetime.today(
))[:10]) if tam_letra * len(str(
datetime.datetime.today())[:10]) > tam_col[1] else tam_col[1]
worksheet.write(3, 0, "Periodo", boldbord)
tam_col[0] = tam_letra * len("Periodo") if tam_letra * len(
"Periodo") > tam_col[0] else tam_col[0]
worksheet.write(3, 1, "Libro", boldbord)
tam_col[1] = tam_letra * len("Libro") if tam_letra * len(
"Libro") > tam_col[1] else tam_col[1]
worksheet.write(3, 2, "Voucher", boldbord)
tam_col[2] = tam_letra * len("Voucher") if tam_letra * len(
"Voucher") > tam_col[2] else tam_col[2]
worksheet.write(3, 3, u"Fecha Emisión", boldbord)
tam_col[3] = tam_letra * len(u"Fecha Emisión") if tam_letra * len(
u"Fecha Emisión") > tam_col[3] else tam_col[3]
worksheet.write(3, 4, u"Fecha Pago", boldbord)
tam_col[4] = tam_letra * len(u"Fecha Pago") if tam_letra * len(
u"Fecha Pago") > tam_col[4] else tam_col[4]
worksheet.write(3, 5, "TD.", boldbord)
tam_col[5] = tam_letra * len(
"TD.") if tam_letra * len("TD.") > tam_col[5] else tam_col[5]
worksheet.write(3, 6, "Serie", boldbord)
tam_col[6] = tam_letra * len("Serie") if tam_letra * len(
"Serie") > tam_col[6] else tam_col[6]
worksheet.write(3, 7, u"Número", boldbord)
tam_col[7] = tam_letra * len(u"Número") if tam_letra * len(
u"Número") > tam_col[7] else tam_col[7]
worksheet.write(3, 8, "TDP.", boldbord)
tam_col[8] = tam_letra * len(
"TDP.") if tam_letra * len("TDP.") > tam_col[8] else tam_col[8]
worksheet.write(3, 9, "Num. Documento", boldbord)
tam_col[9] = tam_letra * len("Num. Documento") if tam_letra * len(
"Num. Documento") > tam_col[9] else tam_col[9]
worksheet.write(3, 10, "Razon Social", boldbord)
tam_col[10] = tam_letra * len("Razon Social") if tam_letra * len(
"Razon Social") > tam_col[10] else tam_col[10]
worksheet.write(3, 11, u"Divisa", boldbord)
tam_col[11] = tam_letra * len(u"Divisa") if tam_letra * len(
u"Divisa") > tam_col[11] else tam_col[11]
worksheet.write(3, 12, u"Monto", boldbord)
tam_col[12] = tam_letra * len(u"Monto") if tam_letra * len(
u"Monto") > tam_col[12] else tam_col[12]
worksheet.write(3, 13, u"Retención", boldbord)
tam_col[13] = tam_letra * len(u"Retención") if tam_letra * len(
u"Retención") > tam_col[13] else tam_col[13]
worksheet.write(3, 14, "Neto Pagado", boldbord)
tam_col[14] = tam_letra * len("Neto Pagado") if tam_letra * len(
"Neto Pagado") > tam_col[14] else tam_col[14]
worksheet.write(3, 15, "Estado", boldbord)
tam_col[15] = tam_letra * len("Estado") if tam_letra * len(
"Estado") > tam_col[15] else tam_col[15]
worksheet.write(3, 16, u"Periodo Pago", boldbord)
tam_col[16] = tam_letra * len(u"Periodo Pago") if tam_letra * len(
u"Periodo Pago") > tam_col[16] else tam_col[16]
for line in self.env['account.forth.category'].search([]):
worksheet.write(x, 0, line.periodo if line.periodo else '',
bord)
worksheet.write(x, 1, line.libro if line.libro else '', bord)
worksheet.write(x, 2, line.voucher if line.voucher else '',
bord)
worksheet.write(x, 3,
line.fechaemision if line.fechaemision else '',
bord)
worksheet.write(x, 4, line.fechapago if line.fechapago else '',
bord)
worksheet.write(
x, 5, line.tipodocumento if line.tipodocumento else '',
bord)
worksheet.write(x, 6, line.serie if line.serie else '', bord)
worksheet.write(x, 7, line.numero if line.numero else '', bord)
worksheet.write(x, 8, line.tipodoc if line.tipodoc else '',
bord)
worksheet.write(x, 9, line.numdoc if line.numdoc else '', bord)
worksheet.write(x, 10, line.partner if line.partner else '',
bord)
worksheet.write(x, 11, line.divisa if line.divisa else '',
bord)
worksheet.write(x, 12, line.monto, numberdos)
worksheet.write(x, 13, line.retencion, numberdos)
worksheet.write(x, 14, line.neto, numberdos)
worksheet.write(x, 15, line.state if line.state else '', bord)
worksheet.write(x, 16,
line.periodopago if line.periodopago else '',
bord)
tam_col[0] = tam_letra * len(
line.periodo if line.periodo else ''
) if tam_letra * len(line.periodo if line.periodo else ''
) > tam_col[0] else tam_col[0]
tam_col[1] = tam_letra * len(
line.libro if line.libro else ''
) if tam_letra * len(line.libro if line.libro else ''
) > tam_col[1] else tam_col[1]
tam_col[2] = tam_letra * len(
line.voucher if line.voucher else ''
) if tam_letra * len(line.voucher if line.voucher else ''
) > tam_col[2] else tam_col[2]
tam_col[3] = tam_letra * len(
line.fechaemision if line.fechaemision else ''
) if tam_letra * len(line.fechaemision if line.fechaemision
else '') > tam_col[3] else tam_col[3]
tam_col[4] = tam_letra * len(
line.fechapago if line.fechapago else ''
) if tam_letra * len(line.fechapago if line.fechapago else ''
) > tam_col[4] else tam_col[4]
tam_col[5] = tam_letra * len(
line.tipodocumento if line.tipodocumento else ''
) if tam_letra * len(line.tipodocumento if line.tipodocumento
else '') > tam_col[5] else tam_col[5]
tam_col[6] = tam_letra * len(
line.serie if line.serie else ''
) if tam_letra * len(line.serie if line.serie else ''
) > tam_col[6] else tam_col[6]
tam_col[7] = tam_letra * len(
line.numero if line.numero else ''
) if tam_letra * len(line.numero if line.numero else ''
) > tam_col[7] else tam_col[7]
tam_col[8] = tam_letra * len(
line.tipodoc if line.tipodoc else ''
) if tam_letra * len(line.tipodoc if line.tipodoc else ''
) > tam_col[8] else tam_col[8]
tam_col[9] = tam_letra * len(
line.numdoc if line.numdoc else ''
) if tam_letra * len(line.numdoc if line.numdoc else ''
) > tam_col[9] else tam_col[9]
tam_col[10] = tam_letra * len(
line.partner if line.partner else ''
) if tam_letra * len(line.partner if line.partner else ''
) > tam_col[10] else tam_col[10]
tam_col[11] = tam_letra * len(
line.divisa if line.divisa else ''
) if tam_letra * len(line.divisa if line.divisa else ''
) > tam_col[11] else tam_col[11]
tam_col[12] = tam_letra * len(
"%0.2f" % line.monto) if tam_letra * len(
"%0.2f" % line.monto) > tam_col[12] else tam_col[12]
tam_col[13] = tam_letra * len(
"%0.2f" % line.retencion) if tam_letra * len(
"%0.2f" %
line.retencion) > tam_col[13] else tam_col[13]
tam_col[14] = tam_letra * len(
"%0.2f" % line.neto) if tam_letra * len(
"%0.2f" % line.neto) > tam_col[14] else tam_col[14]
tam_col[15] = tam_letra * len(
line.state if line.state else ''
) if tam_letra * len(line.state if line.state else ''
) > tam_col[15] else tam_col[15]
tam_col[16] = tam_letra * len(
line.periodopago if line.periodopago else ''
) if tam_letra * len(line.periodopago if line.periodopago else
'') > tam_col[16] else tam_col[16]
x = x + 1
tam_col = [
15.5, 7.14, 9, 14, 14, 8, 9, 11, 9, 17, 45, 7, 11, 11, 11, 8,
12
]
worksheet.set_column('A:A', tam_col[0])
worksheet.set_column('B:B', tam_col[1])
worksheet.set_column('C:C', tam_col[2])
worksheet.set_column('D:D', tam_col[3])
worksheet.set_column('E:E', tam_col[4])
worksheet.set_column('F:F', tam_col[5])
worksheet.set_column('G:G', tam_col[6])
worksheet.set_column('H:H', tam_col[7])
worksheet.set_column('I:I', tam_col[8])
worksheet.set_column('J:J', tam_col[9])
worksheet.set_column('K:K', tam_col[10])
worksheet.set_column('L:L', tam_col[11])
worksheet.set_column('M:M', tam_col[12])
worksheet.set_column('N:N', tam_col[13])
worksheet.set_column('O:O', tam_col[14])
worksheet.set_column('P:P', tam_col[15])
worksheet.set_column('Q:Q', tam_col[16])
workbook.close()
f = open(direccion + 'tempo_cuartacategoria.xlsx', 'rb')
sfs_obj = self.pool.get('repcontab_base.sunat_file_save')
vals = {
'output_name': 'LibroHonorarios.xlsx',
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['export.file.save'].create(vals)
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
def __init__(self, fileName, data):
self.wk = Workbook(fileName)
self.wk.set_properties({
'title':
'This is a Tesla Loginfo',
'subject':
'With document properties',
'author':
'Devin Xu',
'manager':
'Levy Li',
'company':
'NVIDIA',
'category':
'Log parser spreadsheets',
'comments':
'Created with Python and XlsxWriter',
})
self.loginfo = data
self.defineTitle = [
"filePath",
"^SN",
"SerialNumber",
"ScanSN",
"ecid",
"IRQ",
"KernelDriver",
"ModsIRQ",
"ateSpeedo",
"VBIOSVersion",
"AteIddq",
"MemorySize",
"T148Failure",
"MarketingName",
"MemoryVendor",
"MemoryPN",
"ProductPN",
"Station",
"BuildDate",
"ModsVersion",
"699PN",
"Project",
"exitErroCode",
"LastErrorCode",
"failuresite",
"site\d+.+",
"fused*",
"failurePartition",
"pageRetirements",
"Nautilus Fatorcy Code",
"Failure Details",
"Failure Code",
".+TestTime",
"EC139FailTime"
"modsStartTime",
"modsEndTime",
"TestTime",
"HostName",
"FLATID",
"E.+SN",
"DiagVer",
"StartTestTime",
"EndTesttime",
"ErrorCode",
"lastEnterTest",
"lastEnterTestTime",
# "lastStation",
"reboot",
".+Gflops",
# ,"INPUT.+"
# ,"OUTPUT_NVVDD"
# ,"AdcError*"
"_MAX$",
".+RawData"
#,"_Min",".+lastMax",".+Delta"
,
"_R$",
"hbmIndex.+",
"GPU_",
".+print",
"local*",
"host*",
"PEXLanesXStatus",
"PEXLanesYStatus",
"NVLink0X",
"NVLink0Y",
"NVLink1X",
"NVLink1Y",
"NVLink2X",
"NVLink2Y",
"NVLink3X",
"NVLink3Y",
"NVLink4X",
"NVLink4Y",
"NVLink5X",
"NVLink5Y"
]
self.orderTitleList = []
"NumberOfRatings", "Price", "Link"
])
restoIndex = 0
printData(1)
time.sleep(0.3)
for i in range(1, pageCount):
driver.find_element_by_css_selector("span[data-from=" + "'" + str(i) +
"00" + "'" + "]").click()
element = WebDriverWait(driver, 30).until(
EC.presence_of_element_located((By.ID, "results-pagination")))
time.sleep(2)
printData(i + 1)
# In[6]:
workbook = Workbook(dataFolder + city + "OTLinks" + '.xlsx')
worksheet = workbook.add_worksheet()
with open(dataFolder + city + "OTLinks.csv") as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, col)
workbook.close()
# In[ ]:
# In[ ]:
def getWorkbook(fname, options=None):
"""Return a xlsxwriter Workbook by the given name"""
if options is not None:
return Workbook(fname, options)
return Workbook(fname)
def get_excel(self):
import io
from xlsxwriter.workbook import Workbook
ReportBase = self.env['report.base']
direccion = self.env['main.parameter'].search(
[('company_id', '=', self.company_id.id)], limit=1).dir_create_file
if not direccion:
raise UserError(
u'No existe un Directorio Exportadores configurado en Parametros Principales de Contabilidad para su Compañía'
)
workbook = Workbook(direccion + 'Analisis_Vencimientos.xlsx')
workbook, formats = ReportBase.get_formats(workbook)
import importlib
import sys
importlib.reload(sys)
##########ANALISIS VENCIMIENTO############
worksheet = workbook.add_worksheet("ANALISIS VENCIMIENTO")
worksheet.set_tab_color('blue')
HEADERS = [
'FECHA EM', 'FECHA VEN', 'CUENTA', 'DIVISA', 'TDP', 'RUC',
'PARTNER', 'TD', 'NRO COMPROBANTE', u'CU BANCO', u'SALDO MN',
u'SALDO ME', u'0 - 30', u'31 - 60', u'61 - 90', u'91 - 120',
u'121 - 150', u'151 - 180', u'181 - MÁS'
]
worksheet = ReportBase.get_headers(worksheet, HEADERS, 0, 0,
formats['boldbord'])
x = 1
for line in self.env['maturity.analysis.book'].search([]):
worksheet.write(x, 0, line.fecha_emi if line.fecha_emi else '',
formats['dateformat'])
worksheet.write(x, 1, line.fecha_ven if line.fecha_ven else '',
formats['dateformat'])
worksheet.write(x, 2, line.cuenta if line.cuenta else '',
formats['especial1'])
worksheet.write(x, 3, line.divisa if line.divisa else '',
formats['especial1'])
worksheet.write(x, 4, line.tdp if line.tdp else '',
formats['especial1'])
worksheet.write(x, 5, line.doc_partner if line.doc_partner else '',
formats['especial1'])
worksheet.write(x, 6, line.partner if line.partner else '',
formats['especial1'])
worksheet.write(x, 7, line.td_sunat if line.td_sunat else '',
formats['especial1'])
worksheet.write(
x, 8, line.nro_comprobante if line.nro_comprobante else '',
formats['especial1'])
worksheet.write(x, 9, line.cu_banco if line.cu_banco else '',
formats['especial1'])
worksheet.write(x, 10, line.saldo_mn if line.saldo_mn else '0.00',
formats['numberdos'])
worksheet.write(x, 11, line.saldo_me if line.saldo_me else '0.00',
formats['numberdos'])
worksheet.write(x, 12,
line.cero_treinta if line.cero_treinta else '0.00',
formats['numberdos'])
worksheet.write(
x, 13,
line.treinta1_sesenta if line.treinta1_sesenta else '0.00',
formats['numberdos'])
worksheet.write(
x, 14,
line.sesenta1_noventa if line.sesenta1_noventa else '0.00',
formats['numberdos'])
worksheet.write(
x, 15,
line.noventa1_ciento20 if line.noventa1_ciento20 else '0.00',
formats['numberdos'])
worksheet.write(
x, 16,
line.ciento21_ciento50 if line.ciento21_ciento50 else '0.00',
formats['numberdos'])
worksheet.write(
x, 17,
line.ciento51_ciento80 if line.ciento51_ciento80 else '0.00',
formats['numberdos'])
worksheet.write(x, 18,
line.ciento81_mas if line.ciento81_mas else '0.00',
formats['numberdos'])
x += 1
widths = [
10, 12, 8, 8, 6, 11, 40, 6, 21, 10, 16, 16, 15, 15, 15, 15, 15, 15,
15
]
worksheet = ReportBase.resize_cells(worksheet, widths)
workbook.close()
f = open(direccion + 'Analisis_Vencimientos.xlsx', 'rb')
return self.env['popup.it'].get_file(
'Analisis_Vencimientos.xlsx',
base64.encodestring(b''.join(f.readlines())))
############################################################################## # # A simple formatting example using XlsxWriter. # # This program demonstrates the indentation cell format. # # Copyright 2013, John McNamara, [email protected] # from xlsxwriter.workbook import Workbook workbook = Workbook('text_indent.xlsx') worksheet = workbook.add_worksheet() indent1 = workbook.add_format({'indent': 1}) indent2 = workbook.add_format({'indent': 2}) worksheet.set_column('A:A', 40) worksheet.write('A1', "This text is indented 1 level", indent1) worksheet.write('A2', "This text is indented 2 levels", indent2) workbook.close()
def get_xlsx_writer(self, response, **kwargs):
return Workbook(response, {'in_memory': True})
keyword_set = keyword_split
keyword_sep = ''
for each in keyword_set:
keyword_sep = keyword_sep + each + '|'
keyword_sep = keyword_sep.strip('|')
#print('keyword_sep:', keyword_sep)
if (len(keyword_sep)) == 0:
continue
#用所有关键词将整段话分割，再插入富字符串，然后捆绑颜色、关键词和后面的文本，需注意一一对应
temp_list = re.split(keyword_sep, txt)
params = []
temp_list_num = len(temp_list)
for i in range(temp_list_num):
if i != 0:
params.extend((red, keyword_set[i - 1], temp_list[i]))
else:
params.append(temp_list[i])
worksheet.write(work_row, work_col, id_data)
worksheet.write_rich_string(work_row, work_col2, *params)
work_row = work_row + 1
workbook.close()
if __name__ == '__main__':
data = pd.read_excel(r'E:\DLML\DataHandle\data\data.xlsx',
index=None) #原文本文件
data_result = Workbook(r'E:\DLML\DataHandle\data\data-result.xlsx') #标注结果
cate_data = pd.read_excel(r'E:\DLML\DataHandle\data\cate_data.xlsx',
index=None) #匹配的文本文件
model_set_colors(data, data_result, cate_data)
def do_rebuild(self):
ctas_txt = [0, 0, 0, 0, 0]
for i in self.cuentas:
ctas_txt.append(i.id)
ctas_txt = str(tuple(ctas_txt))
import io
from xlsxwriter.workbook import Workbook
output = io.BytesIO()
########### PRIMERA HOJA DE LA DATA EN TABLA
#workbook = Workbook(output, {'in_memory': True})
direccion = self.env['main.parameter'].search([])[0].dir_create_file
workbook = Workbook(direccion +
'tempo_account_move_lineflujocaja.xlsx')
worksheet = workbook.add_worksheet("Reporte Flujo Caja")
bold = workbook.add_format({'bold': True})
normal = workbook.add_format()
boldbord = workbook.add_format({'bold': True})
boldbord.set_border(style=2)
numbertres = workbook.add_format({'num_format': '0.000'})
numberdos = workbook.add_format({'num_format': '0.00'})
numberdosbold = workbook.add_format({
'num_format': '0.00',
'bold': True
})
bord = workbook.add_format()
bord.set_border(style=1)
numberdos.set_border(style=1)
numbertres.set_border(style=1)
x = 6
tam_col = [
12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12,
12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
]
tam_letra = 1.1
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
worksheet.merge_range(0, 1, 0, 13, "Flujos de Caja", bold)
worksheet.write(1, 1, "RUC", boldbord)
worksheet.write(2, 1, "Empresa", boldbord)
worksheet.write(3, 1, "Ejercicio Fiscal", boldbord)
worksheet.write(
1, 2,
self.env['res.company'].search([])[0].partner_id.nro_documento,
boldbord)
worksheet.write(2, 2,
self.env['res.company'].search([])[0].partner_id.name,
boldbord)
worksheet.write(3, 2, self.anio_fiscal.name, boldbord)
worksheet.write(5, 1, "Rubro", boldbord)
worksheet.write(5, 2, "Enero", boldbord)
worksheet.write(5, 3, "Febrero", boldbord)
worksheet.write(5, 4, "Marzo", boldbord)
worksheet.write(5, 5, "Abril", boldbord)
worksheet.write(5, 6, "Mayo", boldbord)
worksheet.write(5, 7, "Junio", boldbord)
worksheet.write(5, 8, "Julio", boldbord)
worksheet.write(5, 9, "Agosto", boldbord)
worksheet.write(5, 10, "Septiembre", boldbord)
worksheet.write(5, 11, "Octubre", boldbord)
worksheet.write(5, 12, "Noviembre", boldbord)
worksheet.write(5, 13, "Diciembre", boldbord)
meses = [
'01/', '02/', '03/', '04/', '05/', '06/', '07/', '08/', '09/',
'10/', '11/', '12/'
]
anio = str(self.anio_fiscal.name)
todos_los_elementos = [['SALDO INICIAL', '']]
y = 1
x = 7
worksheet.write(6, y, 'Saldo Inicial', normal)
for il in self.env['flujo.caja.it'].search(
[]).sorted(key=lambda r: r.orden):
todos_los_elementos.append([il.rubro, il.grupo])
worksheet.write(x, y, il.rubro, normal)
x += 1
for line in meses:
x = 6
y += 1
all_sum = 0
for elementos in todos_los_elementos:
total_line = 0
if elementos[0] == 'SALDO INICIAL':
self.env.cr.execute(
"""
select sum(total) from (
select sum(aml.debit - aml.credit) as total from
account_move am
inner join account_move_line aml on aml.move_id = am.id
LEFT join flujo_caja_it fc on fc.id = aml.flujo_caja_id
inner join account_account aa on aa.id = aml.account_id
inner join account_period ap on coalesce(ap.special,false) = coalesce(am.fecha_special,false) and am.fecha_contable >= ap.date_start and am.fecha_contable <= ap.date_stop
where aa.id in """ + str(ctas_txt) + """ and am.state = 'posted'
and periodo_num(ap.code) = periodo_num('""" + '00/' + anio + """')
union all
select sum(aml.debit - aml.credit) as total from
account_move am
inner join account_move_line aml on aml.move_id = am.id
inner join flujo_caja_it fc on fc.id = aml.flujo_caja_id
inner join account_account aa on aa.id = aml.account_id
inner join account_period ap on coalesce(ap.special,false) = coalesce(am.fecha_special,false) and am.fecha_contable >= ap.date_start and am.fecha_contable <= ap.date_stop
where aa.id in """ + str(ctas_txt) + """ and am.state = 'posted'
and periodo_num(ap.code) < periodo_num('""" + line + anio +
"""') and periodo_num(ap.code) > periodo_num('""" +
'00/' + anio + """')
)T
""")
else:
self.env.cr.execute("""
select sum(aml.debit - aml.credit) as total,fc.id, fc.codigo, fc.rubro, fc.grupo, fc.orden, sum(aml.debit - aml.credit) as total from
account_move am
inner join account_move_line aml on aml.move_id = am.id
inner join flujo_caja_it fc on fc.id = aml.flujo_caja_id
inner join account_account aa on aa.id = aml.account_id
inner join account_period ap on coalesce(ap.special,false) = coalesce(am.fecha_special,false) and am.fecha_contable >= ap.date_start and am.fecha_contable <= ap.date_stop
where aa.id in """ + str(ctas_txt) + """ and am.state = 'posted'
and ap.code = '""" + line + anio + """' and fc.rubro = '""" +
elementos[0] + """'
group by fc.id, fc.codigo, fc.rubro, fc.grupo, fc.orden
order by orden
""")
element = self.env.cr.fetchall()
for gg_ele in element:
total_line = gg_ele[0] if gg_ele[0] else 0
all_sum += total_line
worksheet.write(x, y, total_line, numberdos)
x = x + 1
worksheet.write(x, y, all_sum, numberdosbold)
worksheet.set_column('A:A', tam_col[0])
worksheet.set_column('B:B', tam_col[1])
worksheet.set_column('C:C', tam_col[2])
worksheet.set_column('D:D', tam_col[3])
worksheet.set_column('E:E', tam_col[4])
worksheet.set_column('F:F', tam_col[5])
worksheet.set_column('G:G', tam_col[6])
worksheet.set_column('H:H', tam_col[7])
worksheet.set_column('I:I', tam_col[8])
worksheet.set_column('J:J', tam_col[9])
worksheet.set_column('K:K', tam_col[10])
worksheet.set_column('L:L', tam_col[11])
worksheet.set_column('M:M', tam_col[12])
worksheet.set_column('N:N', tam_col[13])
worksheet.set_column('O:O', tam_col[14])
worksheet.set_column('P:P', tam_col[15])
worksheet.set_column('Q:Q', tam_col[16])
worksheet.set_column('R:R', tam_col[17])
worksheet.set_column('S:S', tam_col[18])
worksheet.set_column('T:T', tam_col[19])
workbook.close()
f = open(direccion + 'tempo_account_move_lineflujocaja.xlsx', 'rb')
vals = {
'output_name': 'FlujoCaja.xlsx',
'output_file': base64.encodestring(''.join(f.readlines())),
}
mod_obj = self.env['ir.model.data']
act_obj = self.env['ir.actions.act_window']
sfs_id = self.env['export.file.save'].create(vals)
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
# # Example of how use Python and XlsxWriter to generate Excel outlines and # grouping. # # Excel allows you to group rows or columns so that they can be hidden or # displayed with a single mouse click. This feature is referred to as outlines. # # Outlines can reduce complex data down to a few salient sub-totals or # summaries. # # Copyright 2013, John McNamara, [email protected] # from xlsxwriter.workbook import Workbook # Create a new workbook and add some worksheets workbook = Workbook('outline.xlsx') worksheet1 = workbook.add_worksheet('Outlined Rows') worksheet2 = workbook.add_worksheet('Collapsed Rows') worksheet3 = workbook.add_worksheet('Outline Columns') worksheet4 = workbook.add_worksheet('Outline levels') # Add a general format bold = workbook.add_format({'bold': 1}) ############################################################################### # # Example 1: A worksheet with outlined rows. It also includes SUBTOTAL() # functions so that it looks like the type of automatic outlines that are # generated when you use the Excel Data->SubTotals menu item. # # For outlines the important parameters are 'level' and 'hidden'. Rows with
def report_to_xlsx(self, path):
workbook = Workbook(path)
form = ExcelFormat(workbook)
summarysheet = workbook.add_worksheet('Summary')
row = 0
col = 0
summarysheet.merge_range(row, col, row+3, col, '', form.cell)
col += 1
for platform in TEST_PLATFORM:
if not platform in self.summary:
continue
row += 1
platform_col = col
for target in self.summary[platform]:
row += 1
target_col = col
for tc_type in TESTCASE_TYPES + ('Total',):
row += 1
tc_type_col = col
for result in TC_RESULT + ('Total',):
summarysheet.write(row, col, result, form.result_title[result])
col += 1
row -= 1
summarysheet.merge_range(row, tc_type_col, row, col-1, tc_type, form.title)
row -= 1
summarysheet.merge_range(row, target_col, row, col-1, target, form.title)
row -= 1
summarysheet.merge_range(row, platform_col, row, col-1, platform, form.title)
total_col = col
for result in TC_RESULT + ('Total','Pass Rate',):
summarysheet.write(row+3, col, result, form.result_title[result])
col += 1
summarysheet.merge_range(row, total_col, row+2, col-1, "Total", form.total)
row += 4
col = 0
for module in TESTSUITE_MODULES :
col = 0
summarysheet.write(row, col, module, form.title)
col += 1
module_total_txt = dict()
for result in TC_RESULT + ('Total',):
module_total_txt[result] = '=SUM('
for platform in TEST_PLATFORM:
if not platform in self.summary:
continue
for target in self.summary[platform]:
for tc_type in TESTCASE_TYPES:
for result in TC_RESULT:
result_sum = 0
for build_type in self.summary[platform][target]:
for transport in self.summary[platform][target][build_type]:
for network in self.summary[platform][target][build_type][transport]:
result_sum += self.summary[platform][target][build_type][transport][network][tc_type][module][result]
summarysheet.write(row, col, result_sum, form.cell)
col += 1
total_txt = '=SUM(%s:%s)' % (get_cell_name(col - len(TC_RESULT), row), get_cell_name(col - 1, row))
summarysheet.write(row, col, total_txt, form.cell)
col += 1
for result in TC_RESULT + ('Total',):
total_txt = '=SUM(%s,%s)' % (get_cell_name(col - (len(TC_RESULT) + 1)*2, row), get_cell_name(col - (len(TC_RESULT) + 1), row))
summarysheet.write(row, col, total_txt, form.cell)
module_total_txt[result] += '%s,' % get_cell_name(col, row)
col += 1
for result in TC_RESULT + ('Total',):
module_total_txt[result] += ')'
summarysheet.write(row, col, module_total_txt[result], form.cell)
col += 1
total_txt = '=IF(%s=0,"",ROUND(%s/%s*100, 2))' % (get_cell_name(col - 1, row), get_cell_name(col - 3, row), get_cell_name(col - 1, row))
summarysheet.write(row, col, total_txt, form.cell)
row += 1
col = 0
summarysheet.write(row, col, 'Total', form.total)
col += 1
for platform in TEST_PLATFORM:
if not platform in self.summary:
continue
for target in self.summary[platform]:
for tc_type in TESTCASE_TYPES + ('Total',):
for result in TC_RESULT + ('Total',):
total_txt = '=SUM(%s:%s)' % (get_cell_name(col, row - len(TESTSUITE_MODULES)), get_cell_name(col, row - 1))
summarysheet.write(row, col, total_txt, form.total_no)
col += 1
for result in TC_RESULT + ('Total',):
total_txt = '=SUM(%s:%s)' % (get_cell_name(col, row - len(TESTSUITE_MODULES)), get_cell_name(col, row - 1))
summarysheet.write(row, col, total_txt, form.total_no)
col += 1
total_txt = '=IF(%s=0,"",ROUND(%s/%s*100, 2))' % (get_cell_name(col - 1, row), get_cell_name(col - 3, row), get_cell_name(col - 1, row))
summarysheet.write(row, col, total_txt, form.total_no)
def write_pass_result(row, col, testcase, testspec, sheet, platform, target, transport, network):
tc_col = col
tc_row = row
col += 2
index = 0
while(index < len(testcase.runresult)):
sheet.write(row, col, testcase.runresult[index].result, form.cell)
sheet.write(row, col + 1, testcase.runresult[index].runtype, form.cell)
sheet.write(row, col + 2, testcase.runresult[index].runtime, form.cell)
sheet.write(row, col + 3, testcase.runresult[index].fail_msg, form.cell_wrap)
temp_log = get_log_content_or_filename(testcase.runresult[index].test_log)
sheet.write(row, col + 4, temp_log, form.cell_wrap)
index += 1
row +=1
col = tc_col
merge_cell(sheet, tc_row, col, row - 1, col, testcase.name, form.cell)
col += 1
merge_cell(sheet, tc_row, col, row - 1, col,
"%d/%d" % (testcase.fail, testcase.fail + testcase.success), form.cell)
col += 6
if testcase.name in testspec:
spec = testspec[testcase.name]
for key, title in sorted(TAG_DIC.items(), key=operator.itemgetter(1)):
if (title[0] < 5):
continue;
merge_cell(sheet, tc_row, col, row -1, col, spec.__dict__[key], form.cell_wrap)
col += 1
col = tc_col
return row, col
def write_fail_result(row, col, testcase, testspec, sheet, platform, target, transport, network):
tc_col = col
tc_row = row
col += 2
index = 0
while(index < len(testcase.runresult)):
sheet.write(row, col, testcase.runresult[index].result, form.cell)
sheet.write(row, col + 1, testcase.runresult[index].runtype, form.cell)
sheet.write(row, col + 2, testcase.runresult[index].runtime, form.cell)
sheet.write(row, col + 3, testcase.runresult[index].fail_msg, form.cell_wrap)
temp_log = get_log_content_or_filename(testcase.runresult[index].test_log)
sheet.write(row, col + 4, temp_log, form.cell_wrap)
index += 1
row +=1
col = tc_col
merge_cell(sheet, tc_row, col, row - 1, col, testcase.name, form.cell)
col += 1
merge_cell(sheet, tc_row, col, row - 1, col,
"%d/%d" % (testcase.fail, testcase.fail + testcase.success), form.cell)
col += 6
if testcase.name in testspec:
spec = testspec[testcase.name]
for key, title in sorted(TAG_DIC.items(), key=operator.itemgetter(1)):
if (title[0] < 5):
continue;
merge_cell(sheet, tc_row, col, row -1, col, spec.__dict__[key], form.cell_wrap)
col += 1
col = tc_col
return row, col
def write_defect_result(row, col, testcase, testspec, sheet, platform, target, transport, network):
tc_col = col
tc_row = row
col += 2
index = 0
while(index < len(testcase.runresult)):
sheet.write(row, col, testcase.runresult[index].result, form.cell)
sheet.write(row, col + 1, testcase.runresult[index].runtype, form.cell)
sheet.write(row, col + 2, testcase.runresult[index].runtime, form.cell)
index += 1
row +=1
col = tc_col
merge_cell(sheet, tc_row, col, row - 1, col, testcase.name, form.cell)
col += 1
merge_cell(sheet, tc_row, col, row - 1, col,
"%d/%d" % (testcase.fail, testcase.fail + testcase.success), form.cell)
col += 4
merge_cell(sheet, tc_row, col, row-1, col, '', form.cell_wrap)
col += 1
merge_cell(sheet, tc_row, col, row-1, col, '', form.cell)
col += 1
test_target = ''
if testcase.name in list(testspec):
test_target = testspec[testcase.name].target
description = '[Device: %s %s]\n' %(platform, target)\
+ '[TC: %s]\n' % (testcase.name)\
+ '[Target: %s]\n\n' %(test_target) \
+ DEFECT_DESCRIPTION
merge_cell(sheet, tc_row, col, row-1, col, description, form.cell_wrap)
col = tc_col
return row, col
def get_log_content_or_filename(log):
if len(log) > 10000:
begin_index = log.find('Log File Name:') + len('Log File Name:')
end_index = log.find('Content:')
log = log[begin_index:end_index].strip()
return log
passsheet = workbook.add_worksheet('PassTC')
self.report_result(passsheet, form, RESULT_TITLE, write_pass_result, self.passtc)
failsheet = workbook.add_worksheet('FailedTC')
self.report_result(failsheet, form, RESULT_TITLE, write_fail_result, self.failtc)
defectsheet = workbook.add_worksheet('DefectReport')
self.report_result(defectsheet, form, DEFECT_TITLE, write_defect_result, self.failtc)
workbook.close()
# This source code or any portion thereof must not be
# reproduced or used in any manner whatsoever.
# ======================================================================
# Import Module
import sys
import os
import glob
import csv
from xlsxwriter.workbook import Workbook
reload(sys)
sys.setdefaultencoding('utf-8')
for csvfile in glob.glob(os.path.join('.', '<CSV_FILE>')):
workbook = Workbook('<EXCEL_FILE>')
worksheet = workbook.add_worksheet()
# # 書式設定を変更する場合はworksheet.write()の時に使用するためここに定義
# format_num = workbook.add_format()
# format_num.set_num_format('#,##0')
with open(csvfile, 'rb') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
# worksheet.write(r, c, col)
# 列ごとに書式設定するときはここで分岐
if r == 0:
worksheet.write(
r, c, col,
workbook.add_format({
'bg_color': '#00CC00',
import cv2
from numpy import *
from xlsxwriter.workbook import Workbook
ROUND = 7.
img = cv2.imread('123.jpg')
y, x, ch = img.shape
img = cv2.resize(img, (y / 3, x / 3))
y, x, ch = img.shape
t = Workbook('pic.xlsx')
ts = t.add_worksheet()
ts.set_default_row(height=1.8)
ts.set_column(0, 5000, .2)
s = [(i, j) for i in xrange(y) for j in xrange(x)]
for i, j in s:
color = rint(img[i, j] / ROUND) * ROUND
color = '#%02X%02X%02X' % tuple(color.tolist()[::-1])
fmat = t.add_format({'bg_color': color})
ts.write(i, j, '', fmat)
if j == 0: print i, '/', y
t.close()
import sqlite3
from xlsxwriter.workbook import Workbook
workbook = Workbook('Список студентов.xlsx') # Открытие и запись в файл
worksheet = workbook.add_worksheet()
conn = sqlite3.connect('students.db') # Подключение к БД
c = conn.cursor()
c.execute("SELECT * FROM students")
print('Выберите направление работы:')
vib = int(
input('''1 - Научное студенческое общество;
2 - Социальная поддержка;
3 - Спорт;
4 - Культура и творчество;
5 - Штаб трудовых дел. \n''')) # Выбор вида работ
if vib == 1:
mysel = c.execute(
"SELECT * FROM students WHERE direction_of_work='Научное студенческое общество'"
)
elif vib == 2:
mysel = c.execute(
"SELECT * FROM students WHERE direction_of_work='Социальная поддержка'"
)
elif vib == 3:
mysel = c.execute("SELECT * FROM students WHERE direction_of_work='Спорт'")
class reportGenerate():
def __init__(self, fileName, data):
self.wk = Workbook(fileName)
self.wk.set_properties({
'title':
'This is a Tesla Loginfo',
'subject':
'With document properties',
'author':
'Devin Xu',
'manager':
'Levy Li',
'company':
'NVIDIA',
'category':
'Log parser spreadsheets',
'comments':
'Created with Python and XlsxWriter',
})
self.loginfo = data
self.defineTitle = [
"filePath",
"^SN",
"SerialNumber",
"ScanSN",
"ecid",
"IRQ",
"KernelDriver",
"ModsIRQ",
"ateSpeedo",
"VBIOSVersion",
"AteIddq",
"MemorySize",
"T148Failure",
"MarketingName",
"MemoryVendor",
"MemoryPN",
"ProductPN",
"Station",
"BuildDate",
"ModsVersion",
"699PN",
"Project",
"exitErroCode",
"LastErrorCode",
"failuresite",
"site\d+.+",
"fused*",
"failurePartition",
"pageRetirements",
"Nautilus Fatorcy Code",
"Failure Details",
"Failure Code",
".+TestTime",
"EC139FailTime"
"modsStartTime",
"modsEndTime",
"TestTime",
"HostName",
"FLATID",
"E.+SN",
"DiagVer",
"StartTestTime",
"EndTesttime",
"ErrorCode",
"lastEnterTest",
"lastEnterTestTime",
# "lastStation",
"reboot",
".+Gflops",
# ,"INPUT.+"
# ,"OUTPUT_NVVDD"
# ,"AdcError*"
"_MAX$",
".+RawData"
#,"_Min",".+lastMax",".+Delta"
,
"_R$",
"hbmIndex.+",
"GPU_",
".+print",
"local*",
"host*",
"PEXLanesXStatus",
"PEXLanesYStatus",
"NVLink0X",
"NVLink0Y",
"NVLink1X",
"NVLink1Y",
"NVLink2X",
"NVLink2Y",
"NVLink3X",
"NVLink3Y",
"NVLink4X",
"NVLink4Y",
"NVLink5X",
"NVLink5Y"
]
self.orderTitleList = []
def addNVLinkTittleList(self):
nvlinkList = []
nvlinkList.append("NVLink0$X_STATUS")
nvlinkList.append("NVLink0$Y_STATUS")
nvlinkList.append("NVLink1$X_STATUS")
nvlinkList.append("NVLink1$Y_STATUS")
nvlinkList.append("NVLink2$X_STATUS")
nvlinkList.append("NVLink2$Y_STATUS")
nvlinkList.append("NVLink3$X_STATUS")
nvlinkList.append("NVLink3$Y_STATUS")
nvlinkList.append("NVLink4$X_STATUS")
nvlinkList.append("NVLink4$Y_STATUS")
nvlinkList.append("NVLink5$X_STATUS")
nvlinkList.append("NVLink5$Y_STATUS")
for eachTittle in nvlinkList:
if eachTittle not in self.titleList:
self.titleList.append(eachTittle)
def addPexTittleList(self):
pexList = []
pexList.append("local_Correctable")
pexList.append("host_Correctable")
pexList.append("local_none_fatal")
pexList.append("host_none_fatal")
pexList.append("local_fatal")
pexList.append("host_fatal")
pexList.append("local_lineErrors")
pexList.append("local_CRCErrors")
pexList.append("local_NAKs_received")
pexList.append("local_NAKs_received")
pexList.append("local_failedL0sExits")
pexList.append("local_NAKs_sent")
for eachTittle in pexList:
if eachTittle not in self.titleList:
self.titleList.append(eachTittle)
#print(self.titleList)
return pexList
def addPexErrorInfo(self, data, eachSubStationInfo):
pexList = self.addPexTittleList()
for eachPex in pexList:
data[
eachPex] = "" if eachPex not in eachSubStationInfo else eachSubStationInfo[
eachPex]
def addDataPerTittleList(self):
pass
def getTitleList(self):
'return the unordered title list'
titleSet = set()
self.orderTitleList = []
for eachlogInfo in self.loginfo:
col = 0
#if "FunctionalSpec" in eachlogInfo.logInfo and \
uniqueData = eachlogInfo.uniqueData
fatoryInfo = eachlogInfo.factoryInfo
#print(uniqueData)
eachSubStationData = eachlogInfo.dataForeachSubstation
for eachStation in uniqueData:
titleSet.update(uniqueData[eachStation])
titleSet.update(eachSubStationData)
titleSet.update(fatoryInfo)
titleSet.update(["Station"])
titleSet.update(["TestTime"])
titleSet.update(["LastErrorCode"])
for eachOrderTitle in self.defineTitle:
tempList = []
for eachGetTitle in list(titleSet):
#logging.info(eachOrderTitle)
#logging.info(eachGetTitle)
result = re.search(eachOrderTitle, eachGetTitle, re.IGNORECASE)
if result is not None and eachGetTitle not in self.orderTitleList:
tempList.append(eachGetTitle)
#logging.info("add %s to title list"%(sorted(tempList)))
self.orderTitleList = self.orderTitleList + sorted(tempList)
logging.info(self.orderTitleList)
return list(self.orderTitleList)
def ttoAnalysis(self, run_on_error):
failureListByStation = {}
loginfoSheet = self.wk.add_worksheet("TTO_Failure Details")
if run_on_error == False:
for eachLoginfo in self.loginfo:
logData = eachLoginfo.uniqueData
fileName = eachLoginfo.factoryInfo["filePath"]
for eachStation in logData:
if "List" in eachStation:
continue
failureInfo = None if "Failure Details" not in logData[
eachStation] else logData[eachStation][
"Failure Details"]
if failureInfo is None:
continue
failureList = failureInfo.split("$$")
#logging.info(failureList)
for eachFailureTest in failureList:
failureSplit = eachFailureTest.split('$')
#logging.info(fileName)
#logging.info(eachFailureTest)
#logging.info(failureSplit)
station = failureSplit[0]
testName = "NA" if len(
failureSplit) < 2 else failureSplit[1]
testNumber = "NA" if len(
failureSplit) < 3 else failureSplit[2]
testID = "NA" if len(
failureSplit) < 4 else failureSplit[3]
errorCode = "NA" if len(
failureSplit) < 5 else failureSplit[4]
failreDict = {
"testName": testName,
"testNumber": testNumber,
"testID": testID,
"errorCode": errorCode,
"faileName": fileName
}
if station not in failureListByStation:
failureListByStation[station] = [failreDict]
else:
failureListByStation[station].append(failreDict)
row = 0
for eachStation in failureListByStation:
logging.info(eachStation)
col = 0 ###
loginfoSheet.write_row(0, 0, [
"Station", "Test", "Test Number", "Test ID", "error Code",
"FileName"
])
for eachFailureDict in failureListByStation[eachStation]:
logging.info(row)
row += 1
col = 0
loginfoSheet.write(row, col, eachStation)
col += 1
loginfoSheet.write(
row, col, eachFailureDict["testName"] +
eachFailureDict["testNumber"] +
eachFailureDict["testID"])
col += 1
loginfoSheet.write(row, col, eachFailureDict["testNumber"])
col += 1
loginfoSheet.write(
row, col, eachFailureDict["testID"].replace("NA", ""))
col += 1
loginfoSheet.write(row, col, eachFailureDict["errorCode"])
col += 1
#logging.info(eachFailureDict["faileName"])
loginfoSheet.write(row, col, eachFailureDict["faileName"])
def getDataFromLoginfo(self, logInfo, key):
value = 0
if "pwr".upper() in key.upper() or "temp".upper() in key.upper():
data = self.getDataFromStr(logInfo[key])
value = max(data)
else:
value = logInfo[key]
return value
def failureByECID(self, sheetName):
self.defineTitle = [
"^SN", "SerialNumber", "ecid", "IRQ", "ModsIRQ", "ateSpeedo",
"VBIOSVersion", "ModsVersion", "AteIddq", "BuildDate", "699PN",
"Project", "exitErroCode", "PSHCBEU", "failuresite", "site.+",
"failurePartition", "fused*", "pageRetirements", "Failure Code"
]
row = 0
col = 0
self.getTitleList()
loginfoSheet = self.wk.add_worksheet(sheetName)
loginfoSheet.write_row(row, col, self.orderTitleList)
ecidData = {}
for eachlogInfo in self.loginfo:
col = 0
uniqueData = eachlogInfo.uniqueData
factoryInfo = eachlogInfo.factoryInfo
ecidInfo = eachlogInfo.databyECID
#logging.info(uniqueData)
eachSubStationData = eachlogInfo.dataForeachSubstation
# if
#logging.debug(eachSubStationData)
for eachStation in uniqueData:
data = []
#logging.info(eachStation)
#logging.info(uniqueData[eachStation])
if "List" in eachStation or "ecid" not in uniqueData[
eachStation]:
continue
ecid = uniqueData[eachStation]["ecid"]
if uniqueData[eachStation]["ecid"] not in ecidData:
ecidData[ecid] = {}
for eachTitle in self.orderTitleList:
if eachTitle in uniqueData[
eachStation] and "site" in eachTitle:
if "site" in eachTitle:
if eachTitle not in ecidData[ecid]:
ecidData[ecid][eachTitle] = uniqueData[
eachStation][eachTitle]
else:
for eachSitefailInfo in uniqueData[
eachStation][eachTitle].split(","):
ecidData[ecid][eachTitle] = ecidData[ecid][
eachTitle] if eachSitefailInfo in ecidData[
ecid][eachTitle] and len(
ecidData[ecid][eachTitle]
) > 250 else ecidData[ecid][
eachTitle] + ", " + eachSitefailInfo
#data.append(uniqueData[eachStation][eachTitle])
#elif eachTitle in eachSubStationData:
#data.append(eachSubStationData[eachStation][eachTitle])
elif eachTitle in uniqueData[
eachStation] and "fuse" in eachTitle:
if eachTitle not in ecidData[ecid]:
ecidData[ecid][
eachTitle] = uniqueData[eachStation][
eachTitle] if eachTitle not in ecidData[
ecid] else ecidData[ecid][
eachTitle] + ", " + uniqueData[
eachStation][eachTitle]
else:
for eachfuse in uniqueData[eachStation][
eachTitle].split(","):
if eachfuse.strip(
) not in ecidData[ecid][eachTitle]:
ecidData[ecid][eachTitle] = ecidData[ecid][
eachTitle] + ", " + eachfuse.strip()
elif eachTitle in uniqueData[
eachStation] and "page" in eachTitle:
if eachTitle not in ecidData[ecid]:
ecidData[ecid][eachTitle] = int(
uniqueData[eachStation][eachTitle])
else:
ecidData[ecid][eachTitle]=int(ecidData[ecid][eachTitle]) if int(uniqueData[eachStation][eachTitle])<int(ecidData[ecid][eachTitle]) \
else int(uniqueData[eachStation][eachTitle])
elif eachTitle in uniqueData[eachStation] and (
"failurePartition" in eachTitle
or "PSHCBEU" in eachTitle):
if eachTitle not in ecidData[ecid]:
ecidData[ecid][eachTitle] = uniqueData[
eachStation][eachTitle]
else:
ecidData[ecid][eachTitle]=ecidData[ecid][eachTitle] if uniqueData[eachStation][eachTitle] in ecidData[ecid][eachTitle] and len(ecidData[ecid][eachTitle])>250\
else ecidData[ecid][eachTitle]+","+uniqueData[eachStation][eachTitle]
elif eachTitle in uniqueData[
eachStation] and "exit" in eachTitle and uniqueData[
eachStation][eachTitle] is not None:
if eachTitle not in ecidData[ecid]:
ecidData[ecid][eachTitle] = uniqueData[
eachStation][eachTitle]
elif ecidData[ecid][eachTitle] is not None:
ecidData[ecid][eachTitle]=ecidData[ecid][eachTitle] if uniqueData[eachStation][eachTitle] in ecidData[ecid][eachTitle] \
else ecidData[ecid][eachTitle]+"$"+uniqueData[eachStation][eachTitle]
elif eachTitle in factoryInfo:
ecidData[ecid][eachTitle] = factoryInfo[eachTitle]
elif eachTitle in uniqueData[eachStation]:
ecidData[ecid][eachTitle] = uniqueData[eachStation][
eachTitle]
#data.append(factoryInfo[eachTitle])
elif eachTitle == "Station":
data.append(eachStation)
fulldata = []
#for title in self.orderTitleList:
col = 0
row = 0
loginfoSheet.write_row(row, col, self.orderTitleList)
for eachECID in ecidData:
eachdata = []
data = []
row = row + 1
for title in self.orderTitleList:
if title == "ecid":
data.append(eachECID)
elif title in ecidData[eachECID]:
data.append(ecidData[eachECID][title])
else:
data.append("")
loginfoSheet.write_row(row, col, data)
#logging.info(ecidData)
# self.wk.close()
def failureAnalysis(self, sheetName):
row = 0
col = 0
self.getTitleList()
loginfoSheet = self.wk.add_worksheet(sheetName)
loginfoSheet.write_row(row, col, self.orderTitleList)
for eachlogInfo in self.loginfo:
col = 0
uniqueData = eachlogInfo.uniqueData
factoryInfo = eachlogInfo.factoryInfo
#logging.debug(uniqueData)
eachSubStationData = eachlogInfo.dataForeachSubstation
#logging.debug(eachSubStationData)
for eachStation in uniqueData:
data = []
#logging.info(eachStation)
#logging.info(uniqueData[eachStation])
if "List" in eachStation:
continue
for eachTitle in self.orderTitleList:
if eachTitle in uniqueData[eachStation]:
data.append(uniqueData[eachStation][eachTitle])
#elif eachTitle in eachSubStationData:
#data.append(eachSubStationData[eachStation][eachTitle])
elif eachTitle in factoryInfo:
data.append(factoryInfo[eachTitle])
elif eachTitle == "Station":
data.append(eachStation)
elif eachTitle == "TestTime":
if "modsEndTime" in uniqueData[eachStation]:
data.append(uniqueData[eachStation]["modsEndTime"])
elif "bgPrintCount" in uniqueData[eachStation]:
data.append(
uniqueData[eachStation]["bgPrintCount"] * 60)
print(uniqueData[eachStation]["bgPrintCount"])
elif "lastEnterTestTime" in uniqueData[
eachStation] and "modsStartTime" in uniqueData[
eachStation]:
start = datetime.datetime.strptime(
uniqueData[eachStation]["modsStartTime"],
"%a %b %d %H:%M:%S %Y")
end = datetime.datetime.strptime(
uniqueData[eachStation]["lastEnterTestTime"],
"%a %b %d %H:%M:%S %Y")
testTime = (end - start).seconds + (
end - start).days * 24 * 60 * 60
#print(start,testTime,testTime)
data.append(testTime)
pass
else:
data.append(0)
elif eachTitle == "LastErrorCode": # and "SerialNumber" in uniqueData[eachStation]:
lastErrorCode = self.getLastErrorCode(
self.loginfo, uniqueData[eachStation], eachStation)
#data.append(lastErrorCode)
data.append("NA")
#elif eachTitle=="exitErroCode":
# data.append(uniqueData[eachStation][eachTitle] if not ("ModsDrvBreakPoint" not in uniqueData[eachStation] or uniqueData[eachStation]["ModsDrvBreakPoint"]<5) else "ModsDriveBreakPoint")
else:
data.append(None)
#print()
row = row + 1
#logging.debug("write the %d row "%row)
#logging.debug(data)
#logging.debug(row)
loginfoSheet.write_row(row, 0, data)
#self.wk.save()
# self.wk.close()
def closeWorkbook(self):
#self.wk.save()
self.wk.close()
def getLastErrorCode(self, loginfo, dataForStation, station):
#logging.info(station)
#logging.info(station)
lastErrorCode = None if "exitErroCode" not in dataForStation else dataForStation[
"exitErroCode"]
logging.info("First %s" % lastErrorCode)
if "SerialNumber" not in dataForStation:
return lastErrorCode
sn = dataForStation["SerialNumber"]
lastTestTime = None
if "lastEnterTestTime" in dataForStation:
lastTestTime = datetime.datetime.strptime(
dataForStation["lastEnterTestTime"], "%a %b %d %H:%M:%S %Y")
else:
return lastErrorCode
for eachlogInfo in loginfo:
if station not in eachlogInfo.uniqueData:
continue
data = eachlogInfo.uniqueData[station]
if "lastEnterTestTime" in data and "exitErroCode" in data and "SerialNumber" in data and sn == data[
"SerialNumber"]:
#logging.info()
testTime = datetime.datetime.strptime(
data["lastEnterTestTime"], "%a %b %d %H:%M:%S %Y")
if testTime > lastTestTime:
lastErrorCode = data["exitErroCode"]
lastTestTime = testTime
#logging.info("last%s"%lastErrorCode)
return lastErrorCode
#pass
def writeLoninfo(self, sheetName):
row = 0
col = 0
self.getTitleList()
loginfoSheet = self.wk.add_worksheet("logInfo")
loginfoSheet.write_row(row, col, self.orderTitleList)
row += 1
#print(len(self.loginfo))
count = 0
for eachlogInfo in self.loginfo:
#print(count)
count = count + 1
col = 0
#if "FunctionalSpec" in eachlogInfo.logInfo and \
uniqueData = eachlogInfo.uniqueData
#print(uniqueData)
eachSubStationData = eachlogInfo.dataForeachSubstation
#logging.debug(eachSubStationData)
logging.debug(eachlogInfo.uniqueData["filePath"])
for eachSpec in eachSubStationData:
data = {}
dataForEachStation = eachSubStationData[eachSpec]
if True:
col = 0
#print(eachSpec)
data["filePath"] = eachlogInfo.uniqueData["filePath"]
#logging.debug(dataForEachStation)
data[
"SN"] = None if "SN" not in eachlogInfo.uniqueData else eachlogInfo.uniqueData[
"SN"]
data[
"ecid"] = None if "ecid" not in eachlogInfo.uniqueData else eachlogInfo.uniqueData[
"ecid"]
data[
"ateSpeedo"] = None if "ateSpeedo" not in eachlogInfo.uniqueData else eachlogInfo.uniqueData[
"ateSpeedo"]
#"modsStartTime","FLATID","lastEnterTest","reboot",
data[
"modsStartTime"] = None if "modsStartTime" not in eachlogInfo.uniqueData else eachlogInfo.uniqueData[
"modsStartTime"]
data[
"FLATID"] = None if "FLATID" not in eachlogInfo.uniqueData else eachlogInfo.uniqueData[
"FLATID"]
data[
"lastEnterTest"] = None if "lastEnterTest" not in eachlogInfo.uniqueData else eachlogInfo.uniqueData[
"lastEnterTest"]
data[
"reboot"] = None if "reboot" not in eachlogInfo.uniqueData else eachlogInfo.uniqueData[
"reboot"]
stationName = None if "Station" not in data else data[
"Station"]
data[
"initNvvdd"] = None if "initNvvdd" not in eachlogInfo.uniqueData else eachlogInfo.uniqueData[
"initNvvdd"]
data[
"exitErroCode"] = "pass" if "exitErroCode" not in eachlogInfo.uniqueData else eachlogInfo.uniqueData[
"exitErroCode"]
data["LastErroCode"] = self.getLastErrorCode(
self.loginfo, data["SN"], stationName)
#data["subStation"]=None if "subStation" not in dataForEachStation else dataForEachStation["subStation"]
#print(data["subStation"])
data["subStation"] = eachSpec
data[
"Failure Code"] = "pass" if "ErrorCode" not in dataForEachStation else dataForEachStation[
"ErrorCode"]
data[
"pstate"] = None if "pstate" not in dataForEachStation else dataForEachStation[
"pstate"]
data[
"nvvdd"] = None if "nvvdd" not in dataForEachStation else dataForEachStation[
"nvvdd"]
data[
"pexLaneX"] = None if "pexLane$X_STATUS" not in dataForEachStation else dataForEachStation[
"pexLane$X_STATUS"]
data[
"pexLaneY"] = None if "pexLane$Y_STATUS" not in dataForEachStation else dataForEachStation[
"pexLane$Y_STATUS"]
data[
"NVLink0$X_STATUS"] = None if "NVLink0$X_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink0$X_STATUS"]
data[
"NVLink0$Y_STATUS"] = None if "NVLink0$Y_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink0$Y_STATUS"]
data[
"NVLink1$X_STATUS"] = None if "NVLink1$X_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink1$X_STATUS"]
data[
"NVLink1$Y_STATUS"] = None if "NVLink1$Y_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink1$Y_STATUS"]
data[
"NVLink2$X_STATUS"] = None if "NVLink2$X_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink2$X_STATUS"]
data[
"NVLink2$Y_STATUS"] = None if "NVLink2$Y_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink2$Y_STATUS"]
data[
"NVLink3$X_STATUS"] = None if "NVLink3$X_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink3$X_STATUS"]
data[
"NVLink3$Y_STATUS"] = None if "NVLink3$Y_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink3$Y_STATUS"]
data[
"NVLink4$X_STATUS"] = None if "NVLink4$X_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink4$X_STATUS"]
data[
"NVLink4$Y_STATUS"] = None if "NVLink4$Y_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink4$Y_STATUS"]
data[
"NVLink5$X_STATUS"] = None if "NVLink5$X_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink5$X_STATUS"]
data[
"NVLink5$Y_STATUS"] = None if "NVLink5$Y_STATUS" not in dataForEachStation else dataForEachStation[
"NVLink5$Y_STATUS"]
#self.addPexErrorInfo(data,dataForEachStation)
#print( data["pexLane"])
#data["gpuMax"]=mean(dataForEachStation["gpuMax"].split(" "))
if "gpuMax" in dataForEachStation:
try:
results = [
float(i) for i in
dataForEachStation["gpuMax"].split(" ")
]
data["Max gpuTemp"] = round(mean(results), 1)
data["Average gpuTemp"] = round(max(results), 1)
results = [
float(i) for i in
dataForEachStation["hbm2_COMBINED_MAX"].split(
" ")
]
data["Average hbm2_COMBINED_MAX"] = round(
mean(results), 1)
data["Max hbm2_COMBINED_MAX"] = round(
max(results), 1)
#logging.debug(dataForEachStation["hbm0Temp"])
results = [
float(i) for i in
dataForEachStation["hbm0Temp"].split(" ")
]
data["Average hbm0Temp"] = round(mean(results), 1)
data["Max hbm0Temp"] = round(max(results), 1)
results = [
float(i) for i in
dataForEachStation["hbm1Temp"].split(" ")
]
data["Average hbm1Temp"] = round(mean(results), 1)
data["Max hbm1Temp"] = round(max(results), 1)
results = [
float(i) for i in
dataForEachStation["hbm2Temp"].split(" ")
]
data["Average hbm2Temp"] = round(mean(results), 1)
data["Max hbm2Temp"] = round(max(results), 1)
results = [
float(i) for i in
dataForEachStation["hbm3Temp"].split(" ")
]
data["Average hbm3Temp"] = round(mean(results), 1)
data["Max hbm3Temp"] = round(max(results), 1)
results = [
float(i) for i in
dataForEachStation["totalPowerReg"].split(" ")
]
data["Average totalPower"] = round(
mean(results), 1)
data["Max totalPower"] = round(max(results), 1)
except ValueError as e:
logging.debug(e)
#logging.debug(data)
for eachCol in self.titleList:
#print(len(self.titleList))
loginfoSheet.write(
row, col,
None if eachCol not in data else data[eachCol])
col += 1
row += 1
self.wk.close()
def writeHistogram(self, sheetName, col=0, row=0):
pass
station = ["fct", "eft", "bi", "fpt", "sbt"]
parameter = [
"INPUT_NVVDD_Max", "INPUT_TOTAL_BOARD_Max", "OUTPUT_NVVDD_Max",
"INPUT_PWR_SRC_PP_Max", "gpuMaxTemp_Max", "HBM2_COMBINED_MAX_Max"
]
parameterDict = {}
row = 0
col = 0
loginfoSheet = self.wk.add_worksheet("logInfo")
loginfoSheet.write_row(row, col, self.orderTitleList)
#print(len(self.loginfo))
count = 0
for eachlogInfo in self.loginfo:
count = count + 1
col = 0
uniqueData = eachlogInfo.uniqueData
#print(uniqueData)
eachSubStationData = eachlogInfo.dataForeachSubstation
#logging.debug("Histogram")
#logging.debug(eachlogInfo.uniqueData["filePath"])
for eachSpec in uniqueData:
if "List" in eachSpec:
continue
#logging.info(uniqueData[eachSpec])
for parameterName in parameter:
logStationName = eachSpec
if logStationName not in parameterDict:
parameterDict[logStationName] = {}
#logging.info("add %s"%(logStationName))
if parameterName in uniqueData[eachSpec]:
if parameterName not in parameterDict[logStationName]:
parameterDict[logStationName][parameterName] = [
uniqueData[eachSpec][parameterName]
]
else:
parameterDict[logStationName][
parameterName].append(
uniqueData[eachSpec][parameterName])
logging.info(parameterDict)
for station, parameterDict in parameterDict.items():
#logging.info(station)
#logging.info(parameterDict)
sheetName = station + " " + "Hist"
sheetName = sheetName if len(sheetName) < 30 else sheetName[0:29]
loginfoSheet = self.wk.add_worksheet(sheetName)
cell_format = self.wk.add_format()
cell_format.set_border()
#cell_format.set_bor
row = 0
col = 0
for orderParameterName in parameter:
for parameterName, parameterValues in parameterDict.items():
if parameterName == orderParameterName:
#logging.info(parameterValues)
hist = getDistribution(parameterValues)
#logging.info(hist[0])
#logging.info(hist[1])
loginfoSheet.write(row, 0,
parameterName + " Histogram")
row = row + 1
loginfoSheet.write(row, 0, parameterName, cell_format)
loginfoSheet.write_row(row, 1, hist[1], cell_format)
row = row + 1
loginfoSheet.write(row, 0, "Count", cell_format)
loginfoSheet.write_row(row, 1, hist[0], cell_format)
row = row + 1
loginfoSheet.write_row(row, 0, [
"Min", "Max", "Average", "Std Value", "-3.5 sigma",
"+3.5 Sigma"
], cell_format)
row = row + 1
loginfoSheet.write_row(row, 0, [
min(parameterValues),
max(parameterValues),
mean(parameterValues),
statistics.pstdev(parameterValues),
mean(parameterValues) -
3.5 * statistics.pstdev(parameterValues),
mean(parameterValues) +
3.5 * statistics.pstdev(parameterValues)
], cell_format)
if len(hist[0]) + 1 <= 26:
colName = str(chr(96 + len(hist[0]) + 1))
else:
colName = str(
chr(96 + int(len(hist[0]) / 26))) + str(
chr(96 + len(hist[0]) % 26 + 1))
#logging.info(colName)
chart = self.wk.add_chart({'type': 'column'})
colName = colName.upper()
#chart.add_series
logging.info(
'=\'{sheetname}\'!$B${row}:${col}${row}'.format(
sheetname=sheetName,
col=colName,
row=row + 1 - 2,
end=len(hist[0]) + 1))
logging.info(
'=\'{sheetname}\'!$B${row}:${col}${row}'.format(
sheetname=sheetName,
col=colName,
row=row - 2,
end=len(hist[0]) + 1))
chart.add_series({
'values':
'=\'{sheetname}\'!$B${row}:${col}${row}'.format(
sheetname=sheetName,
col=colName,
row=row + 1 - 2,
end=len(hist[0]) + 1),
'categories':
'=\'{sheetname}\'!$B${row}:${col}${row}'.format(
sheetname=sheetName,
col=colName,
row=row - 2,
end=len(hist[0]) + 1),
'gap':
2,
})
chart.set_title({"name": parameterName + " Histogram"})
row = row + 2
loginfoSheet.insert_chart(row, 4, chart)
row = row + 20
def compareLogs(self, sheetName, col=0, row=0):
print("compare loginfo for two logs")
if len(self.loginfo) != 2:
print("Don't have 2 logs in the log info")
return 1
firstLog = self.loginfo[0] if "_1" in self.loginfo[
0].filePath else self.loginfo[1]
secondLog = self.loginfo[1] if "_2" in self.loginfo[
1].filePath else self.loginfo[0]
#print(firstLog.uniqueData)
for each in firstLog.uniqueData:
print(each)
if "memory" not in each and "ModsVersion" in firstLog.uniqueData[
each]:
if float(secondLog.uniqueData[each]["ModsVersion"]) - float(
firstLog.uniqueData[each]["ModsVersion"]) < 0:
temp = firstLog
firstLog = secondLog
secondLog = temp
else:
continue
firstLogInfo = firstLog.dataForeachSubstation
secondLog
sheet = self.wk.add_worksheet("compare")
cellFormat = self.wk.add_format()
cellFormat.set_num_format("0.0")
cellFormat.set_border()
col = 0
tempData = ["", ""]
powerData = ["", ""]
#print("start ....")
print(firstLog.uniqueData["subStationList"])
print(secondLog.uniqueData["subStationList"])
#print(secondLog.uniqueData)
for orderSubStation in firstLog.uniqueData["subStationList"]:
#print(orderSubStation)
#for eachSpec in firstLogInfo:
# if orderSubStation == eachSpec:
# continue
row = 0
eachSpec = orderSubStation
logging.info(eachSpec)
sheet.write(row, col, eachSpec)
eachSubStationData = firstLogInfo[eachSpec]
#print(eachSubStationData)
eachSubStationDataSecond = secondLog.dataForeachSubstation[
eachSpec]
#print(eachSubStationDataSecond)
testList = None if "testList" not in eachSubStationData else eachSubStationData[
"testList"]
if "gpuMaxTemp" in eachSubStationData:
logging.info(eachSubStationData["gpuMaxTemp"])
results = [
float(i)
for i in eachSubStationData["gpuMaxTemp"].split(" ")
]
maxGputemp = round(max(results), 1)
tempData[
0] = tempData[0] + " " + eachSubStationData["gpuMaxTemp"]
results = [
float(i)
for i in eachSubStationData["INPUT_TOTAL_BOARD"].split(" ")
]
maxTotalPwr = round(max(results), 1)
powerData[0] = powerData[0] + " " + eachSubStationData[
"INPUT_TOTAL_BOARD"]
results = [
float(i)
for i in eachSubStationDataSecond["gpuMaxTemp"].split(" ")
]
maxGputempSec = round(max(results), 1)
tempData[1] = tempData[1] + " " + eachSubStationDataSecond[
"gpuMaxTemp"]
results = [
float(i) for i in
eachSubStationDataSecond["INPUT_TOTAL_BOARD"].split(" ")
]
maxTotalPwrSec = round(max(results), 1)
powerData[1] = powerData[1] + " " + eachSubStationDataSecond[
"INPUT_TOTAL_BOARD"]
row = row + 1
sheet.write_row(row, col, [
"FileName",
firstLog.filePath.split(
os.sep)[len(firstLog.filePath.split(os.sep)) - 1]
], cellFormat)
sheet.write_row(row, col + 5, [
"FileName",
secondLog.filePath.split(
os.sep)[len(secondLog.filePath.split(os.sep)) - 1]
], cellFormat)
row = row + 1
sheet.write_row(
row, col,
["nvvdd", float(eachSubStationData["nvvdd"])], cellFormat)
sheet.write_row(
row, col + 5,
["nvvdd",
float(eachSubStationDataSecond["nvvdd"])], cellFormat)
row = row + 1
sheet.write_row(
row, col,
["GpcClk", float(eachSubStationData["ClkGpc"])],
cellFormat)
sheet.write_row(
row, col + 5,
["GpcClk",
float(eachSubStationDataSecond["ClkGpc"])], cellFormat)
row = row + 1
sheet.write_row(row, col, ["MaxGpuTemp", maxGputemp],
cellFormat)
sheet.write_row(row, col + 5, ["MaxGpuTemp", maxGputempSec],
cellFormat)
row = row + 1
sheet.write_row(row, col, ["MaxBoardTotalPwr", maxTotalPwr],
cellFormat)
sheet.write_row(row, col + 5,
["MaxBoardTotalPwr", maxTotalPwrSec],
cellFormat)
row = row + 1
sheet.write_row(row, col, [
"Test", "ErrorCode", "Test Parameter&&Result", "Test Time"
], cellFormat)
sheet.write_row(row, col + 5, [
"Test", "ErrorCode", "Test Parameter&&Result", "Test Time"
], cellFormat)
row = row + 1
if testList is None:
continue
secondTestList = secondLog.dataForeachSubstation[eachSpec][
"testList"]
print(secondTestList)
print(testList)
totalTimeDiff = 0
'''summary the tests which are removed in the new version diag'''
for eachTest in testList:
secondEachTest = "N/A$N/A$N/A$" if self.testExist(
eachTest, secondTestList) is False else self.testExist(
eachTest, secondTestList)
if self.testExist(eachTest, secondTestList):
secondTestList.remove(secondEachTest)
totalTimeDiff = totalTimeDiff + float(
secondEachTest.split("$")[3]) - float(
eachTest.split("$")[3])
# sheet.write_row(row,col,eachTest.split("$"),cellFormat)
# sheet.write(row,col+4,"--->>>",cellFormat)
# sheet.write_row(row,col+5,secondEachTest.split("$"),cellFormat)
# sheet.write(1,col+9,totalTimeDiff,cellFormat)
# row+=1
else:
totalTimeDiff = totalTimeDiff - float(
eachTest.split("$")[3])
sheet.write_row(row, col, eachTest.split("$"), cellFormat)
sheet.write(row, col + 4, "--->>>", cellFormat)
sheet.write_row(row, col + 5, secondEachTest.split("$"),
cellFormat)
sheet.write(1, col + 9, totalTimeDiff, cellFormat)
row += 1
print(eachTest)
'''summary the tests which are added in the new version diag'''
secondTestList = secondLog.dataForeachSubstation[eachSpec][
"testList"]
for eachTest in secondTestList:
secondEachTest = "N/A$N/A$N/A$" if self.testExist(
eachTest, testList) is False else self.testExist(
eachTest, testList)
if self.testExist(eachTest, testList):
testList.remove(secondEachTest)
totalTimeDiff = totalTimeDiff + float(
secondEachTest.split("$")[3]) - float(
eachTest.split("$")[3])
else:
totalTimeDiff = totalTimeDiff - float(
eachTest.split("$")[3])
sheet.write_row(row, col + 5, eachTest.split("$"),
cellFormat)
sheet.write(row, col + 4, "<<<---", cellFormat)
sheet.write_row(row, col, secondEachTest.split("$"),
cellFormat)
#sheet.write(1,col+9,totalTimeDiff,cellFormat)
row += 1
#sheet.wr
#print(eachTest)
col += 10
#break
dataSheet = self.wk.add_worksheet("pwrTemp")
dataSheet.write_row(0, 0, [
"FileName",
firstLog.filePath.split(
os.sep)[len(firstLog.filePath.split(os.sep)) - 1]
], cellFormat)
dataSheet.write(1, 1, "temperature")
temp0data = tempData[0]
dataSheet.write_column(2, 1, self.getDataFromStr(temp0data))
dataSheet.write(1, 2, "MaxTotalPwr")
dataSheet.write_column(2, 2, self.getDataFromStr(powerData[0]))
chartTemp = self.wk.add_chart({'type': 'line'})
#print('=pwrTemp!$A$%2:$A1$LHX$1'%(len(temp0data)))
chartTemp.add_series({
'values':
'=pwrTemp!$B$3:$B$%d' % (len(self.getDataFromStr(temp0data)))
})
dataSheet.write_row(0, 3, [
"FileName",
secondLog.filePath.split(
os.sep)[len(secondLog.filePath.split(os.sep)) - 1]
], cellFormat)
dataSheet.write(1, 3, "temperature")
dataSheet.write_column(2, 3, self.getDataFromStr(tempData[1]))
dataSheet.write(1, 4, "MaxTotalPwr")
dataSheet.write_column(2, 4, self.getDataFromStr(powerData[1]))
dataSheet.insert_chart(10, 5, chartTemp)
###############################################################
# cmdResult=compareCmdLine(firstLog.factoryInfo["commandLine"], secondLog.factoryInfo["commandLine"])
#
# cmdSheet=self.wk.add_worksheet("Command Line Diff")
#
# row=0
# for key,value in cmdResult.items():
# cmdSheet.write(row, 0,key)
# cmdSheet.write_row(row, 1,value)
# row=row+1
self.wk.close()
def getDataFromStr(self, data):
if data is None:
return None
#logging.info(data)
splitData = data.split(" ")
result = []
for each in splitData:
try:
temp = float(each)
result.append(temp)
except ValueError as e:
print(e)
print(each)
#print(result)
return result
def testExist(self, testName, testList):
actualName = testName.split("$")[0] + testName.split("$")[2]
if len(testList) < 1:
return False
for each in testList:
if actualName == each.split("$")[0] + each.split("$")[2]:
#print("find tests %s"%(actualName))
return each
return False
import os
import glob
import csv
from xlsxwriter.workbook import Workbook
for csvfile in glob.glob(os.path.join('.', '*.csv')):
workbook = Workbook(csvfile[:-4] + '.xlsx')
worksheet = workbook.add_worksheet()
with open(csvfile, 'rt', encoding='utf8') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, col)
workbook.close()
def generate_report(self, cr, uid, context=None):
cr.execute("""TRUNCATE TABLE stock_move_report""")
filter = self._prepare_filter(cr, uid, context)
#create sql
sql = self._create_sql%(uid, uid, filter)
cr.execute(sql)
#reverse sql
type = context.get('type','in')
if type not in ('consumption','production'):
filter = self._reverse_filter(cr, uid, context)
sql = self._reverse_sql%(uid, uid, filter)
if sql:
cr.execute(sql)
#create fold
if not os.path.exists('/tmp/oe-report/'):
os.mkdir('/tmp/oe-report')
filelist = glob.glob("/tmp/oe-report/*.xlsx")
for f in filelist:
os.remove(f)
os.chmod('/tmp/oe-report',0777)#check rights
#TODO
header, content = self._get_table_data(cr, uid, type, context)
csv_file = '/tmp/stock.move.report.csv'
with open(csv_file, "wb") as f:
fileWriter = csv.writer(f, delimiter=',',quotechar='"', quoting=csv.QUOTE_MINIMAL)
fileWriter.writerow(header)
fileWriter.writerows(content)
#cr.execute("COPY stock_move_in_report TO '/tmp/oe-report/stock.move.report.csv' WITH CSV HEADER NULL AS '' DELIMITER ';'")
#create message
message_id = self._create_message(cr, uid,context=context)
attachment_pool = self.pool.get('ir.attachment')
def convert_time(time):
tz = pytz.timezone('Asia/Shanghai')
time = pytz.utc.localize(datetime.strptime(time,'%Y-%m-%d %H:%M:%S')).astimezone(tz).strftime('%Y-%m-%d %H:%M:%S')
return time
period = "%s~%s"%(convert_time(context.get('start_date','2013-03-31 16:00:00')),convert_time(context.get('end_date','2013-03-31 16:00:00')))
xlsfile = '/tmp/oe-report/stock.move.report.%s[%s].xlsx'%(type,period)
#print xlsfile
w = Workbook(xlsfile)
ws = w.add_worksheet('Stock Moves')
ufile = open(csv_file,'r')
spamreader = csv.reader(ufile, delimiter=',', quotechar='"')
#line = 0
for rowx, row in enumerate(spamreader):
for colx, cell in enumerate(row):
ws.write(rowx, colx, unicode(cell, 'utf-8'))
# for row in spamreader:
# print ', '.join(row)
# col=0
# for cell in row:
# ws.write(line,col,unicode(cell, 'utf-8'))
# col += 1
# line +=1
w.close()
shutil.make_archive("/tmp/stock_move_report_%s[%s]"%(type,period), "zip", "/tmp/oe-report")
zipfile = open('/tmp/stock_move_report_%s[%s].zip'%(type,period),'r')
attachment_id = attachment_pool.create(cr, uid, {
'name': "stock.move.report.%s[%s].zip"%(type,period),
'datas': base64.encodestring(zipfile.read()),
'datas_fname': "stock.move.report.%s[%s].zip"%(type,period),
'res_model': 'mail.message',
'res_id': message_id,
})
cr.execute("""
INSERT INTO message_attachment_rel(
message_id, attachment_id)
VALUES (%s, %s);
""", (message_id, attachment_id))
return True
import sqlite3
from sr_extract_stress import stress_to_database
from sr_extract_sr import sr_to_database
from sr_extract_vm import vm_stress_to_database
from sr_extract_solid import solid_stress_to_database
from patran_input import process_input
import time
from xlsxwriter.workbook import Workbook
from math import sqrt
# Creare fisier excel pentru scrierea rezultatelor
workbook = Workbook('ResultsSR.xlsx')
worksheet = workbook.add_worksheet('Data_Results_SR')
worksheet2 = workbook.add_worksheet('Summary_SR_MOS')
worksheet3 = workbook.add_worksheet('Data_Results_HC')
worksheet4 = workbook.add_worksheet('Summary_HC_MOS')
worksheet5 = workbook.add_worksheet('Data_Results_VM')
worksheet6 = workbook.add_worksheet('Summary_VM')
# Creare baza de date pentru stocarea datelor
conn = sqlite3.connect('ResultsData.db')
conn.execute('pragma journal_mode=wal')
c = conn.cursor()
c.execute('''PRAGMA synchronous = OFF''')
c.execute("BEGIN TRANSACTION")
def nrCazuri():
c.execute('SELECT COUNT(DISTINCT subcase) FROM ElmStrengthRatio')
nrCaz = c.fetchone()
return nrCaz
def excel_overdues(request):
date_beg = request.session['date_beg']
date_end = request.session['date_end']
if date_beg is None:
date_beg = datetime.now()
if date_end is None:
date_end = datetime.now()
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response[
'Content-Disposition'] = "attachment; filename=Overdues{}.xlsx".format(
date_end)
context = {'in_memory': True, 'remove_timezone': True}
book = Workbook(response, context)
columns = 14
sheet = book.add_worksheet('Погашения по кредитам')
for i in range(columns):
sheet.set_column(i, i, 18)
col = 0
row = 0
sheet.write(0, col, 'Номер ID', excel.style_format(book))
sheet.write(row, col + 1, 'Клиент', excel.style_format(book))
sheet.write(row, col + 2, 'Дата рождения', excel.style_format(book))
sheet.write(row, col + 3, 'Паспорт', excel.style_format(book))
sheet.write(row, col + 4, 'ИНН', excel.style_format(book))
sheet.write(row, col + 5, 'Начало просрочки', excel.style_format(book))
sheet.write(row, col + 6, 'Конец просрочки', excel.style_format(book))
sheet.write(row, col + 7, 'Дни просрочки', excel.style_format(book))
sheet.write(row, col + 8, 'Просрочка осн. сумма', excel.style_format(book))
sheet.write(row, col + 9, 'Кредитный специалист', excel.style_format(book))
sheet.write(row, col + 10, 'Продукт', excel.style_format(book))
sheet.write(row, col + 11, 'Номер счета', excel.style_format(book))
sheet.write(row, col + 12, 'Остаток в номинале', excel.style_format(book))
sheet.write(row, col + 13, 'Остаток в нац. валюте',
excel.style_format(book))
row = 1
col = 0
cursor.execute(commands.overdue(date_beg, date_end))
sql_row = cursor.fetchall()
for a in sql_row:
sheet.write(row, col, a.number, excel.number_format(book))
sheet.write(row, col + 1, a.ClientName, excel.style_format(book))
sheet.write(row, col + 2, a.birth_date, excel.date_format(book))
sheet.write(row, col + 3, a.passport, excel.style_format(book))
sheet.write(row, col + 4, a.inn, excel.number_format(book))
sheet.write(row, col + 5, a.start_overdue, excel.date_format(book))
sheet.write(row, col + 6, a.end_overdue, excel.date_format(book))
sheet.write(row, col + 7, a.overdue_days, excel.number_format(book))
sheet.write(row, col + 8, a.main_overdue_summ,
excel.money_format(book))
sheet.write(row, col + 9, a.user, excel.style_format(book))
sheet.write(row, col + 10, a.product, excel.style_format(book))
sheet.write(row, col + 11, a.account_no, excel.number_format(book))
sheet.write(row, col + 12, a.current_balance, excel.money_format(book))
sheet.write(row, col + 13, a.current_nat_balance,
excel.money_format(book))
row = row + 1
book.close()
return response
############################################################################## # # A simple program to write some dates and times to an Excel file # using the XlsxWriter Python module. # # Copyright 2013, John McNamara, [email protected] # from datetime import datetime from xlsxwriter.workbook import Workbook # Create a workbook and add a worksheet. workbook = Workbook('datetimes.xlsx') worksheet = workbook.add_worksheet() bold = workbook.add_format({'bold': True}) # Expand the first columns so that the date is visible. worksheet.set_column('A:B', 30) # Write the column headers. worksheet.write('A1', 'Formatted date', bold) worksheet.write('B1', 'Format', bold) # Create a datetime object to use in the examples. date_time = datetime.strptime('2013-01-23 12:30:05.123', '%Y-%m-%d %H:%M:%S.%f') # Examples date and time formats. In the output file compare how changing # the format codes change the appearance of the date. date_formats = ( 'dd/mm/yy',
############################################################################## # # A simple example of some of the features of the XlsxWriter Python module. # # Copyright 2013, John McNamara, [email protected] # from xlsxwriter.workbook import Workbook # Create an new Excel file and add a worksheet. workbook = Workbook('demo.xlsx') worksheet = workbook.add_worksheet() # Widen the first column to make the text clearer. worksheet.set_column('A:A', 20) # Add a bold format to use to highlight cells. bold = workbook.add_format({'bold': 1}) # Write some simple text. worksheet.write('A1', 'Hello') # Text with formatting. worksheet.write('A2', 'World', bold) # Write some numbers, with row/column notation. worksheet.write(2, 0, 123) worksheet.write(3, 0, 123.456) workbook.close()
def do_rebuild(self):
self.env.cr.execute(
""" DROP VIEW IF EXISTS saldo_comprobante_empresa;
create or replace view saldo_comprobante_empresa as (
select t1.id as id, ap.name as periodo,t3.nro_documento as ruc,t3.name as empresa,t4.code as code,t4.name as descripcion,
CASE WHEN t5.type= 'payable' THEN 'A pagar' ELSE 'A cobrar' END as tipo_cuenta
,t_debe as debe,t_haber as haber, CASE WHEN abs(t_debe-t_haber) < 0.01 then 0 else t_debe-t_haber end as saldo from (
select min(aml.id) as id, concat(aml.partner_id,'-',aml.account_id) as identificador,sum(aml.debit) as t_debe,sum(aml.credit) as t_haber from account_move_line aml
inner join account_move am on am.id = aml.move_id
inner join account_period api on api.date_start <= am.fecha_contable and api.date_stop >= am.fecha_contable and am.fecha_special = api.special
inner join account_account aa on aa.id = aml.account_id
left join account_account_type aat on aat.id = aa.user_type_id
where (aat.type='receivable' or aat.type='payable' ) -- and aa.reconcile = true
and periodo_num(api.code) >= periodo_num('""" + str(self.periodo_ini.code) +
"""') and periodo_num(api.code) <= periodo_num('""" +
str(self.periodo_fin.code) + """')
and am.state != 'draft'
group by identificador) t1
left join account_move_line t2 on t2.id=t1.id
left join account_move am on am.id = t2.move_id
left join account_period ap on ap.date_start <= am.fecha_contable and ap.date_stop >= am.fecha_contable and am.fecha_special = ap.special
left join res_partner t3 on t3.id=t2.partner_id
left join account_account t4 on t4.id=t2.account_id
left join account_account_type t5 on t5.id = t4.user_type_id
order by code,empresa
)""")
filtro = []
if self.check == True:
filtro.append(('saldo', '!=', 0))
if self.cuenta.id:
filtro.append(('code', '=', self.cuenta.code))
if self.empresa.id:
filtro.append(('empresa', '=', self.empresa.name))
if self.tipo:
filtro.append(('tipo_cuenta', '=', self.tipo))
move_obj = self.env['saldo.comprobante.empresa']
lstidsmove = move_obj.search(filtro)
if (len(lstidsmove) == 0):
raise osv.except_osv('Alerta', 'No contiene datos.')
#DSC_Exportar a CSV por el numero de filas
self.env.cr.execute(
"""select count(*) from saldo_comprobante_empresa""")
rows = self.env.cr.fetchone()
#if self.mostrar == 'excel' and rows[0] > 1000:
# self.mostrar = 'csv'
if self.mostrar == 'pantalla':
return {
'domain': filtro,
'type': 'ir.actions.act_window',
'res_model': 'saldo.comprobante.empresa',
'view_mode': 'tree',
'view_type': 'form',
'views': [(False, 'tree')],
}
#DSC_
if self.mostrar == 'csv':
direccion = self.env['main.parameter'].search(
[])[0].dir_create_file
docname = 'SaldoEmpresa.csv'
#CSV
sql_query = """ COPY (SELECT * FROM saldo_comprobante_empresa )TO '""" + direccion + docname + """' WITH DELIMITER ',' CSV HEADER
"""
self.env.cr.execute(sql_query)
#Caracteres Especiales
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
f = open(direccion + docname, 'rb')
vals = {
'output_name': docname,
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['export.file.save'].create(vals)
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
if self.mostrar == 'excel':
import io
from xlsxwriter.workbook import Workbook
output = io.BytesIO()
########### PRIMERA HOJA DE LA DATA EN TABLA
#workbook = Workbook(output, {'in_memory': True})
direccion = self.env['main.parameter'].search(
[])[0].dir_create_file
workbook = Workbook(direccion + 'saldoperiodo.xlsx')
worksheet = workbook.add_worksheet("Analisis Saldo x Empresa")
#Print Format
worksheet.set_landscape() #Horizontal
worksheet.set_paper(9) #A-4
worksheet.set_margins(left=0.75, right=0.75, top=1, bottom=1)
worksheet.fit_to_pages(1, 0) # Ajustar por Columna
bold = workbook.add_format({'bold': True})
normal = workbook.add_format()
boldbord = workbook.add_format({'bold': True})
boldbord.set_border(style=2)
boldbord.set_align('center')
boldbord.set_align('vcenter')
boldbord.set_text_wrap()
boldbord.set_font_size(9)
boldbord.set_bg_color('#DCE6F1')
numbertres = workbook.add_format({'num_format': '0.000'})
numberdos = workbook.add_format({'num_format': '0.00'})
bord = workbook.add_format()
bord.set_border(style=1)
bord.set_text_wrap()
numberdos.set_border(style=1)
numbertres.set_border(style=1)
title = workbook.add_format({'bold': True})
title.set_align('center')
title.set_align('vcenter')
title.set_text_wrap()
title.set_font_size(20)
worksheet.set_row(0, 30)
x = 9
tam_col = [
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
]
tam_letra = 1.2
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
worksheet.merge_range(0, 0, 0, 8, u"Análisis de Saldos x Empresa",
title)
worksheet.write(1, 0, u"Año Fiscal", bold)
worksheet.write(1, 1, self.fiscal_id.name, normal)
worksheet.write(2, 0, u"Periodo Inicial", bold)
worksheet.write(2, 1, self.periodo_ini.name, normal)
worksheet.write(3, 0, u"Periodo Final", bold)
worksheet.write(3, 1, self.periodo_fin.name, normal)
worksheet.write(4, 0, u"Solo Pendientes", bold)
worksheet.write(4, 1, 'Si' if self.check else 'No', normal)
worksheet.write(5, 0, u"Empresa", bold)
worksheet.write(5, 1,
self.empresa.name if self.empresa.name else '',
normal)
worksheet.write(6, 0, u"Cuenta", bold)
worksheet.write(6, 1, self.cuenta.name if self.cuenta.name else '',
normal)
worksheet.write(8, 0, "Periodo", boldbord)
worksheet.write(8, 1, "Empresa", boldbord)
worksheet.write(8, 2, "RUC", boldbord)
worksheet.write(8, 3, "Tipo Cuenta", boldbord)
worksheet.write(8, 4, u"Cuenta", boldbord)
worksheet.write(8, 5, u"Descripción", boldbord)
worksheet.write(8, 6, "Debe", boldbord)
worksheet.write(8, 7, "Haber", boldbord)
worksheet.write(8, 8, "Saldo", boldbord)
for line in self.env['saldo.comprobante.empresa'].search(filtro):
worksheet.write(x, 0, line.periodo if line.periodo else '',
bord)
worksheet.write(x, 1, line.empresa if line.empresa else '',
bord)
worksheet.write(x, 2, line.ruc if line.ruc else '', bord)
worksheet.write(x, 3,
line.tipo_cuenta if line.tipo_cuenta else '',
bord)
worksheet.write(x, 4, line.code if line.code else '', bord)
worksheet.write(x, 5,
line.descripcion if line.descripcion else '',
bord)
worksheet.write(x, 6, line.debe, numberdos)
worksheet.write(x, 7, line.haber, numberdos)
worksheet.write(x, 8, line.saldo, numberdos)
x = x + 1
tam_col = [
15, 45, 12, 10, 25, 45, 11, 11, 10, 11, 14, 10, 11, 14, 14, 10,
16, 16, 20, 36
]
worksheet.set_column('A:A', tam_col[0])
worksheet.set_column('B:B', tam_col[1])
worksheet.set_column('C:C', tam_col[2])
worksheet.set_column('D:D', tam_col[3])
worksheet.set_column('E:E', tam_col[4])
worksheet.set_column('F:F', tam_col[5])
worksheet.set_column('G:G', tam_col[6])
worksheet.set_column('H:H', tam_col[7])
worksheet.set_column('I:I', tam_col[8])
worksheet.set_column('J:J', tam_col[9])
worksheet.set_column('K:K', tam_col[10])
worksheet.set_column('L:L', tam_col[11])
worksheet.set_column('M:M', tam_col[12])
worksheet.set_column('N:N', tam_col[13])
worksheet.set_column('O:O', tam_col[14])
worksheet.set_column('P:P', tam_col[15])
worksheet.set_column('Q:Q', tam_col[16])
worksheet.set_column('R:R', tam_col[17])
worksheet.set_column('S:S', tam_col[18])
worksheet.set_column('T:T', tam_col[19])
workbook.close()
f = open(direccion + 'saldoperiodo.xlsx', 'rb')
sfs_obj = self.pool.get('repcontab_base.sunat_file_save')
vals = {
'output_name': 'SaldoEmpresa.xlsx',
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['export.file.save'].create(vals)
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
def generate_excel(self):
tipeFile = ["extracto_bancario", "cajas_registradoras"]
title_report_list = ["Extracto Bancario", "Cajas registradoras"]
size_ref = 0
# nombre de archivo
if re.match("(.*)CAJA(.*)", self.name):
name_file = tipeFile[1] + \
datetime.now().strftime("%Y%m%d%H%M%S") + '.xlsx'
title_report = title_report_list[1]
size_ref = 40
else:
name_file = tipeFile[0] + \
datetime.now().strftime("%Y%m%d%H%M%S") + '.xlsx'
title_report = title_report_list[0]
size_ref = 20
direccion = self.env['main.parameter'].search([])[0].dir_create_file
# nombre de la direccion
direccion += name_file
workbook = Workbook(direccion, {
'in_memory': True,
'strings_to_numbers': True
})
workbook.formats[0].set_font_size(10)
workbook.formats[0].set_font_name("Arial")
worksheet = workbook.add_worksheet(title_report)
worksheet.set_zoom(80)
worksheet.set_row(1, 60)
worksheet.set_column(1, 1, 15) # ancho de B y C
worksheet.set_column(2, 5, 40) # ancho de B y C
worksheet.set_column(3, 4, 50) # ancho de B y C
worksheet.set_column(6, 6, 18)
########################### Cabecera ##################################
merge_format_left_label = workbook.add_format({
'bold': 1,
'align': 'left',
'valign': 'vcenter',
'font_size': 12,
})
merge_format_left_text = workbook.add_format({
'bold': 0,
'border': 0,
'align': 'left',
'valign': 'vcenter',
})
merge_format_title = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'underline': 0,
'valign': 'top',
'font_size': 14,
'font_name': 'Arial'
})
merge_format_right = workbook.add_format({
'align': 'right',
'valign': 'vcenter',
'right': 1
})
merge_format_center_header = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'font_size': 12,
'font_name': 'Arial',
})
merge_format_center_text = workbook.add_format({
'border': 0,
'align': 'center',
'valign': 'vcenter',
'font_size': 10,
'font_name': 'Arial',
})
merge_format_center_text_wrap = workbook.add_format({
'border':
0,
'align':
'center',
'valign':
'vcenter',
'font_size':
10,
'font_name':
'Arial',
})
merge_format_bottom_center = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'font_size': 12,
'font_name': 'Arial',
})
formatMoneyWithBorder = workbook.add_format({
'valign':
'vcenter',
'align':
'right',
'num_format':
'"%s" #,##0.00' % self.currency_id.symbol
})
formatMoney = workbook.add_format({
'num_format': '#,##0.00',
'border': 1,
'valign': 'vcenter',
'align': 'right'
})
# agregando logo al documento
#worksheet.insert_image('B2:D2', 'company_logo.jpg', {
# 'x_offset': 25, 'y_offset': 5, 'y_scale': 0.95, 'x_scale': 0.85})
# Borde celdas de imagen
#worksheet.merge_range('B2:C2', "", merge_format_title)
# coloca Titulo
# worksheet.merge_range('D2:N2', title_report, merge_format_title)
# Inf - Fecha
merge_format_bottom_center.set_text_wrap()
worksheet.merge_range('B2:G2', title_report + "\n" + self.name,
merge_format_bottom_center)
# Cabezera
worksheet.write('B4', "Diario:", merge_format_left_label)
worksheet.write('B5', "Fecha:", merge_format_left_label)
worksheet.write('F4', "Saldo Inicial:", merge_format_left_label)
worksheet.write('F5', "Balance Final:", merge_format_left_label)
worksheet.write('C4', self.journal_id[0].name, merge_format_left_text)
worksheet.write('C5', self.date, merge_format_left_text)
worksheet.write_number('G4', self.balance_start, formatMoneyWithBorder)
worksheet.write_number('G5', self.balance_end_real,
formatMoneyWithBorder)
# worksheet.set_column(4, 6, 30) # ancho de B y C
x = 7
y = 1
i = 0
worksheet.write("B7", "Fecha", merge_format_center_header)
worksheet.write("C7", "Etiqueta", merge_format_center_header)
worksheet.write("D7", "Partner", merge_format_center_header)
worksheet.write("E7", "Referencia", merge_format_center_header)
worksheet.write("F7", "Medio de Pago", merge_format_center_header)
worksheet.write("G7", "Cantidad", merge_format_center_header)
for item in self.line_ids:
worksheet.set_row(x + i, size_ref)
if (item['date']):
worksheet.write(x + i, y, item['date'],
merge_format_center_text)
if (item['name']):
worksheet.write(x + i, y + 1, item['name'],
merge_format_center_text)
if item.partner_id.name:
worksheet.write(x + i, y + 2, item.partner_id.name,
merge_format_center_text)
if item['ref']:
worksheet.write(x + i, y + 3, item['ref'],
merge_format_center_text)
if item.medio_pago.name:
merge_format_center_text.set_text_wrap()
worksheet.write(x + i, y + 4, item.medio_pago.name,
merge_format_center_text)
if item['amount']:
worksheet.write_number(x + i, y + 5, item['amount'],
formatMoneyWithBorder)
i = i + 1
workbook.close()
# os.remove('company_logo.jpg')
f = open(direccion, 'rb')
vals = {
'output_name': 'Reporte_' + name_file,
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['export.file.save'].create(vals)
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
def get(self, request, *args, **kwargs):
from xlsxwriter.workbook import Workbook
from io import BytesIO
resolve = {'1': 0, '3': 0, '5': 0}
time = {'1': 0, '2': 0, '3': 0, '5': 0}
difficulty = {'1': 0, '2': 0, '3': 0, '5': 0}
contact = {'1': 0, '2': 0, '3': 0, '4': 0, '5': 0, '6': 0}
satisfied = {'1': 0, '2': 0, '3': 0, '5': 0}
init_date = request.GET['init_date']
end_date = request.GET['end_date']
if init_date and end_date:
issue_evaluations = IssueEvaluation.objects.filter(
issue__created_at__lte=end_date,
issue__created_at__gte=init_date)
else:
issue_evaluations = IssueEvaluation.objects.all()
output = BytesIO()
book = Workbook(output)
header = book.add_format({
'bg_color': '#F7F7F7',
'color': 'black',
'align': 'center',
'valign': 'top',
'border': 1
})
datax = book.add_format({
'color': 'black',
'align': 'center',
'valign': 'top',
'border': 1
})
sheet = book.add_worksheet('Listado')
row = 1
for ev in issue_evaluations:
time[ev.time_evaluation] = time[ev.time_evaluation] + 1
resolve[ev.resolve] = resolve[ev.resolve] + 1
difficulty[ev.difficulty] = difficulty[ev.difficulty] + 1
contact[ev.contact] = contact[ev.contact] + 1
satisfied[ev.satisfied] = satisfied[ev.satisfied] + 1
sheet.write(0, 0, "Numero de evaluaciones presentadas:", header)
sheet.write(0, 1, issue_evaluations.count(), header)
sheet.write(2, 0, "Su solicitud fue resuelta", header)
sheet.merge_range(2, 0, 2, 1, "Su solicitud fue resuelta", header)
sheet.write(3, 0, "No resuelta", header)
sheet.write(3, 1, resolve['1'], datax)
sheet.write(4, 0, "Fue resuelta completamente", header)
sheet.write(4, 1, resolve['5'], datax)
sheet.merge_range(6, 0, 6, 1, "El Tiempo en atender su solicitud fue:",
header)
sheet.write(7, 0, "Muy lento", header)
sheet.write(7, 1, time['1'], datax)
sheet.write(8, 0, "Lento", header)
sheet.write(8, 1, time['2'], datax)
sheet.write(9, 0, "Rapido", header)
sheet.write(9, 1, time['3'], datax)
sheet.write(10, 0, "Muy rapido", header)
sheet.write(10, 1, time['5'], datax)
sheet.merge_range(
12, 0, 12, 1, "EL nivel de dificultad para usar el software de " +
"reporte de incidencias fue:", header)
sheet.write(13, 0, "Muy facil", header)
sheet.write(13, 1, difficulty['1'], datax)
sheet.write(14, 0, "Facil", header)
sheet.write(14, 1, difficulty['2'], datax)
sheet.write(15, 0, "Dificil", header)
sheet.write(15, 1, difficulty['3'], datax)
sheet.write(16, 0, "Muy dificil", header)
sheet.write(16, 1, difficulty['5'], datax)
sheet.merge_range(
18, 0, 18, 1, "Fue contactado por alguno de estos medios para " +
"resolver su solicitud", header)
sheet.write(19, 0, "Extension telefonica", header)
sheet.write(19, 1, contact['1'], datax)
sheet.write(20, 0, 'Correo electronico', header)
sheet.write(20, 1, contact['2'], datax)
sheet.write(21, 0, "Celular", header)
sheet.write(21, 1, contact['3'], datax)
sheet.write(22, 0, 'Chat', header)
sheet.write(22, 1, contact['4'], datax)
sheet.write(23, 0, "Personalmente", header)
sheet.write(23, 1, contact['5'], datax)
sheet.write(23, 0, "Ninguno", header)
sheet.write(23, 1, contact['5'], datax)
sheet.merge_range(25, 0, 25, 1,
'Nivel de satisfaccion con la atencion recibida:',
header)
sheet.write(26, 0, 'Muy insatisfecho', header)
sheet.write(26, 1, satisfied['1'], datax)
sheet.write(27, 0, "Insatisfecho", header)
sheet.write(27, 1, satisfied['2'], datax)
sheet.write(28, 0, "Satisfecho", header)
sheet.write(28, 1, satisfied['3'], datax)
sheet.write(29, 0, "Muy satisfecho", header)
sheet.write(29, 1, satisfied['5'], datax)
book.close()
output.seek(0)
response = HttpResponse(output.read(),
content_type="application/vnd.openxmlformats" +
"-officedocument.spreadsheetml." + "sheet")
return response
