如何创建 CSV 以便数字在电子表格中显示为字符串？

发布于 2024-10-25 16:02:59 字数 599 浏览 1 评论 0原文

我正在尝试构建一个应用程序，从数据库中提取一些数据，然后使用一些数据创建一个由 Excel 加载的 CSV 文件。代码：

foreach (xOFDocInfo cm in docs)
{
    string s = bi.Agency
        + "," + cm.BatNbr.Trim()
        + "," + cm.RefNbr
        + "," + cm.DocType
        + "," + cm.OrigDocAmt.ToString()
        + "," + cm.CreateDate.ToShortDateString();

    writer.WriteLine(s);

}

“cm.BatNbr”是一个 6 个字符的零填充数字，例如“001234”。我希望 Excel 将该列格式化为文本，这样我就不会丢失前面的零。我尝试了一些技巧，例如在数字前加上单引号（撇号），但我得到的只是撇号前缀。如果我将单元格设置为文本格式，然后删除撇号，我也会丢失前面的零。

我意外地发现，如果我用百分号作为前缀，Excel 会将单元格中的值转换为百分比，所以也许我可以使用一些前缀来使 Excel 在加载单元格中的值时将其作为文本向上？

原文

I am trying to build an application that extracts some data from a database, and then uses some of the data to create a CSV file to be loaded up by Excel. The codez:

foreach (xOFDocInfo cm in docs)
{
    string s = bi.Agency
        + "," + cm.BatNbr.Trim()
        + "," + cm.RefNbr
        + "," + cm.DocType
        + "," + cm.OrigDocAmt.ToString()
        + "," + cm.CreateDate.ToShortDateString();

    writer.WriteLine(s);

}

The "cm.BatNbr" is a 6 character zero-filled numeric such as "001234". I want Excel to format that column as text so I don't lose the zeroes up front. I've tried some tricks, such as prefixing the number with a single-quote (apostrophe), but all I get is an apostrophe prefix. If I set the cells to be formatted as text then remove the apostrophes, I also lose the zeroes at the front.

I accidentally found that if I prefix the thing with a percent sign, Excel converts the value in the cell to a percentage, so perhaps there is some prefix I can use to cause Excel to take the value in the cell as text when I load it up?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苦行僧 2024-11-01 16:02:59

您可以将数据格式设置为 ="001234"。这将导致 Excel 将其显示为 001234。

foreach (xOFDocInfo cm in docs)
{
    string s = bi.Agency
        + ",=\"" + cm.BatNbr.Trim()
        + "\"," + cm.RefNbr
        + "," + cm.DocType
        + "," + cm.OrigDocAmt.ToString()
        + "," + cm.CreateDate.ToShortDateString();

    writer.WriteLine(s);

}

您还可以尝试使用 SYLK 格式而不是 CSV。 SYLK 使您可以更好地控制格式。

You could format the data as ="001234". This will cause Excel to display it as 001234.

foreach (xOFDocInfo cm in docs)
{
    string s = bi.Agency
        + ",=\"" + cm.BatNbr.Trim()
        + "\"," + cm.RefNbr
        + "," + cm.DocType
        + "," + cm.OrigDocAmt.ToString()
        + "," + cm.CreateDate.ToShortDateString();

    writer.WriteLine(s);

}

You could also try using the SYLK format instead of CSV. SYLK gives you better control over formatting.

回复收藏 0 原文

一抹苦笑 2024-11-01 16:02:59

这对我有用：

更改行：

    + "," + cm.BatNbr.Trim()

用于：

    + ",\t" + cm.BatNbr.Trim()

This worked for me:

Change the line:

    + "," + cm.BatNbr.Trim()

for:

    + ",\t" + cm.BatNbr.Trim()

回复收藏 0 原文

向地狱狂奔 2024-11-01 16:02:59

尝试引用 BatNbr

foreach (xOFDocInfo cm in docs)
{
    string s = bi.Agency
        + ", \"" + cm.BatNbr.Trim() + "\""
        + "," + cm.RefNbr
        + "," + cm.DocType
        + "," + cm.OrigDocAmt.ToString()
        + "," + cm.CreateDate.ToShortDateString();

    writer.WriteLine(s);

}

Try quoting the BatNbr

foreach (xOFDocInfo cm in docs)
{
    string s = bi.Agency
        + ", \"" + cm.BatNbr.Trim() + "\""
        + "," + cm.RefNbr
        + "," + cm.DocType
        + "," + cm.OrigDocAmt.ToString()
        + "," + cm.CreateDate.ToShortDateString();

    writer.WriteLine(s);

}

回复收藏 0 原文