NPOI导入，导出数据库

来源：互联网发布：费曼feynman知乎编辑：程序博客网时间：2024/05/20 13:41
<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等。
日期类型的数据处理有些特殊，在代码中可以看出
0 0