コード例 #1
0
    # 若上午或下午的标志位不是出勤,那么标记异常
    if AM != "出勤" or PM != "出勤":
        if AM != "出勤":
            per_row[5] = "异常"
            per_row[6] += AM
        if PM != "出勤":
            per_row[5] = "异常"
            per_row[6] += PM

    # print(per_row[3], AM, PM, start_time, end_time, min_time, max_time)
#############################################################################
# 输出已经过检验的day_list列表至EXCLE xlsx文件中
#############################################################################

# 创建考勤报表工作簿
wb_report = openpyxl.Workbook()
ws_report = wb_report.active
# 设置工作表格式

# 写入表头
header = ["部门", "员工编号", "姓名"] + date_list
ws_report.append(header)

# 写入用户信息
for per_row in user_list:
    ws_report.append(per_row)

# 读day_list列表,将信息写入单元格
# 定位单元格行row 列col

for per_row in day_list:
コード例 #2
0
def splitDeals():
    # Open an existing excel file
    wb = openpyxl.load_workbook('data' + ".xlsx")
    sheet = wb.worksheets[0]
    # Create a new excel file
    out = openpyxl.Workbook()
    # Open the worksheet we want to edit
    outsheet = out.create_sheet("data")

    # if 'sheet' appears randomly we can delete it
    rm = out.get_sheet_by_name('Sheet')
    out.remove_sheet(rm)

    start = 2
    end = sheet.max_row + 1

    #################
    # DO STUFF HERE #
    #################
    ID = "A"
    COMPANY = "B"
    FINANCIAL_INSTITUTION = "C"
    DEAL_TYPE = "D"
    DEAL_SUB_TYPE = "E"
    ORIGINAL_DEAL_TYPE = "F"
    AMOUNT_MM_USD = "G"
    COMMENTS = "H"
    DATE = "I"

    outsheet[ID + '1'].value = "Transaction Id"
    outsheet[COMPANY + '1'].value = "Company"
    outsheet[FINANCIAL_INSTITUTION + '1'].value = "Financial Institution"
    outsheet[DEAL_TYPE + '1'].value = "Deal Type"
    outsheet[DEAL_SUB_TYPE + '1'].value = "Deal Sub Type"
    outsheet[ORIGINAL_DEAL_TYPE + '1'].value = "Original Deal Type"
    outsheet[AMOUNT_MM_USD + '1'].value = "Amount MM USD"
    outsheet[COMMENTS + '1'].value = "Comments"
    outsheet[DATE + '1'].value = "Date"

    # Only care about column C
    count = 2
    for row in range(start, end):
        if sheet[FINANCIAL_INSTITUTION + str(row)].value:
            banks = sheet[FINANCIAL_INSTITUTION + str(row)].value.replace(
                ',', '\n').split('\n')
        else:
            banks = ["Undisclosed"]
        for bank in banks:
            outsheet[ID + str(count)].value = sheet[ID + str(row)].value
            outsheet[COMPANY + str(count)].value = sheet[COMPANY +
                                                         str(row)].value
            outsheet[FINANCIAL_INSTITUTION + str(count)].value = bank.strip()
            outsheet[DEAL_TYPE + str(count)].value = sheet[DEAL_TYPE +
                                                           str(row)].value
            outsheet[DEAL_SUB_TYPE + str(count)].value = sheet[DEAL_SUB_TYPE +
                                                               str(row)].value
            outsheet[ORIGINAL_DEAL_TYPE +
                     str(count)].value = sheet[ORIGINAL_DEAL_TYPE +
                                               str(row)].value
            outsheet[AMOUNT_MM_USD + str(count)].value = sheet[AMOUNT_MM_USD +
                                                               str(row)].value
            outsheet[COMMENTS + str(count)].value = sheet[COMMENTS +
                                                          str(row)].value
            outsheet[DATE + str(count)].value = sheet[DATE + str(row)].value
            count = count + 1

    out.save("organized.xlsx")

    # LMK when the script is done
    pygame.init()
    pygame.mixer.music.load('/home/andrefisch/python/evan/note.mp3')
    pygame.mixer.music.play()
    time.sleep(5)
    pygame.mixer.music.stop()
コード例 #3
0
    def energies(self, file, energies, frequencies=None,
                 stoichiometry=None, corrections=None):
        """Writes detailed information from multiple Energies objects to
         single xlsx file.

        Parameters
        ----------
        file: string
            path to file
        energies: list of glassware.Energies
            Energies objects that is to be exported
        frequencies: glassware.DataArray, optional
            DataArray object containing frequencies
        stoichiometry: glassware.InfoArray, optional
            InfoArray object containing stoichiometry information
        corrections: list of glassware.DataArray
            DataArray objects containing energies corrections"""
        wb = oxl.Workbook()
        ws = wb.active
        ens_no = len(energies)
        ws.title = 'Collective overview'
        headers = ['Gaussian output file', 'Populations / %',
                   'Energies / hartree']
        headers += ['Imag'] if frequencies is not None else []
        headers += ['Stoichiometry'] if stoichiometry is not None else []
        cells = ['A1', 'B1', f'{chr(66+ens_no)}1', f'{chr(66+2*ens_no)}1',
                 f'{chr(67+2*ens_no)}1']
        for header, cell in zip(headers, cells):
            ws[cell] = header
        names = [self._header[en.genre] for en in energies]
        ws.append([''] + names + names)
        ws.merge_cells('A1:A2')
        ws.merge_cells(f'B1:{chr(65+ens_no)}1')
        ws.merge_cells(f'{chr(66+ens_no)}1:{chr(65+2*ens_no)}1')
        if frequencies is not None or stoichiometry is not None:
            ws.merge_cells(f'{chr(66+2*ens_no)}1:{chr(66+2*ens_no)}2')
        if frequencies is not None and stoichiometry is not None:
            ws.merge_cells(f'{chr(67+2*ens_no)}1:{chr(67+2*ens_no)}2')
        ws.freeze_panes = 'A3'
        # data = self.ts.energies
        filenames = energies[0].filenames
        fmts = ['0'] + ['0.00%'] * len(energies) + \
               ['0.' + '0' * (8 if en.genre == 'scf' else 6) for en in
                energies] + \
               ['0', '0']
        values = [en.values for en in energies]
        populs = [en.populations for en in energies]
        imag = frequencies.imaginary if frequencies is not None else []
        stoich = stoichiometry.values if stoichiometry is not None else []
        rows = zip_longest(filenames, *populs, *values, imag, stoich)
        for row_num, values in enumerate(rows):
            filtered_values = ((f, v) for f, v in zip(fmts, values)
                               if v is not None)
            for col_num, (fmt, value) in enumerate(filtered_values):
                cell = ws.cell(row=row_num + 3, column=col_num + 1)
                cell.value = value
                cell.number_format = fmt
        # set cells width
        widths = [0] + [10] * ens_no + [16] * ens_no
        widths += [6] if frequencies is not None else []
        widths += [0] if stoichiometry is not None else []
        for column, width in zip(ws.columns, widths):
            if not width:
                width = max(len(str(cell.value)) for cell in column) + 2
            ws.column_dimensions[column[0].column].width = width
        # proceed to write detailed info on separate sheet for each energy
        corrs = {c.genre[:3]: c for c in corrections} \
            if corrections is not None else {}
        for en in energies:
            genre = en.genre
            corr = corrs.get(genre, None)
            fmts = ['0', '0.00%'] + ['0.0000'] * 2 + \
                   ['0.00000000' if genre == 'scf' else '0.000000'] * 2
            ws = wb.create_sheet(title=self._header[genre])
            ws.freeze_panes = 'A2'
            header = ['Gaussian output file', 'Population / %',
                      'Min. B. Factor', 'DE / (kcal/mol)',
                      'Energy / Hartree']
            header += ['Correction / Hartree'] if corr is not None else []
            ws.append(header)
            corr = corr.values if corr is not None else []
            rows = zip_longest(en.filenames, en.populations, en.min_factors,
                               en.deltas, en.values, corr)
            for row_num, values in enumerate(rows):
                filtered_values = ((f, v) for f, v in zip(fmts, values)
                                   if v is not None)
                for col_num, (fmt, value) in enumerate(filtered_values):
                    cell = ws.cell(row=row_num + 2, column=col_num + 1)
                    cell.value = value
                    cell.number_format = fmt
            # set cells width
            widths = [0, 15, 14, 15, 16, 19]
            for column, width in zip(ws.columns, widths):
                if not width:
                    width = max(len(str(cell.value)) for cell in column) + 2
                ws.column_dimensions[column[0].column].width = width
        wb.save(file)
        logger.info('Energies export to xlsx files done.')
コード例 #4
0
def run(output_path=None, user_provided_master_path=None, date_range=None):
    """
    Main function to run this analyser.
    :param output_path:
    :param user_provided_master_path:
    :return:
    """

    if user_provided_master_path:
        logger.info(f"Using master file: {user_provided_master_path}")
        NUMBER_OF_PROJECTS = projects_in_master(user_provided_master_path)
    else:
        logger.info(f"Using default master file (refer to config.ini)")
        NUMBER_OF_PROJECTS = projects_in_master(
            os.path.join(ROOT_PATH,
                         runtime_config['MasterForAnalysis']['name']))

    wb = openpyxl.Workbook()
    segment_series_generator = _segment_series()

    logger.debug(f"Using block_start of {BLOCK_START}")
    logger.debug(f"Using day_range of {DAY_RANGE}")
    logger.debug(f"Using block_skip of {BLOCK_SKIP}")
    logger.debug(f"Using block_end of {BLOCK_END}")
    logger.debug(f"Using forecast_actual_skip of {FORECAST_ACTUAL_SKIP}")

    for p in range(1, NUMBER_OF_PROJECTS + 1):
        proj_num, st_row = _row_calc(p)
        wb = gather_data(st_row,
                         proj_num,
                         wb,
                         block_start_row=BLOCK_START,
                         interested_range=DAY_RANGE,
                         master_path=user_provided_master_path,
                         date_range=date_range)[0]

    chart = ScatterChart()
    chart.title = CHART_TITLE
    chart.style = CHART_STYLE
    chart.height = CHART_HEIGHT
    chart.width = CHART_WIDTH
    chart.x_axis.title = CHART_X_AXIS_TITLE
    chart.y_axis.title = CHART_Y_AXIS_TITLE
    chart.legend = None
    chart.x_axis.majorUnit = CHART_X_AXIS_MAJOR_UNIT
    chart.x_axis.minorGridlines = None
    chart.y_axis.majorUnit = CHART_Y_AXIS_MAJOR_UNIT

    derived_end = 2

    if GREYMARKER:
        markercol = _grey_marker_colours
    else:
        markercol = _marker_colours

    for p in range(NUMBER_OF_PROJECTS):
        for i in range(
                1, 7
        ):  # 7 here is hard-coded number of segments within a project series (ref: dict in _segment_series()
            if i == 1:
                inner_start_row = derived_end
            else:
                inner_start_row = derived_end
            _inner_step = next(segment_series_generator)
            series, derived_end = _series_producer(wb.active, inner_start_row,
                                                   _inner_step[1] - 1)
            if _inner_step[0] == 'pvr_gate_zero':
                series.marker.symbol = "diamond"
                series.marker.graphicalProperties.solidFill = markercol[0]
            elif _inner_step[0] == 'sobc':
                series.marker.symbol = "circle"
                series.marker.graphicalProperties.solidFill = markercol[0]
            elif _inner_step[0] == 'obc':
                series.marker.symbol = "triangle"
                series.marker.graphicalProperties.solidFill = markercol[0]
            elif _inner_step[0] == 'fbc':
                series.marker.symbol = "square"
                series.marker.graphicalProperties.solidFill = markercol[0]
            elif _inner_step[0] == 'readiness_closure_exit':
                series.marker.symbol = "plus"
                series.marker.graphicalProperties.solidFill = markercol[0]
            else:
                series.marker.symbol = "triangle"
                series.marker.graphicalProperties.solidFill = markercol[0]
            series.marker.size = 10
            chart.series.append(series)
        segment_series_generator = _segment_series()
        derived_end = derived_end + 1

    wb.active.add_chart(chart, CHART_ANCHOR_CELL)
    try:
        if output_path:
            wb.save(
                os.path.join(output_path[0],
                             'swimlane_assurance_milestones.xlsx'))
            logger.info(
                f"Saved swimlane_assurance_milestones.xlsx to {output_path}")
        else:
            output_path = os.path.join(ROOT_PATH, 'output')
            wb.save(
                os.path.join(output_path,
                             'swimlane_assurance_milestones.xlsx'))
            logger.info(
                f"Saved swimlane_assurance_milestones.xlsx to {output_path}")
    except PermissionError:
        logger.critical(
            "Cannot save swimlane_assurance_milestones.xlsx file - you already have it open. Close and run again."
        )
        return
