使用c#导入excel文档时，如何防止前导零被剥离
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you prevent leading zeros from being stripped when importing an excel doc using c#
提问by TheEmirOfGroofunkistan
I'm able to connect to and read an excel file no problem. But when importing data such as zipcodes that have leading zeros, how do you prevent excel from guessing the datatype and in the process stripping out leading zeros?
我能够连接到并读取一个 excel 文件没问题。但是，在导入具有前导零的邮政编码等数据时，如何防止 excel 猜测数据类型并在此过程中去除前导零？
采纳答案by palehorse
I believe you have to set the option in your connect string to force textual import rather than auto-detecting it.
我相信您必须在连接字符串中设置选项以强制文本导入而不是自动检测它。
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=c:\path\to\myfile.xlsx;
Extended Properties=\"Excel 12.0 Xml;IMEX=1\";
Your milage may vary depending on the version you have installed. The IMEX=1 extended property tells Excel to treat intermixed data as text.
您的里程可能会因您安装的版本而异。IMEX=1 扩展属性告诉 Excel 将混合数据视为文本。
回答by Stu
Prefix with '
以 ' 为前缀
回答by Owen
Prefixing the contents of the cell with ' forces Excel to see it as text instead of a number. The ' won't be displayed in Excel.
使用 ' 前缀单元格的内容会强制 Excel 将其视为文本而不是数字。' 不会显示在 Excel 中。
回答by Krantz
I think the way to do this would be to format the source excel file such that the column is formatted as Text instead of General. Select the entire column and right click and select format cells, select text from the list of options.
我认为这样做的方法是格式化源 excel 文件，以便将列格式化为文本而不是常规。选择整列并右键单击并选择格式单元格，从选项列表中选择文本。
I think that would explicitly define that the column content is text and should be treated as such.
我认为这将明确定义列内容是文本并且应该这样对待。
Let me know if that works.
让我知道这是否有效。
回答by TheEmirOfGroofunkistan
Saving the file as a tab delimited text file has also worked well.
将文件另存为制表符分隔的文本文件也运行良好。
---old Unfortunately, we can't rely on the columns of the excel doc to stay in a particular format as the users will be pasting data into it regularly. I don't want the app to crash if we're relying on a certain datatype for a column.
---旧 不幸的是，我们不能依靠 excel 文档的列来保持特定格式，因为用户会定期将数据粘贴到其中。如果我们依赖某个列的特定数据类型，我不希望应用程序崩溃。
prefixing with ' would work, is there a reasonable way to do that programatically once the data already exists in the excel doc?
以 ' 为前缀会起作用，一旦数据已存在于 excel 文档中，是否有合理的方法以编程方式执行此操作？
回答by cjk
There is a registry hack that can force Excel to read more than the first 8 rows when reading a column to determine the type:
有一个注册表黑客可以强制 Excel 在读取列以确定类型时读取多于前 8 行：
Change
改变
HKLM\Software\Microsoft\Jet.0\Engines\Excel\TypeGuessRows
To be 0 to read all rows, or another number to set it to that number of rows.
为 0 以读取所有行，或为另一个数字以将其设置为该行数。
Not that this will have a slighht performance hit.
并不是说这会对性能造成轻微影响。
回答by user3136602
Sending value 00022556as '=" 00022556"'from Sql server is excellent way to handle leading zero problem
发送值00022556作为'=" 00022556"'从SQL Server是很好的方式来处理前导零问题
回答by zoobiezoobie
Add "\t" before your string. It'll make the string seem in a new tab.
在字符串前添加“\t”。它会使字符串出现在新选项卡中。
