SQLServer数据库读取数据在Excel中显示

.在WinForm界面和SQLServer数据库进行连接读取使用using System.Data.SqlClient;命名空间下的SqlDataAdapter,SqlCommand,SqlReader等，但是当我们要向Excel中读取时需要使用ADODB命名空间下的方法得到DataResult直接复制将减少大量的循环写入，从而省去大量的时间，注意Connction关闭的时间，如果提前关闭则第二个方法将不能读取：
private ADODB.Connection conn = null;
private ADODB.Recordset rs = null;
public Recordset GetRecordSet()
{
String strConn = @"PROVIDER=SQLOLEDB;SERVER=localhost;DATABASE=ZHANG_DL";
conn = new ADODB.Connection();
conn.ConnectionString = strConn;

try
{
conn.Open(strConn, "sa", "", -1);//这个方法的各个参数自己设定，根据自己的设置

}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
finally
{

Console.WriteLine(conn.State);
}
rs = new ADODB.Recordset();
String sql = "SELECT * FROM T_EMPLOYEE";//从数据库的Employee表格的内容进行读取
rs.Open(sql, conn, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockBatchOptimistic, (int)ADODB.CommandTypeEnum.adCmdText);//查一下每个参数的意义

return rs;
}

public void ShowInExcel(ADODB.Recordset rs)
{

String path=@"C:\Users\Administrator\Documents\book2.xlsx";//这个路径名可以根据前面的openFileDialog的方法动态的设定读取
object missing = System.Reflection.Missing.Value;
System.Globalization.CultureInfo currentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet sheet = null;
Microsoft.Office.Interop.Excel.Range rg = null;

wb = app.Workbooks.Add(true);

try
{
sheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[1];
app.Application.DisplayAlerts = false;
app.Application.Visible = false;
sheet.Cells[1, 1] = "EMPLOYEE_ID";
sheet.Cells[1, 2] = "EMPLOYEE_NAME";
sheet.Cells[1, 3] = "EMPLOYEE_AGE";
sheet.Cells[1, 4] = "EMPLOYEE_PHONE_NUMBER";
sheet.Cells[1, 5] = "EMPLOYEE_AGE";

rg = sheet.get_Range(sheet.Cells[2, 1], sheet.Cells[10, 5]);
// rg.Select();
rg.CopyFromRecordset(rs, missing, missing);
sheet.Columns.EntireColumn.AutoFit();

wb.SaveAs(path,missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
wb.Close(false, missing, missing);
wb = null;
app.Quit();
app = null;

}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
finally
{
rs.Close();
}
}