将多个.txt文件的内容放入Excel 2010中 [英] Put the content of multiple .txt files into Excel 2010

问题描述

有没有什么办法可以将不同的多个.txt文件（实际上是一个文件夹中的所有.txt文件的内容）的内容放入Excel 2010中？我需要一个单元格（A1）作为文件的名称，另一个单元格（A2）是该.txt文件的全部内容。对于其他.txt文件，即B1-B2，C1-C2等也是一样。

Is there any way I can put the content of different multiple .txt files (practically the content of all .txt file in one folder) into Excel 2010? I need one cell (A1) to be the name of the file, and the other cell (A2) to be the whole content of that .txt file. Same goes for the other .txt files, i.e. B1-B2, C1-C2, etc.

提前感谢

推荐答案

如果 CSV 可以接受您可以编写一个程序来读取给定目录中的所有文本文件，并以CSV格式写出所需的数据：

If a CSV is acceptable, you can write a little program to read in all text files in the given directory and write out the data you need in CSV format:

"File Name 1","Contents of File 1" 
"File Name 2","Contents of File 2"

如果您在Excel中打开CSV，数据将按照您的指定显示。

If you open the CSV in Excel, the data will display just as you specify.

如果您必须拥有一个真正的Excel文件.xls，.xlsx），您可以使用Interop从C＃访问Excel库，但这个解决方案有点复杂。

If you must have a true Excel file (.xls, .xlsx), you can use Interop to access the Excel libraries from C#, but this solution is somewhat more complex.

http://msdn.microsoft.com/en-us/library/ms173186（v = vs80）.aspx

您可以使用 Directory.EnumerateFiles 列出所需文件夹中的所有文件名，并将 File.ReadAllText 读入每个文件的内容。

You can use Directory.EnumerateFiles to list all of the file names in the desired folder, and File.ReadAllText to read in the contents of each file.

当使用CSV文件时，输出的正确引用有一些细微差别（见答案开头的维基百科链接）。我写了一个扩展方法，使其更容易输出正确引用的CSV ：

When working with CSV files, there are some nuances around proper quoting of the output (see the Wikipedia link at the start of my answer). I wrote a little extension method to make it easier to output properly quoted CSV:

   static public class Extensions
    {
        static public string CsvQuote(this string text)
        {
            if (text == null)
            {
                return string.Empty;
            }

            bool containsQuote = false;
            bool containsComma = false;
            int len = text.Length;
            for (int i = 0; i < len && (containsComma == false || containsQuote == false); i++)
            {
                char ch = text[i];
                if (ch == '"')
                {
                    containsQuote = true;
                }
                else if (ch == ',')
                {
                    containsComma = true;
                }
            }

            bool mustQuote = containsComma || containsQuote;

            if (containsQuote)
            {
                text = text.Replace("\"", "\"\"");
            }

            if (mustQuote)
            {
                return "\"" + text + "\"";  // Quote the cell and replace embedded quotes with double-quote
            }
            else
            {
                return text;
            }
        }

    }

编辑：

在我的头顶（没有调试或任何东西），写出CSV的代码可能看起来像这样：

Off the top of my head (not debugged or anything), the code to write out the CSV could look something like this:

string myDirectory = @"C:\Temp";
StringBuilder csv = new StringBuilder();
foreach (string fileName in Directory.EnumerateFiles(myDirectory))
{
    string fileContents = File.ReadAllText(fileName);
    csv.Append(fileName).Append(",").AppendLine(fileContents.CsvQuote());
}
File.WriteAllText(@"C:\SomePath\SomeFile.csv", csv.ToString());

这篇关于将多个.txt文件的内容放入Excel 2010中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！