Exemplo n.º 1
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="fileAddress"></param>
        /// <returns></returns>
        private DataTable OpenExcelToDataTable(string fileAddress)
        {
            IWorkbook wk;

            //创建表标题
            var dt = dbList.ImportDt();

            using (var fsRead = File.OpenRead(fileAddress))
            {
                wk = new XSSFWorkbook(fsRead);
                //获取第一个sheet
                var sheet = wk.GetSheetAt(0);

                //创建完标题后,开始从第二行起读取对应列的值
                for (var r = 1; r <= sheet.LastRowNum; r++)
                {
                    var result = false;
                    var dr     = dt.NewRow();

                    //获取当前行(注:只能获取行中有值的项,为空的项不能获取;即row.Cells.Count得出的总列数就只会汇总"有值的列"之和)
                    var row = sheet.GetRow(r);
                    if (row == null)
                    {
                        continue;
                    }

                    for (var j = 0; j < 5 /*row.Cells.Count*/; j++)
                    {
                        //循环获取行中的单元格
                        var cell      = row.GetCell(j);
                        var cellValue = GetCellValue(cell);

                        if (cellValue == string.Empty)
                        {
                            continue;
                        }
                        else
                        {
                            dr[j] = cellValue;
                        }

                        //全为空就不取
                        if (dr[j].ToString() != "")
                        {
                            result = true;
                        }
                    }

                    if (result == true)
                    {
                        //把每行增加到DataTable
                        dt.Rows.Add(dr);
                    }
                }
            }

            return(dt);
        }