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

Qcls2009 2009-12-30 04:26:44
我做一个将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,"指定列"];
}

但是速度很慢,怎么解决呢?

...全文
1506 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
Smark_du 2012-06-08
  • 打赏
  • 举报
 回复
最快的有find 但是达不到要求。。只能遍历。。郁闷，难道没有返回一个数组的 findall？
mao44mao 2011-12-19
  • 打赏
  • 举报
 回复
端到端
renstars 2010-01-03
  • 打赏
  • 举报
 回复
我也正在找这个
rxslove1314 2010-01-02
  • 打赏
  • 举报
 回复
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();
}
}
}
小明aa 2009-12-31
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 assky124 的回复:]
oledb
[/Quote]

同意
peterb 2009-12-31
  • 打赏
  • 举报
 回复
dts,当作数据源来操作
Qcls2009 2009-12-31
  • 打赏
  • 举报
 回复
但是怎么用oledb获取工作薄中的表名呢,比如一个EXCEL中有多个表:sheet1,sheet3,digitoa三个表,我要把这三个表名读到一个combox控件中,方便选择表,用什么函数呢?
daichenghua 2009-12-31
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 wxm3630478 的回复:]
只是纯粹读数据  把Excel作为一个数据库 那样来查询 比用 Office类快些

就像3楼同志那么搞OdbcConnection会快点
[/Quote]
同意
柳晛 2009-12-31
  • 打赏
  • 举报
 回复
OLEDB会比Excel快很多，因为不需要打开Office Excel。
wuyq11 2009-12-30
  • 打赏
  • 举报
 回复
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();}
tianliang1 2009-12-30
  • 打赏
  • 举报
 回复
想个办法把excel中那列读出来，放在一个HashTable中保存，然后在查询。。。效率很高。。。
wxm3630478 2009-12-30
  • 打赏
  • 举报
 回复
只是纯粹读数据 把Excel作为一个数据库 那样来查询 比用 Office类快些

就像3楼同志那么搞OdbcConnection会快点
ggg2009 2009-12-30
  • 打赏
  • 举报
 回复
好多高手啊，我要好好学习
wangminqi 2009-12-30
  • 打赏
  • 举报
 回复
前面弄错了，那是导出的，下面是导入的


        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
wangminqi 2009-12-30
  • 打赏
  • 举报
 回复 
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
assky124 2009-12-30
  • 打赏
  • 举报
 回复
oledb
Qcls2009 2009-12-30
  • 打赏
  • 举报
 回复
是否可用多线程?怎么用?
Excel数据透视表实战视频课程【你学得会】
数据透视表是Excel的重要内容，是Excel交叉数据报表，是Excel分析数据的工具。之所以称为数据透视表，是因为它可以动态地改变布局，以便按照不同方式分析数据。所以它综合了排序。筛选。分类汇总的特点。杨老师通过《Excel数据透视表实战视频课程【你学得会】》的课程从入门基础，常见应用，其他案例这三个方面着手，讲解包括数据透视表的组成，创建，使用，案例等，深刻地教你使用Excel数据透视表
C#

110,567

社区成员

642,567

社区内容

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • Web++
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告

让您成为最强悍的C#开发者

试试用AI创作助手写篇文章吧

+ 用AI写文章