导入Excel到数据库

Question

我要做一个导入excel 客户端上传 服务器读写xls 然后验证数据正确性 第一列日期 第二列入：银-19273求正解

Answer 1

步骤:
1.将excel上传至服务器,
2.读取excel的数据并返回DataTable,
3.循环DataTable行,进行验证并录入.

Answer 2

让他们先把Excel导入一个DataGrid，在DataGrid验证数据方便很多，然后在DataGrid存入到数据库也方便很多……毕竟操作Excel是一件又占资源又易出问题的事……

Answer 3

我想楼主要的是代码吧！这个你可以看看
public partial class ERP_OMC_Rpt_ReceivablesToExcel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {    }
    protected void btnSearch_click(object sender, EventArgs e)
    {
        string filename = string.Empty;
        try
        {
            filename = UpdateXls(this.File1);//上传XLS文件
            ImportXlsToData(filename);//将XLS文件内容导入
            //删除文件
            if (filename != string.Empty && File.Exists(filename))
            {
                File.Delete(filename);//删除上传的文件
            }
        }
        catch (Exception ex)
        {
            this.WriteErrorXML(ex);//出错处理
           
        }
        finally
        {
          
          
        }
    }    private void WriteErrorXML(Exception ex)
    {
        throw new NotImplementedException();
        throw ex;
    }    //// <summary>
        /// 从Excel提取数据--》Dataset
        /// </summary>
        /// <param name="filename">Excel文件路径名</param>  
        private void ImportXlsToData(string fileName)
        {
            try
            {
                if (fileName == string.Empty) 
                { 
                    throw new ArgumentNullException("上传文件失败！");
                }
                //
                string oleDBConnString = String.Empty; 
                oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;"; 
                oleDBConnString += "Data Source="; 
                oleDBConnString += fileName; 
                oleDBConnString += ";Extended Properties=Excel 8.0;"; 
                //
                OleDbConnection oleDBConn = null; 
                OleDbDataAdapter oleAdMaster = null; 
                DataTable m_tableName=new DataTable();
                DataSet ds=new DataSet();                 oleDBConn = new OleDbConnection(oleDBConnString); 
                oleDBConn.Open(); 
                m_tableName=oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);                 if (m_tableName != null && m_tableName.Rows.Count > 0) 
                {                     m_tableName.TableName =m_tableName.Rows[0]["TABLE_NAME"].ToString();                 } 
                string sqlMaster; 
                sqlMaster=" SELECT *  FROM ["+m_tableName.TableName+"]"; 
                oleAdMaster=new OleDbDataAdapter(sqlMaster,oleDBConn); 
                oleAdMaster.Fill(ds,"m_tableName"); 
                oleAdMaster.Dispose();
                oleDBConn.Close();
                oleDBConn.Dispose();                //测试是否提取数据
                //this.Datagrid1.DataSource = ds.Tables["m_tableName"];
                //this.Datagrid1.DataBind();
                //将Dataset中数据导入SQL
                //AddDatasetToSQL(ds);            }
            catch(Exception ex)
            {
                throw ex;
            }
        }        //上传Excel文件
        private string UpdateXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
        {
            string orifilename = string.Empty;
            string uploadfilepath = string.Empty;
            string modifyfilename = string.Empty;
            string fileExtend = "" ;//文件扩展名
            int fileSize = 0;//文件大小            try
            {
                if(inputfile.Value != string.Empty)
                {
                    //得到文件的大小
                    fileSize = inputfile.PostedFile.ContentLength;
                    if(fileSize == 0 )
                    {
                        throw new Exception("找不到该文件！");
                    }
                    //得到扩展名
                    fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
                    if(fileExtend.ToLower() != "xls")
                    {
                        throw new Exception("请确认您所导入的文件是否EXCEL文件！！");
                    }
                    //路径
                    uploadfilepath = System.Web.HttpContext.Current.Server.MapPath(".");
                    //新文件名
                    modifyfilename = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() 
                        + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() 
                        + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() 
                        + DateTime.Now.Millisecond.ToString();
                    modifyfilename += "."+inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
                    //判断是否有该目录
                    System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
                    if (!dir.Exists)
                    {
                        dir.Create();
                    }
                    orifilename = uploadfilepath+modifyfilename;
                    //如果存在,删除文件
                    if(File.Exists(orifilename))
                    {
                        File.Delete(orifilename);
                    }
                    // 上传文件
                    inputfile.PostedFile.SaveAs(orifilename);
                }
                else
                {
                    throw new Exception("没有选择Excel文件!");
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
            return orifilename;
        }        //将Dataset的内容导入SQL
        //private bool AddDatasetToSQL(DataSet pds)
        //{
        //    int ic,ir;
        //    ic = pds.Tables[0].Columns.Count;
        //    if (pds.Tables[0].Columns.Count < 7)
        //    {
        //        throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
        //    }
        //    ir = pds.Tables[0].Rows.Count;
        //    if (pds != null && pds.Tables[0].Rows.Count > 0)
        //    {
        //        for (int i = 0;i < pds.Tables[0].Rows.Count;i++)
        //        {
        //            Save(pds.Tables[0].Rows[i][0].ToString(),pds.Tables[0].Rows[i][1].ToString(),
        //                pds.Tables[0].Rows[i][2].ToString(),pds.Tables[0].Rows[i][3].ToString(),
        //                pds.Tables[0].Rows[i][4].ToString(),pds.Tables[0].Rows[i][5].ToString(),
        //                pds.Tables[0].Rows[i][6].ToString());
        //        }
        //    }
        //    else
        //    {
        //        throw new Exception("导入数据为空！");
        //    }
        //    return true;
        //}
}
 

Answer 4

服务器读xls 到datatable 验证 处理 用程序做

Answer 5

sql2005有这个功能,直接导入到数据库中

Answer 6

恩 sql2005可以直接导入的.....