コード例 #5
0
ファイル: Write_xlsx8.py プロジェクト: nanigasi-san/nanigasi
#Excel関数の埋め込み
import openpyxl

book = openpyxl.Workbook()

active_sheet = book.active
active_sheet["B2"] = 1
active_sheet["B3"] = 10
active_sheet["B4"] = 100
active_sheet["B5"] = "=sum(B2,B3,B4)"

book.save("library/use_file/openpyxl/xlsx/sample8.xlsx")
コード例 #6
0
#print(type(wb))
#print(wb.get_sheet_names())
l = wb.get_sheet_names()
l.pop()
#anotherSheet = wb.active
#print(anotherSheet)
#sheet = wb.get_sheet_by_name('Current')
#print(sheet)
#print(sheet['A1'].value)

#print(sheet.max_row)
#print(sheet.max_column)

#print(sheet.cell(row=1, column=2).value)

wb1 = openpyxl.Workbook()
print(wb1.active)

sheet1 = wb1.get_sheet_by_name('Sheet')
wb1.remove_sheet(sheet1)
for i in l:
    sheet = wb.get_sheet_by_name(i)
    sheet1 = wb1.create_sheet(title=i)
    n = sheet.max_row
    print(n)
    m = sheet.max_column
    print(m)
    for i in range(1, n + 1):
        for j in range(1, m + 1):
            sheet1.cell(row=i,
                        column=j).value = sheet.cell(row=i,
コード例 #7
0
ファイル: excel.py プロジェクト: diandian888/mykesgo
 def __init__(self, filename):
     self.wb = openpyxl.Workbook()#生成一个实例
     self.wb.save(filename)#生成一个空excel文件
     self.filename = filename
     self.wb = load_workbook(self.filename)#读取excel
     self.ws = self.wb.active  # 激活sheet
コード例 #8
0
ファイル: xlsx.py プロジェクト: jayvdb/pyexcel-xlsx
 def open(self, file_name, **keywords):
     BookWriter.open(self, file_name, **keywords)
     self.native_book = openpyxl.Workbook()
コード例 #9
0
def make_excel_file():
    remote_addr = ipaddress.ip_address(request.remote_addr)
    if remote_addr != ipaddress.ip_address("127.0.0.1"):
        return "Access Denied", 403

    hospital_id = request.args.get("hpid")
    exporting_path = request.args.get("path")
    #print(exporting_path)
    db = Database(**dns)

    def get_columns(result):
        #q_columns = f'''
        #    SHOW COLUMNS FROM `{ table }`;
        #'''
        #res_columns = db.query( q_columns )
        #columns = list( map( lambda x: x[ 'Field' ], res_columns ) )
        #return columns
        return result[0].keys()

    def table_to_excel(table, ws):
        if isinstance(table[0], dict):
            columns = table[0].keys()
        elif isinstance(table[0], list):
            columns = table[0]
            if isinstance(table[1][0], tuple):
                table = table[1]
            else:
                table.pop(0)

        x = 1
        for column in columns:
            ws.cell(row=1, column=x, value=column)
            x += 1

        for i, table_row in enumerate(table):
            for j, column in enumerate(columns):
                value = None
                if isinstance(table[0], dict):
                    value = table_row[column]
                elif isinstance(table[0], tuple):
                    value = table_row[j]
                elif isinstance(table[0], list):
                    try:
                        value = table_row[j]
                    except IndexError:
                        value = None
                if value == b'\x00':
                    if column == 'sex':
                        value = 'Female'
                    else:
                        value = False
                elif value == b'\x01':
                    if column == 'sex':
                        value = 'Male'
                    else:
                        value = True
                #print( value )
                ws.cell(row=2 + i, column=j + 1, value=value)

    # baseline
    q_baseline = f'''
        SELECT * FROM `patients`
            LEFT JOIN `ucg`
                ON `patients`.`ucg_id` = `ucg`.`ucg_id`
            WHERE `patients`.`hospital_id` = { hospital_id }
            ORDER BY `patients`.`patient_number`;
    '''
    baseline_table = db.query(q_baseline)

    wb = xl.Workbook()
    baseline_ws = wb.active
    baseline_ws.title = "Baseline"
    table_to_excel(baseline_table, baseline_ws)
    for count, col in enumerate([1, 2, 3, 5]):
        baseline_ws.delete_cols(col - count)

    #1st session
    q_first_session = f'''
        SELECT `patients`.`patient_number`, `first_ablation`.* FROM `patients`
            LEFT JOIN `first_ablation`
                ON `patients`.`patient_serial_number` = `first_ablation`.`patient_serial_number`
            WHERE `patients`.`hospital_id` = { hospital_id }
            ORDER BY `patients`.`patient_number`;
    '''
    first_session_table = db.query(q_first_session)

    first_session_ws = wb.create_sheet(title="1st session")
    table_to_excel(first_session_table, first_session_ws)

    for count, col in enumerate([2, 4, 5]):
        first_session_ws.delete_cols(col - count)

    #1st session medicine
    q_first_medicine = f'''
        SELECT `patients`.`patient_number`, `internal_medicine`.* FROM `patients`
            LEFT JOIN `first_ablation`
                ON `patients`.`patient_serial_number` = `first_ablation`.`patient_serial_number`
            LEFT JOIN `internal_medicine`
                ON `first_ablation`.`internal_medicine_id` = `internal_medicine`.`internal_medicine_id`
            WHERE `patients`.`hospital_id` = { hospital_id }
            ORDER BY `patients`.`patient_number`;
    '''
    first_medicine_table = db.query(q_first_medicine)

    first_medicine_ws = wb.create_sheet(title="1st session内服薬")
    table_to_excel(first_medicine_table, first_medicine_ws)

    for count, col in enumerate([2]):
        first_medicine_ws.delete_cols(col - count)

    #Following session
    q_following_session = f'''
        SELECT  `patients`.`patient_number`, `following_ablation`.*, `ucg`.*, `internal_medicine`.* FROM `patients`
            LEFT JOIN `following_ablation`
                ON `following_ablation`.`patient_serial_number` = `patients`.`patient_serial_number`
            LEFT JOIN `ucg`
                ON `following_ablation`.`ucg_id` = `ucg`.`ucg_id`
            LEFT JOIN `internal_medicine`
                ON `following_ablation`.`internal_medicine_id` = `internal_medicine`.`internal_medicine_id`
            WHERE `patients`.`hospital_id` = { hospital_id }
            ORDER BY `patient_number`, `following_ablation`.`date`;
    '''
    following_session_table = db.query(q_following_session)

    # horizonize
    following_session_df = pd.DataFrame(following_session_table)
    following_session_df = following_session_df.drop([
        'following_ablation_id', 'patient_serial_number', 'ucg_id',
        'internal_medicine_id'
    ],
                                                     axis=1)
    patients = following_session_df["patient_number"].unique()

    fs_table = []
    iterated_count = 0
    for patient in patients:
        following_session_a_patient = following_session_df[
            following_session_df["patient_number"] == patient]

        patient_row = []
        for count, row in enumerate(following_session_a_patient.itertuples()):
            row_list = list(row)
            row_list.pop(0)
            patient_row.extend(row_list)
            if count > iterated_count:
                iterated_count = count

        #print( patient_row )
        fs_table.append(patient_row)

    original_headers = following_session_df.columns.tolist()
    #original_headers.pop(0)
    header_list = []
    for i in range(iterated_count):
        header_list.extend(original_headers)

    fs_table.insert(0, header_list)

    #print( fs_table )

    following_session_ws = wb.create_sheet(title="following session")
    table_to_excel(fs_table, following_session_ws)
    #for count,col in enumerate([2,4,5]):
    #    following_session_ws.delete_cols(col - count)

    #Follow up
    q_followup = f'''
        SELECT `patients`.`patient_number`, `follow_up`.*,
                `ucg1`.*, `ucg2`.*, `ucg3`.*, `blood1`.*, `blood2`.*, `blood3`.* FROM `patients` 
            LEFT JOIN `follow_up` ON `patients`.`patient_serial_number` = `follow_up`.`patient_serial_number`
            LEFT JOIN `ucg` as `ucg1` ON `follow_up`.`ucg_id1` = `ucg1`.`ucg_id`
            LEFT JOIN `ucg` as `ucg2` ON `follow_up`.`ucg_id2` = `ucg2`.`ucg_id`
            LEFT JOIN `ucg` as `ucg3` ON `follow_up`.`ucg_id3` = `ucg3`.`ucg_id`
            LEFT JOIN `blood_exam` as `blood1` ON `follow_up`.`blood_id1` = `blood1`.`blood_id`
            LEFT JOIN `blood_exam` as `blood2` ON `follow_up`.`blood_id2` = `blood2`.`blood_id`
            LEFT JOIN `blood_exam` as `blood3` ON `follow_up`.`blood_id3` = `blood3`.`blood_id`
            WHERE `patients`.`hospital_id` = { hospital_id }
            ORDER BY `patients`.`patient_number`;
    '''
    followup_table = db.query(q_followup, duplicated=True)
    #print(followup_table[0])
    #print(followup_table[1])
    followup_ws = wb.create_sheet(title="follow up")
    table_to_excel(followup_table, followup_ws)

    wb.save(exporting_path)

    return ("dekitayo!")
コード例 #10
0
ファイル: yiqing.py プロジェクト: 1165175713/COVIDdata2excel
    soup = BeautifulSoup(open('yiqing.html','rb'))
    table = soup.table
    head = Head(table)
    body = Body(table)

    #body = Body_China(table)
    #body = Body_country(body) 

    # tmp = []
    # for i in body:
    #     tmp.append(i[1])
    # import pickle
    # with open("tmp2","wb") as f:
    #     pickle.dump(tmp, f)

    outwb = openpyxl.Workbook()  # 打开一个将写的文件
    outws = outwb.create_sheet(index=0) 
    for i in range(len(body)):
        for j in range(len(head)):
            outws.cell(i*len(head)+j+1, 1).value = body[i][0] 
            outws.cell(i*len(head)+j+1, 2).value = body[i][1] 
            outws.cell(i*len(head)+j+1, 3).value = head[j]
            if j==0:
                outws.cell(i*len(head)+j+1, 4).value = body[i][j+2]
            else:
                outws.cell(i*len(head)+j+1, 4).value = int(body[i][j+2])-int(body[i][j+1])
            try:
                outws.cell(i*len(head)+j+1, 5).value = body[i][j+2]
            except:
                print(i,j)
         
コード例 #11
0
ファイル: models.py プロジェクト: eataix/varie-fied
    def export(self) -> str:
        def cm_to_inch(cm: float) -> float:
            return cm * 0.393701

        def prepare(ws: openpyxl.worksheet.Worksheet) -> None:
            ws.header_footer.setHeader(
                '&L&"Calibri,Regular"&K000000&G&C&"Lao UI,Bold"&8Total Project Construction Pty. Ltd.&"Lao UI,Regular"&K000000_x000D_ACN 117 578 560  ABN 84 117 578 560_x000D_PO Box 313 HALL ACT_x000D_P: 02-6230 2455   F:02-6230 2488_x000D_E: [email protected]'
            )
            ws.header_footer.setFooter(
                f'&L&"Arial,Italic"&9&K000000App. A - Contract Variations&R&"Arial,Italic"&9&K000000{self.name}'
            )
            ws.page_margins.top = cm_to_inch(3.4)
            ws.page_margins.bottom = cm_to_inch(2)
            ws.page_margins.left = cm_to_inch(1.2)
            ws.page_margins.right = cm_to_inch(1.1)

        fill = PatternFill(patternType='solid', fgColor=Color('D8E4BC'))

        wb: openpyxl.Workbook = openpyxl.Workbook()

        ws: openpyxl.worksheet.Worksheet = wb.active
        ws.title = 'Claim - TOTAL'
        prepare(ws)

        if self.client is None:
            client_name = ''
            client_first_line_address = ''
            client_second_line_address = ''
        else:
            client_name = self.client.name
            client_first_line_address = self.client.first_line_address
            client_second_line_address = self.client.second_line_address

        if self.superintendent is None:
            superintendent_name = ''
            superintendent_first_line_address = ''
            superintendent_second_line_address = ''
        else:
            superintendent_name = self.superintendent.name
            superintendent_first_line_address = self.superintendent.first_line_address
            superintendent_second_line_address = self.superintendent.second_line_address

        ws['A1'].value = 'Client:      '
        ws['A1'].value += '    '.join([client_name, superintendent_name])

        ws['A1'].font = Font(name='Lao UI', size=10, bold=True)
        ws['A2'].value = '             '
        ws['A2'].value += '    '.join(
            [client_first_line_address, superintendent_first_line_address])
        ws['A3'].value = '             '
        ws['A3'].value += '    '.join(
            [client_second_line_address, superintendent_second_line_address])
        ws['C1'].value = f'Reference #: {arrow.now("Australia/Canberra").format("MM")}-{self.reference_number}'
        ws['C1'].font = Font(name='Lao UI', size=10, bold=True)
        ws['C3'].value = f'Date: {arrow.now("Australia/Canberra").format("DD/MM/YY")}'
        ws['C3'].font = Font(name='Lao UI', size=10)

        ws['A4'].value = 'PROGRESS CLAIM No.'
        ws['A4'].font = Font(name='Lao UI', size=14)
        ws['A4'].fill = fill
        ws['B4'].value = f'Project No: {self.reference_number}'
        ws['B4'].font = Font(name='Lao UI', size=10)
        ws['C4'].value = f'{arrow.now("Australia/Canberra").format("MMMM")}'
        ws['C4'].font = Font(name='Lao UI', size=10)
        ws['B5'].value = 'Approval terms: '
        ws['B5'].font = Font(name='Lao UI', size=10)

        ws['A6'].value = f'Project: {self.name}'
        ws['A6'].font = Font(name='Lao UI', size=11, bold=True)
        ws['B6'].value = 'Contract'
        ws['B6'].font = Font(name='Lao UI', size=10, bold=True)
        ws['B6'].alignment = Alignment(horizontal='center')
        ws['C6'].value = 'Completed'
        ws['C6'].font = Font(name='Lao UI', size=10, bold=True)
        ws['C6'].alignment = Alignment(horizontal='center')
        ws['A7'].value = ''
        ws['B7'].value = 'Value'
        ws['B7'].font = Font(name='Lao UI', size=10, bold=True)
        ws['B7'].alignment = Alignment(horizontal='center')
        ws['C7'].value = 'To Date'
        ws['C7'].font = Font(name='Lao UI', size=10, bold=True)
        ws['C7'].alignment = Alignment(horizontal='center')
        ws.merge_cells('D6:D7')
        ws['D6'].value = '%'
        ws['D6'].font = Font(name='Arial', size=12, bold=True)
        ws['D6'].alignment = Alignment(horizontal='center', vertical='center')

        for row in [6, 7]:
            for column in 'ABCD':
                cell = ws[f'{column}{row}']
                cell.fill = fill

        ws['A1'].border = Border(
            top=Side(border_style='medium', color='FF000000'))
        ws['B1'].border = Border(
            top=Side(border_style='medium', color='FF000000'),
            right=Side(border_style='medium', color='FF000000'),
        )
        ws['C1'].border = Border(
            top=Side(border_style='medium', color='FF000000'))
        ws['D1'].border = Border(
            top=Side(border_style='medium', color='FF000000'))

        ws['B2'].border = Border(
            right=Side(border_style='medium', color='FF000000'))

        ws['A3'].border = Border(
            bottom=Side(border_style='medium', color='FF000000'))
        ws['B3'].border = Border(
            bottom=Side(border_style='medium', color='FF000000'),
            right=Side(border_style='medium', color='FF000000'),
        )

        ws['A4'].border = Border(
            top=Side(border_style='medium', color='FF000000'),
            bottom=Side(border_style='medium', color='FF000000'),
        )
        ws['A6'].border = Border(
            top=Side(border_style='medium', color='FF000000'),
            bottom=Side(border_style='medium', color='FF000000'),
            left=Side(border_style='medium', color='FF000000'),
            right=Side(border_style='medium', color='FF000000'),
        )
        ws['B6'].border = Border(
            top=Side(border_style='medium', color='FF000000'),
            left=Side(border_style='medium', color='FF000000'),
            right=Side(border_style='medium', color='FF000000'),
        )
        ws['C6'].border = Border(
            top=Side(border_style='medium', color='FF000000'),
            left=Side(border_style='medium', color='FF000000'),
            right=Side(border_style='medium', color='FF000000'),
        )
        ws['D6'].border = Border(
            top=Side(border_style='medium', color='FF000000'),
            left=Side(border_style='medium', color='FF000000'),
            right=Side(border_style='medium', color='FF000000'),
        )
        ws['A7'].border = Border(
            top=Side(border_style='medium', color='FF000000'),
            bottom=Side(border_style='medium', color='FF000000'),
            left=Side(border_style='medium', color='FF000000'),
            right=Side(border_style='medium', color='FF000000'),
        )
        ws['B7'].border = Border(
            bottom=Side(border_style='medium', color='FF000000'),
            left=Side(border_style='medium', color='FF000000'),
            right=Side(border_style='medium', color='FF000000'),
        )
        ws['C7'].border = Border(
            bottom=Side(border_style='medium', color='FF000000'),
            left=Side(border_style='medium', color='FF000000'),
            right=Side(border_style='medium', color='FF000000'),
        )
        ws['D7'].border = Border(
            bottom=Side(border_style='medium', color='FF000000'),
            left=Side(border_style='medium', color='FF000000'),
            right=Side(border_style='medium', color='FF000000'),
        )

        row = 8

        def _comp(p):
            return p.id

        self.progress_items.sort(key=_comp)
        for progress_item in self.progress_items:
            ws[f'A{row}'].value = progress_item.name
            ws[f'B{row}'].value = progress_item.contract_value
            ws[f'C{row}'].value = progress_item.completed_value
            ws[f'D{row}'].value = f'= C{row}/B{row}'
            # print(ws['B{}'.format(row)].number_format)
            # print(ws['C{}'.format(row)].number_format)
            row += 1

        for irow in range(8, row):
            for column in 'ABCD':
                cell = ws[f'{column}{irow}']
                cell.font = Font(name='Lao UI', size=9)
                cell.border = Border(
                    left=Side(border_style='thin', color='FF000000'),
                    right=Side(border_style='thin', color='FF000000'),
                )
                if column == 'D':
                    cell.alignment = Alignment(vertical='center',
                                               horizontal='center')

        ws[f'A{row}'].value = 'TOTAL OF CONTRACT'
        ws[f'B{row}'].value = f'=SUM(B{8}:B{row - 1})'
        ws[f'C{row}'].value = f'=SUM(C{8}:C{row - 1})'
        ws[f'D{row}'].value = f'=C{row}/B{row}'

        for column in 'ABCD':
            cell = ws[f'{column}{row}']
            cell.font = Font(name='Lao UI', size=9, bold=True)

        row += 1

        ws[f'A{row}'].value = 'Variations - See Appendix A attached'
        ws[f'B{row}'].value = r"='Appendix A'!D34"
        ws[f'C{row}'].value = r"='Appendix A'!E34"
        ws[f'D{row}'].value = f'=C{row}/B{row}'
        for row in [row - 1, row]:
            for column in 'ABCD':
                cell = ws[f'{column}{row}']
                cell.fill = fill
                cell.border = Border(top=Side(border_style='thin',
                                              color='FF000000'),
                                     bottom=Side(border_style='thin',
                                                 color='FF000000'),
                                     left=Side(border_style='thin',
                                               color='FF000000'),
                                     right=Side(border_style='thin',
                                                color='FF000000'))

        row += 1
        ws[f'A{row}'].value = 'Totals Excluding GST'
        ws[f'B{row}'].value = f'=B{row - 1} + B{row - 2}'
        ws[f'C{row}'].value = f'=C{row - 1} + C{row - 2}'
        ws[f'D{row}'].value = f'=C{row}/B{row}'
        for column in 'BCD':
            cell = ws[f'{column}{row}']
            cell.fill = fill
            cell.border = Border(top=Side(border_style='medium',
                                          color='FF000000'),
                                 bottom=Side(border_style='medium',
                                             color='FF000000'),
                                 left=Side(border_style='medium',
                                           color='FF000000'),
                                 right=Side(border_style='medium',
                                            color='FF000000'))

        row += 1
        ws[f'A{row}'].value = 'Less paid to date'
        ws[f'C{row}'].border = Border(left=Side(border_style='thin',
                                                color='FF000000'),
                                      right=Side(border_style='thin',
                                                 color='FF000000'))

        row += 1
        ws[f'A{row}'].value = 'Value of work completed this period'
        ws[f'C{row}'].value = f'=C{row - 2} - C{row - 1}'
        ws[f'C{row}'].border = Border(left=Side(border_style='thin',
                                                color='FF000000'),
                                      right=Side(border_style='thin',
                                                 color='FF000000'))

        row += 1
        ws[f'A{row}'].value = 'GST this period'
        ws[f'C{row}'].value = f'=C{row - 1} * 10%'
        ws[f'C{row}'].border = Border(left=Side(border_style='thin',
                                                color='FF000000'),
                                      right=Side(border_style='thin',
                                                 color='FF000000'))
        for irow in range(row - 3, row + 1):
            for column in 'ABCD':
                cell = ws[f'{column}{irow}']
                cell.font = Font(name='Lao UI', size=9)

        row += 1
        ws[f'A{row}'].value = 'TOTAL PAYABLE THIS CLAIM'
        ws[f'A{row}'].font = Font(name='Lao UI', size=9, bold=True)
        ws[f'C{row}'].value = f'=C{row - 2} + C{row - 1}'
        ws[f'C{row}'].font = Font(name='Lao UI', size=9, bold=True)
        ws[f'C{row}'].border = Border(top=Side(border_style='medium',
                                               color='FF000000'),
                                      bottom=Side(border_style='medium',
                                                  color='FF000000'),
                                      left=Side(border_style='medium',
                                                color='FF000000'),
                                      right=Side(border_style='medium',
                                                 color='FF000000'))

        for irow in range(8, row + 1):
            ws[f'B{irow}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
            ws[f'C{irow}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
            ws[f'D{irow}'].number_format = r'0.00%'

        ws.column_dimensions['A'].width = 40
        ws.column_dimensions['B'].width = 15
        ws.column_dimensions['C'].width = 15
        ws.column_dimensions['D'].width = 8
        ws.sheet_view.view = 'pageLayout'

        ws: openpyxl.worksheet.Worksheet = wb.create_sheet()
        ws.title = 'Appendix A'
        prepare(ws)

        ws.merge_cells('A1:D1')
        ws['A1'].style.alignment.wrap_text = True
        ws['A1'].value = f'{self.name}\nJOB #: {self.reference_number}'
        ws['A1'].fill = fill
        ws['A1'].font = Font(name='Lao UI', size=11, bold=True)

        ws.merge_cells('A3:B3')
        ws['A3'].value = "Appendix 'A' - Contract variations"
        ws['A3'].font = Font(name='Lao UI', size=10, bold=True)
        # ws['E3'].value = '=TODAY()'
        ws['E3'].value = datetime.today()
        ws['E3'].number_format = 'mm-dd-yy'
        ws['E3'].font = Font(name='Lao UI', size=9)

        ws['A5'].value = 'NO.'
        ws['B5'].value = 'ITEM'
        ws['C5'].value = 'PENDING'
        ws['D5'].value = 'APPROVED'
        ws['E5'].value = 'COMPLETED'
        for cell in ['A5', 'B5', 'C5', 'D5', 'E5']:
            ws[cell].font = Font(name='Lao UI', size=10, bold=True)
            ws[cell].alignment = Alignment(horizontal='center')
            ws[cell].fill = fill
            ws[cell].border = Border(top=Side(border_style='medium',
                                              color='FF000000'),
                                     bottom=Side(border_style='medium',
                                                 color='FF000000'),
                                     left=Side(border_style='medium',
                                               color='FF000000'),
                                     right=Side(border_style='medium',
                                                color='FF000000'))

        row = 6
        self.variations.sort(key=lambda v: v.vid)
        for variation in self.variations:
            ws['A' + str(row)].value = row - 5
            ws['B' + str(row)].value = variation.description

            if variation.pending or variation.approved:
                if variation.pending:
                    column = 'C'
                else:
                    column = 'D'
                ws[column + str(row)].value = variation.amount
            elif variation.declined:
                ws['B' + str(
                    row
                )].value = f"{variation.description} (declined {variation.amount})"
                ws['C' + str(row)].value = 0.0

            if variation.completed:
                ws['E' + str(row)].value = variation.amount

            row += 1

        while row < 34:
            for column in ['A', 'B', 'C', 'D', 'E']:
                cell = ws[column + str(row)]
                cell.border = Border(left=Side(border_style='thin',
                                               color='FF000000'),
                                     right=Side(border_style='thin',
                                                color='FF000000'))
            row += 1

        for index in range(6, row):
            for column in ['A', 'B', 'C', 'D', 'E']:
                cell = ws[f'{column}{index}']

                cell.border = Border(left=Side(border_style='thin',
                                               color='FF000000'),
                                     right=Side(border_style='thin',
                                                color='FF000000'))

                if column == 'A':
                    cell.font = Font(name='Lao UI', size=10, bold=True)
                    cell.alignment = Alignment(vertical='center',
                                               horizontal='center')
                else:
                    cell.font = Font(name='Lao UI', size=9)
                    if column == 'B':
                        cell.alignment = Alignment(vertical='center',
                                                   horizontal='left')
                    else:
                        cell.alignment = Alignment(vertical='center')
                        cell.number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'

        ws.merge_cells(f'A{row}:B{row}')
        ws[f'A{row}'].value = 'TOTALS'
        ws[f'C{row}'].value = f'=SUM(C6:C{row - 1})'
        ws[f'D{row}'].value = f'=SUM(D6:D{row - 1})'
        ws[f'E{row}'].value = f'=SUM(E6:E{row - 1})'

        for column in 'ABCDE':
            cell = ws[f'{column}{row}']
            cell.alignment = Alignment(vertical='center', horizontal='center')
            cell.fill = fill
            cell.border = Border(left=Side(border_style='medium',
                                           color='FF000000'),
                                 right=Side(border_style='medium',
                                            color='FF000000'),
                                 top=Side(border_style='medium',
                                          color='FF000000'),
                                 bottom=Side(border_style='medium',
                                             color='FF000000'))
            if column != 'A':
                cell.number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'

        ws.row_dimensions[1].height = 30
        for row in range(len(self.variations)):
            ws.row_dimensions[6 + row].height = 30
        ws.column_dimensions['A'].width = 5
        ws.column_dimensions['B'].width = 40
        ws.column_dimensions['C'].width = 13
        ws.column_dimensions['D'].width = 13
        ws.column_dimensions['E'].width = 13

        ws.cell('A1').alignment = Alignment(wrapText=True)
        ws.sheet_view.view = 'pageLayout'

        for index, variation in enumerate(self.variations):
            new_ws: openpyxl.worksheet.Worksheet = wb.create_sheet()
            prepare(new_ws)
            new_ws.title = f'V{index + 1}'

            new_ws.merge_cells('A1:H1')
            new_ws['A1'].value = 'CONTRACT VARIATION'
            new_ws['A1'].fill = fill
            new_ws['A1'].font = Font(name='Lao UI', size=16, bold=True)
            new_ws['A1'].alignment = Alignment(vertical='center',
                                               horizontal='center')

            for column in 'ABCDEFGH':
                new_ws[column + '1'].border = Border(
                    top=Side(border_style='medium', color='FF000000'),
                    bottom=Side(border_style='medium', color='FF000000'),
                    left=Side(border_style='medium', color='FF000000'),
                    right=Side(border_style='medium', color='FF000000'))

            new_ws.merge_cells('B3:H3')
            new_ws['B3'].value = f'PROJECT: {self.name}'
            new_ws['B3'].fill = fill
            new_ws['B3'].font = Font(name='Lao UI', size=12, bold=True)
            for column in 'BCDEFGH':
                new_ws[f'{column}3'].border = Border(
                    top=Side(border_style='thin', color='FF000000'),
                    bottom=Side(border_style='thin', color='FF000000'))

            new_ws['B5'].value = 'VARIATION NO:'
            new_ws['B5'].fill = fill
            new_ws['C5'].fill = fill
            new_ws['B5'].font = Font(name='Lao UI', size=12, bold=True)
            new_ws.merge_cells('D5:E5')
            new_ws['D5'].value = index + 1
            new_ws['D5'].fill = fill
            new_ws['D5'].font = Font(name='Lao UI', size=14, bold=True)
            new_ws.merge_cells('G5:H5')
            new_ws['G5'].value = f'TPC REF: {self.reference_number}'
            new_ws['G5'].font = Font(name='Lao UI', size=14, bold=True)
            new_ws['G5'].alignment = Alignment(vertical='center',
                                               horizontal='left')
            for column in 'BCDE':
                cell = new_ws[column + str(5)]
                cell.border = Border(top=Side(border_style='thin',
                                              color='FF000000'),
                                     bottom=Side(border_style='thin',
                                                 color='FF000000'))

            for column in 'BCDEFGH':
                cell = new_ws[column + str(6)]
                cell.border = Border(
                    bottom=Side(border_style='medium', color='FF000000'))

            row = 7
            if len(variation.items) > 1:
                new_ws.merge_cells(f'B{row}:G{row}')
                new_ws[f'B{row}'].value = variation.description
                new_ws[f'B{row}'].font = Font(name='Lao UI',
                                              size=11,
                                              bold=True)
                new_ws[f'B{row}'].alignment = Alignment(vertical='center')
                row += 1

            for item in variation.items:
                new_ws.merge_cells(f'B{row}:G{row}')
                new_ws[f'B{row}'].value = item.description
                new_ws[f'B{row}'].font = Font(name='Lao UI',
                                              size=11,
                                              bold=True)
                new_ws[f'B{row}'].alignment = Alignment(vertical='center')

                new_ws[f'H{row}'].value = item.amount
                new_ws[f'H{row}'].font = Font(name='Lao UI',
                                              size=11,
                                              bold=True)
                new_ws[f'H{row}'].alignment = Alignment(vertical='center')
                new_ws[
                    f'H{row}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'

                new_ws.row_dimensions[row].height = 40

                row += 1

            while row < 13:
                new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)
                new_ws[f'H{row}'].font = Font(name='Lao UI', size=11)
                row += 1

            for column in 'BCDEFGH':
                cell = new_ws[column + str(row - 1)]
                cell.border = Border(
                    bottom=Side(border_style='thin', color='FF000000'))

            new_ws['B' + str(row)].value = 'Value of work'
            new_ws['H' + str(row)].value = f'=SUM(H7:H{row - 1})'
            new_ws['B' + str(row)].font = Font(name='Lao UI', size=11)
            new_ws['H' + str(row)].font = Font(name='Lao UI', size=11)
            row += 1

            new_ws['B' +
                   str(row)].value = f'Add OH/Profit {self.margin * 100}%'
            new_ws['H' +
                   str(row)].value = f'=H{row - 1} * {self.margin * 100}%'
            new_ws['B' + str(row)].font = Font(name='Lao UI', size=11)
            new_ws['H' + str(row)].font = Font(name='Lao UI', size=11)

            if self.admin_fee is not None and self.admin_fee != 0:
                row += 1
                new_ws['B' + str(row)].value = 'Fixed administration fee'
                new_ws['H' + str(row)].value = self.admin_fee
                new_ws['B' + str(row)].font = Font(name='Lao UI', size=11)
                new_ws['H' + str(row)].font = Font(name='Lao UI', size=11)

            for column in 'BCDEFGH':
                new_ws[column + str(row)].border = Border(
                    bottom=Side(border_style='thin', color='FF000000'))
            row += 1

            new_ws['B' + str(row)].value = 'Subtotal'
            if self.admin_fee is None:
                new_ws['H' + str(row)] = f'=H{row - 2} + H{row - 1}'
            else:
                new_ws['H' +
                       str(row)] = f'=H{row - 3} + H{row - 2} + H{row - 1}'
            new_ws['H' + str(row)].font = Font(name='Lao UI',
                                               size=11,
                                               bold=True)
            row += 1

            new_ws['B' + str(row)] = 'Add GST'
            new_ws['H' + str(row)].value = f'=H{row - 1} * 0.1'
            new_ws['H' + str(row)].font = Font(name='Lao UI',
                                               size=11,
                                               underline='singleAccounting')
            for column in 'BCDEFGH':
                new_ws[column + str(row)].border = Border(
                    bottom=Side(border_style='medium', color='FF000000'))
            row += 1

            new_ws.merge_cells(f'B{row}:C{row}')
            for column in 'BCDEFGH':
                new_ws[f'{column}{row}'].fill = fill
            new_ws['B' + str(row)] = 'TOTAL'
            new_ws['H' + str(row)].value = f'=H{row - 1} + H{row - 2}'
            new_ws['H' + str(row)].font = Font(name='Lao UI',
                                               size=11,
                                               bold=True)

            for idx in range(7, row + 1):
                new_ws['H' + str(
                    idx
                )].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'

            row += 4
            new_ws[f'B{row}'].value = 'Variation Prepared By:'
            new_ws[f'G{row}'].value = 'Variation Prepared For:'
            new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)
            new_ws[f'G{row}'].font = Font(name='Lao UI', size=11)

            row += 5
            new_ws[f'B{row}'].value = 'FOR'
            new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)

            new_ws[f'B{row + 1}'].value = 'Total Project Construction Pty Ltd'
            new_ws[f'B{row + 1}'].font = Font(name='Lao UI', size=11)

            new_ws[f'G{row}'].value = 'FOR'
            new_ws[f'G{row}'].font = Font(name='Lao UI', size=11)

            new_ws[f'G{row + 1}'].value = variation.prepared_for
            new_ws[f'G{row + 1}'].font = Font(name='Lao UI', size=11)

            row += 3
            new_ws[f'B{row}'].value = 'Date:'
            # new_ws['C{}'.format(row)].value = '=TODAY()'
            new_ws[f'C{row}'].value = datetime.today()
            new_ws[f'C{row}'].number_format = 'mm-dd-yy'
            new_ws[f'G{row}'].value = 'Date:'
            new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)
            new_ws[f'C{row}'].font = Font(name='Lao UI', size=11)
            new_ws[f'G{row}'].font = Font(name='Lao UI', size=11)

            new_ws.row_dimensions[1].height = 60
            new_ws.row_dimensions[3].height = 40
            new_ws.column_dimensions['A'].width = 3
            new_ws.column_dimensions['D'].width = 6
            new_ws.column_dimensions['F'].width = 4
            new_ws.column_dimensions['H'].width = 12
            new_ws.sheet_view.view = 'pageLayout'

        fn = self.name + '.xlsx'
        wb.save('generated/' + fn)
        return fn
