[C#][ASP.NET]DataSet,DataTable(DataSet)导出至指定XLS,DBF模版

详细链接:https://shop499704308.taobao.com/?spm=a1z38n.10677092.card.11.594c1debsAGeak 

using System;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data;
using System.Collections;
using System.Text;
using System.IO;

using System.Data.SqlClient;

 

.cs类中

............

public class DbFileMapDataSet{

          private DataSet my_Ds;//存放文件的数据集
private string my_TableName;//传入的文件名
private TableType my_TableType;//传入的文件类型
private string my_TablePath;//传入的文件路径
        OleDbCommandBuilder my_Builder;//命令串
        private string my_DbfTemplatePath;//DBF模版存放路径


/// <summary>
/// 数据库连接变量
/// </summary>
private string my_StrConnection;//连接字符串
private string my_StrSelect;//select语句

 

/// <summary>
/// 可以处理的文件类型
/// </summary>
public enum TableType
{
MDB,XLS,DBF,VFPDBF,DOC,TXT,XML,HTML
}
  
public DbFileMapDataSet(string TablePath,string TableName,TableType TableType)
{
///<summary>
///获得传入的路径,文件名及文件类型;
///</summary>
this.my_TablePath=TablePath;//路径
this.my_TableName=TableName;//文件名
this.my_TableType=TableType;//文件类型
}


        public DbFileMapDataSet(string TablePath, string TableName, TableType TableType, string DbfTemplatePath)
        {
            ///<summary>
            ///获得传入的路径,文件名及文件类型;
            ///</summary>
            this.my_TablePath = TablePath;//路径
            this.my_TableName = TableName;//文件名
            this.my_TableType = TableType;//文件类型
            this.my_DbfTemplatePath = DbfTemplatePath;//模版路径
        }

 

public bool Convert(DataSet ds)
{
bool returnvalue = false; 
switch (this.my_TableType)
{
 
case TableType.DBF:
returnvalue = this.DsToDbf(ds);
break;
                case TableType.XLS:
                    returnvalue = this.DsToXls(ds);
                    break;
 
}
return returnvalue;
        }

 

  /// <summary>
        /// 将DataSet 表的内容导入到XLS
        /// </summary>
        /// <param name="ds">DataSet集合</param>
        /// <returns>ture,false</returns>
        private bool DsToXls(DataSet ds)
        {
            try
            {
                File.Delete(this.my_TablePath + this.my_TableName + ".xls");
            }
            catch { }
            //数据库连接定义
            OleDbConnection my_conn; //数据连接 
            OleDbCommand my_Command;


            //数据库连接
            this.my_StrConnection = "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + this.my_TablePath + this.my_TableName + ".xls";
            my_conn = new OleDbConnection(this.my_StrConnection);
            my_conn.Open();
                
            my_Command = new OleDbCommand("", my_conn);
          
            Int32[] columnSize = new Int32[ds.Tables[0].Columns.Count];
            Int32[] rowSize = new Int32[ds.Tables[0].Rows.Count];
            int i = 0;
            //取字段最大长度
            foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
            {
                //数组初值
                columnSize[i] = 0;
                i++;
            }
            //取行数
            i = 0;
            foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
            {
                //数组初值
                rowSize[i] = 0;
                i++;
            }


            foreach (System.Data.DataRow dr1 in ds.Tables[0].Rows)
            {
                i = 0;
                foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
                {
                    Byte[] a = Encoding.Unicode.GetBytes(dr1[column.ColumnName].ToString());
                    if (a.Length > columnSize[i])
                    {
                        columnSize[i] = a.Length;
                    }


                    i++;
                }
            }
            //创建新表
            my_Command.CommandText = " create table " + this.my_TableName + "( ";
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            i = 0;
            foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
            {
                if (columnSize[i] > 0)//
                {
                    if (columnSize[i] > 220)
                        my_Command.CommandText += column.ColumnName + " varchar(220),";
                    else
                        my_Command.CommandText += column.ColumnName + " varchar(" + columnSize[i].ToString() + "),";
                }
                else
                    my_Command.CommandText += column.ColumnName + " varchar(20),";
                i++;


            }
            my_Command.CommandText = my_Command.CommandText.Substring(0, my_Command.CommandText.Length - 1) + ")";
            my_Command.ExecuteNonQuery();
          


            foreach (System.Data.DataRow dr1 in ds.Tables[0].Rows)
            {
                my_Command.CommandText = "insert into " + this.my_TableName + " (";
                foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
                {
                    my_Command.CommandText += column.ColumnName + ",";


                }
                my_Command.CommandText = my_Command.CommandText.Substring(0, my_Command.CommandText.Length - 1) + ") ";
                my_Command.CommandText += " values( ";
                i = 0;
                foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
                {
                    my_Command.CommandText += "'" + dr1[column.ColumnName].ToString() + "',";
                    Byte[] a = Encoding.Unicode.GetBytes(dr1[column.ColumnName].ToString());
                    if (a.Length > columnSize[i])
                        columnSize[i] = a.Length;
                    i++;
                }
                my_Command.CommandText = my_Command.CommandText.Substring(0, my_Command.CommandText.Length - 1) + ") ";
                my_Command.ExecuteNonQuery();
            }
            
            my_conn.Close();
            return true; 
        }

 

 

/// <summary>
/// 将DataSet 表的内容导入到DBF
/// </summary>
        /// <param name="ds">DataSet集合</param>
        /// <returns>ture,false</returns>
private bool DsToDbf(DataSet ds)

//数据库连接定义
OdbcConnection my_conn; //数据连接 
OdbcCommand my_Command;


//数据库连接
this.my_StrConnection= "Driver={Microsoft dBASE Driver (*.dbf)}; DriverID=277;Dbq=" + this.my_TablePath;
my_conn = new OdbcConnection(this.my_StrConnection);
my_conn.Open();

my_Command = new OdbcCommand("",my_conn);
//删除文件
try
{
File.Delete(this.my_TablePath+this.my_TableName+".dbf" );
}
catch{}
Int32[] columnSize = new Int32[ds.Tables[0].Columns.Count];
Int32[] rowSize =new Int32[ds.Tables[0].Rows.Count];
int i=0;
//取字段最大长度
foreach (System.Data.DataColumn column in ds.Tables[0].Columns)

//数组初值
columnSize[i]=0;
i++;

//取行数
i=0;
foreach (System.Data.DataRow dr in ds.Tables[0].Rows)

//数组初值
rowSize[i]=0;
i++;
}  


foreach (System.Data.DataRow dr1 in ds.Tables[0].Rows)
{
i=0;
foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
{
Byte[] a = Encoding.Unicode.GetBytes(dr1[column.ColumnName].ToString());
if (a.Length>columnSize[i])
{
columnSize[i]=a.Length;  
}


i++;
}
}
//创建新表
my_Command.CommandText =" create table "+this.my_TableName+"( ";
System.Text.StringBuilder sql=new System.Text.StringBuilder(); 
i=0;
foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
{  
if(columnSize[i]>0)//
{
if(columnSize[i]>220)
                      my_Command.CommandText += column.ColumnName+" varchar(220),";
else
                      my_Command.CommandText += column.ColumnName+" varchar("+columnSize[i].ToString()+"),"; 
}
else
my_Command.CommandText += column.ColumnName+" varchar(20),"; 
i++;


}
my_Command.CommandText=my_Command.CommandText.Substring(0,my_Command.CommandText.Length-1)+")"; 
my_Command.ExecuteNonQuery();



foreach (System.Data.DataRow dr1 in ds.Tables[0].Rows)
{
my_Command.CommandText = "insert into "+this.my_TableName+" (";
foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
{
my_Command.CommandText += column.ColumnName+",";


}
my_Command.CommandText=my_Command.CommandText.Substring(0,my_Command.CommandText.Length-1)+") ";
my_Command.CommandText+=" values( ";
i=0;
foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
{
my_Command.CommandText += "'"+dr1[column.ColumnName].ToString()+"',";
Byte[] a = Encoding.Unicode.GetBytes(dr1[column.ColumnName].ToString()); 
if (a.Length>columnSize[i])
columnSize[i]=a.Length;
i++;
}
my_Command.CommandText=my_Command.CommandText.Substring(0,my_Command.CommandText.Length-1)+") "; 
my_Command.ExecuteNonQuery();
}

my_conn.Close(); 
return true; 
}

 

