NPOI用法之Excel基本操作

NPOI用法之Excel基本操作

前言
使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NOPI相关dll下载https://pan.baidu.com/s/1kBNQMuwxSmo7FQsvEV5Flw，提取码：h2g6

1. 创建ExcelSheet

 /// <summary>
       /// 创建Sheet
       /// </summary>
       /// <param name="hssfworkbook"></param>
       /// <param name="sheetName">sheet名</param>
       /// <returns></returns>
        public List<HSSFSheet> creatExcelSheet(ref HSSFWorkbook hssfworkbook,List<string> sheetName)
        {
            List<HSSFSheet> ExcelSheet = new List<HSSFSheet>();

            hssfworkbook = new HSSFWorkbook();//创建excel
            HSSFSheet sheet = null;
            for (int i = 0; i < sheetName.Count; i++)
            {
                sheet = (HSSFSheet)hssfworkbook.CreateSheet(sheetName[i]);//创建sheet
                ExcelSheet.Add(sheet);
            }
            return ExcelSheet;
        }

2、创建单元格

  /// <summary>
        /// 创建单元格
        /// </summary>
        /// <param name="ExcelSheet">sheet名</param>
        /// <param name="rowIndex">第rowIndex行</param>
        /// <param name="cellIndex">第cellIndex列</param>
        /// <param name="cellValue">单元格值</param>
        public void creatExcelCell(HSSFSheet ExcelSheet, int rowIndex, int cellIndex, string cellValue)
        {
            HSSFRow excelRow = (HSSFRow)ExcelSheet.CreateRow(rowIndex);//创建行
            ICell excelCell= excelRow.CreateCell(cellIndex);//创建单元格
            excelCell.SetCellValue(cellValue);
        }

3、完成Excel创建

/// <summary>
        /// 完成Excel创建
        /// </summary>
        /// <param name="hssfworkbook"></param>
        /// <param name="excelPath">excel路径</param>
        /// <param name="excelName">excel名</param>
        /// <param name="deleteCurrenExcel">若excel存在，是否删除</param>
        public void createExcelFinish(HSSFWorkbook hssfworkbook, string excelPath, string excelName, bool deleteCurrenExcel)
        {
            if (deleteCurrenExcel == true)
            {
                //若文件存在，则删除
                if (File.Exists(excelPath + excelName))
                {
                    File.Delete(excelPath + excelName);
                }
            }
            FileStream fileStream = new FileStream(excelPath + excelName, FileMode.Create);//创建文件流
            hssfworkbook.Write(fileStream);//excel写入文件流
            fileStream.Close();//关闭文件流
            MessageBox.Show("文件" + excelName + "创建完成!", "提示");
        }

4、移除sheet

/// <summary>
        /// 移除sheet
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheetIndex">sheet索引</param>
        public void removeSheet(HSSFWorkbook workbook,int sheetIndex)
        {
            workbook.RemoveSheetAt(sheetIndex);
        }

5、按名称获得Sheet对象

/// <summary>
        /// 按名称获得Sheet对象     
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public HSSFSheet getSheetByName(HSSFWorkbook workbook,string sheetName)
        {
            HSSFSheet sheet = (HSSFSheet)workbook.GetSheet(sheetName);
            return sheet;
        }

6、根据索引获得Sheet对象

/// <summary>
        /// 根据索引获得Sheet对象                     
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheetIndex">sheet索引</param>
        /// <returns></returns>
        public HSSFSheet getSheetByIndex(HSSFWorkbook workbook, int sheetIndex)
        {
            HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex);
            return sheet;
        }

7、获得某一个ISheet的所有IRow

/// <summary>
        /// 获得某一个ISheet的所有IRow
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public IEnumerator getSheetRow(HSSFSheet sheet)
        {
            IEnumerator SheetRow = sheet.GetRowEnumerator();
            return SheetRow;

        }

8、 获得某一特定行

/// <summary>
        /// 获得某一特定行
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rowIndex">行索引</param>
        /// <returns></returns>
        public HSSFRow getRowByIndex(HSSFSheet sheet,int rowIndex)
        {
            HSSFRow row =(HSSFRow)sheet.GetRow(rowIndex);
            return row;
        }

9、获取Excel的单元格对象

  /// <summary>
        /// 获取Excel的单元格对象
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rowIndex"></param>
        /// <param name="cellIndex"></param>
        /// <returns></returns>
        public HSSFCell getExcelCell(HSSFSheet sheet, int rowIndex, int cellIndex)
        {
            HSSFCell cell = (HSSFCell)sheet.GetRow(rowIndex).GetCell(cellIndex);
            return cell;
        }

10、合并单元格

 /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="regionDtata">合并单元格的左上角单元格及右下角单元格</param>
        public void RegionCell(HSSFWorkbook workbook,int sheetIndex,int[] regionDtata)
        {
            HSSFSheet sheet=getSheetByIndex(workbook, sheetIndex);
            sheet.AddMergedRegion(new CellRangeAddress(regionDtata[0], regionDtata[1], regionDtata[2], regionDtata[3]));
            HSSFCell cell= getExcelCell(sheet, regionDtata[0], regionDtata[1]);

            //设置居中
            HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
            style.VerticalAlignment = VerticalAlignment.CENTER;
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            cell.CellStyle = style;
        }

11、示例执行NOPI创建Excel

private void 创建ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string excelPath = @"C:\Users\njtq\Desktop\NOPI\";
            string excelName = "test.xls";
            List<string> sheetName = new List<string>();
            sheetName.Add("sheet1");
            sheetName.Add("sheet2");
            HSSFWorkbook hssfworkbook=null;


            //创建sheet
            List<HSSFSheet> ExcelSheet = creatExcelSheet(ref hssfworkbook, sheetName);
             
            //创建cell
            creatExcelCell(ExcelSheet[0], 0, 0, "标题");
            int[] regionData = new int[4] { 0, 0, 0, 4 };

            //合并单元格
            RegionCell(hssfworkbook,0, regionData);

            //创建完成
            createExcelFinish(hssfworkbook, excelPath, excelName, true);
        }