sqlserver数据导出问题，请高手指点！十万火急！

aha44 2004-08-23 11:41:45

c＃中如何将sqlserver中已有的数据导出到excel中形成一个excel文件？最好能给详细点的代码，不胜感激！

...全文

127 点赞收藏 11

写回复

11 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

aha44 2004-08-24

谢谢海宁！！！

回复赞

zairwolfc 2004-08-23

唉。

_____________________________________________________________________________

该问题已经结贴，得分记录： zairwolfc (300)

回复赞

Eddie005 2004-08-23

/// <summary>
/// 将DataTable中的数据导出到指定的Excel文件中
/// </summary>
/// <param name="page">Web页面对象</param>
/// <param name="tab">包含被导出数据的DataTable对象</param>
/// <param name="FileName">Excel文件的名称</param>
public static void Export(System.Web.UI.Page page,System.Data.DataTable tab,string FileName)
{
System.Web.HttpResponse httpResponse = page.Response;
System.Web.UI.WebControls.DataGrid dataGrid=new System.Web.UI.WebControls.DataGrid();
dataGrid.DataSource=tab.DefaultView;
dataGrid.AllowPaging = false;
dataGrid.HeaderStyle.BackColor = System.Drawing.Color.Green;
dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
dataGrid.HeaderStyle.Font.Bold = true;
dataGrid.DataBind();
httpResponse.AppendHeader("Content-Disposition","attachment;filename="+HttpUtility.UrlEncode(FileName,System.Text.Encoding.UTF8)); //filename="*.xls";
httpResponse.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
httpResponse.ContentType ="application/ms-excel";
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
dataGrid.RenderControl(hw);

string filePath = page.Server.MapPath("..")+"\\Files\\" +FileName;
System.IO.StreamWriter sw = System.IO.File.CreateText(filePath);
sw.Write(tw.ToString());
sw.Close();

DownFile(httpResponse,FileName,filePath);

httpResponse.End(); }

-------------------------

private static bool DownFile(System.Web.HttpResponse Response,string fileName,string fullPath)
{
try
{
Response.ContentType = "application/octet-stream";

Response.AppendHeader("Content-Disposition","attachment;filename=" +
HttpUtility.UrlEncode(fileName,System.Text.Encoding.UTF8) + ";charset=GB2312");
System.IO.FileStream fs= System.IO.File.OpenRead(fullPath);
long fLen=fs.Length;
int size=102400;//每100K同时下载数据
byte[] readData = new byte[size];//指定缓冲区的大小
if(size>fLen)size=Convert.ToInt32(fLen);
long fPos=0;
bool isEnd=false;
while (!isEnd)
{
if((fPos+size)>fLen)
{
size=Convert.ToInt32(fLen-fPos);
readData = new byte[size];
isEnd=true;
}
fs.Read(readData, 0, size);//读入一个压缩块
Response.BinaryWrite(readData);
fPos+=size;
}
fs.Close();
System.IO.File.Delete(fullPath);
return true;
}
catch
{
return false;
}
}

回复赞

hanbinghai 2004-08-23

给你个实现打印的代码，你参考参考

回复赞

hanbinghai 2004-08-23

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Reflection;
using System.Data.SqlClient;

namespace test
{
/// <summary>
/// Form1 的摘要说明。
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.Container components = null;

public Form1()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent();

//
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
}

/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}

#region Windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(72, 88);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(88, 23);
this.button1.TabIndex = 0;
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(368, 277);
this.Controls.Add(this.button1);
this.Name = "Form1";
this.Text = "Form1";
this.ResumeLayout(false);

}
#endregion

/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
public Excel.Application excel = null;
private void button1_Click(object sender, System.EventArgs e)
{
//连接字符串
string strSQL = "Server=.;Integrated Security = SSPI; Database = electric;";
SqlConnection conn = new SqlConnection(strSQL);
conn.Open();
//sql语句，需要改动
strSQL = "SELECT ID AS 自动编号, allName AS 文件名, parentID AS 父节点自动编号, flag AS 标志位 FROM allFile WHERE (ID <= 10)";
SqlCommand cmd = new SqlCommand (strSQL,conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL,conn);
try
{

DataSet DS_hai = new DataSet();
dataAdapter.Fill(DS_hai,"allfile");
this.printExcel(DS_hai,"我的报表");
}
catch(Exception err)
{
MessageBox.Show(err.ToString());
}
finally
{
dataAdapter.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}

}

