vsC#数据库导入EXCLE

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using Microsoft.Office.Core ;

using Excel=Microsoft.Office.Interop.Excel;

namespace ExcelReport

{

public partial class FormExcelNormal : Form

{

public FormExcelNormal()

{

InitializeComponent();

}

PRivate void FormExcelNormal_Load(object sender, EventArgs e)

{

DataSet objDataSet = FormUtil.FormGridView("MEMBERINFO");

dgvMemberInfo.DataSource = objDataSet.Tables["MEMBERINFO"];

}

// Excel导出

private void btnExcelOut_Click(object sender, EventArgs e)

{

string strConnect = System.Configuration.ConfigurationSettings.AppSettings["connStr"];

SqlConnection objConnection = new SqlConnection(strConnect);

SqlCommand objCommand = new SqlCommand("SELECT MEMBERNAME, SEX, AREA, EMAIL FROM MEMBERINFO", objConnection);

DataSet objDataset = new DataSet();

SqlDataAdapter objAdapter = new SqlDataAdapter(objCommand);

// 数据填充

objConnection.Open();

objAdapter.Fill(objDataset, "Excel");

objConnection.Close();

// 创建Excel对象

Excel.application xlApp = new Excel.Application();

Excel.Workbook wb = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];

Excel.Range targetRange = xlApp.get_Range("A1");

// 设置标题

xlApp.Cells[1, 1] = "会员姓名";

xlApp.Cells[1, 2] = "性别";

xlApp.Cells[1, 3] = "籍贯";

xlApp.Cells[1, 4] = "电子邮件";

// 设置格式

int iMaxRow = objDataset.Tables["Excel"].Rows.Count;

int iMaxCol = objDataset.Tables["Excel"].Columns.Count;

ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iMaxCol]).Font.Name = "黑体";

ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iMaxCol]).Font.Bold = true;

ws.get_Range(ws.Cells[1, 1], ws.Cells[iMaxRow + 1, iMaxCol]).Borders.LineStyle = 1;

// 填充数据

for (int iRow = 0; iRow < iMaxRow; iRow++)

{

for (int iCol = 0; iCol < iMaxCol; iCol++)

{

xlApp.Cells[iRow + 2, iCol + 1] = objDataset.Tables["Excel"].Rows[iRow][iCol].ToString();

}

}

// 保存Excel

xlApp.Save("sheet1.xls");

// 打开Excel

xlApp.Visible = true;

}

}

}