asp.net操作读取excel的两种方法

本文通过上传一个excle文件，并规定具体格式，读取数据填充到gridview中

前台页面代码如下：

 <div style=" width:500px; height:400px; text-align:center">
        <form id="form1" runat="server" enctype="multipart/form-data">
        <asp:Label runat="server">我们只支持excel(.xls/.xlsx)的上传,模板点击这里下载</asp:Label>
        <asp:Button ID="LoadTemplate" runat="server" Text="下载模板" OnClick="LoadTemplate_Click"/>
        <div id="MyFile" style=" float:left; margin-top:20px; margin-bottom:20px">
            <input name="File" type="file" class="FileUpStyle" id="File1" />
             <asp:Button runat="server" Text="上传" ID="Upload" BorderColor="Desktop" BorderWidth="1px"
            Height="20px" Width="60px" OnClick="Upload_Click"></asp:Button>
        </div>
        <asp:GridView ID="GridView1" BorderColor="Black" runat="server" AutoGenerateColumns="False"
            Font-Size="12px" Width="530px" AllowSorting="True">
            <Columns>
                <asp:BoundField DataField="holiday" HeaderText="holiday" />
            </Columns>
            <HeaderStyle BackColor="Azure" Font-Size="12px" HorizontalAlign="Center" />
            <RowStyle HorizontalAlign="Center" />
            <PagerStyle HorizontalAlign="Center" />
        </asp:GridView>
        </form>
    </div>

 

