Python openpyxl.cell() Examples

def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0,
                    freeze_panes=None):
        """
        Write given formated cells into Excel an excel sheet

        Parameters
        ----------
        cells : generator
            cell of formated data to save to Excel sheet
        sheet_name : string, default None
            Name of Excel sheet, if None, then use self.cur_sheet
        startrow: upper left cell row to dump data frame
        startcol: upper left cell column to dump data frame
        freeze_panes: integer tuple of length 2
            contains the bottom-most row and right-most column to freeze
        """
        pass 

def load_template_tables(template_workbook):
    """Load template tabs that define the field names and formats for tables found in SRUM"""
    template_tables = {}    
    sheets = template_workbook.get_sheet_names()
    for each_sheet in sheets:
        #open the first sheet in the template
        template_sheet = template_workbook.get_sheet_by_name(each_sheet)
        #retieve the name of the ESE table to populate the sheet with from A1
        ese_template_table = template_sheet.cell(row=1,column=1).value
        #retrieve the names of the ESE table columns and cell styles from row 2 and format commands from row 3 
        template_field = {}
        #Read the first Row B & C in the template into lists so we know what data we are to extract
        for eachcolumn in range(1,template_sheet.max_column+1):
            field_name = template_sheet.cell(row = 2, column = eachcolumn).value
            if field_name == None:
                break
            template_style = template_sheet.cell(row = 4, column = eachcolumn).style
            template_format = template_sheet.cell(row = 3, column = eachcolumn).value
            template_value = template_sheet.cell(row = 4, column = eachcolumn ).value
            if not template_value:
                template_value= field_name
            template_field[field_name] = (template_style,template_format,template_value)
        template_tables[ese_template_table] = (each_sheet, template_field)
    return template_tables 

def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0,
                    freeze_panes=None):
        """
        Write given formated cells into Excel an excel sheet

        Parameters
        ----------
        cells : generator
            cell of formated data to save to Excel sheet
        sheet_name : string, default None
            Name of Excel sheet, if None, then use self.cur_sheet
        startrow: upper left cell row to dump data frame
        startcol: upper left cell column to dump data frame
        freeze_panes: integer tuple of length 2
            contains the bottom-most row and right-most column to freeze
        """
        pass 

def read_vertical(self, sheet, __start, __end):
        __vertical = []
        #print " ... Please use column[n]:column[m], vertical read "
        cell_of_col = sheet[__start:__end]
        for row in cell_of_col:
            for cell in row:
                v = cell.value
                if v == None: continue # do nothing below code, back to next for loop step
                __vertical.append(v) # 리스트 __vertical에 아이디 추가
        return __vertical #__cnt, __cnt_n # 세로 셀 데이터, 데이터 갯수, None 갯수 

def load_template_lookups(template_workbook):
    """Load any tabs named lookup-xyz form the template file for lookups of columns with the same format type"""
    template_lookups = {}
    for each_sheet in template_workbook.get_sheet_names():
        if each_sheet.lower().startswith("lookup-"):
            lookupname = each_sheet.split("-")[1]
            template_sheet = template_workbook.get_sheet_by_name(each_sheet)
            lookup_table = {}
            for eachrow in range(1,template_sheet.max_row+1):
                value = template_sheet.cell(row = eachrow, column = 1).value
                description = template_sheet.cell(row = eachrow, column = 2).value
                lookup_table[value] = description
            template_lookups[lookupname] = lookup_table
    return template_lookups 

def test_column_format(self, merge_cells, ext, engine):
        # Test that column formats are applied to cells. Test for issue #9167.
        # Applicable to xlsxwriter only.
        with warnings.catch_warnings():
            # Ignore the openpyxl lxml warning.
            warnings.simplefilter("ignore")
            import openpyxl

        with ensure_clean(ext) as path:
            frame = DataFrame({'A': [123456, 123456],
                               'B': [123456, 123456]})

            writer = ExcelWriter(path)
            frame.to_excel(writer)

            # Add a number format to col B and ensure it is applied to cells.
            num_format = '#,##0'
            write_workbook = writer.book
            write_worksheet = write_workbook.worksheets()[0]
            col_format = write_workbook.add_format({'num_format': num_format})
            write_worksheet.set_column('B:B', None, col_format)
            writer.save()

            read_workbook = openpyxl.load_workbook(path)
            try:
                read_worksheet = read_workbook['Sheet1']
            except TypeError:
                # compat
                read_worksheet = read_workbook.get_sheet_by_name(name='Sheet1')

            # Get the number format from the cell.
            try:
                cell = read_worksheet['B2']
            except TypeError:
                # compat
                cell = read_worksheet.cell('B2')

            try:
                read_num_format = cell.number_format
            except Exception:
                read_num_format = cell.style.number_format._format_code

            assert read_num_format == num_format 

