将DataGridView中的数据导出到EXCEL表中
//首先要添加引用 COM组件 Microsoft Excel 12.0 Object Library
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.SqlClient;
using Microsoft.Office.Interop.Excel;
namespace Test
{
public partial class frmOpenExcel : Form
{
public frmOpenExcel()
{
InitializeComponent();
}
SqlConnection con;
SqlCommand com;
string sql;
private void frmOpenExcel_Load(object sender, EventArgs e)
{
DBLink link = new DBLink(".", "DBTest", "true");
con = link.ConOpen();
sql = "select u_id as 编号,u_petname as 昵称,u_password as 密码,u_realname as 姓名,";
sql += "p.p_papername as 证件名称,u_papernum as 证件号码,u_tel as 联系电话,u_email as 电子邮箱,";
sql += "u_individuality as 个性说明 from userinfo as u,paper as p where p.p_id=u_paperid";
com = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
link.ConClose(con);
}
private void button1_Click(object sender, EventArgs e)
{
//导出没有标题行的
ToExcel(dataGridView1);
}
private void button2_Click(object sender, EventArgs e)
{
//导出有标题行的
ExportDataGridTableToExcel(dataGridView1, this.Text);
}
/// DataGridView中绑定的是一个DataTable
/// </summary>
/// <param name="grid">DataGridView</param>
/// <param name="ExcelTitle">Excel标题</param>
public static void ToExcel(DataGridView grid)
{
System.Data.DataTable mytable = (System.Data.DataTable)grid.DataSource;
try
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
int rowIndex, colIndex;
rowIndex = 1;
colIndex = 0;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
if (grid.Rows.Count > 0)
{
foreach (DataGridViewColumn colu in grid.Columns)
{
if (colu.Visible)
{
colIndex = colIndex + 1;
xlApp.Cells[1, colIndex] = colu.HeaderText;
}
}
for (int row = 0; row < mytable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < grid.Columns.Count; col++)
{
if (grid.Columns[col].Visible)
{
colIndex = colIndex + 1;
if (grid.Columns[col].CellType.Name == "DataGridViewComboBoxCell")
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].FormattedValue;
else
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].Value;
}
}
}
}
else
{
foreach (DataColumn Col in mytable.Columns)
{
colIndex = colIndex + 1;
xlApp.Cells[1, colIndex] = Col.ColumnName;
}
for (int row = 0; row < mytable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < mytable.Columns.Count; col++)
{
colIndex = colIndex + 1;
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].Value;
}
}
}
xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colIndex]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Visible = true;
}
catch(Exception ex)
{
MessageBox.Show("错误消息："+ex.Message,"错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
/// DataGridView中绑定的是一个DataTable
/// </summary>
/// <param name="grid">DataGridView</param>
/// <param name="ExcelTitle">Excel标题</param>
public static void ExportDataGridTableToExcel(DataGridView grid, string ExcelTitle)
{
System.Data.DataTable myTable = (System.Data.DataTable)grid.DataSource;
try
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
int rowIndex;
int colIndex;
rowIndex = 2;
colIndex = 0;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
if (grid.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, grid.Columns.Count]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ExcelTitle;
xlApp.ActiveCell.Font.Size = 18;
xlApp.ActiveCell.Font.Bold = true;
foreach (DataGridViewColumn colu in grid.Columns)
{
if (colu.Visible)
{
colIndex = colIndex + 1;
xlApp.Cells[2, colIndex] = colu.HeaderText;
}
}
//得到的表所有行,赋值给单元格
for (int row = 0; row < myTable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < grid.Columns.Count; col++)
{
if (grid.Columns[col].Visible)
{
colIndex = colIndex + 1;
if (grid.Columns[col].CellType.Name == "DataGridViewComboBoxCell")
{
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].FormattedValue;
// grid.Rows[row].Cells[0]
}
else
{
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].Value;
}
}
}
}
}
else
{
Microsoft.Office.Interop.Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, myTable.Columns.Count]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ExcelTitle;
xlApp.ActiveCell.Font.Size = 18;
xlApp.ActiveCell.Font.Bold = true;
//将表中的栏位名称填到Excel的第一行
foreach (DataColumn Col in myTable.Columns)
{
colIndex = colIndex + 1;
xlApp.Cells[2, colIndex] = Col.ColumnName;
}
//得到的表所有行,赋值给单元格
for (int row = 0; row < myTable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < myTable.Columns.Count; col++)
{
colIndex = colIndex + 1;
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].Value;
}
}
}
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show("错误消息：" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
posted on 2009-03-24 22:44 VictorShan 阅读(1332) 评论(0) 编辑 收藏 举报
