C# 读取Excel数据 并对日期是数字的进行处理

//按钮事件

protected void btnQueryExcel_Click(object sender, EventArgs e)
    {
        DataSet ds; string strError;
        string SaveFilePath = Server.MapPath(Page.Request.ApplicationPath) + "//UpLoadExcel";
        if (!System.IO.Directory.Exists(SaveFilePath))
            System.IO.Directory.CreateDirectory(SaveFilePath);
        if (FileUpload1.FileName.Trim() =="")
        {
            alert("请选择要导入的Excel");
            return;
        }
        FileUpload1.SaveAs(SaveFilePath + "//" + FileUpload1.FileName);

        string Sql = "SELECT * FROM [CCS$];";
        bool bRet = ImportDataFromExcelToDs(out ds, SaveFilePath + "//" + FileUpload1.FileName, Sql, out strError);
        if (!bRet)
        {
            alert(strError);
            return;
        }
        if (ds.Tables.Count == 0 && ds.Tables[0].Rows.Count > 0)
        {
            alert("没有数据");
            return;
        }

       if (!QueryExcelData(ds, out strError))
       {
           alert(strError);
            return;
       }
     
    }

  /// <summary>
    /// 将DataSet当中的数据导入到Excel当中
    /// </summary>
    /// <param name="ds"></param>
    /// <param name="strError"></param>
    /// <returns></returns>
    private bool QueryExcelData(DataSet ds, out string strError)
    {
        gvClient.DataSource = null;
        gvClient.DataBind();
        strError = "";
        List<ClientStructure> datas = new List<ClientStructure>();
        try
        {
            #region 给对象赋值

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                DataRow dr = ds.Tables[0].Rows[i];
                ClientStructure data = new ClientStructure();

                //序号（展现用）
                data.SN = i + 1;
                //公司ID
                data.CorpID = CheckUser().CorpID;
                //客户名称（暂无用）
                data.ClientName = dr[0].ToString().Replace("/n","");

                if (data.ClientName == "")
                {
                    break;
                }
              
                //客户编码(根据客户编码查询客户ID)
                data.ClientKey = dr[1].ToString().Replace("/n", "");

                if (data.ClientKey == "")
                {
                    break;
                }
              
                //月份 查询数据格式为数字
                try
                {
                    data.RecordMonth = DateTime.FromOADate(Convert.ToInt32(dr[2].ToString())).ToString

("yyyy-MM");
                }
                catch
                {
                    data.RecordMonth = "";
                }

                //月份，查询数据格式为日期格式
                try
                {
                    if (data.RecordMonth=="")
                    {
                        data.RecordMonth = Convert.ToDateTime(dr[2].ToString()).ToString("yyyy-MM");
                    }
                }
                catch
                {
                    data.RecordMonth = "";
                }
                //将对象添加到集合当中
                datas.Add(data);
            }
            #endregion
        }
        catch (System.Exception e)
        {
            strError = e.Message;
            return false;
        }
        gvClient.DataSource = datas;
        gvClient.DataBind();



        ViewState["data"] = datas;
        return true;

        //ClientStructureManager manager = new ClientStructureManager();
        //return manager.AddClientStructureFromExcel(datas, out strError);

    }

 

private bool ImportDataFromExcelToDs(out System.Data.DataSet ds, string FileName, string Sql, out string strError)
    {
        ds = new DataSet();
        strError = "";
        string Connection = GetConn(FileName);
        if (Connection.Equals(""))
        {
            strError = "所选文件不是Excel";
            return false;
        }
        System.Data.OleDb.OleDbConnection OConnection = new System.Data.OleDb.OleDbConnection(Connection);
        try
        {
            System.Data.OleDb.OleDbCommand Ocmd = new System.Data.OleDb.OleDbCommand(Sql, OConnection);
            System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter(Ocmd);
            OConnection.Open();
            adp.Fill(ds);
            OConnection.Close();
        }
        catch
        {
            if (OConnection.State == ConnectionState.Open)
                OConnection.Close();
        }
        return true;
    }

    private string GetConn(string FileName)
    {
        int index = FileName.LastIndexOf(".");
        string suffix = FileName.Substring(index + 1);
        if (suffix.Equals("xls"))
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0;";
        else if (suffix.Equals("xlsx"))
            return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=Excel 12.0;";
        else
            return "";
    }