Excel导入这样写可以吗？

Question

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.OleDb;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;public partial class Default3 : System.Web.UI.Page
{
    OleDbConnection olecon = null;
    OleDbDataAdapter oledata = null;
    DataTable datatable = new DataTable();
    DataSet dataset = new DataSet();    protected void Page_Load(object sender, EventArgs e)
    {
    }    //其中ImportXlsToData是使用OleDb链接Excel文件并将其数据导入Dataset中；
    private void ImportXlsData(string filename)
    {
        if (filename == String.Empty)
        {
            throw new ArgumentNullException("上传文件失败！");
        }
        string oleDBConnString = String.Empty;
        oleDBConnString = "Provider=Microsoft.ACE.OLEDB.12.0; ";
        oleDBConnString += " Data Source= ";
        oleDBConnString += filename;
        oleDBConnString += ";Extended Properties=Excel 12.0";        olecon = new OleDbConnection(oleDBConnString);
        olecon.Open();        datatable = olecon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (datatable != null && datatable.Rows.Count > 0)
        {
            datatable.TableName = datatable.Rows[0]["TABLE_NAME"].ToString();
        }        string strsql = "select * from [" + datatable.TableName + "]";
        oledata = new OleDbDataAdapter(strsql, olecon);
        oledata.Fill(dataset, "datatable");
        oledata.Dispose();
        olecon.Close();
        olecon.Dispose();
        //datagrid 显示数据
        this.DataGrid1.DataSource = dataset.Tables["datatable"];
        this.DataGrid1.DataBind();
    }
    //UpdateXls是上传Xls文件
    private string UpdataXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
    {
        string orifilenme = string.Empty;
        string uploadfilepath = string.Empty;
        string modifyfilename = string.Empty;
        string path = 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(".") + path;                //新文件名
                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();
                }
                orifilenme = uploadfilepath + modifyfilename;
                //如果存在,删除文件
                if (File.Exists(orifilenme))
                {
                    File.Delete(orifilenme);
                }
                // 上传文件
                inputfile.PostedFile.SaveAs(orifilenme);
            }
            else
            {
                throw new Exception("没有选择Excel文件!");
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return orifilenme;    }    private void Save(string p, string p_2, string p_3, string p_4, string p_5, string p_6, string p_7)
    {
        throw new Exception("");
    }
    protected void Button1_Click(object sender, EventArgs e)
    {        string filename = string.Empty;
        try
        {
            filename = UpdataXls((System.Web.UI.HtmlControls.HtmlInputFile)fileup);//上传XLS文件
            ImportXlsData(filename);//将XLS文件内容导入
            //删除文件
            if (filename != string.Empty && File.Exists(filename))
            {
                File.Delete(filename);//删除上传的文件
            }
            string conn1 = "User ID=sa;Data Source=localhost;Password=sa;Initial Catalog=BooksDB;Provider=SQLOLEDB.1;";
            OleDbConnection thisconnection1 = new OleDbConnection(conn1);
            thisconnection1.Open();
            int count = dataset.Tables["[Sheet1$]"].Rows.Count;            for (int i = 0; i < count; i++)
            {
                string id, id_1, id_2, id_3;
                id = dataset.Tables["[Sheet1$]"].Rows[i]["id"].ToString();
                id_1 = dataset.Tables["[Sheet1$]"].Rows[i]["id_1"].ToString();
                id_2 = dataset.Tables["[Sheet1$]"].Rows[i]["id_2"].ToString();
                id_3 = dataset.Tables["[Sheet1$]"].Rows[i]["id_3"].ToString();
                string excelsql = "insert into ExceltoDate(id,id_1,id_2,id_3) values ('" + id + "','" + id_1 + "','" + id_2 + "','" + id_3 + "') ";
                OleDbCommand mycommand1 = new OleDbCommand(excelsql, thisconnection1);
                mycommand1.ExecuteNonQuery();
            }
            Response.Write("更新成功");
            thisconnection1.Close();
        }
        catch (Exception ex)
        {
            this.lblMessage.Text = ex.Message;//错误显示
        }
    }
}请问我Excel导入这样在写可以吗？
  它为什么会写不进数据库 还报[DBNETLIB][ConnectionOpen (Connect()).]SQL Server 不存在或拒绝访问。这个错误啊？
  请大家帮我一下

Answer 1

你怎么连接SQL Server用OleDbConnection ？？？

Answer 2

给你段代码看看，简单化的
            //省略上传文件的检查等                        //上传的文件名
            string strFileName = System.IO.Path.GetFileName(FileUpload_XXXX.PostedFile.FileName);            //保存上传文件
            //保存的路径与页面文件目录相同
            FileUpload_XXXX.PostedFile.SaveAs(Server.MapPath(strFileName));            //读取文件，加载数据
            //建立连接,读取表格中的数据
            DataSet XXXXDataSet = new DataSet();            string strCon;            //获得文件扩展名
            string strFileExtName = GetFileExtendName(strFileName);            OleDbConnection XXXXConn = null;
            OleDbDataAdapter XXXXCommand = null;            try
            {
                if (strFileExtName == "xls")
                {
                    strCon = @" Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + Server.MapPath(strFileName) + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";///建立连接,地址为strFIlePath传递的地址
                }
                else
                {
                    strCon = @" Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + Server.MapPath(strFileName) + "; Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";///建立连接,地址为strFIlePath传递的地址
                }
                XXXXConn = new OleDbConnection(strCon);
                string strCom = "SELECT * FROM [Sheet1$]";///SQL操作语句,就是说:取得所有数据从data                XXXXConn.Open();
                XXXXCommand = new OleDbDataAdapter(strCom, XXXXConn);
                XXXXDataSet = new DataSet();//建立新的数据集NetworkDataSet
                XXXXCommand.Fill(XXXXDataSet, "[Sheet1$]");//填充数据集
                XXXXConn.Close();
                XXXXCommand.Dispose();
            }
            catch (Exception ex)
            {
                //写入异常日志
                
                ShowMessage("读取文件信息失败！请撤销Excel的保护并重新执行导入！");
                return;
            }
            finally
            {
                if (XXXXConn != null)
                {
                    XXXXConn.Close();
                }                if (XXXXCommand != null)
                {
                    XXXXCommand.Dispose();
                }                //删除上传的导入Excel文件
                System.IO.File.Delete(Server.MapPath(strFileName));
            }            //调用业务层方法写入数据库
              //对XXXXDataSet.Table[0]处理就可以了，写入数据表