Python pandas.ExcelWriter() Examples

def save_cluster_xlsx(
    filepath: str, de_results: List[pd.DataFrame], cluster_names: List
):
    """Saves multi-clusters DE in an xlsx sheet

    Parameters
    ----------
    filepath
        xslx save path
    de_results
        list of pandas Dataframes for each cluster
    cluster_names
        list of cluster names
    """
    writer = pd.ExcelWriter(filepath, engine="xlsxwriter")
    for i, x in enumerate(cluster_names):
        de_results[i].to_excel(writer, sheet_name=str(x))
    writer.close() 

def _clone_excel(file_name):
    from urllib.error import URLError
    import openpyxl
    try:
        from urllib.request import urlopen
        book = openpyxl.load_workbook(urlopen(file_name))
    except (ValueError, URLError):
        with open(file_name, 'rb') as file:
            book = openpyxl.load_workbook(file)
    import io
    import pandas as pd
    fd = io.BytesIO()
    # noinspection PyTypeChecker
    writer = pd.ExcelWriter(
        fd, engine='openpyxl', optimized_write=True, write_only=True
    )
    writer.book = book
    writer.sheets.update(dict((ws.title, ws) for ws in book.worksheets))
    return writer, fd 

def __init__(self, kwargs, *, cp_dir, log_dir, excel_dir, logger2file):
        self.log_dir = log_dir
        self.writer = tf.summary.create_file_writer(log_dir)
        # self.writer.set_as_default()
        self.checkpoint = tf.train.Checkpoint(**kwargs)
        self.saver = tf.train.CheckpointManager(self.checkpoint, directory=cp_dir, max_to_keep=5, checkpoint_name='ckpt')
        self.excel_writer = pd.ExcelWriter(excel_dir + '/data.xlsx')
        self.logger = self.create_logger(
            name='logger',
            console_level=logging.INFO,
            console_format='%(levelname)s : %(message)s',
            logger2file=logger2file,
            file_name=log_dir + 'log.txt',
            file_level=logging.WARNING,
            file_format='%(lineno)d - %(asctime)s - %(module)s - %(funcName)s - %(levelname)s - %(message)s'
        ) 

def _resize_columns(self, sheetname_dataframe, writer, max_width, text_wrap=True):
        """
        Resizes columns in workbook.
        :param sheetname_dataframe: A map mapping the sheet name to a dataframe.
        :param writer: The ExcelWriter, which the worksheets already added using writer.to_excel
        :param max_width: The maximum width of the columns.
        :param text_wrap: Whether or not to turn on text wrapping if columns surpass max_width.
        :return: None
        """
        workbook = writer.book
        wrap_format = workbook.add_format({'text_wrap': text_wrap})
        for name in sheetname_dataframe:
            for i, width in enumerate(self._get_col_widths(sheetname_dataframe[name])):
                if width > max_width:
                    writer.sheets[name].set_column(i, i, width=max_width, cell_format=wrap_format)
                else:
                    writer.sheets[name].set_column(i, i, width=width) 

def save_df_as_table(df, path,
                     format_str=SETTINGS.table_export_format,
                     transpose=SETTINGS.table_export_transpose,
                     confirm_overwrite=False):
    if confirm_overwrite and not user.check_and_confirm_overwrite(path):
        return
    if transpose:
        df = df.T
    if format_str == "excel":
        # requires xlwt and/or openpyxl to be installed
        with pd.ExcelWriter(path) as writer:
            df.to_excel(writer)
    else:
        getattr(df, "to_" + format_str)(path)
    logger.debug("{} table saved to: {}".format(
        format_str, path)) 

def write_to_excel(comp_values):
    current_dir = os.path.realpath(os.path.dirname(__file__))
    df = pd.DataFrame(comp_values)
    with pd.ExcelWriter(os.path.join(current_dir, "output" + ".xlsx")) as writer:
        for col in df:
            df1 = pd.concat([df[col], df[col].apply(pd.Series)], axis=1).drop(
                [col], axis=1
            )
            df1.to_excel(writer, sheet_name=col)
            worksheet = writer.sheets[col]
            worksheet.set_column(0, 19, 16)


