C#中dategridview数据导出为excel文件
先从数据库中获取数据,绑定在datagridview中,再从dategridview中导出为excel文件
1、新建窗体,把控件datagridview和按钮设置好,如图
2、设置datagridview绑定数据表的字段
1)点击datagriview右上角,弹出编辑列,添加列
2)点击添加列,新建数据,跟数据库中数据表的列名一样
3、获取数据库中数据表的方法
SqlConnection conn = null;
/// <summary>
/// 查询事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSelect_Click(object sender, EventArgs e)
{
conn = DBHelp.GetConnection(); //获取数据库连接,并开启连接
string sql = "select * from UseTab"; //查询数据表的 sql语句
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sql,conn);
sda.Fill(ds);
DataTable dt = ds.Tables[0]; //获取数据表的数据
this.dgvUseName.DataSource = dt; //把dattable绑定datagridview
DBHelp.CloseConnection(conn); //关闭连接
//选择整行显示数据
this.dgvUseName.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
//选择是否只读
this.dgvUseName.ReadOnly = true;
}
4、导出为excel文件的方法
private void btnExport_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出Excel文件到";
DateTime now = DateTime.Now;
saveFileDialog.FileName = now.Year.ToString().PadLeft(2)
+ now.Month.ToString().PadLeft(2, '0')
+ now.Day.ToString().PadLeft(2, '0') + "-"
+ now.Hour.ToString().PadLeft(2, '0')
+ now.Minute.ToString().PadLeft(2, '0')
+ now.Second.ToString().PadLeft(2, '0');
saveFileDialog.ShowDialog();
Stream myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
string str = "";
try
{
//写标题
for (int i = 0; i < this.dgvUseName.ColumnCount; i++)
{
if (i > 0)
{
str += "\t";
}
str += this.dgvUseName.Columns[i].HeaderText;
}
sw.WriteLine(str);
//写内容
for (int j = 0; j < this.dgvUseName.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < this.dgvUseName.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += this.dgvUseName.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception ex)
{
//MessageBox.Show(ex.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
5、数据库辅助类
class DBHelp
{
//连接字符串
static String str = "server=.;database=TestDB;uid=sa;pwd=sa";
static SqlConnection conn = null;
static SqlCommand cmd = null;
static int result=0;
/// <summary>
/// 获取可用连接
/// </summary>
/// <returns>数据库连接对象</returns>
public static SqlConnection GetConnection() {
conn = new SqlConnection(str);
conn.Open();
return conn;
}
/// <summary>
/// 关闭连接对象
/// </summary>
/// <param name="conn">连接对象</param>
public static void CloseConnection(SqlConnection conn) {
if (conn != null && conn.State == System.Data.ConnectionState.Open) {
conn.Close();
}
}
}
6、最后导出的效果图
保存时,弹出的窗体
打开excel文件查看数据
参考的导出方法来自这个地址:http://www.cnblogs.com/hfzsjz/archive/2013/05/07/3064231.html