blocks|key|1525894|text|不要使用CSV，使用SYLK。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1525895|http://en.wikipedia.org/wiki/SYmbolic_LinK_(SYLK)|offset|length|1525896|它为格式化提供了更多的控制，Excel不会试图通过检查内容来猜测字段的类型。它看起来有点复杂，但您可以使用一个非常小的子集。|1525897|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1D|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

Don't use CSV, use SYLK. 
<a href="http://en.wikipedia.org/wiki/SYmbolic_LinK_(SYLK)" rel="noreferrer">http://en.wikipedia.org/wiki/SYmbolic_LinK_(SYLK)</a>

It gives much more control over formatting, and Excel won't try to guess the type of a field by examining the contents. It looks a bit complicated, but you can get away with using a very small subset.

blocks|key|1525570|text|您可以简单地将范围格式化为文本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1525571|此外，here是一篇关于数字格式以及如何对其进行编程的很好的文章。|offset|length|1525572|entityMap|0|LINK|mutability|MUTABLE|url|http://peltiertech.com/Excel/NumberFormats.html^0|0|3|4|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You can simply format your range as Text.

Also <a href="http://peltiertech.com/Excel/NumberFormats.html" rel="nofollow noreferrer">here</a> is a nice article on the number formats and how you can program them.

blocks|key|1741031|text|实际上，我发现，至少从Office2003开始，您可以将Excel电子表格另存为XML文件。因此，我可以生成一个XML文件，当我双击它时，它将在Excel中打开。它提供与SYLK相同级别的控制，但XML语法更直观。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1741032|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Actually I discovered that, at least starting with Office 2003, you can save an Excel spreadsheet as an XML file.
Thus, I can produce an XML file and when I double-click on it, it'll be opened in Excel.
It provides the same level of control as SYLK, but XML syntax is more intuitive.

blocks|key|1526272|text|在单元格中添加不间断的空格可能会有所帮助。例如："firstvalue";"secondvalue";"005 ";"othervalue"|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1526273|它强制Excel将其视为文本，并且空格不可见。在Windows上，你可以通过tiping+alt%2B0160来添加一个不间断的空格。有关更多信息，请查看此处：http://en.wikipedia.org/wiki/Non-breaking_space|1526274|已在Excel+2010上试用。希望这能帮助那些仍然在寻找这个问题的合适解决方案的人。|1526275|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Non-breaking_space^0|O|1A|0|26|1B|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|U|8|@]|D|@$9|V|A|W|1|X]]|E|$]]|$1|H|3|I|5|6|7|Y|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]]]

Adding a non-breaking space in the cell could help.
For instance: 
<code>"firstvalue";"secondvalue";"005 ";"othervalue"</code>

It forces Excel to treat it as a text and the space is not visible.
On Windows you can add a non-breaking space by tiping alt+0160.
See here for more info: <a href="http://en.wikipedia.org/wiki/Non-breaking_space" rel="nofollow">http://en.wikipedia.org/wiki/Non-breaking_space</a>

Tried on Excel 2010.
Hope this can help people who still search a quite proper solution for this problem.

blocks|key|1526380|text|我在从C#代码导出CSV数据时遇到了这个问题，并通过在前导零数据前加上制表符\t来解决这个问题，这样数据在Excel中就会被解释为文本而不是数字(但与前缀其他字符不同的是，它不会被看到)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1526381|我确实喜欢="001“方法，但这不允许将导出的CSV数据重新导入到我的C#应用程序中，而不是删除导入CSV文件中的所有格式(相反，我将只修剪导入数据)。|1526382|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I had this issue when exporting CSV data from C# code, and resolved this by prepending the leading zero data with the tab character \t, so the data was interpreted as text rather than numeric in Excel (yet unlike prepending other characters, it wouldn't be seen). 

I did like the ="001" approach, but this wouldn't allow exported CSV data to be re-imported again to my C# application without removing all this formatting from the import CSV file (instead I'll just trim the import data).

blocks|key|1525699|text|我相信当您导入文件时，您可以选择列类型。将其设置为文本而不是数字。不过，目前我面前没有一份可以检查的副本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1525700|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I believe when you import the file you can select the Column Type. Make it Text instead of Number. I don't have a copy in front of me at the moment to check though.

