使用环境：windows xp,windows2003，asp.net 2.0 ,sql server2000,office2003
使用背景：将应用系统中的数据表数据导出到Excel文件中打印及其它处理
步骤：
1）新建项目，选择windows应用程序
2）添加dataGridView控件，button按钮控件
3）添加按钮事件代码，原代码如下，根据自己的情况适当修改
4）在项目中添加引用，选择excel
5）ExportDataGridview（）方法代码不用修改，直接拷贝过来使用
6）客户端若没安装dotnet2.0，则需下载dotnetfx.exe安装包
dotnetfx.exe下载地址：
http://www.microsoft.com/downloads/Browse.aspx?displaylang=zh-cn&categoryid=10
源代码下载：aspnet2_export_table_to_excel.txt
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.Sql;
using System.Data.SqlClient;
namespace WindowsApplication3
{
public partial class Form1 : Form
{
SqlDataAdapter myda;
DataSet myds;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
String cmdtext="select * from yourtable ";
String connstr ="Data Source=192.168.1.3;Initial Catalog=yourdb;User ID=sa;Password=yourpwd";
SqlConnection sqlcon = new SqlConnection();
sqlcon.ConnectionString = connstr;
sqlcon.Open();
myda = new SqlDataAdapter(cmdtext, sqlcon);
myds = new DataSet();
myda.Fill(myds);
sqlcon.Close();
dataGridView1.DataSource = myds.Tables[0];
}
private void button1_Click(object sender, EventArgs e)
{
ExportDataGridview(dataGridView1, true);
}
public bool ExportDataGridview(DataGridView dgv, bool isShowExcle)
{
if (dgv.Rows.Count == 0)
return false;
//建立Excel对象
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcle;
//生成字段名称
for (int i = 0; i < dgv.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dgv.RowCount - 1; i++)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "" + dgv[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
}
}
}
return true;
}
}
}
注意：连接参数或应用程序的配置文件中使用ip地址，尽量避免机器名方式，否则容易出现类似这样的错误，因为系统默认去连接 SQL Server 2005数据库。
Could not open a connection to SQL Server.txt
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Could not open a connection to SQL Server