NPOI 操作数据库中数据的导入导出(Excel.xls文件) 和null数据的处理。

App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <connectionStrings>
    <add name="sql" connectionString="Data Source=.;Initial Catalog=DBLQBZ;Integrated Security=True;"/>
  </connectionStrings>
</configuration>

*.sc

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
//using NPOI.HSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 数据的导入导出_Excel文件_
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
        private void btnBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog opd = new OpenFileDialog();
            opd.Filter = "Excel 数据文件|*.xls";
            opd.AddExtension = true;
            opd.Title = "请选择Excel数据文件：";
            opd.ShowDialog(this);
            txtExcelPath.Text = opd.FileName;
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            string sql = "select * from tblCusInfo";
            using (SqlDataReader reader = this.ExportData(sql, null))
            {
                if (reader.HasRows)
                {
                    using (FileStream fs = new FileStream(txtExcelPath.Text, FileMode.OpenOrCreate, FileAccess.Write))
                    {
                        //如果创建工作薄的时候指定了文件流，表示要打开一个Excel文件
                        //如果不指定文件流，则表示要创建一个新的工作薄（excel文件）
                        using (Workbook wb = new HSSFWorkbook())
                        {
                            Sheet sheet = wb.CreateSheet("Data");
                            Row name = sheet.CreateRow(0);
                            int fieldcount = reader.FieldCount;//列的数目
                            for (int i = 1; i < fieldcount; i++)
                            {
                                name.CreateCell(i).SetCellValue(reader.GetName(i));//得到表列名
                            }
                            int rows = 1;
                            while (reader.Read())
                            {
                                Row row = sheet.CreateRow(rows);
                                rows++;
                                for (int i = 1; i < fieldcount; i++)//去除自动编号列
                                {
                                    if (reader.IsDBNull(i))
                                    {
                                        row.CreateCell(i, CellType.BLANK);//空单元格
                                    }
                                    else
                                    {//类型很多具体问题具体对待
                                        string typename = reader.GetDataTypeName(i);//数据类型.ToString()
                                        switch (typename)
                                        {
                                            case "int":
                                                row.CreateCell(i, CellType.NUMERIC).SetCellValue(reader.GetInt32(i));
                                                break;
                                            case "bit":
                                                row.CreateCell(i, CellType.BOOLEAN).SetCellValue(reader.GetBoolean(i));
                                                break;
                                            default:
                                                row.CreateCell(i, CellType.STRING).SetCellValue(reader.GetString(i));
                                                break;
                                        }
                                    }
                                }
                            }
                            wb.Write(fs);
                        }
                        MessageBox.Show("导出成功！");
                    }
                }
            }
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            using (FileStream fs = File.OpenRead(txtExcelPath.Text))
            {
                using (Workbook wk = new HSSFWorkbook(fs))
                {
                    string sql = "insert into tblCusInfo(姓名,手机,数量,固定电话,车号,车架号) values(@name,@mphone,@count,@phone,@carNum,@carStruNum);";
                    Sheet sheet = wk.GetSheetAt(0);
                    int len = sheet.LastRowNum;//这里行号是从0开始的，表格中实际上是1.2...
                    for (int i = 1; i <= len; i++)//标题行省去
                    {
                        Row row = sheet.GetRow(i);
                        SqlParameter[] param = new SqlParameter[] {
                        new SqlParameter("@name",SqlDbType.NVarChar),
                        new SqlParameter("@mphone",SqlDbType.NVarChar),
                        new SqlParameter("@count",SqlDbType.Int),
                        new SqlParameter("@phone",SqlDbType.NVarChar),
                        new SqlParameter("@carNum",SqlDbType.NVarChar),
                        new SqlParameter("@carStruNum",SqlDbType.NVarChar)};
                        for (int j = 0; j < row.LastCellNum; j++)
                        {
                            //Excel中空单元格不能用如下方法判断
                            //1.若单元格格式是：字符串类型
                            //会报错：未将对象引用设置到对象的实例。
                            /* string value = row.GetCell(j).ToString();
                             if (value != string.Empty)
                             {
                                 param[j].Value = value;
                             }
                             //2.若单元格的格式是：数值类型
                             //空单元格被转换成："";
                             if (row.GetCell(j)!=null)
                             {
                                 string value = row.GetCell(j).ToString();
                                 param[j].Value = value;
                             }*/
                            //最好的方法是判断类型
                            if (row.GetCell(j) != null && row.GetCell(j).CellType != CellType.BLANK)
                            {
                                string value = row.GetCell(j).ToString();
                                param[j].Value = value;
                            }
                            else
                            {
                                param[j].Value = DBNull.Value;
                            }
                        }
                        this.ImportData(sql, param);
                    }
                }
                MessageBox.Show("数据导入成功！");
            }
        }

        private SqlDataReader ExportData(string sql, SqlParameter[] param)
        {
            //这里用using链接会释放的
            SqlConnection con = new SqlConnection(constr);

            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                con.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }

        }

        private int ImportData(string sql, SqlParameter[] param)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

