Example #1
0
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()
Example #3
0
    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'
Example #4
0
    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'
Example #5
0
 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'))
Example #6
0
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()
Example #7
0
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()
Example #8
0
  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'] = [[]]
Example #9
0
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()
Example #10
0
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()
Example #12
0
    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
Example #13
0
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()
Example #15
0
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()
Example #17
0
 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
Example #18
0
 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
Example #19
0
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()
Example #20
0
 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()
Example #23
0
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()
Example #24
0
 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'
Example #25
0
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
Example #26
0
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()
Example #27
0
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()
Example #28
0
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()
Example #29
0
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 #31
0
###############################################################################
#
# 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.'
Example #32
0
    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
Example #34
0
    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",
        }
Example #35
0
    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)
Example #36
0
    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",
        }
Example #37
0
    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",
            }
Example #38
0
 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 = []
Example #39
0
        "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[ ]:
Example #40
0
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())))
Example #42
0
##############################################################################
#
# 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)
Example #45
0
    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 #46
0
#
# 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
Example #47
0
    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()
Example #48
0
# 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',
Example #49
0
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()
Example #50
0
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='Спорт'")
Example #51
0
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
Example #52
0
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()
Example #53
0
    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
Example #56
0
##############################################################################
#
# 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',
Example #57
0
##############################################################################
#
# 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",
            }
Example #59
0
    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",
        }
Example #60
0
    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