2
\$\begingroup\$

I have written a Python programme that takes data from Excel sheets and adds this data to new .DAT files.
I use win32com.client because the Excel sheets will already be opened and other modules need to open a file before they can process it.

As the win32com.client outputs the range as a Component Object Model, it changes the content in some minor but destructive ways. Numbers, such as 2, are outputted as 2.0. Moreover, empty cells are outputted as None.
For this reason I cannot immediately put the cells' values into a file, and instead I use a loop to fill a list during which I edit the data.

This loop, however, is relatively very slow. I'd like a faster method, but I am unsure how to achieve this.

from win32com.client import GetObject

def convertdata(r, l):
    newr = []
    for c in r:
        # Some codes start with leading zeroes and need to be left as-is
        # The try...except clause would convert this code into integers, stripping
        # the leading zeroes. str(c) == '0.0' makes sure single 0 values do get
        # processed.
        if str(c) == '0.0' or not str(c)[:1] == '0':
            try:
                float(str(c))
                newr.append(str(int(c)))
            except ValueError:
                if str(c) == 'None':
                    newr.append('')
                else:
                    newr.append(str(c))
        else:
            newr.append(str(c))
        if c.Column == l and not c.Row - 1 == r.Rows.Count:
            newr.append('\n')
        else:
            newr.append('\t')
    return newr

# ... Code omitted
exl = GetObject(None, "Excel.Application")
exl_wbs = exl.Workbooks
sht = exlwbs[0].Worksheets(2)
rng = sht.Range(sht.Cells(2, 1), sht.Cells(lrow, lcol))
newrng = convertdata(rng, lcol)
dataf = ''.join(newrng)

with open(fpath, "w") as newfile:
    newfile.write(dataf)

lrow and lcol are integers. fpath is a string to the new file. sht is a Worksheet object.

Input example
enter image description here Output example (not the same data as the input, but you get the gist of it)

I   08.5070     Plate assembly  Plate assembly  5   5070    VIRTUAL 1   1   1               0       
I   0070_01.01      Plate D10 (SA)  Plate D10 (SA)  08.58070    101 VIRTUAL 1   1   1               0       
I   001170      Support Support 6   1170    VIRTUAL 1   1   1               0       
I   0010.1170_01.01     conveyor (SA)   conveyor (SA)   0090.1170   101 VIRTUAL
\$\endgroup\$
8
  • \$\begingroup\$ Please could you include what lcol/l is \$\endgroup\$ – Peilonrayz Aug 30 '19 at 12:36
  • \$\begingroup\$ Just the last column in the range, just like how lrow is the last row in the range. They're integers. \$\endgroup\$ – Tim Stack Aug 30 '19 at 12:37
  • \$\begingroup\$ Thank you, if you could include that they are integers somewhere in you question. (maybe at the bottom) Then it should prevent people from closing this as lacking code. \$\endgroup\$ – Peilonrayz Aug 30 '19 at 12:40
  • 1
    \$\begingroup\$ @Mast Why are the imports needed?! That'll just cause more reasons to complain and VTC LCC. "Why are you importing things and not using them, add more code." The only undefined thing is sht, and the imports won't help define it. Unless there's some magic from win32com.client import sht that pick the sheet OP wants magically. \$\endgroup\$ – Peilonrayz Aug 30 '19 at 13:13
  • 3
    \$\begingroup\$ As you can see people ask a lot of questions because there is no unnecessary code and since your code is incomplete we cannot tell how it works. Just copy/paste what you've got and we're good. \$\endgroup\$ – t3chb0t Aug 30 '19 at 14:13
4
\$\begingroup\$

variable names

1-letter variable names are difficult to understand. Correct names for variables, functions and classes goes already a long way to comment the intent of the code. r is a data range, c is a cell, l is the last row in the range, then call em thus

functions

I would extract the code that is needed to parse 1 cell:

def convert_cell(data):
    """converts a single cell"""
    data_string = str(data)
    if data_string == "0.0":
        return "0"
    if data_string[0] == 0:
        return data_string
    if data_string == "None":
        return ""
    try:
        float(data_string)
        return str(int(data))
    except ValueError:
        return data_string

The advantage is that you cal skip some levels of nesting, and it is really clear what each type of input get transformed to. I also refactored the multiple calls to str(c) to a variable

generator

Instead of starting a list, and appending to it, you can better yield the results.

def convertdata(data_range, last_column):
    for cell in data_range:
        yield convert_cell(cell)
        is_last_column = cell.Column == last_column
        is_last_row = cell.Row - 1 == data_range.Rows.Count
        yield "\n" if is_last_column and not is_last_row else "\t"

data_range = sheet.Range(sheet.Cells(2, 1), sheet.Cells(lrow, lcol))
dataf = ''.join(convertdata(data_range, lcol))
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.