方法一是使用office提供的类库来获取：

 static string filePath = string.Empty;

        /// <summary>
        /// 删除文件
        /// </summary>
        /// <param name="path"></param>
        private void DeleteFile(DirectoryInfo path)
        {
            foreach (DirectoryInfo d in path.GetDirectories())
            {
                DeleteFile(d);
            }
            foreach (FileInfo f in path.GetFiles())
            {
                f.Delete();
            }
        } 

        /// <summary>
        /// 上传文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Upload_Click(object sender, EventArgs e)
        {
            HttpFileCollection files = System.Web.HttpContext.Current.Request.Files;

            for (int i = 0; i < files.Count; i++)
            {
                string ShortFileName = files[i].FileName.Substring(files[i].FileName.LastIndexOf("\\") + 1);
                string type = ShortFileName.Substring(files[i].FileName.LastIndexOf(".") + 1);
                if (ShortFileName == "")
                {
                    Response.Write("<script>alert('未选择文件！');</script>");
                }
                else if (!type.ToLower().Equals("xls") && !type.ToLower().Equals("xlsx"))
                {
                    Response.Write("<script>alert('文件格式不正确！必须是(.xls/.xlsx)');</script>");
                }
                else if (ShortFileName != "" && (type.ToLower().Equals("xls") || type.ToLower().Equals("xlsx")))
                {
                    filePath = Server.MapPath("~/bin/Files/ExcelFiles/" + ShortFileName);

                    DeleteFile(new DirectoryInfo(Server.MapPath("~/bin/Files/ExcelFiles/")));

                    files[i].SaveAs(filePath);
                    DataSet ds = ReadExcel(filePath,type);
                    if (ds == null)
                    {
                        Response.Write("<script>alert('文件格式不正确！');</script>");
                        return;
                    }
                    bool folat = ISQualified(ds);
                    if (folat)
                    {
                        Response.Write("<script>alert('日期格式不正确！应该为yyyy-MM-dd');</script>");
                    }
                    else
                    {
                        Save(ds);
                    }
                }
            }
        }

        /// <summary>
        /// 保存到数据库
        /// </summary>
        /// <param name="ds"></param>
        private void Save(DataSet ds)
        {
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private DataSet ReadExcel(string path,string type)
        {
            DataSet ds;
            try
            {
                ReadExcel readExcel = new ReadExcel();
                string sheetName = readExcel.ExcelSheetName(filePath,type)[0].ToString();
                ds = readExcel.ExcelDataSource(filePath,type,sheetName);
            }
            catch (Exception e)
            {
                return null;
            }
            return ds;
        }

        /// <summary>
        /// 判断时间格式
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        private bool ISQualified(DataSet ds)
        {
            bool folat = false;
            if (ds == null || ds.Tables[0].Rows.Count < 1)
                return true;
            foreach (DataRow d in ds.Tables[0].Rows)
            {
                try
                {
                    string holidayyear = d["holiday"].ToString().Trim();
                    if (string.IsNullOrEmpty(holidayyear))
                        continue;
                    DateTime b = DateTime.ParseExact(holidayyear, "yyyy-MM-dd 0:00:00", CultureInfo.InvariantCulture);
                    folat = false;
                }
                catch (Exception e)
                {
                    folat = true;
                    return folat;
                }
            }
            return folat;
        }

        /// <summary>
        /// 文件模板下载
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void LoadTemplate_Click(object sender, EventArgs e)
         {
             Response.ContentType = "application/ms-excel";
             Response.AddHeader("Content-Disposition", "attachment;filename=Template.xls");
             string filename = Server.MapPath("~/bin/Files/Template/Template.xls");
             Response.TransmitFile(filename);
         }

 方法二是引用npoi类库进行读取，相比上一个方法，这个更灵活，可操作性更高：

static string filePath = string.Empty;

        /// <summary>
        /// 删除文件
        /// </summary>
        /// <param name="path"></param>
        private void DeleteFile(DirectoryInfo path)
        {
            foreach (DirectoryInfo d in path.GetDirectories())
            {
                DeleteFile(d);
            }
            foreach (FileInfo f in path.GetFiles())
            {
                f.Delete();
            }
        }

        /// <summary>
        /// 文件模板下载
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void LoadTemplate_Click(object sender, EventArgs e)
        {
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=Template.xls");
            string filename = Server.MapPath("~/bin/Files/Template/Template.xls");
            Response.TransmitFile(filename);
        }

        /// <summary>
        /// 上传文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Upload_Click(object sender, EventArgs e)
        {
            HttpFileCollection files = System.Web.HttpContext.Current.Request.Files;

            for (int i = 0; i < files.Count; i++)
            {
                string ShortFileName = files[i].FileName.Substring(files[i].FileName.LastIndexOf("\\") + 1);
                string type = ShortFileName.Substring(files[i].FileName.LastIndexOf(".") + 1);
                if (ShortFileName == "")
                {
                    Response.Write("<script>alert('未选择文件！');</script>");
                }
                else if (!type.ToLower().Equals("xls") && !type.ToLower().Equals("xlsx"))
                {
                    Response.Write("<script>alert('文件格式不正确！必须是(.xls/.xlsx)');</script>");
                }
                else if (ShortFileName != "" && (type.ToLower().Equals("xls") || type.ToLower().Equals("xlsx")))
                {
                    filePath = Server.MapPath("~/bin/Files/ExcelFiles/" + ShortFileName);

                    DeleteFile(new DirectoryInfo(Server.MapPath("~/bin/Files/ExcelFiles/")));

                    files[i].SaveAs(filePath);
                    DataTable dt = GetExcelInfo(filePath);
                    if (dt == null)
                    {
                        Response.Write("<script>alert('文件格式不正确！');</script>");
                        return;
                    }
                    bool folat = ISQualified(dt);
                    if (folat)
                    {
                        Response.Write("<script>alert('日期格式不正确！应该为yyyy-MM-dd');</script>");
                    }
                    else
                    {
                        Save(dt);
                    }
                }
            }
        }

        private DataTable GetExcelInfo(string path)
        {
            using (FileStream stream = new FileStream(@path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                HSSFWorkbook workbook = new HSSFWorkbook(stream);
                //获取excel的第一个sheet
                HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);

                DataTable table = new DataTable();
                //获取sheet的首行
                HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);

                //一行最后一个方格的编号 即总的列数
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
                //最后一列的标号  即总的行数
                int rowCount = sheet.LastRowNum;

                for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
                {
                    HSSFRow row = (HSSFRow)sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        HSSFCell hsc = (HSSFCell)row.GetCell(j);
                        if (hsc != null && !string.IsNullOrEmpty(hsc.ToString()))
                        {
                            try
                            {
                                string date = row.GetCell(j).DateCellValue.ToString();
                                dataRow[j] = date;
                            }
                            catch (Exception e)
                            {
                                dataRow[j] = null;
                            }

                        }
                    }

                    table.Rows.Add(dataRow);
                }

                workbook = null;
                sheet = null;
                return table;
            }
        }

        private bool ISQualified(DataTable dt)
        {
            bool folat = false;
            if (dt == null || dt.Rows.Count < 1)
                return true;
            foreach (DataRow d in dt.Rows)
            {
                try
                {
                    string holidayyear = d["holiday"].ToString().Trim();
                    if (string.IsNullOrEmpty(holidayyear))
                        continue;
                    DateTime b = DateTime.ParseExact(holidayyear, "yyyy-MM-dd 0:00:00", CultureInfo.InvariantCulture);
                    folat = false;
                }
                catch (Exception e)
                {
                    folat = true;
                    return folat;
                }
            }
            return folat;
        }

        /// <summary>
        /// 保存到数据库
        /// </summary>
        /// <param name="ds"></param>
        private void Save(DataTable dt)
        {
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }