c# winfrom 使用npoi读取excel并存入数据库

1.界面样式

2.代码片段

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            openFileDialog1.Filter = "*.xls(Excel文件)|*.xls|*.xlsx(Excel文件)|*.xlsx";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                textBox1.Text = openFileDialog1.FileName;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrEmpty(textBox1.Text))
            {
                MessageBox.Show("选择文件后重试");
            }
            else
            {
                //GetExcelTableNames(textBox1.Text);
                dataGridView1.DataSource = null; //每次打开清空内容
                //DataTable dt = ReadExcelToTable(textBox1.Text);
                DataTable dt = ExcelToDataTable(textBox1.Text, "Sheet1", true);
                dataGridView1.DataSource = dt;
                var count = insert(dt);
                MessageBox.Show("更新成功，共" + count + "条");
            }
        }
        ///使用oledb的方法存在问题 必须打开这excl才行  否则会报错
        public static DataTable ReadExcelToTable(string path)//excel存放的路径
        {
            try
            {
                if (string.IsNullOrEmpty(path))
                {
                    return null;
                }
                //获取文件扩展名
                string strExtension = System.IO.Path.GetExtension(path);
                string strFileName = System.IO.Path.GetFileName(path);
                //Excel的连接
                OleDbConnection conn = null;
                #region
                //switch (strExtension)
                //{
                //    case ".xls":
                //        conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
                //        break;
                //    case ".xlsx":
                //        conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)  备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数，"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 
                //        break;
                //    default:
                //        conn = null;
                //        break;
                //}
                #endregion
                
                conn = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=yes;IMEX=1';"); //Office 07以下版本 
                if (conn == null)
                {
                    return null;
                }
                #region
                //连接字符串
                //string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
                //string connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本 
                //using (OleDbConnection conn = new OleDbConnection(connstring))
                #endregion
                using (conn)
                {
                    conn.Open();
                    DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
                    string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
                    string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串                    //string sql = string.Format("SELECT * FROM [{0}] WHERE [日期] is not null", firstSheetName); //查询字符串
                    OleDbDataAdapter ada = new OleDbDataAdapter(sql, conn);
                    DataSet set = new DataSet();
                    ada.Fill(set);
                    List<renyuan> ry = new List<renyuan>();
                    foreach (DataRow mDr in set.Tables[0].Rows)
                    {
                        renyuan rymodel = new renyuan();
                        foreach (DataColumn mDc in set.Tables[0].Columns)
                        {
                            string colname = mDc.ColumnName;//获取当前列的列名字
                            switch (colname)
                            {
                                case "序号":
                                    rymodel.Id = Convert.ToInt32(mDr[mDc]);
                                    break;
                                case "姓名":
                                    rymodel.Name = mDr[mDc].ToString();
                                    break;
                                case "性别":
                                    rymodel.Sex = mDr[mDc].ToString();
                                    break;
                                case "年龄":
                                    rymodel.Age = Convert.ToInt32(mDr[mDc]);
                                    break;
                                default:
                                    break;
                            }
                            //Console.WriteLine(mDr[mDc].ToString());
                        }
                        ry.Add(rymodel);
                    }
                    var sqlcon = sqlconn();
                    var re = sqlcon.Queryable<renyuan>().ToList();
                    var res = sqlcon.Insertable(ry).ExecuteCommand();//将实体更新到数据库
                    return set.Tables[0];
                }
            }
            catch (Exception ex)
            {
                var a = ex;
                return null;
            }

        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
        
        /// <summary>
        /// npoi 将excel解析到datatable
        /// </summary>
        /// <param name="fileName">文件绝对路径</param>
        /// <param name="sheetName">工作表的名称</param>
        /// <param name="isFirstRowColumn">是否将第一行当作列名</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn)
        {
            try
            {
                ISheet sheet = null;
                DataTable data = new DataTable();
                int startRow = 0;
                using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = null;
                    if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    if (sheetName != null)
                    {
                        sheet = workbook.GetSheet(sheetName);
                    }
                    else
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    if (sheet != null)
                    {
                        IRow firstRow = sheet.GetRow(0);
                        int cellCount = firstRow.LastCellNum; // 列数
                        if (isFirstRowColumn)
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
                                data.Columns.Add(column);
                            }
                            startRow = sheet.FirstRowNum + 1;
                        }
                        else
                        {
                            startRow = sheet.FirstRowNum;
                        }
                        int rowCount = sheet.LastRowNum; // 行数
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row == null) continue;
                            DataRow dataRow = data.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = row.GetCell(j).ToString();
                                }
                            }
                            data.Rows.Add(dataRow);
                        }
                    }
                }
                return data;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }

        }
        /// <summary>
        /// 将数据更新到数据库
        /// </summary>
        /// <param name="a">datatable</param>
        /// <returns></returns>
        private static int insert(DataTable a)
        {
            List<renyuan> ry = new List<renyuan>();
            foreach (DataRow mDr in a.Rows)
            {
                renyuan rymodel = new renyuan();
                foreach (DataColumn mDc in a.Columns)
                {
                    string colname = mDc.ColumnName;//获取当前列的列名字
                    switch (colname)
                    {
                        case "序号":
                            rymodel.Id = Convert.ToInt32(mDr[mDc]);
                            break;
                        case "姓名":
                            rymodel.Name = mDr[mDc].ToString();
                            break;
                        case "性别":
                            rymodel.Sex = mDr[mDc].ToString();
                            break;
                        case "年龄":
                            rymodel.Age = Convert.ToInt32(mDr[mDc]);
                            break;
                        default:
                            break;
                    }
                    //Console.WriteLine(mDr[mDc].ToString());
                }
                ry.Add(rymodel);
            }
            var sqlcon = sqlconn();
            var res = sqlcon.Insertable(ry).ExecuteCommand();//将实体更新到数据库
            return res;
        }
        /// <summary>
        /// sqlserver数据库
        /// </summary>
        /// <returns></returns>
        private static SqlSugarClient sqlconn() {
            SqlSugarClient db = new SqlSugarClient(
                    new ConnectionConfig()
                    {
                        ConnectionString = "server =.; uid = sa; pwd = q@123; database = test",
                        DbType = SqlSugar.DbType.SqlServer,
                        IsAutoCloseConnection = true
                    }
           );
            return db;
        }
    }
}

 3.需要安装的包

 4.测试数据如下