c#遍历Excel中的数据什么方式最快?

Question

   我做一个将Excel中的某列汉字转换成拼音后,放到同一Excel中指定的某列,这就需要先遍历数据,但是EXCEL有60000多行,整个的遍历要花相当长的时间.我是这么做的:
         Microsoft.Office.Interop.Excel.Application appExcel = null;//创建EXCEL对像
        Microsoft.Office.Interop.Excel.Workbook WorkBook;//创建工作薄
        Microsoft.Office.Interop.Excel.Worksheet ws = null;//创建工作表
  //遍历数据:
   for(int i=1;i<=ws.rows.count;i++)
   {
     //如果有值就读出来
     txt=ws.cell[i,"指定列"];
    }但是速度很慢,怎么解决呢?

Answer 1

string filename = "";
            if (DialogResult.OK == saveFileDlg.ShowDialog())
            {
                filename = saveFileDlg.FileName;
                //execl 导出
                String source = null;
                OdbcConnection conn = null;                try
                {
                    source = "Driver={Microsoft Excel Driver (*.xls)};FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\""+filename +"\";DBQ=" + filename ;
                    conn = new OdbcConnection(source);
                    conn.Open();
                }
                catch
                {
                    try
                    {
                        source = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\""+filename +"\";DBQ=" + filename ;
                        conn = new OdbcConnection(source);
                        conn.Open();
                    }
                    catch
                    {
                        MessageBox.Show("请确认此文件没有被其它程序打开！");
                        return;
                    }
                }                if (dataSet1.Tables.Count <= 0) return;
                try
                {
                    string s = "";
                    string f = "";
                    string[] numlx = new string[] { "System.Int32", "System.Int16", "System.Int64", "System.Decimal", "System.Single", "System.Double" };                    for (int i = 0; i < dataSet1.Tables[0].Columns.Count; i++)
                    {
                        if (numlx.Contains(dataSet1.Tables[0].Columns[i].DataType.ToString()))
                        {
                            s = s + ",[" + dataSet1.Tables[0].Columns[i].ColumnName + "] NUMBER";
                        }
                        else
                        {
                            s = s +",[" +dataSet1.Tables[0].Columns[i].ColumnName + "] TEXT";
                        }
                        f = f + ",[" + dataSet1.Tables[0].Columns[i].ColumnName+"]";
                    }
                    s = "CREATE TABLE " + dataSet1.Tables[0].TableName + "(" + s.Substring(1) + ")";
                    f = "insert into " + dataSet1.Tables[0].TableName + "(" + f.Substring(1) + ") values(";
                    OdbcCommand cmd1 = new OdbcCommand(s, conn);
                    cmd1.ExecuteNonQuery();
                    foreach (DataRow dr in dataSet1.Tables[0].Rows)
                    {
                        string sz = "";
                        for (int i = 0; i < dataSet1.Tables[0].Columns.Count; i++)
                        {
                            if (dr[i] != DBNull.Value)
                            {
                                sz = sz + ",'" + dr[i].ToString() + "'";
                            }
                            else
                            {
                                sz = sz + ",null";
                            }
                        }
                        sz = sz.Substring(1) + ")";
                        cmd1.CommandText = f + sz;
                        cmd1.ExecuteNonQuery();
                    }
                    MessageBox.Show("导出完毕！   ", "导出", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch(Exception e1)
                {
                    MessageBox.Show("发生错误：  " + e1.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    conn.Close();
                }
            }
http://www.mybuffet.cn/html/0/165.htm
http://www.mybuffet.cn

Answer 2

前面弄错了，那是导出的，下面是导入的        private void button1_Click(object sender, System.EventArgs e)
        {
            if (filename.Trim() == "")
            {
                MessageBox.Show("请选择要导入数据库的Excel文件！  ", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (tablename.Trim() == "")
            {
                MessageBox.Show("请选择要导入到的数据库表！  ", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            
            String source = null;
            OdbcConnection conn = null;
            try
            {
              source = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" + filename ;
              conn = new OdbcConnection(source);
              conn.Open();
            }
            catch 
            {
              try
              {
                  source = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + filename;
                  conn = new OdbcConnection(source);
                  conn.Open();
              }
              catch 
              {
                  MessageBox.Show("请确认此文件没有被其它程序打开！");
                  return;
              }
            }
            try
            {
                string Excelsql_Count="select count(*) from [Sheet1$]";
                if (textBox2.Text.Trim() != "")
                    Excelsql_Count = "select count(*) from [" + textBox2.Text.Trim() + "$]";
                OdbcCommand cmd1 = new OdbcCommand(Excelsql_Count, conn);
                int count = int.Parse(cmd1.ExecuteScalar().ToString());//不算第一行
                progressBar1.Value = 0;
                progressBar1.Maximum = count;                string sql = "select * from [Sheet1$]";
                if (textBox2.Text.Trim() != "")
                    sql = "select * from [" + textBox2.Text.Trim() + "$]";
                OdbcCommand cmd = new OdbcCommand(sql, conn);
                OdbcDataReader read = cmd.ExecuteReader();
                try
                {
                            while (read.Read())
                            {
..................................
                                selectcmd.CommandText = "insert into " + tablename + "(" + zdm + ") values(" + zdz + ")";
                                selectcmd.ExecuteNonQuery();
                                progressBar1.Value = progressBar1.Value + 1;
                                progressBar1.Update();
............................................................
                                i++;
                            }
 
                            tx.Commit();
                            progressBar1.Value = progressBar1.Maximum;
                            progressBar1.Update();
                            MessageBox.Show("导入完毕！   ", "导入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception e1)
                {
                    tx.Rollback();
                    tx.Dispose();
                    MessageBox.Show("第" + i + "行导入失败！错误：  " + e1.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    read.Close();
                    conn.Close();
                    sqlConnection1.Close();
                }
            }
            catch(Exception e1)
            {
                MessageBox.Show("发生错误：  " + e1.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
http://www.mybuffet.cn

Answer 3

只是纯粹读数据  把Excel作为一个数据库 那样来查询 比用 Office类快些就像3楼同志那么搞OdbcConnection会快点

Answer 4

想个办法把excel中那列读出来，放在一个HashTable中保存，然后在查询效率很高

Answer 5

DataSet ds = new DataSet();
    string strConn = ("Provider=Microsoft.Jet.OleDb.4.0;" + "data source=") + Path + ";Extended Properties=Excel 8.0;";
    using (OleDbConnection Conn = new OleDbConnection(strConn)) {
        Conn.Open();
        string strSql = "Select * From [Sheet1$]";
        OleDbCommand Cmd = new OleDbCommand(strSql, Conn);
        OleDbDataAdapter sqlada = new OleDbDataAdapter();
        sqlada.SelectCommand = Cmd;
        sqlada.Fill(ds);
        Conn.Close();}

Answer 6

OLEDB会比Excel快很多，因为不需要打开Office Excel。

Answer 7

但是怎么用oledb获取工作薄中的表名呢,比如一个EXCEL中有多个表:sheet1,sheet3,digitoa三个表,我要把这三个表名读到一个combox控件中,方便选择表,用什么函数呢?

Answer 8

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.Threading;
using System.IO;
using System.Data.OleDb;namespace _229a
{
    public partial class ucExecl : UserControl
    {
        public ucExecl()
        {
            InitializeComponent();
            btnCreatePying.Click += new EventHandler(btnCreatePying_Click);
        }
        Microsoft.Office.Interop.Excel.Application appExcel = null;//创建EXCEL对像
        Microsoft.Office.Interop.Excel.Workbook WorkBook;//创建工作薄
        Microsoft.Office.Interop.Excel.Worksheet ws = null;//创建工作表
        Microsoft.Office.Interop.Excel.Range rangedata;        #region 加载事件
        private void UserControl1_Load(object sender, EventArgs e)
        {
            for (int i = 1; i <= 256; i++)
            {
                cmbSave.Items.Add(i.ToString());
            }
        }
        #endregion        #region 浏览
        private void btnLL_Click(object sender, EventArgs e)
        {
            openFileDialog1.Filter = "(*.xls)|*.xls";//对话框只读取EXCEL文件夹
            openFileDialog1.FileName = "";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                txtFileName.Text = openFileDialog1.FileName;
                try
                {
                    cmbWorksheet.Items.Clear();                    appExcel = new Microsoft.Office.Interop.Excel.Application();//实例化EXCEL对像
                    WorkBook = appExcel.Workbooks.Open(txtFileName.Text, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//实例化工作薄                    #region  将工作表的名称读到cmbworksheet中
                    cmbWorksheet.Items.Add("请选择");
                    for (int i = 1; i <= WorkBook.Worksheets.Count; i++)
                    {
                        ws = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets[i];
                        cmbWorksheet.Items.Add(ws.Name.ToString());                    }
                    cmbWorksheet.SelectedIndex = 0;
                    #endregion
                }
                catch
                { }
                finally
                {
                    appExcel.Quit();
                    appExcel = null;
                }
            }
        }
        #endregion        #region 在一个comBox中选择工作表,并读出相应表的列头到另一个COMboxk中
        private void cmbWorksheet_SelectedValueChanged(object sender, EventArgs e)
        {
            cmbWorkli.Items.Clear();
            if (cmbWorksheet.Text == "请选择")
            {
                return;
            }
            try
            {
                appExcel = new Microsoft.Office.Interop.Excel.Application();//实例化EXCEL对像
                WorkBook = appExcel.Workbooks.Open(txtFileName.Text, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//实例化工作薄
                //获取工作表
                ws = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets[int.Parse(cmbWorksheet.SelectedIndex.ToString())];
                #region 读取工作表中的列头到cmbWorkli控件中
                cmbWorkli.Items.Add("请选择字段");
                for (int i = 1; i <= ws.UsedRange.Columns.Count; i++)
                {
                    string str = ((Microsoft.Office.Interop.Excel.Range)ws.UsedRange.Cells[1, i]).Text.ToString();
                    cmbWorkli.Items.Add(str);
                }
                cmbWorkli.SelectedIndex = 0;
                #endregion
            }
            catch
            { }
            finally
            {
                appExcel.Quit();
                if (appExcel != null)
                {
                    appExcel = null;
                }
            }
        }
        #endregion        //int row = 0, colSave = 0, colSelect = 0;        void loadSouce()
        {
            try
            {                //连接EXCEL数据源
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileName.Text + ";Extended Properties=" + "\"" + "Excel 8.0;HDR=Yes;IMEX=1" + "\"";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                /*
                 如果用户把sheet表名改了就是报下面的错：
                'sheet1$' 不是一个有效名称。请确认它不包含无效的字符或标点，且名称不太长。 
                如果可以动态获得Excel中各个sheet的名称能动态的选中返回哪个sheet的数据，
                即使用户更改了名称也不怕，完全可以把所有sheet的名称列出来供用户选择: 
                下面就把解决问题的代码列出来，主要用到了OleDbConnection.GetOleDbSchemaTable()方法
                 */
                //OleDbDataAdapter myCommand = null;
                //从指定的表明查询数据,可先把所有表明列出来供用户选择
                if (cmbWorksheet.Text == "请选择")
                    return;
                if (cmbWorkli.Text == "请选择字段")
                    return;
                string strExcel = "select id," + cmbWorkli.Text + " from [" + cmbWorksheet.Text + "]";
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                DataSet myDataSet = new DataSet();
                try
                {
                    myCommand.Fill(myDataSet);
                    MessageBox.Show(myDataSet.Tables[0].Rows.Count.ToString());
                    dataGridView1.DataSource = myDataSet.Tables[0];
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);                }            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }        void btnCreatePying_Click(object sender, EventArgs e)
        {            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileName.Text + ";Extended Properties=" + "\"" + "Excel 8.0;HDR=Yes;IMEX=1" + "\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();            if (cmbWorksheet.Text == "请选择")
            {
                MessageBox.Show("请选择工作表");
                return;
            }
            if (cmbWorkli.Text == "请选择字段")
            {
                MessageBox.Show("请选择要操作的列");
                return;
            }
            try
            {
                appExcel = new Microsoft.Office.Interop.Excel.Application();//实例化EXCEL对像
                WorkBook = appExcel.Workbooks.Open(txtFileName.Text, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//实例化工作薄                //获取工作表
                int rowcount = 0;//记录一列中有多少条记录
                ws = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets[int.Parse(cmbWorksheet.SelectedIndex.ToString())];
                rangedata = ws.get_Range("H2", "H100");
                rowcount = ws.UsedRange.Rows.Count;
                MessageBox.Show("共" + rowcount + "条记录");                MessageBox.Show(dataGridView1.Rows.Count.ToString ());
                string[] ConvertDigital = new string[rowcount];                OleDbCommand cmd = new OleDbCommand();
                
                cmd.Connection = conn;
                for (int j = 0; j < dataGridView1.Rows.Count; j++)
                {
                    cmd.CommandText = "update [" + cmbWorksheet.Text + "] set "+cmbWorkli .Text+"='" + Hz2Py.Convert(dataGridView1.Rows[j].Cells[cmbWorkli.Text].Value.ToString()) + "' where id='" + dataGridView1.Rows[j].Cells["id"].Value.ToString() + "'";
                    cmd.ExecuteNonQuery();
                    //ConvertDigital[j] = Hz2Py.Convert(dataGridView1.Rows[j].Cells[cmbWorkli.Text].Value.ToString());
                    //MessageBox.Show(ConvertDigital [j ]);
                }
                ////设置新保存列的列头
                //((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, int.Parse(cmbSave.Text.ToString())]).Value2 = "简拼";
                ////将选择的列转换成拼音后保存
                //for (int i = 2; i <= rowcount; i++)
                //{
                //    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[i, int.Parse(cmbSave.Text.ToString())]).Value2 = Hz2Py.Convert(((Microsoft.Office.Interop.Excel.Range)ws.Cells[i, int.Parse(cmbWorkli.SelectedIndex.ToString())]).Text.ToString());
                //}                //WorkBook.Save();
                //MessageBox.Show("保存成功");
            }
            catch
            { }
            finally
            {                appExcel.Quit();
                if (appExcel != null)
                {
                    appExcel = null;
                }
            }
        }        private void cmbWorkli_SelectedValueChanged(object sender, EventArgs e)
        {
            loadSouce();
        }
    }
}

Answer 9

最快的有find 但是达不到要求。。只能遍历。。郁闷，难道没有返回一个数组的 findall？