コード例 #12
0
def Creat_excel(excel_name):
    pwd = os.getcwd()
    print(pwd)
    wb = openpyxl.Workbook()  #创建文件对象
    wb.save(filename=excel_name)  #保存文件,名字为excel01
    print(excel_name + "新建成功")
コード例 #13
0
def output_excel(categories, count_eval, avg_iou, detection, unnecessary,
                 pre_label_list, recalls, precisions):
    size = len(categories)

    # エクセル作成
    wb = px.Workbook()

    # シート作成
    ws_1 = wb.active
    ws_1 = wb.create_sheet(title='推論結果')

    # セル結合
    ws_1.merge_cells(start_row=1, start_column=1, end_row=3, end_column=1)
    ws_1.merge_cells(start_row=1,
                     start_column=2,
                     end_row=1,
                     end_column=2 + size)
    ws_1.merge_cells(start_row=2, start_column=2, end_row=3, end_column=2)
    ws_1.merge_cells(start_row=1,
                     start_column=3 + size,
                     end_row=1,
                     end_column=4 + size * 2)
    ws_1.merge_cells(start_row=2,
                     start_column=3,
                     end_row=2,
                     end_column=2 + size)
    ws_1.merge_cells(start_row=2,
                     start_column=3 + size,
                     end_row=2,
                     end_column=2 + size * 2)
    ws_1.merge_cells(start_row=2,
                     start_column=3 + size * 2,
                     end_row=3,
                     end_column=3 + size * 2)
    ws_1.merge_cells(start_row=2,
                     start_column=4 + size * 2,
                     end_row=3,
                     end_column=4 + size * 2)
    ws_1.merge_cells(start_row=1,
                     start_column=5 + size * 2,
                     end_row=3,
                     end_column=5 + size * 2)
    ws_1.merge_cells(start_row=1,
                     start_column=6 + size * 2,
                     end_row=3,
                     end_column=6 + size * 2)
    ws_1.merge_cells(start_row=1,
                     start_column=7 + size * 2,
                     end_row=3,
                     end_column=7 + size * 2)
    ws_1.merge_cells(start_row=1,
                     start_column=8 + size * 2,
                     end_row=3,
                     end_column=8 + size * 2)

    # ヘッダーを記述
    ws_1.cell(row=1, column=1).value = 'ラベル名'
    ws_1.cell(row=1, column=2).value = '正解ボックス'
    ws_1.cell(row=1, column=3 + size).value = '推論ボックス'
    ws_1.cell(row=2, column=2).value = '未検出'
    ws_1.cell(row=2, column=3).value = '検出'
    ws_1.cell(row=2, column=3 + size).value = '検出'
    ws_1.cell(row=2, column=3 + size * 2).value = '過検出'
    ws_1.cell(row=2, column=4 + size * 2).value = '不要'
    ws_1.cell(row=1, column=5 + size * 2).value = '平均IOU'
    ws_1.cell(row=1, column=6 + size * 2).value = '再現率(Recall)'
    ws_1.cell(row=1, column=7 + size * 2).value = '適合率(Precision)'
    ws_1.cell(row=1, column=8 + size * 2).value = 'F値(F-measure)'

    # ラベル名を記述
    for i, label in enumerate(categories):
        ws_1.cell(row=4 + i, column=1).value = label
        ws_1.cell(row=3, column=3 + i).value = label
        ws_1.cell(row=3, column=3 + size + i).value = label

    # 評価データを記述
    for i, box in enumerate(count_eval):
        for j, num in enumerate(box):
            ws_1.cell(row=4 + i, column=2 + j).value = num

    # 平均IOUを記述
    for i, iou in enumerate(avg_iou):
        ws_1.cell(row=4 + i, column=5 + size * 2).value = iou

    #再現率、適合率、F値を記述
    for i in range(len(recalls)):
        ws_1.cell(row=4 + i, column=6 + size * 2).value = recalls[i]
        ws_1.cell(row=4 + i, column=7 + size * 2).value = precisions[i]
        if recalls[i] != 0 and precisions[i] != 0:
            fmeasure = 2 * recalls[i] * precisions[i] / (recalls[i] +
                                                         precisions[i])
        else:
            fmeasure = 0
        ws_1.cell(row=4 + i, column=8 + size * 2).value = fmeasure

    # アルファベットのリストを生成
    alphabet_list = [chr(i) for i in range(97, 105 + size * 2)]

    # セル幅を広めに修正
    # for i, c in enumerate(alphabet_list):
    #    ws_1.column_dimensions[c].width = 14

    # 文字位置を変更
    for i in range(1, 9 + size * 2):
        for j in range(1, size + 4):
            ws_1.cell(row = j, column = i).alignment = \
                    Alignment(horizontal = 'center', vertical = 'center')

    # 枠線を生成
    for i in range(1, 9 + size * 2):
        for j in range(1, size + 4):
            ws_1.cell(row = j, column = i).border = \
                borders.Border(top    = borders.Side(style = borders.BORDER_THIN, color = '000000'),
                               left   = borders.Side(style = borders.BORDER_THIN, color = '000000'),
                               right  = borders.Side(style = borders.BORDER_THIN, color = '000000'),
                               bottom = borders.Side(style = borders.BORDER_THIN, color = '000000'))

    # フォントを変更
    font = Font(name='メイリオ')

    for i in range(1, 9 + size * 2):
        for j in range(1, size + 4):
            ws_1.cell(row=j, column=i).font = font

    # シート作成
    ws_2 = wb.create_sheet(title='IOU詳細')

    # セル結合
    ws_2.merge_cells(start_row=1, start_column=7, end_row=2, end_column=7)
    ws_2.merge_cells(start_row=1, start_column=8, end_row=1, end_column=10)
    ws_2.merge_cells(start_row=1, start_column=11, end_row=1, end_column=13)

    # ヘッダーを記述
    ws_2.cell(row=1, column=1).value = '画像'
    ws_2.cell(row=1, column=2).value = '正解ラベル'
    ws_2.cell(row=1, column=3).value = '推論ラベル'
    ws_2.cell(row=1, column=4).value = 'IOU'
    ws_2.cell(row=1, column=5).value = 'スコア'

    ws_2.cell(row=1, column=7).value = 'ラベル名'
    ws_2.cell(row=1, column=8).value = '検出'
    ws_2.cell(row=1, column=11).value = '過検出'

    ws_2.cell(row=2, column=8).value = '0.6未満'
    ws_2.cell(row=2, column=9).value = '0.6以上0.7未満'
    ws_2.cell(row=2, column=10).value = '0.7以上'
    ws_2.cell(row=2, column=11).value = '0.6未満'
    ws_2.cell(row=2, column=12).value = '0.6以上0.7未満'
    ws_2.cell(row=2, column=13).value = '0.7以上'

    # データを記述
    for i, data in enumerate(detection):
        ws_2.cell(row=2 + i, column=1).value = data[0]
        ws_2.cell(row=2 + i, column=2).value = data[1]
        ws_2.cell(row=2 + i, column=3).value = data[2]
        ws_2.cell(row=2 + i, column=4).value = data[5]
        ws_2.cell(row=2 + i, column=5).value = data[6]

    cnt = 0

    for i, x in enumerate(pre_label_list):
        switch = 0

        for j, y in enumerate(detection):
            if (x[0] == y[0] and x[1] == y[2] and x[6] == y[4]
                    and x[7] == y[6]):
                switch = 1

        if (switch == 0):
            ws_2.cell(row=len(detection) + 2 + cnt, column=1).value = x[0]
            ws_2.cell(row=len(detection) + 2 + cnt, column=3).value = x[1]
            ws_2.cell(row=len(detection) + 2 + cnt, column=5).value = x[7]

            cnt += 1

    iou_split = [[0 for i in range(7)] for j in range(len(categories))]

    for i, label in enumerate(categories):
        iou_split[i][0] = label

        for data in detection:
            if (label == data[1] and data[1] == data[2] and data[5] < 0.6):
                iou_split[i][1] += 1

            elif (label == data[1] and data[1] == data[2] and data[5] >= 0.7):
                iou_split[i][3] += 1

            elif (label == data[1] and data[1] == data[2] and data[5] >= 0.6
                  and data[5] < 0.7):
                iou_split[i][2] += 1

            if (label == data[1] and data[1] != data[2] and data[5] < 0.6):
                iou_split[i][4] += 1

            elif (label == data[1] and data[1] != data[2] and data[5] >= 0.7):
                iou_split[i][6] += 1

            elif (label == data[1] and data[1] != data[2] and data[5] >= 0.6
                  and data[5] < 0.7):
                iou_split[i][5] += 1

    # データを記述
    for i, data in enumerate(iou_split):
        ws_2.cell(row=3 + i, column=7).value = data[0]
        ws_2.cell(row=3 + i, column=8).value = data[1]
        ws_2.cell(row=3 + i, column=9).value = data[2]
        ws_2.cell(row=3 + i, column=10).value = data[3]
        ws_2.cell(row=3 + i, column=11).value = data[4]
        ws_2.cell(row=3 + i, column=12).value = data[5]
        ws_2.cell(row=3 + i, column=13).value = data[6]

    # アルファベットのリストを生成
    alphabet_list = [chr(i) for i in range(97, 110)]

    # セル幅を広めに修正
    for i, c in enumerate(alphabet_list):
        ws_2.column_dimensions[c].width = 14

    # 文字位置を変更
    for i in range(1, 6):
        ws_2.cell(row = 1, column = i).alignment = \
            Alignment(horizontal = 'center', vertical = 'center')

    for i in range(6, 14):
        for j in range(1, size + 3):
            ws_2.cell(row = j, column = i).alignment = \
                Alignment(horizontal = 'center', vertical = 'center')

    # 枠線を生成
    for i in range(7, 14):
        for j in range(1, size + 3):
            ws_2.cell(row = j, column = i).border = \
                borders.Border(top    = borders.Side(style = borders.BORDER_THIN, color = '000000'),
                               left   = borders.Side(style = borders.BORDER_THIN, color = '000000'),
                               right  = borders.Side(style = borders.BORDER_THIN, color = '000000'),
                               bottom = borders.Side(style = borders.BORDER_THIN, color = '000000'))

    # フォントを変更
    for i in range(1, 14):
        for j in range(1, len(detection) + 1):
            ws_2.cell(row=j, column=i).font = font

    # シート削除
    wb.remove(wb['Sheet'])

    # エクセル保存
    save_excel_path = os.path.join(args.pre_xml, 'evaluation.xlsx')
    wb.save(save_excel_path)

    print('Successful Completion ({})'.format(save_excel_path))
