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