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)
