CODEWITHSUNDEEP
pythonexcelcsv
BrittleStiff
BrittleStiff

Reputation: 35

xlsx writing cell_value error, writing to new worksheet

I'm trying to build a report generator which reads excel sheets and returns rows which contain values. I built a version which works as I require but only works for csv this is only my 1st code-mash-together, but it worked. I now would like to include conditional formatting as well (highlight certain cells values eg. if <65 format red) and so that required that I rewrite with xlsx sheets rather than csv. Below is my attempt at getting this to work... I can find the values and return the row, but on the second run through it returns an error

AttributeError: 'Worksheet' object has no attribute 'cell_value'

Which is surprising because it worked just previously and stepping through the code retuns the values I want.... I have tried changing it to .value, but returns:

AttributeError: 'function' object has no attribute 'value'

Help, I have no idea what I'm doing now. If it doens't make any sense i'm happy to post my original code for the csv to 'explain'

Thanks

import xlsxwriter
import xlrd
import os
import xlwt

# open original excelbook and access first sheet
for excelDocs in os.listdir('.'):
    if not excelDocs.endswith('.xlsx'):
        continue    # skip non-xlsx files

    workbook = xlrd.open_workbook(excelDocs)
    sheet = workbook.sheet_by_index(0)
    cellslist = []
    i = 0
#########WORKS!#####################
    for row in range(sheet.nrows):
        for col in range(sheet.ncols):

            if sheet.cell_value(row, col) == 'CP' or sheet.cell_value(row, col) == 'LNA' or sheet.cell_value(row, col) == 'Last Name':
                i = i + 1
                data = [sheet.cell_value(0, col) for col in range(sheet.ncols)]
                workbook = xlsxwriter.Workbook()
                sheet = workbook.add_worksheet('excelDocs')

                for index, value in enumerate(data):
                    sheet.write(i, index, value)
                workbook = xlrd.open_workbook(excelDocs)

Upvotes: 0

Views: 2164

Answers (2)

Mark T
Mark T

Reputation: 105

I have no experience with xlsxwriter, xlrd or xlwt. As this is your "1st code-mash-together" I figured I would offer an alternative using openpyxl. I do not have your data, so testing is a little difficult, but any syntax errors could be fixed. Please let me know if this does not run and I will help fix if required.

I am assuming your output is to a seperate file(report.xlsx here) and a tab for each workbook checked(each tab named for source book name).

    import openpyxl
        from openpyxl import *
        from openpyxl.utils import get_column_letter

        interestingValues = ['CP','LNA', 'LastName']
        report = Workbook()
        dest_filename = 'report.xlsx'
        # open original excelbook and access first sheet
        for excelDocs in os.listdir('.'):
            if not excelDocs.endswith('.xlsx'):
                continue    # skip non-xlsx files

            workbook = load_workbook(excelDocs)
            sheet = workbook.active
            workingReportSheet = report.create_sheet(str(excelDocs.split('.')[0]))
            i = 0
            for row in range(1,sheet.max_row):
                for col in range(sheet.max_column):
                    columnLetter = get_column_letter(col +1)
                    if str(sheet['%s%s' % (columnLetter,row)].value) in interestingValues:
                        i += 1
                        data = [sheet['%s%s' % (str(get_column_letter(col)),i)].value for col in range(1,sheet.max_column +1)]
                        for index, value in enumerate(data):
                            workingReportSheet['%s%s' % (str(get_column_letter(index+1)),i)].value = value

        report.save(filename = dest_filename)

Upvotes: 1

Mark T
Mark T

Reputation: 105

Reading your code again, it may be that you are discarding your output. Try the below.

import xlsxwriter
import xlrd
import os
import xlwt

#Create output sheet
outputworkbook = xlsxwriter.Workbook()
# open original excelbook and access first sheet
for excelDocs in os.listdir('.'):
if not excelDocs.endswith('.xlsx'):
    continue    # skip non-xlsx files

workbook = xlrd.open_workbook(excelDocs)
sheet = workbook.sheet_by_index(0)
cellslist = []
i = 0
outputsheet = outputworkbook.add_worksheet('excelDocs')
for row in range(sheet.nrows):
    for col in range(sheet.ncols):

        if sheet.cell_value(row, col) == 'CP' or sheet.cell_value(row, col) == 'LNA' or sheet.cell_value(row, col) == 'Last Name':
            i = i + 1
            data = [sheet.cell_value(0, col) for col in range(sheet.ncols)]

            for index, value in enumerate(data):
                outputsheet.write(i, index, value)

Upvotes: 1

Related Questions