强制 Excel 按照我设置格式的方式理解数字

发布于 2024-10-01 13:16:29 字数 4300 浏览 5 评论 0原文

我在软件上做了一些基准测试实验，现在我必须绘制往返时间的结果。

在实验过程中，我们生成了大量 Syslog 格式 (RFC 5424) 的消息，指示在不同的受控测试条件下程序处理消息所花费的时间。现在我们有几条日志，6 台机器各有 1 条，可以进行几乎一整天的实验。

我决定使用 Excel 绘制实验时间和 RTT 的图表，因此我创建了一个小型 C# 实用程序来解析 Syslog 消息并将其转换为 CSV，选择我想要的字段。

以下是实验的一些系统日志消息的示例。

<167>1 2010-10-07T13:29:53+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="50"] RTT: 12.353
<167>1 2010-10-07T13:29:56+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="51"] RTT: 12.775
<167>1 2010-10-07T13:29:59+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="52"] RTT: 12.979
<167>1 2010-10-07T13:30:02+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="53"] RTT: 20.899
<167>1 2010-10-07T13:30:05+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="54"] RTT: 12.971
<167>1 2010-10-07T13:30:08+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="55"] RTT: 12.419
<167>1 2010-10-07T13:30:11+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="56"] RTT: 12.34
<167>1 2010-10-07T13:30:14+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="57"] RTT: 13.622
<167>1 2010-10-07T13:30:17+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="58"] RTT: 12.873
<167>1 2010-10-07T13:30:20+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="59"] RTT: 12.474

所有消息的格式都相同。 RTT 时间以毫秒为单位。不幸的是，当我生成这些日志时，我并不关心指定固定的小数位数，因此一些 RTT 结果看起来像 12.34ms 而不是 12.340ms，正如我将要讨论的那样。

转换实用程序仅选择一些字段并生成一个 CSV 文件，在最后一列中包含时间戳。如您所见，数字格式使用小数点。不幸的是，在意大利，我们使用小数逗号并点作为组分隔符，因此Excel会忽略它并认为12.419是12419，12.34是1234。如果Excel认为这些是可以的数字以微秒为单位，但 1234μs 比 12419μs 小 10 倍。

我的问题是

如何强制Excel理解这些数字是用小数点表示的？如果我输入自定义数字格式 0.000，那些具有 2 位十进制数字的数字将被视为字符串并左对齐。

或者

，因为我可以根据需要多次重新转换日志，但我不会不想重复整个实验，你能提醒我如何在 C# 中将双精度数转换为强制保留 3 位小数的字符串吗？

提前谢谢你

PS：我知道这个问题是在 stackoverflow 和超级用户之间，但我还在第二种情况下询问了 C# 帮助

原文

I did some benchmarking experiments on a software, and now I must plot the results of the round-trip time.

During the experiment, we generated lots of messages in Syslog format (RFC 5424) indicating how much time it took for a message to be processed by the program under different and controlled test conditions. Now we have several logs, 1 from each of 6 machines, for an almost entire day of experimenting.

I decided to use Excel to plot a graph of time of experiment and RTT, so I created a little C# utility to parse Syslog messages and convert them into CSV selecting the fields I want.

The following is an example of some Syslog messages for the experiment

<167>1 2010-10-07T13:29:53+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="50"] RTT: 12.353
<167>1 2010-10-07T13:29:56+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="51"] RTT: 12.775
<167>1 2010-10-07T13:29:59+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="52"] RTT: 12.979
<167>1 2010-10-07T13:30:02+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="53"] RTT: 20.899
<167>1 2010-10-07T13:30:05+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="54"] RTT: 12.971
<167>1 2010-10-07T13:30:08+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="55"] RTT: 12.419
<167>1 2010-10-07T13:30:11+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="56"] RTT: 12.34
<167>1 2010-10-07T13:30:14+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="57"] RTT: 13.622
<167>1 2010-10-07T13:30:17+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="58"] RTT: 12.873
<167>1 2010-10-07T13:30:20+02:00 rosy717 RTTMonitor 4669 - [CallerData@8289 ClassName="RTTMonitor.Program" MethodName="source_MessageReceived" ModuleName="RTTMonitor" LogName="Logfile"][timeQuality tzKnown="1"][origin ip="10.0.0.58" enterpriseId="8289" software="Logbus-ng-sharp" swVersion="1.8.3"][meta sequenceId="59"] RTT: 12.474

The messages are all formatted the same way. RTT times are in milliseconds. Unfortunately, when I generated these logs, I didn't care about specifying a fixed number of decimals, so some RTT results look like 12.34ms rather than 12.340ms, as I'm going to discuss

The conversion utility picks only some fields and generates a CSV file containing, in the final column, the timestamps. As you can see, the number format uses the decimal point. Unfortunately, in Italy we use decimal comma and point as group separator, so Excel ignores it and thinks 12.419 is 12419 and 12.34 is 1234. It would be OK if Excel thinks these numbers are microseconds, but 1234μs is 10 times less than 12419μs.

My question is

How to force Excel understand that these numbers are meant with decimal point? If I input a customized number format 0.000, those numbers that have 2 decimal digits are treated as strings and aligned to left

Alternatively

Since I can re-convert logs as many times as I want, but I wouldn't like to repeat the whole experiment, can you remind me how in C# do you convert a double to string forcing 3 decimals?

Thank you in advance

PS: I understand this question is between stackoverflow and superuser, but I also asked C# help in the second case

分享到QQ

分享到微博