C#读取指定路径表格数据

表格方法类

 /// <summary>
        /// 获取指定路径下的EXCEL数据
        /// </summary>
        /// <param name="excelPath">EXCEL路径</param>
        /// <param name="sheetIndex">列表序号</param>
        /// <returns></returns>
        public DataTable ImportExcel(string excelPath, int sheetIndex)
        {

            IWorkbook workbook = null;//全局workbook
            ISheet sheet = null;//sheet
            DataTable table = null;
            try
            {
                FileInfo fileInfo = new FileInfo(excelPath);//判断文件是否存在
                if (fileInfo.Exists)
                {
                    FileStream fileStream = fileInfo.OpenRead();//打开文件，得到文件流
                    switch (fileInfo.Extension)
                    {
                        //xls是03，用HSSFWorkbook打开，.xlsx是07或者10用XSSFWorkbook打开
                        case ".xls": workbook = new HSSFWorkbook(fileStream); break;
                        case ".xlsx": workbook = new XSSFWorkbook(fileStream); break;
                        default: break;
                    }
                    fileStream.Close();//关闭文件流
                }
                table = GetDatatableFromExcel(workbook, sheet, sheetIndex);
                return table;

            }
            catch (Exception e)
            {
                return table;
            }
            finally
            {
                //释放资源
                if (table != null) { table.Dispose(); }
                workbook = null;
                sheet = null;
            }
        }

        /// <summary>
        /// Excel数据转换DataTable
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="sheetIndex"></param>
        /// <returns></returns>
        public DataTable GetDatatableFromExcel(IWorkbook workbook, ISheet sheet, int sheetIndex)
        {

            DataTable table = null;
            if (workbook != null)
            {
                sheet = workbook.GetSheetAt(sheetIndex);//读取到指定的sheet
                table = new DataTable();//初始化一个table

                IRow headerRow = sheet.GetRow(0);//获取第一行，一般为表头
                int cellCount = headerRow.LastCellNum;//得到列数

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);//初始化table的列
                    table.Columns.Add(column);
                }
                //遍历读取cell
                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    NPOI.SS.UserModel.IRow row = sheet.GetRow(i);//得到一行
                    DataRow dataRow = table.NewRow();//新建一个行

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);//得到cell
                        if (cell == null)//如果cell为null，则赋值为空
                        {
                            dataRow[j] = "";
                        }
                        else
                        {
                            dataRow[j] = row.GetCell(j).ToString();//否则赋值
                        }
                    }

                    table.Rows.Add(dataRow);//把行 加入到table中
                }
            }
            return table;
        }

        /// <summary>
        /// 从ECXEL读取数据到DataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public DataTable GetDataFromExcel(string filePath)
        {
            string sConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", filePath);
            OleDbConnection oledbConn = new OleDbConnection(sConnString);
            oledbConn.Open();
            DataTable dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            string sTableName = (String)(dt.Rows[0]["TABLE_NAME"]);
            string strExcel = "select * from [" + sTableName + "]";
            OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strExcel, oledbConn);
            DataTable dt2 = new DataTable();
            oleAdapter.Fill(dt2);
            oledbConn.Close();
            return dt2;
        }

文件路径 “/EXCEL/data.xls” 打卡项目所属文件 新建 EXCEL文件夹→data.xls表格名称

 public string PATH = AppDomain.CurrentDomain.BaseDirectory + "/EXCEL/data.xls";

示例(PATH, 0)中的零代表第一行数据以此类推 row[8]表示表格一共有9列

  var list = new List<DataCenter.Helper.Proxy.Data_MetaData>();
            EXCELDataImport dataImport = new EXCELDataImport();
            #region 第一张表格_第一个站点数据
            //第一张表格_第一个站点数据
            var dataTable = dataImport.ImportExcel(PATH, 0);
            if (readIndex == 27)
                readIndex = 0;
            DataRow row = dataTable.Rows[readIndex];
            var a1 = Convert.ToDouble(row[0]).ToString();
            var a2 = Convert.ToDouble(row[1]).ToString();
            var a3 = Convert.ToDouble(row[2]).ToString();
            var a4 = Convert.ToDouble(row[3]).ToString();
            var a5 = Convert.ToDouble(row[4]).ToString();
            var a6 = Convert.ToDouble(row[5]).ToString();
            var a7 = Convert.ToDouble(row[6]).ToString();
            var a8 = Convert.ToDouble(row[7]).ToString();
            var a9 = Convert.ToDouble(row[8]).ToString();