WinFrm导出数据到dbf文件
来源：互联网 发布：芯片设计工程师 知乎 编辑：程序博客网 时间：2024/04/30 05:27
1.在写程序之前，先说明下dbf文件的特性以及保存时遇到的问题，以此来供大家参考！
（1）如果要在程序中新创建DBF文件，要导出列不能太多，否则创建不成功，即提示：字段将不能置于记录中；
注：和数据的多少无关（dbf文件 多个(32)个长字段(200)，创建时即报错）
（2）在已知的dbf表中插入数据，dbf文件名即就是表名(包括后缀)；文件名（不包含后缀）不能太长，最好改至8个字符以内，否则无法插入时无法识别该表
2.程序功能
（1）根据时间和选择条件从Oracle数据库查询出数据，显示到GridView视图上
（2）点击导出按钮，支持创建新的DBF文件和插入已有DBF文件两种格式，将数据导成DBF格式的文件
3.代码详解
public partial class Form1 : Form { public Form1() { InitializeComponent(); } DataTable dt = new DataTable(); private void btSearch_Click(object sender, EventArgs e) { if (dtpEndTime.Value.CompareTo(this.dtpStartTime.Value) == -1) { MessageBox.Show("开始时间应该小于结束时间!","提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); return; } string startime = DateTimeToString(dtpStartTime.Value.Year, dtpStartTime.Value.Month.ToString(), dtpStartTime.Value.Day.ToString()); string endtime = DateTimeToString(dtpEndTime.Value.Year,dtpEndTime.Value.Month.ToString(),dtpEndTime.Value.Day.ToString()); string connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.x.xx)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=ORCL)));User ID= ;Password= ;"; try { OracleConnection oracleStr = new OracleConnection(connStr); string sql = null; if (RadioButton_XY.Checked == true) { sql = string.Format("Select * From viewortablename where colunmname>='{0}' AND colunmname<='{1}'", startime, endtime); } else { sql = string.Format("Select * From viewortablename where colunmname>='{0}' AND colunmname<='{1}'", startime, endtime); } OracleCommand oc1 = new OracleCommand(sql, oracleStr); //OracleCommand oc1 = new OracleCommand("select * from amsh_abnormity where ourid = 201603225272877", oracleStr); DataSet ds1 = new DataSet(); OracleDataAdapter da1 = new OracleDataAdapter(); da1.SelectCommand = oc1; da1.Fill(ds1); //通过展示数据列表的datasource属性与上述已填充数据的空器进行关联．记得空器可能包含多个表，要用dataset.tables[0],仅提取一个表 this.dataGridView.DataSource = ds1.Tables[0]; dt = ds1.Tables[0]; this.LB_AmountCount.Text = string.Format("总数据有{0}条",dt.Rows.Count); this.LB_ExportCount.Text = string.Format("已导出0条"); } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void btExport_Click(object sender, EventArgs e) { if (dataGridView.DataSource != null && (dataGridView.DataSource as DataTable).Rows.Count != 0) { DialogResult dr = MessageBox.Show("是否创建新的DBF文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dr == DialogResult.Yes) { #region 创建新的DBF导出方法,用于列较少的表 SaveFileDialog dlg = new SaveFileDialog(); dlg.Filter = "DBF文件(*.dbf)|*.dbf|BDF文件(*.dbf)|*.dbf"; dlg.FileName = "请指定文件名，且控制在8个字符内"; if (dlg.ShowDialog() == DialogResult.OK) { string fName = dlg.FileName; FileInfo fi = new FileInfo(@dlg.FileName); string mulu = fi.DirectoryName; dt.TableName = fi.Name; DbfExportHelper helper = new DbfExportHelper(@mulu); if (helper.CreateNewTable(dt)) { int insertcount = 0; int count = 0; this.progressBar1.Maximum = dt.Rows.Count; foreach (DataRow row in dt.Rows) { if (!helper.batchfillData(dt, insertcount)) { if (count > 0) { count--; } break; } count = insertcount++; if (insertcount <= dt.Rows.Count) { //进度条控件 this.progressBar1.Value++; } else { this.progressBar1.Value = 0; } } this.progressBar1.Value = 0; this.LB_ExportCount.Text = string.Format("已导出{0}条", count + 1); MessageBox.Show("导出完成！", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } } #endregion } else { #region 通过FileDialog获取文件导出方法 OpenFileDialog dlg = new OpenFileDialog(); dlg.Filter = "DBF文件(*.dbf)|*.dbf|BDF文件(*.dbf)|*.dbf"; if (dlg.ShowDialog() == DialogResult.OK) { string fName = dlg.FileName; FileInfo fi = new FileInfo(@dlg.FileName); string mulu = fi.DirectoryName; dt.TableName = fi.Name; DbfExportHelper helper = new DbfExportHelper(@mulu); int j = helper.GetDBFColumnData(fi.Name); if(j==0) { if (helper.DeleteDbfData(dt.TableName)) { int insertcount=0; int count = 0; this.progressBar1.Maximum = dt.Rows.Count; foreach (DataRow row in dt.Rows) { if (!helper.batchfillData(dt, insertcount)) { if (count > 0) { count--; } break; } count = insertcount++; if (insertcount <= dt.Rows.Count) { this.progressBar1.Value++; } else { this.progressBar1.Value = 0; } } this.progressBar1.Value = 0; this.LB_ExportCount.Text = string.Format("已导出{0}条", count + 1); MessageBox.Show("导出完成！", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } } else if (j== 1) { int insertcount = 0; int count = 0; this.progressBar1.Maximum = dt.Rows.Count; foreach (DataRow row in dt.Rows) { if (!helper.batchfillData(dt, insertcount)) { if (count > 0) { count--; } break; } count = insertcount++; if (insertcount <= dt.Rows.Count) { this.progressBar1.Value++; } else { this.progressBar1.Value = 0; } } this.progressBar1.Value = 0;; this.LB_ExportCount.Text = string.Format("已导出{0}条", count + 1); MessageBox.Show("导出完成！", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } } #endregion } #region 已知固定路径直接在DBF文件中插入数据 //FileInfo fi = new FileInfo(@"D:\DBF\N042_YH.dbf"); //string mulu = fi.DirectoryName; //dt.TableName = fi.Name; //DbfExportHelper helper = new DbfExportHelper(@"D:\DBF"); //if (helper.GetDBFData(fi.Name)) //{ // helper.DeleteDbfData(dt.TableName); // helper.fillData(dt); //} //else //{ // helper.CreateNewTable(dt); // helper.fillData(dt); //} #endregion } else { MessageBox.Show("请点击查询按钮或更改条件再次查询！","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning); } } public string DateTimeToString(int year,string month,string day) { StringBuilder datestr = new StringBuilder(); datestr.Append(year); if (month.Length == 1) { month = "0" + month; } datestr.Append(month); if (day.Length == 1) { day = "0" + day; } datestr.Append(day); return datestr.ToString(); } } public class DbfExportHelper { List dbfcolumnName = new List(); /// /// 数据库所在路径 /// private string filePath = ""; /// /// 连接字符串 /// private string connstring = ""; /// /// 数据库连接 /// private OleDbConnection Connection = new OleDbConnection(); /// /// 错误信息 /// private string _ErrInfo; /// /// 构造函数 /// /// dbf文件所在文件夹路径 public DbfExportHelper(string filePath) { this.filePath = filePath; this.connstring = string.Format("Provider = Microsoft.Jet.OLEDB.4.0 ;Data Source ={0};Extended Properties=dBASE IV;", filePath); this.Connection = new OleDbConnection(connstring); } /// /// 改变数据库所在路径 /// /// 新文件夹路径 /// /// 构造dbf文件，文件名称为dt的表名，后缀名为dbf /// /// 待写入的表格数据 /// cols = new List(); StringBuilder cols = new StringBuilder();//和List要实现的功能相同 foreach (DataColumn dc in dt.Columns) { string colType = ""; string colName = dc.ColumnName; switch (dc.DataType.Name) { case "Boolean": colType = "bool"; break; case "Double": case "Float": colType = "double"; break; case "Int16": case "Int32": case "Int64": case "Int": colType = "int"; break; case "String": colType = "varchar"; break; default: colType = "varchar"; break; } //cols.Add(string.Format(@"{0} {1}", colName, colType)); if (i < dt.Columns.Count - 1) { cols.Append(string.Format("{0} {1}" + ",", colName, colType)); i++; } else { cols.Append(string.Format("{0} {1}", colName, colType)); } } string cols_where = cols.ToString(); //string cols_where = string.Join(",", cols); string sql = string.Format(@"CREATE TABLE {0} ({1})", dt.TableName, cols_where); command.CommandText = sql; command.ExecuteNonQuery(); success = true; } catch (Exception c) { _ErrInfo = c.Message; MessageBox.Show("创建失败，错误提示信息为：" + _ErrInfo, "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { command.Dispose(); if (Connection.State == System.Data.ConnectionState.Open) Connection.Close(); command.Dispose(); } return success; } /// /// 查看已知DBF中是否存在数据,并获取列名与表对比 /// /// ///删除DBF表文件的数据 /// public bool DeleteDbfData(string tablename) { bool deletedbf = true; OleDbCommand dc = Connection.CreateCommand(); try { Connection.Open(); string sqldelete = @"delete from " + tablename; dc.CommandText = sqldelete; dc.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(tablename+"中的数据无法被删除，请确认该文件是否在其他地方被打开！","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning); deletedbf = false; } finally { if (Connection != null) Connection.Close(); dc.Dispose(); } return deletedbf; } /// /// 导入数据到dbf文件 /// /// /// 导入的数据条数 public bool batchfillData(DataTable dt,int i) { bool batchexportsuccess = true; OleDbCommand dc = Connection.CreateCommand(); Connection.Open(); try { if(dbfcolumnName!=null&&dbfcolumnName.Count > 0) { string sqlInsert = "insert into " + dt.TableName + "({0}) values ({1})"; string invalues = ""; string cols = ""; foreach (DataColumn col in dt.Columns) { bool bl = false; foreach (string dbf in dbfcolumnName) { if (col.ColumnName.Equals(dbf)) { bl = true; break; } } if (bl) { if (dt.Rows[i][col].ToString() != string.Empty && dt.Rows[i][col].ToString() != null && dt.Rows[i][col].ToString() != "null") { cols += col.ColumnName + ","; if (col.DataType == typeof(string)) { invalues += "'" + dt.Rows[i][col].ToString() + "',"; } else if (col.DataType == typeof(DateTime)) { invalues += "'" + dt.Rows[i][col].ToString() + "',"; } else { invalues += dt.Rows[i][col].ToString() + ","; } } } } invalues = invalues.Remove(invalues.Length - 1, 1); cols = cols.Remove(cols.Length - 1, 1); sqlInsert = string.Format(sqlInsert, cols, invalues); dc.CommandText = sqlInsert; dc.ExecuteNonQuery(); } else { string sqlInsert = "insert into " + dt.TableName + "({0}) values({1})"; string invalues = ""; string cols = ""; foreach (DataColumn col in dt.Columns) { if (dt.Rows[i][col].ToString() != string.Empty && dt.Rows[i][col].ToString() != null && dt.Rows[i][col].ToString() != "null") { cols += col.ColumnName + ","; if (col.DataType == typeof(string)) { invalues += "'" + dt.Rows[i][col].ToString() + "',"; } else if (col.DataType == typeof(DateTime)) { invalues += "'" + dt.Rows[i][col].ToString() + "',"; } else { invalues += dt.Rows[i][col].ToString() + ","; } } } invalues = invalues.Remove(invalues.Length - 1, 1); cols = cols.Remove(cols.Length - 1, 1); sqlInsert = string.Format(sqlInsert, cols, invalues); dc.CommandText = sqlInsert; dc.ExecuteNonQuery(); } } catch (Exception ex) { DialogResult dr = MessageBox.Show("第"+i+1+"条数据插入失败，失败提示信息为："+ex.Message+"是否继续？","警告",MessageBoxButtons.YesNo,MessageBoxIcon.Warning); if (dr == DialogResult.No) { batchexportsuccess = false; } } finally { if (Connection != null) Connection.Close(); dc.Dispose(); } return batchexportsuccess; } /* 无滚动条或显示导出条数的导出 public bool fillData(DataTable dt) { bool exportsuccess = true; int count = 0; OleDbCommand dc = Connection.CreateCommand(); _ErrInfo = ""; try { Connection.Open(); //导入数据 if (dbfcolumnName!=null&&dbfcolumnName.Count > 0) { foreach (DataRow row in dt.Rows) { string sqlInsert = "insert into " + dt.TableName + "({0}) values ({1})"; string invalues = ""; string cols = ""; foreach (DataColumn col in dt.Columns) { bool bl = false; foreach (string dbf in dbfcolumnName) { if (col.ColumnName.Equals(dbf)) { bl = true; break; } } if (bl) { if (row[col].ToString() != string.Empty && row[col].ToString() != null && row[col].ToString() != "null") { cols += col.ColumnName + ","; if (col.DataType == typeof(string)) { invalues += "'" + row[col].ToString() + "',"; } else if (col.DataType == typeof(DateTime)) { invalues += "'" + row[col].ToString() + "',"; } else { invalues += row[col].ToString() + ","; } } } } invalues = invalues.Remove(invalues.Length - 1, 1); cols = cols.Remove(cols.Length - 1, 1); sqlInsert = string.Format(sqlInsert, cols, invalues); dc.CommandText = sqlInsert; count += dc.ExecuteNonQuery(); InsertCount(count); } } else { foreach (DataRow row in dt.Rows) { string sqlInsert = "insert into " + dt.TableName + "({0}) values({1})"; string invalues = ""; string cols = ""; foreach (DataColumn col in dt.Columns) { if (row[col].ToString() != string.Empty && row[col].ToString() != null && row[col].ToString() != "null") { cols += col.ColumnName + ","; if (col.DataType == typeof(string)) { invalues += "'" + row[col].ToString() + "',"; } else if (col.DataType == typeof(DateTime)) { invalues += "'" + row[col].ToString() + "',"; } else { invalues += row[col].ToString() + ","; } } } invalues = invalues.Remove(invalues.Length - 1, 1); cols = cols.Remove(cols.Length - 1, 1); sqlInsert = string.Format(sqlInsert, cols, invalues); dc.CommandText = sqlInsert; count += dc.ExecuteNonQuery(); InsertCount(count); } } } catch (Exception err) { _ErrInfo = err.Message; MessageBox.Show("导出失败,错误信息为："+_ErrInfo, "警告", MessageBoxButtons.OK, MessageBoxIcon.Error); exportsuccess = false; } finally { if (Connection != null) Connection.Close(); dc.Dispose(); MessageBox.Show("导出成功!", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } return exportsuccess; } */ /// /// 摧毁对象 /// public void Dispose() { if (Connection != null) Connection.Dispose(); } } 4.运行结果：
5.最后声明：保存好的DBF文件可以用EXCEL打开
6.其他相关文章链接http://blog.csdn.net/dujiajiyiyi/article/details/53841398?
https://my.oschina.net/Tsybius2014/blog/282797
阅读全文
0 0
- WinFrm导出数据到dbf文件
- c#实现将DataTable中的数据导出到DBF文件
- Oracle数据导出到DBF的问题
- 读取oracle数据表，导出到DBF文件。
- asp.net中将数据导出为dbf文件
- 用javadbf从数据库导出数据成dbf文件
- DBF文件初步了解（二）——DBF数据导出代码实现
- DBF 文件数据读取
- 导出dbf格式的文件
- C# 读取dbf文件中的数据到datatable中
- DBF文件输出到WORD
- VB从后台SQL SERVER导出数据到客户端DBF的方法
- VB从后台SQL SERVER导出数据到客户端DBF的方法
- dbf文件数据导入数据库
- 数据导出到TXT文件
- 导出资源数据到文件
- 导出数据到CSV文件
- oracle导出数据到文件
- $.ajax()方法详解
- Linux Shell脚本编程－－cut命令
- TensorFlow 入门
- 【数据库-SQLServer】时间-日期语法
- TS协议解析第一部分（PAT）
- WinFrm导出数据到dbf文件
- 左耳朵耗子：Go语言，Docker和新技术
- Tomcat配置
- 广搜
- 提高MySQL性能的7个技巧
- MySQL INFORMATION_SCHEMA 使用
- 数据结构之线索二叉树的前序,中序和后序遍
- 【JavaWeb】springmvc + hibernate整合配置
- 开发者最讨厌的编程语言：Perl 名列第一，PHP、Objective-C 和 Ruby 都在其中