Form.cs

namespace 数据的导入导出_Excel文件_
{
    partial class Form1
    {
        /// <summary>
        /// 必需的设计器变量。
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// 清理所有正在使用的资源。
        /// </summary>
        /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows 窗体设计器生成的代码

        /// <summary>
        /// 设计器支持所需的方法 - 不要
        /// 使用代码编辑器修改此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {
            this.btnBrowse = new System.Windows.Forms.Button();
            this.lblExcelPath = new System.Windows.Forms.Label();
            this.txtExcelPath = new System.Windows.Forms.TextBox();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.btnExport = new System.Windows.Forms.Button();
            this.btnImport = new System.Windows.Forms.Button();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // btnBrowse
            // 
            this.btnBrowse.Location = new System.Drawing.Point(194, 98);
            this.btnBrowse.Name = "btnBrowse";
            this.btnBrowse.Size = new System.Drawing.Size(75, 23);
            this.btnBrowse.TabIndex = 0;
            this.btnBrowse.Text = "浏览...";
            this.btnBrowse.UseVisualStyleBackColor = true;
            this.btnBrowse.Click += new System.EventHandler(this.btnBrowse_Click);
            // 
            // lblExcelPath
            // 
            this.lblExcelPath.AutoSize = true;
            this.lblExcelPath.Location = new System.Drawing.Point(9, 27);
            this.lblExcelPath.Name = "lblExcelPath";
            this.lblExcelPath.Size = new System.Drawing.Size(95, 12);
            this.lblExcelPath.TabIndex = 1;
            this.lblExcelPath.Text = "Excel文件路径：";
            // 
            // txtExcelPath
            // 
            this.txtExcelPath.Location = new System.Drawing.Point(11, 60);
            this.txtExcelPath.Name = "txtExcelPath";
            this.txtExcelPath.Size = new System.Drawing.Size(258, 21);
            this.txtExcelPath.TabIndex = 2;
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.lblExcelPath);
            this.groupBox1.Controls.Add(this.txtExcelPath);
            this.groupBox1.Controls.Add(this.btnBrowse);
            this.groupBox1.Location = new System.Drawing.Point(3, 12);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(279, 139);
            this.groupBox1.TabIndex = 3;
            this.groupBox1.TabStop = false;
            this.groupBox1.Text = "Excel文件路径选择";
            // 
            // btnExport
            // 
            this.btnExport.Location = new System.Drawing.Point(14, 189);
            this.btnExport.Name = "btnExport";
            this.btnExport.Size = new System.Drawing.Size(75, 23);
            this.btnExport.TabIndex = 4;
            this.btnExport.Text = "数据导出";
            this.btnExport.UseVisualStyleBackColor = true;
            this.btnExport.Click += new System.EventHandler(this.btnExport_Click);
            // 
            // btnImport
            // 
            this.btnImport.Location = new System.Drawing.Point(197, 189);
            this.btnImport.Name = "btnImput";
            this.btnImport.Size = new System.Drawing.Size(75, 23);
            this.btnImport.TabIndex = 5;
            this.btnImport.Text = "数据导入";
            this.btnImport.UseVisualStyleBackColor = true;
            this.btnImport.Click += new System.EventHandler(this.btnImport_Click);
            // 
            // Form1
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(284, 262);
            this.Controls.Add(this.btnImport);
            this.Controls.Add(this.btnExport);
            this.Controls.Add(this.groupBox1);
            this.Name = "Form1";
            this.Text = "Form1";
            this.groupBox1.ResumeLayout(false);
            this.groupBox1.PerformLayout();
            this.ResumeLayout(false);

        }

        #endregion

        private System.Windows.Forms.Button btnBrowse;
        private System.Windows.Forms.Label lblExcelPath;
        private System.Windows.Forms.TextBox txtExcelPath;
        private System.Windows.Forms.GroupBox groupBox1;
        private System.Windows.Forms.Button btnExport;
        private System.Windows.Forms.Button btnImport;
    }
}

项目文件和NPOI***.dll文件和测试数据文件：http://pan.baidu.com/s/1c0d3N2K