C# NPOI read excel files include xls and xlsx

2021-01-04 14:28

阅读:506

标签:end   last   int   code   open   pac   value   poi   name   

1.Install-package npoi;

2.Add necessary namespace as below.

using System.IO;
using System.IO.Compression;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

3.code 

 static void ReadPrintExcelViaNPOI(string excelFileName)
        {
            string excelExtension=  Path.GetExtension(excelFileName);
            switch(excelExtension)
            {
                case ".xls":
                    NPOIReadXls(excelFileName);
                    break;
                case ".xlsx":
                    NPOIReadXlsX(excelFileName);
                    break;
            }   
        }

        static void NPOIReadXls(string xlsFileName)
        {
            try
            {
                HSSFWorkbook book;
                using (FileStream fs = new FileStream(xlsFileName, FileMode.Open, FileAccess.Read))
                {
                    book = new HSSFWorkbook(fs);
                    ISheet st = book.GetSheetAt(0);
                    int rowsCount = st.LastRowNum;
                    totalRowsCount = rowsCount;
                    for (int i = 0; i )
                    {
                        int columnsCount = st.GetRow(i).Cells.Count();
                        StringBuilder rowBuilder = new StringBuilder();
                        for (int j = 0; j )
                        {

                            ICell cell = st.GetRow(i).GetCell(j);
                            string cellValue = string.Empty;
                            if (cell != null)
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.String:
                                        cellValue = cell.StringCellValue;
                                        break;
                                    case CellType.Numeric:
                                        cellValue = cell.NumericCellValue.ToString();
                                        break;
                                }
                            }
                            rowBuilder.Append(cellValue + ",");
                        }
                        string msg = rowBuilder.ToString().TrimEnd(new char[] { , });
                        if (!string.IsNullOrWhiteSpace(msg))
                        {
                            Console.WriteLine(msg);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        static void NPOIReadXlsX(string xlsxFileName)
        {
            try
            {
                XSSFWorkbook  book;
                using (FileStream fs = new FileStream(xlsxFileName, FileMode.Open, FileAccess.Read))
                {
                    book = new XSSFWorkbook(fs);
                    ISheet st = book.GetSheetAt(0);
                    int rowsCount = st.LastRowNum;
                    totalRowsCount = rowsCount;
                    for (int i = 0; i )
                    {
                        int columnsCount = st.GetRow(i).Cells.Count();
                        StringBuilder rowBuilder = new StringBuilder();
                        for (int j = 0; j )
                        {
                            ICell cell = st.GetRow(i).GetCell(j);
                            string cellValue = string.Empty;
                            if (cell != null)
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.String:
                                        cellValue = cell.StringCellValue;
                                        break;
                                    case CellType.Numeric:
                                        cellValue = cell.NumericCellValue.ToString();
                                        break;
                                }
                            }
                            rowBuilder.Append(cellValue + ",");
                        }
                        string msg = rowBuilder.ToString().TrimEnd(new char[] { , });
                        if (!string.IsNullOrWhiteSpace(msg))
                        {
                            Console.WriteLine(msg);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

 

C# NPOI read excel files include xls and xlsx

标签:end   last   int   code   open   pac   value   poi   name   

原文地址:https://www.cnblogs.com/Fred1987/p/13195932.html


评论


亲,登录后才可以留言!