xl

Read and Write Excel Files.

Functions

load(xlfile, sheets=[], columns=[], datatype=None, headers=True)

Read data from Excel file.

Parameters:

Name	Type	Description	Default
xlfile	str	full path of the excel file to read	required
sheets	list	worksheets to read. Defaults to all the sheets.	[]
columns	list	Names to give to the columns. It builds a dictionary for each row with the column name and value. If none given (default), it returns a simple list of values.	[]
datatype	type	Type of the data. Defaults to None.	None
headers	bool	Whether to use the first row as headers. Defaults to True.	True

Returns:

Type	Description
dict[str, dict[str, Any]]	Excel data grouped by worksheet. Each worksheet is a dictionary with headers and rows.

Source code in pyrevitlib/pyrevit/interop/xl.py

def load(xlfile, sheets=[], columns=[], datatype=None, headers=True):
    """Read data from Excel file.

    Args:
        xlfile (str): full path of the excel file to read
        sheets (list, optional): worksheets to read. Defaults to all the sheets.
        columns (list, optional): Names to give to the columns.
            It builds a dictionary for each row with the column name and value.
            If none given (default), it returns a simple list of values.
        datatype (type, optional): Type of the data. Defaults to None.
        headers (bool, optional): Whether to use the first row as headers.
            Defaults to True.

    Returns:
        (dict[str, dict[str, Any]]): Excel data grouped by worksheet.
            Each worksheet is a dictionary with `headers` and `rows`.
    """
    xldata = {}
    xlwb = xlrd.open_workbook(xlfile)
    for xlsheet in xlwb.sheets():
        if sheets:
            if xlsheet.name in sheets:
                xldata[xlsheet.name] = _read_xlsheet(xlsheet,
                                                     columns=columns,
                                                     datatype=datatype,
                                                     headers=headers)
        else:
            xldata[xlsheet.name] = _read_xlsheet(xlsheet,
                                                 columns=columns,
                                                 datatype=datatype,
                                                 headers=headers)
    return xldata

dump(xlfile, datadict)

Write data to Excel file.

Creates a worksheet for each item of the input dictionary.

Parameters:

Name	Type	Description	Default
xlfile	str	full path of the target excel file	required
datadict	dict[str, list]	dictionary of worksheets names and data	required

Source code in pyrevitlib/pyrevit/interop/xl.py

def dump(xlfile, datadict):
    """Write data to Excel file.

    Creates a worksheet for each item of the input dictionary.

    Args:
        xlfile (str): full path of the target excel file
        datadict (dict[str, list]): dictionary of worksheets names and data
    """
    xlwb = xlsxwriter.Workbook(xlfile)
    # bold = xlwb.add_format({'bold': True})
    for xlsheetname, xlsheetdata in datadict.items():
        xlsheet = xlwb.add_worksheet(xlsheetname)
        for idx, data in enumerate(xlsheetdata):
            xlsheet.write_row(idx, 0, data)
    xlwb.close()