python xlrd and xlwt

Question

I have the following content in an old Excel sheet:

I need to generate a new Excel sheet with the following values:

In the input Excel file's 3rd column I have a range 10010-10040 and an increment value of 10. This needs to be expanded in my new Excel file.

If I give a comma (,) in between values, it should be treated as separate values and expanded. (Like in row2, column 3)

I have no idea how to do this and I am new to Python.

Answer 1

 xlrd: It is a python module used to retrieve or extract data from spreadsheets.

 xlwt: This python module helps in modifying and writing the data into the spreadsheets.

You can consider the below code to understand how to read and write into spreadsheets:

import xlrd

import xlwt

 

wb_in = xlrd.open_workbook(r'input.xls')

sheet_name = wb_in.sheet_names()[0]

ws_in = wb_in.sheet_by_name(sheet_name)

 

wb_out = xlwt.Workbook()

ws_out = wb_out.add_sheet(sheet_name)   # Use the same sheet name

 

row_out = 0

 

for row_in in range(ws_in.nrows):

    row = ws_in.row_values(row_in)

 

    if isinstance(row[2], float):

        req_spec = str(int(row[2]))

    else:

        req_spec = row[2]

 

    req_range = req_spec.split('-')

    req_enum = req_spec.split(',')

 

    if len(req_range) > 1:    # e.g. 10010-10040-10

        for value in range(int(str(req_range[0])), int(str(req_range[1])) + 1, int(str(req_range[2]))):

            ws_out.write(row_out, 0, row[0])

            ws_out.write(row_out, 1, row[1])

            ws_out.write(row_out, 2, str(value))

            row_out += 1

    elif len(req_enum) > 1:    # e.g. 1010,1020

        for value in req_enum:

            ws_out.write(row_out, 0, row[0])

            ws_out.write(row_out, 1, row[1])

            ws_out.write(row_out, 2, value)

            row_out += 1

    else:                      # e.g. 10100

        ws_out.write(row_out, 0, row[0])

        ws_out.write(row_out, 1, row[1])

        ws_out.write(row_out, 2, req_spec)

        row_out += 1

 

wb_out.save('output.xls')  

Curious about python, check out the amazing  Python certification by Intellipaat.