今天预实现一功能,将txt中的数据转到excel表中,做为matlab的数据源。搜集一些c#操作excel的程序。步骤如下:
下载一个Microsoft.Office.Interop.Excel.dll 在项目中引用。
编写代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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秒钟的时间,提高效率几十倍。代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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( "转化成功!" );
|
看到这里就结束了吗?不才刚开始: