Biegral Blog

//Excel导入
    public DataTable ExcelImportTotImportSequenceNo(string filePath, string ExtensionName)
    {
        OleDbConnection olecon = null;
        OleDbDataAdapter oleda = null;
        string con = "";
        try
        {
            if (ExtensionName == ".xls")
                con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;IMEX=1'";
            else
                con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
            olecon = new OleDbConnection(con);
            olecon.Open();
            //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　
            DataTable dtSheetName = olecon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            oleda = new OleDbDataAdapter(string.Format("select * from [Sheet1$]"), olecon);
            DataTable dt = new DataTable();
            oleda.Fill(dt);
            //对列名 进行空格处理
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.Trim();
            }
            return dt;
        }
        catch (Exception ex)
        {
            oleda.Dispose();
            olecon.Dispose();
            olecon.Close();
            throw ex;
        }
        finally
        {
            oleda.Dispose();
            olecon.Dispose();
            olecon.Close();
        }
    

[Sheet1$]