# Tests for running the plots independently
# if __name__ == "__main__":
#    write_to_excel({'ieee_4node': {'cyme_output': {'1': [1.796043190045738, 5.388117303585387, 0.10784685850195197, 0.5904760366014294], '2': [2.0202605985010313, 6.518985500570162, 0.22820004614399358, 0.8158267453203445], '3': [0.2536631703405771, 0.8984713888134145, 0.06581202655980385, 0.24831713333607722], '4': [0.5340964354229437, 2.3119539172019614, 0.23396047634518685, 0.46239954705589636]}, 'demo_output': {}, 'gridlabd_output': {'sourcebus': [1.7960358301233543, 5.388107490370064, 1.6037668205527515, 6.415067282211007], 'node2': [11.615625751028661, 11.520529778987775, 9.073598525409436, 5.8609512435066655], 'node3': [1.2918305507375225, 1.2769209930818217, 1.0089298663668416, 0.6470699623879508], 'load4': [0.8640026377752124, 0.41845221416471984, 0.8640056993976751, 0.4184497947877163], 'node1': [11.95788550650638, 12.207304856874984, 9.189536257036401, 6.043847565567804]}, 'opendss_output': {'sourcebus': [0.0002463564270509644, 0.0007390394604267333, 0.00018858267905109123, 0.0007542723977164494], 'n2': [0.29564583680274237, 0.7167450994489304, 0.11638654713225582, 0.2358768460293939], 'n3': [0.06870713919610347, 0.23863115122119738, 0.048858783362332986, 0.18910852255356034], 'n4': [0.5051857600366919, 0.7781653125896516, 0.21133259350889166, 0.4141945608522559]}, 'synergi_output': {'node_61746791326': [0.000999999415454687, 0.0009999923015324673, 0.0009999996415073831, 0.0009999921771822826], 'node_61746791327': [1.4052433405517772, 0.8534261345199505, 1.100207413576314, 0.09925701086833777], 'node_61746839533': [0.5767771891663984, 1.2140898058567755, 0.5400898631419725, 1.138540681909626], 'node_61746842036': [2.2802968553458975, 1.9930774142792114, 1.8176517572334512, 1.1452767401123385]}}, 'ieee_13node': {'cyme_output': {'650': [1.6442002249862027, 2.525071217368887, 0.00175590064769382, 0.013818660541591976], '633': [2.191962671993786, 2.7645145724914206, 0.13502678340376117, 0.30258402028428943], '634': [0.03373885959967424, 0.0437948202860033, 0.006851233217346727, 0.01281928879314106], '7': [1.6444948113338582, 2.5248296649991535, 0.0027565424317121945, 0.014811194353743895], 'rg60': [1.6444956621823732, 2.524829864977333, 0.002756707027920302, 0.014813876873885912], '632': [2.1103087225772716, 2.527515777000864, 0.0791161472443358, 0.2320482533916044], '652': [1.2506186991395154, 1.2878722947329384, 1.2506186991395154, 1.2878722947329384], '611': [1.1239472985549395, 1.3770646713358636, 1.1239472985549395, 1.3770646713358636], '692': [2.631742710227492, 2.70681263013228, 0.16404792757812026, 0.44179116013620445], '645': [2.2388323604857887, 2.784026210466048, 0.1850730276433944, 0.31739303544051045], '646':[2.317657028721549, 2.949917446377885, 0.24850513527121498, 0.3676808102436452], '671': [2.630682474394549, 2.706364834017407, 0.16301316760562778, 0.44085146992520197], '675': [2.7067614833052813, 2.7173749384388346, 0.23870615585157873, 0.47708961405564254], '684': [2.6970360888981926, 2.8036701407791496, 0.23157910972791085, 0.5317115549334911], '680': [2.7200580191836536, 3.2662254613120227, 0.19824054093535326, 0.5540558678888127]}, 'demo_output': {}, 'gridlabd_output': {}, 'opendss_output': {'sourcebus': [0.17960357997560872, 0.5388107499674782, 0.16044213136336458, 0.6414216083753025], '650': [3.46173977571697e-05, 0.00027688611330264445, 0.00024463297015852746, 0.0011161493255213788], 'rg60': [0.0003803596442662037, 0.0006225803505498496, 0.0005903829452526287, 0.0014616392165030185], '633': [0.38047984831844595, 0.8729366035169337, 0.13271664569004354, 0.28972479926771977], '634': [0.010133922766079825, 0.020570453379602505, 0.006830751758597249, 0.012931935773494248], '671': [0.6108771018713602, 1.3265863413420522, 0.16093756369535386, 0.42423496513300524], '645': [0.4373080269407039, 0.8584390204064538, 0.18280284255143675, 0.32024566175842095], '646': [0.536560726260114, 0.9883607573047537, 0.24564544687076215, 0.36912074589189336], '692': [0.6119751654440972, 1.3274110796789444, 0.16196524206540175, 0.4251679482288616], '675': [0.7447836834016821, 1.3496142794655475, 0.20882248763406844, 0.46165856154276824], '611': [0.47109807838681994, 0.8597942885112269, 0.47109807838681994, 0.8597942885112269], '652': [0.5900525671135924, 0.8394468662430931, 0.5900525671135924, 0.8394468662430931], '670': [0.3852338293138269, 0.8975161336344963, 0.1039477429886314, 0.2879817616207214], '632': [0.2799093266146708, 0.6803831271514527, 0.07670340676752073, 0.21819919790776035], '680': [0.7346431542735055, 1.6877642140995293, 0.19615949605780691, 0.5372620670065573], '684': [0.7244901189090343, 1.397819420078607, 0.22833210323410577, 0.4698019455814719]}, 'synergi_output': {}}}) 

def pack_measures(self):
        writer = pd.ExcelWriter(self.excel_name, engine='xlwt')
        self.measure_pd.to_excel(writer, sheet_name='measures', index=False, engine='xlwt')

        # Add the total column and row
        if self.path_template is not None:
            for sheet_name in self.distrib_matrix_dct:
                if '#' in sheet_name:
                    df = self.distrib_matrix_dct[sheet_name].copy()
                    df = df.append(df.sum(numeric_only=True, axis=0), ignore_index=True)
                    df['total'] = df.sum(numeric_only=True, axis=1)
                    df.iloc[-1, df.columns.get_loc('vert')] = 'total'
                    df.to_excel(writer, sheet_name=sheet_name, index=False, engine='xlwt')
                else:
                    self.distrib_matrix_dct[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False, engine='xlwt')

        # Save pickle
        self.distrib_matrix_dct['measures'] = self.measure_pd
        with open(self.pickle_name, 'wb') as handle:
            pickle.dump(self.distrib_matrix_dct, handle)

        # Save Excel
        writer.save() 

def test_to_excel():
    modin_df = create_test_modin_dataframe()
    pandas_df = create_test_pandas_dataframe()

    TEST_EXCEL_DF_FILENAME = "test_df.xlsx"
    TEST_EXCEL_pandas_FILENAME = "test_pandas.xlsx"

    modin_writer = pandas.ExcelWriter(TEST_EXCEL_DF_FILENAME)
    pandas_writer = pandas.ExcelWriter(TEST_EXCEL_pandas_FILENAME)

    modin_df.to_excel(modin_writer)
    pandas_df.to_excel(pandas_writer)

    modin_writer.save()
    pandas_writer.save()

    assert assert_files_eq(TEST_EXCEL_DF_FILENAME, TEST_EXCEL_pandas_FILENAME)

    teardown_test_file(TEST_EXCEL_DF_FILENAME)
    teardown_test_file(TEST_EXCEL_pandas_FILENAME) 

