def initialWorkBook(self): '''initial the workbook''' if self.mergeFlag: if len(self.DictMerge.keys()) == 0: content = "\nWarning:The merged data is duplicated, no need to update" self.logSteps(content) sys.exit(1) else: try: wb = load_workbook( os.path.join(self.CurrentLocation, self.ReportFileName)) content = "start to merge the data" self.logSteps(content) except: content = "\nWarning:Sorry,the report excel is missing, you couldn't merge the data,\nplease clear the merge folder at first to initial new report, then you could merge" self.logSteps(content) sys.exit(1) else: if len(self.Dict.keys()) == 0: content = "\nWarning:There is no report to initial" self.logSteps(content) sys.exit(1) wb = Workbook() sheet = wb.create_sheet("Summary") sheet = wb["Sheet"] wb.remove(sheet) content = "start to initial new report" self.logSteps(content) return wb
def create_input_data(): dttime=dt.datetime.now().strftime("%H:%M:%S") readfile="C:\\NSE\\inputs\\NSEOptions.xlsx" writefile="C:\\NSE\\inputs\\NSEOptions1.xlsx" workbook_name = writefile wb = Workbook() wb.create_sheet('NSE') print(wb.sheetnames) std=wb['Sheet'] wb.remove(std) wb.save(filename = writefile) print(wb.sheetnames) workbook_name = readfile wb1 = openpyxl.load_workbook(readfile, data_only=True) wb2 = openpyxl.load_workbook(writefile) sheet1 = wb1['Sheet2'] sheet2 = wb2['NSE'] max_rows=sheet1.max_row for row in sheet1['A1' :'U'+str(max_rows)]: for cell in row: sheet2[cell.coordinate].value = cell.value wb1.save(writefile)
def dump_to_template(fname, n): """ Dumps the transposed intermediate to a template for further personal analyses. All three timepoints of each condition are on the same sheet now. Further update: make the column headers the different fluorophore names, and include the date and time. """ #Read Transposed Intermediate excel transposed_data = load_workbook(fname) #Create new workbook to reformat data into wb = Workbook() #Delete auto-generated Sheet 1 wb.remove(wb['Sheet']) #For every n sheets: for sheet in transposed_data.worksheets: #Get sheet index: index starts at 0 i = transposed_data.index(sheet) #print("Sheet index: ", i) #If you are on the first condition, if (i % n == 0): #Create sheetname for new workbook, remove last 3 characters to get rid of "D1" etc new_sheetname = f'{sheet}'[:-8] new_sheetname = new_sheetname[12:] #Create new sheet in new workbook wb.create_sheet(title=new_sheetname) #Go from sheet i, to sheet i + (n-1) copyList = transposed_data.sheetnames[i:(i + (n))] #Paste Start Column pasteCol = 1 #For each sheet in transposed data while i % n == 1: for sheets in copyList: curSheet = transposed_data[sheets] #Get max column with data col = curSheet.max_column #Get max row with data row = curSheet.max_row #Copy the data from [B1 to max col, max row]. Copy it from the appropriate sheet on transposed data copyData = copyRange(2, 1, col, row, curSheet) #Paste the copied data starting at column n + #print("paste start col = ", pasteCol) #print("Sheet receiving: ", wb[new_sheetname]) pasteRange(pasteCol, 1, n + pasteCol - 1, row, wb[new_sheetname], copyData) #Update paste col pasteCol += n #TODO: Remove this fname change. Just save as transposed when I have final version ready. fname = fname[:-5] fname = fname + "_pasted.xlsx" wb.save(fname) return wb
def newWorkbook(filename): files = [ f for f in listdir(flightslistpath) if isfile(join(flightslistpath, f)) ] wb = Workbook() wb.remove(wb.active) for f in files: wb = addSheet(wb, f) wb.save(sheets_directory + filename + extension)
def export_book(cls, databook, freeze_panes=True): """Returns XLSX representation of DataBook.""" wb = Workbook() for sheet in wb.worksheets: wb.remove(sheet) for i, dset in enumerate(databook._datasets): ws = wb.create_sheet() ws.title = dset.title if dset.title else 'Sheet%s' % (i) cls.dset_sheet(dset, ws, freeze_panes=freeze_panes) stream = BytesIO() wb.save(stream) return stream.getvalue()
def export_book(cls, databook, freeze_panes=True, invalid_char_subst="-"): """Returns XLSX representation of DataBook. If dataset.title contains characters which are considered invalid for an XLSX file sheet name (http://www.excelcodex.com/2012/06/worksheets-naming-conventions/), they will be replaced with `invalid_char_subst`. """ wb = Workbook() for sheet in wb.worksheets: wb.remove(sheet) for i, dset in enumerate(databook._datasets): ws = wb.create_sheet() ws.title = (safe_xlsx_sheet_title(dset.title, invalid_char_subst) if dset.title else 'Sheet%s' % (i)) cls.dset_sheet(dset, ws, freeze_panes=freeze_panes) stream = BytesIO() wb.save(stream) return stream.getvalue()
class OpenpyxlWriter(ExcelWriter): engine = "openpyxl" supported_extensions = (".xlsx", ".xlsm") def __init__( self, path, engine=None, mode: str = "w", storage_options: StorageOptions = None, **engine_kwargs, ): # Use the openpyxl module as the Excel writer. from openpyxl.workbook import Workbook super().__init__(path, mode=mode, storage_options=storage_options, **engine_kwargs) # ExcelWriter replaced "a" by "r+" to allow us to first read the excel file from # the file and later write to it if "r+" in self.mode: # Load from existing workbook from openpyxl import load_workbook self.book = load_workbook(self.handles.handle) else: # Create workbook object with default optimized_write=True. self.book = Workbook() if self.book.worksheets: self.book.remove(self.book.worksheets[0]) def save(self): """ Save workbook to disk. """ self.book.save(self.handles.handle) @classmethod def _convert_to_style_kwargs( cls, style_dict: dict) -> Dict[str, "Serialisable"]: """ Convert a style_dict to a set of kwargs suitable for initializing or updating-on-copy an openpyxl v2 style object. Parameters ---------- style_dict : dict A dict with zero or more of the following keys (or their synonyms). 'font' 'fill' 'border' ('borders') 'alignment' 'number_format' 'protection' Returns ------- style_kwargs : dict A dict with the same, normalized keys as ``style_dict`` but each value has been replaced with a native openpyxl style object of the appropriate class. """ _style_key_map = {"borders": "border"} style_kwargs: Dict[str, Serialisable] = {} for k, v in style_dict.items(): if k in _style_key_map: k = _style_key_map[k] _conv_to_x = getattr(cls, f"_convert_to_{k}", lambda x: None) new_v = _conv_to_x(v) if new_v: style_kwargs[k] = new_v return style_kwargs @classmethod def _convert_to_color(cls, color_spec): """ Convert ``color_spec`` to an openpyxl v2 Color object. Parameters ---------- color_spec : str, dict A 32-bit ARGB hex string, or a dict with zero or more of the following keys. 'rgb' 'indexed' 'auto' 'theme' 'tint' 'index' 'type' Returns ------- color : openpyxl.styles.Color """ from openpyxl.styles import Color if isinstance(color_spec, str): return Color(color_spec) else: return Color(**color_spec) @classmethod def _convert_to_font(cls, font_dict): """ Convert ``font_dict`` to an openpyxl v2 Font object. Parameters ---------- font_dict : dict A dict with zero or more of the following keys (or their synonyms). 'name' 'size' ('sz') 'bold' ('b') 'italic' ('i') 'underline' ('u') 'strikethrough' ('strike') 'color' 'vertAlign' ('vertalign') 'charset' 'scheme' 'family' 'outline' 'shadow' 'condense' Returns ------- font : openpyxl.styles.Font """ from openpyxl.styles import Font _font_key_map = { "sz": "size", "b": "bold", "i": "italic", "u": "underline", "strike": "strikethrough", "vertalign": "vertAlign", } font_kwargs = {} for k, v in font_dict.items(): if k in _font_key_map: k = _font_key_map[k] if k == "color": v = cls._convert_to_color(v) font_kwargs[k] = v return Font(**font_kwargs) @classmethod def _convert_to_stop(cls, stop_seq): """ Convert ``stop_seq`` to a list of openpyxl v2 Color objects, suitable for initializing the ``GradientFill`` ``stop`` parameter. Parameters ---------- stop_seq : iterable An iterable that yields objects suitable for consumption by ``_convert_to_color``. Returns ------- stop : list of openpyxl.styles.Color """ return map(cls._convert_to_color, stop_seq) @classmethod def _convert_to_fill(cls, fill_dict): """ Convert ``fill_dict`` to an openpyxl v2 Fill object. Parameters ---------- fill_dict : dict A dict with one or more of the following keys (or their synonyms), 'fill_type' ('patternType', 'patterntype') 'start_color' ('fgColor', 'fgcolor') 'end_color' ('bgColor', 'bgcolor') or one or more of the following keys (or their synonyms). 'type' ('fill_type') 'degree' 'left' 'right' 'top' 'bottom' 'stop' Returns ------- fill : openpyxl.styles.Fill """ from openpyxl.styles import GradientFill, PatternFill _pattern_fill_key_map = { "patternType": "fill_type", "patterntype": "fill_type", "fgColor": "start_color", "fgcolor": "start_color", "bgColor": "end_color", "bgcolor": "end_color", } _gradient_fill_key_map = {"fill_type": "type"} pfill_kwargs = {} gfill_kwargs = {} for k, v in fill_dict.items(): pk = gk = None if k in _pattern_fill_key_map: pk = _pattern_fill_key_map[k] if k in _gradient_fill_key_map: gk = _gradient_fill_key_map[k] if pk in ["start_color", "end_color"]: v = cls._convert_to_color(v) if gk == "stop": v = cls._convert_to_stop(v) if pk: pfill_kwargs[pk] = v elif gk: gfill_kwargs[gk] = v else: pfill_kwargs[k] = v gfill_kwargs[k] = v try: return PatternFill(**pfill_kwargs) except TypeError: return GradientFill(**gfill_kwargs) @classmethod def _convert_to_side(cls, side_spec): """ Convert ``side_spec`` to an openpyxl v2 Side object. Parameters ---------- side_spec : str, dict A string specifying the border style, or a dict with zero or more of the following keys (or their synonyms). 'style' ('border_style') 'color' Returns ------- side : openpyxl.styles.Side """ from openpyxl.styles import Side _side_key_map = {"border_style": "style"} if isinstance(side_spec, str): return Side(style=side_spec) side_kwargs = {} for k, v in side_spec.items(): if k in _side_key_map: k = _side_key_map[k] if k == "color": v = cls._convert_to_color(v) side_kwargs[k] = v return Side(**side_kwargs) @classmethod def _convert_to_border(cls, border_dict): """ Convert ``border_dict`` to an openpyxl v2 Border object. Parameters ---------- border_dict : dict A dict with zero or more of the following keys (or their synonyms). 'left' 'right' 'top' 'bottom' 'diagonal' 'diagonal_direction' 'vertical' 'horizontal' 'diagonalUp' ('diagonalup') 'diagonalDown' ('diagonaldown') 'outline' Returns ------- border : openpyxl.styles.Border """ from openpyxl.styles import Border _border_key_map = { "diagonalup": "diagonalUp", "diagonaldown": "diagonalDown" } border_kwargs = {} for k, v in border_dict.items(): if k in _border_key_map: k = _border_key_map[k] if k == "color": v = cls._convert_to_color(v) if k in ["left", "right", "top", "bottom", "diagonal"]: v = cls._convert_to_side(v) border_kwargs[k] = v return Border(**border_kwargs) @classmethod def _convert_to_alignment(cls, alignment_dict): """ Convert ``alignment_dict`` to an openpyxl v2 Alignment object. Parameters ---------- alignment_dict : dict A dict with zero or more of the following keys (or their synonyms). 'horizontal' 'vertical' 'text_rotation' 'wrap_text' 'shrink_to_fit' 'indent' Returns ------- alignment : openpyxl.styles.Alignment """ from openpyxl.styles import Alignment return Alignment(**alignment_dict) @classmethod def _convert_to_number_format(cls, number_format_dict): """ Convert ``number_format_dict`` to an openpyxl v2.1.0 number format initializer. Parameters ---------- number_format_dict : dict A dict with zero or more of the following keys. 'format_code' : str Returns ------- number_format : str """ return number_format_dict["format_code"] @classmethod def _convert_to_protection(cls, protection_dict): """ Convert ``protection_dict`` to an openpyxl v2 Protection object. Parameters ---------- protection_dict : dict A dict with zero or more of the following keys. 'locked' 'hidden' Returns ------- """ from openpyxl.styles import Protection return Protection(**protection_dict) def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0, freeze_panes=None): # Write the frame cells using openpyxl. sheet_name = self._get_sheet_name(sheet_name) _style_cache: Dict[str, Dict[str, Serialisable]] = {} if sheet_name in self.sheets: wks = self.sheets[sheet_name] else: wks = self.book.create_sheet() wks.title = sheet_name self.sheets[sheet_name] = wks if validate_freeze_panes(freeze_panes): wks.freeze_panes = wks.cell(row=freeze_panes[0] + 1, column=freeze_panes[1] + 1) for cell in cells: xcell = wks.cell(row=startrow + cell.row + 1, column=startcol + cell.col + 1) xcell.value, fmt = self._value_with_fmt(cell.val) if fmt: xcell.number_format = fmt style_kwargs: Optional[Dict[str, Serialisable]] = {} if cell.style: key = str(cell.style) style_kwargs = _style_cache.get(key) if style_kwargs is None: style_kwargs = self._convert_to_style_kwargs(cell.style) _style_cache[key] = style_kwargs if style_kwargs: for k, v in style_kwargs.items(): setattr(xcell, k, v) if cell.mergestart is not None and cell.mergeend is not None: wks.merge_cells( start_row=startrow + cell.row + 1, start_column=startcol + cell.col + 1, end_column=startcol + cell.mergeend + 1, end_row=startrow + cell.mergestart + 1, ) # When cells are merged only the top-left cell is preserved # The behaviour of the other cells in a merged range is # undefined if style_kwargs: first_row = startrow + cell.row + 1 last_row = startrow + cell.mergestart + 1 first_col = startcol + cell.col + 1 last_col = startcol + cell.mergeend + 1 for row in range(first_row, last_row + 1): for col in range(first_col, last_col + 1): if row == first_row and col == first_col: # Ignore first cell. It is already handled. continue xcell = wks.cell(column=col, row=row) for k, v in style_kwargs.items(): setattr(xcell, k, v)
from write_put_oi import write_put_oi from write_strike_price import write_strike_price dttime=dt.datetime.now().strftime("%d-%b-%Y %H:%M:%S") heading=[" Changes in OI for Options as on " + dttime[:11]] filename='C:\\NSE\\outputs\\NSEOI-LOG-' + dt.datetime.now().strftime('%Y-%B-%d')+".xlsx" print(os.path.isfile(filename)) if os.path.isfile(filename): os.remove(filename) print("File Removed") data=dttime workbook_name = filename wb = Workbook() wb.create_sheet('CHGOI') wb.create_sheet('OI') print(wb.sheetnames) std=wb['Sheet'] wb.remove(std) wb.save(filename = workbook_name) print(wb.sheetnames) print("File Created") write_strike_price() write_calls_oi() write_put_oi()
def write_to_workbook(*args): print("Writing results to the Excel file") # do not take into account the headers, so start in row 2 row_cursor = 2 # Workbook headers workbook = Workbook() worksheet = workbook.create_sheet() worksheet.title = "Groups" # this line affects the first row worksheet.row_dimensions[1].height = 20 # Titles for each column worksheet['A1'] = "GroupName" worksheet.column_dimensions['A'].width = 40 worksheet.cell(row=1, column=1).font = openpyxl_styles.openpyxl_styles() worksheet['B1'] = "Group Desc" worksheet.column_dimensions['B'].width = 40 worksheet.cell(row=1, column=2).font = openpyxl_styles.openpyxl_styles() worksheet['C1'] = "Form Name" worksheet.column_dimensions['C'].width = 40 worksheet.cell(row=1, column=3).font = openpyxl_styles.openpyxl_styles() worksheet['D1'] = "Delete Privilege" worksheet.column_dimensions['D'].width = 40 worksheet.cell(row=1, column=4).font = openpyxl_styles.openpyxl_styles() worksheet['E1'] = "Edit Privilege" worksheet.column_dimensions['E'].width = 40 worksheet.cell(row=1, column=5).font = openpyxl_styles.openpyxl_styles() worksheet['F1'] = "Execute Privilege" worksheet.column_dimensions['F'].width = 40 worksheet.cell(row=1, column=6).font = openpyxl_styles.openpyxl_styles() worksheet['G1'] = "Insert Privilege" worksheet.column_dimensions['G'].width = 40 worksheet.cell(row=1, column=7).font = openpyxl_styles.openpyxl_styles() worksheet['H1'] = "Read Privilege" worksheet.column_dimensions['H'].width = 40 worksheet.cell(row=1, column=8).font = openpyxl_styles.openpyxl_styles() worksheet['I1'] = "Update Privilege" worksheet.column_dimensions['I'].width = 40 worksheet.cell(row=1, column=9).font = openpyxl_styles.openpyxl_styles() worksheet['J1'] = "Bulk Update Privilege" worksheet.column_dimensions['J'].width = 40 worksheet.cell(row=1, column=10).font = openpyxl_styles.openpyxl_styles() # Insert values in each column # for index, value in enumerate(args[0]): try: worksheet.cell(row=row_cursor, column=1).value = value[0] worksheet.cell(row=row_cursor, column=2).value = value[1] worksheet.cell(row=row_cursor, column=3).value = value[2] worksheet.cell(row=row_cursor, column=4).value = value[3] worksheet.cell(row=row_cursor, column=5).value = value[4] worksheet.cell(row=row_cursor, column=6).value = value[5] worksheet.cell(row=row_cursor, column=7).value = value[6] worksheet.cell(row=row_cursor, column=8).value = value[7] worksheet.cell(row=row_cursor, column=9).value = value[8] worksheet.cell(row=row_cursor, column=10).value = value[9] except: print("Error in line %s\n data=%s" % (index, value)) # Counter for going to the next row row_cursor = row_cursor + 1 # delete the sheet one in Openpyxl because you do not want to display this empty sheet sheet_one = workbook['Sheet'] workbook.remove(sheet_one) # save the workbook workbook.save("C:\\Temp\\PythonOutputFiles\\Dump_" + str(datetime.now()).split(' ')[0] + ".xlsx") print("The results in the workbook have been saved.")
def test_remove_sheet(): wb = Workbook() new_sheet = wb.create_sheet(0) wb.remove(new_sheet) assert new_sheet not in wb.worksheets
class _OpenpyxlWriter(ExcelWriter): engine = 'openpyxl' supported_extensions = ('.xlsx', '.xlsm') def __init__(self, path, engine=None, mode='w', **engine_kwargs): # Use the openpyxl module as the Excel writer. from openpyxl.workbook import Workbook super().__init__(path, mode=mode, **engine_kwargs) if self.mode == 'a': # Load from existing workbook from openpyxl import load_workbook book = load_workbook(self.path) self.book = book else: # Create workbook object with default optimized_write=True. self.book = Workbook() if self.book.worksheets: try: self.book.remove(self.book.worksheets[0]) except AttributeError: # compat - for openpyxl <= 2.4 self.book.remove_sheet(self.book.worksheets[0]) def save(self): """ Save workbook to disk. """ return self.book.save(self.path) @classmethod def _convert_to_style(cls, style_dict): """ converts a style_dict to an openpyxl style object Parameters ---------- style_dict : style dictionary to convert """ from openpyxl.style import Style xls_style = Style() for key, value in style_dict.items(): for nk, nv in value.items(): if key == "borders": (xls_style.borders.__getattribute__(nk) .__setattr__('border_style', nv)) else: xls_style.__getattribute__(key).__setattr__(nk, nv) return xls_style @classmethod def _convert_to_style_kwargs(cls, style_dict): """ Convert a style_dict to a set of kwargs suitable for initializing or updating-on-copy an openpyxl v2 style object Parameters ---------- style_dict : dict A dict with zero or more of the following keys (or their synonyms). 'font' 'fill' 'border' ('borders') 'alignment' 'number_format' 'protection' Returns ------- style_kwargs : dict A dict with the same, normalized keys as ``style_dict`` but each value has been replaced with a native openpyxl style object of the appropriate class. """ _style_key_map = { 'borders': 'border', } style_kwargs = {} for k, v in style_dict.items(): if k in _style_key_map: k = _style_key_map[k] _conv_to_x = getattr(cls, '_convert_to_{k}'.format(k=k), lambda x: None) new_v = _conv_to_x(v) if new_v: style_kwargs[k] = new_v return style_kwargs @classmethod def _convert_to_color(cls, color_spec): """ Convert ``color_spec`` to an openpyxl v2 Color object Parameters ---------- color_spec : str, dict A 32-bit ARGB hex string, or a dict with zero or more of the following keys. 'rgb' 'indexed' 'auto' 'theme' 'tint' 'index' 'type' Returns ------- color : openpyxl.styles.Color """ from openpyxl.styles import Color if isinstance(color_spec, str): return Color(color_spec) else: return Color(**color_spec) @classmethod def _convert_to_font(cls, font_dict): """ Convert ``font_dict`` to an openpyxl v2 Font object Parameters ---------- font_dict : dict A dict with zero or more of the following keys (or their synonyms). 'name' 'size' ('sz') 'bold' ('b') 'italic' ('i') 'underline' ('u') 'strikethrough' ('strike') 'color' 'vertAlign' ('vertalign') 'charset' 'scheme' 'family' 'outline' 'shadow' 'condense' Returns ------- font : openpyxl.styles.Font """ from openpyxl.styles import Font _font_key_map = { 'sz': 'size', 'b': 'bold', 'i': 'italic', 'u': 'underline', 'strike': 'strikethrough', 'vertalign': 'vertAlign', } font_kwargs = {} for k, v in font_dict.items(): if k in _font_key_map: k = _font_key_map[k] if k == 'color': v = cls._convert_to_color(v) font_kwargs[k] = v return Font(**font_kwargs) @classmethod def _convert_to_stop(cls, stop_seq): """ Convert ``stop_seq`` to a list of openpyxl v2 Color objects, suitable for initializing the ``GradientFill`` ``stop`` parameter. Parameters ---------- stop_seq : iterable An iterable that yields objects suitable for consumption by ``_convert_to_color``. Returns ------- stop : list of openpyxl.styles.Color """ return map(cls._convert_to_color, stop_seq) @classmethod def _convert_to_fill(cls, fill_dict): """ Convert ``fill_dict`` to an openpyxl v2 Fill object Parameters ---------- fill_dict : dict A dict with one or more of the following keys (or their synonyms), 'fill_type' ('patternType', 'patterntype') 'start_color' ('fgColor', 'fgcolor') 'end_color' ('bgColor', 'bgcolor') or one or more of the following keys (or their synonyms). 'type' ('fill_type') 'degree' 'left' 'right' 'top' 'bottom' 'stop' Returns ------- fill : openpyxl.styles.Fill """ from openpyxl.styles import PatternFill, GradientFill _pattern_fill_key_map = { 'patternType': 'fill_type', 'patterntype': 'fill_type', 'fgColor': 'start_color', 'fgcolor': 'start_color', 'bgColor': 'end_color', 'bgcolor': 'end_color', } _gradient_fill_key_map = { 'fill_type': 'type', } pfill_kwargs = {} gfill_kwargs = {} for k, v in fill_dict.items(): pk = gk = None if k in _pattern_fill_key_map: pk = _pattern_fill_key_map[k] if k in _gradient_fill_key_map: gk = _gradient_fill_key_map[k] if pk in ['start_color', 'end_color']: v = cls._convert_to_color(v) if gk == 'stop': v = cls._convert_to_stop(v) if pk: pfill_kwargs[pk] = v elif gk: gfill_kwargs[gk] = v else: pfill_kwargs[k] = v gfill_kwargs[k] = v try: return PatternFill(**pfill_kwargs) except TypeError: return GradientFill(**gfill_kwargs) @classmethod def _convert_to_side(cls, side_spec): """ Convert ``side_spec`` to an openpyxl v2 Side object Parameters ---------- side_spec : str, dict A string specifying the border style, or a dict with zero or more of the following keys (or their synonyms). 'style' ('border_style') 'color' Returns ------- side : openpyxl.styles.Side """ from openpyxl.styles import Side _side_key_map = { 'border_style': 'style', } if isinstance(side_spec, str): return Side(style=side_spec) side_kwargs = {} for k, v in side_spec.items(): if k in _side_key_map: k = _side_key_map[k] if k == 'color': v = cls._convert_to_color(v) side_kwargs[k] = v return Side(**side_kwargs) @classmethod def _convert_to_border(cls, border_dict): """ Convert ``border_dict`` to an openpyxl v2 Border object Parameters ---------- border_dict : dict A dict with zero or more of the following keys (or their synonyms). 'left' 'right' 'top' 'bottom' 'diagonal' 'diagonal_direction' 'vertical' 'horizontal' 'diagonalUp' ('diagonalup') 'diagonalDown' ('diagonaldown') 'outline' Returns ------- border : openpyxl.styles.Border """ from openpyxl.styles import Border _border_key_map = { 'diagonalup': 'diagonalUp', 'diagonaldown': 'diagonalDown', } border_kwargs = {} for k, v in border_dict.items(): if k in _border_key_map: k = _border_key_map[k] if k == 'color': v = cls._convert_to_color(v) if k in ['left', 'right', 'top', 'bottom', 'diagonal']: v = cls._convert_to_side(v) border_kwargs[k] = v return Border(**border_kwargs) @classmethod def _convert_to_alignment(cls, alignment_dict): """ Convert ``alignment_dict`` to an openpyxl v2 Alignment object Parameters ---------- alignment_dict : dict A dict with zero or more of the following keys (or their synonyms). 'horizontal' 'vertical' 'text_rotation' 'wrap_text' 'shrink_to_fit' 'indent' Returns ------- alignment : openpyxl.styles.Alignment """ from openpyxl.styles import Alignment return Alignment(**alignment_dict) @classmethod def _convert_to_number_format(cls, number_format_dict): """ Convert ``number_format_dict`` to an openpyxl v2.1.0 number format initializer. Parameters ---------- number_format_dict : dict A dict with zero or more of the following keys. 'format_code' : str Returns ------- number_format : str """ return number_format_dict['format_code'] @classmethod def _convert_to_protection(cls, protection_dict): """ Convert ``protection_dict`` to an openpyxl v2 Protection object. Parameters ---------- protection_dict : dict A dict with zero or more of the following keys. 'locked' 'hidden' Returns ------- """ from openpyxl.styles import Protection return Protection(**protection_dict) def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0, freeze_panes=None): # Write the frame cells using openpyxl. sheet_name = self._get_sheet_name(sheet_name) _style_cache = {} if sheet_name in self.sheets: wks = self.sheets[sheet_name] else: wks = self.book.create_sheet() wks.title = sheet_name self.sheets[sheet_name] = wks if _validate_freeze_panes(freeze_panes): wks.freeze_panes = wks.cell(row=freeze_panes[0] + 1, column=freeze_panes[1] + 1) for cell in cells: xcell = wks.cell( row=startrow + cell.row + 1, column=startcol + cell.col + 1 ) xcell.value, fmt = self._value_with_fmt(cell.val) if fmt: xcell.number_format = fmt style_kwargs = {} if cell.style: key = str(cell.style) style_kwargs = _style_cache.get(key) if style_kwargs is None: style_kwargs = self._convert_to_style_kwargs(cell.style) _style_cache[key] = style_kwargs if style_kwargs: for k, v in style_kwargs.items(): setattr(xcell, k, v) if cell.mergestart is not None and cell.mergeend is not None: wks.merge_cells( start_row=startrow + cell.row + 1, start_column=startcol + cell.col + 1, end_column=startcol + cell.mergeend + 1, end_row=startrow + cell.mergestart + 1 ) # When cells are merged only the top-left cell is preserved # The behaviour of the other cells in a merged range is # undefined if style_kwargs: first_row = startrow + cell.row + 1 last_row = startrow + cell.mergestart + 1 first_col = startcol + cell.col + 1 last_col = startcol + cell.mergeend + 1 for row in range(first_row, last_row + 1): for col in range(first_col, last_col + 1): if row == first_row and col == first_col: # Ignore first cell. It is already handled. continue xcell = wks.cell(column=col, row=row) for k, v in style_kwargs.items(): setattr(xcell, k, v)
#encoding=utf-8 import os import json from openpyxl.workbook import Workbook List = os.listdir(os.getcwd()) NewList = filter(lambda x: "." not in x, List) wb = Workbook() sheet = wb.create_sheet("Summary") sheet = wb["Sheet"] wb.remove(sheet) SumTitleLine = ["No", "Name", "Status", "Owner"] sheet = wb["Summary"] for index, rowValue in enumerate(["SumTitleLine"] + NewList): if index == 0: sheet.append(SumTitleLine) else: rowContent = [str(index), rowValue] sheet.append(rowContent) sheet.column_dimensions['B'].width = 40 wb.save("OWASP_Scan_Staus.xlsx")
#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Created on Fri Feb 12 08:30:26 2021 @author: oguzhanozdmr """ import argparse import os.path from openpyxl.workbook import Workbook from openpyxl.styles import PatternFill from openpyxl import load_workbook new_workbook = Workbook() new_workbook.remove(new_workbook['Sheet']) case_sensitive = False just_first_color = '5CFF5C' just_second_color = 'FFC55C' diff_color = 'FFFF00' def is_file(paths: str): isFile = os.path.isfile(paths[0]) and os.path.isfile(paths[1]) return isFile def read_excel(path: str): wb = load_workbook(filename=path, read_only=True) return wb
def main(): input_file_dir = '../output/output.txt' if os.path.exists(input_file_dir)==False: print("Warning: File {} not exists, please check!".format(input_file_dir)) sys.exit() output_file_name = 'Agent_Status.xlsx' # Title Style title_style = NamedStyle(name="title_style") bd = Side(style='thin', color='000000') title_style.border = Border(left=bd, right=bd, top=bd, bottom=bd) title_style.fill = PatternFill("solid", fgColor=Color.TITLE.value) title_style.font = Font(bold=True) title_style.alignment = Alignment(horizontal="center", vertical="center") # Running Style running_style = NamedStyle(name="running_style") bd = Side(style='thin', color='000000') running_style.border = Border(left=bd, right=bd, top=bd, bottom=bd) running_style.fill = PatternFill("solid", fgColor=Color.GREEN.value) running_style.font = Font(bold=True) running_style.alignment = Alignment(horizontal="center", vertical="center") # Not Running Style not_running_style = NamedStyle(name="not_running_style") bd = Side(style='thin', color='000000') not_running_style.border = Border(left=bd, right=bd, top=bd, bottom=bd) not_running_style.fill = PatternFill("solid", fgColor=Color.YELLOW.value) not_running_style.font = Font(bold=True) not_running_style.alignment = Alignment(horizontal="center", vertical="center") # Not Installed Style not_installed_style = NamedStyle(name="not_installed_style") bd = Side(style='thin', color='000000') not_installed_style.border = Border(left=bd, right=bd, top=bd, bottom=bd) not_installed_style.fill = PatternFill("solid", fgColor=Color.RED.value) not_installed_style.font = Font(bold=True) not_installed_style.alignment = Alignment(horizontal="center", vertical="center") # Create new excel sheet wb_output = Workbook() wb_output.remove(wb_output["Sheet"]) # Add all style into workbook wb_output.add_named_style(title_style) wb_output.add_named_style(running_style) wb_output.add_named_style(not_running_style) wb_output.add_named_style(not_installed_style) # Create Cover sheet ws_output_cover = wb_output.create_sheet('Qualys Agent') img = Image('../resources/logo.jpg') img.width = 1800 img.height = 920 ws_output_cover.add_image(img, 'D2') # Create multi sheet: Intalled - Running, Installed - NotRunning , NotInstall ws_output_running = wb_output.create_sheet('Running') ws_output_running.append(['Hostname', 'Version']) ws_output_not_running = wb_output.create_sheet('Not Running') ws_output_not_running.append(['Hostname', 'Version']) ws_output_not_installed = wb_output.create_sheet('Not Installed') ws_output_not_installed.append(['Hostname', 'Version']) # Read input_file render into excel sheet with open(input_file_dir) as input_file: for cnt, line in enumerate(input_file): line_array = line.split(',') if line_array[1]=='running': ws_output_running.append([line_array[0], line_array[2]]) elif line_array[1]=='not_running' and 'NA' in line_array[2]: ws_output_not_installed.append([line_array[0], line_array[2]]) else: ws_output_not_running.append([line_array[0], line_array[2]]) # adding style into Title for i in range(ws_output_running.max_column): ws_output_running[get_column_letter(i + 1) + str(1)].style = 'title_style' ws_output_running.column_dimensions[get_column_letter(i + 1)].width = 40 for i in range(ws_output_not_running.max_column): ws_output_not_running[get_column_letter(i + 1) + str(1)].style = 'title_style' ws_output_not_running.column_dimensions[get_column_letter(i + 1)].width = 40 for i in range(ws_output_not_installed.max_column): ws_output_not_installed[get_column_letter(i + 1) + str(1)].style = 'title_style' ws_output_not_installed.column_dimensions[get_column_letter(i + 1)].width = 40 # adding style into Body for i in range(ws_output_running.max_column): for j in range(1, ws_output_running.max_row): ws_output_running['%s%d' % (get_column_letter(i + 1), j+1)].style = 'running_style' for i in range(ws_output_not_running.max_column): for j in range(1, ws_output_not_running.max_row): ws_output_not_running['%s%d' % (get_column_letter(i + 1), j+1)].style = 'not_running_style' for i in range(ws_output_not_installed.max_column): for j in range(1, ws_output_not_installed.max_row): ws_output_not_installed['%s%d' % (get_column_letter(i + 1), j+1)].style = 'not_installed_style' wb_output.save('../excel/'+ output_file_name)