首页新闻找找看学习计划

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

0
[已解决问题] 解决于 2018-09-25 12:19
 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]";

请问这个地方应该怎么解决,感谢!!!

笑叹、的主页 笑叹、 | 初学一级 | 园豆:6
提问于:2018-06-25 11:30
< >
分享
最佳答案
0

可以尝试用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);
奖励园豆:5
ms_doudou | 小虾三级 |园豆:540 | 2018-06-26 17:37
其他回答(2)
0

能读到数据嘛?

逐梦者66 | 园豆:106 (初学一级) | 2018-06-25 16:58
0

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

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

3.找出bug的具体问题

竜咚咚 | 园豆:744 (小虾三级) | 2018-06-26 11:49
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册