from openpyxl import Workbook
from openpyxl import load_workbook

inwb = load_workbook(filename)

outwb = Workbook()

careerSheet = outwb.create_sheet(0, 'career')

for sheetName in inwb.get_sheet_names():
  if not sheetName.isdigit():
    continue
  sheet = inwb[sheetName]

if len(sheet.columns) < 2:
  continue

colA, colB = sheet.columns[:2]

cells = sheet['A1':'B20']

alen = len(colA)
for i in range(1, alen + 1):
  sheet.cell('C%s' % (i)).value = None

sheet.append(strA, dateB, numC)

outwb.save("test.xlsx")

for idx, cell in enumerate(colA):
  # do something...

if not cell.value
  continue

if isinstance(cell.value, unicode):
  break

# annoying seperators
dot = u'\u00b7'
dash = u'\u2014'
emph = u'\u2022'
dot2 = u'\u2027'

seps = (u'.', dot, dash, emph, dot2)

def get_clean_ch_string(chstring):
  """Remove annoying seperators from the Chinese string.

  Usage:
    cleanstring = get_clean_ch_string(chstring)
  """
  cleanstring = chstring
  for sep in seps:
    cleanstring = cleanstring.replace(sep, u' ')
  return cleanstring

# regex pattern matching all ascii characters
asciiPattern = ur'[%s]+' % ''.join(chr(i) for i in range(32, 127))
# regex pattern matching all common Chinese characters and seporators
chinesePattern = ur'[\u4e00-\u9fff. %s]+' % (''.join(seps))

def split_name(name):
  """Split [English name, Chinese name].

    If one of them is missing, None will be returned instead.
  Usage:
    engName, chName = split_name(name)
  """
  matches = re.match('(%s) (%s)' % (asciiPattern, chinesePattern), name)
  if matches: # English name + Chinese name
    return matches.group(1).strip(), matches.group(2).strip()
  else:
    matches = re.findall('(%s)' % (chinesePattern), name)
    matches = ''.join(matches).strip()
    if matches: # Chinese name only
      return None, matches
    else: # English name only
      matches = re.findall('(%s)' % (asciiPattern), name)
      return ''.join(matches).strip(), None

def split_ch_name(chName):
  """Split the Chinese name into first name and last name.

    * If the name is XY or XYZ, X will be returned as the last name.
    * If the name is WXYZ, WX will be returned as the last name.
    * If the name is ...WXYZ, the whole name will be returned
     as the last name.
    * If the name is ..ABC * XYZ..., the part before the seperator
     will be returned as the last name.
  Usage:
    chFirstName, chLastName = split_ch_name(chName)
  """
  if len(chName) < 4: # XY or XYZ
    chLastName = chName[0]
    chFirstName = chName[1:]
  elif len(chName) == 4: # WXYZ
    chLastName = chName[:2]
    chFirstName = chName[2:]
  else: # longer
    cleanName = get_clean_ch_string(chName)
    nameParts = cleanName.split()
    print u' '.join(nameParts)
    if len(nameParts) < 2: # ...WXYZ
      return None, nameParts[0]
    chLastName, chFirstName = nameParts[:2] # ..ABC * XYZ...
  return chFirstName, chLastName