导入EXCEL到DataTable

最新推荐文章于 2024-01-18 12:41:22 发布

程序人生lwp

最新推荐文章于 2024-01-18 12:41:22 发布

阅读量323

点赞数

文章标签： excel ui 前端

本文链接：https://blog.csdn.net/weixin_42992927/article/details/131792297

版权

将execl导入到datatable当中

    /// <summary>
    /// 导入EXCEL到DataTable
    /// </summary>
    /// <param name="fileName">Excel全路径文件名</param>
    /// <returns>导入成功的DataTable</returns>
    public System.Data.DataTable ImportExcel(string fileName)
    {
        Excel.Application xlApp = new Excel.Application();
        if (xlApp == null)
        {
            returnStatus = -1;
            returnMessage = "无法创建Excel对象，可能您的计算机未安装Excel！";
            return null;
        }

        Workbook workbook;
        try
        {
            workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
        }
        catch
        {
            returnStatus = -1;
            returnMessage = "Excel文件处于打开状态，请保存关闭！";
            return null;
        }

        var workSheetsCount = workbook.Worksheets.Count;
        var sheetSet = new string[workSheetsCount];
        var arrayList = new ArrayList();
        for (int i = 1; i <= workSheetsCount; i++)
        {
            sheetSet[i - 1] = ((Worksheet)workbook.Worksheets[i]).Name;
        }

        workbook.Close(null, null, null);
        xlApp.Quit();
        if (workbook != null)
        {
            Marshal.ReleaseComObject(workbook);
            workbook = null;
        }
        if (xlApp != null)
        {
            Marshal.ReleaseComObject(xlApp);
            xlApp = null;
        }
        GC.Collect();

        var dataSet = new DataSet();
        var dataTable = new System.Data.DataTable();
        //string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0";
        string connStr = " Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + fileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
        using (OleDbConnection odConn = new OleDbConnection(connStr))
        {
            odConn.Open();
            OleDbDataAdapter odda;
            string sql = "select * from [" + sheetSet[0] + "$] ";
            odda = new OleDbDataAdapter(sql, odConn);
            odda.Fill(dataSet, sheetSet[0]);
            odda.Dispose();
            dataTable = dataSet.Tables[0];
            odConn.Close();
            odConn.Dispose();
        }
        return dataTable;
    }