求教基础问题，麻烦大大们指教一下
同学有两个文件，需要将a.text中的几万行文本中每行的几个字符更新，更新值要从b.xls中对应项目来获取，我用VB 2012 Express写了个程序来实现，但是程序超级慢，老是会无响应（能一直运行并更新）,一秒估计才能更新一两行...
求各位大大指教我怎么优化...告诉我到底哪里拖慢程序。
是不是不用replace，而更新完所有数组单元行，再合并数组会快很多？
Imports System.Data.OleDb
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
OpenFileDialog1.ShowDialog()
End Sub
Private Sub OpenFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
TextBox1.Text = OpenFileDialog1.FileName
TextBox3.Text = IO.File.ReadAllText(TextBox1.Text)
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
OpenFileDialog2.ShowDialog()
End Sub
Private Sub OpenFileDialog2_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog2.FileOk
TextBox2.Text = OpenFileDialog2.FileName
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
Dim Splitline() As String
Dim Oldtext As String
Dim Newtext As String
Dim ifv As String
Dim if5 As String
Dim iftitle As String
Dim ttl As Integer
Dim nocount As Integer = 0
Dim updatecount As Integer = 0
Dim objConn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + TextBox2.Text + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'")
objConn.Open()
Splitline = Split(TextBox3.Text, vbCrLf)
For ttl = 0 To Splitline.Length - 1
iftitle = Split(Splitline(ttl), vbTab)(1)
Oldtext = Split(Splitline(ttl), vbTab)(3)
'MsgBox(iftitle)
Dim objCmdSelect As New OleDbCommand("SELECT * FROM [JCR2012$] where [Abbreviated Journal Title (linked to journal information)]= '" + iftitle + "'", objConn)
Dim myrs As OleDbDataReader = objCmdSelect.ExecuteReader()
If myrs.HasRows = True Then
While myrs.Read()
ifv = myrs(4).ToString
If ifv = "" Then
ifv = "0"
End If
if5 = myrs(5).ToString
If if5 = "" Then
if5 = "0"
End If
End While
Newtext = iftitle + " (" + ifv + ") 5years(" + if5 + ")"
TextBox3.Text = Replace(TextBox3.Text, Oldtext, Newtext)
updatecount = updatecount + 1
Else
nocount = nocount + 1
Label1.Text = "已忽略" + nocount.ToString + "项"
End If
Next
Label2.Text = "已更新" + updatecount.ToString + "项"
End Sub
End Class
using NPOI.HSSF.UserModel;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
FileStream fs;
POIFSFileSystem poifs;
HSSFWorkbook book;
ISheet sheet;
private void Form1_Load(object sender, EventArgs e)
{
listBox1.Items.Add("读入文件到内存"); listBox1.Update();
fs = new FileStream(Environment.CurrentDirectory + @"\xls.xls", FileMode.Open);
listBox1.Items.Add("展开到工作簿"); listBox1.Update();
poifs = new POIFSFileSystem(fs);
book = new HSSFWorkbook(poifs);
sheet = book.GetSheetAt(0);
listBox1.Items.Add("初始化主键字典"); listBox1.Update();
initData();
listBox1.Items.Add("主键字典共 " + dic_cache.Count.ToString() + "条"); listBox1.Update();
listBox1.Items.Add("初始化完成"); listBox1.Update();
bgw1.DoWork += bgw1_DoWork;
bgw1.RunWorkerCompleted += bgw1_RunWorkerCompleted;
bgw2.DoWork += bgw2_DoWork;
bgw2.RunWorkerCompleted += bgw2_RunWorkerCompleted;
this.Text = "初始化完成";
}
double a = 0, b = 0;
void initData()
{
for (int i = 1; i < sheet.LastRowNum; i++)
{
switch (sheet.GetRow(i).GetCell(4).CellType)
{
case CellType.BLANK:
break;
case CellType.BOOLEAN:
break;
case CellType.ERROR:
break;
case CellType.FORMULA:
break;
case CellType.NUMERIC:
a = sheet.GetRow(i).GetCell(4).NumericCellValue;
break;
case CellType.STRING:
a = -999;
break;
case CellType.Unknown:
break;
default:
break;
}
switch (sheet.GetRow(i).GetCell(5).CellType)
{
case CellType.BLANK:
break;
case CellType.BOOLEAN:
break;
case CellType.ERROR:
break;
case CellType.FORMULA:
break;
case CellType.NUMERIC:
b = sheet.GetRow(i).GetCell(5).NumericCellValue;
break;
case CellType.STRING:
b = -999;
break;
case CellType.Unknown:
break;
default:
break;
}
if (!dic_cache.ContainsKey(sheet.GetRow(i).GetCell(1).StringCellValue)) { dic_cache.Add(sheet.GetRow(i).GetCell(1).StringCellValue, new double[] { a, b }); }
}
}
Stopwatch sw = new Stopwatch();
Dictionary<string, double[]> dic_cache = new Dictionary<string, double[]>();
BackgroundWorker bgw1 = new BackgroundWorker();
BackgroundWorker bgw2 = new BackgroundWorker();
private void button1_Click(object sender, EventArgs e)
{
//try
//{
procCount = 0;
sw.Reset(); sw.Start();
bgw1.RunWorkerAsync();
bgw2.RunWorkerAsync();
//}
//catch (Exception ex)
//{
// this.Text = ex.Message;
//}
}
void bgw2_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
if (!bgw1.IsBusy)
{
System.IO.File.WriteAllLines(Environment.CurrentDirectory + @"\newtxt.txt", arr_str, Encoding.Default);
sw.Stop();
this.Text = "替换了 " + procCount.ToString() + " 个匹配项 ， 耗时 " + sw.Elapsed.TotalSeconds.ToString("0.0000000") + " 秒";
}
}
void bgw1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
if (!bgw2.IsBusy)
{
System.IO.File.WriteAllLines(Environment.CurrentDirectory + @"\newtxt.txt", arr_str, Encoding.Default);
sw.Stop();
this.Text = "替换了 " + procCount.ToString() + " 个匹配项 ， 耗时 " + sw.Elapsed.TotalSeconds.ToString("0.0000000") + " 秒";
}
}
void bgw2_DoWork(object sender, DoWorkEventArgs e)
{
string[] temp_arr_str = null;
for (int i = arr_str.Length / 2; i < arr_str.Length; i++)
{
temp_arr_str = arr_str[i].Split('\t');
if (temp_arr_str.Length > 2)
{
if (dic_cache.ContainsKey(temp_arr_str.Reverse().ToArray()[2].ToUpper()))
{
arr_str[i] = arr_str[i].Replace("0)", dic_cache[temp_arr_str.Reverse().ToArray()[2].ToUpper()][0].ToString() + ")");
arr_str[i] = arr_str[i].Replace("5years(0)", "5years(" + dic_cache[temp_arr_str.Reverse().ToArray()[2].ToUpper()][1].ToString() + ")");
arr_str[i] = arr_str[i].Replace("-999", "0");
procCount++;
}
}
}
}
void bgw1_DoWork(object sender, DoWorkEventArgs e)
{
string[] temp_arr_str = null;
for (int i = 0; i < arr_str.Length / 2; i++)
{
temp_arr_str = arr_str[i].Split('\t');
if (temp_arr_str.Length > 2)
{
if (dic_cache.ContainsKey(temp_arr_str.Reverse().ToArray()[2].ToUpper()))
{
arr_str[i] = arr_str[i].Replace("0)", dic_cache[temp_arr_str.Reverse().ToArray()[2].ToUpper()][0].ToString() + ")");
arr_str[i] = arr_str[i].Replace("5years(0)", "5years(" + dic_cache[temp_arr_str.Reverse().ToArray()[2].ToUpper()][1].ToString() + ")");
arr_str[i] = arr_str[i].Replace("-999", "0");
procCount++;
}
}
}
}
int procCount = 0;
string[] arr_str = System.IO.File.ReadAllLines(Environment.CurrentDirectory + @"\txt.txt", Encoding.Default);
void updateValue(int index)
{
string[] temp_arr_str = null;
temp_arr_str = arr_str[index].Split('\t');
if (temp_arr_str.Length > 2)
{
if (dic_cache.ContainsKey(temp_arr_str.Reverse().ToArray()[2].ToUpper()))
{
arr_str[index] = arr_str[index].Replace("0)", dic_cache[temp_arr_str.Reverse().ToArray()[2].ToUpper()][0].ToString() + ")");
arr_str[index] = arr_str[index].Replace("5years(0)", "5years(" + dic_cache[temp_arr_str.Reverse().ToArray()[2].ToUpper()][1].ToString() + ")");
arr_str[index] = arr_str[index].Replace("-999", "0");
procCount++;
}
}
}
}
}
Imports System.Data.OleDb
Public Class Form1
Dim Oldall As String
Dim Newall As String
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
OpenFileDialog1.ShowDialog()
End Sub
Private Sub OpenFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
TextBox1.Text = OpenFileDialog1.FileName
Oldall = IO.File.ReadAllText(TextBox1.Text)
Button2.Enabled = True
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
OpenFileDialog2.ShowDialog()
End Sub
Private Sub OpenFileDialog2_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog2.FileOk
TextBox2.Text = OpenFileDialog2.FileName
Button3.Enabled = True
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
TextBox3.Focus()
Dim Splitline() As String
Dim Oldtext As String
Dim Newtext As String
Dim ifv As String
Dim if5 As String
Dim iftitle As String
Dim ttl As Integer
Dim nocount As Integer = 0
Dim updatecount As Integer = 0
Dim objConn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + TextBox2.Text + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'")
objConn.Open()
Dim objCmdSelect As New OleDbCommand
objCmdSelect.Connection = objConn
Dim myrs As OleDbDataReader
Splitline = Split(Oldall, vbCrLf)
Newall = Oldall
For ttl = 0 To Splitline.Length - 1
If Splitline(ttl) = "" Then
MsgBox("已对比" + ttl.ToString + "项，已更新至缓存")
Button4.Enabled = True
Exit Sub
Else
iftitle = Split(Splitline(ttl), vbTab)(1)
Oldtext = Split(Splitline(ttl), vbTab)(3)
'MsgBox(iftitle)
objCmdSelect.CommandText = "SELECT * FROM [JCR2012$] where [Abbreviated Journal Title (linked to journal information)]= '" + iftitle + "'"
myrs = objCmdSelect.ExecuteReader()
'MsgBox("查询完毕")
If myrs.HasRows = True Then
While myrs.Read()
ifv = myrs(4).ToString
If Trim(ifv) = "" Then
ifv = "0"
End If
if5 = myrs(5).ToString
If Trim(if5) = "" Then
if5 = "0"
End If
Newtext = iftitle + " (" + ifv + ") 5years(" + if5 + ")"
Newall = Replace(Newall, Oldtext, Newtext)
TextBox3.AppendText("更新项：" + Newtext + vbCrLf)
End While
myrs.Close()
updatecount = updatecount + 1
Label2.Text = "已更新" + updatecount.ToString + "项"
'MsgBox("更新")
Else
myrs.Close()
nocount = nocount + 1
Label1.Text = "已忽略" + nocount.ToString + "项"
'MsgBox("忽略")
End If
My.Application.DoEvents()
End If
Next
MsgBox("已对比" + ttl.ToString + "项，已更新至缓存")
Button4.Enabled = True
End Sub
Private Sub SaveFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles SaveFileDialog1.FileOk
IO.File.WriteAllText(SaveFileDialog1.FileName, Newall)
End Sub
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
SaveFileDialog1.ShowDialog()
End Sub
End Class