def test_io_xlsx(test_df, meta_args):
    # add column to `meta`
    test_df.set_meta(['a', 'b'], 'string')

    # write to xlsx (direct file name and ExcelWriter, see bug report #300)
    file = 'testing_io_write_read.xlsx'
    for f in [file, pd.ExcelWriter(file)]:
        test_df.to_excel(f, **meta_args[0])
        if isinstance(f, pd.ExcelWriter):
            f.close()

        # read from xlsx
        import_df = IamDataFrame(file, **meta_args[1])

        # assert that IamDataFrame instances are equal and delete file
        assert_iamframe_equal(test_df, import_df)
        os.remove(file) 

def write_results(results, summary, output):
    writer = pd.ExcelWriter(output, engine='xlsxwriter')
    cell_fmt = writer.book.add_format({
        "font_name": "Arial",
        "font_size": "10"
    })
    index_fmt = writer.book.add_format({
        "font_name": "Arial",
        "font_size": "10",
        "bold": True,
    })
    header_fmt = writer.book.add_format({
        "font_name": "Arial",
        "font_size": "10",
        "bold": True,
        "bottom": 1
    })

    add_sheet(summary, 'Number of wins per version', writer, cell_fmt, index_fmt, header_fmt)

    for version in reversed(sorted(results.keys())):
        add_sheet(results[version], version, writer, cell_fmt, index_fmt, header_fmt)

    writer.save() 

def export_meta(self, excel_writer, sheet_name='meta'):
        """Write the 'meta' indicators of this object to an Excel sheet

        Parameters
        ----------
        excel_writer : str, path object or ExcelWriter object
            any valid string path, :class:`pathlib.Path`
            or :class:`pandas.ExcelWriter`
        sheet_name : str
            name of sheet which will contain dataframe of 'meta' indicators
        """
        if not isinstance(excel_writer, pd.ExcelWriter):
            close = True
            excel_writer = pd.ExcelWriter(excel_writer)
        write_sheet(excel_writer, sheet_name, self.meta, index=True)
        if close:
            excel_writer.close() 

def spia_matrices_to_excel(spia_matrices: SPIADataFrames, path: str) -> None:
    """Export a SPIA data dictionary into an Excel sheet at the given path.

    .. note::

        # The R import should add the values:
        # ["nodes"] from the columns
        # ["title"] from the name of the file
        # ["NumberOfReactions"] set to "0"
    """
    writer = pd.ExcelWriter(path, engine='xlsxwriter')

    for relation, df in spia_matrices.items():
        df.to_excel(writer, sheet_name=relation, index=False)

    # Save excel
    writer.save() 

def export_text2xlsx(infile, outfile, field_delimiter, number):
    df = pd.read_csv(infile, delimiter=field_delimiter, engine='python')
    rows_number = df.shape[0]
    if rows_number > number:
        data_frames = split_dataframe(df, number)
        frame_number = 1
        for frame in data_frames:
            filename, ext = os.path.splitext(outfile)
            excel_file = "{}_{}.xlsx".format(filename, frame_number)
            writer = ExcelWriter(excel_file, engine='xlsxwriter')
            frame.to_excel(writer, 'sheet1')
            writer.save()
            frame_number += 1
    else:
        writer = ExcelWriter(outfile)
        df.to_excel(writer, 'sheet1')
        writer.save() 

def write_data(self, file_descr):
        """
        Use dataframe to_excel to write into file_descr (filename) - open first if file exists.
        """
        if os.path.isfile(file_descr):
            print(file_descr, 'exists')
            # Solution to keep existing data
            book = load_workbook(file_descr)
            writer = pd.ExcelWriter(file_descr, engine='openpyxl')
            writer.book = book
            writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
            self.data_df.to_excel(writer, sheet_name='Mongo_Schema', index=True,
                                  float_format='%.2f')
            writer.save()
        else:
            self.data_df.to_excel(file_descr, sheet_name='Mongo_Schema', index=True,
                                  float_format='%.2f') 

