求教基础问题，麻烦大大们指教一下

[rumusanfen]

同学有两个文件，需要将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

[黄瓜黄瓜]

引用 3 楼 rumusanfen 的回复:

引用 2 楼 Net_Java_dram 的回复:1.首先select放到循环去 在循环外面用DataTable接收 然后每次在DataTable中去找. 2.我看你只用到了select结果的第5列和第6列，不需要select * 3.是否可以减少循环次数呢(如果不是每行都需要替换的话，先对spilt的数组筛选一次) 1.额，请问如何在DataTable查找比……

我又看了下你的代码 原来你把Select 放循环里面 是因为where 条件依赖循环 你可以在循环外面查出所有放在DataTable或集合中 在循环里在查找 代码很简单的么

[rumusanfen]

唉，每次循环都好在8k多条里select，吐血啊... 对textbox的替换操作是不是也是很费时呢？6w行...有没有把数组组合成字符串的函数啊？

[rumusanfen]

Dim objCmdSelect As New OleDbCommand("SELECT * FROM [JCR2012$] where [Abbreviated Journal Title (linked to journal information)]= '" + iftitle + "'", objConn) Dim myrs As OleDbDataReader = objCmdSelect.ExecuteReader() 请问这里如何分离定义与赋值 能否把dim放到for循环外去

[黄瓜黄瓜]

1.首先select放到循环去 在循环外面用DataTable接收 然后每次在DataTable中去找. 2.我看你只用到了select结果的第5列和第6列，不需要select * 3.是否可以减少循环次数呢(如果不是每行都需要替换的话，先对spilt的数组筛选一次)

[rumusanfen]

引用 2 楼 Net_Java_dram 的回复:

1.首先select放到循环去 在循环外面用DataTable接收 然后每次在DataTable中去找. 2.我看你只用到了select结果的第5列和第6列，不需要select * 3.是否可以减少循环次数呢(如果不是每行都需要替换的话，先对spilt的数组筛选一次)

1.额，请问如何在DataTable查找比对？我是小白，求代码... 2.嗯，这个我改一下，只取3个字段看看 3.要不要替换必须查找对比后才知道，如果不是txt和excel，而是两个表，那我内联查询就可以解决问题了...

[rumusanfen]

优化后的代码，大家看如何进一步提升效率

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

[rumusanfen]

仔细调试了

引用 4 楼 Net_Java_dram 的回复:

我又看了下你的代码 原来你把Select 放循环里面 是因为where 条件依赖循环 你可以在循环外面查出所有放在DataTable或集合中 在循环里在查找 代码很简单的么 ……

我仔细调试了一下，发现最大的问题也不是replace，而是TextBox3.Text=xxx，把TextBox3.Text赋值给一个变量，然后变量用replace，不用输出到屏幕，就不会卡了，速度勉强能接受。TextBox3.Text的输出过程才是罪魁祸首... 不知道一直用replace和重新逐行累加，这两种哪一种更高效...

[SadlyCodes]

拿分来

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++;
                }
            }
        }
    }
}