python类load_workbook()的实例源码

def read_excel(excel_path):
    candidate_list = list()
    wb = load_workbook(excel_path)
    ws = wb.active
    for i in range(2, ws.max_row - 1):  # -1 means that the last row is null
        candidate = Candidate(uid=ws.cell(row=i, column=1).value, nickname=ws.cell(row=i, column=2).value,
                              age=ws.cell(row=i, column=3).value, height=ws.cell(row=i, column=4).value,
                              image=ws.cell(row=i, column=5).value, marriage=ws.cell(row=i, column=6).value,
                              education=ws.cell(row=i, column=7).value, work_location=ws.cell(row=i, column=8).value,
                              work_sublocation=ws.cell(row=i, column=9).value,
                              shortnote=ws.cell(row=i, column=10).value,
                              matchCondition=ws.cell(row=i, column=11).value,
                              randListTag=ws.cell(row=i, column=12).value,
                              province=ws.cell(row=i, column=13).value, gender=ws.cell(row=i, column=14).value)
        candidate_list.append(candidate)
        # print(candidate)

    return candidate_list

def test_xls_export_works_with_unicode(self):
        survey = create_survey_from_xls(_logger_fixture_path(
            'childrens_survey_unicode.xls'))
        export_builder = ExportBuilder()
        export_builder.set_survey(survey)
        temp_xls_file = NamedTemporaryFile(suffix='.xlsx')
        export_builder.to_xls_export(temp_xls_file.name, self.data_utf8)
        temp_xls_file.seek(0)
        # check that values for red\u2019s and blue\u2019s are set to true
        wb = load_workbook(temp_xls_file.name)
        children_sheet = wb.get_sheet_by_name("children.info")
        data = dict([(r[0].value, r[1].value) for r in children_sheet.columns])
        self.assertTrue(data[u'children.info/fav_colors/red\u2019s'])
        self.assertTrue(data[u'children.info/fav_colors/blue\u2019s'])
        self.assertFalse(data[u'children.info/fav_colors/pink\u2019s'])
        temp_xls_file.close()

def test_to_xls_export_respects_custom_field_delimiter(self):
        survey = self._create_childrens_survey()
        export_builder = ExportBuilder()
        export_builder.GROUP_DELIMITER = ExportBuilder.GROUP_DELIMITER_DOT
        export_builder.set_survey(survey)
        xls_file = NamedTemporaryFile(suffix='.xls')
        filename = xls_file.name
        export_builder.to_xls_export(filename, self.data)
        xls_file.seek(0)
        wb = load_workbook(filename)

        # check header columns
        main_sheet = wb.get_sheet_by_name('childrens_survey')
        expected_column_headers = [
            u'name', u'age', u'geo.geolocation', u'geo._geolocation_latitude',
            u'geo._geolocation_longitude', u'geo._geolocation_altitude',
            u'geo._geolocation_precision', u'tel.tel.office',
            u'tel.tel.mobile', u'_id', u'meta.instanceID', u'_uuid',
            u'_submission_time', u'_index', u'_parent_index',
            u'_parent_table_name', u'_tags', '_notes', '_version']
        column_headers = [c[0].value for c in main_sheet.columns]
        self.assertEqual(sorted(column_headers),
                         sorted(expected_column_headers))
        xls_file.close()

def test_to_xls_export_generates_valid_sheet_names(self):
        survey = create_survey_from_xls(_logger_fixture_path(
            'childrens_survey_with_a_very_long_name.xls'))
        export_builder = ExportBuilder()
        export_builder.set_survey(survey)
        xls_file = NamedTemporaryFile(suffix='.xls')
        filename = xls_file.name
        export_builder.to_xls_export(filename, self.data)
        xls_file.seek(0)
        wb = load_workbook(filename)
        # check that we have childrens_survey, children, children_cartoons
        # and children_cartoons_characters sheets
        expected_sheet_names = ['childrens_survey_with_a_very_lo',
                                'childrens_survey_with_a_very_l1',
                                'childrens_survey_with_a_very_l2',
                                'childrens_survey_with_a_very_l3']
        self.assertEqual(wb.get_sheet_names(), expected_sheet_names)
        xls_file.close()

