How to use openpyxl - 10 common examples

def time_openpyxl():
    """ Run OpenPyXL in default mode. """
    start_time = clock()

    workbook = openpyxl.workbook.Workbook()
    worksheet = workbook.active

    for row in range(row_max // 2):
        for col in range(col_max):
            colletter = get_column_letter(col + 1)
            worksheet.cell('%s%s' % (colletter, row * 2 + 1)).value = "Row: %d Col: %d" % (row, col)
        for col in range(col_max):
            colletter = get_column_letter(col + 1)
            worksheet.cell('%s%s' % (colletter, row * 2 + 2)).value = row + col

    workbook.save('openpyxl.xlsx')

    elapsed = clock() - start_time
    print_elapsed_time('openpyxl', elapsed)

exit(0)

    v_workBook = openpyxl.Workbook()
    v_workBook.remove(v_workBook.active)

    v_workSheet = v_workBook.create_sheet(v_sheetName)
    v_workSheet.sheet_view.showGridLines = False

    # v_workSheet.add_image(
    #    openpyxl.drawing.image.Image('/path/to/image.png'),
    #    'A1'
    # )

    v_boldFont = openpyxl.styles.Font(bold=True)

    v_centerAlignment = openpyxl.styles.Alignment(
        horizontal="center", vertical="center", wrapText=True
    )

    v_workSheet.merge_cells("A2:F2")
    v_workSheet["A2"] = bs4.BeautifulSoup(v_reportName, "lxml").get_text()
    v_workSheet["A2"].font = v_boldFont
    v_workSheet["A2"].alignment = v_centerAlignment

    v_headerFont = openpyxl.styles.Font(bold=True)

    v_headerFill = openpyxl.styles.PatternFill("solid", fgColor="DBE5F1")

    v_headerAlignment = openpyxl.styles.Alignment(
        horizontal="center", vertical="center", wrapText=True
    )

params = {"alignment": "vertical",
                  "time_alignment": 0,
                  "continuity": True,
                  "blank_rows": False,
                  "time_header_coord": "A1",
                  "data_starts": 2,
                  "data_ends": None,
                  "frequency": "M",
                  "missings": True,
                  "missing_value": None,
                  "time_multicolumn": False,
                  "time_composed": False}

        end = CleanSingleColumn().clean_time_index(ws, params)

        wb_exp = load_workbook(
            os.path.join(abs_path("expected"), "test_case1.xlsx"))

        # wb.save("test_case2_after_cleaning_index.xlsx")
        self.assertTrue(compare_cells(wb, wb_exp))
        self.assertEqual(end, 256)

def test_create_all_cards_sheet(oracle: Oracle) -> None:
    book = openpyxl.Workbook()
    sheet = book.create_sheet()
    xlsx.create_all_cards(sheet, oracle.index)
    assert sheet.title == "All Cards"
    rows = [[cell.value for cell in row] for row in sheet.rows]
    assert rows == [
        ["name", "have"],
        ["Air Elemental", '=IF(\'LEA\'!A2>0,"LEA: "&\'LEA\'!A2&", ","")'],
        ["Akroma's Vengeance", '=IF(\'HOP\'!A2>0,"HOP: "&\'HOP\'!A2&", ","")'],
        [
            "Dark Ritual",
            '=IF(\'LEA\'!A3>0,"LEA: "&\'LEA\'!A3&", ","")&'
            'IF(\'ICE\'!A2>0,"ICE: "&\'ICE\'!A2&", ","")&'
            'IF(\'HOP\'!A3>0,"HOP: "&\'HOP\'!A3&", ","")',
        ],
        ["Forest", None],
        ["Rhox", '=IF(\'S00\'!A2>0,"S00: "&\'S00\'!A2&", ","")'],

def test_fill_implicit_missings_horizontal(self):
        strategy = GetSingleFrequencyContinuous
        wb = Workbook()
        ws = wb.active

        ws["A1"] = arrow.get(2015, 6, 13).datetime
        ws["B1"] = arrow.get(2015, 6, 14).datetime
        ws["C1"] = arrow.get(2015, 6, 15).datetime
        ws["D1"] = arrow.get(2015, 6, 18).datetime
        ws["E1"] = arrow.get(2015, 6, 19).datetime
        ws["F1"] = arrow.get(2015, 6, 20).datetime
        ws["G1"] = arrow.get(2015, 6, 22).datetime
        ws["H1"] = arrow.get(2015, 6, 23).datetime

        values = range(8)
        frequency = "D"
        time_header_coord = "A1"
        ini_col = 1
        end_col = 8

# )

    v_boldFont = openpyxl.styles.Font(bold=True)

    v_centerAlignment = openpyxl.styles.Alignment(
        horizontal="center", vertical="center", wrapText=True
    )

    v_workSheet.merge_cells("A2:F2")
    v_workSheet["A2"] = bs4.BeautifulSoup(v_reportName, "lxml").get_text()
    v_workSheet["A2"].font = v_boldFont
    v_workSheet["A2"].alignment = v_centerAlignment

    v_headerFont = openpyxl.styles.Font(bold=True)

    v_headerFill = openpyxl.styles.PatternFill("solid", fgColor="DBE5F1")

    v_headerAlignment = openpyxl.styles.Alignment(
        horizontal="center", vertical="center", wrapText=True
    )

    v_numberAlignment = openpyxl.styles.Alignment(
        horizontal="right", vertical="center", wrapText=True
    )

    v_textAlignment = openpyxl.styles.Alignment(
        horizontal="left", vertical="center", wrapText=True
    )

    v_dateAlignment = openpyxl.styles.Alignment(
        horizontal="center", vertical="center", wrapText=True
    )

# Actions performed
        actions_performed = dict()
        num_files = len(files_list)

        update_time = time.time()

        for file_index, my_file in enumerate(files_list):
            ## Get file path
            file_path = os.path.join(folder_path, my_file)

            ## Get Excel file and load in a pandas dataframe
            sheets_names = pd.ExcelFile(file_path).sheet_names
            for sheet in sheets_names:
                ### Rename sheets by "file_sheet"
                ss = openpyxl.load_workbook(file_path)
                ss_sheet = ss.get_sheet_by_name(sheet)
                title = ss_sheet.title

                if not my_file.split(".")[0] in title:
                    title = '_'.join((my_file.split(".")[0] + "_" + sheet).split())
                    # ss.save(file_path)

                title = '_'.join(title.split())
                title = title.replace(')', '')
                title = title.replace('(', '')

                create_dataset = True
                if title in datasets_in_project:
                    if overwrite:
                        project.get_dataset(title).delete()
                        actions_performed[title] = "replaced"

def _write_dxfs(self):
        if self._style_properties and 'dxf_list' in self._style_properties:
            dxfs = SubElement(self._root, 'dxfs', {'count': str(len(self._style_properties['dxf_list']))})
            for d in self._style_properties['dxf_list']:
                dxf = SubElement(dxfs, 'dxf')
                if 'font' in d and d['font'] is not None:
                    font_node = SubElement(dxf, 'font')
                    if d['font'].color is not None:
                        if str(d['font'].color.index).split(':')[0] == 'theme':  # strip prefix theme if marked
                            if str(d['font'].color.index).split(':')[2]:
                                SubElement(font_node, 'color', {'theme': str(d['font'].color.index).split(':')[1],
                                                                'tint': str(d['font'].color.index).split(':')[2]})
                            else:
                                SubElement(font_node, 'color', {'theme': str(d['font'].color.index).split(':')[1]})
                        else:
                            SubElement(font_node, 'color', {'rgb': str(d['font'].color.index)})
                    if d['font'].bold:
                        SubElement(font_node, 'b', {'val': '1'})
                    if d['font'].italic:
                        SubElement(font_node, 'i', {'val': '1'})
                    if d['font'].underline != 'none':
                        SubElement(font_node, 'u', {'val': d['font'].underline})
                    if d['font'].strikethrough:
                        SubElement(font_node, 'strike')
                if 'fill' in d:
                    f = d['fill']
                    fill = SubElement(dxf, 'fill')
                    if f.fill_type:
                        node = SubElement(fill, 'patternFill', {'patternType': f.fill_type})
                    else:

def _write_fonts(self):
        """ add fonts part to root
            return {font.crc => index}
        """

        fonts = SubElement(self._root, 'fonts')

        # default
        font_node = SubElement(fonts, 'font')
        SubElement(font_node, 'sz', {'val':'11'})
        SubElement(font_node, 'color', {'theme':'1'})
        SubElement(font_node, 'name', {'val':'Calibri'})
        SubElement(font_node, 'family', {'val':'2'})
        SubElement(font_node, 'scheme', {'val':'minor'})

        # others
        table = {}
        index = 1
        for st in self._style_list:
            if st.font != DEFAULTS.font and st.font not in table:
                table[hash(st.font)] = str(index)
                font_node = SubElement(fonts, 'font')
                SubElement(font_node, 'sz', {'val':str(st.font.size)})
                if str(st.font.color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such
                    if str(st.font.color.index).split(':')[2]:
                        SubElement(font_node, 'color', {'theme':str(st.font.color.index).split(':')[1],
                                                        'tint':str(st.font.color.index).split(':')[2]})
                    else:
                        SubElement(font_node, 'color', {'theme':str(st.font.color.index).split(':')[1]})
                else:

SubElement(border, 'diagonal')

        # others
        table = {}
        index = 1
        for st in self._style_list:
            if st.borders != DEFAULTS.borders and st.borders not in table:
                table[hash(st.borders)] = str(index)
                border = SubElement(borders, 'border')
                # caution: respect this order
                for side in ('left', 'right', 'top', 'bottom', 'diagonal'):
                    obj = getattr(st.borders, side)
                    if obj.border_style is None or obj.border_style == 'none':
                        node = SubElement(border, side)
                    else:
                        node = SubElement(border, side, {'style':obj.border_style})
                        if str(obj.color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such
                            if str(obj.color.index).split(':')[2]:
                                SubElement(node, 'color', {'theme':str(obj.color.index).split(':')[1],
                                                                'tint':str(obj.color.index).split(':')[2]})
                            else:
                                SubElement(node, 'color', {'theme':str(obj.color.index).split(':')[1]})
                        else:
                            SubElement(node, 'color', {'rgb':str(obj.color.index)})
                index += 1

        borders.attrib["count"] = str(index)
        return table