コード例 #14
0
 def __init__(self, record):
     self.rb = openpyxl.Workbook()
     self.records_for_report = record
     self.api_analysis_record = self.create_analysis(record)
コード例 #15
0
def generate_analysis_report(result: dict,
                             file_path: str,
                             analyzer_name_dict: dict = {},
                             stock_name_dict: dict = {}):
    wb = openpyxl.Workbook()
    ws_score = wb.active
    ws_score.title = 'Score'
    ws_comments = wb.create_sheet('Comments')

    ws_score['A1'] = 'Securities\\Analyzer'
    ws_comments['A1'] = 'Securities\\Analyzer'

    ROW_OFFSET = 2
    total_score = []

    column = 1
    for analyzer_uuid, analysis_result in result.items():
        # Write securities column
        if column == 1:
            # The first run. Init the total score list here.
            # Flaw: The first column of result should be the full one. Otherwise the index may out of range.
            total_score = [[] for _ in range(0, len(analysis_result))
                           ]  # 100: Pass; 0: Fail; 50: Pass with None

            row = 2
            col = index_to_excel_column_name(column)
            for r in analysis_result:
                securities_name = stock_name_dict.get(r.securities, '')
                display_text = (
                    r.securities + ' | ' +
                    securities_name) if securities_name != '' else r.securities
                ws_score[col + str(row)] = display_text
                ws_comments[col + str(row)] = display_text
                row += 1
            column = 2

        # Write analyzer name
        row = 1
        col = index_to_excel_column_name(column)
        analyzer_name = analyzer_name_dict.get(analyzer_uuid, analyzer_uuid)
        ws_score[col + str(row)] = analyzer_name
        ws_comments[col + str(row)] = analyzer_name

        # Write scores
        row = ROW_OFFSET
        for r in analysis_result:
            ws_score[col + str(row)] = r.score if r.score is not None else '-'
            ws_comments[col + str(row)] = r.reason

            if r.score is not None:
                total_score[row - ROW_OFFSET].append(r.score)
            fill_style = __score_to_fill_style(r.score)

            ws_score[col + str(row)].fill = fill_style
            ws_comments[col + str(row)].fill = fill_style
            row += 1
        column += 1

    # Write total score
    row = 1
    col = index_to_excel_column_name(column)

    for score in total_score:
        if row == 1:
            ws_score[col + str(row)] = 'Total Result'
            row = 2

        if len(score) > 0:
            min_score = min(score)
            # avg_score = sum(score) / float(len(score))
        else:
            min_score = None
            # avg_score = None
        fill_text = __score_to_fill_text(min_score)
        fill_style = __score_to_fill_style(min_score)

        ws_score[col + str(row)] = fill_text
        ws_comments[col + str(row)] = fill_text

        ws_score[col + str(row)].fill = fill_style
        ws_comments[col + str(row)].fill = fill_style

        row += 1

    # Write file
    wb.save(file_path)
