Excel 导入的两种常用方法

1 NPOI方法:

/// <summary>
    /// 上传事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (!ful.HasFile) //判断是否有文件
        {
            ClientScript.RegisterStartupScript(this.GetType(),"msg","<script>alert('无文件')</script>");
        }
        string IsExcel = System.IO.Path.GetExtension(ful.FileName);//获取文件后缀名
        if ( IsExcel != ".xls")
        {
            ClientScript.RegisterStartupScript(this.GetType(),"msg","<script>alert('格式错误')</script>");
        }
        if (ful.PostedFile.ContentLength>1024*1024||ful.PostedFile.ContentLength<0) //判断文件大小
        {
            ClientScript.RegisterStartupScript(this.GetType(),"msg","<script>alert('大小不正确')</script>");
        }
      //  string filepath = ful.PostedFile.FileName; //获取客户端的上传路径
        string fileName = ful.FileName;
        string path = Server.MapPath("file/") + fileName;//获取服务器的上传路径 
        ful.SaveAs(path);//保存到服务器
        DataTable dt = GetDataTable(path);
        //DataTable dt = GetDataSet(path,fileName);

    }
    /// <summary>
    /// 读取EXCEL到DataTable中(NPOI方法)
    /// </summary>
    /// <param name="path">服务器路径</param>
    /// <param name="fileName">文件名</param>
    /// <returns></returns>
    public DataTable GetDataSet(string path, string fileName)
    {
        DataTable dt = new DataTable(); 
        HSSFWorkbook workbook; //创建工作簿
       
        using (FileStream file=new FileStream (path,FileMode.Open,FileAccess.Read)) //创建文件流,第二个参数打开方式,第三个读取类型 需要using IO
        {
            workbook = new HSSFWorkbook(file);
        }
        ISheet sheet =workbook.GetSheetAt(0); //获得第一个表实例对象
        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();//获得表中的行对象
        while (rows.MoveNext()) //读取行
        {
            IRow row = (HSSFRow)rows.Current; //当前行对象
            if (row.RowNum == 0) //判断表头
            {
                for (int i = 0; i < row.LastCellNum; i++) //遍历此行的单元格
                {
                    ICell cell = row.GetCell(i);  //获得单元格
                    if (cell == null)
                    {
                        dt.Columns.Add((Convert.ToChar((int)'A' + i)).ToString());//如果表头没有值,则添字母
                    }
                    else
                    {
                        dt.Columns.Add(cell.ToString());
                    }
                }              
            }
            else
            {
                DataRow dr = dt.NewRow(); //根据表头行 来创建新行
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell==null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
        }

        return dt;
    }

2 OleDb方法:

/// <summary>
    /// 导入Excel(OleDb方法)
    /// </summary>
    /// <param name="serfilepath">文件地址</param>
    /// <returns></returns>
    public DataTable GetDataTable(string serfilepath)
    {
        DataTable dt = new DataTable();
        string strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + serfilepath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
        using (OleDbConnection oleconn = new OleDbConnection(strConn))
        {
            oleconn.Open();
            dt = oleconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
            string sheetName = dt.Rows[0]["TABLE_NAME"].ToString(); ;           
            string sql = "select * from [" + sheetName + "]";
            OleDbDataAdapter olda = new OleDbDataAdapter(sql, oleconn);
            olda.Fill(dt);
            oleconn.Close();
            return dt;
        }
    }

 

转载于:https://www.cnblogs.com/joker-xp/p/3154302.html