C# 读取Excel表格 ie8中无限读取空白行如何解决？

Question

 private DataTable xsldata()
        {
            string FileName = string.Empty;
            if (fuload.FileName == "")
            {
                Response.Write("<script>alert('请选择文件')</script>");
                return null;
            }
            string fileExtenSion;
            string localpath = Server.MapPath("/tmp" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
            fileExtenSion = Path.GetExtension(fuload.FileName);
            if (fileExtenSion.ToLower() != ".xls" && fileExtenSion.ToLower() != ".xlsx")
            {
                Response.Write("<script>alert('上传的文件格式不正确(.xls/.xlsx)')</script>");
                return null;
            }
            try
            {
                string User_Id = CommonMethod.GetCookiesValue("User_ID");
                //string FileName = "App_Data/" + Path.GetFileName(fuload.FileName);
                FileName = "App_Data/" + User_Id + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                if (File.Exists(Server.MapPath(FileName)))
                {
                    File.Delete(Server.MapPath(FileName));
                }
                fuload.SaveAs(Server.MapPath(FileName));
                //HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES  
                string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                string connstr2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
                OleDbConnection conn;
                if (fileExtenSion.ToLower() == ".xls")
                {
                    conn = new OleDbConnection(connstr2003);
                }
                else
                {
                    conn = new OleDbConnection(connstr2007);
                }
                conn.Open();
                string sql = "select * from [Sheet1$]";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                DataTable dt = new DataTable();
                OleDbDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                sdr.Close();
                conn.Close();
                //删除服务器里上传的文件  
                if (File.Exists(Server.MapPath(FileName)))
                {
                    File.Delete(Server.MapPath(FileName));
                }
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Error(typeof(Login), ex.Message, new Exception("error"));
                if (File.Exists(Server.MapPath(FileName)))
                {
                    File.Delete(Server.MapPath(FileName));
                }
                return null;
            }
        }

设置读取1到10行也没有用

 string sql = "select * from [Sheet1$1:10]";

请问这个地方应该怎么解决，感谢！！！

Answer 1

1.先看一下 上传的文件有没有值

2.在调程序 看有没有从这个文件里面读取到值

3.找出bug的具体问题

Answer 2

可以尝试用Spire.XLS读取，参考文章：http://e-iceblue.cn/spirexls/import-or-export-data.html，代码：

//创建Workbook对象并加载Excel文档
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"F:\ExportData.xlsx" , ExcelVersion.Version2013);
            
//获取第一张sheet
Worksheet sheet = workbook.Worksheets[0];

//设置单元格区域
CellRange range = sheet.Range[sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn];
           
//导出到datatable
DataTable dt = sheet.ExportDataTable(range, true, true);

Answer 3

能读到数据嘛？