在Excel中打开CSV文件会更改我的格式 [英] Opening a CSV file in excel changes my formats

问题描述

我有一个CSV文件，它有一个日期时间列，我使用php来改变为这种格式，02/18/2013 03:53:48 PM

I have a CSV File which has a DateTime Column that i used php to alter in order for it to be in this format, 02/18/2013 03:53:48 PM

但是当这个文件在excel中打开时，所有的格式都改变为这种格式：
02/18/2013 15:53

But when this file is opened up in excel all the formats are changed to this format, 02/18/2013 15:53

现在这不会是一个问题，我看看或处理，但我想最终用户不必看看时间在24小时格式在excel。如何让excel不改变我的格式一旦csv打开和允许最终用户排序这个DateTime列没有任何问题。我知道我可以通过格式化单元格，一旦我打开文件，但我不希望最终用户甚至不得不采取这一步，改变格式的日期。

Now this would not be a problem for me to look at or deal with but i want the end user to not have to look at the time in a 24 hour format in excel. How do i make excel not change my format once the csv is opened AND allow the end user to sort this DateTime column without any issues. I am aware i can change the format of the date by formatting the cells once i open the file but i don't want the end user to even have to take this step.

我还要指出，当我选择一个单元格时，公式栏将以我想要的格式显示我的日期时间列，这让我感到困惑。

I'd also like to point out that when i select a cell the formula bar will show my DateTime column in the format i want which baffles me.

推荐答案

CSV文件没有格式，只包含数据。

CSV Files don't have a format, they just contain data.

MS Excel将检查该数据，格式。例如，它右对齐数值。它可以识别包含人类可读的日期/时间值的字符串，并将其转换为自己的内部timestammp值（表示为浮点数），然后对该单元格应用适当的日期/时间掩码，以使其样式为日期或时间，但不一定使用您在CSV文件中的字符串表示形式。

MS Excel will examine that data, and try to set an appropriate format for it. It right-justifies numeric values, for example. It can recognise a string containing a human-readable date/time value, and converts it to its own internal timestammp value (represented as a floating point number) and then applies an appropriate date/time mask to that cell so that it's styles as a date or time, but not necessarily using the string representation that you had in your CSV file.

解决方案＃1，

解决方案＃2，写一个实际的Excel文件，您可以在其中控制单元格的格式。

Solution#2, write an actual Excel file where you can control the formatting of cells.

这篇关于在Excel中打开CSV文件会更改我的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！