[转帖]将SQL Server中的数据导入到Excel
[hide]SQL Server中的DTS也能将数据倒入Excel，但不如使用程序灵活,
//此处省略了若干必要的基本using名字空间，请自行加上。
using System.Data;
using System.Data.SqlClient;
//定义方法GetData(),返回一个数据表
private System.Data.DataTable GetData()
{
SqlConnection conn= new SqlConnection(@"Server=PXGD2;Initial Catalog=pingxiang;Uid=sa;Pwd=;");
SqlDataAdapter adapter= new SqlDataAdapter("select username 用户名,catalyst_port 占用端口,home_address 住宅地址,ip_address
ip地址,phone 电话,addtime 开通日期 from userinfo where catalyst_port=1 or catalyst_port='' order by ip_address desc",conn);
DataSet ds= new DataSet();
try
{
adapter.Fill(ds,"Customer");
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
return ds.Tables[0];
}
//按钮
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application excel= new Excel.Application();
int rowIndex=1;
int colIndex=0;
excel.Application.Workbooks.Add(true);
DataTable table=GetData();
//将所得到的表的列名,赋值给单元格
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1,colIndex]=col.ColumnName;
}
//同样方法处理数据
foreach(DataRow row in table.Rows)
{
rowIndex++;
colIndex=0;
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
}
}
//不可见,即后台处理
excel.Visible=true;
}
下面是来自第 21 楼的 [bo]eastsnake[/bo] 提供的例子:<!--StartFragment-->
using System;
using System.Drawing;
using System.Collections;
using
using System.Windows.Forms;
using System.Data;
using
using System.Reflection;
namespace MyExcel
{
/// <summary>
/// Form1 的摘要说明。
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btnNormal;
private System.Windows.Forms.Button btnAdvace;
/// <summary>
/// 必需的设计器变量。
/// </summary>
private components = null;
public Form1()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent();
//
// TOD 在 InitializeComponent 调用后添加任何构造函数代码
//
}
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.btnNormal = new System.Windows.Forms.Button();
this.btnAdvace = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// btnNormal
//
this.btnNormal.Location = new System.Drawing.Point(49, 55);
this.btnNormal.Name = "btnNormal";
this.btnNormal.TabIndex = 0;
this.btnNormal.Text = "普通报表";
this.btnNormal.Click += new System.EventHandler(this.btnNormal_Click);
//
// btnAdvace
//
this.btnAdvace.Location = new System.Drawing.Point(169, 55);
this.btnAdvace.Name = "btnAdvace";
this.btnAdvace.TabIndex = 1;
this.btnAdvace.Text = "高级报表";
this.btnAdvace.Click += new System.EventHandler(this.btnAdvace_Click);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(292, 133);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.btnAdvace,this.btnNormal});
this.Name = "Form1";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "Form1";
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private string [,] myData=
{
{"车牌号","类型","品 牌","型 号","颜 色","附加费证号","车架号"},
{"浙KA3676","危险品","货车","铁风SZG9220YY","白","1110708900","022836"},
{"浙KA4109","危险品","货车","解放CA4110P1K2","白","223132","010898"},
{"浙KA0001A","危险品","货车","南明LSY9190WS","白","1110205458","0474636"},
{"浙KA0493","上普货","货车","解放LSY9190WS","白","1110255971","0094327"},
{"浙KA1045","普货","货车","解放LSY9171WCD","蓝","1110391226","0516003"},
{"浙KA1313","普货","货车","解放9190WCD","蓝","1110315027","0538701"},
{"浙KA1322","普货","货车","解放LSY9190WS","蓝","24323332","0538716"},
{"浙KA1575","普货","货车","解放LSY9181WCD","蓝","1110314149","0113018"},
{"浙KA1925","普货","货车","解放LSY9220WCD","蓝","1110390626","00268729"},
{"浙KA2258","普货","货车","解放LSY9220WSP","蓝","111048152","00320"}
};
//普通报表，即单纯的文件导出功能
private void btnNormal_Click(object sender, System.EventArgs e)
{
//创建一个Excel文件
Excel.Application myExcel = new Excel.Application ( ) ;
myExcel.Application.Workbooks.Add ( true ) ;
//让Excel文件可见
myExcel.Visible=true;
//第一行为报表名称
myExcel.Cells[1,4]="普通报表";
//逐行写入数据，
for(int i=0;i<11;i++)
{
for(int j=0;j<7;j++)
{
//以单引号开头，表示该单元格为纯文本
myExcel.Cells[2+i,1+j]="'"+myData[i,j];
}
}
}
//高级报表，根据模板生成的报表
private void btnAdvace_Click(object sender, System.EventArgs e)
{
string filename="";
//将模板文件复制到一个新文件中
SaveFileDialog mySave=new SaveFileDialog();
mySave.Filter="Excel文件(*.XLS)|*.xls|所有文件(*.*)|*.*";
if(mySave.ShowDialog()!=DialogResult.OK)
{
return;
}
else
{
filename=mySave.FileName;
//将模板文件copy到新位置，建议实际开发时用相对路径，如
//Application.StartupPath.Trim()+"\\report\\normal.xls"
string filenameold=mySave.FileName;
FileInfo mode=new FileInfo("d:\\normal.xls");
try
{
mode.CopyTo(filename,true);
}
catch(Exception ee)
{
MessageBox.Show(ee.Message);
return;
}
}
//打开复制后的文件
object missing=Missing.Value;
Excel.Application myExcel=new Excel.Application ( );
//打开新文件
myExcel.Application.Workbooks.Open(filename,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing, missing,missing);
//将Excel显示出来
myExcel.Visible=true;
//逐行写入数据，数组中第一行我列标题，忽略
for(int i=1;i<11;i++)
{
for(int j=0;j<7;j++)
{
//以单引号开头，表示该单元格为纯文本
myExcel.Cells[4+i,1+j]="'"+myData[i,j];
}
}
//将列标题和实际内容选中
Excel.Workbook myBook=myExcel.Workbooks[1];
Excel.Worksheet mySheet=(Excel.Worksheet)myBook.Worksheets[1];
Excel.Range r=mySheet.get_Range(mySheet.Cells[3,1],mySheet.Cells[14,7]);
r.Select();
//=====通过执行宏来格表格加边框=======//
try
{
myExcel.Run("宏1",missing,missing,
missing,missing,missing,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing,missing);
}
catch
{
}
//保存修改
myBook.Save();
}
}//end of form
}
[/hide]
以前见到很多人问，不过在下不懂，刚才偶然找到一篇，转帖过来供大家参考。
最新加入了来自第 21 楼的 [bo]eastsnake[/bo] 提供的例子，感谢[bo]eastsnake[/bo] 提供。PS：请大家耐心看完回帖内容，[bo]eastsnake[/bo] 朋友的回帖也异常精彩！
[此贴子已经被作者于2005-5-12 10:41:09编辑过]
