.在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();
}
}