def test_child_record_parent_table_is_updated_when_sheet_is_renamed(self):
        survey = create_survey_from_xls(_logger_fixture_path(
            'childrens_survey_with_a_very_long_name.xls'))
        export_builder = ExportBuilder()
        export_builder.set_survey(survey)
        xls_file = NamedTemporaryFile(suffix='.xlsx')
        filename = xls_file.name
        export_builder.to_xls_export(filename, self.long_survey_data)
        xls_file.seek(0)
        wb = load_workbook(filename)

        # get the children's sheet
        ws1 = wb.get_sheet_by_name('childrens_survey_with_a_very_l1')

        # parent_table is in cell K2
        parent_table_name = ws1.cell('K2').value
        expected_parent_table_name = 'childrens_survey_with_a_very_lo'
        self.assertEqual(parent_table_name, expected_parent_table_name)

        # get cartoons sheet
        ws2 = wb.get_sheet_by_name('childrens_survey_with_a_very_l2')
        parent_table_name = ws2.cell('G2').value
        expected_parent_table_name = 'childrens_survey_with_a_very_l1'
        self.assertEqual(parent_table_name, expected_parent_table_name)
        xls_file.close()

def check_noequations(self):
    """
    Verify that no column in a sheet contains an equation
    Based on checking every cell in the 4th row

    Keyword arguments:
    sheet -- sheet object from workbook
    """
    wb_open = load_workbook(self.path, read_only = True)
    sheet = wb_open.get_sheet_by_name(self.name)

    for i in range(1, len(self.header_entries)):
      for j in range(1,5):
        value = sheet.cell(row = j, column = i).value
        # equation check logic, TODO might be better code out there
        if (value and isinstance(value, str) and value[0] == "="):
          raise AMIExcelError("Cell R4C{0} contain equations."
            .format(i))

    return True

def main():
    parser = _make_parser()
    args = parser.parse_args()

    _configure_logging(args)

    if args.excel:
        excel = ami_excel(args.excel)

    if excel and excel.validate_workbook():
        LOGGER.info("{}: valid".format(args.excel))
    else:
        if args.output:
            wb = load_workbook(args.excel, data_only = True)
            wb.save(args.output)
            new_excel = ami_excel(args.output)
            if new_excel.validate_workbook():
                LOGGER.info("{}: valid".format(args.output))
            else:
                LOGGER.error("{}: invalid".format(args.output))

def parse_statistics(logfile):
    xl = pd.ExcelFile(logfile)
    df = xl.parse("Sheet")
    df = df.sort_values(by='Line Numbers')

    writer = pd.ExcelWriter(logfile)
    df.to_excel(writer, sheet_name='Sheet', index=False)
    writer.save()

    wb = openpyxl.load_workbook(logfile)
    ws = wb.active

    row_count = ws.max_row
    column_count = ws.max_column

    chart = ScatterChart()
    chart.title = "Time upload domain names"
    chart.style = 13
    chart.x_axis.title = "Line numbers"
    chart.y_axis.title = "Time, sec"

    xvalues = Reference(ws, min_col=1, min_row=2, max_row=row_count)
    color_choice = ['3F888F', 'D24D57']
    for i in range(2, column_count + 1):
        values = Reference(ws, min_col=i, min_row=1, max_row=row_count)
        series = Series(values, xvalues, title_from_data=True)
        series.marker.symbol = "diamond"
        series.graphicalProperties.line.solidFill = color_choice[i-2]
        series.marker.graphicalProperties.line.solidFill = color_choice[i-2]
        series.marker.graphicalProperties.solidFill = color_choice[i-2]
        series.graphicalProperties.line.width = 20000
        chart.series.append(series)

    chart.legend.legendPos = 'b'
    ws.add_chart(chart)
    wb.save(logfile)