def write_excel(filename, **kwargs):
    """Write data tables to an Excel file, using kwarg names as sheet names.
    
    Parameters
    ----------
    filename : str
        The filename to write to.
    kwargs : dict
        Mapping from sheet names to data.
    """
    writer = pd.ExcelWriter(filename)
    for sheet_name, obj in kwargs.items():
        if isinstance(obj, dict):
            obj = _params_dict_to_dataframe(obj)
        if isinstance(obj, pd.DataFrame):
            obj.to_excel(writer, sheet_name=sheet_name)
    writer.save()
    writer.close() 

def template(output_file, cycle_type, gear_box_type, wltp_class):
    """
    Writes a sample template OUTPUT_FILE.

    OUTPUT_FILE: SYNCING input template file (.xlsx). [default: ./datasync.xlsx]
    """

    import pandas as pd
    from co2mpas.core.model.physical.cycle import dsp
    theoretical = sh.selector(['times', 'velocities'], dsp(inputs=dict(
        cycle_type=cycle_type.upper(), gear_box_type=gear_box_type,
        wltp_class=wltp_class, downscale_factor=0
    ), outputs=['times', 'velocities'], shrink=True))
    base = dict.fromkeys((
        'times', 'velocities', 'target gears', 'engine_speeds_out',
        'engine_coolant_temperatures', 'co2_normalization_references',
        'alternator_currents', 'battery_currents', 'target fuel_consumptions',
        'target co2_emissions', 'target engine_powers_out'
    ), [])
    data = dict(theoretical=theoretical, dyno=base, obd=base)
    os.makedirs(osp.dirname(output_file), exist_ok=True)
    with pd.ExcelWriter(output_file) as writer:
        for k, v in data.items():
            pd.DataFrame(v).to_excel(writer, k, index=False)
    return data 

def save_summary(summary, output_summary_file, start_time):
    """
    Save CO2MPAS model configurations.

    :param summary:
        Summary data.
    :type summary: list

    :param output_summary_file:
        Output summary file path.
    :type output_summary_file: str

    :param start_time:
        Run start time.
    :type start_time: datetime.datetime
    """
    import pandas as pd
    # noinspection PyProtectedMember
    from .core.write.convert import _co2mpas_info2df, _add_units, _sort_key
    df = pd.DataFrame(summary)
    df.set_index(['id', 'base'], inplace=True)
    df = df.reindex(columns=sorted(
        df.columns,
        key=lambda x: _sort_key(x, p_keys=('cycle', 'stage', 'usage', 'param'))
    ))
    if not df.columns.empty:
        df.columns = pd.MultiIndex.from_tuples(_add_units(
            df.columns, short=False
        ))

    os.makedirs(osp.dirname(output_summary_file) or '.', exist_ok=True)
    with pd.ExcelWriter(output_summary_file) as writer:
        df.to_excel(writer, 'summary')
        _co2mpas_info2df(start_time).to_excel(writer, 'proc_info')
    log.info('CO2MPAS summary written into (%s)...', output_summary_file) 

def save_summary(summary, filename):
        import pandas as pd
        writer = pd.ExcelWriter(filename)
        summary.to_excel(writer)
        writer.save() 

def to_excel(self, excel_writer, sheet_name='data', iamc_index=False,
                 include_meta=True, **kwargs):
        """Write object to an Excel spreadsheet

        Parameters
        ----------
        excel_writer : str, path object or ExcelWriter object
            any valid string path, :class:`pathlib.Path`
            or :class:`pandas.ExcelWriter`
        sheet_name : string
            name of sheet which will contain :meth:`timeseries()` data
        iamc_index : bool, default False
            if True, use `['model', 'scenario', 'region', 'variable', 'unit']`;
            else, use all 'data' columns
        include_meta : boolean or string
            if True, write 'meta' to an Excel sheet name 'meta' (default);
            if this is a string, use it as sheet name
        """
        # open a new ExcelWriter instance (if necessary)
        close = False
        if not isinstance(excel_writer, pd.ExcelWriter):
            close = True
            excel_writer = pd.ExcelWriter(excel_writer, engine='openpyxl')

        # write data table
        write_sheet(excel_writer, sheet_name, self._to_file_format(iamc_index))

        # write meta table unless `include_meta=False`
        if include_meta:
            meta_rename = dict([(i, i.capitalize()) for i in META_IDX])
            write_sheet(excel_writer,
                        'meta' if include_meta is True else include_meta,
                        self.meta.reset_index().rename(columns=meta_rename))

        # close the file if `excel_writer` arg was a file name
        if close:
            excel_writer.close() 

