Python Xlrd and Xlwt
<p>I have the following content in an old Excel sheet:</p>

<p><a href="https://i.stack.imgur.com/64eFb.png" rel="nofollow"><img src="https://i.stack.imgur.com/64eFb.png" alt="excel sheet screenshot"></a></p>

<p>I need to generate a new Excel sheet with the following values:</p>

<p><a href="https://i.stack.imgur.com/17UIw.png" rel="nofollow"><img src="https://i.stack.imgur.com/17UIw.png" alt="excel sheet updated"></a></p>

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

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

<p>I have no idea how to do this and I am new to Python.</p>

<p>Try the following. This uses the <code>xlrd</code> and <code>xlwt</code> libraries to read and write <code>xls</code> spreadsheets:</p>

<pre><code>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) &gt; 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) &gt; 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')  
</code></pre>

<p>Unfortunately, if you not familiar with Python there is quite a lot to take in. </p>

<p>The script works by creating an input workbook and an output workbook. For each row in the input, it assumes you will always have 3 columns and that the third one contains one of your three types of specifies. It decides which is in use based on whether or not there is a <code>-</code> or a <code>,</code> present. It then writes out rows to the output based on this range.</p>

<p>Note, when reading the file in, <code>xlrd</code> attempts to guess the format of the cell. For most of your entries, it guesses a string format, but sometimes it wrongly guesses a floating point number. The script tests for this and converts it to a string for consistency. Also <code>xlrd</code> uses unicode <code>u"xxx"</code> format to store the strings. These need to be converted to numbers to be able to calculate the required ranges.</p>

python xlrd xlwt