EXCEL 文件导入SQL 中

public static void ImportFromExcel(string strFilePath,string strCmdText)
        {
            DataTable dt = FillDataSet(strFilePath).Tables[0];
            for (int j=0;j<dt.Rows.Count;j++)
            {
                string strNewCmdText = strCmdText;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    strNewCmdText = strNewCmdText +""+ dt.Rows[j][i].ToString()+"‘,";
                }
                strNewCmdText = strNewCmdText.Remove((strNewCmdText.Length)-1) + ")";
                sqlHelper.sqlExcute(strNewCmdText);
            }
            
        }

 

 

 public static DataSet FillDataSet(string FilePath)
        {
            DataSet ds = null;
            OleDbConnection conn = null;
            string strFileExt = string.Empty;
            if (!File.Exists(FilePath))
            {
                throw new Exception("Excel文件不存在!");
            }
            else      //    \d\f.xls    (4,8-(4))
            {
                strFileExt = FilePath.Substring(FilePath.LastIndexOf(.), FilePath.Length - FilePath.LastIndexOf(.));
                switch (strFileExt)
                {
                    case ".xls":
                        conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";" + "Extended Properties=‘Excel 8.0; HDR = YES; IMEX = 1‘");
                        break;
                    case ".xlsx":
                        conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";" + "Extended Properties=‘Excel 12.0; HDR = YES; IMEX = 1‘");
                        break;
                    default:
                        break;
                }
                try
                {
                    conn.Open();
                    OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter("select * from [Sheet1$]", conn);
                    ds = new DataSet();
                    oleDbDataAdapter.Fill(ds);

                }
                catch(OleDbException ex)
                {
                    MessageBox.Show(ex.Message);
                }

                conn.Close();
                return ds;

            }
        }

 

Excel 文件导入到SQL库

1、Excel 文件数据先绑定到dataSet

2、读取DataSet  文件到SQL中

 

EXCEL 文件导入SQL 中

上一篇:操作mysql数据库


下一篇:关于asp.net MVC 中的TryUpdateModel方法