比较C#三种方法实现读取Execl数据到数据库

最新推荐文章于 2024-09-09 09:10:35 发布

reder005

最新推荐文章于 2024-09-09 09:10:35 发布

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

分类专栏： .NET C# 文章标签： c# string excel exception microsoft properties

本文链接：https://blog.youkuaiyun.com/reder005/article/details/6131846

本文详细介绍了使用C#通过Microsoft.Office.Interop.Excel、ODBC和OLEDB三种方式读取Excel数据并导入数据库的过程。通过示例代码展示了每种方法的实现细节，并指出第一种方式效率较低，推荐使用ODBC或OLEDB方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

方式一：用微软提供的Microsoft.Office.Interop.Excel这个DLL，程序读取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
                {