コード例 #16
0
 def __init__(self):
     #Before this class/function comes into action
     #	have every necessary field already generated
     self.wb = oxl.Workbook()
コード例 #17
0
import requests
import openpyxl

client_id = '77dhvEQYUb57IC1W2u8j'
client_secret = '2Bjqxuk_Pn'
start, num = 1, 0

excel_file = openpyxl.Workbook()
excel_sheet = excel_file.active
excel_sheet.column_dimensions['B'].width = 100
excel_sheet.column_dimensions['C'].width = 100
excel_sheet.append(['랭킹', '제목', '링크'])

for index in range(10):
    start_number = start + (index * 100)
    naver_open_api = 'https://openapi.naver.com/v1/search/shop.json?query=샤오미&display=100&start=' + str(
        start_number)
    header_params = {
        "X-Naver-Client-Id": client_id,
        "X-Naver-Client-Secret": client_secret
    }
    res = requests.get(naver_open_api, headers=header_params)

    if res.status_code == 200:
        data = res.json()
        for item in data['items']:
            num += 1
            #print(num, item['title'], item['link'])
            excel_sheet.append([num, item['title'], item['link']])
    else:
        print("Error Code:", res.status_code)
コード例 #18
0
import pandas as pd
from pandas import DataFrame

print(os.path.abspath('.'))  # 打开文件夹统计xlsx个数
n = [
    i for i in os.listdir('.')  # 循环查找"."文件
    if os.path.isfile(i) and os.path.splitext(i)[1] == '.xlsx'
]  # 如果是.xlsx计数 加一得到这些文件 放入字典中【】
print(n)
print(len(n))
print(n[0])
wb0 = openpyxl.load_workbook(filename=n[0])  # 打开文件judge1
ws0 = wb0.active
# 第一个评分judge1表作为基准表
print(ws0.cell(1, 1).value)
wb1 = openpyxl.Workbook().save("list.xlsx")
wb1 = openpyxl.load_workbook("list.xlsx")
ws1 = wb1.active
# 两个表格同时使用 将基准表的值赋给最终总表
for i in range(1, 12):  # 11为列数,共统计11列
    ws1.cell(1, i).value = ws0.cell(1, i).value
    ws1.cell(2, i).value = ws0.cell(2, i).value
