Python写出Excel文件数字类型变科学计数法以及精度丢失尾数变成0修复笔记

最新推荐文章于 2024-07-22 03:37:14 发布

蛙鳜鸡鹳狸猿

最新推荐文章于 2024-07-22 03:37:14 发布

阅读量5.7k

点赞数

分类专栏： # Python 文章标签： Excel科学计数法 Excel数字精度丢失 Excel数字末尾变成0 Excel保留数字原始值 Python

本文链接：https://blog.csdn.net/sweeper_freedoman/article/details/105800137

版权

Python 专栏收录该内容

32 篇文章 1 订阅

订阅专栏

Python的int型写入Excel时会有两种潜在问题（参考https://support.office.com/en-us/article/display-numbers-in-scientific-exponential-notation-f85a96c0-18a1-4249-81c3-e934cd2aae25?ui=en-US&rs=en-US&ad=US）：

数字长度超过12个会自动转变成科学计数法（官方文档说是12个/本地测试结果为8个）；
数字长度超过15个会失去数字精度，即15位以后的数字位全部变为零。

但实际使用中从数据库中读出来的id类数据很容易触碰这两个潜在问题，为了保留原始数据值，用Python写出时，只需要像我们在Excel手动输入长数字之前将类型设置为“文本”一样，在脚本中也将int型str化。

以下示例脚本中的静态方法`correct_int_row`作为参考。

#!/usr/bin/env python
# -*- coding: utf-8 -*-


"""
author    : 蛙鳜鸡鹳狸猿
create_ts : 2020-04-06
program   : *_* excel file utility *_*
"""


import openpyxl


class Excel(object):
    def __init__(self, writer="data.xlsx", is_del_default=False, encoding="utf-8"):
        """
        Excel file init.
        :param writer: full path of Excel file to write out.
        :param is_del_default: whether or not to del default work sheet "Sheet" before saving workbook.
        :param encoding: encoding type.
        """
        self.writer = writer
        self.if_del_default = is_del_default
        self.encoding = encoding

        self.wkbook = openpyxl.Workbook()
        self.wkbook.encoding = self.encoding

    def __enter__(self):
        return self.wkbook

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.if_del_default:
            del self.wkbook["Sheet"]
        self.wkbook.save(self.writer)

    @staticmethod
    def get_sheet(wkbook, title=None, index=None):
        """
        Get an active sheet.
        :param title: work sheet title name.
        :param index: optional position at which the sheet will be inserted.
        """
        return wkbook.create_sheet(title, index) if title else wkbook.active

    @staticmethod
    def correct_int_row(row):
        """
        Correct int type of one line data(iterable) for Excel. See also
            https://support.office.com/en-us/article/display-numbers-in-scientific-exponential-notation-f85a96c0-18a1-4249-81c3-e934cd2aae25?ui=en-US&rs=en-US&ad=US
        :param row: a line of data(iterable) for Excel.
        """
        correct_row_list = []
        for _ in row:
            if isinstance(_, int) and len(str(_)) > 8:
                correct_row_list.append(str(_))
            else:
                correct_row_list.append(_)
        return correct_row_list


if __name__ == "__main__":
    header = ['name', 'rank', 'remark']
    record = [["Linux", 0, 12345678],
              ["MySQL", 1, 2238150616750203],
              ["MySQL", 1, "ありません"],
              ["Python", 2, "必須の箇所"],
              ["Python", 2, 123456789],
              ["Python", 2, 1234567890123]]
    with Excel("data.xlsx", True) as xlsx:
        wsheet = Excel.get_sheet(xlsx, title="test_data", index=0)
        wsheet.append(header)
        for _ in record:
            wsheet.append(Excel.correct_int_row(_))