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

来源：互联网发布：室内电脑设计软件编辑：程序博客网时间：2024/06/06 08:35

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

            Excel.Application m_ExcelFile = new Excel.Application();
            Excel._Workbook m_Workbook;
            Excel._Worksheet m_Worksheet;
            object missing = System.Reflection.Missing.Value;
            Console.WriteLine("excelFilePath:" + excelFilePath);
            m_ExcelFile.Workbooks.Open(excelFilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing
                , missing, missing, missing, missing, missing);
            m_ExcelFile.Visible = false;
            m_Workbook = m_ExcelFile.Workbooks[1];
            m_Worksheet = (Excel.Worksheet)m_Workbook.ActiveSheet;
            int clomn_Count = m_Worksheet.UsedRange.Columns.Count;
            int row_Count = m_Worksheet.UsedRange.Rows.Count;
        
           
            for (int i = 2; i < row_Count + 1; i++)// 
            {
                string lv_strSql;
                string lv_strSqlOne = "insert into user (";
                string lv_strSqlTwo = " value(";
                for (int j = 1; j < clomn_Count + 1; j++)
                {
                    if (((Excel.Range)m_Worksheet.UsedRange.Cells[1, j]).Text.ToString() == "会员姓名" && ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString().Trim() != "")
                    {
                        lv_strSqlOne += "name,";
                        lv_strSqlTwo += "'" + ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString() + "',";
                    }
                    .........//表格可能有好多列 
                    else if (((Excel.Range)m_Worksheet.UsedRange.Cells[1, j]).Text.ToString() == "累计积分" && ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString().Trim() != "")
                    {
                        lv_strSqlOne += "score,";
                        lv_strSqlTwo += "'" + ((Excel.Range)m_Worksheet.UsedRange.Cells[i, j]).Text.ToString() + "',";
                    }
                }
                lv_strSqlOne += "create_date,sync_flag)";
                lv_strSqlTwo += "'" + DateTime.Now + "',0)";
                lv_strSql = lv_strSqlOne + lv_strSqlTwo;
                Console.WriteLine("lv_strSql:" + lv_strSql);
                try
                {
                    int lv_ret = m_db.RunNoQuery(lv_strSql);//执行数据库插入操作。 
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            //关闭Excel相关对象 
            m_Worksheet = null;
            m_Workbook = null;
            m_ExcelFile.Quit();
            m_ExcelFile = null;

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

            String source = null;
            OdbcConnection conn = null;
            string sql = "select * from [Sheet1$]";
            try
            {
                source = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" + tbExcelFilePath.Text;
                conn = new OdbcConnection(source);
                conn.Open();
            }
            catch (OdbcException e)
            {
                try
                {
                    source = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + tbExcelFilePath.Text;
                    conn = new OdbcConnection(source);
                    conn.Open();
                }
                catch (OdbcException e1)
                {
                    MessageBox.Show("请确认此文件没有被其它程序打开！");
                }
            }
            try
            {
                OdbcCommand cmd = new OdbcCommand(sql, conn);
                OdbcCommand cmd1 = new OdbcCommand("select count(*) from [Sheet1$]", conn);
                OdbcDataReader read = cmd.ExecuteReader();
                int count = int.Parse(cmd1.ExecuteScalar().ToString());
                int rn = 1;
                while (read.Read())
                {
                    try
                    {
                        if (m_stop) break;
                        rn++;
                        string lv_strSql;
                        string lv_strSqlOne = "insert into user (";
                        string lv_strSqlTwo = " value(";
                        String[] row = new String[read.FieldCount];
                        for (int i = 0; i < read.FieldCount; i++)
                        {
                            row[i] = read.GetValue(i).ToString();
                            if (read.GetName(i) == "会员姓名" && read.GetValue(i).ToString().Trim() != "")
                            {
                                lv_strSqlOne += "name,";
                                lv_strSqlTwo += "'" + read.GetValue(i).ToString() + "',";
                            }
                            ............//Excel可能有多列 
                            else if (read.GetName(i) == "累计积分" && read.GetValue(i).ToString().Trim() != "")
                            {
                                lv_strSqlOne += "score,";
                                lv_strSqlTwo += "'" + read.GetValue(i).ToString() + "',";
                            }
                        }
                        lv_strSqlOne += "create_date,sync_flag)";
                        lv_strSqlTwo += "'" + DateTime.Now + "',0)";
                        lv_strSql = lv_strSqlOne + lv_strSqlTwo;
                        Console.WriteLine("lv_strSql:" + lv_strSql);
                        int lv_ret = m_db.RunNoQuery(lv_strSql);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }                
                }
                read.Close();
                conn.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }  

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