c#遍历Excel中的数据什么方式最快?
我做一个将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,"指定列"];
}
但是速度很慢,怎么解决呢?
[解决办法]
- C# code
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(); } }
[解决办法]
前面弄错了，那是导出的，下面是导入的
- C# code
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); } }
[解决办法]
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();
}
}
}