C#实现把txt文本数据快速读取到excel中

 

今天预实现一功能，将txt中的数据转到excel表中，做为matlab的数据源。搜集一些c#操作excel的程序。步骤如下：

下载一个Microsoft.Office.Interop.Excel.dll   在项目中引用。

编写代码如下：

string path = "c://date//xyu.txt";
StreamReader sr = new StreamReader(path);
string strLine = sr.ReadLine();
int rowNum = 1;
object missing = System.Reflection.Missing.Value;
 
ApplicationClass app = new ApplicationClass();
 
app.Application.Workbooks.Add(true);
 
Workbook book = (Workbook)app.ActiveWorkbook;
Worksheet sheet = (Worksheet)book.ActiveSheet;
while (!string.IsNullOrEmpty(strLine))
{
  string[] tempArr;
  tempArr = strLine.Split(',');
  for (int k = 1; k <= tempArr.Length; k++)
  {
    sheet.Cells[rowNum, k] = tempArr[k - 1];
 
  }
  strLine = sr.ReadLine();
  rowNum++;
 
}
 
//保存excel文件
book.SaveCopyAs("D://source.xls");
//关闭文件
book.Close(false, missing, missing);
//退出excel
app.Quit();
MessageBox.Show("转化成功！");

以上代码可以实现功能，由于txt中的数据有60501行，数据量太大。我估算了一下，用以上代码转到excel要用大约2-3分钟。我一共要转9个txt。一共要用20多分钟。这样作出系统显然是让人难以忍受的。接着找资料，发现用rang方法可以提高速率。只用大约3-4秒钟的时间，提高效率几十倍。代码如下：

string path = "c://date//xyu.txt";
     StreamReader sr = new StreamReader(path);
     string strLine = sr.ReadLine();
     int rowNum = 1;
     object missing = System.Reflection.Missing.Value;
 
     ApplicationClass app = new ApplicationClass();
 
     app.Application.Workbooks.Add(true);
 
     Workbook book = (Workbook)app.ActiveWorkbook;
     Worksheet sheet = (Worksheet)book.ActiveSheet;
     Range r = sheet.get_Range("A1", "C1");
 
     //获取行数
 
 
 
 
 
     object[,] objectData = new object[65535, 3]; 
     while (!string.IsNullOrEmpty(strLine))
     {
       string[] tempArr;
       tempArr = strLine.Split(',');
       for (int k = 1; k <= tempArr.Length; k++)
       {
          
         objectData[rowNum-1, k-1] = tempArr[k - 1];
 
       }
       strLine = sr.ReadLine();
       rowNum++;
 
     }
     r = r.get_Resize(65535, 3);
     r.Value2 = objectData;
     r.EntireColumn.AutoFit();
     //保存excel文件
     book.SaveCopyAs("D://source.xls");
     //关闭文件
     book.Close(false, missing, missing);
     //退出excel
     app.Quit();
     MessageBox.Show("转化成功！");

 

 

//备忘 https://www.jb51.net/article/67256.htm

/// <summary> 
/// 日志文件记录 
/// </summary> 
/// <param name="logName">日志描述</param>
/// <param name="msg">写入信息</param> 
public static void WriteMsg(string logName, string msg)
{
try
{
string path = Path.Combine("./log");
if (!Directory.Exists(path))//判断是否有该文件 
Directory.CreateDirectory(path);
string logFileName = path + "\\" + DateTime.Now.ToString("yyyy-MM-dd") + ".log";//生成日志文件 
if (!File.Exists(logFileName))//判断日志文件是否为当天
{
FileStream fs;
fs = File.Create(logFileName);//创建文件
fs.Close();
}
StreamWriter writer = File.AppendText(logFileName);//文件中添加文件流

writer.WriteLine(DateTime.Now.ToString("HH:mm:ss") + " " + logName + "\r\n" + msg);
writer.WriteLine("--------------------------------分割线--------------------------------");
writer.Flush();
writer.Close();
}
catch (Exception e)
{
string path = Path.Combine("./log");
if (!Directory.Exists(path))
Directory.CreateDirectory(path);
string logFileName = path + "\\" + DateTime.Now.ToString("yyyy-MM-dd") + ".log";
if (!File.Exists(logFileName))//判断日志文件是否为当天
{
FileStream fs;
fs = File.Create(logFileName);//创建文件
fs.Close();
}
StreamWriter writer = File.AppendText(logFileName);//文件中添加文件流
writer.WriteLine(DateTime.Now.ToString("日志记录错误HH:mm:ss") + "\r\n " + e.Message + " " + msg);
writer.WriteLine("--------------------------------分割线--------------------------------");
writer.Flush();
writer.Close();
}

}

 

/*****第二种*******/

/// <summary>
/// 
/// </summary>
/// <param name="logtype">日志类型</param>
/// <param name="source">来源</param>
/// <param name="message">结果</param>
/// <param name="detail">详细信息</param>
public static void Write(string logtype, string source, string message, string detail)
{
try
{
string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Logs");
Directory.CreateDirectory(path);
string str2 = Path.Combine(path, DateTime.Today.ToString("yyyyMMdd") + ".log");
string str3 = "********************************" + DateTime.Now.ToString() + "********************************";
using (FileStream stream = new FileStream(str2, FileMode.Append, FileAccess.Write, FileShare.Read))
{
using (StreamWriter writer = new StreamWriter(stream, Encoding.UTF8))
{
writer.WriteLine(str3);
writer.WriteLine("Type: " + logtype);
writer.WriteLine("Source: " + source);
writer.WriteLine("Message: " + message);
writer.WriteLine("Detail: " + detail);
string str4 = "";
writer.WriteLine(str4.PadLeft(str3.Length, '*'));
writer.WriteLine();
writer.Flush();
}
}
}
catch
{
}

 

记录自己的成长，也希望能帮助别人成长，高手勿喷，有不足的地方非常感谢你的指导