CODEWITHSUNDEEP
pythonsearchxlrd
qode
qode

Reputation: 33

python xlwt - search for a specific value

I made a script using xlrd to extract multiple data from multiple cells in multiple excel files, and used xlwt to write these data to a new excel file. In the new excel file I added two additional rows with Formulas that will compute the average and the ttest. Now I'm trying to add a script that will search through the ttest line and all the values that are under 0.05 to be colored in red. On stackoverflow I found some help but I still receive an error. (For the color, I'm using this source: https://pypi.python.org/pypi/xlwt)
Could you please help me ?
Thanks !

    from xlwt import *
    style = xlwt.easyxf('font: colour red, bold on')
    wb=xlwt.Workbook()
    wbs=wb.add_sheet("sheet_to_write")
    w=xlrd.open_workbook("file_to_read.xlsx")
    ws=w.sheet_by_name("sheet_to_read")
    c=ws.cell(2,6).value
    wbs.write(46,1,c)
    ... #same as the last two lines, extracting different cells from the sheet_to_red and writing them in the sheet_to_write
    wbs.write(61,1,Formula("TTEST($B3:$B18, $B19:$B57, 2, 2)"))

Old code:

    for p in range(61):
        p.append(str(sheet.cell(row,col).value))
        if p.value < 0.05:
           cell.color =='22'

code 2:

    for row in range(61):
      for col in range(wbs.nrows):
        cell=ws.cell(row,col)
            try:
                if float(cell.value) < 0.05:
                  cell.color =='22'
            except ValueError: pass

AttributeError: 'Cell' object has no attribute 'color'

code 3:

    for row in range(61):
      for col in range(wbs.nrows):
        search=wbs.cell(row,col)
            try:
                if float(search.value) < 0.05:
                  wbs.write(row, col, search.value, style)
            except ValueError: pass
    ERROR:
    AttributeError: 'Worksheet' object has no attribute 'cell',

My Conclusion: this method won't work, because xlwt has no attribute cell, or nrows, these attibutes are specific for xlrd. Hence, the only method that would work is to create another file that will use xlrd, search for the specific value, and write it to a new file. Thanks Pyrce and tmrlvi for your help !

Upvotes: 3

Views: 3422

Answers (2)

Bharathiraja
Bharathiraja

Reputation: 812

xlwt supports only xls format and below are snippet for writing particular cell value

import xlwt
import xlrd
from xlutils.copy import copy

def writedata():
    workbook = xlrd.open_workbook("test.xls", "rb")
    wb = copy(workbook)
    w_sheet = wb.get_sheet(0)
    w_sheet.write(2, 4, 'Updated Value') # row, column, value
    wb.save("test.xls")

writedata()

Install below package if any error

pip install xlwt
pip install xlrd
pip install xlutils

Upvotes: 0

Pyrce
Pyrce

Reputation: 8571

You're trying to append a string to an integer when you just want an assignment. I'm guessing you meant to do something like this:

# Generate a color style for writing back into xlwt
xlwt.add_palette_colour("my_color", 0x22)
style = xlwt.easyxf('font: colour my_color;')

for row in range(61):
    cell = input_sheet.cell(row, col)
    try:
       if float(cell.value) < 0.05:
          output_sheet.write(row, col, cell.value, style)
    except ValueError: pass

Also as you can see the color assignment is a little different in xlwt than you might expect. You may also need to iterate over all cells and copy them over to the output sheet, or share the same sheet that was read to make this do exactly what you want.

Upvotes: 3

Related Questions