exceltodatabase.aspx.cs

来自「该服务平台解决了计算机网络与移动网络之间信息交换问题」· CS 代码 · 共 333 行

using System;
using System.Data;
using System.Configuration;
using System.Collections;
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 Common;

public partial class UserManagement_ExcelToDataBase : System.Web.UI.Page
{
    private Security.RulesManagement rulesManagement;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            rulesManagement = new Security.RulesManagement();
            int rolesID = 0;
            try
            {
                HttpCookie rolesCookie = Request.Cookies["RolesID"];
                //rolesID = int.Parse(rolesCookie.Value);
                rolesID = int.Parse(Session["RolesID"].ToString());
                if (rulesManagement.ValidUserRules(rolesID, 18))
                {
                    FillComboBox();
                }
                else
                {
                    Response.Redirect("../none.aspx");
                }
            }
            catch
            {
                Response.Redirect("../none.aspx");
            }


        }
    }

    private void FillComboBox()
    {
        DataAccess.TableAccessor.CountyVillage countyVillage = new DataAccess.TableAccessor.CountyVillage();
        cbUserTeam.DataTextField = "AreaName";
        cbUserTeam.DataValueField = "AreaID";
        cbUserTeam.DataSource = countyVillage.GetAreaInfo();
        cbUserTeam.DataBind();
        cbUserTeam.Items.Insert(0, "");
    }
    protected void cbUserTeam_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (cbUserTeam.SelectedValue != "")
        {
            int userTeamID = int.Parse(cbUserTeam.SelectedValue);
            DataAccess.TableAccessor.UserConporation userCorporation = new DataAccess.TableAccessor.UserConporation();
            cbDept.DataTextField = "CorporationName";
            cbDept.DataValueField = "CorporationID";
            cbDept.DataSource = userCorporation.GetMostCorporationByAreaID(userTeamID);
            cbDept.DataBind();
            cbDept.Items.Insert(0, "");
        }
    }
    protected void btnExcelFileDown_Click(object sender, EventArgs e)
    {
        int userTeam = 0;
        int deptID = 0;
        if (cbUserTeam.SelectedValue == "")
        {
            //Response.Write("<script language='javascript'>alert('请选择群组！');</script>");
            //return;
            this.textLabel.Text = "<script language='javascript'>alert('请选择群组！');</script>";
            return;
        }
        if (cbDept.SelectedValue == "")
        {
            //Response.Write("<script language='javascript'>alert('请选择部门单位！');</script>");
            //return;
            this.textLabel.Text = "<script language='javascript'>alert('请选择部门单位！');</script>";
            return;
        }
        userTeam = int.Parse(cbUserTeam.SelectedValue);
        deptID = int.Parse(cbDept.SelectedValue);

        String excelPath = Server.MapPath(@".\UpLoadFiles\" + "ExcelFile.xls");
        DataAccess.ExcelTransfer excelTransfer = new DataAccess.ExcelTransfer(excelPath);
        if (excelTransfer.InsertDataToExcel(userTeam, deptID, 100) > 0)
        {
            String url = "UpLoadFiles/ExcelFile.xls";
            //HttpResponse response = HttpContext.Current.Response;
            //string js = "<script language=javascript>window.open('{0}');</script>";
            //js = string.Format(js, url);
            //response.Write(js);
            Response.Redirect("UpLoadFiles/ExcelFile.xls");
        }
        else
        {
            this.textLabel.Text = "<script language='javascript'>alert('下载文件失败！');</script>";
            //Response.Write("<script language='javascript'>alert('下载文件失败！');</script>");
            return;
        }
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        //base.VerifyRenderingInServerForm(control);
    }
    private void ShowExcelFile(String excelPathName, String excelTableName)
    {
        DataAccess.ExcelTransfer excelTransfer = new DataAccess.ExcelTransfer(excelPathName, excelTableName);
        DataTable dt = excelTransfer.GetExcelFileData();
        gvDownExcel.DataSource = dt;
        gvDownExcel.DataBind();
    }
    public void UploadFile(object sender, EventArgs E)
    {
        //检查上传文件不为空
        if (myFile.PostedFile != null)
        {
            string nam = myFile.PostedFile.FileName;
            //取得文件名(抱括路径)里最后一个"."的索引
            int i = nam.LastIndexOf(".");
            if (i == -1)
            {
                this.textLabel.Text = "<script language='javascript'>alert('上传文件不为空！');</script>";
                return;
            }
            //取得文件扩展名
            string newext = nam.Substring(i);
            //这里我自动根据日期和文件大小不同为文件命名,确保文件名不重复
            DateTime now = DateTime.Now;
            string newname = now.Year.ToString() + now.Month.ToString() + now.Day.ToString() + now.Hour.ToString() +
                now.Minute + now.Second.ToString() + myFile.PostedFile.ContentLength.ToString();

            //保存文件到你所要的目录,这里是IIS根目录下的uploadfiles目录
            //注意: 我这里用Server.MapPath()取当前文件的绝对目录.
            String excelPath = Server.MapPath(@".\UpLoadFiles\" + newname + newext);
            myFile.PostedFile.SaveAs(excelPath);

            //得到这个文件的相关属性:文件名,文件类型,文件大小
            tbFileName.Text = myFile.PostedFile.FileName;
            tbFileType.Text = myFile.PostedFile.ContentType;
            tbFileSize.Text = myFile.PostedFile.ContentLength.ToString();
            gvDownExcel.Visible = true;
            try
            {
                ShowExcelFile(excelPath, tbExcelTableName.Text);
             //   LabelTitle.Visible = false;
            }
            catch
            {
                this.textLabel.Text = "<script language='javascript'>alert('上传数据失败！');</script>";
                //Response.Write("<script language='javascript'>alert('上传数据失败！');</script>");
                return;
            }
        }
        
    }
    protected void btnImportData_Click(object sender, EventArgs e)
    {
        if (gvDownExcel.Rows.Count <= 0)
        {
            this.textLabel.Text = "<script>alert('并没有数据可供上传！');</script>";
            //Response.Write("<script language='javascript'>alert('并没有数据可供上传！');</script>");
             return;
        }
        Common.Model.UserInfo userInfoData = new Common.Model.UserInfo();

        for (int i = 0; i < gvDownExcel.Rows.Count; i++)
        {
            
            
                if (gvDownExcel.Rows[i].Cells[2].Text != "&nbsp;")
                {
                    DataRow row = userInfoData.Tables[Common.Model.UserInfo.TABLE_USER_INFO].NewRow();

                    if (gvDownExcel.Rows[i].Cells[0].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_AREA_ID] = gvDownExcel.Rows[i].Cells[0].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_AREA_ID] = "";

                    if (gvDownExcel.Rows[i].Cells[1].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_COUNTY_ID] = gvDownExcel.Rows[i].Cells[1].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_COUNTY_ID] = "";
                   
                    if (gvDownExcel.Rows[i].Cells[2].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_MOBILE] = gvDownExcel.Rows[i].Cells[2].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_MOBILE] = "";
                    if (gvDownExcel.Rows[i].Cells[3].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_TRADE_ID] = gvDownExcel.Rows[i].Cells[3].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_TRADE_ID] = "";
                   

                

                    if (gvDownExcel.Rows[i].Cells[4].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_NAME] = gvDownExcel.Rows[i].Cells[4].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_NAME] = "";



                    string sex = gvDownExcel.Rows[i].Cells[5].Text;
                    if (sex == "1" || sex == "0")
                    {
                        row[Common.Model.UserInfo.COLUMNS_SEX] = sex;
                    }
                    else
                    {
                        row[Common.Model.UserInfo.COLUMNS_SEX] = 2;
                    }
                   

                    if (gvDownExcel.Rows[i].Cells[6].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_BIRTHDAY] = gvDownExcel.Rows[i].Cells[6].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_BIRTHDAY] = "";


                    if (gvDownExcel.Rows[i].Cells[7].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_RACE] = gvDownExcel.Rows[i].Cells[7].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_RACE] = "";



                    if (gvDownExcel.Rows[i].Cells[8].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_IDCARD] = gvDownExcel.Rows[i].Cells[8].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_IDCARD] = "";


                    if (gvDownExcel.Rows[i].Cells[9].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_JIGUAN] = gvDownExcel.Rows[i].Cells[9].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_JIGUAN] = "";

                    if (gvDownExcel.Rows[i].Cells[10].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_HUKOU] = gvDownExcel.Rows[i].Cells[10].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_HUKOU] = "";

                    if (gvDownExcel.Rows[i].Cells[11].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_JOINTIME] = gvDownExcel.Rows[i].Cells[11].Text;
                    else

                        row[Common.Model.UserInfo.COLUMNS_JOINTIME] = "";
              

                    if (gvDownExcel.Rows[i].Cells[12].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_SCHOOL_AGE] = gvDownExcel.Rows[i].Cells[12].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_SCHOOL_AGE] = "";
                    if (gvDownExcel.Rows[i].Cells[13].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_SCHOOL] = gvDownExcel.Rows[i].Cells[13].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_SCHOOL] = "";

                    

                    if (gvDownExcel.Rows[i].Cells[14].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_SPECIALITY] = gvDownExcel.Rows[i].Cells[14].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_SPECIALITY] = "";

                   

                    if (gvDownExcel.Rows[i].Cells[15].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_HOMEADDRESS] = gvDownExcel.Rows[i].Cells[15].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_HOMEADDRESS] = "";


                    if (gvDownExcel.Rows[i].Cells[16].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_DUTY] = gvDownExcel.Rows[i].Cells[16].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_DUTY] = "";



                    if (gvDownExcel.Rows[i].Cells[17].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_USER_DEFINE_ID] = gvDownExcel.Rows[i].Cells[17].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_USER_DEFINE_ID] = "";

                   


                    
                 

                    if (gvDownExcel.Rows[i].Cells[18].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_NOWADDRESS] = gvDownExcel.Rows[i].Cells[18].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_NOWADDRESS] = "";

                    if (gvDownExcel.Rows[i].Cells[19].Text != "&nbsp;")
                        row[Common.Model.UserInfo.COLUMNS_BEIZHU] = gvDownExcel.Rows[i].Cells[19].Text;
                    else
                        row[Common.Model.UserInfo.COLUMNS_BEIZHU] = "";


                   

                    userInfoData.Tables[Common.Model.UserInfo.TABLE_USER_INFO].Rows.Add(row);
                }
            
        }
        DataAccess.TableAccessor.UserInfo userInfoAccessor = new DataAccess.TableAccessor.UserInfo();
        int insertRows = userInfoAccessor.ImportUserInfoData(userInfoData);
        if (insertRows > 0)
        {
            this.textLabel.Text = "<script language='javascript'>alert('上传数据成功！');</script>";
            //Response.Write("<script language='javascript'>alert('上传数据成功！');</script>");
        }
        else
        {
            //Response.Write("<script language='javascript'>alert('上传数据失败！');</script>");
            this.textLabel.Text = "<script>windows.location.href='ExcelToDataBase.aspx'</script>";
            string goUrl = string.Format("<script>windows.location.href='ExcelToDataBase.aspx'</script>");
            Response.Write(goUrl);
        }

    }
}