def test_column_format(self, merge_cells, ext, engine):
        # Test that column formats are applied to cells. Test for issue #9167.
        # Applicable to xlsxwriter only.
        with warnings.catch_warnings():
            # Ignore the openpyxl lxml warning.
            warnings.simplefilter("ignore")
            import openpyxl

        with ensure_clean(ext) as path:
            frame = DataFrame({'A': [123456, 123456],
                               'B': [123456, 123456]})

            writer = ExcelWriter(path)
            frame.to_excel(writer)

            # Add a number format to col B and ensure it is applied to cells.
            num_format = '#,##0'
            write_workbook = writer.book
            write_worksheet = write_workbook.worksheets()[0]
            col_format = write_workbook.add_format({'num_format': num_format})
            write_worksheet.set_column('B:B', None, col_format)
            writer.save()

            read_workbook = openpyxl.load_workbook(path)
            try:
                read_worksheet = read_workbook['Sheet1']
            except TypeError:
                # compat
                read_worksheet = read_workbook.get_sheet_by_name(name='Sheet1')

            # Get the number format from the cell.
            try:
                cell = read_worksheet['B2']
            except TypeError:
                # compat
                cell = read_worksheet.cell('B2')

            try:
                read_num_format = cell.number_format
            except Exception:
                read_num_format = cell.style.number_format._format_code

            assert read_num_format == num_format 

def test_column_format(self, merge_cells, ext, engine):
        # Test that column formats are applied to cells. Test for issue #9167.
        # Applicable to xlsxwriter only.
        with warnings.catch_warnings():
            # Ignore the openpyxl lxml warning.
            warnings.simplefilter("ignore")
            import openpyxl

        with ensure_clean(ext) as path:
            frame = DataFrame({'A': [123456, 123456],
                               'B': [123456, 123456]})

            writer = ExcelWriter(path)
            frame.to_excel(writer)

            # Add a number format to col B and ensure it is applied to cells.
            num_format = '#,##0'
            write_workbook = writer.book
            write_worksheet = write_workbook.worksheets()[0]
            col_format = write_workbook.add_format({'num_format': num_format})
            write_worksheet.set_column('B:B', None, col_format)
            writer.save()

            read_workbook = openpyxl.load_workbook(path)
            try:
                read_worksheet = read_workbook['Sheet1']
            except TypeError:
                # compat
                read_worksheet = read_workbook.get_sheet_by_name(name='Sheet1')

            # Get the number format from the cell.
            try:
                cell = read_worksheet['B2']
            except TypeError:
                # compat
                cell = read_worksheet.cell('B2')

            try:
                read_num_format = cell.number_format
            except Exception:
                read_num_format = cell.style.number_format._format_code

            assert read_num_format == num_format 

def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0,
                    freeze_panes=None):
        # Write the frame cells using xlwt.

        sheet_name = self._get_sheet_name(sheet_name)

        if sheet_name in self.sheets:
            wks = self.sheets[sheet_name]
        else:
            wks = self.book.add_sheet(sheet_name)
            self.sheets[sheet_name] = wks

        if _validate_freeze_panes(freeze_panes):
            wks.set_panes_frozen(True)
            wks.set_horz_split_pos(freeze_panes[0])
            wks.set_vert_split_pos(freeze_panes[1])

        style_dict = {}

        for cell in cells:
            val = _conv_value(cell.val)

            num_format_str = None
            if isinstance(cell.val, datetime):
                num_format_str = self.datetime_format
            elif isinstance(cell.val, date):
                num_format_str = self.date_format

            stylekey = json.dumps(cell.style)
            if num_format_str:
                stylekey += num_format_str

            if stylekey in style_dict:
                style = style_dict[stylekey]
            else:
                style = self._convert_to_style(cell.style, num_format_str)
                style_dict[stylekey] = style

            if cell.mergestart is not None and cell.mergeend is not None:
                wks.write_merge(startrow + cell.row,
                                startrow + cell.mergestart,
                                startcol + cell.col,
                                startcol + cell.mergeend,
                                val, style)
            else:
                wks.write(startrow + cell.row,
                          startcol + cell.col,
                          val, style) 

def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0,
                    freeze_panes=None):
        # Write the frame cells using xlsxwriter.
        sheet_name = self._get_sheet_name(sheet_name)

        if sheet_name in self.sheets:
            wks = self.sheets[sheet_name]
        else:
            wks = self.book.add_worksheet(sheet_name)
            self.sheets[sheet_name] = wks

        style_dict = {}

        if _validate_freeze_panes(freeze_panes):
            wks.freeze_panes(*(freeze_panes))

        for cell in cells:
            val = _conv_value(cell.val)

            num_format_str = None
            if isinstance(cell.val, datetime):
                num_format_str = self.datetime_format
            elif isinstance(cell.val, date):
                num_format_str = self.date_format

            stylekey = json.dumps(cell.style)
            if num_format_str:
                stylekey += num_format_str

            if stylekey in style_dict:
                style = style_dict[stylekey]
            else:
                style = self._convert_to_style(cell.style, num_format_str)
                style_dict[stylekey] = style

            if cell.mergestart is not None and cell.mergeend is not None:
                wks.merge_range(startrow + cell.row,
                                startcol + cell.col,
                                startrow + cell.mergestart,
                                startcol + cell.mergeend,
                                cell.val, style)
            else:
                wks.write(startrow + cell.row,
                          startcol + cell.col,
                          val, style) 

