Skip to content

Excel Exporter

Bases: DataFrameExporter


              flowchart TD
              metaframe.src.dataframe.exporter.excel.ExcelExporter[ExcelExporter]
              metaframe.src.dataframe.exporter.base.DataFrameExporter[DataFrameExporter]

                              metaframe.src.dataframe.exporter.base.DataFrameExporter --> metaframe.src.dataframe.exporter.excel.ExcelExporter
                


              click metaframe.src.dataframe.exporter.excel.ExcelExporter href "" "metaframe.src.dataframe.exporter.excel.ExcelExporter"
              click metaframe.src.dataframe.exporter.base.DataFrameExporter href "" "metaframe.src.dataframe.exporter.base.DataFrameExporter"
Source code in metaframe/src/dataframe/exporter/excel.py 
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
class ExcelExporter(DataFrameExporter):

    Opts = ExportOptionsExcel

    # Override
    def __init__(self, df, *args, **kwargs) -> None:
        super().__init__(df, *args, **kwargs)
        self.writer: pd.ExcelWriter = None  # set by `export()`
        self.d_format: Dict = None          # set by `export()`

    # Override
    def prepare_opts(self, *args, **kwargs) -> Opts:
        opts = super().prepare_opts(*args, **kwargs)
        if opts.merge_cells and opts.extensive:
            raise NotImplementedError("Can not write in extensive mode with merged cells!")
        if opts.grp_row is not None:
            if opts.grp_row_by is None:
                opts.grp_row_by = opts.grp_row
            if opts.grp_row_whole_by is None:
                opts.grp_row_whole_by = opts.grp_row
        if opts.grp_col is not None:
            if opts.grp_col_by is None:
                opts.grp_col_by = opts.grp_col
            if opts.grp_col_whole_by is None:
                opts.grp_col_whole_by = opts.grp_col
        if isinstance(opts.grp_row_whole_by, str):
            opts.grp_row_whole_by = [opts.grp_row_whole_by]
        if isinstance(opts.grp_col_whole_by, str):
            opts.grp_col_whole_by = [opts.grp_col_whole_by]
        if isinstance(opts.grp_row_by, str):
            opts.grp_row_by = [opts.grp_row_by]
        if isinstance(opts.grp_col_by, str):
            opts.grp_col_by = [opts.grp_col_by]
        return opts

    # Override
    def export(self) -> None:
        self.writer = self.prepare_file()
        try:
            self.d_format = self.prepare_format()
            if self.opts.sheet_name:
                self._export()
            if self.opts.write_summaries:
                self.export_summaries()
        finally:
            if self.opts.output is not None:
                self.writer.close()

    def export_summaries(self) -> None:
        """
        Export summary informations to an Excel friendly format.
        """
        df = self.df
        if COLNAME_METAFRAME_UNIQUE_ID in df.index.names:
            df = df.droplevel(COLNAME_METAFRAME_UNIQUE_ID, axis=0)
        if COLNAME_METAFRAME_UNIQUE_ID in df.columns.names:
            df = df.droplevel(COLNAME_METAFRAME_UNIQUE_ID, axis=1)
        dfs = df.to_summary(**self.opts.kwargs_summary)
        # Basic summary
        if self.opts.sheet_name_summary_basic is not None:
            dfs.basic().to_file_excel(self.writer, sheet_name=self.opts.sheet_name_summary_basic, index=True, extensive=False, write_dtypes=False, write_summaries=False, _no_col=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
        # Whole summary
        if self.opts.sheet_name_summary_whole is not None:
            dfs.whole().to_file_excel(self.writer, sheet_name=self.opts.sheet_name_summary_whole, extensive=False, write_dtypes=False, write_summaries=False, _no_col=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
        # Whole summary grouped by row
        if self.opts.sheet_name_summary_grp_row_whole is not None and self.opts.grp_row_whole_by is not None:
            for i, by in enumerate(self.opts.grp_row_whole_by):
                dfs.whole(by=by, axis=0).to_file_excel(self.writer, sheet_name=f"{self.opts.sheet_name_summary_grp_row_whole}{i+1}", extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _invert_col=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
        # Whole summary grouped by col
        if self.opts.sheet_name_summary_grp_col_whole is not None and self.opts.grp_col_whole_by is not None:
            for i, by in enumerate(self.opts.grp_col_whole_by):
                dfs.whole(by=by, axis=1).to_file_excel(self.writer, sheet_name=f"{self.opts.sheet_name_summary_grp_col_whole}{i+1}", extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
        # Per row summary
        if self.opts.sheet_name_summary_row is not None:
            dfs.row().mfloc[:, self.opts.summary_row_id].to_file(self.writer, sheet_name=self.opts.sheet_name_summary_row, extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _invert_col=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
        # Per col summary
        if self.opts.sheet_name_summary_col is not None:
            dfs.col().mfloc[:, self.opts.summary_col_id].to_file(self.writer, sheet_name=self.opts.sheet_name_summary_col, extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
        # Per grouped row summary
        if self.opts.sheet_name_summary_grp_row is not None and self.opts.grp_row_by is not None:
            for i, by in enumerate(self.opts.grp_row_by):
                dfs.row(by=by).mfloc[:, self.opts.summary_row_id].to_file(self.writer, sheet_name=f"{self.opts.sheet_name_summary_grp_row}{i+1}", extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _invert_col=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
        # Per grouped col summary
        if self.opts.sheet_name_summary_grp_col is not None and self.opts.grp_col_by is not None:
            for i, by in enumerate(self.opts.grp_col_by):
                dfs.col(by=by).mfloc[:, self.opts.summary_col_id].to_file(self.writer, sheet_name=f"{self.opts.sheet_name_summary_grp_col}{i+1}", extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)

    def prepare_file(self) -> pd.ExcelWriter:
        """
        If needed, open the output file.
        Can reset `output` option to None if the file is already open.

        Returns
        -------
        pd.ExcelWriter
        """
        is_file_path = isinstance(self.opts.output, str)
        if not is_file_path and not isinstance(self.opts.output, pd.ExcelWriter):
            raise ValueError("Parameter 'output' must be a file path or an ExcelWriter object!")
        if is_file_path:
            writer = pd.ExcelWriter(self.opts.output, engine='xlsxwriter') # engine_kwargs={"options": {"use_future_functions": True}}
        else:
            writer = self.opts.output
            self.opts.output = None
        return writer

    def prepare_format(self) -> Dict:
        """
        Sets the row/columns/cell formats.

        Returns
        -------
        Dict
            a dictionary of formats
        """
        if self.opts._d_format is None:
            workbook = self.writer.book
            format_header = workbook.add_format(XLSX_FORMAT_HEADER)
            d_format = {
                "header": format_header,
                "header_row": workbook.add_format(XLSX_FORMAT_HEADER_ROW),
                "header_col": workbook.add_format(XLSX_FORMAT_HEADER_COL),
                "row": workbook.add_format(XLSX_FORMAT_ROW),
                "col": workbook.add_format(XLSX_FORMAT_COL),
                "cell": workbook.add_format(XLSX_FORMAT_CELL),
                "empty": workbook.add_format(XLSX_FORMAT_EMPTY)
            }
        else:
            d_format = self.opts._d_format.copy()
        if self.opts._mfr is not None:
            if self.opts._mfr:
                d_format["header_col"] = d_format["header_row"]
            d_format["header_row"] = d_format["header"]
        else:
            if self.opts._invert_col:
                d_format["header_row"], d_format["header_col"] = d_format["header_col"], d_format["header_row"]
            if self.is_table or self.opts._no_col or self.opts._no_col_mfr:
                d_format["header_row"] = d_format["header"]
            if self.is_table or self.opts._no_col or self.opts._no_col_mfc:
                d_format["header_col"] = d_format["header"]
        return d_format

    def _export(self) -> None:
        """
        Main Excel sheet(s) export, containing the DataFrame raw informations.
        """
        if self.opts.extensive:
            self._export_dataframe_extensive()
        elif self.is_table:
            self._export_table()
        else:
            self._export_dataframe()

    def _export_dataframe_extensive(self) -> None:
        """
        Export the DataFrame with extensive format.
        """
        workbook = self.writer.book
        # Create main sheets
        worksheet_ds = workbook.add_worksheet(self.opts.sheet_name)
        worksheet_mfr = workbook.add_worksheet(self.opts.sheet_name_mfr)
        worksheet_mfc = workbook.add_worksheet(self.opts.sheet_name_mfc)
        # Write MF and Hide first column (containing the unique ID)
        self.df.mfr.to_file(self.writer, sheet_name=self.opts.sheet_name_mfr, write_dtypes=False, _d_format=self.d_format, _no_col=self.opts._no_col, _no_col_mfr=self.opts._no_col_mfr, _invert_col=self.opts._invert_col)
        self.df.mfc.to_file(self.writer, sheet_name=self.opts.sheet_name_mfc, write_dtypes=False, _d_format=self.d_format, _no_col=self.opts._no_col, _no_col_mfc=self.opts._no_col_mfc, _invert_col=self.opts._invert_col)
        worksheet_mfr.set_column(0, 0, options={'hidden': True})
        worksheet_mfc.set_column(0, 0, options={'hidden': True})
        # Write raw values (the worksheet will be hidden)
        pd.DataFrame(self.df.values).to_excel(self.writer, index=False, header=False, sheet_name=RAW_WORKSHEET)
        self.writer.sheets[RAW_WORKSHEET].hide()
        # Write main sheet and Hide DF first column and first row (containing the unique ID)
        self._write_dynamic_array_formula()
        worksheet_ds.set_column(0, 0, options={'hidden': True})
        worksheet_ds.set_row(0, 0, options={'hidden': True})
        # Replace empty cells between MFC and the values
        self._write_placeholder()
        # Write dtypes
        self.export_dtypes()
        # Apply formats
        self._conditional_format()
        self._freeze_panes()  

    def _export_dataframe(self) -> None:
        """
        Export a non-table DataFrame in a non-extensive format.
        """
        mfr = self.df.mf(axis=0)
        mfc = self.df.mf(axis=1)
        _, mfr_n_col = self.mfr_shape
        _, mfc_n_col = self.mfc_shape
        if self.opts.merge_cells:
            self.df.to_excel(self.writer, sheet_name=self.opts.sheet_name, merge_cells=True, **self.opts.kwargs)
        else:
            mfc.T.to_excel(self.writer, merge_cells=False, startrow=0, startcol=mfr_n_col-1, header=False, index=True, sheet_name=self.opts.sheet_name)
            mfr.to_excel(self.writer, merge_cells=False, startrow=mfc_n_col, startcol=0, index=False, sheet_name=self.opts.sheet_name)
            pd.DataFrame(self.df.values).to_excel(self.writer, merge_cells=False, startrow=mfc_n_col+1, startcol=mfr_n_col, header=False, index=False, sheet_name=self.opts.sheet_name)
        self._write_placeholder()
        self.export_dtypes()
        self._conditional_format()
        self._freeze_panes()
        self._auto_filter()

    def _export_table(self) -> None:
        """
        Export a table DataFrame
        """
        self.df.to_excel(self.writer, index=self.opts.index, header=self.opts.header, sheet_name=self.opts.sheet_name, **self.opts.kwargs)
        self.export_dtypes()
        self._conditional_format()
        self._freeze_panes()
        if self.opts.header:
            self._auto_filter()

    def _auto_filter(self) -> None:
        """
        Add auto-filters.
        """
        worksheet = self.writer.sheets[self.opts.sheet_name]
        mfr_n_row, mfr_n_col = self.mfr_shape
        mfc_n_row, mfc_n_col = self.mfc_shape
        if self.is_table:
            worksheet.autofilter(0, 0, mfr_n_row, mfc_n_col-1+int(self.opts.index))
        else:
            worksheet.autofilter(mfc_n_col, 0, mfc_n_col+mfr_n_row, mfr_n_col+mfc_n_row-1)

    def _write_dynamic_array_formula(self) -> None:
        """
        Write the dynamic array formula for extensive format main sheet.

        These formulas ensure that the main sheet reacts to filters in the
        MFR and MFC sheets.
        """
        ## Compatible with Google Sheet
        worksheet = self.writer.sheets[self.opts.sheet_name]
        mfr_n_row, mfr_n_col = self.mfr_shape
        mfc_n_row, mfc_n_col = self.mfc_shape
        worksheet.write_dynamic_array_formula(0, mfr_n_col-1, mfc_n_col-1, mfr_n_col-1+mfc_n_row, F_EXCEL_FORMULA.format(F_EXCEL_TRANSPOSE.format(F_EXCEL_CHOOSE_MF.format(sheet_name=self.opts.sheet_name_mfc, col=n_to_excel_column(mfc_n_col), row=mfc_n_row+1))))
        worksheet.write_dynamic_array_formula(mfc_n_col, 0, mfc_n_col+mfr_n_row, mfr_n_col-1, F_EXCEL_FORMULA.format(F_EXCEL_CHOOSE_MF.format(sheet_name=self.opts.sheet_name_mfr, col=n_to_excel_column(mfr_n_col), row=mfr_n_row+1)))
        worksheet.write_dynamic_array_formula(mfc_n_col+1, mfr_n_col, mfc_n_col+mfr_n_row, mfr_n_col+mfc_n_row-1, F_EXCEL_FORMULA.format(F_EXCEL_CHOOSE.format(sheet_name=RAW_WORKSHEET, last_col=n_to_excel_column(mfc_n_row), last_row=mfr_n_row, mfr_first_row=mfc_n_col+2, mfr_last_row=mfr_n_row+mfc_n_col+1, mfc_first_col=n_to_excel_column(mfr_n_col+1), mfc_last_col=n_to_excel_column(mfc_n_row+mfr_n_col))))
        ## This is simpler but incompatible with Google Sheet
        # worksheet.write_formula(0, mfr_n_col-1, F_EXCEL_FORMULA.format(F_EXCEL_TRANSPOSE.format(F_EXCEL_CHOOSE_MF.format(sheet_name=sheet_name_mfc, col=n_to_excel_column(mfc_n_col), row=mfc_n_row+1))))
        # worksheet.write_formula(mfc_n_col, 0, F_EXCEL_FORMULA.format(F_EXCEL_CHOOSE_MF.format(sheet_name=sheet_name_mfr, col=n_to_excel_column(mfr_n_col), row=mfr_n_row+1)))
        # worksheet.write_formula(mfc_n_col+1, mfr_n_col, F_EXCEL_FORMULA.format(F_EXCEL_CHOOSE.format(sheet_name=RAW_WORKSHEET, last_col=n_to_excel_column(self.shape[1]), last_row=self.shape[0], mfr_first_row=mfc_n_col+2, mfr_last_row=self.shape[0]+mfc_n_col+1, mfc_first_col=n_to_excel_column(mfr_n_col+1), mfc_last_col=n_to_excel_column(self.shape[1]+mfr_n_col))))

    def _write_placeholder(self) -> None:
        """
        Write specific placeholder to garantee error-less reading of the Excel file.
        """
        worksheet_ds = self.writer.sheets[self.opts.sheet_name]
        _, mfr_n_col = self.mfr_shape
        _, mfc_n_col = self.mfc_shape
        for col in range(mfr_n_col, mfr_n_col+self.df.shape[1]):
            worksheet_ds.write(mfc_n_col, col, DF_EMPTY_CELL, self.d_format["empty"])

    def _freeze_panes(self):
        """
        Add freeze panes.
        """
        if not self.opts.freeze_panes:
            return
        worksheet = self.writer.sheets[self.opts.sheet_name]
        _, mfr_n_col = self.mfr_shape
        _, mfc_n_col = self.mfc_shape
        if self.is_table:
            worksheet.freeze_panes(int(self.opts.header), int(self.opts.index))
        else:
            worksheet.freeze_panes(mfc_n_col, mfr_n_col)

    def _conditional_format(self) -> None:
        """
        Apply the format in `self.d_format`.
        """
        worksheet = self.writer.sheets[self.opts.sheet_name]
        mfr_n_row, mfr_n_col = self.mfr_shape
        mfc_n_row, mfc_n_col = self.mfc_shape
        if self.is_table:
            if self.opts.index:
                worksheet.conditional_format(mfc_n_row, 0, mfc_n_row+mfr_n_row-1, 0, {'type': 'no_errors', 'format': self.d_format["header_row"]})
            if self.opts.header:
                worksheet.conditional_format(0, mfr_n_col, 0, mfr_n_col+mfc_n_col-1, {'type': 'no_errors', 'format': self.d_format["header_col"]})
            worksheet.conditional_format(int(self.opts.header), int(self.opts.index), int(self.opts.header)+mfr_n_row-1, self.opts.index+mfc_n_col-1, {'type': 'no_errors', 'format': self.d_format["cell"]})
            return
        worksheet.conditional_format(mfc_n_col, 0, mfc_n_col, mfr_n_col - 1, {'type': 'no_errors', 'format': self.d_format["header_row"]})
        worksheet.conditional_format(0, mfr_n_col - 1, mfc_n_col-1, mfr_n_col - 1, {'type': 'no_errors', 'format': self.d_format["header_col"]})
        if self.opts.extensive:
            for row_num in range(mfc_n_col+1, mfc_n_col+1+mfr_n_row):
                worksheet.conditional_format(row_num, 0, row_num, mfr_n_col-1, {'type': 'formula', 'format': self.d_format["row"], 'criteria': f'=NOT(ISBLANK($A{row_num+1}))'})
            for col_num in range(mfr_n_col, mfr_n_col+mfc_n_row):
                worksheet.conditional_format(0, col_num, mfc_n_col-1, col_num, {'type': 'formula', 'format': self.d_format["col"], 'criteria': f'=NOT(ISBLANK({n_to_excel_column(col_num+1)}$1))'})
        else:
            worksheet.conditional_format(mfc_n_col+1, 0, mfc_n_col+mfr_n_row, mfr_n_col-1, {'type': 'no_errors', 'format': self.d_format["row"]})
            worksheet.conditional_format(0, mfr_n_col, mfc_n_col-1, mfr_n_col+mfc_n_row-1, {'type': 'no_errors', 'format': self.d_format["col"]})

    def export_dtypes(self) -> None:
        """
        Export dtypes information of the DataFrame.

        Enable faster reading of the Excel file.
        """
        if not self.opts.write_dtypes:
            return
        if not self.is_table:
            for dtypes, dt_sheet_name in zip([pd.DataFrame(e.dtypes).T for e in [self.df.mf(axis=0), self.df.mf(axis=1)]], [MFR_DTYPES_WORKSHEET, MFC_DTYPES_WORKSHEET]):
                if COLNAME_METAFRAME_UNIQUE_ID in dtypes.columns:
                    dtypes = dtypes.drop(COLNAME_METAFRAME_UNIQUE_ID, axis=1)
                dtypes.to_excel(self.writer, sheet_name=dt_sheet_name, index=False, header=True)
                self.writer.sheets[dt_sheet_name].hide()
        pd.DataFrame(self.df.dtypes).reset_index(drop=True).T.to_excel(self.writer, sheet_name=DF_DTYPES_WORKSHEET, index=False, header=False)
        self.writer.sheets[DF_DTYPES_WORKSHEET].hide()

export_summaries()

Export summary informations to an Excel friendly format.

Source code in metaframe/src/dataframe/exporter/excel.py 
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
def export_summaries(self) -> None:
    """
    Export summary informations to an Excel friendly format.
    """
    df = self.df
    if COLNAME_METAFRAME_UNIQUE_ID in df.index.names:
        df = df.droplevel(COLNAME_METAFRAME_UNIQUE_ID, axis=0)
    if COLNAME_METAFRAME_UNIQUE_ID in df.columns.names:
        df = df.droplevel(COLNAME_METAFRAME_UNIQUE_ID, axis=1)
    dfs = df.to_summary(**self.opts.kwargs_summary)
    # Basic summary
    if self.opts.sheet_name_summary_basic is not None:
        dfs.basic().to_file_excel(self.writer, sheet_name=self.opts.sheet_name_summary_basic, index=True, extensive=False, write_dtypes=False, write_summaries=False, _no_col=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
    # Whole summary
    if self.opts.sheet_name_summary_whole is not None:
        dfs.whole().to_file_excel(self.writer, sheet_name=self.opts.sheet_name_summary_whole, extensive=False, write_dtypes=False, write_summaries=False, _no_col=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
    # Whole summary grouped by row
    if self.opts.sheet_name_summary_grp_row_whole is not None and self.opts.grp_row_whole_by is not None:
        for i, by in enumerate(self.opts.grp_row_whole_by):
            dfs.whole(by=by, axis=0).to_file_excel(self.writer, sheet_name=f"{self.opts.sheet_name_summary_grp_row_whole}{i+1}", extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _invert_col=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
    # Whole summary grouped by col
    if self.opts.sheet_name_summary_grp_col_whole is not None and self.opts.grp_col_whole_by is not None:
        for i, by in enumerate(self.opts.grp_col_whole_by):
            dfs.whole(by=by, axis=1).to_file_excel(self.writer, sheet_name=f"{self.opts.sheet_name_summary_grp_col_whole}{i+1}", extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
    # Per row summary
    if self.opts.sheet_name_summary_row is not None:
        dfs.row().mfloc[:, self.opts.summary_row_id].to_file(self.writer, sheet_name=self.opts.sheet_name_summary_row, extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _invert_col=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
    # Per col summary
    if self.opts.sheet_name_summary_col is not None:
        dfs.col().mfloc[:, self.opts.summary_col_id].to_file(self.writer, sheet_name=self.opts.sheet_name_summary_col, extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
    # Per grouped row summary
    if self.opts.sheet_name_summary_grp_row is not None and self.opts.grp_row_by is not None:
        for i, by in enumerate(self.opts.grp_row_by):
            dfs.row(by=by).mfloc[:, self.opts.summary_row_id].to_file(self.writer, sheet_name=f"{self.opts.sheet_name_summary_grp_row}{i+1}", extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _invert_col=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)
    # Per grouped col summary
    if self.opts.sheet_name_summary_grp_col is not None and self.opts.grp_col_by is not None:
        for i, by in enumerate(self.opts.grp_col_by):
            dfs.col(by=by).mfloc[:, self.opts.summary_col_id].to_file(self.writer, sheet_name=f"{self.opts.sheet_name_summary_grp_col}{i+1}", extensive=False, write_dtypes=False, write_summaries=False, _no_col_mfr=True, _d_format=self.d_format, merge_cells=self.opts.merge_cells_summary)

prepare_file()

If needed, open the output file. Can reset output option to None if the file is already open.

Returns:

Type Description
ExcelWriter
Source code in metaframe/src/dataframe/exporter/excel.py 
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
def prepare_file(self) -> pd.ExcelWriter:
    """
    If needed, open the output file.
    Can reset `output` option to None if the file is already open.

    Returns
    -------
    pd.ExcelWriter
    """
    is_file_path = isinstance(self.opts.output, str)
    if not is_file_path and not isinstance(self.opts.output, pd.ExcelWriter):
        raise ValueError("Parameter 'output' must be a file path or an ExcelWriter object!")
    if is_file_path:
        writer = pd.ExcelWriter(self.opts.output, engine='xlsxwriter') # engine_kwargs={"options": {"use_future_functions": True}}
    else:
        writer = self.opts.output
        self.opts.output = None
    return writer

prepare_format()

Sets the row/columns/cell formats.

Returns:

Type Description
Dict

a dictionary of formats
Source code in metaframe/src/dataframe/exporter/excel.py 
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
def prepare_format(self) -> Dict:
    """
    Sets the row/columns/cell formats.

    Returns
    -------
    Dict
        a dictionary of formats
    """
    if self.opts._d_format is None:
        workbook = self.writer.book
        format_header = workbook.add_format(XLSX_FORMAT_HEADER)
        d_format = {
            "header": format_header,
            "header_row": workbook.add_format(XLSX_FORMAT_HEADER_ROW),
            "header_col": workbook.add_format(XLSX_FORMAT_HEADER_COL),
            "row": workbook.add_format(XLSX_FORMAT_ROW),
            "col": workbook.add_format(XLSX_FORMAT_COL),
            "cell": workbook.add_format(XLSX_FORMAT_CELL),
            "empty": workbook.add_format(XLSX_FORMAT_EMPTY)
        }
    else:
        d_format = self.opts._d_format.copy()
    if self.opts._mfr is not None:
        if self.opts._mfr:
            d_format["header_col"] = d_format["header_row"]
        d_format["header_row"] = d_format["header"]
    else:
        if self.opts._invert_col:
            d_format["header_row"], d_format["header_col"] = d_format["header_col"], d_format["header_row"]
        if self.is_table or self.opts._no_col or self.opts._no_col_mfr:
            d_format["header_row"] = d_format["header"]
        if self.is_table or self.opts._no_col or self.opts._no_col_mfc:
            d_format["header_col"] = d_format["header"]
    return d_format

_export()

Main Excel sheet(s) export, containing the DataFrame raw informations.

Source code in metaframe/src/dataframe/exporter/excel.py 
169
170
171
172
173
174
175
176
177
178
def _export(self) -> None:
    """
    Main Excel sheet(s) export, containing the DataFrame raw informations.
    """
    if self.opts.extensive:
        self._export_dataframe_extensive()
    elif self.is_table:
        self._export_table()
    else:
        self._export_dataframe()

_export_dataframe_extensive()

Export the DataFrame with extensive format.

Source code in metaframe/src/dataframe/exporter/excel.py 
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
def _export_dataframe_extensive(self) -> None:
    """
    Export the DataFrame with extensive format.
    """
    workbook = self.writer.book
    # Create main sheets
    worksheet_ds = workbook.add_worksheet(self.opts.sheet_name)
    worksheet_mfr = workbook.add_worksheet(self.opts.sheet_name_mfr)
    worksheet_mfc = workbook.add_worksheet(self.opts.sheet_name_mfc)
    # Write MF and Hide first column (containing the unique ID)
    self.df.mfr.to_file(self.writer, sheet_name=self.opts.sheet_name_mfr, write_dtypes=False, _d_format=self.d_format, _no_col=self.opts._no_col, _no_col_mfr=self.opts._no_col_mfr, _invert_col=self.opts._invert_col)
    self.df.mfc.to_file(self.writer, sheet_name=self.opts.sheet_name_mfc, write_dtypes=False, _d_format=self.d_format, _no_col=self.opts._no_col, _no_col_mfc=self.opts._no_col_mfc, _invert_col=self.opts._invert_col)
    worksheet_mfr.set_column(0, 0, options={'hidden': True})
    worksheet_mfc.set_column(0, 0, options={'hidden': True})
    # Write raw values (the worksheet will be hidden)
    pd.DataFrame(self.df.values).to_excel(self.writer, index=False, header=False, sheet_name=RAW_WORKSHEET)
    self.writer.sheets[RAW_WORKSHEET].hide()
    # Write main sheet and Hide DF first column and first row (containing the unique ID)
    self._write_dynamic_array_formula()
    worksheet_ds.set_column(0, 0, options={'hidden': True})
    worksheet_ds.set_row(0, 0, options={'hidden': True})
    # Replace empty cells between MFC and the values
    self._write_placeholder()
    # Write dtypes
    self.export_dtypes()
    # Apply formats
    self._conditional_format()
    self._freeze_panes()

_export_dataframe()

Export a non-table DataFrame in a non-extensive format.

Source code in metaframe/src/dataframe/exporter/excel.py 
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
def _export_dataframe(self) -> None:
    """
    Export a non-table DataFrame in a non-extensive format.
    """
    mfr = self.df.mf(axis=0)
    mfc = self.df.mf(axis=1)
    _, mfr_n_col = self.mfr_shape
    _, mfc_n_col = self.mfc_shape
    if self.opts.merge_cells:
        self.df.to_excel(self.writer, sheet_name=self.opts.sheet_name, merge_cells=True, **self.opts.kwargs)
    else:
        mfc.T.to_excel(self.writer, merge_cells=False, startrow=0, startcol=mfr_n_col-1, header=False, index=True, sheet_name=self.opts.sheet_name)
        mfr.to_excel(self.writer, merge_cells=False, startrow=mfc_n_col, startcol=0, index=False, sheet_name=self.opts.sheet_name)
        pd.DataFrame(self.df.values).to_excel(self.writer, merge_cells=False, startrow=mfc_n_col+1, startcol=mfr_n_col, header=False, index=False, sheet_name=self.opts.sheet_name)
    self._write_placeholder()
    self.export_dtypes()
    self._conditional_format()
    self._freeze_panes()
    self._auto_filter()

_export_table()

Export a table DataFrame

Source code in metaframe/src/dataframe/exporter/excel.py 
229
230
231
232
233
234
235
236
237
238
def _export_table(self) -> None:
    """
    Export a table DataFrame
    """
    self.df.to_excel(self.writer, index=self.opts.index, header=self.opts.header, sheet_name=self.opts.sheet_name, **self.opts.kwargs)
    self.export_dtypes()
    self._conditional_format()
    self._freeze_panes()
    if self.opts.header:
        self._auto_filter()

_auto_filter()

Add auto-filters.

Source code in metaframe/src/dataframe/exporter/excel.py 
240
241
242
243
244
245
246
247
248
249
250
def _auto_filter(self) -> None:
    """
    Add auto-filters.
    """
    worksheet = self.writer.sheets[self.opts.sheet_name]
    mfr_n_row, mfr_n_col = self.mfr_shape
    mfc_n_row, mfc_n_col = self.mfc_shape
    if self.is_table:
        worksheet.autofilter(0, 0, mfr_n_row, mfc_n_col-1+int(self.opts.index))
    else:
        worksheet.autofilter(mfc_n_col, 0, mfc_n_col+mfr_n_row, mfr_n_col+mfc_n_row-1)

_write_dynamic_array_formula()

Write the dynamic array formula for extensive format main sheet.

These formulas ensure that the main sheet reacts to filters in the MFR and MFC sheets.

Source code in metaframe/src/dataframe/exporter/excel.py 
252
253
254
255
256
257
258
259
260
261
262
263
264
265
def _write_dynamic_array_formula(self) -> None:
    """
    Write the dynamic array formula for extensive format main sheet.

    These formulas ensure that the main sheet reacts to filters in the
    MFR and MFC sheets.
    """
    ## Compatible with Google Sheet
    worksheet = self.writer.sheets[self.opts.sheet_name]
    mfr_n_row, mfr_n_col = self.mfr_shape
    mfc_n_row, mfc_n_col = self.mfc_shape
    worksheet.write_dynamic_array_formula(0, mfr_n_col-1, mfc_n_col-1, mfr_n_col-1+mfc_n_row, F_EXCEL_FORMULA.format(F_EXCEL_TRANSPOSE.format(F_EXCEL_CHOOSE_MF.format(sheet_name=self.opts.sheet_name_mfc, col=n_to_excel_column(mfc_n_col), row=mfc_n_row+1))))
    worksheet.write_dynamic_array_formula(mfc_n_col, 0, mfc_n_col+mfr_n_row, mfr_n_col-1, F_EXCEL_FORMULA.format(F_EXCEL_CHOOSE_MF.format(sheet_name=self.opts.sheet_name_mfr, col=n_to_excel_column(mfr_n_col), row=mfr_n_row+1)))
    worksheet.write_dynamic_array_formula(mfc_n_col+1, mfr_n_col, mfc_n_col+mfr_n_row, mfr_n_col+mfc_n_row-1, F_EXCEL_FORMULA.format(F_EXCEL_CHOOSE.format(sheet_name=RAW_WORKSHEET, last_col=n_to_excel_column(mfc_n_row), last_row=mfr_n_row, mfr_first_row=mfc_n_col+2, mfr_last_row=mfr_n_row+mfc_n_col+1, mfc_first_col=n_to_excel_column(mfr_n_col+1), mfc_last_col=n_to_excel_column(mfc_n_row+mfr_n_col))))

_write_placeholder()

Write specific placeholder to garantee error-less reading of the Excel file.

Source code in metaframe/src/dataframe/exporter/excel.py 
271
272
273
274
275
276
277
278
279
def _write_placeholder(self) -> None:
    """
    Write specific placeholder to garantee error-less reading of the Excel file.
    """
    worksheet_ds = self.writer.sheets[self.opts.sheet_name]
    _, mfr_n_col = self.mfr_shape
    _, mfc_n_col = self.mfc_shape
    for col in range(mfr_n_col, mfr_n_col+self.df.shape[1]):
        worksheet_ds.write(mfc_n_col, col, DF_EMPTY_CELL, self.d_format["empty"])

_freeze_panes()

Add freeze panes.

Source code in metaframe/src/dataframe/exporter/excel.py 
281
282
283
284
285
286
287
288
289
290
291
292
293
def _freeze_panes(self):
    """
    Add freeze panes.
    """
    if not self.opts.freeze_panes:
        return
    worksheet = self.writer.sheets[self.opts.sheet_name]
    _, mfr_n_col = self.mfr_shape
    _, mfc_n_col = self.mfc_shape
    if self.is_table:
        worksheet.freeze_panes(int(self.opts.header), int(self.opts.index))
    else:
        worksheet.freeze_panes(mfc_n_col, mfr_n_col)

_conditional_format()

Apply the format in self.d_format.

Source code in metaframe/src/dataframe/exporter/excel.py 
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
def _conditional_format(self) -> None:
    """
    Apply the format in `self.d_format`.
    """
    worksheet = self.writer.sheets[self.opts.sheet_name]
    mfr_n_row, mfr_n_col = self.mfr_shape
    mfc_n_row, mfc_n_col = self.mfc_shape
    if self.is_table:
        if self.opts.index:
            worksheet.conditional_format(mfc_n_row, 0, mfc_n_row+mfr_n_row-1, 0, {'type': 'no_errors', 'format': self.d_format["header_row"]})
        if self.opts.header:
            worksheet.conditional_format(0, mfr_n_col, 0, mfr_n_col+mfc_n_col-1, {'type': 'no_errors', 'format': self.d_format["header_col"]})
        worksheet.conditional_format(int(self.opts.header), int(self.opts.index), int(self.opts.header)+mfr_n_row-1, self.opts.index+mfc_n_col-1, {'type': 'no_errors', 'format': self.d_format["cell"]})
        return
    worksheet.conditional_format(mfc_n_col, 0, mfc_n_col, mfr_n_col - 1, {'type': 'no_errors', 'format': self.d_format["header_row"]})
    worksheet.conditional_format(0, mfr_n_col - 1, mfc_n_col-1, mfr_n_col - 1, {'type': 'no_errors', 'format': self.d_format["header_col"]})
    if self.opts.extensive:
        for row_num in range(mfc_n_col+1, mfc_n_col+1+mfr_n_row):
            worksheet.conditional_format(row_num, 0, row_num, mfr_n_col-1, {'type': 'formula', 'format': self.d_format["row"], 'criteria': f'=NOT(ISBLANK($A{row_num+1}))'})
        for col_num in range(mfr_n_col, mfr_n_col+mfc_n_row):
            worksheet.conditional_format(0, col_num, mfc_n_col-1, col_num, {'type': 'formula', 'format': self.d_format["col"], 'criteria': f'=NOT(ISBLANK({n_to_excel_column(col_num+1)}$1))'})
    else:
        worksheet.conditional_format(mfc_n_col+1, 0, mfc_n_col+mfr_n_row, mfr_n_col-1, {'type': 'no_errors', 'format': self.d_format["row"]})
        worksheet.conditional_format(0, mfr_n_col, mfc_n_col-1, mfr_n_col+mfc_n_row-1, {'type': 'no_errors', 'format': self.d_format["col"]})

export_dtypes()

Export dtypes information of the DataFrame.

Enable faster reading of the Excel file.

Source code in metaframe/src/dataframe/exporter/excel.py 
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
def export_dtypes(self) -> None:
    """
    Export dtypes information of the DataFrame.

    Enable faster reading of the Excel file.
    """
    if not self.opts.write_dtypes:
        return
    if not self.is_table:
        for dtypes, dt_sheet_name in zip([pd.DataFrame(e.dtypes).T for e in [self.df.mf(axis=0), self.df.mf(axis=1)]], [MFR_DTYPES_WORKSHEET, MFC_DTYPES_WORKSHEET]):
            if COLNAME_METAFRAME_UNIQUE_ID in dtypes.columns:
                dtypes = dtypes.drop(COLNAME_METAFRAME_UNIQUE_ID, axis=1)
            dtypes.to_excel(self.writer, sheet_name=dt_sheet_name, index=False, header=True)
            self.writer.sheets[dt_sheet_name].hide()
    pd.DataFrame(self.df.dtypes).reset_index(drop=True).T.to_excel(self.writer, sheet_name=DF_DTYPES_WORKSHEET, index=False, header=False)
    self.writer.sheets[DF_DTYPES_WORKSHEET].hide()

set_mfr_shape()

Sets the expected MetaFrameRow shape in the export.

MetaFrameRow shape for non-tables, DataFrame number of rows & 1 (if header+index) or 0 fr tables

Source code in metaframe/src/dataframe/exporter/base.py 
49
50
51
52
53
54
55
56
57
58
def set_mfr_shape(self) -> None:
    """
    Sets the expected MetaFrameRow shape in the export.

    MetaFrameRow shape for non-tables, DataFrame number of rows & 
    1 (if header+index) or 0 fr tables
    """
    if not self.is_table:
        return self.df.mf(axis=0).shape
    return (self.df.shape[0], 1 if self.opts.header and self.opts.index else 0)

set_mfc_shape()

Sets the expected MetaFrameCol shape in the export.

MetaFrameCol shape for non-tables, DataFrame number of rows & 1 (if header+index) or 0 fr tables

Source code in metaframe/src/dataframe/exporter/base.py 
60
61
62
63
64
65
66
67
68
69
def set_mfc_shape(self) -> None:
    """
    Sets the expected MetaFrameCol shape in the export.

    MetaFrameCol shape for non-tables, DataFrame number of rows & 
    1 (if header+index) or 0 fr tables
    """
    if not self.is_table:
        return self.df.mf(axis=1).shape
    return (1 if self.opts.header and self.opts.index else 0, self.df.shape[1])

prepare_data(df)

Prepare a copy of the DataFrame for serialization.

The returned DataFrame is transformed to ensure it can be written and later reconstructed correctly by readers. Transformations may include:

  • resetting the index/columns
  • normalizing headers
  • converting indices/columns to MultiIndex form
  • adding MetaFrames rows unique identifiers

Parameters:

Name Type Description Default
df DataFrame
required

Returns:

Type Description
DataFrame

A modified copy of the original DataFrame ready for export.
Notes

The original DataFrame is not modified.

Source code in metaframe/src/dataframe/exporter/base.py 
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
def prepare_data(self, df: 'DataFrame') -> 'DataFrame':  # type: ignore # noqa: F821
    """
    Prepare a copy of the DataFrame for serialization.

    The returned DataFrame is transformed to ensure it can be written and
    later reconstructed correctly by readers. Transformations may include:

    - resetting the index/columns
    - normalizing headers
    - converting indices/columns to MultiIndex form
    - adding MetaFrames rows unique identifiers

    Parameters
    ----------
    df: 'DataFrame'

    Returns
    -------
    DataFrame
        A modified copy of the original DataFrame ready for export.

    Notes
    -----
    The original DataFrame is not modified.
    """
    df = df.copy()
    if self.is_table:
        if self.opts.index:
            if df.index.name in [None, 0]:
                # For reading to work corrrectly when index = True and header = True
                df.index.name = DF_EMPTY_CELL
        return df
    if not self.opts.index:
        df = df.reset_index(drop=True)
        df.index.name = DF_EMPTY_CELL
    if not self.opts.header:
        df.columns = range(df.shape[1])
        # df.columns.name = DF_EMPTY_CELL
    if not is_mi(df.index):
        if df.index.name in [0, '0', '', None]:
            df.index.name = DF_EMPTY_CELL
        df.index = pd.MultiIndex.from_frame(df.mfr)
        df.index.names = [DF_EMPTY_CELL]
    if not is_mi(df.columns):
        if df.columns.name in [0, '0', '', None]:
            df.columns.name = DF_EMPTY_CELL
        df.columns = pd.MultiIndex.from_frame(df.mfc)
    if self.opts.extensive:
        # Add unique IDs to MFR and MFC
        mfr = df.mf(axis=0)
        mfr.insert(0, COLNAME_METAFRAME_UNIQUE_ID, df.mfr.index+1)
        df.mfr = mfr
        mfc = df.mf(axis=1)
        mfc.insert(0, COLNAME_METAFRAME_UNIQUE_ID, df.mfc.index+1)
        df.mfc = mfc
    return df