Percentage and amount cannot be read-only value: carry the format along with it

This paper uses synthetic samples to demonstrate the processing method. The code has been verified using examples in an isolated environment; this does not guarantee effectiveness for all real files or products. Before processing important files, please retain the original copies and verify them against the boundaries and acceptance criteria outlined in this document. When automating Excel report processing or performing document translation, developers often fall into a subtle trap: directly reading cell.value and assumes this is what the user sees. However, when the cell displays as “15%” or “¥1,234.50”, its underlying stored value may be nothing more than 0.15 or 1234.5 . If ignored number_format , the downstream system will receive raw data lacking business semantics, leading to translation errors or computational biases. This paper takes openpyxl as an example to explain how to correctly synchronize the extraction of values and formatting, and to establish reliable acceptance criteria. The core mechanism of separating storage from presentation in Excel Excel’s design philosophy strictly separates data storage from visual presentation. The cell object contains two independent properties: value stores the original data types (float, int, datetime), while number_format is a string template used solely to guide client-side rendering. openpyxl, as the underlying read/write library, faithfully reflects this separation mechanism; it does not automatically convert 0.15 is converted to "15%" string is returned. This means that if you are performing data migration, ETL cleansing, or bilingual concordance translation, relying solely on value is equivalent to actively discarding key contextual elements such as “percentage,” “currency unit,” and “precision.” For example, even with the same numerical value 1234.5 , formatted as #,##0.00 represents the amount, formatted as 0.00% , on the other hand, represents a ratio. In translation scenarios, the former may require retaining the currency symbol and converting the thousands separator, while the latter necessitates verifying the target language’s convention for the percent sign. Grasping this mechanism is essential for developing robust parsing code. input and output boundaries for securely extracting the displayed value must establish a clear input-output contract in order to restore the human-readable display value. The input side includes cell.value (which may be None, a numeric value, a date, or text) and cell.number_format (which may be empty, 'General', or a complex custom string). The output should be a deterministic string, or, if processing is not possible, return the original value with an explicit marker. Key boundary conditions include: - Null Value Handling : When value When set to None, the output should be an empty string regardless of the format. - General format : When number_format is 'General' When the value is an empty string, it should be converted according to Python's default rules rather than raising an error. - type mismatch with : When the format requires a percentage but the value is text, it should not be forcibly converted; instead, an exception should be flagged. - unaccounted format : For cases involving conditional judgments (such as [Red] ), color codes, or formats for rare locale settings; the code should not make guesses but rather return [UNHANDLED:format_string] original_value for human intervention. This defensive programming strategy ensures the stability of automated workflows while explicitly exposing uncertainties to reviewers. Example of synthetic data validation based on openpyxl The following Python code demonstrates the complete logic for constructing samples in memory and extracting display values. Note: This code is for educational demonstration purposes only and has not been executed in a production environment. Please verify it yourself in an environment where openpyxl >= 3.1.0 is locked. Example deliberately avoids implementing a full-fledged Excel format parser, instead focusing on secure fallbacks for high-frequency scenarios. from openpyxl import Workbook def get_display_value(cell): """ 根据 number_format 安全生成显示字符串。 输入: openpyxl Cell 对象 输出: 格式化后的字符串，或带标记的原始值 """ val = cell.value fmt = cell.number_format or 'General' # 边界1: 空值直接返回 if val is None: return '' # 边界2: General 或空格式按默认处理 if fmt == 'General': return str(val) # 高频场景: 百分比 (假设存储值为小数) if fmt == '0.00%': try: return f'{float(val) * 100:.2f}%' except (ValueError, TypeError): return f'[TYPE_MISMATCH:{fmt}] {val}' # 高频场景: 人民币金额 if fmt.startswith('¥') and '#,##0.00' in fmt: try: return f'¥{float(val):,.2f}' except (ValueError, TypeError): return f'[TYPE_MISMATCH:{fmt}] {val}' # 兜底: 未覆盖格式，保留原始值并标记 return f'[UNHANDLED:{fmt}] {val}' # === 构造合成测试数据 === wb = Workbook() ws = wb.active # 准备测试用例 ws['A1'] = 0.15 ws['A1'].number_format = '0.00%' ws['B1'] = 1234.5 ws['B1'].number_format = '¥#,##0.00' ws['C1'] = 0.999 # C1 保持默认 General 格式 # === 验证预期输出 === assert get_display_value(ws['A1']) == '15.00%', "百分比格式化失败" assert get_display_value(ws['B1']) == '¥1,234.50', "货币格式化失败" assert get_display_value(ws['C1']) == '0.999', "General格式处理失败" print("所有断言通过，逻辑符合预期。") This example demonstrates how to encapsulate the format-mapping logic as a pure function, facilitating unit testing. For production environments, it is recommended to externalize the format whitelist into a configuration file and apply [UNHANDLED] tag establishes monitoring and alerting. Acceptance Criteria and Manual Review Boundary Table Automated scripts cannot cover all Excel format variants. The table below defines the code-level acceptance baseline and the scenarios that must be handed over for manual review. This table applies only to the validation of format-extraction logic and does not represent the final quality of end-to-end document conversion. Observation of the phenomenon automated decision-making Whether manual review is required number_format='0.00%' and value=0.15 output '15.00%' no number_format contains a currency symbol but value is the text retains the original text, marked as [TYPE_MISMATCH] is number_format is a custom Chinese format such as '元#,##0.00' returns [UNHANDLED] + original value is value is a datetime but number_format='General' outputs a string in ISO format no number_format is an empty string or None is regarded as 'General' processes no format includes conditional expressions such as [>100]"高";[<0]"负" returns [UNHANDLED] + original value is uses built-in style aliases such as 'Percent' rather than the specific format string returns [UNHANDLED] + original value is Special note: openpyxl has limited support for built-in styles (Named Styles), and localized names (such as “Percentage” in the Chinese version) may not be recognized. In such cases, always rely on manual verification of the source file’s actual display. References and Next Steps Working with styles - openpyxl documentation uses this checklist for document translation first determines which elements are text, which are formulas, and which are business codes, then proceeds with table translation. Post-translation verification covers not only whether the Chinese content has been correctly rendered into the target language, but also whether formulas, identifiers, and citation relationships have remained unchanged. OmniDoc (product entry point) provides an entry point for translating multi-format documents, and the official website highlights its OCR, terminology database, and bilingual alignment features. For teams that wish to start processing directly from files, this is a path worth evaluating with real-world samples. Please use de-identified files that you are authorized to upload, and verify the results against the checklist provided in this document; we do not claim that OmniDoc utilizes the open-source libraries or internal algorithms referenced herein, nor do we substitute examples for formal product performance testing. - 1 -