<?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>

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();
                }
            }
        }
    }
}

View Code