上传Excle表内容到数据库，用GridView显示出来

[c#]代码库
using System.IO;
//判断是否上传文件，若上传，上传的文件是否存在、格式是否正确。fileupload1为FileUpload 控件ID
    protected bool IsFileupload(FileUpload fileupload1, out string message, out string filepath)
    {
        if (fileupload1.HasFile) //判断该控件是否包含文件
        {
            string fullfilename = fileupload1.PostedFile.FileName; 
            string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1);
            string type = filename.Substring(filename.LastIndexOf('.') + 1); //截取文件名后缀
            if (type.ToLower() == "xls")
            {
                string basepath = this.Server.MapPath("~\\Temp\\教师信息"); //保存到文件夹
                string subpath = basepath + "\\" + DateTime.Now.ToString("yyyyMMddhhmmss") + filename;   //修改上传文件名字，避免重复名称
                if (!File.Exists(subpath))
                {
                    if (!Directory.Exists(Server.MapPath("~\\Temp")))
                        Directory.CreateDirectory(basepath);
                    if (!Directory.Exists(basepath))
                        Directory.CreateDirectory(basepath);
                    filepath = subpath;
                    message = "";
                    return true;
                }
                else
                {
                    message = "您上传的文件已存在，请重命名后再上传！";
                    filepath = "";
                    return false;
                }
            }
            else
            {
                message = "您上传的文件格式不正确！";
                filepath = "";
                return false;
            }
        }
        else
        {
            message = "您未上传文件或上传的文件为空！";
            filepath = "";
            return false;
        }
    }
 
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Collections;
//将Excel表中数据转换为DataSet 类型
    public static DataSet ExcelToDatatable(string filepath)
    {
        HSSFWorkbook hssfworkbook = null;
        using (FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read))
        {
            hssfworkbook = new HSSFWorkbook(file);
        }
 
        NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
        IEnumerator rows = sheet.GetRowEnumerator();
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        int rowIndex = 1;       
        while (rows.MoveNext())
        {
            NPOI.SS.UserModel.IRow row = (NPOI.HSSF.UserModel.HSSFRow)rows.Current;
            DataRow dr = null;
 
            if (rowIndex == 1)
            {
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    string value;
                    if (cell == null)
                        value = string.Empty;
                    else
                        value = cell.ToString();
                    dt.Columns.Add(value);
                }
                if (dr != null)
                    dt.Rows.Add(dr);
            }
            else
            {
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    string value;
                    if (cell == null)
                        value = string.Empty;
                    else
                        value = cell.ToString();
                    if(dr == null)
                        dr = dt.NewRow();
                    dr[i] = value;
                }
                if (dr != null)
                    dt.Rows.Add(dr);
            }
            rowIndex++;
        }
        ds.Tables.Add(dt);
        return ds;
    }
 
//导入按钮事件
  protected void btn_users_Click(object sender, EventArgs e)
    {
        string message = "";
        string filepath = "";
        if (IsFileupload(FU_users, out message, out filepath))
        {
            FU_users.PostedFile.SaveAs(filepath);
            DataSet ds = NPOITool.ExcelToDatatable(filepath);
            if (ds != null && ds.Tables.Count > 0)
            {
                try
                {
                    // 数据有效性判断
                       string error = "";//姓名是否为空\是否重复     
                     List<string> namelist = new List<string>(); 
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = ds.Tables[0].Rows[i];
                        string name = dr[0].ToString();                        
                        if (string.IsNullOrEmpty(name))
                        {
                            error += (i + 2).ToString() + ",";
                        }
                        if (namelist.Contains(name))
                        {
                            error += (i + 2).ToString() + ",";
                        }
                        else
                            namelist.Add(name);                       
                       }
                       if (error == "")
                       {
                         //把数据添加到数据库
                         }
                    else
                    {
                        error = "以下行数姓名为空或重复：" + error.TrimEnd(',') + "\\n";
                        ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('" + error + "')</script>");
                        File.Delete(filepath);
                    }
                }
                catch
                {
                    ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('请按模板要求填写信息。')</script>");
                    File.Delete(filepath);
                }
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('文件异常。')</script>");
                File.Delete(filepath);
            }
        }
        else
        {
            ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('" + message + "')</script>");
        }
    }
用户注册

用户登录

发表随想

该用户最新代码

编程随想

[c#]代码库

网友评论 (发表评论)

发表评论：

评论须知：

扫码下载

输入口令后可复制整站源码
