.NET操作Excel–NPOI组件的使用

   有很多时候需要将数据导入 Excel中或者从Excel中导出到数据库，这里我们看下.net中怎么操作excel的。

  本文是使用 NPOI来操作Excel的， NPOI能够分析Excel文件的格式，能够进行常用Excel操作，不依赖于Excel，节省资源，没有安全性、性能的问题，在ASP.net中用最合适。但：只能处理xls格式文件、不能处理xlsx这样的新版本。处理xlsx要用OpenXML。

   *需引用NPOI组件，文章最后有下载链接*

    读取Excel：

 private void button1_Click(object sender, EventArgs e)
        {
            //1.创建文件流
            using (FileStream fsRead = File.OpenRead("ReadExcel.xls"))
            {
                //2.创建工作薄
                IWorkbook wk = new HSSFWorkbook(fsRead);
                //3.循环读取当前工作薄中的所有工作表
                for (int i = 0; i < wk.NumberOfSheets; i++)
                {
                    ISheet sheet = wk.GetSheetAt(i);
                    Console.WriteLine("=============={0}==============", sheet.SheetName);
                    //4.输出工作表中的每一行
                    for (int r = 0; r <= sheet.LastRowNum; r++)
                    {
                        //5.获取每一行
                        IRow row = sheet.GetRow(r);
                        if (row != null)
                        {
                            //6.获取每一行的单元格
                            for (int c = 0; c < row.LastCellNum; c++)
                            {
                                ICell cell = row.GetCell(c);
                                if (cell != null)
                                {
                                    Console.Write(cell.ToString() + "\t");
                                }

                            }
                            Console.WriteLine();
                        }
                    }
                }
            }
  }

   Excel写入：

  private void button2_Click(object sender, EventArgs e)
        {
            List<Person> list = new List<Person>() {
            new Person(){ Name="张三", Age=19},
            new Person(){ Name="李四", Age=29},
            new Person(){ Name="王二", Age=39},
            };
            //1.创建Workbook
            IWorkbook wk = new HSSFWorkbook();
            //2.创建工作表
            ISheet sheet = wk.CreateSheet("Person");
            //3.写入行
            for (int i = 0; i < list.Count; i++)
            {
                IRow row = sheet.CreateRow(i);
                row.CreateCell(0).SetCellValue(list[i].Name);
                row.CreateCell(1).SetCellValue(list[i].Age);
            }
            using (FileStream fsWrite = File.OpenWrite("person.xls"))
            {
                wk.Write(fsWrite);
            }
            MessageBox.Show("ok");
        }

Excel的内容导入到数据库表：（需SqlHelper文件）

 private void button4_Click(object sender, EventArgs e)
        {
            using (FileStream fsRead = File.OpenRead("tseats.xls"))
            {
                //1.读取Excel
                IWorkbook wk = new HSSFWorkbook(fsRead);
                ISheet sheet = wk.GetSheetAt(0);
                string sql_insert = "insert into T_Seats values(@uid,@pwd,@uname,@errorTimes,@lockDate,@testint)";
                for (int r = 0; r <= sheet.LastRowNum; r++)
                {
                    IRow row = sheet.GetRow(r);
                    string loginId = row.GetCell(1).StringCellValue;
                    string password = row.GetCell(2).StringCellValue;
                    string username = row.GetCell(3).StringCellValue;
                    int errorTimes = (int)row.GetCell(4).NumericCellValue;
                    double? lockDate = null;
                    ICell cellLockDate = row.GetCell(5);
                    if (cellLockDate != null && cellLockDate.CellType != CellType.BLANK)
                    {
                        lockDate = row.GetCell(5).NumericCellValue;
                    }
                    else
                    {
                        //lockDate = null;
                    }

                    int? testInt = null;
                    ICell cellTestInt = row.GetCell(6);

                    if (cellTestInt != null && cellTestInt.CellType != CellType.BLANK)
                    {
                        testInt = (int)cellTestInt.NumericCellValue;
                    }
                    else
                    {
                        //testInt = null;
                    }

                    SqlParameter[] pms = new SqlParameter[] {
                        new SqlParameter("@uid",loginId),
                        new SqlParameter("@pwd",password),
                        new SqlParameter("@uname",username),
                        new SqlParameter("@errorTimes",errorTimes),
                        new SqlParameter("@lockDate",lockDate==null?DBNull.Value:(object)DateTime.FromOADate((double)lockDate)),
                        new SqlParameter("@testint",testInt==null?DBNull.Value:(object)testInt),
                    };
                    //2.执行插入操作
                    SqlHelper.ExecuteNonQuery(sql_insert, CommandType.Text, pms);
                }
            }
            MessageBox.Show("ok");

NPOI 组件与Openxml下载链接：http://pan.baidu.com/s/1eQgvSeI 密码：xmwd