# 复制第一,二行赋值
wb1.save("list.xlsx")
# for i in range(1, len(n)):
#     print("judge"+str(i))
#     ws1.cell(1, i+1).value = "judge"+str(i)
# # 赋值judge1,judge2,judge3表头
# for i in range(2, ws0.max_row+1):
#     ws1.cell(i, 2).value = ws0.cell(i, 2).value
コード例 #19
0
#from sklearn.tree import DecisionTreeClassifier
#clf = DecisionTreeClassifier()
#clf.fit(X_train,Y_train)

from sklearn.ensemble import RandomForestClassifier
clf2 = RandomForestClassifier(n_estimators=1500, random_state=0)
clf2.fit(X_train, Y_train)

#Y_pred = clf.predict(X_test)
Y_pred2 = clf2.predict(X_test)

Y_pred2_list = Y_pred2.tolist()
##type(list(Y_pred2[0]))
import openpyxl as xl

wb = xl.Workbook()
sheet = wb.active
#sheet['A1'].value = 1
from openpyxl.utils import get_column_letter
#get_column_letter(1)

j = 1
for i in Y_pred2_list:
    sheet['A' + str(j)] = j
    sheet['B' + str(j)] = i
    j = j + 1

wb.save('preds2.xlsx')

#import csv
#
コード例 #20
0
def run(url):
    global wb, ws, driver

    # Init start time
    start = datetime.datetime.now().replace(microsecond=0)

    # Init Log
    log_file = open(LOG_FILE, 'w+')
    log_file.close()

    # Init Driver
    LOGGER.setLevel(logging.DEBUG)
    driver = init_driver()

    if driver is None:
        sys.exit()

    # Init Excel
    wb = openpyxl.Workbook()
    ws = wb.active

    # TODO: Check if max page exists

    # Init Scraping
    original_url = url
    proceed = True
    try:
        driver.get(original_url)
        title = driver.title
    except Exception as e:
        log("ERROR Failed openning " + original_url + " - " +
            str(e).split(os.linesep, 1)[0])
        close_driver()
        proceed = False

    if proceed is True:

        # Get all URLs
        ads_urls = []
        for page_index in range(1, args.pages + 1):

            if driver is None:
                driver = init_driver()
                if driver is None:
                    log("WARN  Failed to initialize driver, page " +
                        page_index + " will be skipped")
                    continue

            index = original_url.rfind('/')
            url = original_url[:index] + '/page-' + str(
                page_index) + original_url[index:]

            page_urls = get_page_ads(url, page_index)

            if len(page_urls) == 0:
                continue

            for ad_url in page_urls:
                if ad_url not in ads_urls:
                    ads_urls.append(ad_url)

            if (page_index) % 10 == 0 or page_index == args.pages:
                close_driver()

        # Parse ads
        total = len(ads_urls)
        log("INFO  * Total of " + str(total) + " ads will be scraped")

        for index, ad_url in enumerate(ads_urls):

            if driver is None:
                driver = init_driver()

            log("INFO  Parsing " + str(index + 1) + "/" + str(total) + ": " +
                ad_url)
            try:
                parse_ad(ad_url)
            except selenium.common.exceptions.TimeoutException as e:
                log("ERROR Failed to load page " + str(page_index) +
                    " before time out elapsed, closing driver...")
                close_driver()
            except Exception as e:
                log("ERROR Failed to parse " + ad_url + " - (" +
                    str(e).split(os.linesep, 1)[0] + ")")

            if (index + 1) % 20 == 0 or index + 1 == total:
                close_driver()

        # Save Results to Excel
        try:
            if os.path.isfile(RESULTS_FILE):
                os.remove(RESULTS_FILE)
            wb.save(RESULTS_FILE)
            log("INFO  Results saved as " + RESULTS_FILE + " in " + DIR_PATH)
        except Exception as e:
            log("ERROR Failed to save results " + str(e))

    # Calculate Duration
    end = datetime.datetime.now().replace(microsecond=0)

    # Send eMail
    if args.mail is True and proceed is True:

        if can_send_email():
            from_name = "Kijiji Scrapper"
            if os.path.isfile(RESULTS_FILE):
                message = title + os.linesep
                message += original_url + os.linesep
                message += "Duration - " + str(end - start) + os.linesep
                send_mail(from_name, args.recipients,
                          "Kijiji Scraping Results", message,
                          [RESULTS_FILE, LOG_FILE], args.smtp_server,
                          args.smtp_server_port, args.smtp_server_username,
                          args.smtp_server_password, True)
            else:
                message = "Operation Failed." + os.linesep
                message += "Logs Attached" + os.linesep
                message += "Duration - " + str(end - start)
                send_mail(from_name, args.recipients,
                          "Kijiji Scraping Results - Failed", "Logs attached",
                          [LOG_FILE], args.smtp_server, args.smtp_server_port,
                          args.smtp_server_username, args.smtp_server_password,
                          True)
            log("INFO  Sent results by mail to: " + ','.join(args.recipients))
        else:
            log("ERROR Can not send eMail")

    log("INFO  Script finsihed in " + str(end - start))

    # Cleanup
    if (args.mail):
        if os.path.exists(RESULTS_FILE):
            os.remove(RESULTS_FILE)
        if os.path.exists(LOG_FILE):
            os.remove(LOG_FILE)

    print("Bye.")
コード例 #21
0
    def export_as_xslx(modeladmin, request, queryset):

        # get fields to export
        opts = modeladmin.model._meta
        if not fields:
            field_names = [field.name for field in opts.fields]
        else:
            field_names = fields

        # Create a response header
        response = HttpResponse(
            content_type=
            'application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response[
            'Content-Disposition'] = 'attachment; filename={}.xlsx'.format(
                str(opts).replace('.', '_'))

        # Create a new workbook
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = str(opts).replace('.', '_')

        # Write the header (if desired)
        if header:

            def makeHeaderCell(field):
                c = cell.Cell(ws, value=field)
                c.font = styles.Font(bold=True)
                return c

            ws.append([makeHeaderCell(field) for field in field_names])

        # Write each of the rows
        for row in queryset.values_list(*field_names):

            def makeCell(prop):
                try:
                    return to_excel(prop)
                except:
                    return str(prop)

            ws.append([makeCell(c) for c in row])

        # adjust column widths
        # adapted from https://stackoverflow.com/a/39530676
        for col in ws.columns:
            max_length = 0
            column = col[0].column  # Get the column name
            for c in col:
                try:
                    if len(str(c.value)) > max_length:
                        max_length = len(c.value)
                except:
                    pass
            adjusted_width = (max_length + 2) * 1.2
            ws.column_dimensions[column].width = adjusted_width

        # and export
        wb.save(response)
        return response
