python 读写excel xlsx与xls

xlwt 对应xls
xlsxwriter 对应xlsx
def letter_to_number(self, letter_var):
letter_var = letter_var.strip().lower()
re = ‘’
for i in letter_var:
index = string.ascii_lowercase.index(i) + 1
if index > 9:
index = chr(65 + (index - 10))

        re += str(index)
        
    return re
        
def read_excel(self, filename, begin_cr=None, target_r=None, target_c=None, as_header=True):
    """read_excel('test.xlsx', 'A1', 6, 'AB')"""
    file_path = os.path.join(ROOT_DIR, filename)
    #file_path = filename
    with xlrd.open_workbook(file_path) as book:
        def cell_ctype(cell, workbook):
            if 0 == cell.ctype:
                return None
            elif 1 == cell.ctype:
                return cell.value
            elif 2 == cell.ctype:
                return int(cell.value) if str(cell.value).endswith('.0') else float(cell.value)
            elif 3 == cell.ctype:
                return datetime(*xlrd.xldate_as_tuple(cell.value,workbook.datemode)).strftime('%Y-%m-%d %H:%M:%S')
            elif 4 == cell.ctype:
                return True if cell.value else False
            
            return cell.value
        
        temp = {}
        for sheet_name in book.sheet_names():
            sheet = book.sheet_by_name(sheet_name)
            row_num = sheet.nrows
            col_num = sheet.ncols
            if col_num < 1 or row_num < 1:
                continue
            
            temp[sheet_name] = []
            temp_header = []
            header_r = 0
            header_c = 1
            if begin_cr and as_header:
                header_r = int(re.findall(r'\d+', begin_cr)[0])
                header_c = int(self.nbase_to_decimal(self.letter_to_number(re.findall(r'\D+', begin_cr)[0]), 26))
                temp_header = [cell_ctype(sheet.cell(header_r-1, i), book) for i in range(header_c-1, col_num)]
            elif begin_cr and not as_header:
                header_r = int(re.findall(r'\d+', begin_cr)[0]) - 1
                header_c = int(self.nbase_to_decimal(self.letter_to_number(re.findall(r'\D+', begin_cr)[0]), 26))
                
            temp_merged = [[(i, j) for i in range(m_cells[0], m_cells[1]) \
                            for j in range(m_cells[2], m_cells[3])] for m_cells in sheet.merged_cells]
            
            for i in range(header_r, row_num):
                if 0 != len(temp_header):
                    temp_row = {}
                    for j in range(header_c-1, col_num):
                        temp_row[temp_header[j-header_c+1]] = cell_ctype(sheet.cell(i, j), book)
                    
                    temp[sheet_name].append(temp_row)
                else:
                    temp_row = [cell_ctype(sheet.cell(i, j), book) for j in range(header_c-1, col_num)]
                    temp[sheet_name].append(temp_row)
              
            for m_cells in temp_merged:
                merded_value = None
                for m_cell in m_cells:
                    m_cell_r = m_cell[0]-1 if 0 != len(temp_header) else m_cell[0]
                    m_cell_c = temp_header[m_cell[1]] if 0 != len(temp_header) else m_cell[1]
                    if 0 == m_cells.index(m_cell):
                        merded_value = temp[sheet_name][m_cell_r][m_cell_c]
                    
                    temp[sheet_name][m_cell_r][m_cell_c] = merded_value
            
            if target_r and not target_c:
                return temp[sheet_name][target_r-1-header_r]
            
            if target_c and not target_r:
                target_c = self.nbase_to_decimal(self.letter_to_number(target_c), 26)
                m_c = temp_header[target_c-header_c] if 0 != len(temp_header) else target_c-header_c
                target_cels = [r[m_c] for r in temp[sheet_name]]
                if 0 != len(temp_header): 
                    target_cels.insert(0, m_c)
                    
                return target_cels
            
            if target_c and target_r:
                target_c = self.nbase_to_decimal(self.letter_to_number(target_c), 26)
                m_c = temp_header[target_c-header_c] if 0 != len(temp_header) else target_c-header_c
                return temp[sheet_name][target_r-1-header_r][m_c]
                
        return temp
    return None

def write_excel(self, filename, data={}, header=None, begin_cr=None, merged_cells=None):
    """filename = 'test.xls' or 'test.xlsx'
       data = {'sheet_name': [[],...]}
       header = {'sheet_name': ['a',...]}
       begin_cr = {'sheet_name': 'A1'}
       merged_cells = {'sheet_name': ['A1:B2',...]} #row 1-2 col A-B merged"""
    file_path = os.path.join(ROOT_DIR, filename)
    #file_path = filename
    xls_m = True if file_path.endswith('.xls') else False
    try:
        def set_xlwt_style(content='pattern: pattern solid, fore_colour gray_ega; font: bold on; alignment: horz center;'):
            return xlwt.easyxf(content)
        
        def set_xlsxwriter_style(workbook, content={'align': 'center','bold': 1,'bg_color':'gray'}):
            return workbook.add_format(content)
        
        book = xlwt.Workbook() if xls_m else xlsxwriter.Workbook(file_path)
        for sheet_name in data:
            sheet = book.add_sheet(sheet_name, cell_overwrite_ok=True) if xls_m else \
                    book.add_worksheet(sheet_name)
                    
            header_r = 0
            header_c = 0
            if begin_cr and begin_cr.has_key(sheet_name):
                header_r = int(re.findall(r'\d+', begin_cr[sheet_name])[0]) -1
                header_c = int(self.nbase_to_decimal(self.letter_to_number(re.findall(r'\D+', begin_cr[sheet_name])[0]), 26)) - 1
                
            begin_r = 0
            if header and header.has_key(sheet_name):
                begin_r += 1
                set_style = set_xlwt_style() if xls_m else set_xlsxwriter_style(book)
                [sheet.write(header_r, j+header_c, header[sheet_name][j], set_style) for j in range(len(header[sheet_name]))]
                
            for i in range(len(data[sheet_name])):
                temp = data[sheet_name][i]
                [sheet.write(i+begin_r+header_r, j+header_c, temp[j]) for j in range(len(temp))]
            
            if merged_cells and merged_cells.has_key(sheet_name):
                m_cells = merged_cells[sheet_name]
                for mcells in m_cells:
                    mcells = mcells.split(':')
                    cell_r1 = int(re.findall(r'\d+', mcells[0])[0]) - 1
                    cell_c1 = int(self.nbase_to_decimal(self.letter_to_number(re.findall(r'\D+', mcells[0])[0]), 26)) - 1
                    cell_r2 = int(re.findall(r'\d+', mcells[1])[0]) - 1
                    cell_c2 = int(self.nbase_to_decimal(self.letter_to_number(re.findall(r'\D+', mcells[1])[0]), 26)) - 1
                    
                    cell_value = data[sheet_name][cell_r1-begin_r-header_r][cell_c1-header_c]
                    set_style = xlwt.Style.default_style if xls_m else None
                    if cell_r1-begin_r-header_r < 0:
                        cell_value = header[sheet_name][cell_c1-header_c]
                        set_style = set_xlwt_style() if xls_m else set_xlsxwriter_style(book)
                        
                    if xls_m:
                        sheet.write_merge(cell_r1, cell_r2, cell_c1, cell_c2, cell_value, set_style)
                    else:
                        sheet.merge_range(mcells[0] + ':' + mcells[1], cell_value, set_style)
               
        if 0 == len(data):
            sheet = book.add_sheet('sheet', cell_overwrite_ok=True)
            
        if xls_m:
            book.save(file_path)
        else:
            book.close()
            
        return True
    except Exception, e:
        pass
    
    return False