ASP.net 读取(导入)客户端Excel文档

实现的思路很清晰，先将文件上传到服务端的临时路径，再调用数据库或者Office接口读取和解析文档内容即可。

 

1.在页面上放置FileUpload控件，和一个“上传”按钮

<asp:FileUpload ID="FileUpload_Excel" runat="server" Font-Names="Calibri" Font-Size="Small" Height="18px" Width="170px" />

<asp:Button ID="Button_Upload" runat="server" CssClass="ButtonCss" Height="20px" OnClick="Button_Upload_Click" TabIndex="1" Text="Upload" Width="60px" />

2.在Button_Upload的单击事件响应函数中执行FileUpload的上传方法：

            if (FileUpload_Excel.HasFile)
            {
                if ((System.IO.Path.GetExtension(FileUpload_Excel.FileName)).ToLower().StartsWith(".xls"))//检测扩展名
                {
                    try
                    {
                        String path_temp = "........"; //要保存的临时路径
                        FileUpload_Excel.SaveAs(path_temp); //FileUpload的SaveAs方法实现上传
                        Label_Msg.Text = "Saved !";
                        //......读取文档
                    }
                    catch (Exception ex) { Label_Msg.Text = ex.Message; }
                }
                else Label_Msg.Text = "Only ECXEL-format doc. accepted!";
            }
            else Label_Msg.Text = "No file selected!.";

3.读取/解析Excel文档。至少有两种方法，一种是使用Microsoft.Office.Interop.Excel中提供的方法，另一种是System.Data.OleDb数据库接口。前一种方法的优点是可以读取Excel的一些高级属性，比如字体、颜色、有效性规则等；而后已中的优点是速度很快。本例中笔者只需要读取Excel中的表格数据，对其他高级属性不感兴趣，故采用第二种方法。

该方法的封装函数如下：

            public DataSet ExcelToSet(String Path_Im ,bool Delete)
            {
                try
                {
                    DataSet SheetSet = new DataSet();
                    //通过OleDb连接到Excel
                    String conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path_Im + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
                    System.Data.OleDb.OleDbConnection DbConn = new System.Data.OleDb.OleDbConnection(conString);
                    DbConn.Open();
                    if (DbConn.State == ConnectionState.Open)
                    {
                    //获取Sheet列表
                        System.Data.DataTable Schema_dt = DbConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                        if (Schema_dt != null)
                        {
                            foreach (DataRow dr in Schema_dt.Rows)//遍历所有Sheet
                            {
                                String SheetName = dr["TABLE_NAME"].ToString();//获取Sheet名称
                                DataTable Dt_Sheet = new System.Data.OleDb.OleDbDataAdapter(
                                    String.Format("select * from [{0}]", SheetName), DbConn)//填充Sheet数据到DataTable
                                //获取的Sheet名称是带后缀的，需要处理一下
                                SheetName = SheetName.TrimStart("'".ToCharArray());
                                SheetName = SheetName.TrimEnd("'".ToCharArray());
                                SheetName = SheetName.TrimEnd("$".ToCharArray());
                                Dt_Sheet.Name = SheetName;
                                //添加该Sheet（作为一个DataTable）的数据到DataSet
                                try { SheetSet.Tables.Add(Dt_Sheet); }
                                catch { }
                            }
                        }
                        else
                            SheetSet = null;
                        DbConn.Close();
                    }
                    else
                        SheetSet = null;
                    //删除临时文档
                    if (Delete && File.Exists(Path_Im))
                        File.Delete(Path_Im);
                    return SheetSet;
                }
                catch
                {
                }
                return null;
            }

这样，Excel中的数据就装填到DataSet中了，DataSet中的一个DataTable就对应了Excel中的一个Sheet。