C# Excel使用NPOI

程序处理excel使用using Microsoft.Office.Interop.Excel方式，运行程序需要电脑安装excel，而且excel版本还需要一样，使用起来不方便。使用NPOI不用电脑安装office.

下载地址:http://npoi.codeplex.com/

下载NPOI 2.2.1 binary package.zip，解压缩里面有Net20和Net40。我使用的Net40，把里面所有的dll添加引用。

 

读excel

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
 private void ReadPatternExcel(string filePath,int sheetIndex)
        {
            IWorkbook workBook = null;
            ISheet sheet;
            try
            {
                FileInfo fileInfo = new FileInfo(filePath);
                if(fileInfo.Exists)
                {
                    FileStream fs = fileInfo.OpenRead();
                    switch (fileInfo.Extension)
                    {
                        //xls是03，用HSSFWorkbook打开，.xlsx是07或者10用XSSFWorkbook打开
                        case ".xls":
                            workBook = new HSSFWorkbook(fs);
                            break;
                        case ".xlsx":
                            workBook = new XSSFWorkbook(fs);
                            break;
                        default:
                            break;
                    }
                    fs.Close();//关闭文件流
                }

                if(workBook!=null)
                {
                    sheet = workBook.GetSheetAt(sheetIndex);
                    IRow headerRow = sheet.GetRow(0);
                    int colCount = headerRow.LastCellNum;//列数
                    //遍历
                    for(int i=sheet.FirstRowNum;i<=sheet.LastRowNum;i++)
                    {
                        Console.Write("row "+i.ToString()+" ");
                        NPOI.SS.UserModel.IRow row = sheet.GetRow(i);//得到一行
                        for(int j=row.FirstCellNum;j<row.LastCellNum;j++)
                        {
                            string data = row.GetCell(j).ToString();
                            Console.Write(data);
                        }
                        Console.WriteLine();
                    }
                }


            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                workBook = null;
                sheet = null;
            }

        }

 写入Excel

 private void ExportToExcel()
        {
            SaveFileDialog saveFileDlg = new SaveFileDialog();
            saveFileDlg.Filter = "Excel|*.xlsx|All file|*.*";
            if (saveFileDlg.ShowDialog()==DialogResult.OK)
            {              
                WriteExcel(saveFileDlg.FileName);
            }
           
        }

        private void WriteExcel(string filePath)
        {
            IWorkbook workBook = null;
            ISheet sheet = null;
            FileStream fs=null;
            try
            {
                workBook = new XSSFWorkbook();
                sheet = workBook.CreateSheet("sheet0");             

                //设置列头  
                IRow row = sheet.CreateRow(0);//excel第一行设为列头 
                ICell cell = row.CreateCell(0);
                cell.SetCellValue("Data");
                cell = row.CreateCell(1);
                cell.SetCellValue("Remark");

                //添加数据
                for(int rowIndex=0; rowIndex < collapseDataGridView1.RowCount; rowIndex++)
                {
                    row = sheet.CreateRow(rowIndex+1);
                    cell = row.CreateCell(0);
                    cell.SetCellValue(collapseDataGridView1.Rows[rowIndex].Cells[0].Value.ToString());

                    cell = row.CreateCell(1);
                    cell.SetCellValue(collapseDataGridView1.Rows[rowIndex].Cells[1].Value.ToString());
                }


                fs = File.OpenWrite(filePath);
                workBook.Write(fs);
                MessageBox.Show("导入excel完成！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            finally
            {
                workBook = null;
                sheet = null;
                if(fs!=null)
                {
                    fs.Close();
                    
                }
               
            }


        }