/// <summary>
        /// 将DataTable导出至指定DBF模版
        /// </summary>
        /// <param name="dt">内存中数据的一个表</param>
        /// <param name="dbftemplatePath">指定DBF模版所在路径</param>
        /// <returns></returns>
        private bool DtToDbf(DataTable dt)
        {
            //删除文件
            try
            {
                File.Delete(this.my_TablePath + this.my_TableName + ".dbf");
                File.Copy(this.my_DbfTemplatePath + this.my_TableName + ".dbf", this.my_TablePath + this.my_TableName + ".dbf");
            }
            catch { }
            //数据库连接定义
            OdbcConnection my_conn; //数据连接 
            OdbcCommand my_Command;


            OdbcDataAdapter my_Adapter;//数据适配器


            //数据库连接
            this.my_StrConnection = "Driver={Microsoft dBASE Driver (*.dbf)}; DriverID=277;Dbq=" + this.my_TablePath;


            this.my_StrSelect = "SELECT * FROM " + this.my_TableName;
            my_conn = new OdbcConnection(this.my_StrConnection);
            my_Command = new OdbcCommand("", my_conn);
            try
            {
                my_conn.Open();
                my_Adapter = new OdbcDataAdapter(this.my_StrSelect, my_conn);
                this.my_Ds = new DataSet();


                //填充数据集
                my_Adapter.Fill(this.my_Ds, this.my_TableName);
                string[] ColumnName = new string[my_Ds.Tables[0].Columns.Count];
     
                int i = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    i = 0;
                    my_Command.CommandText = "insert into " + this.my_TableName + "(";
                    foreach (System.Data.DataColumn column in my_Ds.Tables[0].Columns)
                    {
                        my_Command.CommandText += column.ColumnName + ",";
                        ColumnName[i] = column.ColumnName;
                        i++;
                    }
                    my_Command.CommandText = my_Command.CommandText.Substring(0, my_Command.CommandText.Length - 1) + ") values ( ";
                    for (i = 0; i < ColumnName.Length; i++)
                    {
                        my_Command.CommandText += "'" + dr[ColumnName[i].ToString()].ToString() + "',";
                    }
                    my_Command.CommandText = my_Command.CommandText.Substring(0, my_Command.CommandText.Length - 1) + ") ";
                    my_Command.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                string a = ex.Message;
                return false;
            }
            finally
            {
                my_conn.Close();
            }
        }

}

