.net OleDb 导入Excel文件读取数据内容

混口饭吃的码农

已于 2022-09-20 10:31:43 修改

阅读量286

点赞数

分类专栏： .net 后端文章标签： .net microsoft sql

于 2022-09-20 10:29:20 首次发布

本文链接：https://blog.csdn.net/weixin_44606182/article/details/126948131

版权

.net 后端专栏收录该内容

18 篇文章 0 订阅

订阅专栏

1：安装最新版 System.Data.OleDb
2：调用代码如下

	[HttpPost, HttpGet]
    public ObjectResult SelExcelData()
    {
        JObject jobjResult = new JObject();
        var dt = ExcelHelper.SelExcelData("\\\\192.168.2.1\\abc\\aaa.xlsx");
        if(dt != null)
        {
            for (var i=0;i<dt.Rows.Count;i++)
            {
                var r = dt.Rows[i][6].ToString(); 
            }
        }
        jobjResult.Add("DataTable", JsonConvert.SerializeObject(dt));
        return new ObjectResult(jobjResult.ToString());
    }

	/// <summary>
    /// 读取Excel文件
    /// </summary>
    /// <param name="url">\\\\192.168.2.1\\abc\\aaa.xlsx</param>
    public static DataTable SelExcelData(string url)
    {
        string getStr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={url};Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
        try
        {
            OleDbConnection conn = new OleDbConnection(getStr);
            string Sql = "SELECT * FROM [Sheet1$] ";
            OleDbCommand cmd = new OleDbCommand(Sql, conn);
            OleDbDataAdapter dpa = new OleDbDataAdapter(cmd);
            DataSet Des = new DataSet();
            dpa.Fill(Des);
            var data = Des.Tables[0];
            return data;
        }
        catch (Exception ex)
        {
            LoggerHelper.Error(ex, $"UpdateStationData->");
            return null;
        }
    }