Ejemplo n.º 1
0
    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")))
Ejemplo n.º 2
0
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
Ejemplo n.º 3
0
 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
Ejemplo n.º 4
0
 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")
Ejemplo n.º 5
0
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
    
Ejemplo n.º 6
0
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()
Ejemplo n.º 7
0
 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
Ejemplo n.º 9
0
    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)
Ejemplo n.º 10
0
    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)
Ejemplo n.º 15
0
    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)
Ejemplo n.º 16
0
    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)
Ejemplo n.º 17
0
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)
Ejemplo n.º 18
0
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.')
Ejemplo n.º 20
0
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
Ejemplo n.º 23
0
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
Ejemplo n.º 24
0
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
Ejemplo n.º 25
0
    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))
Ejemplo n.º 26
0
    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)
Ejemplo n.º 27
0
 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
Ejemplo n.º 28
0
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')
Ejemplo n.º 29
0
 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
Ejemplo n.º 30
0
    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)
Ejemplo n.º 31
0
|                                           |
|                                           |
+-------------------------------------------+

	'''
# 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
Ejemplo n.º 32
0
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',
    ]
Ejemplo n.º 33
0
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)
Ejemplo n.º 34
0
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) #保存
Ejemplo n.º 35
0
 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))
Ejemplo n.º 36
0
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)
Ejemplo n.º 37
0
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'):
Ejemplo n.º 38
0
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)
Ejemplo n.º 39
0
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]
Ejemplo n.º 41
0
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
Ejemplo n.º 42
0
Archivo: excel.py Proyecto: DT021/wau
    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)
Ejemplo n.º 43
0
def test_column_letter_boundries(value):
    with pytest.raises(ValueError):
        get_column_letter(value)
Ejemplo n.º 44
0
def test_column_letter(value, expected):
    assert get_column_letter(value) == expected
Ejemplo n.º 45
0
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)
Ejemplo n.º 46
0
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)
Ejemplo n.º 47
0
                           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'
]
Ejemplo n.º 48
0
 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())
Ejemplo n.º 49
0
    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)
Ejemplo n.º 50
0
    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)
Ejemplo n.º 51
0
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()
Ejemplo n.º 53
0
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)
Ejemplo n.º 54
0
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))
Ejemplo n.º 55
0
    },
    '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::
Ejemplo n.º 56
0
    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
Ejemplo n.º 57
0
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')
Ejemplo n.º 58
0
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)
Ejemplo n.º 59
0
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)
Ejemplo n.º 60
0
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)