.............

 

 

 

protected void lnkToExcel_Click(object sender, System.EventArgs e)
{

            string tablename = "total";
            DbFileMapDataSet(this.Server.MapPath("../out/"), tablename, DbFileMapDataSet.TableType.XLS);
            System.Data.DataSet ds = GetDateSet();
            string sUrl = "";
            if (obj.Convert(ds))
            {
                sUrl = UrlBase + "/out/" + tablename + ".xls";
                Response.Write("<script language=javascript>window.open('" + sUrl + "')</script>");


            }
            else
            {
                PageCtrl.ShowMessage(this, "导出失败!");
                //return "";
            } 

}

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
c# DBF数据库导入导出实例 using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.Odbc; using System.Data.SqlClient; namespace DbfExample { public partial class Form1 : Form { System.Data.Odbc.OdbcConnection conn; public Form1() { InitializeComponent(); } //导出数据 private void btnOut_Click(object sender, EventArgs e) { string connect = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:\\; "; OdbcConnection myconn = new OdbcConnection(connect); string sqlt ="CREATE TABLE aa.DBF (cc int(10))"; myconn.Open(); OdbcCommand olec = new OdbcCommand(sqlt, myconn); try { int i = olec.ExecuteNonQuery(); MessageBox.Show("'" + i + "'success"); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { olec.Dispose(); myconn.Close(); } //string ole_connstring = @"Provider=microsoft.jet.oledb.5.0;Data Source=D:\;"; //System.Data.OleDb.OleDbConnection ole_conn = new System.Data.OleDb.OleDbConnection(ole_connstring); //try //{ // ole_conn.Open(); // System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand // ("Create Table TestTable (Field1 int, Field2 char(10),Field float(10,2))", // ole_conn); // System.Data.OleDb.OleDbCommand cmd2 = new System.Data.OleDb.OleDbCommand // ("Insert Into TestTable values (1,'Hello3',520.20)", ole_conn); // System.Data.OleDb.OleDbCommand cmd3 = new System.Data.OleDb.OleDbCommand // ("Insert Into TestTable values (2,'Hello4',18076.60)", ole_conn); // cmd1.ExecuteNonQuery(); // cmd2.ExecuteNonQuery(); // cmd3.ExecuteNonQuery(); //} //catch (Exception ex) //{ // MessageBox.Show(ex.Message); //} //finally //{ // ole_conn.Close(); //} } //导入数据 private void btnIn_Click(object sender, EventArgs e) { } private void Form1_Load(object sender, EventArgs e) { Bind(); } private void Bind() { try { conn = new System.Data.Odbc.OdbcConnection(); string table = @"C:\测试例子\Dbf\prepur.dbf"; string connStr = @"Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + table + ";Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO"; conn.ConnectionString = connStr; conn.Open(); string sql = @"select * from " + table; OdbcDataAdapter da = new OdbcDataAdapter(sql, conn); DataTable dt = new DataTable(); da.Fill(dt); this.dataGridView1.DataSource = dt.DefaultView; //MessageBox.Show(dt.Rows[0][0].ToString()); } catch { } finally { conn.Close(); } } private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) { try { conn = new System.Data.Odbc.OdbcConnection(); string table = @"C:\测试例子\Dbf\table1.dbf"; string connStr = @"Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + table + ";Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO"; conn.ConnectionString = connStr; conn.Open(); string id = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString(); string sql = @"select * from " + table + " where id='" + id + "'"; OdbcDataAdapter da = new OdbcDataAdapter(sql, conn); DataTable dt = new DataTable(); da.Fill(dt); txtId.Text = id; txtName.Text = dt.Rows[0]["name"].ToString(); txtAddress.Text = dt.Rows[0]["address"].ToString(); } catch { } finally { conn.Close(); } } private void Add() { conn = new System.Data.Odbc.OdbcConnection(); string table = @"C:\temp\Dbf\table1.dbf"; string connStr = @"Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + table + ";Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO"; conn.ConnectionString = connStr; conn.Open(); OdbcCommand cmd = new OdbcCommand(); cmd.Connection = conn; string sql = "insert into " + table + " values('" + txtId.Text + "','" + txtName.Text + "','" + txtAddress.Text + "')"; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); Bind(); } private void btnTOSQL_Click(object sender, EventArgs e) { try { string sql = "Insert Into dbftosql select * From openrowset('MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=C:\\temp\\Dbf','select * from table1.dbf')"; SqlConnection con = new SqlConnection("server=.;database=labelprint;uid=sa;pwd=sa"); con.Open(); SqlCommand cmd = new SqlCommand(sql, con); cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } } }
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值