blocks|key|1741201|text|Load+csv+into+oleDB+and+force+all+inferred+datatypes+to+string|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1741202|我问了同样的问题，然后用代码回答了它。|1741203|基本上，当csv文件加载时，oledb驱动程序进行假设，您可以告诉它要做什么假设。|1741204|我的代码强制所有数据类型为字符串...更改模式非常容易。出于我的目的，我使用xslt以我想要的方式获取ti+-但是我正在解析各种各样的文件。|1741205|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/1688497/load-csv-into-oledb-and-force-all-inferred-datatypes-to-string^0|0|1Q|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|C|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|K|$L|$5|M|N|O|C|$P|Q]]]]

<a href="https://stackoverflow.com/questions/1688497/load-csv-into-oledb-and-force-all-inferred-datatypes-to-string">Load csv into oleDB and force all inferred datatypes to string</a>

i asked the same question and then answerd it with code.

basically when the csv file is loaded the oledb driver makes assumptions, you can tell it what assumptions to make.

My code forces all datatypes to string though ... its very easy to change the schema.
for my purposes i used an xslt to get ti the way i wanted - but i am parsing a wide variety of files.

blocks|key|1741258|text|我知道这是一个老问题，但我有一个解决方案没有在这里列出。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1741259|生成csv时，在逗号之后、值之前添加一个空格，例如,+005,。|offset|length|style|CODE|1741260|这就阻止了在excel2007中自动设置日期格式。|1741261|entityMap^0|0|P|6|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

I know this is an old question, but I have a solution that isn't listed here.

When you produce the csv add a space after the comma but before your value e.g. <code>, 005,</code>. 

This worked to prevent auto date formatting in excel 2007 anyway .

blocks|key|1526307|text|只需在CSV文档中的数字前添加‘。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1526308|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Just add ' before the number in the CSV doc.

blocks|key|1526424|text|这一整天都让我抓狂(因为在打开CSV文件之前，你确实不能控制Excel的列类型)，这对我很有效，使用VB.NET和Excel+Interop：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1526425|++++++++'Convert+.csv+file+to+.txt+file.
++++++++FileName+=+ConvertToText(FileName)

++++++++Dim+ColumnTypes(,)+As+Integer+=+New+Integer(,)+{{1,+xlTextFormat},+_
++++++++++++++++++++++++++++++++++++++++++++++++++++++++{2,+xlTextFormat},+_
++++++++++++++++++++++++++++++++++++++++++++++++++++++++{3,+xlGeneralFormat},+_
++++++++++++++++++++++++++++++++++++++++++++++++++++++++{4,+xlGeneralFormat},+_
++++++++++++++++++++++++++++++++++++++++++++++++++++++++{5,+xlGeneralFormat},+_
++++++++++++++++++++++++++++++++++++++++++++++++++++++++{6,+xlGeneralFormat}}

++++++++'We+are+using+OpenText()+in+order+to+specify+the+column+types.
++++++++mxlApp.Workbooks.OpenText(FileName,+,+,+Excel.XlTextParsingType.xlDelimited,+,+,+True,+,+True,+,+,+,+ColumnTypes)
++++++++mxlWorkBook+=+mxlApp.ActiveWorkbook
++++++++mxlWorkSheet+=+CType(mxlApp.ActiveSheet,+Excel.Worksheet)


Private+Function+ConvertToText(ByVal+FileName+As+String)+As+String
++++'Convert+the+.csv+file+to+a+.txt+file.
++++'If+the+file+is+a+text+file,+we+can+specify+the+column+types.
++++'Otherwise,+the+Codes+are+first+converted+to+numbers,+which+loses+trailing+zeros.

++++Try
++++++++Dim+MyReader+As+New+StreamReader(FileName)
++++++++Dim+NewFileName+As+String+=+FileName.Replace(".CSV",+".TXT")
++++++++Dim+MyWriter+As+New+StreamWriter(NewFileName,+False)
++++++++Dim+strLine+As+String

