关于Out of range value adjusted for column''

本文详细介绍了在MySQL新版本中遇到字段严格检查问题时,如何通过修改my.ini配置文件或设置sql_mode来解决该问题。推荐使用修改my.ini的方法,因为它操作简单且不会影响后续编程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

原因:
新版本的MySQL对字段的严格检查。

解决方法(两种方法任选一种即可):
1、修改my.ini,将sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
改为sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"。

也就是去掉STRICT_TRANS_TABLES 这个东东就好了,然后重新启动MySQL。
推荐这个方法。下面个方法比较麻烦,会给以后的编程带来麻烦。。。不推荐,但是还是在这里说一下。
2、在执行sql语句前,先执行以下语句:
mysql_query("set sql_mode=''"或"set sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'"(与第一种一样));
def format_attendance_report(output_path, exclude_columns=["匹配状态"]): """ 优化考勤报表格式的专用函数 参数: output_path - 需要格式化的文件路径 exclude_columns - 需要排除格式化的列(默认包含"匹配状态") """ try: wb = load_workbook(output_path) # 定义通用样式 header_font = Font(bold=True, color="FFFFFF") header_fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid") body_font = Font(name="微软雅黑", size=10) thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) for sheet_name in wb.sheetnames: ws = wb[sheet_name] # 设置列宽自适应 for col in ws.columns: max_length = 0 column = col[0].column for cell in col: if cell.value and cell.column not in exclude_columns: try: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = (max_length + 2) * 1.2 ws.column_dimensions[get_column_letter(column)].width = min(adjusted_width, 30) # 设置表头样式 for cell in ws[1]: if cell.column not in exclude_columns: cell.font = header_font cell.fill = header_fill cell.alignment = Alignment(horizontal="center") # 设置正文样式 for row in ws.iter_rows(min_row=2): for cell in row: if cell.column not in exclude_columns: cell.font = body_font cell.border = thin_border if isinstance(cell.value, (int, float)): cell.alignment = Alignment(horizontal="right") else: cell.alignment = Alignment(horizontal="left") # 设置冻结窗格 ws.freeze_panes = "A2" # 设置时间列格式 time_columns = [col for col in ws[1] if "时间" in str(col.value)] for col in time_columns: column_letter = get_column_letter(col.column) for cell in ws[column_letter][1:]: cell.number_format = "yyyy-mm-dd hh:mm:ss" wb.save(output_path) return True except Exception as e: print(f"格式化失败:{str(e)}") return False请对这个excel处理函数进行优化,要求,1,所有单元格字体设置为微软雅黑。2.首行字体大小16,加粗,白色,其余为黑色12。3.首行行高22,其余行行高18。4.开启首行筛选,并冻结首行。5.A~I列,列宽12,J列列宽99。6.A~I列,高度长度居中,J列高度居中,长度靠左
03-10
import os from docx import Document from openpyxl import Workbook from openpyxl.styles import Alignment from openpyxl.utils import get_column_letter def get_merged_regions(table): """获取表格所有合并区域信息""" merged = [] for merge in table._tbl.xpath('.//w:gridSpan | .//w:vMerge'): cell = merge.getparent().getparent() row_idx = int(cell.xpath('count(ancestor::w:tr/preceding-sibling::w:tr)')) col_idx = int(cell.xpath('count(preceding-sibling::w:tc)')) # 处理水平合并 if 'gridSpan' in merge.tag: colspan = int(merge.get('{http://schemas.openxmlformats.org/wordprocessingml/2006/main}val', 1)) merged.append(('h', row_idx, col_idx, colspan)) # 处理垂直合并 elif 'vMerge' in merge.tag: if merge.get('{http://schemas.openxmlformats.org/wordprocessingml/2006/main}val') != 'continue': rowspan = 1 next_row = row_idx + 1 while next_row < len(table.rows): next_cell = table.cell(next_row, col_idx)._tc if next_cell.xpath('.//w:vMerge'): v_merge_val = next_cell.xpath('.//w:vMerge')[0].get( '{http://schemas.openxmlformats.org/wordprocessingml/2006/main}val') if v_merge_val == 'continue': rowspan += 1 next_row += 1 else: break else: break merged.append(('v', row_idx, col_idx, rowspan)) return merged def get_table_data_range(table, start_text, end_text): """获取表格中从start_text到end_text之间的数据范围""" start_row = None end_row = None for row_idx, row in enumerate(table.rows): for cell in row.cells: if start_text in cell.text: start_row = row_idx if end_text in cell.text: end_row = row_idx if start_row is not None and end_row is not None: break return start_row, end_row def convert_table_to_excel(table, ws, start_row=None, end_row=None): """将Word表格转换为Excel工作表(保留合并和样式)""" # 确定处理的行范围 if start_row is None: start_row = 0 if end_row is None: end_row = len(table.rows) # 获取合并区域信息 merged_regions = get_merged_regions(table) # 创建数据矩阵,处理合并单元格的值 data_matrix = [] last_values = {} # 跟踪每列的最后一个非空值,处理合并单元格 for row_idx, row in enumerate(table.rows[start_row:end_row]): original_row_idx = start_row + row_idx row_data = [] for col_idx, cell in enumerate(row.cells): # 检查当前单元格是否属于垂直合并区域 is_in_merged = False for merge_type, m_row, m_col, m_span in merged_regions: if merge_type == 'v' and m_col == col_idx and m_row <= original_row_idx < m_row + m_span: is_in_merged = True # 如果是合并区域的起始行,使用当前单元格的值 if original_row_idx == m_row: value = cell.text.strip() last_values[col_idx] = value # 否则使用该列的最后一个非空值 else: value = last_values.get(col_idx, "") break # 如果不是合并区域,直接使用当前单元格的值 if not is_in_merged: value = cell.text.strip() last_values[col_idx] = value row_data.append(value) data_matrix.append(row_data) # 调整合并区域的行索引以匹配数据矩阵 adjusted_merged_regions = [] for merge_type, row, col, span in merged_regions: if start_row <= row < end_row: new_row = row - start_row adjusted_merged_regions.append((merge_type, new_row, col, span)) # 写入数据并合并单元格 for row_idx, row_data in enumerate(data_matrix): for col_idx, value in enumerate(row_data): cell = ws.cell(row=row_idx + 1, column=col_idx + 1, value=value) cell.alignment = Alignment(horizontal='center', vertical='center') # 应用合并区域 for merge_type, row, col, span in adjusted_merged_regions: if merge_type == 'h': start_col = col + 1 end_col = start_col + span - 1 ws.merge_cells(start_row=row + 1, start_column=start_col, end_row=row + 1, end_column=end_col) elif merge_type == 'v': start_row = row + 1 end_row = start_row + span - 1 ws.merge_cells(start_row=start_row, start_column=col + 1, end_row=end_row, end_column=col + 1) # 调整列宽(智能适应内容) for col_idx, _ in enumerate(data_matrix[0]): max_length = 0 for row in data_matrix: try: cell_value = row[col_idx] except IndexError: continue if cell_value and len(cell_value) > max_length: max_length = len(cell_value) adjusted_width = (max_length + 2) * 1.2 ws.column_dimensions[get_column_letter(col_idx + 1)].width = adjusted_width # 使用示例 path = './岗位说明书' os.makedirs("EXCEL", exist_ok=True) for file in os.listdir(path): if not file.endswith(('.docx', '.doc')): continue file_path = os.path.join(path, file) try: doc = Document(file_path) except Exception as e: print(f"无法处理文件 {file}: {e}") continue wb = Workbook() wb.remove(wb.active) # 查找目标表格 target_table = None start_row = None end_row = None for table in doc.tables: start, end = get_table_data_range(table, "主要职责及评价标准:", "岗位法律及规范风险点:") if start is not None: target_table = table start_row = start end_row = end break if target_table and start_row is not None: ws = wb.create_sheet("岗位职责") # 如果找到了结束标记,处理到结束标记前一行 if end_row is not None: convert_table_to_excel(target_table, ws, start_row, end_row) else: # 如果没有找到结束标记,处理从开始标记到表格末尾 convert_table_to_excel(target_table, ws, start_row) excel_name = os.path.splitext(file)[0] + "_转换结果.xlsx" wb.save(os.path.join("EXCEL", excel_name)) print(f"已成功转换: {excel_name}") else: print(f"未找到'主要职责及评价标准'表格或'岗位法律及规范风险点'标记: {file}") "工作职责及目的(描述该岗位主要活动及要达到的结果,每一应负责任请依其重要性排列)" 列中序号需要保留在内容中,“重要性”和"工作领域列"中如果为内容重复列就合并
05-14
import win32com.client import time import os from openpyxl import Workbook from openpyxl.utils import get_column_letter def read_and_sort_dwg_text(dwg_path, tolerance=0.001): try: acad = win32com.client.Dispatch("ZWCAD.Application") except Exception as e: print(f"无法连接到中望CAD: {e}") return try: doc = acad.Documents.Open(dwg_path) time.sleep(3) # 等待文件加载 model_space = doc.ModelSpace text_data = [] total_entities = model_space.Count print(f"开始处理 {total_entities} 个实体...") for i in range(total_entities): if i % 100 == 0: print(f"进度: {i}/{total_entities}") try: entity = model_space.Item(i) if entity.EntityName in ['AcDbText', 'AcDbMText']: content = entity.TextString position = entity.InsertionPoint or entity.TextAlignmentPoint text_data.append({ "content": content, "position": (float(position[0]), float(position[1]), float(position[2])) }) except Exception as e: print(f"处理实体 {i} 时出错: {str(e)}") continue doc.Close(False) print("CAD文档处理完成") # 使用容差分组 y_values = [pos[1] for item in text_data for pos in [item["position"]]] normalized_y = {y: round(y / tolerance) * tolerance for y in y_values} grouped = {} for item in text_data: y = normalized_y[item["position"][1]] grouped.setdefault(y, []).append(item) # 分组内按X排序 sorted_groups = {} for y, items in grouped.items(): sorted_groups[y] = sorted(items, key=lambda x: x["position"][0]) # 按Y坐标排序 sorted_y_coords = sorted(sorted_groups.keys(), reverse=True) # 从上到下 # 创建Excel dwg_dir = os.path.dirname(dwg_path) output_file = os.path.join(dwg_dir, "sorted_text_output.xlsx") wb = Workbook() ws = wb.active ws.title = "Sorted Text Data" # 表头 ws.append(["Y坐标", "内容1", "内容2", "内容3", "内容4", "内容5"]) # 填充数据 for y in sorted_y_coords: contents = [item["content"] for item in sorted_groups[y]] ws.append([y] + contents) # 自动调整列宽 for col in ws.columns: max_length = 0 column = col[0].column_letter for cell in col: try: if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = (max_length + 2) * 1.2 ws.column_dimensions[column].width = adjusted_width wb.save(output_file) print(f"文件已保存: {output_file}") return output_file except Exception as e: print(f"处理过程中发生错误: {str(e)}") if 'doc' in locals(): doc.Close(False) return None # 使用示例 if __name__ == "__main__": dwg_file = r"G:\Project\111.dwg" result = read_and_sort_dwg_text(dwg_file) if result: print("处理成功!") else: print("处理失败") 上面代码,需要先将y坐标升序排序,若y坐标之差不大于0.6,则将y坐标大的点的y坐标等于y坐标小的点的y坐标,之后将y坐标相同的点对x坐标进行升序排序
最新发布
06-24
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值