def close(self, format='csv'):
        import pandas as pd

        if format == 'csv':
            pd.DataFrame.from_dict(self._data, orient='index').to_csv(self._file)
        elif format == 'json':
            pd.DataFrame.from_dict(self._data, orient='index').to_json(self._file)
        else:
            writer = pd.ExcelWriter(self._file)
            pd.DataFrame.from_dict(self._data, orient='index').to_excel(writer)
            writer.save() 

def save(self, filename: str = None, path: str = None):
        """
        재무제표 정보를 모두 엑셀파일로 일괄저장

        Parameters
        ----------
        filename: str
            저장할 파일명(default: {corp_code}_{report_tp}.xlsx)
        path: str
            저장할 폴더(default: 실행폴더/fsdata)
        """
        import os
        if path is None:
            path = os.getcwd()
            path = os.path.join(path, 'fsdata')
            create_folder(path)

        if filename is None:
            filename = '{}_{}.xlsx'.format(self.info.get('corp_code'), self.info.get('report_tp'))

        file_path = os.path.join(path, filename)
        with pd.ExcelWriter(file_path) as writer:
            for tp in self._statements:
                fs = self._statements[tp]
                label = self._labels[tp]
                if fs is not None:
                    sheet_name = 'Data_' + tp
                    fs.to_excel(writer, sheet_name=sheet_name)
                    sheet_name = 'Labels_' + tp
                    label.to_excel(writer, sheet_name=sheet_name)
        return file_path 

def export(self, filename=None, file_format=None, title=None, freeze_panes=True):
        if file_format is None:
            if filename is not None:
                base_filename, file_format = os.path.splitext(filename)
                file_format = re.sub(r'^\.', '', file_format)
            else:
                file_format = 'xlsx'
        if file_format not in ('json', 'xlsx', 'csv'):
            raise Exception("export: unsupported file format")
        header_output, contents = self.header_and_contents()
        df = pandas.DataFrame.from_records(contents, columns=header_output)
        outfile = DAFile()
        outfile.set_random_instance_name()
        if filename is not None:
            outfile.initialize(filename=filename, extension=file_format)
        else:
            outfile.initialize(extension=file_format)
        if file_format == 'xlsx':
            if freeze_panes:
                freeze_panes = (1, 0)
            else:
                freeze_panes = None
            writer = pandas.ExcelWriter(outfile.path(),
                                        engine='xlsxwriter',
                                        options={'remove_timezone': True})
            df.to_excel(writer, sheet_name=title, index=False, freeze_panes=freeze_panes)
            writer.save()
        elif file_format == 'csv':
            df.to_csv(outfile.path(), index=False)
        elif file_format == 'json':
            df.to_json(outfile.path(), orient='records')
        outfile.commit()
        outfile.retrieve()
        return outfile 

def update_excel_library():
    readers_copy = readers_df.copy(deep=True)
    books_copy = books_df.copy(deep=True)

    readers_schema = ["借书号", "姓名", "性别", "单位", "借书权限", "借书额度"]
    books_schema = ["ISBN", "书籍名称", "作者", "出版社", "出版日期", "页数",
                    "价格", "主题", "馆藏本数", "索书号", "内容简介", "书籍位置"]

    readers_copy.columns = readers_schema
    books_copy.columns = books_schema

    with pd.ExcelWriter(os.path.join(os.getcwd(), "图书馆信息.xlsx")) as writer_library:
        readers_copy.to_excel(writer_library, sheet_name="读者", index=False)
        books_copy.to_excel(writer_library, sheet_name="书籍", index=False) 

