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

来源：互联网发布：三维图制作软件编辑：程序博客网时间：2024/05/16 06:58

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