/// <summary>
/// 打印报表
/// </summary>
/// <param name="DS_para">要打印的DataSet</param>
/// <param name="printName">报表名称</param>
private void printExcel(DataSet DS_para,string printName)
{
try
{
//创建一个Excel文件
Excel.Application myExcel = new Excel.Application ( ) ;
myExcel.Application.Workbooks.Add ( true ) ;
//让Excel文件可见
myExcel.Visible=true;
//第一行为报表名称
myExcel.Cells[1,4]=printName;

int iRowsCount = DS_para.Tables[0].Rows.Count;
int iColumsCount = DS_para.Tables[0].Columns.Count;
//写入列名称
for(int j=0;j<iColumsCount;j++)
{
//以单引号开头，表示该单元格为纯文本
myExcel.Cells[2,1+j]="'"+DS_para.Tables[0].Columns[j].ToString();
}
for(int i=0;i<iRowsCount;i++)
{
for(int j=0;j<iColumsCount;j++)
{
//以单引号开头，表示该单元格为纯文本
myExcel.Cells[3+i,1+j]="'"+DS_para.Tables[0].Rows[i][j].ToString();
}
}
object hai = null;

myExcel.ActiveWorkbook.PrintPreview(hai);
//myExcel.ActiveWorkbook.PrintOut(hai,hai,hai,hai,hai,hai,hai,hai);

}
catch(Exception err)
{
MessageBox.Show(err.ToString());
}

}
}
}

回复赞

robin0925 2004-08-23

用流写吧!最简单!

回复赞

hanbinghai 2004-08-23

发错了，不好意思

回复赞

hanbinghai 2004-08-23

参见在C#中运用SQLDMO备份和恢复Microsoft SQL Server数据库
http://dev.csdn.net/develop/article/28/28564.shtm
当不使用要恢复的数据库时以上方法可行，但当你使用了数据库时就必须杀死该进程
代码如下：

/// <summary>
/// 还原数据库函数
/// </summary>
/// <param name="strDbName">数据库名</param>
/// <param name="strFileName">数据库备份文件的完整路径名</param>
/// <returns></returns>
public bool RestoreDB(string strDbName,string strFileName)
{
//PBar = pgbMain ;
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass() ;
try
{
//服务器名，数据库用户名，数据库用户名密码
svr.Connect("localhost","sa","hai") ;

SQLDMO.QueryResults qr = svr.EnumProcesses(-1) ;
int iColPIDNum = -1 ;
int iColDbName = -1 ;
for(int i=1;i<=qr.Columns;i++)
{
string strName = qr.get_ColumnName(i) ;
if (strName.ToUpper().Trim() == "SPID")
{
iColPIDNum = i ;
}
else if (strName.ToUpper().Trim() == "DBNAME")
{
iColDbName = i ;
}
if (iColPIDNum != -1 && iColDbName != -1)
break ;
}
//杀死使用strDbName数据库的进程
for(int i=1;i<=qr.Rows;i++)
{
int lPID = qr.GetColumnLong(i,iColPIDNum) ;
string strDBName = qr.GetColumnString(i,iColDbName) ;
if (strDBName.ToUpper() == strDbName.ToUpper())
{
svr.KillProcess(lPID) ;
}
}

SQLDMO.Restore res = new SQLDMO.RestoreClass() ;
res.Action = 0 ;
res.Files = strFileName ;

res.Database = strDbName ;
res.ReplaceDatabase = true ;
res.SQLRestore(svr) ;
return true ;
}
catch
{
return false;
}
finally
{
svr.DisConnect() ;
}
}

回复赞

Eddie005 2004-08-23

直接把它当作文本文件来写也可以，
<table>
<tr>
<td>aaa</td><td>bbb</td>
........................
........................

最后保存为*.xls即可，你会惊喜地发现这个文件也是个可用的Excel文件

回复赞

Eddie005 2004-08-23

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+
"Data Source=" + filePath + ";" +
"Extended Properties=\"Excel 8.0;\"");

OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]",conn);
OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
myData.Fill(rs);
conn.Close();

用这种方式可以把指定excel文件作为数据库读出来，剩下的事情应该就不难了

回复赞

aha44 2004-08-23

不知道哪位大哥能给我windowsform的代码，等着急用呀！必要时我还可以加分的！

回复赞