def multiple_dfs(self, df_list, sheets, file_name, spaces):
        writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
        row = 0
        for dataframe in df_list:
            dataframe.to_excel(writer, sheet_name=sheets, startrow=row, startcol=0)
            row = row + len(dataframe.index) + spaces + 1
        writer.save() 

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 create(self):

        # A memory buffer as ExcelWriter storage backend
        buffer = BytesIO()
        self.workbook.filename = buffer

        # Create "cover" sheet
        self.write_cover_sheet()

        # Create "queries" sheet
        self.write_queries_sheet()

        # Create numberlist sheets
        self.write_numberlist_sheets()

        # Create "comments" sheet
        self.write_comments_sheet()

        # Save/persist ExcelWriter model
        self.writer.save()

        # Get hold of buffer content
        payload = buffer.getvalue()
        return payload 

def __init__(self, data):
        super(DossierXlsx, self).__init__(data)
        self.writer = pandas.ExcelWriter('temp.xlsx', engine='xlsxwriter')
        self.workbook = self.writer.book
        add_worksheet_monkeypatch(self.workbook)

        self.format_wrap_top = self.workbook.add_format()
        self.format_wrap_top.set_text_wrap()
        self.format_wrap_top.set_align('top')

        self.format_small_font = self.workbook.add_format({'align': 'vcenter', 'font_size': 9})
        self.format_small_font_align_top = self.workbook.add_format({'align': 'top', 'font_size': 9}) 

def update_excel_history():
    history_copy = history_df.copy(deep=True)
    history_schema = ["时间", "单位", "姓名", "借书号", "动作", "ISBN", "书名", "书籍位置", "还书期限"]
    history_copy.columns = history_schema

    with pd.ExcelWriter(os.path.join(os.getcwd(), "借阅记录.xlsx")) as writer_history:
        history_copy.to_excel(writer_history, sheet_name="借阅记录", index=False) 

def sql_to_excel():
    conn = get_connection()
    readers_sql = "SELECT * FROM Readers"
    books_sql = "SELECT * FROM Books"
    history_sql = "SELECT * FROM History"

    readers_df = pd.read_sql(readers_sql, conn)
    books_df = pd.read_sql(books_sql, conn)
    history_df = pd.read_sql(history_sql, conn)

    readers_schema = ["借书号", "姓名", "性别", "单位", "借书权限", "借书额度"]
    books_schema = ["ISBN", "书籍名称", "作者", "出版社", "出版日期", "页数",
                    "价格", "主题", "馆藏本数", "索书号", "内容简介", "书籍位置"]
    history_schema = ["时间", "单位", "姓名", "借书号", "动作", "ISBN", "书名", "书籍位置", "还书期限"]

    readers_df.columns = readers_schema
    books_df.columns = books_schema
    history_df.columns = history_schema

    backup_path = os.path.join(os.getcwd(), "备份恢复")
    if not os.path.isdir(backup_path):
        os.makedirs(backup_path)

    with pd.ExcelWriter(os.path.join(backup_path, "图书馆信息.xlsx")) as writer_library:
        readers_df.to_excel(writer_library, sheet_name="读者", index=False)
        books_df.to_excel(writer_library, sheet_name="书籍", index=False)

    with pd.ExcelWriter(os.path.join(backup_path, "借阅记录.xlsx")) as writer_history:
        history_df.to_excel(writer_history, sheet_name="借阅记录", index=False) 

def close(self, format='csv'):
        import pandas as pd

        if format == 'csv':
            pd.DataFrame.from_dict(self._data, orient='index').to_csv(self._file)
        elif format == 'json':
            pd.DataFrame.from_dict(self._data, orient='index').to_json(self._file)
        else:
            writer = pd.ExcelWriter(self._file)
            pd.DataFrame.from_dict(self._data, orient='index').to_excel(writer)
            writer.save()