C#使用OLEDB的方式读写Excel

蓉蓉不哭

已于 2024-05-30 14:14:05 修改

阅读量1.1k

点赞数 25

分类专栏： C# 文章标签： c# mvc

于 2024-03-26 21:00:00 首次发布

本文链接：https://blog.csdn.net/weixin_73037857/article/details/136882009

版权

C# 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在C#中读取Excel的方式有很多种，下面使用OLEDB的方式：

优点：

1.简单快速,能够操作高版本Excel

2.可以直接使用SQL语句操作数据

3.不需要安装Office Excel

缺点：

1.Excel的数据量不能过大，当内存不够时会抛出内存溢出的异常。

2.不能灵活操作Excel，如设置单元格字体，颜色，格式等。

本文采用的是mvc的写法来进行

一：前端视图部分

1.首先在视图里引入jquery脚本文件，然后建立两个input标签，type一个为file，一个为button：

file用于选取导入的Excel，button用于提交到后台处理

<script src="~/Scripts/jquery-3.4.1.min.js"></script>

<input type="file" name="xlsfile" id="xlsfile" value="" />
<input type="button" name="import" id="import" value="import" />

2.然後使用Ajax的方式把文件提交到後台處理：

相应的代码都会有对应的注解

<script>
    $("#import").on("click", function () {
        // 创建一个 FormData 对象，用于将数据以键值对形式发送到服务器
        var formData = new FormData();
        //将 id 为 "xlsfile" 的 input 元素中选择的文件添加到 FormData 中
        formData.append("file", $("#xlsfile")[0].files[0]);
        // 将选定文件的文件名添加到 FormData 中
        formData.append("filename", $("#xlsfile")[0].files[0].name);
        $.ajax({
            //后台处理上传文件的URL
            url: "@Url.Action("import", "oledbDemo")",
            //必须使用POST的方式上传文件
            type: "POST",
            //提交到后台的数据
            data: formData,
             //不设置内容类型
            contentType: false,
            //不对数据进行序列化处理
            processData: false,
            //禁用缓存
            cache: false,
            //同步请求，等待请求完成后再执行后续操作
            async: false,
            //请求成功后的回调函数
            success: function (r) {
                //解析返回的 JSON 数据
                var json = JSON.parse(r)
                //在控制台输出返回的 JSON 数据，方便查看数据传递过来的数据
                console.log(json)
                //判断传递过来的参数
                if (json.state == "OK") {
                    alert("导入文件成功！！！")
                } else {
                    alert("导入失败，错误的地方为：" + json.alert)
                }
            }
        })
    })
</script>

二：后台控制器部分

1.创建方法，获取前端传入过来的参数：

“HttpPostedFileBase”类是用于表示通过HTTP POST请求上传的单个文件，接收前端传递过来的file值和filename值

public Object import(HttpPostedFileBase file, string filename){
    
}

2.对前端传递过来的参数进行处理：

保存文件在指定的路径：

//把文件保存在指定的路径
file.SaveAs(Server.MapPath("/Content/Excel/" + filename));

注：保存的路径需要相对路径，不能使用绝对路径，否则会报错，下图为绝对路径的报错情况

创建链接字符串：这里就是使用oledb的方式来读写Excel

//连接字符串
string strCon;
strCon = "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("/Content/Excel/" + filename) + "; Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\";";

"Provider= Microsoft.ACE.OLEDB.12.0;":

这部分指定了使用的 OLE DB 提供程序，这里使用的是 Microsoft Office 12.0 Access Database Engine，可以使用的Excel格式为.xlsx和.xls

而“Microsoft.Jet.OLEDB.4.0”主要使用的格式为.xls

Data Source=: 指定数据源，即 Excel 文件的路径。这里使用了 Server.MapPath 方法将相对路径转换为服务器上的绝对路径

"Extended Properties="Excel 12.0;HDR=YES;IMEX=1;": 这部分是指定了连接的扩展属性，包括使用的 Excel 版本（此处为 Excel 12.0，即 Excel 2007及以上版本）

“HDR=YES”为是否包含表头（ YES第一行是表头，NO不包含表头）、

以及强制使用文本格式导入：

IMEX=0，开启的Excel文档只能用来做“写入”用途

IMEX=1，开启的Excel文档只能用来做“读取”用途

IMEX=2，可“写入”也可“读取”

这里使用的为读取Excel。

注：在使用oledb的时候，特别需要注意的是自己的电脑是属于32位的应用程式还是64位的应用程式

否則就可能会出出以下报错情況

解决办法：

(1).进入IIS找到应用程序池，找到自己的网站，并打开高级设置

(2).修改应用程式：

3.与数据库进行链接：

//引入oledb类，是用于与数据库进行链接的类
OleDbConnection con = new OleDbConnection(strCon);
//打开链接
con.Open();

//返回Excel的架构，包括各个sheet的名称，类型，创建时间，修改时间
DataTable dtSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

4.获取Excel的表格数据：

因为我只需要一个表格的数据，所以这里做了判断

其中“FilterDatabase”的解释可以查看：https://blog.csdn.net/cmhdl521/article/details/105512818

//获取第0个表（索引从0开始）
string strTableName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();

//判斷 表名中是否包含(FilterDatabase)
int TableNum = strTableName.IndexOf("FilterDatabase");
if (TableNum >= 1)
{
    strTableName = dtSheetName.Rows[1]["TABLE_NAME"].ToString();
}

