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();
int fieldcount = reader.FieldCount;//列的数目
for (int i = ; i < fieldcount; i++)
{
name.CreateCell(i).SetCellValue(reader.GetName(i));//得到表列名
}
int rows = ;
while (reader.Read())
{
Row row = sheet.CreateRow(rows);
rows++;
for (int i = ; 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();
int len = sheet.LastRowNum;//这里行号是从0开始的，表格中实际上是1.2...
for (int i = ; 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 = ; 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(, );
this.btnBrowse.Name = "btnBrowse";
this.btnBrowse.Size = new System.Drawing.Size(, );
this.btnBrowse.TabIndex = ;
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(, );
this.lblExcelPath.Name = "lblExcelPath";
this.lblExcelPath.Size = new System.Drawing.Size(, );
this.lblExcelPath.TabIndex = ;
this.lblExcelPath.Text = "Excel文件路径：";
//
// txtExcelPath
//
this.txtExcelPath.Location = new System.Drawing.Point(, );
this.txtExcelPath.Name = "txtExcelPath";
this.txtExcelPath.Size = new System.Drawing.Size(, );
this.txtExcelPath.TabIndex = ;
//
// 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(, );
this.groupBox1.Name = "groupBox1";
this.groupBox1.Size = new System.Drawing.Size(, );
this.groupBox1.TabIndex = ;
this.groupBox1.TabStop = false;
this.groupBox1.Text = "Excel文件路径选择";
//
// btnExport
//
this.btnExport.Location = new System.Drawing.Point(, );
this.btnExport.Name = "btnExport";
this.btnExport.Size = new System.Drawing.Size(, );
this.btnExport.TabIndex = ;
this.btnExport.Text = "数据导出";
this.btnExport.UseVisualStyleBackColor = true;
this.btnExport.Click += new System.EventHandler(this.btnExport_Click);
//
// btnImport
//
this.btnImport.Location = new System.Drawing.Point(, );
this.btnImport.Name = "btnImput";
this.btnImport.Size = new System.Drawing.Size(, );
this.btnImport.TabIndex = ;
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(, );
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
