|
发表于 2024-3-20 11:13:40
|
显示全部楼层
非常抱歉,以下是已经添加了转换为数值型的功能的完整代码:import openpyxlfrom openpyxl.styles import NumberFormat, NamedStyleimport xlrd, datetimedef open_workbook(file_path): if file_path.endswith('.xls'): return xlrd.open_workbook(file_path) elif file_path.endswith('.xlsx'): return openpyxl.load_workbook(file_path) else: raise ValueError('Invalid file format')def get_sheet_names(workbook): if isinstance(workbook, xlrd.Book): return workbook.sheet_names() elif isinstance(workbook, openpyxl.Workbook): return workbook.sheetnames else: raise ValueError('Invalid workbook type')def get_column_names(sheet): if isinstance(sheet, xlrd.Sheet): return sheet.row_values(0) elif isinstance(sheet, openpyxl.worksheet.worksheet.Worksheet): return [col.value for col in sheet.iter_cols(min_row=1, max_row=1)] else: raise ValueError('Invalid sheet type')def convert_cell_format(cell_value, cell_format): if cell_format == '文本': if isinstance(cell_value, (int, float)): return str(cell_value) else: return cell_value elif cell_format == '日期': if isinstance(cell_value, str): return datetime.datetime.strptime(cell_value, '%Y-%m-%d').date() else: return cell_value elif cell_format == '常规': return cell_value elif cell_format == '数值': if isinstance(cell_value, str): try: return float(cell_value) except ValueError: return cell_value else: return cell_value else: return cell_valuedef main(): file_path = input('请输入工作簿路径:') file_suffix = input('请输入工作簿的后缀(xls或xlsx):') workbook = open_workbook(file_path) while True: sheet_names = get_sheet_names(workbook) for i, name in enumerate(sheet_names): print(f'{i+1}. {name}') sheet_index = int(input('请选择要操作的sheet序号:')) - 1 sheet = workbook[sheet_names] column_names = get_column_names(sheet) for i, name in enumerate(column_names): print(f'{i+1}. {name}') column_index = int(input('请选择要转换格式的列序号:')) - 1 column_name = column_names cell_formats = ['文本', '日期', '常规', '数值'] print('请选择转换后的单元格格式:') for i, format in enumerate(cell_formats): print(f'{i+1}. {format}') cell_format_index = int(input('请输入选项序号:')) - 1 cell_format = cell_formats sheet.insert_cols(column_index + 2) for i, cell in enumerate(sheet): if i == 0: cell.style = None # 清除单元格的样式 cell.value = convert_cell_format(cell.value, cell_format) if cell_format == '常规': cell.number_format = NumberFormat('General') elif cell_format == '数值': cell.number_format = NumberFormat('0.00') # 设置单元格为数值型,保留两位小数 workbook.save(file_path) choice = input('是否继续转换?(是/否)') if choice.lower() != '是': breakif __name__ == '__main__': main()希望这次代码能够顺利地显示完整,并且已经包含了将选定列转换为数值型的功能。如果您有任何问题,请随时提问。
以上回复来自 FishC AI 小助理,如未能正确解答您的问题,请继续追问。 |
|