NPOI操作Excel

NPOI操作Excel的基本用法
文件的读入

        public static void ReadExcel(string excelPath)
        {
            FileStream fs = new FileStream(excelPath,FileMode.Open, FileAccess.Read);
            if(excelPath.EndsWith(".xlsx"))
            {
                workbook = new XSSFWorkbook(fs);
            }
            else if(excelPath.EndsWith(".xls"))
            {
                workbook = new HSSFWorkbook(fs);
            }
            else
            {
                workbook = null;
            }
        }

获取sheet个数

        public static int GetSheetCount()
        {
            if (workbook != null)
            {
                return workbook.NumberOfSheets;
            }
            return 0;
        }

获取sheet名

        public static string GetSheetName(int nIndex)
        {
            if(workbook!=null)
            {
                return workbook.GetSheetName(nIndex);
            }
            return "";
        }

读取excel所有行数据

  public static List<string> GetSheetData(int nIndex)
        {
            List<string> result = new List<string>();
            sheet = workbook.GetSheetAt(nIndex);
            int rowNum = sheet.LastRowNum;
            int cellNumm = 0 ;
            cellNumm = sheet.GetRow(0).LastCellNum;
            string ss = "";
            for (int k=0;k< rowNum;k++)
            {
                ss = "";
                if (k==0)
                {
                  
                    
                }
                else
                {
                    for(int j=0;j< cellNumm;j++)
                    {
                        rowHeader = sheet.GetRow(k);
                        if (rowHeader.GetCell(0).StringCellValue == "")
                            break;
                        ss+= rowHeader.GetCell(j).StringCellValue + "               ";
                    }
                }
                result.Add(ss);
            }
  

            return result;
        }

创建表格

  public static void CreatExcel()
        {
            workbook = new HSSFWorkbook();
            if (workbook.NumberOfSheets > 0)
                sheet = workbook.GetSheetAt(0);
            else
                sheet = workbook.CreateSheet("data");

            style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;


            sheet.SetColumnWidth(0, 30 * 256);
            sheet.SetColumnWidth(1, 30 * 256);
            sheet.SetColumnWidth(2, 30 * 256);
            sheet.SetColumnWidth(3, 30 * 256);
            sheet.SetColumnWidth(4, 30 * 256);
            sheet.SetColumnWidth(5, 30 * 256);
            sheet.SetColumnWidth(6, 30 * 256);
            sheet.SetColumnWidth(7, 30 * 256);
            rowHeader = sheet.CreateRow(0);
            rowHeader.Height = 20 * 25;

            rowHeader.CreateCell(0, CellType.String).SetCellValue("项目号");
            rowHeader.CreateCell(1, CellType.String).SetCellValue("数量");
            rowHeader.CreateCell(2, CellType.String).SetCellValue("XXX");
            rowHeader.CreateCell(3, CellType.String).SetCellValue("XXX");
            rowHeader.CreateCell(4, CellType.String).SetCellValue("XXX");
            rowHeader.CreateCell(5, CellType.String).SetCellValue("XXX");
            rowHeader.CreateCell(6, CellType.String).SetCellValue("XXX");
            rowHeader.CreateCell(7, CellType.String).SetCellValue("XXX");
            for (int k = 0; k < 8; k++)
            {
                rowHeader.GetCell(k).CellStyle = style;
            }
        }

写入数据

        public static void WriteExcel(int rowNum, string sn, string ngItem)
        {
            IRow row = sheet.CreateRow(rowNum);
            row.Height = 40 * 20;
            row.CreateCell(0, CellType.String).SetCellValue(sn);

        }

数据保存

        public static void ExportExcel(string excelPath)
        {
            fs = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.Write);
            workbook.Write(fs);
            workbook.Close();
            fs.Close();
        }