NPOI导入，导出数据库

<pre name="code" class="csharp">using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace 将excel数据插入数据库
{
    class Program
    {
        private static readonly string str = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
        static void Main(string[] args)
        {
            //导入数据库
            using (FileStream stream = new FileStream("C:/Users/dong/Desktop/1234.xls", FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook work = new HSSFWorkbook(stream);
                ISheet sheet = work.GetSheetAt(0);  //注意当是导入的时候用GetSheetAt(0);，导出的时候.CreateSheet("员工数据");

                DataTable table = new DataTable(); //创建以个Datatable对象

                table.Columns.Add("Name", typeof(string));

                int num = sheet.LastRowNum;
                for (int i = 1; i <= num; i++)
                {
                    IRow row = sheet.GetRow(i);   //创建行
                    DataRow daterow = table.NewRow();
                    for (int j = 1; j < row.LastCellNum; j++)
                    {
                        daterow["Name"] = row.GetCell(j).StringCellValue;
                        table.Rows.Add(daterow);
                    }

                    //Student stu = new Student();
                    //stu.Name = row.GetCell(1).StringCellValue;
                    //SqlHelper.ExecuteNonQuery("insert into T_Excel(Name) values(@Name)",
                    //                                       new SqlParameter("@Name", stu.Name));

                }


                using (SqlBulkCopy bulk = new SqlBulkCopy(str))
                {
                    bulk.DestinationTableName = "T_Excel";
                    bulk.ColumnMappings.Add("Name", "Name");
                    bulk.WriteToServer(table);
                }
            }

            //导出数据库
            List<Student> list = new List<Student>();
            DataTable dt= SqlHelper.ExecuteDataTable("select * from T_Excel");
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(RowToModel(dr));
                }
            }
            using (FileStream stream = new FileStream("C:/Users/dong/Desktop/导出.xls", FileMode.Create, FileAccess.Write))
            {
                HSSFWorkbook work = new HSSFWorkbook();
                ISheet sheet = work.CreateSheet("员工数据");
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0, CellType.NUMERIC).SetCellValue("编号");
                row.CreateCell(1, CellType.NUMERIC).SetCellValue("姓名");
                row.CreateCell(2, CellType.NUMERIC).SetCellValue("入职日期");
                for (int i = 0; i <list.Count; i++)
                {
                   Student stu = list[i];
                   IRow irow = sheet.CreateRow(i+1);
                    irow.CreateCell(0, CellType.STRING).SetCellValue(stu.Id);
                    irow.CreateCell(1, CellType.STRING).SetCellValue(stu.Name);

                    //下面是日期类型的处理
                    ICellStyle style = work.CreateCellStyle();
                    IDataFormat format = work.CreateDataFormat();

                    style.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");
                    ICell cellInDate = irow.CreateCell(2,CellType.NUMERIC);
                    cellInDate.CellStyle = style;
                    cellInDate.SetCellValue(stu.Time);
                }
                work.Write(stream);
            }
        }

        public static Student RowToModel(DataRow dr)
        {
            Student s = new Student();
            s.Id=(long)dr["Id"];
            s.Name=(string)dr["Name"];
            s.Time=(DateTime)dr["Time"];
            return s;
        }
    }
}

当从数据库导出的时候，是已知数据，创建excel表，所以用到CreateSheet，CreateRow等。

当从excel导入数据库的时候，用到GetSheet，GetRow等。

日期类型的数据处理有些特殊，在代码中可以看出