CODEWITHSUNDEEP
pythonexcelopenpyxlxlwt
irishombian
irishombian

Reputation: 122

Changing xls to xlsx from xlwt with openpyxl

I have been using xlwt to automate reports. I have to add a COUNTIFS formulas which unfortunately does not work with xlwt in xls so I have to recreate the entire python script using openpyxl. The problem I am having is that the data being pulled is from an rest API and certain value's are identified with a preceding of "sensor." The below code show's what I am trying with open pyxl and the commented rows are what I was using in xlwt. If anyone can tell me how to make the openpyxl do what the xlwt was doing that would be awesome. 

book = Workbook("")
sheet = book.active

sheet["A1"] = "id"
#worksheet.write(0, column_number, 'id')

sheet['B1'] = "hostname"
#worksheet.write(0, column_number, 'hostname')

sheet['C1'] = "os"
#worksheet.write(0, column_number, 'os')

sheet['D1'] = "ip_address"
#worksheet.write(0, column_number, 'ip_address')

sheet['E1'] = "last_checkin_time"
#worksheet.write(0, column_number, 'last_checkin_time')

sheet['F1'] = "days_offline"
#worksheet.write(0, column_number, 'days_offline')

sheet['G1'] = "console"
#worksheet.write(0, column_number, 'console')

cb = CbResponseAPI()    
sensors = list(cb.select(Sensor))


row = 1

for sensor in sensors:

    #print sensor

    if sensor.uninstall == False and (sensor.uninstalled == False or sensor.uninstalled == None):

        last_checkin_time = sensor.last_checkin_time.strftime('%m/%d/%Y')
        p = datetime.now().strftime('%m/%d/%Y')


        d = datetime_object = datetime.strptime(last_checkin_time, '%m/%d/%Y')
        q = datetime_object = datetime.strptime(p, '%m/%d/%Y')

        delta = (q - d).days

        cell = sheet.cell(row=2, column=1)
        cell.value = "sensor.id"
        cell = sheet.cell(row=2, column=2)
        cell.value = "sensor.hostname"
        #worksheet.write(row, 0, sensor.id) 
        #worksheet.write(row, 1, sensor.computer_name) 

        if "Windo 7" in sensor.os_environment_display_string:

            cell = sheet.cell(row=2, column=3)
            cell.value = "sensor.os_environment_display_string"
            #sensor.os_environment_display_string = "Windo 7"
            #worksheet.write(row, 2, sensor.os_environment_display_string) 

        elif "Windo Server 2008" in sensor.os_environment_display_string:

            cell = sheet.cell(row=2, column=3)
            cell.value = "sensor.os_environment_display_string"
            #sensor.os_environment_display_string = "Windo Server 2008"
            #worksheet.write(row, 2, sensor.os_environment_display_string)

        elif "Windo Server 2012" in sensor.os_environment_display_string:

            cell = sheet.cell(row=2, column=3)
            cell.value = "sensor.os_environment_display_string"
            #sensor.os_environment_display_string = "Windo Server 2012"
            #worksheet.write(row, 2, sensor.os_environment_display_string) 

        elif "Windo XP" in sensor.os_environment_display_string:

            cell = sheet.cell(row=2, column=3)
            cell.value = "sensor.os_environment_display_string"
            #sensor.os_environment_display_string = "Windo XP"
            #worksheet.write(row, 2, sensor.os_environment_display_string) 

        elif "Mac OSX" in sensor.os_environment_display_string:

            cell = sheet.cell(row=2, column=3)
            cell.value = "sensor.os_environment_display_string"
            #sensor.os_environment_display_string = "Mac OSX"
            #worksheet.write(row, 2, sensor.os_environment_display_string) 

        elif "Windo Server 2003" in sensor.os_environment_display_string:

            cell = sheet.cell(row=2, column=3)
            cell.value = "sensor.os_environment_display_string"
            #sensor.os_environment_display_string = "Windo Server 2003"
            #worksheet.write(row, 2, sensor.os_environment_display_string) 

        elif "Windo 10" in sensor.os_environment_display_string:

            cell = sheet.cell(row=2, column=3)
            cell.value = "sensor.os_environment_display_string"
            #sensor.os_environment_display_string = "Windo 10"
            #worksheet.write(row, 2, sensor.os_environment_display_string) 

        elif "Windo 8" in sensor.os_environment_display_string:

            cell = sheet.cell(row=2, column=3)
            cell.value = "sensor.os_environment_display_string"
            #sensor.os_environment_display_string = "Windo 8"
            #worksheet.write(row, 2, sensor.os_environment_display_string) 

        elif "Windo Server 2016" in sensor.os_environment_display_string:

            cell = sheet.cell(row=2, column=3)
            cell.value = "sensor.os_environment_display_string"
            #sensor.os_environment_display_string = "Windo Server 2016"
            #worksheet.write(row, 2, sensor.os_environment_display_string) 

        else:
            cell = sheet.cell(row=2, column=3)
        cell.value = "sensor.os_environment_display_string"
        cell = sheet.cell(row=2, column=4)
        cell.value = "sensor.network_adapters"
        cell = sheet.cell(row=2, column=5)
        cell.value = "sensor.last_checkin_time"
        cell = sheet.cell(row=2, column=6)




        #worksheet.write(row, 2, sensor.os_environment_display_string) 
        #worksheet.write(row, 3, sensor.network_adapters) 
        # worksheet.write(row, 4, last_checkin_time)
        # worksheet.write(row, 5, delta)
        # worksheet.write(row, 6, "DELI") 

        row+=1

print("-> DELI-RESPONSE - Done exporting! <-")

book.save("sensor_export.xlsx")

Upvotes: 0

Views: 704

Answers (1)

Joel Smith
Joel Smith

Reputation: 11

Ok. I'm quite an amateur at this, but here are a couple of things I see.
1. In your for loop, the row += 1 is indented 4 spaces too far. It is inside the if loop, not the for loop. Or more specifically, it is inside the else loop at the bottom. 2. You have a variable "row". But in openpyxl, "row" is an attribute of cell. So it is a functional word. I'm not sure if you can make it work by using "row" as both a variable and as an attribute. I would change your variable to maybe "r". Then inside the if's and elif's, you can have: cell = sheet.cell(row=r, column=3).
As the code is right now, you are telling openpyxl that the row = 2 in every instance.

Good luck.

Upvotes: 1

Related Questions