def __init__(self):
        # self._sheet1_count = 1
        # self._sheet2_count = 1
        # self._sheet2_movie_count = 0
        # ??????
        self._xlsx_wb = load_workbook(filename='data.xlsx')
        sheetnames = self._xlsx_wb.get_sheet_names()
        self._xlsx_ws1 = self._xlsx_wb.get_sheet_by_name(sheetnames[0])
        self._xlsx_ws2 = self._xlsx_wb.get_sheet_by_name(sheetnames[1])
        # self._xlsx_wb = Workbook()
        # self._xlsx_ws1 = self._xlsx_wb.active
        # self._xlsx_ws1.title = 'TOP250'
        # self._xlsx_ws1['A1'] = u'??'
        # self._xlsx_ws1['B1'] = u'????'
        # self._xlsx_ws1['C1'] = u'??'
        # self._xlsx_ws1['D1'] = u'??'
        # self._xlsx_ws1['E1'] = u'??'
        # self._xlsx_ws1['F1'] = u'??'
        # self._xlsx_ws2 = self._xlsx_wb.create_sheet(u'????')
        # self._xlsx_ws2['A1'] = u'????'
        # self._xlsx_ws2['B1'] = u'????'
        # self._xlsx_ws2['C1'] = u'????'
        # self._xlsx_ws2['D1'] = u'???'
        # self._xlsx_ws2['E1'] = u'ID?'
        # self._xlsx_ws2['F1'] = u'????'

def on_importXLSX_clicked(self):
        fileName, _ = QFileDialog.getOpenFileName(self, 'Open file...', self.env, "Microsoft Office Excel(*.xlsx *.xlsm *.xltx *.xltm)")
        if fileName:
            wb = openpyxl.load_workbook(fileName)
            ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])
            data = list()
            i = 1
            while True:
                x = ws.cell(row=i, column=1).value
                y = ws.cell(row=i, column=2).value
                if x==None or y==None:
                    break
                try:
                    data.append((round(float(x), 4), round(float(y), 4)))
                except:
                    dlgbox = QMessageBox(QMessageBox.Warning, "File error", "Wrong format.\nThe datasheet seems to including non-digital cell.", (QMessageBox.Ok), self)
                    if dlgbox.exec_():
                        break
                i += 1
            for e in data:
                self.on_add_clicked(e[0], e[1])

def __init__(self, excel_file, sheet_name='SWITCHES', variable_name_row=1,
                 key_column=1, template_column=2, variable_start_column=3):
        ''' Loads the excel configuration file '''
        try:
            # TODO: Check is file is locked, if so wait and try to open N
            # more times after N seconds.
            self._wb = load_workbook(excel_file, data_only=True)
            self._filename = excel_file
        except:
            raise Exception("Could not load spreadsheet '%s'" % excel_file)

        self.set_excel_sheet(sheet_name)
        self.set_variable_name_row(variable_name_row)
        self.set_template_column(template_column)
        self.set_data_start_row(variable_name_row + 1)
        self.set_key_column(key_column)
        self.set_variable_start_column(variable_start_column)

def parse_data(data):
     cols = ['name', "age", "gp", "w", "l","min" ,"pts", "fgm", "fga",
     "fg%", "3pm", "3pa", "ftm", "fta", "ft%" ,"oreb", "dreb", "reb",
     "ast", "tov",      "stl", "blk", "pf", "dd2", "td3"]

     stat_col = cols.index(data)
     player_col = 0

     wb = load_workbook("nbastats.xlsx")
     ws = wb['Sheet1']
     for row in ws.values:
         yield row[player_col].lower(), row[stat_col]

def save_xls(self, dframe):  # ???????????excel?????sheet
        xls_path = os.path.join(current_folder, self.name + '.xlsx')
        if os.path.exists(xls_path):  # excel ??????
            book = load_workbook(xls_path)
            writer = pd.ExcelWriter(xls_path, engine='openpyxl')
            writer.book = book
            writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
            dframe.to_excel(writer, self.name)
            writer.save()
        else:  # ??????
            writer = ExcelWriter(xls_path)
            dframe.to_excel(writer, self.name)
            writer.save()

def __init__(self, ExcelPath):
        self.path = ExcelPath
        if not os.path.isfile(ExcelPath):  # ????????
            self.stat = False
            # "??????????"
            self.wb = openpyxl.Workbook()
        else:
            self.stat = True
            self.wb = openpyxl.load_workbook(ExcelPath)

