【C#】使用ClosedXML，读取Excel xlsx表格

使用说明

• github: https://github.com/ClosedXML/ClosedXML

API

• new XLWorkbook(fileUrl); //读取xlsx并获取实例
• XLWorksheet.FirstRow(); //获得第一行
• Cell(i) //取筛选结果的第i个格子, 如果获取的是列，拿到的就是第i行，如果是行实例拿到的就是第i列
  使用的命名空间

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Linq;

实例主体代码

        static void Main(string[] args)
        {
            const string fileUrl ="" ;

            //读取xlsx
            var workbook = new XLWorkbook(fileUrl);

            Console.WriteLine("表格获取");

            //注意，这个索引是在1开始的
            for (int i = 1; i < workbook.Worksheets.Count+1; i++)
            {
                Console.WriteLine(workbook.Worksheet(i).Name);
            }

            Console.WriteLine("\n");
            //跟查找数据
            Console.WriteLine("拿到第一行的数据，遍历到直到为空");
            IXLWorksheet XLWorksheet = workbook.Worksheet(1);
            var row = XLWorksheet.FirstRow();

            for (int i = 1; i < (row.CellCount() + 1); i++)
            {
                //获得单元格
                var cell =row.Cell(i);
                //如果单元格为空就返回
                if (cell.IsEmpty())
                {
                    break;
                }
                Console.WriteLine(cell.GetString());
            }

            //假设第一行是数据的字段名，现在根据字段名查找一列数据
            List<string> values = new List<string>();

            //配置表里有一个ItemId字段
            string fieldName = "ItemId";
            IXLCell rowCell=null;

            for (int i = 1; i < (row.CellCount() + 1); i++)
            {
                //获得单元格
                var cell = row.Cell(i);
                if (!cell.IsEmpty()&& cell.GetString()== fieldName)
                {
                     rowCell = cell;
                    break;
                }
            }
          
            if (rowCell==null)
            {
                Console.WriteLine("找不到该字段:"+fieldName);
            }
            else
            {
                Console.WriteLine("\n");
                Console.WriteLine(fieldName + " 字段的所有值:");
                //获得该列
                var column=  XLWorksheet.Column(rowCell.WorksheetColumn().ColumnNumber());
                //Cells()
                Console.WriteLine("数据个数:"+column.Cells().Count());
                int index = 1;
                while (!column.Cell(index).IsEmpty())
                {
                    values.Add(column.Cell(index).GetString());
                    index++;
                }
            }
            //输出该字段的所有值
            foreach (var item in values)
            {
                Console.WriteLine(item);
            }
        }