C#读取Excel数据两种方式性能对比

方式一：程序读取Excel数据，代码如下：

              

1.              Excel.Application m_ExcelFile = new Excel.Application();
2.              Excel._Workbook m_Workbook;
3.              Excel._Worksheet m_Worksheet;
4.             object missing = System.Reflection.Missing.Value;
6.              Console.WriteLine("excelFilePath:" + excelFilePath);
7.              m_ExcelFile.Workbooks.Open(excelFilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing
8.                  , missing, missing, missing, missing, missing);
9.              m_ExcelFile.Visible = false;
10.              m_Workbook = m_ExcelFile.Workbooks[1];
11.              m_Worksheet = (Excel.Worksheet)m_Workbook.ActiveSheet;
12.             int clomn_Count = m_Worksheet.UsedRange.Columns.Count;
13.             int row_Count = m_Worksheet.UsedRange.Rows.Count;
14.         
15.            
16.             for (int i = 2; i < row_Count + 1; i++)//
17.              {
18.                 string lv_strSql;
19.                 string lv_strSqlOne = "insert into user (";
20.                 string lv_strSqlTwo = " value(";
21.                 for (int j = 1; j < clomn_Count + 1; j++)
22.                  {
23.                     if (((Excel.Range)m_Worksheet.UsedRange.Cells[1, j]).Text.ToString() == "会员姓名" && ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString().Trim() != "")
24.                      {
25.                          lv_strSqlOne += "name,";
26.                          lv_strSqlTwo += "'" + ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString() + "',";
27.                      }
28.                      .........//表格可能有好多列
29.                     else if (((Excel.Range)m_Worksheet.UsedRange.Cells[1, j]).Text.ToString() == "累计积分" && ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString().Trim() != "")
30.                      {
31.                          lv_strSqlOne += "score,";
32.                          lv_strSqlTwo += "'" + ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString() + "',";
33.                      }
34.                  }
35.                  lv_strSqlOne += "create_date,sync_flag)";
36.                  lv_strSqlTwo += "'" + DateTime.Now + "',0)";
37.                  lv_strSql = lv_strSqlOne + lv_strSqlTwo;
38.                  Console.WriteLine("lv_strSql:" + lv_strSql);
39.                 try
40.                  {
41.                     int lv_ret = m_db.RunNoQuery(lv_strSql);//执行数据库插入操作。
42.                  }
43.                 catch (Exception ex)
44.                  {
45.                      Console.WriteLine(ex.Message);
46.                  }
47.             //关闭Excel相关对象
48.              m_Worksheet = null;
49.              m_Workbook = null;
50.              m_ExcelFile.Quit();
51.              m_ExcelFile = null;

第二种方式：直接把Excel当作数据库，查询Excel的数据，代码如下：

1.              String source = null;
2.              OdbcConnection conn = null;
3.             string sql = "select * from [Sheet1$]";
4.             try
5.              {
6.                  source = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" + tbExcelFilePath.Text;
7.                  conn = new OdbcConnection(source);
8.                  conn.Open();
9.              }
10.             catch (OdbcException e)
11.              {
12.                 try
13.                  {
14.                      source = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + tbExcelFilePath.Text;
15.                      conn = new OdbcConnection(source);
16.                      conn.Open();
17.                  }
18.                 catch (OdbcException e1)
19.                  {
20.                      MessageBox.Show("请确认此文件没有被其它程序打开！");
21.                  }
22.              }
23.             try
24.              {
25.                  OdbcCommand cmd = new OdbcCommand(sql, conn);
26.                  OdbcCommand cmd1 = new OdbcCommand("select count(*) from [Sheet1$]", conn);
27.                  OdbcDataReader read = cmd.ExecuteReader();
28.                 int count = int.Parse(cmd1.ExecuteScalar().ToString());
29.                 int rn = 1;
30.                 while (read.Read())
31.                  {
32.                     try
33.                      {
34.                         if (m_stop) break;
35.                          rn++;
36.                         string lv_strSql;
37.                         string lv_strSqlOne = "insert into user (";
38.                         string lv_strSqlTwo = " value(";
39.                          String[] row = new String[read.FieldCount];
40.                         for (int i = 0; i < read.FieldCount; i++)
41.                          {
42.                              row[i] = read.GetValue(i).ToString();
44.                             if (read.GetName(i) == "会员姓名" && read.GetValue(i).ToString().Trim() != "")
45.                              {
46.                                  lv_strSqlOne += "name,";
47.                                  lv_strSqlTwo += "'" + read.GetValue(i).ToString() + "',";
48.                              }
49.                              ............//Excel可能有多列
50.                             else if (read.GetName(i) == "累计积分" && read.GetValue(i).ToString().Trim() != "")
51.                              {
52.                                  lv_strSqlOne += "score,";
53.                                  lv_strSqlTwo += "'" + read.GetValue(i).ToString() + "',";
54.                              }
56.                          }
57.                          lv_strSqlOne += "create_date,sync_flag)";
58.                          lv_strSqlTwo += "'" + DateTime.Now + "',0)";
59.                          lv_strSql = lv_strSqlOne + lv_strSqlTwo;
60.                          Console.WriteLine("lv_strSql:" + lv_strSql);
62.                         int lv_ret = m_db.RunNoQuery(lv_strSql);
64.                      }
65.                     catch (Exception ex)
66.                      {
67.                          Console.WriteLine(ex.Message);
68.                      }                
69.                  }
70.                  read.Close();
71.                  conn.Close();
72.              }
73.             catch (Exception e)
74.              {
75.                  MessageBox.Show(e.Message);
76.              }  

                 在效率上第一种方式比第二种慢很多，推荐使用第二种。

转自：http://hi.baidu.com/feiyangqingyun/blog/item/bd9dadda72b8add3b6fd48b2.html

收藏于 2010-06-28

来自于百度空间