def import_openpyxl(self):
        """Import the requests module """
        try:
            import openpyxl
            from openpyxl import load_workbook
        except ImportError:
            print("openpyxl module is not installed"\
                       "Please install openpyxl module to"\
                       "perform any activities related to parsing xl sheets")
        else:
            self.openpyxl = openpyxl
            self.load = load_workbook

def load_workbook(self, wb_location):
        """
        Load an existing xl workbook 
        """

        value = None
        try:
            wb = self.load(wb_location)
        except Exception as err:
            print err
            print("Error loading workbook, check if file exists")
        else:
            value = wb
        return value

def extract_part_fields_from_xlsx(filename, inc_field_names=None, exc_field_names=None, recurse=False):
    '''Return a dictionary of part fields extracted from an XLSX spreadsheet.'''

    logger.log(DEBUG_OVERVIEW,
               'Extracting fields {}, -{} from XLSX file {}.'.format(inc_field_names, exc_field_names,
                                                                   filename))

    try:
        wb = pyxl.load_workbook(filename, data_only=True)
        return extract_part_fields_from_wb(wb, inc_field_names, exc_field_names)
    except FieldExtractionError:
        logger.warn('Field extraction failed on {}.'.format(filename))
    return {}

def mainWork(absent):
    absent = list(map(int,absent.split(' ')))
    lec_times = ('7:50','8:50','10:00','11:00','13:00','14:00','15:10')

    strength = 60 # Get from database
    subject = 'sdl' # Get subject code from database
    cl_division = 'TEA' # Get class and division from database
    lec_number = 3 # Needs better way to get

    book = opx.load_workbook(cl_division+'.xlsx')
    sheet = book.get_sheet_by_name(subject)

    curr_col = 1
    while sheet[get_column_letter(curr_col)+'1'].value !=None:
        curr_col += 1

    curr_col = get_column_letter(curr_col)

    sheet[curr_col+'1'].value = str(time.localtime().tm_mday) + '-' + str(time.localtime().tm_mon) + '-' + str(time.localtime().tm_year)

    for i in range(1,strength+1):
        if i in absent:
            sheet[curr_col+str(i+1)].value = 'A'
        else:
            sheet[curr_col+str(i+1)].value = 'P'
        print('Marked!',str(i))

    sheet[curr_col+str(strength+3)].value = '%.2f' % (((strength-len(absent))/strength) * 100)
    sheet[curr_col+str(strength+3)].value += '%'

    book.save(cl_division+'.xlsx')

def setUp(self):
        report_name = "../test-data-set/test.xlsx"
        self.report = rCMS.ComissionXLSX(report_name)

        dataset = DataSet()

        self.report.add_data(dataset.core_details, dataset.plugins, dataset.themes)
        self.report.generate_xlsx()

        self.workbook = load_workbook(report_name)

def test03_transform_filter():
    base_output = "output_fctl_data_dict_filtered"
    outputs = {}
    extensions = ['html', 'xlsx', 'tsv', 'md']
    for ext in extensions:
        outputs[ext] = "{}.{}".format(base_output, ext)

    namespace = os.path.join(TEST_DIR, 'resources', 'input', 'namespace.json')
    exp = os.path.join(TEST_DIR, 'resources', 'expected', 'data_dict_filtered')
    argv = ['transform', SCHEMA_FILE, '--output', base_output,
            '--filter', namespace, '--columns',
            'Field_compact_name', 'Field_name', 'Full_name', 'Description', 'Count', 'Percentage',
            'Types_count',
            '--formats'] + extensions
    main(argv)

    assert filecmp.cmp(outputs['tsv'], "{}.tsv".format(exp))
    assert filecmp.cmp(outputs['md'], "{}.md".format(exp))
    with open(outputs['html']) as out_fd, \
            open("{}.html".format(exp)) as exp_fd:
        assert out_fd.read().replace(' ', '') == exp_fd.read().replace(' ', '')
    res = [cell.value for row in load_workbook(outputs['xlsx']).active for cell in row]
    exp = [cell.value for row in load_workbook("{}.xlsx".format(exp)).active for cell in row]
    assert res == exp
    for output in outputs.values():
        os.remove(output)