C#读取EXCEL文件

由于工作要求excel读取操作是经常需要的

1.首先我们先创建一个控制台项目,引入必要的npoi组件，并添加对应命名空间的引用

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 文件的读写Excel
{
    class Program
    {
        static void Main(string[] args)
        {
            //ReadExcel(@"C:\Users\xg\Desktop\xg.xlsx");
            //WriteToExcel(@"C: \Users\xg\Desktop\x.xlsx");

            ReadExcel(@"C:\Users\xg\Desktop\test.xls");
            Console.ReadKey();
        }

        public static void ReadExcel(string filePath)
        {
            IWorkbook wk = null;
            string extension = Path.GetExtension(filePath);
            using (FileStream fs = File.OpenRead(filePath))
            {
                if (extension.Equals(".xls"))
                {
                    wk = new HSSFWorkbook(fs);
                    //xls格式
                }
                else
                {
                    wk = new XSSFWorkbook(fs);
                    //xlsx格式
                }

            }
            try
            {
                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);//工作簿
                IRow row = sheet.GetRow(0); //读取当前行数据   
                int lastRowNum = sheet.LastRowNum;//总行数-1
                Console.WriteLine(lastRowNum);

                for (int i = 0; i <=lastRowNum; i++)
                {
                    row = sheet.GetRow(i);
                    if (row != null)
                    {
                        int lastCell = row.LastCellNum;//总列数
                        for (int j = 0; j < lastCell; j++)
                        {
                            string value = row.GetCell(j).ToString();
                            Console.Write(value + " ");
                        }
                        Console.WriteLine("\n");//一行的结束
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

        }
        /// <summary>
        /// 写入
        /// </summary>
        /// <param name="filePath"></param>
        public static void WriteToExcel(string filePath)
        {
            using (Stream fileStream = File.OpenWrite(filePath))
            {
                IWorkbook wb = new XSSFWorkbook();//如果生成xls则是HSSFWorkbook
                ISheet sheet = wb.CreateSheet();
                IRow row = sheet.CreateRow(0);//0行号
                row.CreateCell(0).SetCellValue("rupeng");
                row.CreateCell(1).SetCellValue(3.14);
                IRow row2 = sheet.CreateRow(1);
                row2.CreateCell(0).SetCellValue("rupeng");
                row2.CreateCell(1).SetCellValue(3.14);
                wb.Write(fileStream);
            }
        }

    }
}