工具——excel上传到数据库

来源：互联网发布：妙笔生花绘画软件编辑：程序博客网时间：2024/06/08 10:59

上传并保存excel 使用FileUpload（asp控件）

string strName = FileUpload1.PostedFile.FileName;//使用fileupload控件获取上传文件的文件名if (strName != "")//如果文件名存在{    bool fileOK = false;    int i = strName.LastIndexOf(".");//获取。的索引顺序号，在这里。代表图片名字与后缀的间隔    string kzm = strName.Substring(i);//获取文件扩展名的另一种方法 string fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();    string newName = Guid.NewGuid().ToString();//生成新的文件名，保证唯一性    string xiangdui = @"~\AssetsManager\upload\";//设置文件相对网站根目录的保存路径 ，~号表示当前目录，在此表示根目录下的images文件夹    string juedui = Server.MapPath("~\\AssetsManager\\upload\\");//设置文件保存的本地目录绝对路径，对于路径中的字符“＼”在字符串中必须以“＼＼”表示，因为“＼”为特殊字符。或者可以使用上一行的给路径前面加上＠    string newFileName = juedui + newName + kzm;    if (FileUpload1.HasFile)//验证 FileUpload 控件确实包含文件    {        String[] allowedExtensions = { ".xls", ".xlsx" };        for (int j = 0; j < allowedExtensions.Length; j++)        {            if (kzm == allowedExtensions[j])            {                fileOK = true;            }        }    }    if (fileOK)    {        try        {            // 判定该路径是否存在            if (!Directory.Exists(juedui))                Directory.CreateDirectory(juedui);            // 1.文件上传            FileUpload1.PostedFile.SaveAs(newFileName);            UpdateInfo(newFileName);// 更新            Page.RegisterStartupScript(Guid.NewGuid().ToString(), "<script>window.parent.$.weeboxs.close();</script>");        }        catch (Exception ex)        {            MessageBox.show(this, "上传文件失败！", "");        }    }    else    {        MessageBox.show(this, "只能够上传.xls .xlsx格式的文件！", "");    }}else{    MessageBox.show(this, "请上传文件！", "");}

上传并保存excel 使用HTML控件

/// <summary>/// 上传Excel文件/// </summary>/// <param name="inputfile">上传的控件名</param>/// <returns></returns>private string UpLoadXls(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("导入的Excel文件大小为0，请检查是否正确！");        }        //得到扩展名        fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);        if (fileExtend.ToLower() != "xls" && fileExtend.ToLower() != "xlsx")        {            throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");        }        //路径        uploadfilepath = Server.MapPath("~/uploadxls");        //新文件名        modifyfilename = System.Guid.NewGuid().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;}

将excel文件转换成DataSet

//// <summary>/// 从Excel提取数据--》Dataset/// </summary>/// <param name="filename">Excel文件路径名</param>private void ImportXlsToData(string fileName){    try    {        if (fileName == string.Empty)        {            throw new ArgumentNullException("Excel文件上传失败！");        }        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();        AddDatasetToSQL(ds, 11);    }    catch (Exception ex)    {        throw ex;    }}

将DataSet中的数据导入数据库

/// <summary>/// 将Dataset的数据导入数据库/// </summary>/// <param name="pds">数据集</param>/// <param name="Cols">数据集列数</param>/// <returns></returns>private bool AddDatasetToSQL(DataSet pds, int Cols){    int ic, ir;    ic = pds.Tables[0].Columns.Count;    if (pds.Tables[0].Columns.Count < Cols){    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++)    {        if (code.GetTable("select * from TT_PreSelection where percode ='"+ pds.Tables[0].Rows[i][0].ToString() + "'").Rows.Count == 0)        {            TT_PreSelectionEntity pse = new TT_PreSelectionEntity();            pse.percode = pds.Tables[0].Rows[i][0].ToString();//项目编号            pse.pername = pds.Tables[0].Rows[i][1].ToString();//项目名称            pse.xm_type = pds.Tables[0].Rows[i][2].ToString();//项目类型            pse.xm_zbdw = pds.Tables[0].Rows[i][3].ToString();//招标单位            pse.perarea = pds.Tables[0].Rows[i][4].ToString();//项目位置            pse.xm_zxmcode = pds.Tables[0].Rows[i][5].ToString();//主项目名称            pse.xm_lxbm = pds.Tables[0].Rows[i][6].ToString();//立项部门            pse.xm_djrq = pds.Tables[0].Rows[i][7].ToString();//登记日期            pse.xm_lxr = pds.Tables[0].Rows[i][8].ToString();//联系人            pse.xm_lxtel = pds.Tables[0].Rows[i][9].ToString();//联系电话            pse.xm_xmgk = pds.Tables[0].Rows[i][10].ToString();//项目概况            pse.xm_drr = UserCookieInfo.ur_name;// 导入人            pse.xm_drtime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");//导入时间            int id = TT_PreSelection_BLLSub.Create_TT_PreSelectionInsert(pse);            #region 操作日志            sysoperatelogEntity sysoplog = new sysoperatelogEntity();            sysoplog.so_operate = "导入项目编号:[" + pds.Tables[0].Rows[i][0].ToString() + "]";            sysoplog.so_model = "文件导入";            sysoplog.so_userid = UserCookieInfo.ur_id;            sysoplog.so_username = UserCookieInfo.ur_name + "[" + UserCookieInfo.ur_phone + "]";            sysoplog.so_optime = DateTime.Now;            sysoperatelog_BLLSub.Create_sysoperatelogInsert(sysoplog);            #endregion            oknum++;        }            else        {            errernum++;        }    }}    else    {    throw new Exception("导入数据为空！");    }    return true;}

删除无用的上传表格

if (filename != string.Empty && System.IO.File.Exists(filename))    {        filename = filename.Replace("\\","\\\\");        if (System.IO.File.Exists(filename))        {            System.IO.File.Delete(filename);//删除上传的XLS文件        }    }

0 0