Excel 导入导出 .Net

一.excel的导出功能

public partial class OutExcel : System.Web.UI.Page
    {
        UsersOperate useroperate = new UsersOperate();
        string strWhere = " and UserStatus=1";
        int iCurrentPage = 1;

        protected void Page_Load(object sender, EventArgs e)
        {
            StreamWriter sw = null;
            try
            {
                string filePath = Server.MapPath("/csv");
                if (!Directory.Exists(filePath))
                {
                    Directory.CreateDirectory(filePath);
                }

                string name = filePath + "/"+DateTime.Now.ToString("yyyy-MM-dd")+".csv";

                if (File.Exists(name))
                {
                    File.Delete(name);
                }

                sw = new StreamWriter(name, true, System.Text.Encoding.UTF8, 200);
                StringBuilder sbTitle = new StringBuilder();
                sbTitle.Append("用户名称,密码,角色名");
                sw.WriteLine(sbTitle.ToString());

                IList<UsersInfo> userslist = useroperate.GetList(10000, iCurrentPage, strWhere, "UserId asc", "UserId desc"); //获取数据信息
                RolesInfo rinfo = new RolesInfo();
                RolesOperate rop = new RolesOperate();

                foreach (UsersInfo UInfo in userslist)
                {
                    StringBuilder sbContent = new StringBuilder();
                    string str = string.Empty;
                    rinfo = rop.GetObject(UInfo.RoleId);
                    if (rinfo.Id == Convert.ToInt32(UInfo.RoleId))
                    {
                        str = rinfo.RoleName;
                    }
                    sbContent.Append(UInfo.UserLogin + "," + UInfo.UserPassword + "," + str);
                    sw.WriteLine(sbContent.ToString());
                }
                sw.Close();

                if (System.IO.File.Exists(name))
                {
                    string strFileName = System.IO.Path.GetFileName(name);
                    System.IO.FileInfo fileInfo = new System.IO.FileInfo(name);
                    long FileSize = fileInfo.Length;

                    Page.Response.ContentType = "APPLICATION/OCTET-STREAM";
                    Page.Response.AddHeader("Content-length", FileSize.ToString());
                    Page.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8));
                    Page.Response.WriteFile(name);
                    Response.Flush();
                    Response.End();
                }
                else
                {
                    Response.Write("文件不存在!");
                }
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                sw.Close();
            }
        }

    }

