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)