C#中Excel数据导入

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文档数据格式为：

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

欢迎同仁批评指正。