def extract(self):
""" Extracts, duh.
"""
from sqlalchemy import create_engine
engine = create_engine("mssql+pyodbc://%s:%s@%s/%s" % (USER, PASS, self.server, self.database))
#
result = engine.execute(self.query)
from openpyxl import Workbook
from openpyxl.cell import get_column_letter
wb = Workbook()
ws = wb.active
for idx in xrange(1, 3):
for col_idx in xrange(1, len(self.fields) + 1):
col = get_column_letter(col_idx)
if idx == 1:
ws.cell("%s%s" % (col, idx)).value = self.fields[col_idx - 1]
elif idx == 2:
ws.cell("%s%s" % (col, idx)).value = self.mappings[col_idx - 1]
#
for idx, row in enumerate(result):
for col_idx in xrange(1, len(row) + 1):
col = get_column_letter(col_idx)
ws.cell("%s%s" % (col, idx + 3)).value = str(row[col_idx - 1]).decode("utf-8", "ignore")
ws.title = "Extract"
ws = wb.create_sheet()
wb.save(filename="Extracts\%s-%s.xls" % (self.id, datetime.now().strftime("%y%m%d")))
def writeToExcel(iterable,fname):
wb = Workbook()
dest_filename = str(fname)+'.xlsx' #name the file
print dest_filename
ws = wb.worksheets[0]
ws.title = "output" #name the worksheet
row = 1
for item in iterable: #loop through iterable list
col_idx=1
try: #list of dictionaries
for key in item.iterkeys(): #loop through individual iterable items
col = get_column_letter(col_idx)
ws.cell('%s%s'%(col, row)).value = str(item[key]) #save data to cell
col_idx +=1
except: #list of lists
for key in item: #loop through individual iterable items
col = get_column_letter(col_idx)
ws.cell('%s%s'%(col, row)).value = str(key) #save data to cell
col_idx +=1
row +=1
wb.save(filename = dest_filename)
return
def writerow(self, vals):
ws = self.workbook.active
this_row = self.numrows
this_col = 1
#code_color = None
code_color = PatternFill(start_color='00FFCC00', fill_type='solid')
index = 1
if str(vals)[1] == "'":
index = 2
if str(vals)[index] == '1':
code_color = PatternFill(start_color='004472B9', end_color='004472B9', fill_type='solid') #blue
elif str(vals)[index] == '2':
code_color = PatternFill(start_color='004CA454', end_color='004CA454', fill_type='solid') #green
elif str(vals)[index] == '3':
code_color = PatternFill(start_color='00D49b00', end_color='00D49b00', fill_type='solid') #orange
elif str(vals)[index] == '4':
code_color = PatternFill(start_color='00BE4C39', end_color='00BE4C39', fill_type='solid') #red
elif str(vals)[index] == '5':
code_color = PatternFill(start_color='009351A6', end_color='009351A6', fill_type='solid') #purple
for v in vals:
cell = ws.cell(row = this_row, column = this_col)
cell.value = v
cell.fill = code_color
if ws.column_dimensions[get_column_letter(this_col)].width < len(str(v)):
ws.column_dimensions[get_column_letter(this_col)].width = len(str(v)) + 4
this_col += 1
self.numrows += 1
self.worksheet = ws
def __init__(self, searchStr, firstRow, maxRow):
colVals = [item for item in firstRow if searchStr in str(item).lower()]
self.minIndex = chr(ord(get_column_letter(firstRow.index(colVals[0]) + 1))) + str(2)
self.maxIndex = chr(ord(get_column_letter(firstRow.index(colVals[0]) + 1))) + str(maxRow)
# Cell indices start at one in openpyxl, lists start at 0 in Python. +1 is to adjust
if not colVals:
print("Something in the telemetry definition is off. See telemetryGuide.txt for conventions")
def writeToFile(dictionary,fname):
wb = Workbook()
dest_filename = str(fname)+'.xlsx' #name the file
print dest_filename
ws = wb.worksheets[0]
ws.title = "sorting_times" #name the worksheet
row = 1
col_idx=1
for key in dictionary[0].iterkeys():
col = get_column_letter(col_idx)
ws.cell('%s%s'%(col, row)).value = smart_unicode(key) #save data to cell
col_idx +=1
row = 2
for item in dictionary: #loop through dictionary list
col_idx=1
for key in item.iterkeys(): #loop through individual dictionary items
col = get_column_letter(col_idx)
ws.cell('%s%s'%(col, row)).value = smart_unicode(item[key]) #save data to cell
col_idx +=1
row +=1
wb.save(filename = dest_filename)
return
def createExcelFile(con, filepath, dataset): db = con.cursor() query = "SELECT column_ID, name from columns" db.execute(query) results = db.fetchall() columns = dict() for result in results: columns[result['column_ID']] = result['name'] wb = Workbook() ws = wb.worksheets[0] for col_id, col_name in columns.items(): ws.cell(get_column_letter(col_id)+'1').value = col_name row = 1 for data in dataset: row += 1 for key, value in data.items(): if key in columns: ws.cell(get_column_letter(key)+str(row)).value = "; ".join(value) wb.save(filename = filepath+'.xlsx') db.close()
def add_sheet(self, data, title=None, header_row=None, heading=None, auto_width=False, max_auto_width=50):
""" Add a sheet with data to workbook
title: sheet name
header_row: List - Column header (bold with bottom border)
heading: Sheet heading (very top of sheet)
auto_width: will ESTIMATE the width of each column by counting
max chars in each column. It will not work with a formula.
max_auto_width: is the max number of characters a column to be
"""
sheet = self.workbook.create_sheet()
if title:
# Maximum 31 characters allowed in sheet title
clean_title = title[:31]
# From openpyxl's _set_title()
bad_title_char_re = re.compile(r'[\\*?:/\[\]]')
# Replace bad characters with underscores
clean_title = bad_title_char_re.sub('_', clean_title)
sheet.title = unicode(clean_title)
if heading:
sheet.append([unicode(heading)])
if header_row:
header_row = map(unicode, header_row)
sheet.append(header_row)
row = sheet.get_highest_row()
for i, header_cell in enumerate(header_row):
if self.old_openpyxl:
cell = sheet.cell(row=row-1, column=i)
cell.style.font.bold = True
cell.style.borders.bottom.border_style = openpyxl.style.Border.BORDER_THIN
else:
cell = sheet.cell(row=row, column=i+1)
cell.style = cell.style.copy(
font=cell.style.font.copy(bold=True),
border=openpyxl.styles.Border(
bottom=openpyxl.styles.Side(
border_style=openpyxl.styles.borders.BORDER_THIN
)
)
)
for row in data:
row = map(unicode, row)
sheet.append(row)
if auto_width:
column_widths = []
for row in data:
row = map(unicode, row)
for i, cell in enumerate(row):
if len(column_widths) > i:
if len(cell) > column_widths[i]:
column_widths[i] = len(cell)
else:
column_widths += [len(cell)]
for i, column_width in enumerate(column_widths):
if column_width > 3:
if column_width < max_auto_width:
# * 0.9 estimates a typical variable width font
sheet.column_dimensions[get_column_letter(i+1)].width = column_width * 0.9
else:
sheet.column_dimensions[get_column_letter(i+1)].width = max_auto_width
def loadCouchDB(db_name, view_name, excel_name):
tweet_dict = {}
couch_server = Server("http://115.146.94.12:5984")
couch_server.resource.credentials = ('admin', 'admin')
couch_server.config()
db = couch_server[db_name]
wb = Workbook()
ws = wb.active
ws.title = "range names"
rowid = 1
for row in db.view(view_name):
coordinate = re.sub(r"\[|\]", "", str(row.key))
# write coordinate
col = get_column_letter(1)
ws.cell('%s%s'%(col, rowid)).value = coordinate
#write polarity
col = get_column_letter(2)
ws.cell('%s%s'%(col, rowid)).value = getPolarity(row.value)
#write text
col = get_column_letter(3)
ws.cell('%s%s'%(col, rowid)).value = row.value
rowid += 1
ws = wb.create_sheet()
ws.title = 'coordinate'
wb.save(filename = excel_name)
return tweet_dict
def render_xlsx(self, outfd, data):
wb = Workbook(optimized_write = True)
ws = wb.create_sheet()
ws.title = 'Timeline Output'
header = ["Time", "Type", "Item", "Details", "Reason"]
ws.append(header)
total = 1
for line in data:
coldata = line.split("|")
ws.append(coldata)
total += 1
wb.save(filename = self._config.OUTPUT_FILE)
if self._config.HIGHLIGHT != None:
wb = load_workbook(filename = self._config.OUTPUT_FILE)
ws = wb.get_sheet_by_name(name = "Timeline Output")
for col in xrange(1, len(header) + 1):
ws.cell("{0}{1}".format(get_column_letter(col), 1)).style.font.bold = True
for row in xrange(2, total + 1):
for col in xrange(2, len(header)):
if ws.cell("{0}{1}".format(get_column_letter(col), row)).value in self.suspicious.keys():
self.fill(ws, row, len(header) + 1, self.suspicious[ws.cell("{0}{1}".format(get_column_letter(col), row)).value]["color"])
ws.cell("{0}{1}".format(get_column_letter(col + 1), row)).value = self.suspicious[ws.cell("{0}{1}".format(get_column_letter(col), row)).value]["reason"]
wb.save(filename = self._config.OUTPUT_FILE)
def _writecells_xlsx(self, cells, sheet_name, startrow, startcol):
from openpyxl.cell import get_column_letter
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
for cell in cells:
colletter = get_column_letter(startcol + cell.col + 1)
xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
xcell.value = _conv_value(cell.val)
if cell.style:
style = CellStyleConverter.to_xlsx(cell.style)
for field in style.__fields__:
xcell.style.__setattr__(field, style.__getattribute__(field))
if isinstance(cell.val, datetime.datetime):
xcell.style.number_format.format_code = "YYYY-MM-DD HH:MM:SS"
elif isinstance(cell.val, datetime.date):
xcell.style.number_format.format_code = "YYYY-MM-DD"
# merging requires openpyxl latest (works on 1.6.1)
# todo add version check
if cell.mergestart is not None and cell.mergeend is not None:
cletterstart = get_column_letter(startcol + cell.col + 1)
cletterend = get_column_letter(startcol + cell.mergeend + 1)
wks.merge_cells(
"%s%s:%s%s" % (cletterstart, startrow + cell.row + 1, cletterend, startrow + cell.mergestart + 1)
)
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if (start_row is None
or start_column is None
or end_row is None
or end_column is None):
msg = "You have to provide a value either for "\
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise InsufficientCoordinatesException(msg)
else:
range_string = '%s%s:%s%s' % (get_column_letter(start_column),
start_row,
get_column_letter(end_column),
end_row)
elif ":" not in range_string:
if COORD_RE.match(range_string):
return # Single cell
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace('$', '')
if range_string not in self._merged_cells:
self._merged_cells.append(range_string)
cells = cells_from_range(range_string)
# only the top-left cell is preserved
for c in islice(chain.from_iterable(cells), 1, None):
if c in self._cells:
del self._cells[c]
def calculate_dimension(self):
"""Return the minimum bounding range for all cells containing data."""
return '%s%d:%s%d' % (
get_column_letter(1),
self.min_row,
get_column_letter(self.max_column or 1),
self.max_row or 1)
def store_xlsx(data,high,width):
accout=getpass.getuser()
excel=Workbook()
excel_writer=ExcelWriter(workbook = excel)
excel_outpath= r'/home/%s/mapdata/test1.xlsx'%accout
sheet = excel.create_sheet()
sheet=excel.worksheets[0]
sheet.title='testmap'
for row in range (1,(high+1)):
for col in range (1,(width+1)):
column = get_column_letter(col)
sheet.cell('%s%s'%(column,row)).value='%s'%data[row-1][col-1]
for col in range(1,width+1):
column = get_column_letter(col)
sheet.column_dimensions['%s'%column].width = 2.6
for row in range(1,high+1):
sheet.row_dimensions[row].height =14.15
sheet = excel.create_sheet()
sheet.title='parameters'
sheet.cell('A1').value='parameters'
sheet.cell('A2').value='This represents a 2-D grid map, in which each cell represents the probability of occupancy.'
sheet.cell('A7').value='Map width [cells]'
sheet.cell('A8').value='%s'%width
sheet.cell('C7').value='Map height [cells]'
sheet.cell('C8').value='%s'%high
excel.save(filename=excel_outpath)
print 'saving process done'
return 'ok'
def to_excel(self): dest_filename = unicode(QFileDialog.getSaveFileName(self, SAVE_FILE_TITLE, '', ".xlsx(*.xlsx)")) wb = Workbook() ws1 = wb.active ws1.title=REPORTES_TARDANZA_TITLE ws1["B1"]=REPORTES_TARDANZA_TITLE ws1["A2"]=REPORTE_LABEL_NAME ws1["B2"]=self.worker.name+" "+self.worker.father_last_name+" "+self.worker.mother_last_name ws1["C2"]=self.worker.pin for currentColumn in range(self.reporte_table.columnCount()): try: celda = get_column_letter(currentColumn+1)+str(3) ws1[celda] = REPORTE_TARDANZA_TITLE_ROWS[currentColumn] except AttributeError: pass counter=4 for currentRow in range(self.reporte_table.rowCount()): for currentColumn in range(self.reporte_table.columnCount()-1): try: teext = str(self.reporte_table.item(currentRow,currentColumn).text()) celda = get_column_letter(currentColumn+1)+str(currentRow+4) ws1[celda] = teext except AttributeError: pass counter+=1 celda = "A"+str(counter) ws1[celda] = REPORTE_LABEL_TOTAL_MINUTOS_TARDE celda = "B"+str(counter) ws1[celda] = self.text_total_horas.text() try: wb.save(filename = dest_filename) QMessageBox.question(self, 'Message',CREATE_EXCEL_SUCCESS,QMessageBox.Ok) except IOError: QMessageBox.warning(self, 'Error',EXCEL_PROBLEM, QMessageBox.Ok)
def unmerge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Remove merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if start_row is None or start_column is None or end_row is None or end_column is None:
msg = "You have to provide a value either for "\
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise InsufficientCoordinatesException(msg)
else:
range_string = '%s%s:%s%s' % (get_column_letter(start_column + 1), start_row + 1, get_column_letter(end_column + 1), end_row + 1)
elif len(range_string.split(':')) != 2:
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace('$', '')
if range_string in self._merged_cells:
self._merged_cells.remove(range_string)
min_col, min_row = coordinate_from_string(range_string.split(':')[0])
max_col, max_row = coordinate_from_string(range_string.split(':')[1])
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col)
# Mark cell as unmerged
for col in xrange(min_col,max_col+1):
for row in xrange(min_row,max_row+1):
if not (row == min_row and col == min_col):
self._get_cell('%s%s' % (get_column_letter(col), row)).merged = False
else:
msg = 'Cell range %s not known as merged.' % range_string
raise InsufficientCoordinatesException(msg)
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if start_row is None or start_column is None or end_row is None or end_column is None:
msg = "You have to provide a value either for "\
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise InsufficientCoordinatesException(msg)
else:
range_string = '%s%s:%s%s' % (get_column_letter(start_column + 1), start_row + 1, get_column_letter(end_column + 1), end_row + 1)
elif len(range_string.split(':')) != 2:
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace('$', '')
# Make sure top_left cell exists - is this necessary?
min_col, min_row = coordinate_from_string(range_string.split(':')[0])
max_col, max_row = coordinate_from_string(range_string.split(':')[1])
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col)
# Blank out the rest of the cells in the range
for col in xrange(min_col, max_col + 1):
for row in xrange(min_row, max_row + 1):
if not (row == min_row and col == min_col):
# PHPExcel adds cell and specifically blanks it out if it doesn't exist
self._get_cell('%s%s' % (get_column_letter(col), row)).value = None
self._get_cell('%s%s' % (get_column_letter(col), row)).merged = True
if range_string not in self._merged_cells:
self._merged_cells.append(range_string)
def array_to_xlsx(arrayin, filename, alt_gray=True, title='Sheet1', \
set_max_width=False):
wb = Workbook()
dest_filename = filename
ws = wb.worksheets[0]
ws.title = title
for row_index, row in enumerate(arrayin):
for column_index, cell in enumerate(row):
column_letter = get_column_letter((column_index + 1))
cur_cell = ws.cell('%s%s'%(column_letter, (row_index + 1)))
try:
myfloat = float(cell)
cur_cell.value = myfloat
#cur_cell.number_format = '0.00'
#print('yeah, numbers')
except:
cur_cell.value = cell
if alt_gray:
if row_index % 2:
cur_cell.style = lg_style
if set_max_width:
nr, nc = arrayin.shape
for i in range(nc):
max_width = get_max_width(arrayin[:,i])
ws.column_dimensions[get_column_letter(i+1)].width = max_width
wb.save(filename)
def render_ws(ws):
# adjust width of the column
for i in range(ws.max_column):
ws.column_dimensions[get_column_letter(i + 1)].width = 20
# adjust height of the row
for i in range(ws.max_row):
# Get value of specific cells with openpyxl
val = ws["{}{}".format(get_column_letter(5), i + 1)].value
if i == 0:
ws.row_dimensions[i + 1].height = 23
elif val is not None:
if len(val.split('\n')) <= 10:
ws.row_dimensions[i + 1].height = 18 * len(val.split('\n'))
else:
ws.row_dimensions[i + 1].height = 18 * 10
else:
ws.row_dimensions[i + 1].height = 18
# set border, used "iter_rows"
for row in ws.iter_rows("A1:E17"):
for i in range(ws.max_column):
row[i].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
def multiplicationTable(multiplier):
logging.debug('multiplication table initialized...')
# Create the workbook
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
logging.debug('Workbook created and active sheet initialized...')
# Create the first rows
for rows in range(1, multiplier + 1):
sheet[get_column_letter(1) + str(rows)] = rows
logging.debug('First row created...')
# Create the first columns
for columns in range(1, multiplier + 1):
sheet[get_column_letter(columns) + str(1)] = columns
logging.debug('First column created...')
# create the remaining rows with a formula
for rows in range(2, multiplier + 1):
# Create columns.
for columns in range(2, multiplier + 1):
# formula for multiplying the tables.
formula = '=A' + str(rows) + '*' + get_column_letter(columns) + str(1)
sheet[get_column_letter(columns) + str(rows)] = formula
logging.debug('Multiplication table made...')
# Save into a worksheet.
wb.save('multiplicationTable.xlsx')
print('\n\nMultiplication table saved.')
def performanceExcelView(request):
wb = Workbook(encoding='utf-8')
sheet1 = wb.worksheets[0]
header = [
'Name', 'Phone Number', 'Creation Date', 'Facility', 'Village',
'ANC/PNC Advice Registrations', 'Preg/ANC Visit Registered',
'Birth/PNC Visits Registered', 'No. of Confirmed Visits', 'Last Reporting Date'
]
if not cache.get('performance'):
performance = PerformanceView.objects.all().order_by(
'-advice_subs', '-preg_subs', '-birth_subs', '-cvisits'
)
else:
performance = cache.get('performance')
performance_data = performance.values_list(
'name', 'identity', 'created_on', 'facility', 'village',
'advice_subs', 'preg_subs', 'birth_subs', 'cvisits',
'last_reporting_date'
)
for hcol, hcol_data in enumerate(header, start=1):
col_idx = get_column_letter(hcol)
sheet1.cell('%s%s' % (col_idx, 1)).value = hcol_data
for row, row_data in enumerate(performance_data, start=2): # start from row no.2
for col, col_data in enumerate(row_data, start=1):
col_idx = get_column_letter(col)
sheet1.cell('%s%s' % (col_idx, row)).value = col_data
response = HttpResponse(mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=performance_data.xlsx'
wb.save(response)
return response
def to_excel(self): dest_filename = unicode(QFileDialog.getSaveFileName(self, SAVE_FILE_TITLE, '', ".xlsx(*.xlsx)")) wb = Workbook() ws1 = wb.active ws1.title=REPORTES_HORAS_AREA_TITLE ws1["B1"]=REPORTES_HORAS_AREA_TITLE ws1["A3"]=REPORTE_HORAS_AREA_HEADER[0] ws1["B3"]=REPORTE_HORAS_AREA_HEADER[1] for index_f in range(len(self.fechas_lista)): try: celda = get_column_letter(index_f+3)+str(3) ws1[celda] = self.fechas_lista[index_f] except AttributeError: pass for currentRow in range(self.reporte_table.rowCount()): for currentColumn in range(self.reporte_table.columnCount()): try: teext = str(self.reporte_table.item(currentRow,currentColumn).text()) celda = get_column_letter(currentColumn+1)+str(currentRow+4) ws1[celda] = teext except AttributeError: pass try: wb.save(filename = dest_filename) QMessageBox.question(self, 'Message',CREATE_EXCEL_SUCCESS,QMessageBox.Ok) except IOError: QMessageBox.warning(self, 'Error',EXCEL_PROBLEM, QMessageBox.Ok)
def format_xlsx_sheet(df, sheet):
column_widths = []
# Determine the widest cell of the index column (including the index name)
column_widths.append(max([len(df.index.name),
max(map(lambda s: len(str(s)), df.index.values))]))
# For every DataFrame column calculate the widest cell (including the column header)
for column in df.columns:
column_widths.append(max([len(column),
max(map(lambda s: len(str(s)), df[column]))]))
print(' Calculated+Set column_widths = ', column_widths)
for i, column_width in enumerate(column_widths):
sheet.column_dimensions[get_column_letter(i + 1)].width = column_width
font = Font(name='monospace',
size = 10)
ss_num_columns = 1 + len(df.columns) # +1 for the index
ss_num_rows = 1 + len(df) # +1 for the headers
selection = 'A1:' + get_column_letter(ss_num_columns) + str(ss_num_rows)
print(' Cell Selection = ', selection)
print(' Changing font of every cell in the worksheet to name=\'monospace\', size=10')
for column in sheet.columns:
for cell in column:
cell.font = font
def read_dimension(xml_source):
min_row = min_col = max_row = max_col = None
source = _get_xml_iter(xml_source)
it = iterparse(source)
for event, el in it:
if el.tag == '{%s}dimension' % SHEET_MAIN_NS:
dim = el.get("ref")
if ':' in dim:
start, stop = dim.split(':')
else:
start = stop = dim
min_col, min_row = coordinate_from_string(start)
max_col, max_row = coordinate_from_string(stop)
return min_col, min_row, max_col, max_row
if el.tag == '{%s}row' % SHEET_MAIN_NS:
row = el.get("r")
if min_row is None:
min_row = int(row)
span = el.get("spans")
start, stop = span.split(":")
if min_col is None:
min_col = int(start)
max_col = int(stop)
else:
min_col = min(min_col, int(start))
max_col = max(max_col, int(stop))
max_row = int(row)
warn("Unsized worksheet")
return get_column_letter(min_col), min_row, get_column_letter(max_col), max_row
def indexFind(searchStr):
try:
colVals = [item for item in firstRow if searchStr in str(item).lower()]
minIndex = chr(ord(get_column_letter(firstRow.index(colVals[0]))) + 1) + str(2)
maxIndex = chr(ord(get_column_letter(firstRow.index(colVals[0]))) + 1) + str(maxRow)
except:
print("Something in the telemetry definition is off. See telemetryGuide.txt for conventions")
return minIndex, maxIndex
def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
# Write the frame cells using openpyxl.
from openpyxl.cell import get_column_letter
sheet_name = self._get_sheet_name(sheet_name)
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
for cell in cells:
colletter = get_column_letter(startcol + cell.col + 1)
xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
xcell.value = _conv_value(cell.val)
style = None
if cell.style:
style = self._convert_to_style(cell.style)
for field in style.__fields__:
xcell.style.__setattr__(field,
style.__getattribute__(field))
if isinstance(cell.val, datetime.datetime):
xcell.style.number_format.format_code = self.datetime_format
elif isinstance(cell.val, datetime.date):
xcell.style.number_format.format_code = self.date_format
elif isinstance(cell.val, datetime.time):
xcell.style.number_format.format_code = self.time_format
if cell.mergestart is not None and cell.mergeend is not None:
cletterstart = get_column_letter(startcol + cell.col + 1)
cletterend = get_column_letter(startcol + cell.mergeend + 1)
wks.merge_cells('%s%s:%s%s' % (cletterstart,
startrow + cell.row + 1,
cletterend,
startrow + cell.mergestart + 1))
# Excel requires that the format of the first cell in a merged
# range is repeated in the rest of the merged range.
if style:
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
colletter = get_column_letter(col)
xcell = wks.cell("%s%s" % (colletter, row))
for field in style.__fields__:
xcell.style.__setattr__(
field, style.__getattribute__(field))
def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
# Write the frame cells using openpyxl.
from openpyxl.cell import get_column_letter
sheet_name = self._get_sheet_name(sheet_name)
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
for cell in cells:
colletter = get_column_letter(startcol + cell.col + 1)
xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
xcell.value = _conv_value(cell.val)
style_kwargs = {}
# Apply format codes before cell.style to allow override
if isinstance(cell.val, datetime.datetime):
style_kwargs.update(self._convert_to_style_kwargs({
'number_format':{'format_code': self.datetime_format}}))
elif isinstance(cell.val, datetime.date):
style_kwargs.update(self._convert_to_style_kwargs({
'number_format':{'format_code': self.date_format}}))
if cell.style:
style_kwargs.update(self._convert_to_style_kwargs(cell.style))
if style_kwargs:
xcell.style = xcell.style.copy(**style_kwargs)
if cell.mergestart is not None and cell.mergeend is not None:
cletterstart = get_column_letter(startcol + cell.col + 1)
cletterend = get_column_letter(startcol + cell.mergeend + 1)
wks.merge_cells('%s%s:%s%s' % (cletterstart,
startrow + cell.row + 1,
cletterend,
startrow + cell.mergestart + 1))
# Excel requires that the format of the first cell in a merged
# range is repeated in the rest of the merged range.
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
colletter = get_column_letter(col)
xcell = wks.cell("%s%s" % (colletter, row))
xcell.style = xcell.style.copy(**style_kwargs)
def __init__(self):
self.ner_log_workbook = Workbook()
self.ner_log_worksheet = self.ner_log_workbook.worksheets[0]
self.ner_log_worksheet.title = 'NER service invocation results'
self.ner_log_worksheet.cell('%s%s' % (get_column_letter(1), 1)).value = "Title"
self.ner_log_worksheet.cell('%s%s' % (get_column_letter(2), 1)).value = "Description"
self.ner_log_worksheet.cell('%s%s' % (get_column_letter(3), 1)).value = "Zemanta Results"
self.ner_log_worksheet.cell('%s%s' % (get_column_letter(4), 1)).value = "NERD Results"
self.cur_row = 2
def add_totals_to_sheet(wb, sheet, header_dict):
for category, record in header_dict.iteritems():
col = get_column_letter(record[0])
end_col = get_column_letter(record[0] + 2)
row = str(record[1])
sheet.merge_cells(col + row + ':' + end_col + row)
sheet[col + row] = record[2]
sheet[col + row].font = Font(bold = True, size = 14)
sheet[col + row].alignment = Alignment(horizontal='center')
def findIndex(self, searchStr):
# Finds the indices of a column in the telemetry definition
colVals = [item for item in self.firstRow if searchStr in str(item).lower()]
minIndex = chr(ord(get_column_letter(self.firstRow.index(colVals[0]) + 1))) + str(2)
maxIndex = chr(ord(get_column_letter(self.firstRow.index(colVals[0]) + 1))) + str(self.maxRow)
# Cell indices start at one in openpyxl, lists start at 0 in Python. +1 is to adjust
if not colVals:
print("Something in the telemetry definition is off. See telemetryGuide.txt for conventions")
return minIndex, maxIndex
def __str__(self):
""" format excel reference notation """
if self.pos2:
return "'%s'!$%s$%s:$%s$%s" % (self.sheet.title,
get_column_letter(self.pos1[1] + 1), self.pos1[0] + 1,
get_column_letter(self.pos2[1] + 1), self.pos2[0] + 1)
else:
return "'%s'!$%s$%s" % (self.sheet.title,
get_column_letter(self.pos1[1] + 1), self.pos1[0] + 1)
| |
| |
+-------------------------------------------+
'''
# print s
for ws in wb:
print ws.title
highest_column = ws.get_highest_column()
highest_row = ws.get_highest_row()
print highest_row, highest_column
# for col_index in xrange(1, 10):
# col = get_colum_letter(col_index)
# for col_index in xrange(1, highest_column+1):
# col = get_column_letter(col_index)
# for row in xrange(1, highest_row+1):
# # print ws.cell('%s%s' % (col, row)).value
# print '%s%s' % (col, row)
for row in xrange(1, highest_row + 1):
for col_index in xrange(1, highest_column + 1):
col = get_column_letter(col_index)
# print '%s%s' % (col, row)
# print '%s%s: %s' % (col, row, ws.cell('%s%s' % (col, row)).value)
print '%s: %s' % (ws.cell(
'%s%s' % (col, 1)).value, ws.cell('%s%s' % (col, row)).value)
print s
def test_dump_string_table():
test_filename = _get_test_filename()
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
letters = [get_column_letter(x + 1) for x in range(10)]
expected_rows = []
for row in range(5):
ws.append(['%s%d' % (letter, row + 1) for letter in letters])
table = list(wb.shared_strings)
assert table == [
'A1',
'B1',
'C1',
'D1',
'E1',
'F1',
'G1',
'H1',
'I1',
'J1',
'A2',
'B2',
'C2',
'D2',
'E2',
'F2',
'G2',
'H2',
'I2',
'J2',
'A3',
'B3',
'C3',
'D3',
'E3',
'F3',
'G3',
'H3',
'I3',
'J3',
'A4',
'B4',
'C4',
'D4',
'E4',
'F4',
'G4',
'H4',
'I4',
'J4',
'A5',
'B5',
'C5',
'D5',
'E5',
'F5',
'G5',
'H5',
'I5',
'J5',
]
from openpyxl.compat import range
from openpyxl.cell import get_column_letter
wb = Workbook()
dest_filename = 'empty_book2.xlsx'
ws1 = wb.active # 第一个表
ws1.title = "range names" # 第一个表命名
# 遍历第一个表的1到40行，赋值一个600内的随机数
for row in range(1, 40):
ws1.append(range(60))
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
_ = ws3.cell(column=col, row=row, value="%s" % get_column_letter(col))
wb.save(filename=dest_filename)
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.cell import get_column_letter
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active #第一个表
ws1.title = "range names" #第一个表命名
#遍历第一个表的1到39行，每行赋值从1到599.https://segmentfault.com/a/1190000005144821
for row in range(1,40):
ws1.append(range(600))
ws2 = wb.create_sheet(title="Pi") # 创建第二个表
ws2['F5'] = 3.14 #为第二个表的F5单元格赋值为3.14
ws3 = wb.create_sheet(title="Data") #创建第三个表
/* 下面遍历第三个表的10到19行，27到53列，并对每一行的单元格赋一个当前列名的名字如下图 */
for row in range(10,20):
for col in range(27,54):
_=ws3.cell(column=col,row=row,value="%s" % get_column_letter(col)) #_当作一个普通的变量，一般表示后边不再使用
wb.save(filename=dest_filename) #保存
def get_dimensions(self):
if not self._max_col or not self._max_row:
return 'A1'
else:
return '%s%d' % (get_column_letter(self._max_col), (self._max_row))
def get_range(sheet, left_column, left_row, right_column, right_row):
range_string = "%s%d:%s%d" % (get_column_letter(left_column), left_row,
get_column_letter(right_column), right_row)
return sheet.range(range_string)
def shortExit():
if day_num >= 20 and Sum[get_column_letter(month_num+9)+'16'].value == 0:
if input('Did you already log student loans this month?\n').lower().startswith('n'):
from openpyxl import Workbook
from openpyxl.compat import range
#from openpyxl.cell import get_column_letter
## 其实这个demo源于官方文档
## http://openpyxl.readthedocs.io/en/default/usage.html?highlight=get_column_letter
try:
from openpyxl.cell import get_column_letter
except ImportError:
from openpyxl.utils import get_column_letter ### openpyxl新版本中使用utils
wb = Workbook()
dest_filename = 'empty_book4.xlsx'
ws1 = wb.active # 第一个表
ws1.title = "range names" # 第一个表命名
# 遍历第一个表的1到40行，赋值一个600内的随机数
for row in range(1, 40):
ws1.append(range(60))
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20): ### 10 ~20行
for col in range(27, 54): ### 27~54列 即AA~BA列
_ = ws3.cell(column=col, row=row, value="%s" % get_column_letter(col)) ### 在当前单元格写入数据，写入的数据就是列号 ，仔细看创建出来的表
print(ws3['AA10'].value) ###renbin.guo added
wb.save(filename=dest_filename)
import openpyxl
from openpyxl.cell import get_column_letter, column_index_from_string
print(get_column_letter(27))
print(get_column_letter(3))
print(column_index_from_string('AB'))
wb=openpyxl.load_workbook('example.xlsx')
sheet=wb.active
print(get_column_letter(sheet.max_column))
#validate we're getting an integer from the given arguments
int(sys.argv[1])
except:
print('Enter an integer')
sys.exit()
workBook = openpyxl.Workbook()
sheet = workBook.active
sheet.title = str(sys.argv[1]) + ' Multiplication Table'
multiply = 1
lstA = []
fontObj = Font(bold=True)
for i in range(int(sys.argv[1])):
multiply += 1
#columns
sheet[get_column_letter(multiply) + '1'].font = fontObj
sheet[get_column_letter(multiply) + '1'] = multiply - 1
lstA.append(multiply - 1)
#rows
sheet['A' + str(multiply)].font = fontObj
sheet['A' + str(multiply)] = multiply - 1
letter = 2
multiplyIndex = 0
matrixLen = int(sys.argv[1])
for i in range(matrixLen):
row = 2
for m in range(matrixLen):
cell = get_column_letter(letter) + str(row)
sheet[cell] = lstA[i] * lstA[multiplyIndex + m]
def getDateColumn():
for i in range(1, len(sheet.rows[0]) + 1):
col = get_column_letter(i)
if sheet.cell('%s%s' % (col, '1')).value == currentDate:
return col
def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
# Write the frame cells using openpyxl.
from openpyxl.cell import get_column_letter
sheet_name = self._get_sheet_name(sheet_name)
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
for cell in cells:
colletter = get_column_letter(startcol + cell.col + 1)
xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
xcell.value = _conv_value(cell.val)
style_kwargs = {}
# Apply format codes before cell.style to allow override
if isinstance(cell.val, datetime.datetime):
style_kwargs.update(
self._convert_to_style_kwargs({
'number_format': {
'format_code': self.datetime_format
}
}))
elif isinstance(cell.val, datetime.date):
style_kwargs.update(
self._convert_to_style_kwargs(
{'number_format': {
'format_code': self.date_format
}}))
if cell.style:
style_kwargs.update(self._convert_to_style_kwargs(cell.style))
if style_kwargs:
xcell.style = xcell.style.copy(**style_kwargs)
if cell.mergestart is not None and cell.mergeend is not None:
cletterstart = get_column_letter(startcol + cell.col + 1)
cletterend = get_column_letter(startcol + cell.mergeend + 1)
wks.merge_cells('%s%s:%s%s' %
(cletterstart, startrow + cell.row + 1,
cletterend, startrow + cell.mergestart + 1))
# Excel requires that the format of the first cell in a merged
# range is repeated in the rest of the merged range.
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
colletter = get_column_letter(col)
xcell = wks.cell("%s%s" % (colletter, row))
xcell.style = xcell.style.copy(**style_kwargs)
def test_column_letter_boundries(value):
with pytest.raises(ValueError):
get_column_letter(value)
def test_column_letter(value, expected):
assert get_column_letter(value) == expected
def getEmptyCol(start_col, sheet):
for i in range(start_col, 999):
val = sheet[get_column_letter(i) + '1'].value
if val is None or val == time.strftime("%m/%d/%y"):
return get_column_letter(i)
def fast_parse(ws, xml_source, string_table, style_table):
xmlns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
root = fromstring(xml_source)
mergeCells = root.find(QName(xmlns, 'mergeCells').text)
if mergeCells is not None:
mergeCellNodes = mergeCells.findall(QName(xmlns, 'mergeCell').text)
for mergeCell in mergeCellNodes:
ws.merge_cells(mergeCell.get('ref'))
source = _get_xml_iter(xml_source)
it = iterparse(source)
for event, element in filter(filter_cells, it):
value = element.findtext('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v')
coordinate = element.get('r')
style_id = element.get('s')
if style_id is not None:
ws._styles[coordinate] = style_table.get(int(style_id))
if value is not None:
data_type = element.get('t', 'n')
if data_type == Cell.TYPE_STRING:
value = string_table.get(int(value))
ws.cell(coordinate).value = value
# to avoid memory exhaustion, clear the item after use
element.clear()
cols = root.find(QName(xmlns, 'cols').text)
if cols is not None:
colNodes = cols.findall(QName(xmlns, 'col').text)
for col in colNodes:
min = int(col.get('min')) if col.get('min') else 1
max = int(col.get('max')) if col.get('max') else 1
for colId in range(min, max + 1):
column = get_column_letter(colId)
if column not in ws.column_dimensions:
ws.column_dimensions[column] = ColumnDimension(column)
if col.get('width') is not None:
ws.column_dimensions[column].width = float(col.get('width'))
if col.get('bestFit') == '1':
ws.column_dimensions[column].auto_size = True
if col.get('hidden') == '1':
ws.column_dimensions[column].visible = False
if col.get('outlineLevel') is not None:
ws.column_dimensions[column].outline_level = int(col.get('outlineLevel'))
if col.get('collapsed') == '1':
ws.column_dimensions[column].collapsed = True
if col.get('style') is not None:
ws.column_dimensions[column].style_index = col.get('style')
printOptions = root.find(QName(xmlns, 'printOptions').text)
if printOptions is not None:
if printOptions.get('horizontalCentered') is not None:
ws.page_setup.horizontalCentered = printOptions.get('horizontalCentered')
if printOptions.get('verticalCentered') is not None:
ws.page_setup.verticalCentered = printOptions.get('verticalCentered')
pageMargins = root.find(QName(xmlns, 'pageMargins').text)
if pageMargins is not None:
if pageMargins.get('left') is not None:
ws.page_margins.left = float(pageMargins.get('left'))
if pageMargins.get('right') is not None:
ws.page_margins.right = float(pageMargins.get('right'))
if pageMargins.get('top') is not None:
ws.page_margins.top = float(pageMargins.get('top'))
if pageMargins.get('bottom') is not None:
ws.page_margins.bottom = float(pageMargins.get('bottom'))
if pageMargins.get('header') is not None:
ws.page_margins.header = float(pageMargins.get('header'))
if pageMargins.get('footer') is not None:
ws.page_margins.footer = float(pageMargins.get('footer'))
pageSetup = root.find(QName(xmlns, 'pageSetup').text)
if pageSetup is not None:
if pageSetup.get('orientation') is not None:
ws.page_setup.orientation = pageSetup.get('orientation')
if pageSetup.get('paperSize') is not None:
ws.page_setup.paperSize = pageSetup.get('paperSize')
if pageSetup.get('scale') is not None:
ws.page_setup.top = pageSetup.get('scale')
if pageSetup.get('fitToPage') is not None:
ws.page_setup.fitToPage = pageSetup.get('fitToPage')
if pageSetup.get('fitToHeight') is not None:
ws.page_setup.fitToHeight = pageSetup.get('fitToHeight')
if pageSetup.get('fitToWidth') is not None:
ws.page_setup.fitToWidth = pageSetup.get('fitToWidth')
if pageSetup.get('firstPageNumber') is not None:
ws.page_setup.firstPageNumber = pageSetup.get('firstPageNumber')
if pageSetup.get('useFirstPageNumber') is not None:
ws.page_setup.useFirstPageNumber = pageSetup.get('useFirstPageNumber')
headerFooter = root.find(QName(xmlns, 'headerFooter').text)
if headerFooter is not None:
oddHeader = headerFooter.find(QName(xmlns, 'oddHeader').text)
if oddHeader is not None:
ws.header_footer.setHeader(oddHeader.text)
oddFooter = headerFooter.find(QName(xmlns, 'oddFooter').text)
if oddFooter is not None:
ws.header_footer.setFooter(oddFooter.text)
column_index_from_string)
from openpyxl.styles import is_date_format
from openpyxl.shared.date_time import SharedDate
from openpyxl.reader.worksheet import read_dimension
from openpyxl.shared.compat import unicode
from openpyxl.shared.ooxml import (PACKAGE_WORKSHEETS, SHEET_MAIN_NS)
TYPE_NULL = Cell.TYPE_NULL
MISSING_VALUE = None
RE_COORDINATE = re.compile('^([A-Z]+)([0-9]+)$')
SHARED_DATE = SharedDate()
_COL_CONVERSION_CACHE = dict(
(get_column_letter(i), i) for i in xrange(1, 18279))
def column_index_from_string(str_col,
_col_conversion_cache=_COL_CONVERSION_CACHE):
# we use a function argument to get indexed name lookup
return _col_conversion_cache[str_col]
del _COL_CONVERSION_CACHE
RAW_ATTRIBUTES = [
'row', 'column', 'coordinate', 'internal_value', 'data_type', 'style_id',
'number_format'
]
def calculate_dimension(self):
"""Return the minimum bounding range for all cells containing data."""
return 'A1:%s%d' % (get_column_letter(
self.get_highest_column()), self.get_highest_row())
def render_xlsx(self, outfd, data):
BoldStyle = Style(font=Font(name='Calibri',
size=11,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FFFFFFFF'),
fill=PatternFill(fill_type="solid",
start_color='FF000000',
end_color='FF000000'))
RedStyle = Style(font=Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'),
border=Border(left=Side(border_style="thick",
color='FF000000'),
right=Side(border_style="thick",
color='FF000000'),
top=Side(border_style="thick",
color='FF000000'),
bottom=Side(border_style="thick",
color='FF000000'),
diagonal=Side(border_style="thick",
color='FF000000'),
diagonal_direction=0,
outline=Side(border_style="thick",
color='FF000000'),
vertical=Side(border_style="thick",
color='FF000000'),
horizontal=Side(border_style="thick",
color='FF000000')),
fill=PatternFill(start_color='FFFF0000',
end_color='FFFF0000',
fill_type='solid'))
GreenStyle = Style(font=Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'),
fill=PatternFill(start_color="FF00FF00",
end_color="FF00FF00",
fill_type="solid"))
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
ws.title = "Psxview Output"
ws.append([
"Offset (P)", "Name", "PID", "pslist", "psscan", "thrdproc",
"pspcid", "csrss", "session", "deskthrd", "Exit Time"
])
total = 1
for offset, process, ps_sources in data:
incsrss = ps_sources['csrss'].has_key(offset)
insession = ps_sources['session'].has_key(offset)
indesktop = ps_sources['deskthrd'].has_key(offset)
inpspcid = ps_sources['pspcid'].has_key(offset)
inpslist = ps_sources['pslist'].has_key(offset)
inthread = ps_sources['thrdproc'].has_key(offset)
if self._config.APPLY_RULES:
if not incsrss:
if str(process.ImageFileName).lower() in [
"system", "smss.exe", "csrss.exe"
]:
incsrss = "Okay"
elif process.ExitTime > 0:
incsrss = "Okay"
if not insession:
if str(process.ImageFileName).lower() in [
"system", "smss.exe"
]:
insession = "Okay"
elif process.ExitTime > 0:
insession = "Okay"
if not indesktop:
if str(process.ImageFileName).lower() in [
"system", "smss.exe", "lsass.exe"
]:
indesktop = "Okay"
elif process.ExitTime > 0:
indesktop = "Okay"
if not inpspcid:
if process.ExitTime > 0:
inpspcid = "Okay"
if not inpslist:
if process.ExitTime > 0:
inpslist = "Okay"
if not inthread:
if process.ExitTime > 0:
inthread = "Okay"
ws.append([
hex(offset),
str(
utils.remove_unprintable(str(process.ImageFileName))
or ""),
str(process.UniqueProcessId),
str(inpslist),
str(ps_sources['psscan'].has_key(offset)),
str(inthread),
str(inpspcid),
str(incsrss),
str(insession),
str(indesktop),
str(process.ExitTime or '')
])
total += 1
wb.save(filename=self._config.OUTPUT_FILE)
wb = load_workbook(filename=self._config.OUTPUT_FILE)
ws = wb.get_sheet_by_name(name="Psxview Output")
for col in xrange(1, 12):
ws.cell("{0}{1}".format(get_column_letter(col),
1)).style = BoldStyle
for row in xrange(2, total + 1):
for col in xrange(4, 11):
if ws.cell("{0}{1}".format(get_column_letter(col),
row)).value == "False":
ws.cell("{0}{1}".format(get_column_letter(col),
row)).style = RedStyle
else:
ws.cell("{0}{1}".format(get_column_letter(col),
row)).style = GreenStyle
wb.save(filename=self._config.OUTPUT_FILE)
def range(self, range_string, row=0, column=0):
"""Returns a 2D array of cells, with optional row and column offsets.
:param range_string: cell range string or `named range` name
:type range_string: string
:param row: number of rows to offset
:type row: int
:param column: number of columns to offset
:type column: int
:rtype: tuples of tuples of :class:`openpyxl.cell.Cell`
"""
if ':' in range_string:
# R1C1 range
result = []
min_range, max_range = range_string.split(':')
min_col, min_row = coordinate_from_string(min_range)
max_col, max_row = coordinate_from_string(max_range)
if column:
min_col = get_column_letter(
column_index_from_string(min_col) + column)
max_col = get_column_letter(
column_index_from_string(max_col) + column)
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col)
cache_cols = {}
for col in xrange(min_col, max_col + 1):
cache_cols[col] = get_column_letter(col)
rows = xrange(min_row + row, max_row + row + 1)
cols = xrange(min_col, max_col + 1)
for row in rows:
new_row = []
for col in cols:
new_row.append(self.cell('%s%s' % (cache_cols[col], row)))
result.append(tuple(new_row))
return tuple(result)
else:
try:
return self.cell(coordinate=range_string,
row=row,
column=column)
except CellCoordinatesException:
pass
# named range
named_range = self._parent.get_named_range(range_string)
if named_range is None:
msg = '%s is not a valid range name' % range_string
raise NamedRangeException(msg)
result = []
for destination in named_range.destinations:
worksheet, cells_range = destination
if worksheet is not self:
msg = 'Range %s is not defined on worksheet %s' % \
(cells_range, self.title)
raise NamedRangeException(msg)
content = self.range(cells_range)
if isinstance(content, tuple):
for cells in content:
result.extend(cells)
else:
result.append(content)
if len(result) == 1:
return result[0]
else:
return tuple(result)
def setWorkbook():
if Sum[get_column_letter(month_num+9) + '1'].value == 'Average':
if month_num == 12:
'Row ' + str(c.row) + ', colum ' + c.column + ' is ' + c.value
'Cell ' + c.coordinate + ' is ' + c.value
#the first row and column is 1
sheet.cell(row=1, column=2) # 'B1'
sheet.cell(row=1, column=2).value
for i in range(1, 8, 2):
print(i, sheet.cell(row=i, column=2).value)
print()
sheet.max_row
sheet.max_column
#Covert between column letter and number
get_column_letter(1)
column_index_from_string(get_column_letter(sheet.max_column))
#Get rows and column from the sheets
tuple(sheet['A1':'C3'])
for eachTuple in sheet[
'A1':'C3']: #each row is a tuple, so (A1,B1,C1),(A2,B2,C2)...
for m in eachTuple:
print(m.coordinate, m.value)
print('---END OF ROW---')
for k in list(sheet.columns)[1]: #column=list(sheet.columns)
print(k.value)
print()
from openpyxl.cell import get_column_letter
from openpyxl.writer.excel import ExcelWriter
from openpyxl.workbook import Workbook
bid = [3716, 3715, 3714, 3711, 2279]
wb = load_workbook(filename='bookLIst.xlsx')
sheet_ranges = wb['Sheet1']
#print sheet_ranges['A2'].value
count_row = 1
count_col = 1
wb1 = Workbook()
ws1 = wb1.worksheets[0]
ew = ExcelWriter(workbook=wb1)
ws = wb.active
row_count = 1
for row in ws.iter_rows("A1:AU3077"):
try:
value = int(row[0].value)
except:
pass
for i in bid:
if value == i:
for i in row:
col = get_column_letter(count_col)
ws1.cell("%s%s" % (col, count_row)).value = i.value
count_col += 1
count_row += 1
count_col = 1
dest_filename = r"result.xlsx"
ew.save(filename=dest_filename)
def test_column_letter():
eq_('ZZZ', get_column_letter(18278))
eq_('JJJ', get_column_letter(7030))
eq_('AB', get_column_letter(28))
eq_('AA', get_column_letter(27))
eq_('Z', get_column_letter(26))
},
'formula': {
'type': Cell.TYPE_FORMULA,
'style': '0'
},
'boolean': {
'type': Cell.TYPE_BOOL,
'style': '0'
},
}
DESCRIPTORS_CACHE_SIZE = 50
DATETIME_STYLE = Style()
DATETIME_STYLE.number_format.format_code = NumberFormat.FORMAT_DATE_YYYYMMDD2
BOUNDING_BOX_PLACEHOLDER = 'A1:%s%d' % (get_column_letter(MAX_COLUMN), MAX_ROW)
def create_temporary_file(suffix=''):
fobj = NamedTemporaryFile(mode='w+',
suffix=suffix,
prefix='openpyxl.',
delete=False)
filename = fobj.name
return filename
class DumpWorksheet(Worksheet):
"""
.. warning::
def write_table(self,
ws,
table,
v_titles=None,
h_titles=None,
v_sum=False,
h_sum=False,
d_sum=False,
highlight_diagonal=False,
highlight_max=False):
filas = len(table)
columnas = len(table[0])
# creamos copias de todo porque Python pasa todo por referencia
_table = copy.deepcopy(table)
_h_titles = copy.deepcopy(h_titles)
_v_titles = copy.deepcopy(v_titles)
# calculo del valor máximo de la tabla
if highlight_max:
maximum = max(max(l) for l in _table)
# calculo de la suma de la diagonal
if d_sum:
table_short_side = min(len(_table), len(_table[0]))
d_suma = sum([_table[i][i] for i in range(table_short_side)])
# informacion adicional (sumas filas, columnas, diagonales...)
filas, columnas = self.__aditional_inf(_table, _v_titles, _h_titles,
filas, columnas, v_sum, h_sum)
# escritura de titulos de tabla (si existen)
h_desp, v_desp = self.__write_titles(ws, _v_titles, _h_titles)
# indices verticales de tabla
ind_verticales = [str(i + v_desp) for i in range(filas)]
#escritura de tabla
for f in range(filas):
for c in range(columnas):
# if c == columnas-1 and f == filas-1:
# continue
col = get_column_letter(c + h_desp)
cell = ws.cell(col + ind_verticales[f])
cell.value = _table[f][c]
cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER
# maximo de la tabla
if highlight_max and _table[f][c] == maximum:
cell.style.fill.fill_type = Fill.FILL_SOLID
cell.style.fill.start_color.index = Color.myRed
# diagonal de la tabla
if highlight_diagonal and c == f:
if not (c == columnas-1 and h_sum) and \
not (f == filas-1 and v_sum):
cell.style.fill.fill_type = Fill.FILL_SOLID
cell.style.fill.start_color.index = Color.myGreen
# suma de columnas
if f == filas - 1 and h_sum and len(self.cols2add) > 0:
cell.style.font.bold = True
if c not in self.cols2add:
cell.value = ""
# suma de filas
if c == columnas - 1 and v_sum and len(self.rows2add) > 0:
cell.style.font.bold = True
if f not in self.rows2add:
cell.value = ""
# escritura de la suma de la diagonal
if d_sum:
col = get_column_letter(columnas + h_desp - 1)
row = str(filas + v_desp - 1)
if not (h_sum and len(self.rows2add) > 0):
col = get_column_letter(columnas + h_desp)
if not (v_sum and len(self.cols2add) > 0): row = str(int(row) + 1)
cell = ws.cell(col + row)
cell.value = d_suma
cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER
cell.style.fill.fill_type = Fill.FILL_SOLID
cell.style.fill.start_color.index = Color.myGreen
cell.style.font.bold = True
import os, openpyxl, logging, sys
from openpyxl.cell import get_column_letter, column_index_from_string
from openpyxl.styles import Font, Style
logging.basicConfig(level=logging.DEBUG,
format=' %(asctime)s - %(levelname)s - %(message)s')
# logging.disable(logging.CRITICAL)
path = '.\\excel\\'
if not os.path.exists(path):
os.makedirs(path)
os.chdir(path)
wb = openpyxl.load_workbook('table5x5.xlsx')
sheet = wb.get_sheet_by_name('Sheet')
wbNew = openpyxl.Workbook()
sheetNew = wbNew.get_sheet_by_name('Sheet')
startLineNumber = input('start line: ')
blankLinesNumber = input('num. of blank lines: ')
for row in range(sheet.max_row):
col = row + 1
copyValue = get_column_letter(col) + str((row + 1))
logging.debug(copyValue)
sheet.cell(row=2, column=5)
wbNew.save('newTable.xlsx')
import csv
from openpyxl import Workbook
from openpyxl.cell import get_column_letter
f = open(r'D:\Data\Python\export\fragilities.cvs')
csv.register_dialect('colons', delimiter=':')
reader = csv.reader(f, dialect='colons')
wb = Workbook()
dest_filename = r"D:\Data\Python\export\fragilities.xlsx"
ws = wb.worksheets[0]
ws.title = "A Snazzy Title"
for row_index, row in enumerate(reader):
for column_index, cell in enumerate(row):
column_letter = get_column_letter((column_index + 1))
ws.cell('%s%s' % (column_letter, (row_index + 1))).value = cell
wb.save(filename=dest_filename)
for i in xrange(1):
vals = np.genfromtxt(fns[i], delimiter=',', skip_header=1)[:, 1]
r = csv.reader(open(fns[i]))
line1 = r.next()
line1 = line1[0] + line1[1]
hdrs = [int(s) for s in line1.split() if s.isdigit()]
wsd = wb.create_sheet(title=str(hdrs[1]) + ', ' + str(hdrs[0]))
for irow in xrange(int(vals.size)):
wsd.append([vals[irow]])
wb.save('file.xlsx')
from openpyxl.cell import get_column_letter
wb = xl.workbook()
dest_filename = r'empty_book.xlsx'
ws = wb.active
ws.title = "range names"
for col_idx in xrange(1, 40):
col = get_column_letter(col_idx)
for row in xrange(1, 600):
ws.cell('%s%s' % (col, row)).value = '%s%s' % (col, row)
ws = wb.create_sheet()
ws.title = 'Pi'
ws['F5'] = 3.14
wb.save(filename=dest_filename)
def eligible_candidates_list():
try:
#global statusStr
#print "Filtering eligible candidates from the entire applied candidated list..."
#Vstr.set(statusStr)
wb = load_workbook(filename="InputFiles/BranchChange.xlsx")
ws = wb.get_sheet_by_name("common")
tmp = ws.columns
#print len(tmp)
rows = len(tmp[0]) - 1
#print rows
global no_of_applied_students
no_of_applied_students = rows
wb_new = Workbook()
dest_filename = r'OutputFiles/EligibleStudents.xlsx'
ws_new = wb_new.worksheets[0]
ws_new.title = "common"
for col_idx in xrange(1, 11):
col = get_column_letter(col_idx)
row_1 = 1
row_2 = 1
while row_2 < rows + 1:
enrno = ws.cell('%s%s' % ('A', row_2 + 1)).value
name = ws.cell('%s%s' % ('B', row_2 + 1)).value
if ws.cell('%s%s'%('I', row_2 + 1)).value == 21.0 and ws.cell('%s%s'%('J', row_2 + 1)).value == False \
and ws.cell('%s%s'%('C', row_2 + 1)).value in eligible_progs:
ws_new.cell('%s%s' % (col, row_1)).value = ws.cell(
'%s%s' % (col, row_2 + 1)).value
row_1 = row_1 + 1
elif ws.cell('%s%s'%('I', row_2 + 1)).value is not 21.0 and ws.cell('%s%s'%('J', row_2 + 1)).value == False \
and ws.cell('%s%s'%('C', row_2 + 1)).value in eligible_progs:
reason = " Insufficient earned credits "
ineligible_candidates[enrno] = [name, reason]
elif ws.cell('%s%s'%('I', row_2 + 1)).value == 21.0 and ws.cell('%s%s'%('J', row_2 + 1)).value == True \
and ws.cell('%s%s'%('C', row_2 + 1)).value in eligible_progs:
reason = " Penality for indicipline "
ineligible_candidates[enrno] = [name, reason]
elif ws.cell('%s%s'%('I', row_2 + 1)).value == 21.0 and ws.cell('%s%s'%('J', row_2 + 1)).value == False \
and ws.cell('%s%s'%('C', row_2 + 1)).value not in eligible_progs:
reason = " Branch not suitable to change "
ineligible_candidates[enrno] = [name, reason]
elif ws.cell('%s%s'%('I', row_2 + 1)).value is not 21.0 and ws.cell('%s%s'%('J', row_2 + 1)).value == True \
and ws.cell('%s%s'%('C', row_2 + 1)).value in eligible_progs:
reason = " ID/IEC "
ineligible_candidates[enrno] = [name, reason]
elif ws.cell('%s%s'%('I', row_2 + 1)).value == 21.0 and ws.cell('%s%s'%('J', row_2 + 1)).value == True \
and ws.cell('%s%s'%('C', row_2 + 1)).value not in eligible_progs:
reason = " CBNS/ID "
ineligible_candidates[enrno] = [name, reason]
elif ws.cell('%s%s'%('I', row_2 + 1)).value is not 21.0 and ws.cell('%s%s'%('J', row_2 + 1)).value == False \
and ws.cell('%s%s'%('C', row_2 + 1)).value not in eligible_progs:
reason = " CBNS/IEC "
ineligible_candidates[enrno] = [name, reason]
elif ws.cell('%s%s'%('I', row_2 + 1)).value is not 21.0 and ws.cell('%s%s'%('J', row_2 + 1)).value == True \
and ws.cell('%s%s'%('C', row_2 + 1)).value not in eligible_progs:
reason = " CBNS/ID/IEC "
ineligible_candidates[enrno] = [name, reason]
row_2 = row_2 + 1
wb_new.save(filename=dest_filename)
except Exception as e:
tkMessageBox.showerror(
"Error Message",
"Error occured while filtering eligible candidates: \n(%s)" %
str(e))
sys.exit(1)
