C#逐行读取excel所有内容-创新互联

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using NPOI.SS.UserModel;

using NPOI.HSSF.UserModel;

using System.IO;

namespace www.xinduofen.cn

{

  class NpoiOperateExcel

  {

        ///

    /// 代表excel表格保存的地址，包括"文件名.xls"

    /// 代表将要读取的sheet表的索引位置

    /// 返回 “不为空” 代表读取成功，否则为读取失败；读取数据list《string》代表一行数据，有多少个就代表有多行数据(所有行左对齐)

    public static List> rowReadAll(string save_address, int sheet_number)//读取excel表格相应工作表的所有数据

    {

      List> data = null;

      //如果传入参数合法

      if (!string.IsNullOrEmpty(save_address) && sheet_number > 0){

        int rowAllCnt = NpoiOperateExcel.rowORcolAllCount(save_address,sheet_number,true);

        int colAllCnt = NpoiOperateExcel.rowORcolAllCount(save_address,sheet_number,false);

        data = NpoiOperateExcel.rowReadSection(save_address, 1, rowAllCnt, 1, colAllCnt, sheet_number);

      }

      return data;

    }

        public static int rowORcolAllCount(string save_address, int sheet_number, Boolean readFlag)//读取excel表格相应工作表的所有数据

    {

      int rowORcolCnt = -1;//初始化为-1

      FileStream readfile = null;

      try

      {

        //如果传入参数合法

        if (!string.IsNullOrEmpty(save_address) && sheet_number > 0)

        {

          readfile = new FileStream(save_address, FileMode.Open, FileAccess.Read);

          HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);

          ISheet sheet = hssfworkbook.GetSheetAt(sheet_number - 1);

          if (sheet != null)

          {

            if (readFlag)//如果需要读取‘有效行数’

            {

              rowORcolCnt = sheet.LastRowNum+1;//有效行数(NPOI读取的有效行数不包括列头，所以需要加1)

            }

            else

            { //如果需要读取‘大有效列数’

              for (int rowCnt = sheet.FirstRowNum; rowCnt <= sheet.LastRowNum; rowCnt++)//迭代所有行

              {

                IRow row = sheet.GetRow(rowCnt);

                if (row != null && row.LastCellNum > rowORcolCnt)

                {

                  rowORcolCnt = row.LastCellNum;

                }

              }

            }

          }

        }

      }

      catch (Exception)

      {

        Console.WriteLine("NpoiOperateExcel.rowOrColumnAllCount方法产生了异常！");

      }

      finally

      {

        if (readfile != null) { readfile.Close(); }

      }

      return rowORcolCnt;

    }

        public static List> rowReadSection(string save_address, int start_row, int stop_row,

      int sart_column, int stop_column, int sheet_number)//读取excel表格相应工作表的部分数据

    {

      List> data = null;//初始化为空

      FileStream readfile = null;

      try

      {

        //如果传入参数合法

        if (!string.IsNullOrEmpty(save_address) && start_row > 0 && stop_row > 0 && sart_column > 0 && stop_column>0 && sheet_number > 0)

        {

          readfile = new FileStream(save_address, FileMode.Open, FileAccess.Read);

          HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);

          ISheet sheet = hssfworkbook.GetSheetAt(sheet_number - 1);

          if (sheet != null)

          {

            for (int rowIndex = start_row - 1; rowIndex < stop_row; rowIndex++)

            {

              IRow row = sheet.GetRow(rowIndex);

              if (row != null)

              {

                List oneRow = new List();

                for (int columnIndex = sart_column - 1; columnIndex < stop_column; columnIndex++)

                {

                  ICell cell = row.GetCell(columnIndex);

                  if (cell != null)

                  {

                    oneRow.Add(cell.StringCellValue);

                  }

                  else {

                    oneRow.Add("");//填充空的数据

                  }

                }

                if (data == null)

                {

                  data = new List>();//初始化

                }

                data.Add(oneRow);

              }

              else {

                List oneRow = new List();//软件为相应位置空行创建内存中的空数据行

                for (int columnIndex = sart_column - 1; columnIndex < stop_column; columnIndex++)

                {

                  oneRow.Add("");//填充空的数据

                }

                if (data == null)

                {

                  data = new List>();//初始化

                }

                data.Add(oneRow);

              }

            }

          }

        }

      }

      catch (Exception)

      {

        Console.WriteLine("NpoiOperateExcel.rowReadSection方法产生了异常！");

      }

      finally

      {

        if (readfile != null) { readfile.Close(); }

      }

      return data;

    }

    }

}

内容所有权属于：北京继豪电子（专业研究体质测试仪器）

另外有需要云服务器可以了解下创新互联scvps.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。