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

来源：互联网发布：office mac下载编辑：程序博客网时间：2024/05/02 18:05

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