vs2010将dategridview导出成excel或word的方法

using System;
using System.Collections.Generic;
using ponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
namespace supermarket
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
string sql = "select * from tb_Stock";
DataSet ds = BaseClass.SqlHelper.ExecutDataSet(sql);
dataGridView1.DataSource = ds.Tables[0];
}
private void button1_Click(object sender, EventArgs e)
{
string path = Directory.GetCurrentDirectory() + "\\";
BaseClass.COut method = new BaseClass.COut();
method.ExportExcel(this.dataGridView1, true);
MessageBox.Show("导出成功");
Microsoft.Office.Interop.Excel.Application excel =new
Microsoft.Office.Interop.Excel.Application(); //引用Excel对象
Microsoft.Office.Interop.Excel.Workbook book = excel.Application.Workbooks.Add(path + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls");
//引用Excel工作簿
excel.Visible = true; //使Excel可视
}
private void button2_Click(object sender, EventArgs e)
{
string path = Directory.GetCurrentDirectory() + "\\";
BaseClass.COut method = new BaseClass.COut();
method.ExportDataGridview(this.dataGridView1, true);
MessageBox.Show("导出成功");
}
}
}
调用的类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;
namespace supermarket.BaseClass
{
class COut
{
///<summary>
/// excel导出
///</summary>
///<param name="dgv"></param>
///<param name="isShowExcle"></param>
///<returns></returns>
public bool ExportExcel(DataGridView dgv, bool isShowExcle)
{
//文件保存路径为当前文件夹的debug里
string path = Directory.GetCurrentDirectory() + "\\";
if (dgv.Rows.Count == 0)
return false;
Microsoft.Office.Interop.Excel._Application xlApp = new
Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return false; //result = 2;
}
Microsoft.Office.Interop.Excel.Workbook xlbook = xlApp.Workbooks.Add(true);
for (int i = 0; i < dgv.ColumnCount; i++)
{
xlApp.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))
{
xlApp.Cells[i + 2, j + 1] = "" + dgv[j, i].Value.ToString();
}
else
{
xlApp.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
}
}
}
xlbook.SaveCopyAs(path+ DateTime.Now.ToString("yyyyMMddHHmm") + ".xls");
return true;
}
///<summary>
/// word导出
///</summary>
///<param name="dgv"></param>
///<param name="isshowWord"></param>
///<returns></returns>
public bool ExportDataGridview(DataGridView dgv, bool isshowWord)
{
string path = Directory.GetCurrentDirectory() + "\\";
Microsoft.Office.Interop.Word.Document mydoc = new
Microsoft.Office.Interop.Word.Document();//实例化word文档对象
Microsoft.Office.Interop.Word.Table mytable;//声明word表格
Microsoft.Office.Interop.Word.Selection mysel;//声明word选
object myobj;
if (dgv.Rows.Count == 0)
return false;
//生成word对象
Microsoft.Office.Interop.Word.Application word = new
Microsoft.Office.Interop.Word.Application();
myobj = System.Reflection.Missing.Value;
mydoc = word.Documents.Add(ref myobj, ref myobj, ref myobj, ref myobj);
word.Visible = true;
mydoc.Select();
mysel = word.Selection;
//将word生成word表格文件
mytable = mydoc.Tables.Add(mysel.Range, dgv.RowCount, dgv.ColumnCount, ref myobj, ref myobj);
//设置列宽
mytable.Columns.SetWidth(40, Microsoft.Office.Interop.Word.WdRulerStyle.wdAdjustNone);
//输出标题数据
for (int i = 0; i < dgv.ColumnCount; i++)
{
mytable.Cell(1, i + 1).Range.InsertAfter(dgv.Columns[i].HeaderText);
}
//输出控件中的记录
try
{
for (int i = 0; i < dgv.RowCount; i++)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
mytable.Cell(i + 2, j + 1).Range.InsertAfter(dgv[j, i].Value.ToString());
}
}
}
catch (Exception)
{ }
mydoc.SaveAs(path + DateTime.Now.ToString("yyyyMMddHHmm") + ".doc");
return true;
}
}
}
SqlHelper类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace supermarket.BaseClass
{
class SqlHelper
{
private static string CONN_STR = @"Data
Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\db_supermarket.mdf;Integrated Security=SSPI;User Instance=True";//这里是打开vs2010自带的数据库的
///<summary>
/// insert delete update
///</summary>
///<param name="sql"></param>
///<returns></returns>
public static int ExecutNonQuery(string sql)
{
SqlConnection conn = new SqlConnection(CONN_STR);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
int ret = comm.ExecuteNonQuery();
conn.Close();
return ret;
}
/// select 取第一行第一列
///<returns></returns>
public static object ExecutScalar(string selectsql)
{
SqlConnection conn = new SqlConnection(CONN_STR);
conn.Open();
SqlCommand comm = new SqlCommand(selectsql,conn);
object obj=comm.ExecuteScalar();
conn.Close();
return obj;
}
/// select 取所有行所有列
public static DataSet ExecutDataSet(string selectsql)
{
SqlConnection conn = new SqlConnection(CONN_STR);
conn.Open();
DataSet ds = new DataSet();//内存数据库，用来保存从数据库服务器上
SqlDataAdapter da = new SqlDataAdapter(selectsql, conn);
da.Fill(ds);
conn.Close();
return ds;
}
}
}。