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 ExcelOut
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
SqlConnection conn;
DataSet ds;
private void button1_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();//创建Excel
Workbook wb = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//创建工作溥
Worksheet ws=(Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];//创建工作页
int iMaxRow = ds.Tables["Excel1"].Rows.Count;
int iMaxCol = ds.Tables["Excel1"].Columns.Count;
//设置格式
ws.get_Range(ws.Cells[1,1],ws.Cells[1, iMaxRow]).Font.Name = "黑体";
ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iMaxCol]).Font.Bold = true;
//设置标题
xlApp.Cells[1, 1] = "编号";
xlApp.Cells[1, 2] = "星座";
//填充数据
for (int iRow = 0; iRow < iMaxRow; iRow++)
{
for (int iCol = 0; iCol < iMaxCol; iCol++)
{
xlApp.Cells[iRow + 2, iCol + 1] = this.ds.Tables["Excel1"].Rows[iRow][iCol].ToString();
}
}
xlApp.Save("Test.xls");
xlApp.Visible = true;
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
this.conn.Close();
this.conn.Dispose();
}
}
private void Form1_Load(object sender, EventArgs e)
{
this.conn = new SqlConnection("server=.;Initial Catalog=MyQQ;Integrated Security=sspi;uid=sa;pwd=123456");
conn.Open();
SqlCommand comm = new SqlCommand("select * from Star",conn);
SqlDataAdapter ad = new SqlDataAdapter(comm);
ds = new DataSet();
ad.Fill(ds,"Excel1");
dataGridView1.DataSource =ds.Tables["Excel1"];
}
}
}