|
??????????
.在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:UsersAdministratorDocumentsbook2.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,Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
??????????????????? Type.Missing,Type.Missing);
??????????????? wb.Close(false,missing);
??????????????? wb = null;
??????????????? app.Quit();
??????????????? app = null;
??????????? } ??????????? catch (Exception ee) ??????????? { ??????????????? throw new Exception(ee.Message); ??????????? } ??????????? finally ??????????? { ??????????????? rs.Close(); ??????????? } ??????? }????
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
