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;
  5.              Console.WriteLine("excelFilePath:" + excelFilePath);
  6.              m_ExcelFile.Workbooks.Open(excelFilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing
  7.                  , missing, missing, missing, missing, missing);
  8.              m_ExcelFile.Visible = false;
  9.              m_Workbook = m_ExcelFile.Workbooks[1];
  10.              m_Worksheet = (Excel.Worksheet)m_Workbook.ActiveSheet;
  11.             int clomn_Count = m_Worksheet.UsedRange.Columns.Count;
  12.             int row_Count = m_Worksheet.UsedRange.Rows.Count;
  13.         
  14.            
  15.             for (int i = 2; i < row_Count + 1; i++)//
  16.              {
  17.                 string lv_strSql;
  18.                 string lv_strSqlOne = "insert into user (";
  19.                 string lv_strSqlTwo = " value(";
  20.                 for (int j = 1; j < clomn_Count + 1; j++)
  21.                  {
  22.                     if (((Excel.Range)m_Worksheet.UsedRange.Cells[1, j]).Text.ToString() == "会员姓名" && ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString().Trim() != "")
  23.                      {
  24.                          lv_strSqlOne += "name,";
  25.                          lv_strSqlTwo += "'" + ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString() + "',";
  26.                      }
  27.                      .........//表格可能有好多列
  28.                     else if (((Excel.Range)m_Worksheet.UsedRange.Cells[1, j]).Text.ToString() == "累计积分" && ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString().Trim() != "")
  29.                      {
  30.                          lv_strSqlOne += "score,";
  31.                          lv_strSqlTwo += "'" + ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString() + "',";
  32.                      }
  33.                  }
  34.                  lv_strSqlOne += "create_date,sync_flag)";
  35.                  lv_strSqlTwo += "'" + DateTime.Now + "',0)";
  36.                  lv_strSql = lv_strSqlOne + lv_strSqlTwo;
  37.                  Console.WriteLine("lv_strSql:" + lv_strSql);
  38.                 try
  39.                  {
  40.                     int lv_ret = m_db.RunNoQuery(lv_strSql);//执行数据库插入操作。
  41.                  }
  42.                 catch (Exception ex)
  43.                  {
  44.                      Console.WriteLine(ex.Message);
  45.                  }
  46.             //关闭Excel相关对象
  47.              m_Worksheet = null;
  48.              m_Workbook = null;
  49.              m_ExcelFile.Quit();
  50.              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();
  43.                             if (read.GetName(i) == "会员姓名" && read.GetValue(i).ToString().Trim() != "")
  44.                              {
  45.                                  lv_strSqlOne += "name,";
  46.                                  lv_strSqlTwo += "'" + read.GetValue(i).ToString() + "',";
  47.                              }
  48.                              ............//Excel可能有多列
  49.                             else if (read.GetName(i) == "累计积分" && read.GetValue(i).ToString().Trim() != "")
  50.                              {
  51.                                  lv_strSqlOne += "score,";
  52.                                  lv_strSqlTwo += "'" + read.GetValue(i).ToString() + "',";
  53.                              }
  54.                          }
  55.                          lv_strSqlOne += "create_date,sync_flag)";
  56.                          lv_strSqlTwo += "'" + DateTime.Now + "',0)";
  57.                          lv_strSql = lv_strSqlOne + lv_strSqlTwo;
  58.                          Console.WriteLine("lv_strSql:" + lv_strSql);
  59.                         int lv_ret = m_db.RunNoQuery(lv_strSql);
  60.                      }
  61.                     catch (Exception ex)
  62.                      {
  63.                          Console.WriteLine(ex.Message);
  64.                      }                
  65.                  }
  66.                  read.Close();
  67.                  conn.Close();
  68.              }
  69.             catch (Exception e)
  70.              {
  71.                  MessageBox.Show(e.Message);
  72.              }  

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

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值