2.excel导入数据库中

 /// <summary>
        /// 导入事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Btn_OK_Click(object sender, EventArgs e)
        {
            #region
            if (FileUpload1.HasFile)
            {
                if (FileUpload1.PostedFile.ContentLength < 1000000000)
                {
                    string isxls = "";
                    string fistName = "";
                    string savePath = "";
                    string strdt = "";
                    DataTable dt;
                    try
                    {
                        isxls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
                        fistName = System.IO.Path.GetFileName(FileUpload1.FileName).ToString();
                        if (isxls != ".csv") { Label1.Text = "出现异常：请选择正确的CSV文件！"; return; } //检查文件是不是Excel
                        //文件名组装
                        strdt = DateTime.Now.ToString("yyyyMMDDHHmmss") + ".csv";
                        //获取路径
                        savePath = Server.MapPath("/Upload/" + strdt);
                        //文件移动到指定位置
                        FileUpload1.PostedFile.SaveAs(savePath);
                    }
                    catch
                    {
                        Label1.Text = "出现异常：文件上传失败！";
                    }
                    try
                    {
                        dt=ReadCsvFileToTable(true, ',', savePath);
                        DataRow[] dr =dt.Select();
                        int rownum = dt.Rows.Count;
                        if (rownum == 0)
                        {
                            Label1.Text = "出现异常：CSV表为空，无数据！";
                        }
                        else
                        {                            //数据导入
                            for (int i = 0; i < dr.Length; i++)
                            {
                                if (GetUsers(dt))
                                {
                                    Users_ExcelIn(dr[i]);///调用用户导入方法
                                  
                                }
                                else
                                {
                                    Label1.Text = "出现异常：请选择正确的用户资料模板导入";
                                    return;
                                }
                            }
                        }
                    }
                    catch (Exception ex) { Label1.Text = ex.Message; }
                }
                else
                {
                    Label1.Text = "出现异常：文件过大！";
                }
            }
            else
            {
                Label1.Text = "出现异常：没有选择文件！";
            }
            #endregion
        }

        #region  操作数据
        /// <summary>
        /// 导入数据信息过滤
        /// </summary>
        /// <param name="dr"></param>
        public void Users_ExcelIn(DataRow dr)
        {
            if (!string.IsNullOrEmpty(dr[0].ToString()) && !string.IsNullOrEmpty(dr[1].ToString()) && !string.IsNullOrEmpty(dr[2].ToString()))
            {
                UsersOperate uo = new UsersOperate();
                UsersInfo usinfo = new UsersInfo();
                RolesOperate rOperate = new RolesOperate();
                IList<RolesInfo> rList = rOperate.GetListCheckNull(1, 1, " and rolename='" + dr[2] + "' and RoleType<>0", "roleid desc", "roleid asc");
                if (rList.Count > 0)
                {
                    usinfo.UserLogin = System.Convert.ToString(dr[0]);
                    UsersInfo usinfois = new UsersInfo();
                    usinfois = uo.GetisName(usinfo.UserLogin);
                    if (usinfois == null)
                    {
                        if (dr[1].ToString().Length >= 6)
                        {
                            UsersInfo usinfos = new UsersInfo();
                            usinfos.UserLogin = System.Convert.ToString(dr[0]);
                            usinfos.UserPassword = System.Convert.ToString(dr[1]);
                            usinfos.UserEmail = "";
                            usinfos.UserStatus = 1;
                            usinfos.RoleId = rList[0].Id;
                            usinfos.UserAdder = GetUserSession().Id;
                            usinfos.UserAdded = DateTime.Now;
                            usinfos.UserIsLogin = 0;
                            if (uo.Insert(usinfos) > 0)
                            {
                                MessageInfo = "用户导入成功";
                                Label1.Text = "" + MessageInfo + "";
                            }
                            else
                            {
                                MessageInfo = "出现异常：用户导入失败";
                                Label1.Text = "" + MessageInfo + "";
                            }

                        }
                        else
                        {
                            Label1.Text = "出现异常：用户密码长度少于六位！";
                        }
                    }
                    else
                    {

                        Label1.Text = "出现异常：存在重复用户,导入重复的一条数据";
                    }
                }
                else
                {

                    Label1.Text = "出现异常：角色名没有数据!";
                }
            }
            else
            {
                Label1.Text = "出现异常：数据不能为空!";
            }
        }

        /// <summary>
        /// 判断模版格式
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public bool GetUsers(DataTable dt)
        {
            if (dt.Columns[0].ColumnName == "用户名称" &&
             dt.Columns[1].ColumnName == "密码" &&
             dt.Columns[2].ColumnName == "角色名")
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        /// <summary>
        /// 取消
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Btn_cl_Click(object sender, EventArgs e)
        {
            Response.Redirect("../Manager/UserMain.aspx");
        }

        public DataTable ReadCsvFileToTable(bool HeadYes, char span, string filename)
        {
            //文件路径和文件名
            string files = filename;
            DataTable dt = new DataTable();
            StreamReader fileReader = new StreamReader(files, Encoding.Default);
            try
            {
                //是否为第一行（如果HeadYes为TRUE，则第一行为标题行）
                int lsi = 0;
                //列之间的分隔符
                char cv = span;
                while (fileReader.EndOfStream == false)
                {
                    string line = fileReader.ReadLine();
                    string[] y = line.Split(cv);
                    //第一行为标题行
                    if (HeadYes == true)
                    {
                        //第一行
                        if (lsi == 0)
                        {
                            for (int i = 0; i < y.Length; i++)
                            {
                                dt.Columns.Add(y[i].Trim().ToString());
                            }
                            lsi++;
                        }

                        //从第二列开始为数据列
                        else
                        {
                            DataRow dr = dt.NewRow();
                            for (int i = 0; i < y.Length; i++)
                            {
                                dr[i] = y[i].Trim();
                            }
                            dt.Rows.Add(dr);
                        }
                    }

                    //第一行不为标题行
                    else
                    {
                        if (lsi == 0)
                        {
                            for (int i = 0; i < y.Length; i++)
                            {
                                dt.Columns.Add("Col" + i.ToString());
                            }
                            lsi++;
                        }

                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < y.Length; i++)
                        {
                            dr[i] = y[i].Trim();
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                fileReader.Close();
                fileReader.Dispose();
            }
            return dt;
        }

 

转载于:https://www.cnblogs.com/zfybky/archive/2012/09/10/2673277.html