def _convert_to_style(self, style_dict, num_format_str=None):
        """
        converts a style_dict to an xlsxwriter format object
        Parameters
        ----------
        style_dict: style dictionary to convert
        num_format_str: optional number format string
        """

        # If there is no formatting we don't create a format object.
        if num_format_str is None and style_dict is None:
            return None

        # Create a XlsxWriter format object.
        xl_format = self.book.add_format()

        if num_format_str is not None:
            xl_format.set_num_format(num_format_str)

        if style_dict is None:
            return xl_format

        # Map the cell font to XlsxWriter font properties.
        if style_dict.get('font'):
            font = style_dict['font']
            if font.get('bold'):
                xl_format.set_bold()

        # Map the alignment to XlsxWriter alignment properties.
        alignment = style_dict.get('alignment')
        if alignment:
            if (alignment.get('horizontal') and
                    alignment['horizontal'] == 'center'):
                xl_format.set_align('center')
            if (alignment.get('vertical') and
                    alignment['vertical'] == 'top'):
                xl_format.set_align('top')

        # Map the cell borders to XlsxWriter border properties.
        if style_dict.get('borders'):
            xl_format.set_border()

        return xl_format 

def format_output(val, eachformat, eachstyle, xls_sheet):
    """Returns a excel cell with the data formated as specified in the template table"""
    new_cell = WriteOnlyCell(xls_sheet, value = "init")
    new_cell.style = eachstyle
    if val==None:
        val="None"
    elif eachformat in [None, "OLE"]:
        pass
    elif eachformat.startswith("OLE:"):
        val = val.strftime(eachformat[4:])
    elif eachformat=="FILE":
        val = file_timestamp(val)
        new_cell.number_format = 'YYYY MMM DD'
    elif eachformat.startswith("FILE:"):
        val = file_timestamp(val)
        val = val.strftime(eachformat[5:])
    elif eachformat.lower().startswith("lookup-"):
        lookup_name = eachformat.split("-")[1]
        if lookup_name in template_lookups:
            lookup_table = template_lookups.get(lookup_name,{})
            val = lookup_table.get(val,val)
    elif eachformat.lower() == "lookup_id":
        val = id_table.get(val, "No match in srum lookup table for %s" % (val))
    elif eachformat.lower() == "lookup_luid":
        inttype = struct.unpack(">H6B", codecs.decode(format(val,'016x'),'hex'))[0]
        val = template_lookups.get("LUID Interfaces",{}).get(inttype,"")
    elif eachformat.lower() == "seconds":
        val = val/86400.0
        new_cell.number_format = 'dd hh:mm:ss'
    elif eachformat.lower() == "md5":
        val = hashlib.md5(str(val)).hexdigest()
    elif eachformat.lower() == "sha1":
        val = hashlib.sha1(str(val)).hexdigest()
    elif eachformat.lower() == "sha256":
        val = hashlib.sha256(str(val)).hexdigest()
    elif eachformat.lower() == "base16":
        if type(val)==int:
            val = hex(val)
        else:
            val = format(val,"08x")
    elif eachformat.lower() == "base2":
        if type(val)==int:
            val = format(val,"032b")
        else:
            try:
                val = int(str(val),2)
            except :
                val = val
    elif eachformat.lower() == "interface_id" and options.reghive:
        val = interface_table.get(str(val),"")
    elif eachformat.lower() == "interface_id" and not options.reghive:
        val = val
    else:
        val = val
    try:
        new_cell.value = val
    except:
        new_cell.value = re.sub(r'[\000-\010]|[\013-\014]|[\016-\037]|[\x00-\x1f\x7f-\x9f]|[\uffff]',"",val)
    return new_cell 

