C# 如何在Excel中显示长数字？

How to show long numbers in Excel?

提问by Gold

I have to build a C# program that makes CSV files and puts long numbers (as string in my program). The problem is, when I open this CSV file in Excel the numbers appear like this:

我必须构建一个 C# 程序来制作 CSV 文件并输入长数字（作为我程序中的字符串）。问题是，当我在 Excel 中打开这个 CSV 文件时，数字显示如下：

1234E+ or 1234560000000 (the end of the number is 0)

1234E+ 或 1234560000000（数字末尾为0）

How I retain the formatting of the numbers? If I open the file as a text file, the numbers are formatted correctly.

我如何保留数字的格式？如果我将文件作为文本文件打开，则数字格式正确。

Thanks in advance.

提前致谢。

采纳答案by VVS

Format those long numbers as strings by putting a ' (apostrophe) in front or making a formula out of it: ="1234567890123"

将这些长数字格式化为字符串，方法是在前面放置一个 '（撇号）或从中生成一个公式：="1234567890123"

回答by Robert Harvey

You can't. Excel stores numbers with fifteen digits of precision. If you don't mind not having the ability to perform calculations on the numbers from within Excel, you can store them as Text, and all of the digits will display.

你不能。Excel 存储具有 15 位精度的数字。如果您不介意无法在 Excel 中对数字执行计算，您可以将它们存储为文本，并且所有数字都将显示。

回答by Sinan ünür

When I generate data to imported into Excel, I do not generate a CSV file if I want control over how the data are displayed. Instead, I write out an Excel file where the properties of the cells are set appropriately. I do not know if there is a library out there that would do that for you in C# without requiring Excel to be installed on the machine generating the files, but it is something to look into.

当我生成要导入 Excel 的数据时，如果我想控制数据的显示方式，我不会生成 CSV 文件。相反，我写出一个 Excel 文件，其中适当地设置了单元格的属性。我不知道是否有一个库可以在 C# 中为您执行此操作，而无需在生成文件的机器上安装 Excel，但它值得研究。

回答by John Y

As others have mentioned, you can force the data to be a string. The best way for that was ="1234567890123". The = makes the cell a formula, and the quotation marks make the enclosed value an Excel string literal. This will display all the digits, even beyond Excel's numeric precision limit, but the cell (generally) won't be able to be used directly in numeric calculations.

正如其他人提到的，您可以强制数据为字符串。最好的方法是 ="1234567890123"。= 使单元格成为公式，引号使括起来的值成为 Excel 字符串文字。这将显示所有数字，甚至超出 Excel 的数字精度限制，但单元格（通常）将无法直接用于数字计算。

If you need the data to remain numeric, the best way is probably to create a native Excel file (.xls or .xlsx). Various approaches for that can be found in the solutions to this related Stack Overflow question.

如果您需要数据保持数字，最好的方法可能是创建本机 Excel 文件（.xls 或 .xlsx）。在此相关堆栈溢出问题的解决方案中可以找到各种方法。

If you don't mind having thousands separators, there is one other trick you can use, which is to make your C# program insert the thousands separators and surround the value in quotes: "1,234,567,890,123". Do not include a leading = (as that will force it to be a string). Note that in this case, the quotation marks are for protecting the commas in the CSV, not for specifying an Excel string literal.

如果您不介意使用千位分隔符，则可以使用另一种技巧，即让您的 C# 程序插入千位分隔符并将值括在引号中：“1,234,567,890,123”。不要包含前导 =（因为这会强制它成为字符串）。请注意，在这种情况下，引号用于保护 CSV 中的逗号，而不是用于指定 Excel 字符串文字。

回答by Oorang

    My two cents:
    I think it's important to realize there is a difference between "Data" and "Formatting". In this example you are kind of trying to store both in a data-only file. This will, as you can tell from other answers, change the nature of the data. (In other words cause it to be converted to a string. A CSV file is a data only file. You can do some tricks here and there to merge formatting in with data, but to my way of thinking this essentially corrupts the data by merging it with non-data values: ie: "Formatting".
    If you really need to be able to store formatting information I suggest that, if you have time to develop it out, you switch to a file type capable of storing formatting info separately from the data. It sounds like this problem would be a good candidate for a XML Spreadsheet solution. In this way you can not only specify your data, but also it's type and any formatting you choose to use.

    我的两分钱：
    我认为重要的是要意识到“数据”和“格式”之间的区别。在这个例子中，你试图将两者都存储在一个纯数据文件中。正如您从其他答案中可以看出的那样，这将改变数据的性质。（换句话说，将其转换为字符串。CSV 文件是一个纯数据文件。您可以在这里和那里做一些技巧来将格式与数据合并，但在我看来，这本质上是通过合并来破坏数据它具有非数据值：即：“格式”。
    如果您真的需要能够存储格式信息，我建议，如果您有时间开发它，您可以切换到能够将格式信息与数据分开存储的文件类型。听起来这个问题很适合 XML 电子表格解决方案。通过这种方式，您不仅可以指定您的数据，还可以指定它的类型和您选择使用的任何格式。