C#中Excel数据导入

本文记录我在开发中用到的关于excel文件导入问题，记录分享出来方便后续使用。

代码块

读取导入的excel文件 并保存到数据库中

    /// <summary>    /// 文件读取    /// </summary>    public void FileUpload()    {        string result = "导入成功！";        string webPath = "/_data/";        string dir = HttpContext.Current.Server.MapPath("~" + webPath);        if (!System.IO.Directory.Exists(dir))        {            Directory.CreateDirectory(dir);//文件保存路径        }        //获取导入文件源        HttpPostedFile file = HttpContext.Current.Request.Files["file"];        string real_filename = DateTime.Now.ToString("yyyyMMddHHmmssfff") + "_" + file.FileName;        string savePath = dir + real_filename;        //保存导入的附件        file.SaveAs(savePath);        string col = "guid,code,name,sex";//数据库中列        string[] column = col.Split(',');        //导入数据到数据库        InsertTable(savePath, "0", column, ref result);    }    /// <summary>    /// 导入数据到数据库    /// </summary>    /// <param name="excelPath">excel文件访问路径</param>    /// <param name="sheetName">sheet索引 默认为0</param>    /// <param name="column">数据库中数据列</param>    /// <param name="result">返回结果</param>    public void InsertTable(string excelPath, string sheetName, string[] column, ref string result)    {        string msg = "";        try        {            //根据文件索引读取excel文件返回datatable数据            DataTable dt = ExcelData.GetExcelToDataTableBySheet(excelPath, sheetName);            //循环excel数据行            for (int i = 1; i < dt.Rows.Count; i++)            {                try                {                    IInsertDataSourceFace insert = new InsertSQL("Student");                    insert.DataBaseAlias = cConfig.Golden3C_AuthenticationCenter;                    for (int j = 0; j < column.Length; j++)//循环数据列                    {                        if (column[j] == "guid")                        {                            //数据列单独使用情况                            string guid = Guid.NewGuid().ToString();                            insert.AddFieldValue("guid", guid);                        }                        else                            insert.AddFieldValue(column[j], dt.Rows[i][j]);                    }                    insert.ExecuteNonQuery();                }                catch (Exception ex)                {                    msg += dt.Rows[i]["F1"].ToString() + "导入失败！/n";                    ExceptionManage.ExceptionHandle(ex);                }            }        }        catch (Exception ex)        {            msg += "excel文件读取失败！";            ExceptionManage.ExceptionHandle(ex);        }    }    /// <summary>    /// 读取excel文件内容返回datatable    /// </summary>    /// <param name="FileFullPath">文件路径</param>    /// <param name="SheetName">sheet名称</param>    /// <returns></returns>    public static DataTable GetExcelToDataTableBySheet(string FileFullPath, string SheetName)    {        DataSet ds = new DataSet();        int sheetIndex = int.Parse(SheetName);        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();        try        {            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件              conn = new System.Data.OleDb.OleDbConnection(strConn);            conn.Open();            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });            string[] strTableNames = new string[dtSheetName.Rows.Count];            for (int k = 0; k < dtSheetName.Rows.Count; k++)            {                if (k == sheetIndex)//判断  如果sheet的索引与传入值的索引一致  则导入                {                    //获取sheet的名称                    strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();                    string sheetName = "";                    int i = strTableNames[k].IndexOf('$');                    if (strTableNames[k].StartsWith("'"))                    {                        sheetName = strTableNames[k].Substring(1, i - 1);                    }                    else                    {                        sheetName = strTableNames[k].Substring(0, i);                    }                    string sql = string.Format("SELECT * FROM [{0}$]", sheetName);                    System.Data.OleDb.OleDbDataAdapter odda = new System.Data.OleDb.OleDbDataAdapter(sql, conn);                    odda.Fill(ds, SheetName);                    break;                }                else                {                    //Log.WriteLog("系统默认每个table中单个sheet表导入，请确保所导入的sheet表的索引为0");                }            }        }        catch (Exception ex)        {            ExceptionManage.ExceptionHandle(ex);        }        finally        {            conn.Close();        }        return ds.Tables[0];    }

备注：
excel文档数据格式为：

文章中代码读取后的数据格式为：

欢迎同仁批评指正。