Python excel.ExcelWriter类代码示例

 def __init__(self, db_filename = "fbo_solicitations.xlsx",
              report_prefix = "report", 
              sol_sheet_name = "solicitations",
              filtered_sheet_name = "filtered_solicitations",
              index_column = "sponsor_number",
              report_only_new = True):
     '''
     Constructor
     '''
     if(not os.path.isfile(db_filename)):
         #generate a blank writable excel sheet from scratch
         field_names = [field_name for field_name in Opportunity.fields]
         field_names.remove("filtered")
         writer = ExcelWriter(db_filename)
         sol_df = pd.DataFrame(columns = field_names)
         filtered_df = pd.DataFrame(columns = field_names)
         sol_df.to_excel(writer,sol_sheet_name)
         filtered_df.to_excel(writer,filtered_sheet_name)
         writer.save()
         writer.close()
     
     self.report_filename = (report_prefix + "_" 
                             + str(datetime.today())[:19]
                             .replace(":","_").replace(" ","[") + "].xlsx")
     #kept for posterity, in case only the date component is needed and we don't care about overwrites
     #self.report_filename = report_prefix + "_" + str(date.today())
     self.db_filename = db_filename
     self.sol_sheet_name = sol_sheet_name
     self.filtered_sheet_name = filtered_sheet_name
     self.sol_df = pd.read_excel(db_filename,sol_sheet_name, index_col = index_column)
     self.filtered_df = pd.read_excel(db_filename,filtered_sheet_name, index_col = index_column)
     self.usaved_sol_counter = 0
     self.sol_counter = 0
     self.added_items = set()

    def test_sheets(self):
        _skip_if_no_xlrd()
        ext = self.ext
        path = '__tmp_to_excel_from_excel_sheets__.' + ext

        with ensure_clean(path) as path:
            self.frame['A'][:5] = nan

            self.frame.to_excel(path, 'test1')
            self.frame.to_excel(path, 'test1', cols=['A', 'B'])
            self.frame.to_excel(path, 'test1', header=False)
            self.frame.to_excel(path, 'test1', index=False)

            # Test writing to separate sheets
            writer = ExcelWriter(path)
            self.frame.to_excel(writer, 'test1')
            self.tsframe.to_excel(writer, 'test2')
            writer.save()
            reader = ExcelFile(path)
            recons = reader.parse('test1', index_col=0)
            tm.assert_frame_equal(self.frame, recons)
            recons = reader.parse('test2', index_col=0)
            tm.assert_frame_equal(self.tsframe, recons)
            np.testing.assert_equal(2, len(reader.sheet_names))
            np.testing.assert_equal('test1', reader.sheet_names[0])
            np.testing.assert_equal('test2', reader.sheet_names[1])

def save_resutls_to_excel(all_text_results, segments_results, excel_path='res.xlsx'):
    import pandas
    from collections import defaultdict

    all_segment_metric_results = defaultdict(lambda: [])
    for metric_cls in metric_classes:
        metric_results_during_segments = \
            [filter(lambda metric: isinstance(metric, metric_cls), seg_result)[0].get_results()
                for seg_result in segments_results]

        for result in metric_results_during_segments:
            for key, value in result.items():
                all_segment_metric_results[key].append(value)

    all_metric_results_series = {}
    for key, values in all_segment_metric_results.items():
        all_metric_results_series[key] = pandas.Series(values, index=range(1, len(values) + 1))
    df = pandas.DataFrame(all_metric_results_series)


    all_text_metric_results = defaultdict(lambda: [])
    for metric in all_text_results:
        result = metric.get_results()
        for key, value in result.items():
              all_text_metric_results[key] = [value]
    df2 = pandas.DataFrame(all_text_metric_results)

    from pandas.io.excel import ExcelWriter

    excel = ExcelWriter(excel_path)
    df.to_excel(excel, 'segments')
    df2.to_excel(excel, 'all')
    excel.save()

    def write(self, writer, sheet_name='Sheet1', startrow=0,
              startcol=0, freeze_panes=None, engine=None):
        """
        writer : string or ExcelWriter object
            File path or existing ExcelWriter
        sheet_name : string, default 'Sheet1'
            Name of sheet which will contain DataFrame
        startrow :
            upper left cell row to dump data frame
        startcol :
            upper left cell column to dump data frame
        freeze_panes : tuple of integer (length 2), default None
            Specifies the one-based bottommost row and rightmost column that
            is to be frozen
        engine : string, default None
            write engine to use if writer is a path - you can also set this
            via the options ``io.excel.xlsx.writer``, ``io.excel.xls.writer``,
            and ``io.excel.xlsm.writer``.
        """
        from pandas.io.excel import ExcelWriter
        from pandas.io.common import _stringify_path

        if isinstance(writer, ExcelWriter):
            need_save = False
        else:
            writer = ExcelWriter(_stringify_path(writer), engine=engine)
            need_save = True

        formatted_cells = self.get_formatted_cells()
        writer.write_cells(formatted_cells, sheet_name,
                           startrow=startrow, startcol=startcol,
                           freeze_panes=freeze_panes)
        if need_save:
            writer.save()

 def __init__(self, db_filename = "moore_grants_database.xlsx",
              report_prefix = "report", 
              sheet_name = "grants",
              index_column = "url",
              report_only_new = True):
     '''
     Constructor
     '''
     if(not os.path.isfile(db_filename)):
         #generate a blank writable excel sheet from scratch
         field_names = [field_name for field_name in Grant.fields]
         writer = ExcelWriter(db_filename)
         profile_dataframe = pd.DataFrame(columns = field_names)
         profile_dataframe.to_excel(writer,sheet_name)
         writer.save()
         writer.close()
     
     self.report_filename = (report_prefix + "_" 
                             + str(datetime.today())[:19]
                             .replace(":","_").replace(" ","[") + "].xlsx")
     # kept for posterity, in case only the date component is needed and 
     # we don't care about overwrites
     # self.report_filename = report_prefix + "_" + str(date.today())
     self.db_filename = db_filename
     self.sheet_name = sheet_name
     self.dataframe = pd.read_excel(db_filename,sheet_name, index_col = index_column)
     self.usaved_sol_counter = 0
     self.added_counter = 0
     self.added_items = set()
     self.index_column = index_column