def test_column_format(self):
        # Test that column formats are applied to cells. Test for issue #9167.
        # Applicable to xlsxwriter only.
        _skip_if_no_xlsxwriter()

        with warnings.catch_warnings():
            # Ignore the openpyxl lxml warning.
            warnings.simplefilter("ignore")
            _skip_if_no_openpyxl()
            import openpyxl

        with ensure_clean(self.ext) as path:
            frame = DataFrame({'A': [123456, 123456],
                               'B': [123456, 123456]})

            writer = ExcelWriter(path)
            frame.to_excel(writer)

            # Add a number format to col B and ensure it is applied to cells.
            num_format = '#,##0'
            write_workbook = writer.book
            write_worksheet = write_workbook.worksheets()[0]
            col_format = write_workbook.add_format({'num_format': num_format})
            write_worksheet.set_column('B:B', None, col_format)
            writer.save()

            read_workbook = openpyxl.load_workbook(path)
            try:
                read_worksheet = read_workbook['Sheet1']
            except TypeError:
                # compat
                read_worksheet = read_workbook.get_sheet_by_name(name='Sheet1')

            # Get the number format from the cell.
            try:
                cell = read_worksheet['B2']
            except TypeError:
                # compat
                cell = read_worksheet.cell('B2')

            try:
                read_num_format = cell.number_format
            except:
                read_num_format = cell.style.number_format._format_code

            assert read_num_format == num_format 

def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0,
                    freeze_panes=None):
        # Write the frame cells using xlwt.

        sheet_name = self._get_sheet_name(sheet_name)

        if sheet_name in self.sheets:
            wks = self.sheets[sheet_name]
        else:
            wks = self.book.add_sheet(sheet_name)
            self.sheets[sheet_name] = wks

        if _validate_freeze_panes(freeze_panes):
            wks.set_panes_frozen(True)
            wks.set_horz_split_pos(freeze_panes[0])
            wks.set_vert_split_pos(freeze_panes[1])

        style_dict = {}

        for cell in cells:
            val = _conv_value(cell.val)

            num_format_str = None
            if isinstance(cell.val, datetime):
                num_format_str = self.datetime_format
            elif isinstance(cell.val, date):
                num_format_str = self.date_format

            stylekey = json.dumps(cell.style)
            if num_format_str:
                stylekey += num_format_str

            if stylekey in style_dict:
                style = style_dict[stylekey]
            else:
                style = self._convert_to_style(cell.style, num_format_str)
                style_dict[stylekey] = style

            if cell.mergestart is not None and cell.mergeend is not None:
                wks.write_merge(startrow + cell.row,
                                startrow + cell.mergestart,
                                startcol + cell.col,
                                startcol + cell.mergeend,
                                val, style)
            else:
                wks.write(startrow + cell.row,
                          startcol + cell.col,
                          val, style) 

def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0,
                    freeze_panes=None):
        # Write the frame cells using xlsxwriter.
        sheet_name = self._get_sheet_name(sheet_name)

        if sheet_name in self.sheets:
            wks = self.sheets[sheet_name]
        else:
            wks = self.book.add_worksheet(sheet_name)
            self.sheets[sheet_name] = wks

        style_dict = {}

        if _validate_freeze_panes(freeze_panes):
            wks.freeze_panes(*(freeze_panes))

        for cell in cells:
            val = _conv_value(cell.val)

            num_format_str = None
            if isinstance(cell.val, datetime):
                num_format_str = self.datetime_format
            elif isinstance(cell.val, date):
                num_format_str = self.date_format

            stylekey = json.dumps(cell.style)
            if num_format_str:
                stylekey += num_format_str

            if stylekey in style_dict:
                style = style_dict[stylekey]
            else:
                style = self._convert_to_style(cell.style, num_format_str)
                style_dict[stylekey] = style

            if cell.mergestart is not None and cell.mergeend is not None:
                wks.merge_range(startrow + cell.row,
                                startcol + cell.col,
                                startrow + cell.mergestart,
                                startcol + cell.mergeend,
                                cell.val, style)
            else:
                wks.write(startrow + cell.row,
                          startcol + cell.col,
                          val, style) 

def _convert_to_style(self, style_dict, num_format_str=None):
        """
        converts a style_dict to an xlsxwriter format object
        Parameters
        ----------
        style_dict: style dictionary to convert
        num_format_str: optional number format string
        """

        # If there is no formatting we don't create a format object.
        if num_format_str is None and style_dict is None:
            return None

        # Create a XlsxWriter format object.
        xl_format = self.book.add_format()

        if num_format_str is not None:
            xl_format.set_num_format(num_format_str)

        if style_dict is None:
            return xl_format

        # Map the cell font to XlsxWriter font properties.
        if style_dict.get('font'):
            font = style_dict['font']
            if font.get('bold'):
                xl_format.set_bold()

        # Map the alignment to XlsxWriter alignment properties.
        alignment = style_dict.get('alignment')
        if alignment:
            if (alignment.get('horizontal') and
                    alignment['horizontal'] == 'center'):
                xl_format.set_align('center')
            if (alignment.get('vertical') and
                    alignment['vertical'] == 'top'):
                xl_format.set_align('top')

        # Map the cell borders to XlsxWriter border properties.
        if style_dict.get('borders'):
            xl_format.set_border()

        return xl_format