5.查询获取到的Excel数据：

//查询Excel的数据
OleDbDataAdapter da = new OleDbDataAdapter($"select * from [{strTableName}]", con);
//将数据表转为集合
DataSet ds = new DataSet();
//把數據填充到dateset
da.Fill(ds);
//关闭链接
con.Close();

查询数据的时候，可以根据自己的需求去进行查询，逻辑跟SQL Server是一样的。没什么区别。

举几个例子：这个是举例数据

(1).判断非空：

OleDbDataAdapter da = new OleDbDataAdapter($"select * from [{strTableName}] where Sex is not null", con);

得到的结果为李四性别列为空，所以排除掉。

(2).当某一列数据有问题的时候找到并显示原因：

一般在SQL Server里是这样写的：

SELECT [Desc]=CASE when LEN(Name)>2 THEN N'名字不能大于2字符' ELSE N'符合' END, * FROM dbo.Student

但是在oledb就是另外的写法：IIF(条件 , '条件为真' , '条件为假')

OleDbDataAdapter da = new OleDbDataAdapter($"select *,IIF(Age>17,'年龄不能大于17岁','正确') AS Remark FROM [{strTableName}]", con);

这样的写法逻辑就跟三元运算的逻辑相当，当条件正确的时候返回的是左边的条件，否则为右边的条件

结果：

注：在使用第一行数据当做表头的时候，如果某列有空格的时候，需要使用[]包裹起来，这样才会被识别为一个整体，否则会出现错误。

OleDbDataAdapter da = new OleDbDataAdapter($"select * FROM [{strTableName}] order by [Cliss id] desc", con);

根据cliss id降序排序，结果为：

好了，到这里oledb的使用就已经结束了，接下来就要根据自己的需求进行下一步了

如果是把数据传送到页面的话：

有几个表就获取几个表的数据传送到视图即可。

//获取Dateset的第一个表
DataTable dt = ds.Tables[0];
//传值到前端进行展示
ViewBag.dt = dt;

如果是需要把数据导入到数据库的话：

直接定义需要添加字段的变量，然后循环添加即可。

//添加到数据库
var Name = "";
var Sex = "";
var Age = "";
var Classid = "";
string sql = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
     Name = dt.Rows[i]["Name"].ToString();
     Sex = dt.Rows[i]["Sex"].ToString();
     Age = dt.Rows[i]["Age"].ToString();
     Classid = dt.Rows[i]["Class id"].ToString();

     sql = $"insert into Student (Name, Sex, Age, ClassId) values (N'{Name}','{Sex}','{Age}','{Classid}')";
     DBHelper.executeNonQuery(sql);
}

6.使用完毕后删除文件

因为我这里的写法是把文件保存到项目的路径里，所以为了防止导入的文件过多，每一次执行完毕后需要删除掉指定路径里的文件。

//使用完后删除文件
string dirPath = Server.MapPath("/Content/Excel/" + filename);
System.IO.File.Delete(dirPath);

后台完整的代码如下：

        public Object import(HttpPostedFileBase file, string filename)
        {
            var result = new { state = "OK", alert = "成功" };
            try
            {
                //把文件保存在指定的路径
                file.SaveAs(Server.MapPath("/Content/Excel/" + filename));
                //连接字符串
                string strCon;
                strCon = "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("/Content/Excel/" + filename) + "; Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\";";
                
                //引入oledb类，是用于与数据库进行链接的类
                OleDbConnection con = new OleDbConnection(strCon);
                //打开链接
                con.Open();

                //返回Excel的架构，包括各个sheet的名称，类型，创建时间，修改时间
                DataTable dtSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                //获取第0个表（索引从0开始）
                string strTableName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();

                //判斷 表名中是否包含(FilterDatabase) 解释：https://blog.csdn.net/cmhdl521/article/details/105512818
                int TableNum = strTableName.IndexOf("FilterDatabase");
                if (TableNum >= 1)
                {
                    strTableName = dtSheetName.Rows[1]["TABLE_NAME"].ToString();
                }

                //查询Excel的数据
                OleDbDataAdapter da = new OleDbDataAdapter($"select * FROM [{strTableName}] order by [Class id] desc", con);
                //将数据表转为集合
                DataSet ds = new DataSet();
                //把數據填充到dateset
                da.Fill(ds);
                //关闭链接
                con.Close();

                //获取Dateset的第一个表
                DataTable dt = ds.Tables[0];
                //传值到前端进行展示
                ViewBag.dt = dt;

                //添加到数据库
                var Name = "";
                var Sex = "";
                var Age = "";
                var Classid = "";
                string sql = "";
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    Name = dt.Rows[i]["Name"].ToString();
                    Sex = dt.Rows[i]["Sex"].ToString();
                    Age = dt.Rows[i]["Age"].ToString();
                    Classid = dt.Rows[i]["Class id"].ToString();

                     sql = $"insert into Student (Name, Sex, Age, ClassId) values (N'{Name}','{Sex}','{Age}','{Classid}')";
                    DBHelper.executeNonQuery(sql);
                }

                //使用完后删除文件
                string dirPath = Server.MapPath("/Content/Excel/" + filename);
                System.IO.File.Delete(dirPath);
            }
            catch (Exception ex)
            {
                result = new { state = "Error", alert = ex.Message };
            }

            return JsonConvert.SerializeObject(result);
        }

全文完。