def dump_summary_to_excel(output_filename):
    # Save to XLSX
    store = HDFStore('_data_/ProteinDataStore.h5')
    data_summary = store['DataBases_Summary']
    writer = ExcelWriter(output_filename + '.xlsx', engine='xlsxwriter')
    data_summary.to_excel(writer, 'DataBases_Summary', index=True)
    writer.save()

def dfs2Excel(dfs,sheetnames,filename):
    '''
        将一些列DataFrame保存到Excel中
    '''    
    excelFile = ExcelWriter(filename)
    for (i,df) in enumerate(dfs) :
        df.to_excel(excelFile,sheetnames[i])
    excelFile.save()     

def colorful_dump_summary_to_excel(output_filename, range_label='L1:U36229'):
    # < -2 dark green
    # -2 to -1 light green
    # -1 to  1 yellow
    # 1 to 2 Orange
    # > 2 red
    store = HDFStore('_data_/ProteinDataStore.h5')
    data_summary = store['DataBases_Summary']
    writer = ExcelWriter(output_filename + '.xlsx', engine='xlsxwriter')
    data_summary.to_excel(writer, 'DataBases_Summary', index=True)

    workbook = writer.book
    worksheet = writer.sheets['DataBases_Summary']

    # using pallete http://www.colourlovers.com/palette/3687876/
    blue = workbook.add_format({'bg_color': '#69D2E7', 'font_color': '#000000'})
    coral = workbook.add_format({'bg_color': '#A7DBD8', 'font_color': '#000000'})
    yellow = workbook.add_format({'bg_color': '#EAE319', 'font_color': '#000000'})
    orange = workbook.add_format({'bg_color': '#FA6900', 'font_color': '#000000'})
    red = workbook.add_format({'bg_color': '#E2434B', 'font_color': '#000000'})
    # empty = workbook.add_format({'bg_color': '#FFFFFF', 'font_color': '#000000'})
    #
    # worksheet.conditional_format(range_label, {'type': 'text',
    #                                            'criteria': 'begins with',
    #                                            'value': '.',
    #                                            'format': empty})

    worksheet.conditional_format(range_label, {'type': 'cell',
                                               'criteria': '<',
                                               'value': -2,
                                               'format': blue})

    worksheet.conditional_format(range_label, {'type': 'cell',
                                               'criteria': 'between',
                                               'minimum': -2,
                                               'maximum': -1,
                                               'format': coral})

    worksheet.conditional_format(range_label, {'type': 'cell',
                                               'criteria': 'between',
                                               'minimum': -1,
                                               'maximum': 1,
                                               'format': yellow})

    worksheet.conditional_format(range_label, {'type': 'cell',
                                               'criteria': 'between',
                                               'minimum': 1,
                                               'maximum': 2,
                                               'format': orange})

    worksheet.conditional_format(range_label, {'type': 'cell',
                                               'criteria': '>',
                                               'value': 2,
                                               'format': red})
    writer.save()
    store.close()

 def save_all(self):
     '''
     Dumps all solicitations in both databases to an excel file
     '''
     print "\n\n========  Saving {:s}  ========".format(self.sheet_name)
     writer = ExcelWriter(self.db_filename)
     self.dataframe.to_excel(writer,self.sheet_name,merge_cells=False)
     writer.save()
     writer.close()
     print "========  Done saving.  ========\n"

    def test_bytes_io(self, engine, ext):
        # see gh-7074
        bio = BytesIO()
        df = DataFrame(np.random.randn(10, 2))

        # Pass engine explicitly, as there is no file path to infer from.
        writer = ExcelWriter(bio, engine=engine)
        df.to_excel(writer)
        writer.save()

        bio.seek(0)
        reread_df = pd.read_excel(bio, index_col=0)
        tm.assert_frame_equal(df, reread_df)

 def save_all(self):
     '''
     Dumps all solicitations in both databases to an excel file,
     into two separate spreadsheets: one for filtered items, the other
     for the remaining (relevant) items
     '''
     print "\n\n========  Saving solicitations...  ========"
     writer = ExcelWriter(self.db_filename)
     self.sol_df.to_excel(writer,self.sol_sheet_name,merge_cells=False)
     self.filtered_df.to_excel(writer,self.filtered_sheet_name,merge_cells=False)
     writer.save()
     writer.close()
     print "========  Done saving.  ========\n"

    def to_excel(self, path, na_rep='', engine=None, **kwargs):
        """
        Write each DataFrame in Panel to a separate excel sheet

        Parameters
        ----------
        path : string or ExcelWriter object
            File path or existing ExcelWriter
        na_rep : string, default ''
            Missing data representation
        engine : string, default None
            write engine to use - you can also set this via the options
            ``io.excel.xlsx.writer``, ``io.excel.xls.writer``, and
            ``io.excel.xlsm.writer``.

        Other Parameters
        ----------------
        float_format : string, default None
            Format string for floating point numbers
        cols : sequence, optional
            Columns to write
        header : boolean or list of string, default True
            Write out column names. If a list of string is given it is
            assumed to be aliases for the column names
        index : boolean, default True
            Write row names (index)
        index_label : string or sequence, default None
            Column label for index column(s) if desired. If None is given, and
            `header` and `index` are True, then the index names are used. A
            sequence should be given if the DataFrame uses MultiIndex.
        startow : upper left cell row to dump data frame
        startcol : upper left cell column to dump data frame

        Notes
        -----
        Keyword arguments (and na_rep) are passed to the ``to_excel`` method
        for each DataFrame written.
        """
        from pandas.io.excel import ExcelWriter

        if isinstance(path, compat.string_types):
            writer = ExcelWriter(path, engine=engine)
        else:
            writer = path
        kwargs['na_rep'] = na_rep

        for item, df in compat.iteritems(self):
            name = str(item)
            df.to_excel(writer, name, **kwargs)
        writer.save()

    def to_excel(self, path, na_rep=''):
        """
        Write each DataFrame in Panel to a separate excel sheet

        Parameters
        ----------
        excel_writer : string or ExcelWriter object
            File path or existing ExcelWriter
        na_rep : string, default ''
            Missing data representation
        """
        from pandas.io.excel import ExcelWriter
        writer = ExcelWriter(path)
        for item, df in compat.iteritems(self):
            name = str(item)
            df.to_excel(writer, name, na_rep=na_rep)
        writer.save()

 def generate_report(self):
     '''
     Generates a separate excel report, consisting of non-award-type notices
     that are not yet overdue
     '''
     print "\n\n========  Generating report...  ========"
     df = self.dataframe.copy()
     ix = pd.Series([(True if ix in self.added_items else False ) 
                                   for ix in df.index ],
                                   index=df.index)
     report_df = df[ix == True]
     
     writer = ExcelWriter(self.report_filename)
     report_df.to_excel(writer,self.sheet_name,merge_cells=False)
     writer.save()
     writer.close()
     
     print "========  Report Generated as " + self.report_filename + " ========\n"

 def generate_report(self):
     '''
     Generates a separate excel report, consisting of non-award-type notices
     that are not yet overdue
     '''
     print "\n\n========  Generating report...  ========"
     today = datetime.today()
     df = self.sol_df.copy()
     df["new"] = pd.Series([(True if ix in self.added_items else False ) 
                                   for ix in df.index ],
                                   index=df.index)
     
     report_df = df[(df["proposal_due_date"] >= today) | (df["proposal_due_date"] == None)]
     
     writer = ExcelWriter(self.report_filename)
     report_df.to_excel(writer,self.sol_sheet_name,merge_cells=False)
     writer.save()
     writer.close()
     
     print "========  Report Generated as " + self.report_filename + " ========\n"