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.             }  

 

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