コード例 #22
0
ファイル: infer_schema.py プロジェクト: rvuillez/pydqc
def infer_schema(data,
                 fname,
                 output_root='',
                 sample_size=1.0,
                 type_threshold=0.5,
                 n_jobs=1,
                 base_schema=None):
    """
    Infer data types for all columns for the input table

    Parameters
    ----------
    data: pandas DataFrame
        data table to infer
    fname: string
        the output file name
    output_root: string, default=''
        the root directory for the output file
    sample_size: int or float(<= 1.0), default=1.0
        int: number of sample rows to infer the data type (useful for large tables)
        float: sample size in percentage
    type_threshold: float(<= 1.0), default=0.5
        threshold for inferring data type
    n_jobs: int, default=1
        the number of jobs to run in parallel
    base_schema: pandas DataFrame, default=None
        data schema to base on
    """

    # check sample_size
    if sample_size > 1:
        if int(sample_size) != sample_size:
            raise ValueError(
                'sample_size: only accept integer when it is > 1.0')
        if sample_size > data.shape[0]:
            print("sample_size: %d is larger than the data size: %d" %
                  (sample_size, data.shape[0]))

    # check output_root
    if output_root != '':
        if not os.path.isdir(output_root):
            raise ValueError('output_root: root not exists')

    # check type_threshold
    if (type_threshold <= 0) or (type_threshold > 1):
        raise ValueError('type_threshold: should be in (0, 1]')

    # check base_schema
    if base_schema is not None:
        if type(base_schema) != pd.core.frame.DataFrame:
            raise ValueError('base_schema: only accept pandas DataFrame')

    # open a new workbook to store all result
    wb = openpyxl.Workbook()
    ws = wb['Sheet']
    ws.title = 'schema'

    # calculate sample size
    if sample_size <= 1.0:
        sample_size = int(data.shape[0] * sample_size)

    # dictionary to store dropna sample data values
    data_dropna_sample_values = {}
    for col in data.columns.values:
        if len(data[col].dropna()) <= sample_size:
            data_dropna_sample_values[col] = data[col].dropna().values
        else:
            data = data.sample(sample_size).reset_index(drop=True)
            data_dropna_sample_values[col] = data[col].dropna().values

    # use data_dropna_sample_values to infer data type for each column
    _n_jobs = np.min([n_jobs, len(data.columns.values)])
    type_infos = Parallel(n_jobs=_n_jobs)(delayed(_infer_dtype)(
        data_dropna_sample_values[col], col, type_threshold)
                                          for col in data.columns.values)
    type_infos_df = pd.DataFrame(type_infos)[['column', 'type']]

    # dtype mapping for basic stat calculation
    data_types = {}
    for col in data.columns.values:
        data_types[col] = type_infos_df.loc[type_infos_df['column'] == col,
                                            'type'].values[0]

    # get basic statistic information for all columns
    stat_infos = Parallel(n_jobs=_n_jobs)(delayed(_cal_column_stat)(
        data_dropna_sample_values[col], col, data_types[col])
                                          for col in data.columns.values)
    stat_infos_df = pd.DataFrame(stat_infos)

    # merge dtype infomation with stat information
    full_infos_df = type_infos_df.merge(stat_infos_df, on='column', how='left')

    # add include column
    full_infos_df['include'] = 1
    full_infos_df = full_infos_df[[
        'column', 'type', 'include', 'sample_value', 'sample_num_uni',
        'sample_uni_percentage', 'sample_min', 'sample_median', 'sample_max',
        'sample_std'
    ]]

    # if base_schema is provided, we can compare with base schema
    if base_schema is not None:
        base_schema = base_schema[['column', 'type', 'include']]
        base_schema.columns = [
            'base_%s' % (col) for col in base_schema.columns.values
        ]
        full_infos_df = full_infos_df.merge(base_schema,
                                            left_on='column',
                                            right_on='base_column',
                                            how='outer')

        # compare with the base schema
        full_infos_df['base_column'] = full_infos_df['base_column'].apply(
            lambda x: 'column not in base table' if pd.isnull(x) else x)
        full_infos_df['column'] = full_infos_df['column'].apply(
            lambda x: 'column not in current table' if pd.isnull(x) else x)

        # reorder the column
        full_infos_df['include'] = base_schema['base_include']
        full_infos_df = full_infos_df[[
            'column', 'base_column', 'type', 'base_type', 'include',
            'sample_value', 'sample_num_uni', 'sample_uni_percentage',
            'sample_min', 'sample_median', 'sample_max', 'sample_std'
        ]]

    # add data validation for type column
    val_type = DataValidation(type="list",
                              formula1='"key,numeric,str,date"',
                              allow_blank=False)
    ws.add_data_validation(val_type)

    # add data validation for include column
    val_include = DataValidation(type="list",
                                 formula1='"0,1"',
                                 allow_blank=False)
    ws.add_data_validation(val_include)

    # get col_name, excel column mapping
    column_mapping = {}
    for i, col in enumerate(full_infos_df.columns):
        column_mapping[col] = xlsxwriter.utility.xl_col_to_name(i)

    # write everything into the worksheet
    for r_idx, r in enumerate(
            dataframe_to_rows(full_infos_df, index=False, header=True)):
        ws.append(r)
        for cell_idx, cell in enumerate(
                ws.iter_cols(max_col=ws.max_column,
                             min_row=ws.max_row,
                             max_row=ws.max_row)):
            cell = cell[0]
            if r_idx != 0:
                val_type.add(ws['%s%d' % (column_mapping['type'], ws.max_row)])
                val_include.add(ws['%s%d' %
                                   (column_mapping['include'], ws.max_row)])
                if cell_idx == 0:
                    cell.font = Font(bold=True)
            else:
                cell.style = 'Accent5'

    # add conditional formating
    red_fill = PatternFill(bgColor="FFC7CE")
    red_font = Font(color="9C0006")
    green_fill = PatternFill(bgColor="C6EFCE")
    green_font = Font(color="006100")
    blue_fill = PatternFill(bgColor="9ECAE1")
    blue_font = Font(color="08306B")
    orange_fill = PatternFill(bgColor="FDD0A2")
    orange_font = Font(color="A63603")
    purple_fill = PatternFill(bgColor="DADAEB")
    purple_font = Font(color="3F007D")

    # red highlight if there is any inconsistent between base and the target
    if base_schema is not None:
        col1 = column_mapping['column']
        col2 = column_mapping['base_column']
        ws.conditional_formatting.add(
            '%s2:%s%d' % (col1, col1, ws.max_row),
            FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
                        stopIfTrue=True,
                        fill=red_fill,
                        font=red_font))

        ws.conditional_formatting.add(
            '%s2:%s%d' % (col2, col2, ws.max_row),
            FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
                        stopIfTrue=True,
                        fill=red_fill,
                        font=red_font))

        col1 = column_mapping['type']
        col2 = column_mapping['base_type']
        ws.conditional_formatting.add(
            '%s2:%s%d' % (col1, col1, ws.max_row),
            FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
                        stopIfTrue=True,
                        fill=red_fill,
                        font=red_font))

        ws.conditional_formatting.add(
            '%s2:%s%d' % (col2, col2, ws.max_row),
            FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
                        stopIfTrue=True,
                        fill=red_fill,
                        font=red_font))

    # yellow highlight column type and include (which need to be modified)
    ws['%s1' % (column_mapping['type'])].style = 'Neutral'
    ws['%s1' % (column_mapping['include'])].style = 'Neutral'

    # green highlight for the mkey type and red highlight for the error type
    type_cols = [column_mapping['type']]
    if 'base_type' in column_mapping.keys():
        type_cols.append(column_mapping['base_type'])

    for col in type_cols:
        ws.conditional_formatting.add(
            '%s2:%s%d' % (col, col, ws.max_row),
            FormulaRule(formula=['%s2="error"' % (col)],
                        stopIfTrue=True,
                        fill=red_fill,
                        font=red_font))
        ws.conditional_formatting.add(
            '%s2:%s%d' % (col, col, ws.max_row),
            FormulaRule(formula=['%s2="key"' % (col)],
                        stopIfTrue=True,
                        fill=green_fill,
                        font=green_font))
        ws.conditional_formatting.add(
            '%s2:%s%d' % (col, col, ws.max_row),
            FormulaRule(formula=['%s2="numeric"' % (col)],
                        stopIfTrue=True,
                        fill=blue_fill,
                        font=blue_font))
        ws.conditional_formatting.add(
            '%s2:%s%d' % (col, col, ws.max_row),
            FormulaRule(formula=['%s2="str"' % (col)],
                        stopIfTrue=True,
                        fill=orange_fill,
                        font=orange_font))
        ws.conditional_formatting.add(
            '%s2:%s%d' % (col, col, ws.max_row),
            FormulaRule(formula=['%s2="date"' % (col)],
                        stopIfTrue=True,
                        fill=purple_fill,
                        font=purple_font))

    # red highlight for include = 0
    ws.conditional_formatting.add(
        '%s2:%s%d' %
        (column_mapping['include'], column_mapping['include'], ws.max_row),
        FormulaRule(formula=['%s2=0' % (column_mapping['include'])],
                    stopIfTrue=True,
                    fill=red_fill,
                    font=red_font))

    # red highlight for sample_num_uni = 0 or 1, only one unique value
    ws.conditional_formatting.add(
        '%s2:%s%d' % (column_mapping['sample_num_uni'],
                      column_mapping['sample_num_uni'], ws.max_row),
        FormulaRule(formula=['%s2=0' % (column_mapping['sample_num_uni'])],
                    stopIfTrue=True,
                    fill=red_fill,
                    font=red_font))
    ws.conditional_formatting.add(
        '%s2:%s%d' % (column_mapping['sample_num_uni'],
                      column_mapping['sample_num_uni'], ws.max_row),
        FormulaRule(formula=['%s2=1' % (column_mapping['sample_num_uni'])],
                    stopIfTrue=True,
                    fill=red_fill,
                    font=red_font))

    # adjust the column format for the worksheet
    _adjust_ws(ws=ws, row_height=20)

    wb.save(filename=os.path.join(output_root, 'data_schema_%s.xlsx' %
                                  (fname)))
コード例 #23
0
ファイル: main.py プロジェクト: Pushok8/rusbonds_parser
def write_coupon_data_in_excel(row_data: list[Any]) -> None:
    """
    Write row data in coupon_data.xlsx file. If this file not exits,
    function to create this file and write column name.

    row_data: list[Any] -> list consist of any data.
    """
    xlsx_file_name: str = 'coupon_data.xlsx'

    try:
        coupon_data_workbook = openpyxl.load_workbook(xlsx_file_name)
    except FileNotFoundError:
        new_coupon_data_workbook = openpyxl.Workbook()

        # Delete default list in workbook.
        for sheet_name in new_coupon_data_workbook.sheetnames:
            sheet = new_coupon_data_workbook[sheet_name]
            new_coupon_data_workbook.remove(sheet)

        coupon_data_list = new_coupon_data_workbook.create_sheet('Coupon data')

        for column in range(1, len(COLUMNS_NAMES) + 1):
            coupon_data_list.cell(
                row=1, column=column).value = COLUMNS_NAMES[column - 1]

        new_coupon_data_workbook.save(xlsx_file_name)
        new_coupon_data_workbook.close()

        coupon_data_workbook = openpyxl.load_workbook(xlsx_file_name)

    coupon_data_list = coupon_data_workbook['Coupon data']

    row: int = 2
    column_letters = string.ascii_uppercase[:len(row_data)]
    row_is_free: bool = False

    while True:

        for col_letter in column_letters:
            if coupon_data_list[col_letter + str(row)].value is None:
                row_is_free = True
            else:
                row_is_free = False
                break

        if row_is_free:
            for column in range(1, len(row_data) + 1):
                coupon_data_list.cell(
                    row=row, column=column).value = row_data[column - 1]
            row += 1
            break
        else:
            row += 1
    try:
        coupon_data_workbook.save(xlsx_file_name)
        coupon_data_workbook.close()
    except PermissionError:
        print(
            'Пожалуйста, закройте файл coupon_data.xlsx, так как парсер не может работать с открытым файлом для вывода.'
        )
        exit()
コード例 #24
0
# blackRowInserter.py : inserts blank rows from row N to row M to spreadsheet
import sys
import openpyxl

# ENTER IN TERMINAL : blackRowInserter.py StartingRowNumber NumberOfBlankRows yourFile.xlsx
N = int(sys.argv[1]) # Starting row
M = int(sys.argv[2]) # Number of blank rows
filename = sys.argv[3] # File name

print('Opening Workbook...')
wb = openpyxl.load_workbook(filename)
sheet = wb.get_sheet_by_name('Sheet')
new_wb = openpyxl.Workbook()
new_sheet = new_wb.active

print('Adding Blank rows...')
for row in range(1, sheet.max_row+1):
	for col in range(1, sheet.max_column+1):
		if row < N:
			new_sheet.cell(row=row, column=col).value = sheet.cell(row=row, column=col).value
		else:
			new_sheet.cell(row=row+M, column=col).value = sheet.cell(row=row, column=col).value



print('Done')
new_wb.save('yesblank.xlsx')

コード例 #25
0
def reorganize():
    # Open an existing excel file
    wb = openpyxl.load_workbook('data' + ".xlsx")
    sheet = wb.worksheets[0]
    # Create a new excel file
    out = openpyxl.Workbook()
    # Open the worksheet we want to edit
    outsheet = out.create_sheet("data")

    # if 'sheet' appears randomly we can delete it
    rm = out.get_sheet_by_name('Sheet')
    out.remove_sheet(rm)

    #################
    # DO STUFF HERE #
    #################
    COMPANY = "A"
    FINANCIAL_INSTITUTION = "B"
    DEAL_TYPE = "C"
    AMOUNT_USD = "D"
    COMMENTS = "E"
    DATE = "F"

    outsheet[COMPANY + '1'].value = "Company"
    outsheet[FINANCIAL_INSTITUTION + '1'].value = "Financial Institution"
    outsheet[DEAL_TYPE + '1'].value = "Deal Type"
    outsheet[AMOUNT_USD + '1'].value = "Amount USD"
    outsheet[COMMENTS + '1'].value = "Comments"
    outsheet[DATE + '1'].value = "Date"

    # Data will always be in A column
    deals = []
    deal = []
    for row in range(2, sheet.max_row + 1):
        info = sheet['A' + str(row)].value
        deal.append(info)
        if type(info) is not str:
            deals.append(deal)
            deal = []

    for row in range(0, len(deals)):
        deal = deals[row]
        if len(deals[row]) == 6:
            outsheet[COMPANY + str(row + 2)].value = deal[0]
            outsheet[FINANCIAL_INSTITUTION + str(row + 2)].value = deal[1]
            outsheet[DEAL_TYPE + str(row + 2)].value = deal[2]
            outsheet[AMOUNT_USD + str(row + 2)].value = deal[3]
            outsheet[COMMENTS + str(row + 2)].value = deal[4]
            outsheet[DATE + str(row + 2)].value = deal[5]
        elif len(deals[row]) == 4:
            outsheet[COMPANY + str(row + 2)].value = deal[0]
            outsheet[DEAL_TYPE + str(row + 2)].value = deal[1]
            outsheet[COMMENTS + str(row + 2)].value = deal[2]
            outsheet[DATE + str(row + 2)].value = deal[3]
        # HARD ONE
        elif len(deals[row]) == 5:
            # If we are missing the financial transaction fill it out like this
            if deal[2][0] == "$":
                print(str(row + 2) + " is missing TRANSACTION")
                outsheet[COMPANY + str(row + 2)].value = deal[0]
                outsheet[DEAL_TYPE + str(row + 2)].value = deal[1]
                outsheet[AMOUNT_USD + str(row + 2)].value = deal[2]
                outsheet[COMMENTS + str(row + 2)].value = deal[3]
                outsheet[DATE + str(row + 2)].value = deal[4]
            # Otherwise we are missing dollar amount so fill out like this
            else:
                print(str(row + 2) + " is missing AMOUNT")
                outsheet[COMPANY + str(row + 2)].value = deal[0]
                outsheet[FINANCIAL_INSTITUTION + str(row + 2)].value = deal[1]
                outsheet[DEAL_TYPE + str(row + 2)].value = deal[2]
                outsheet[COMMENTS + str(row + 2)].value = deal[3]
                outsheet[DATE + str(row + 2)].value = deal[4]

    print("Reorganized " + str(len(deals)) + " deals")

    out.save("organized.xlsx")

    # LMK when the script is done
    pygame.init()
    pygame.mixer.music.load('/home/andrefisch/python/evan/note.mp3')
    pygame.mixer.music.play()
    time.sleep(5)
    pygame.mixer.music.stop()