++++++++Do+While+Not+MyReader.EndOfStream
++++++++++++strLine+=+MyReader.ReadLine
++++++++++++MyWriter.WriteLine(strLine)
++++++++Loop

++++++++MyReader.Close()
++++++++MyReader.Dispose()
++++++++MyWriter.Close()
++++++++MyWriter.Dispose()

++++++++Return+NewFileName
++++Catch+ex+As+Exception
++++++++MsgBox(ex.Message)
++++++++Return+""
++++End+Try

End+Function|code-block|syntax|javascript|1526426|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This has been driving me crazy all day (since indeed you can't control the Excel column types before opening the CSV file), and this worked for me, using VB.NET and Excel Interop:

<pre class="lang-vb prettyprint-override"><code> 'Convert .csv file to .txt file.
 FileName = ConvertToText(FileName)

 Dim ColumnTypes(,) As Integer = New Integer(,) {{1, xlTextFormat}, _
 {2, xlTextFormat}, _
 {3, xlGeneralFormat}, _
 {4, xlGeneralFormat}, _
 {5, xlGeneralFormat}, _
 {6, xlGeneralFormat}}

 'We are using OpenText() in order to specify the column types.
 mxlApp.Workbooks.OpenText(FileName, , , Excel.XlTextParsingType.xlDelimited, , , True, , True, , , , ColumnTypes)
 mxlWorkBook = mxlApp.ActiveWorkbook
 mxlWorkSheet = CType(mxlApp.ActiveSheet, Excel.Worksheet)


Private Function ConvertToText(ByVal FileName As String) As String
 'Convert the .csv file to a .txt file.
 'If the file is a text file, we can specify the column types.
 'Otherwise, the Codes are first converted to numbers, which loses trailing zeros.

 Try
 Dim MyReader As New StreamReader(FileName)
 Dim NewFileName As String = FileName.Replace(".CSV", ".TXT")
 Dim MyWriter As New StreamWriter(NewFileName, False)
 Dim strLine As String

 Do While Not MyReader.EndOfStream
 strLine = MyReader.ReadLine
 MyWriter.WriteLine(strLine)
 Loop

 MyReader.Close()
 MyReader.Dispose()
 MyWriter.Close()
 MyWriter.Dispose()

 Return NewFileName
 Catch ex As Exception
 MsgBox(ex.Message)
 Return ""
 End Try

End Function
</code></pre>

blocks|key|1740860|text|打开CSV时，您会看到文本导入向导。在向导的最后一步，您应该能够将特定列作为文本导入，从而保留'00‘前缀。之后，您可以以任何您想要的方式设置单元格的格式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1740861|我尝试使用Excel2007，它似乎可以工作。|1740862|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

When opening a CSV, you get the text import wizard. At the last step of the wizard, you should be able to import the specific column as text, thereby retaining the '00' prefix. After that you can then format the cell any way that you want.

I tried with with Excel 2007 and it appeared to work.

blocks|key|1526064|text|嗯，excel从来没有弹出CSV文件的向导。如果将其重命名为.txt，则下次在Excel中执行File>Open时将看到该向导。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1526065|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Well, excel never pops up the wizard for CSV files. If you rename it to .txt, you'll see the wizard when you do a File>Open in Excel the next time.

I produce a report as an CSV file.
When I try to open the file in Excel, it makes an assumption about the data type based on the contents of the cell, and reformats it accordingly.

For example, if the CSV file contains

<pre><code>...,005,...
</code></pre>

Then Excel shows it as 5.
Is there a way to override this and display 005?

I would prefer to do something to the file itself, so that the user could just double-click on the CSV file to open it.

I use Excel 2003.

Excel CSV - Number cell format

Windows

我以CSV文件的形式生成报告。当我尝试在Excel中打开文件时，它会根据单元格的内容对数据类型进行假设，并相应地重新格式化。例如，如果CSV文件包含...,005,...那么Excel会将其显示为5。有没有办法覆盖它并显示005？我更喜欢对文件本身做一些操作，这样用户只需双击CSV文件就可以打开它。我使用Excel2003。

问Excel CSV -数字单元格格式
EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel CSV -数字单元格格式EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel CSV -数字单元格格式
EN