コード例 #26
0
ファイル: output.py プロジェクト: drkane/flatten-tool
 def open(self):
     self.workbook = openpyxl.Workbook()
コード例 #27
0
ファイル: juniper.py プロジェクト: sonicepk/RENAT
def create_best_path_select_data(self,
                                 route_content,
                                 output_excel='best.xlsx'):
    """ Creates the matrix of best path selection 
    
    Provides the test described in  `smb://10.128.3.91/SharePoint01/31_VerificationRoom/31_13_検証環境セット/BGP-Best-Path-SelectionのAll-in-One設定_20161118改良/`
    The test uses predefined Ixia config and follows predefined steps
    """

    wb = openpyxl.Workbook()
    wb.guess_types = True
    tmp = output_excel.split('.')
    sheet_name = tmp[0]
    ws = wb.create_sheet(sheet_name, 0)

    # adding column name
    ws.append([
        'INTF', 'Prefix', 'ProtoPref', 'LocalPref', 'AS_PATH', 'Origin', 'MED',
        'Protocol', 'I/E', 'IGP', 'Age', 'RouterID', 'CLLength', 'Peer',
        'Win/Loose', 'Reason'
    ])

    # sparsing route information
    route_info_list = re.findall(
        r"(\S+/.{1,3}) (?:.*?entr.*?announced.*?)\r\n(( {8}[ *](\S+) *Pref.*?\r\n( {16}.*?\r\n)+)+)\r\n",
        route_content, re.DOTALL)

    line = 1  # excel start at number 1
    for route_info in route_info_list:
        route = route_info[0]
        proto_list = re.findall(
            r" {8}([ *])(\S+) *Preference: (\S+?)(?:/\S+){0,1}\r\n(( {16}.*?\r\n)+)",
            route_info[1], re.DOTALL)
        for proto_info in proto_list:
            if proto_info[0] == '*':
                win = 'win'
                if line > 1:
                    ws.append([
                        '', '', '', '', '', '', '', '', '', '', '', '', '', '',
                        '', ''
                    ])
                    line = line + 1
            else:
                win = 'loose'
            proto = proto_info[1]
            pp = proto_info[2]

            match_intf = re.search(r" via (\S+),", proto_info[3], re.MULTILINE)
            if match_intf:
                intf_index = int(match_intf.group(1).split('.')[1])
                via_intf = chr(ord('A') + intf_index - 1)
            else:
                via_intf = '-'

            match_localpref = re.search(r"Localpref: (\S+)", proto_info[3],
                                        re.MULTILINE)
            if match_localpref: local_pref = match_localpref.group(1)
            else: local_pref = '-'

            match_as_path = re.search(r"AS path: (.*?)\r\n", proto_info[3],
                                      re.MULTILINE)
            if match_as_path:
                as_list = match_as_path.group(1).split()
                as_path = len(as_list) - 1  # the last is the origin
                if as_list[-1] == 'E':
                    origin = 'EGP'
                else:
                    origin = 'IGP'
            else:
                as_path = '-'
                origin = '-'

            match_med = re.search(r"Metric: (\S+) ", proto_info[3],
                                  re.MULTILINE)
            if match_med:
                med = match_med.group(1)
            else:
                med = '-'

            match_type = re.search(r"Local AS:  (\S+) Peer AS: (\S+)",
                                   proto_info[3], re.MULTILINE)
            if match_type:
                if match_type.group(1) == match_type.group(2):
                    type = 'IBGP'
                else:
                    type = 'EBGP'
            else:
                type = '-'

            match_igp = re.search(r"Metric2: (\S+)", proto_info[3],
                                  re.MULTILINE)
            if match_igp:
                igp = match_igp.group(1)
            else:
                igp = '-'

            match_router_id = re.search(r"Router ID: (\S+)", proto_info[3],
                                        re.MULTILINE)
            if match_router_id:
                router_id = match_router_id.group(1)
            else:
                router_id = '-'

            match_cll = re.search(r"Cluster list:  (.*)\r\n", proto_info[3],
                                  re.MULTILINE)
            if match_cll:
                cll = len(match_cll.group(1).split())
            else:
                cll = '0'

            match_peer = re.search(r"Source: (\S+)", proto_info[3],
                                   re.MULTILINE)
            if match_peer:
                peer = match_peer.group(1)
            else:
                peer = '-'

#            match_age = re.search(r"Age: (\S+)",proto_info[3],re.MULTILINE)
#            if match_age:
#                age = reduce(lambda x, y: x*60+y, [int(i) for i in match_age.group(1).split(':')])
#            else:
#                age = '-'
            match_age = re.search(r"Age: (.*?) (?:\r\n|    )", proto_info[3],
                                  re.MULTILINE)
            if match_age:
                m = re.match(r"(\d+w){0,1}(\d+d){0,1} {0,1}(\S+)",
                             match_age.group(1))
                age = 0
                if m.group(1):
                    age = age + int(m.group(1)[:-1]) * 7 * 24 * 60 * 60
                if m.group(2): age = age + int(m.group(2)[:-1]) * 24 * 60 * 60
                age = age + sum(
                    int(x) * 60**i
                    for i, x in enumerate(reversed(m.group(3).split(":"))))
            else:
                age = '-'

            match_reason = re.search(r"Inactive reason: (.+)\r\n",
                                     proto_info[3], re.MULTILINE)
            if match_reason:
                reason = match_reason.group(1)
            else:
                reason = '-'

            BuiltIn().log(
                "    Added row: \
            (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" %
                (via_intf, route, pp, local_pref, as_path, origin, med, proto,
                 type, igp, age, router_id, cll, peer, win, reason))

            row = [
                via_intf, route, pp, local_pref, as_path, origin, med, proto,
                type, igp, age, router_id, cll, peer, win, reason
            ]
            ws.append(row)
            line = line + 1

    # coloring style
    # HeaderFill  = PatternFill(start_color='ffff00',end_color='ffff00',fill_type='solid')
    RedFont = Font(color=colors.RED)
    BlueFont = Font(color=colors.BLUE)
    #for cell in ws[1]:
    #    cell.fill = HeaderFill
    #for cell in ws['O']:
    #    if cell.value=='win': cell.font = RedFont

    for cell in reversed(ws['P']):
        row = int(cell.row)
        if cell.value == 'Update source':
            ws['N%d' % row].font = BlueFont
            ws['N%d' % (row - 1)].font = RedFont
        if cell.value == 'Cluster list length':
            ws['M%d' % row].font = BlueFont
            ws['M%d' % (row - 1)].font = RedFont
        if cell.value == 'Router ID':
            ws['L%d' % row].font = BlueFont
            ws['L%d' % (row - 1)].font = RedFont
        if cell.value == 'Active preferred':
            ws['K%d' % row].font = BlueFont
            ws['K%d' % (row - 1)].font = RedFont
        if cell.value == 'IGP metric':
            ws['J%d' % row].font = BlueFont
            ws['J%d' % (row - 1)].font = RedFont
        if cell.value == 'Interior > Exterior > Exterior via Interior':
            ws['I%d' % row].font = BlueFont
            ws['I%d' % (row - 1)].font = RedFont
        if cell.value == 'Route Metric or MED comparison':
            ws['H%d' % row].font = BlueFont
            ws['H%d' % (row - 1)].font = RedFont
        if cell.value == 'Always Compare MED':
            ws['G%d' % row].font = BlueFont
            ws['G%d' % (row - 1)].font = RedFont
        if cell.value == 'Origin':
            ws['F%d' % row].font = BlueFont
            ws['F%d' % (row - 1)].font = RedFont
        if cell.value == 'AS path':
            ws['E%d' % row].font = BlueFont
            ws['E%d' % (row - 1)].font = RedFont
        if cell.value == 'Local Preference':
            ws['D%d' % row].font = BlueFont
            ws['D%d' % (row - 1)].font = RedFont
        if cell.value == 'Route Preference':
            ws['C%d' % row].font = BlueFont
            ws['C%d' % (row - 1)].font = RedFont

    # adjust column width
    ws.column_dimensions['B'].width = 12
    ws.column_dimensions['C'].width = 12
    ws.column_dimensions['D'].width = 12
    ws.column_dimensions['E'].width = 12
    ws.column_dimensions['H'].width = 12
    ws.column_dimensions['L'].width = 12
    ws.column_dimensions['M'].width = 12
    ws.column_dimensions['O'].width = 12
    ws.column_dimensions['P'].width = 32

    tab = Table(displayName="Table1", ref="A1:P%d" % line)
    style = TableStyleInfo(name="TableStyleMedium9",
                           showFirstColumn=False,
                           showLastColumn=False,
                           showRowStripes=False,
                           showColumnStripes=False)
    tab.tableStyleInfo = style
    ws.add_table(tab)

    # top = Border(top=border.top)
    rows = ws["A1:P%d" % line]
    for row in rows:
        for cell in row:
            cell.alignment = Alignment(horizontal="right")

    ws.append([])
    ws.append(['※1 Protocol Preference の値はルータの import policy で変更'])
    ws.append(['※2 ここでは Always Compare MED 有りの試験のみ実施。'])
    ws.append(
        ['※3 1.6.0.0/staticの場合、ルータにて PP 170 の static経路を設定。ただし、正常に試験できているか不明。'])
    ws.append(['※4 全経路配信後、IF-B から配信しているこの経路(1.9.0.0) だけ一回フラップさせるた。'])

    save_path = os.getcwd() + '/' + Common.get_result_folder(
    ) + '/' + output_excel
    wb.save(save_path)
    BuiltIn().log("Created the best path select matrix")
コード例 #28
0
ファイル: ch19_29.py プロジェクト: u9113034/python
# ch19_29.py
import openpyxl
from openpyxl.styles import Alignment

wb = openpyxl.Workbook()  # 建立空白的活頁簿
ws = wb.active  # 獲得目前工作表
ws['A1'] = '台科大'
ws['B1'] = '明志科大'
ws['C1'] = '北科大'
ws.row_dimensions[1].height = 40  # 高度是40pt
ws.column_dimensions['B'].width = 20  # 寬度是20個英文字元寬
ws['A1'].alignment = Alignment(horizontal='left', vertical='top')
ws['B1'].alignment = Alignment(horizontal='center', vertical='center')
ws['C1'].alignment = Alignment(horizontal='right', vertical='bottom')
wb.save('out19_29.xlsx')  # 將活頁簿儲存
コード例 #29
0
 def open_excel(self):
     ws = openpyxl.Workbook(write_only=True)
     return ws
コード例 #30
0
    xw.append((b + line_slopesL[k] * points[k]) / (line_slopesL[k] - s))
    yw.append(s * xw[k] + b)
    xp3 = [(radius_throat + line_slopesL[k] * points[k]) /
           (line_slopesL[k] - line_slopesR[-1]), xw[k]]
    yp3 = [
        line_slopesR[-1] * (radius_throat + line_slopesL[k] * points[k]) /
        (line_slopesL[k] - line_slopesR[-1]) + radius_throat, yw[k]
    ]
    plt.plot(xp3, yp3, 'g')
xw.insert(0, 0)
yw.insert(0, radius_throat)

new_set = np.transpose([xw, yw])

new_set = new_set.tolist()
wb = op.Workbook()
ws = wb.active
for i in new_set:

    ws.append(i)

wb.save(filename="nozzle_points.xlsx")
wb.close()
plt.plot(xw, yw)

radius_exit = max(yw)
print(yw[-1])
ar = (radius_throat / radius_exit)**2
print(radius_exit)
plt.show()