Excel Front End rau SQL tus neeg rau zaub mov

Cov neeg siv raug ua hauj lwm hauv Microsoft Excel . Vim li cas tsis muab koj cov neeg siv nrog ib lub cuab tam lawv twb paub thiab ntxiv rau nws kev twb kev txuas mus rau hauv koj lub chaw SQL neeg rau zaub mov . Qhov zoo ntawm qhov kev mus kom ze yog lawv Excel spreadsheet yog ib txwm mus txog nrog cov ntaub ntawv tam sim no los ntawm cov ntaub ntawv rov qab kawg. Nws yog raug rau cov neeg siv los ua cov ntaub ntawv hauv Excel, tiam sis feem ntau nws yog ib qho snapshot ntawm cov ntaub ntawv ntawm tus taw tes rau hauv lub sijhawm. Tsab ntawv xov xwm no yuav qhia koj yooj yim npaum li cas yog tsim kho ib qho Excel spreadsheet nrog kev txuas mus rau SQL uas koj tuaj yeem muab rau koj cov neeg siv.

Hauv qhov ua piv txwv, peb yuav siv cov Adventure Works qauv database uas Microsoft xa nrog SQL neeg rau zaub mov 2008.

Teeb meem: Nruab nrab

Lub Sij Hawm Xav Tau: 10 feeb

Ntawm no & # 39; s li cas

1. Koj yuav tsum tau ob peb daim ntawm cov ntaub ntawv mus teeb rau Excel rau SQL neeg rau zaub mov kev twb kev txuas.
   1. • SQL lub npe neeg rau zaub mov - Hauv peb tus piv txwv, SQL neeg rau zaub mov yog MTP \ SQLEXPRESS.
2. Database Npe - Peb tus qauv, peb siv cov AdventureWorks database.
3. Rooj ntawv lossis saib - Peb tabtom yuav tom qab saib Sales.vIndividualCustomer.
4. Qhib Excel thiab tsim ib phau ntawv tshiab.
5. Nyem ntawm cov ntaub ntawv tab. Nrhiav cov "Tau Txais Cov Ntaub Ntawv" xaiv thiab nias rau ntawm "Los ntawm Lwm Qhov Chaw" thiab xaiv "Ntawm SQL Server". Qhov no yuav qhib rau "Kev Sib Txuas Lus Wizard".
6. Sau nyob rau hauv lub npe Server . Hauv qhov ua piv txwv no, lub npe hu ua "MTP \ SQLEXPRESS". Teem kom tus ID nkag mus rau "Siv Qhov Cuab Authentication". Lwm qhov kev xaiv yuav raug siv yog tias koj tus neeg ua haujlwm hauv database muab ib tus us username thiab password rau koj tus neeg siv. Nyem tom ntej. Qhov no coj tau cov "Data Connection Wizard".
7. Xaiv qhov database ("AdventureWorks" hauv peb qhov piv txwv) los ntawm "Xaiv cov ntaub ntawv khaws tseg uas muaj cov ntaub ntawv koj xav tau" kos rau hauv lub thawv. Nco ntsoov tias "Txuas mus rau ib lub rooj" yog kuaj xyuas. Nrhiav kev pom ("Sales.vIndividualCustomer" hauv peb qhov qauv) ntawm daim ntawv thiab xaiv nws. Nyem Qhov kawg uas coj mus rau Cov Ntawv Qhia Cov Ntawv Qhia Ntshuam.

1. Khij lub rooj kuaj lub thawv thiab xaiv qhov chaw koj xav muab cov ntaub ntawv (daim ntawv los yog daim ntawv tshiab). Nias OK uas tsim ib daim ntawv teev Excel thiab imports tag nrho cov lus rau hauv koj daim ntawv khom nqi.
2. Tseg koj daim ntawv khom nqi thiab xa mus rau tus neeg siv. Qhov zoo tshaj plaws txog cov txheej txheem no yog tias koj cov neeg siv tau cov ntaub ntawv tam sim no thaum twg lawv xav tau. Thaum cov ntaub ntawv no tseg rau hauv daim ntawv khom nqi, nws muaj kev txuas mus rau SQL Database. Txhua lub sij hawm koj xav khawm tsab ntawv khom nqi, nias qhov chaw nyob hauv lub rooj thiab nyem rau ntawm "Table" thiab ces "Refresh". Tus ntawd yog nws.

Lub tswv yim

1. Nws yog qhov tseem ceeb heev uas koj yuav tsum paub tseeb tias cov neeg siv kev teeb tsa nyob rau hauv SQL neeg rau zaub mov. Qhov no yog qhov uas ua rau muaj teeb meem nyob rau feem ntau siv cov txheej txheem no.
2. Khij cov ntaub ntawv khaws tseg hauv lub rooj lossis saib kom koj txuas rau. Yog hais tias lub rooj muaj ib lab cov ntaub ntawv, koj xav sau qhov no. Qhov kawg koj xav ua yog hang ntawm SQL neeg rau zaub mov.
3. Nyob ntawm kev sib txuas hauv cov khoom sib txuas lus, muaj ib qho kev xaiv hu ua "Hloov cov ntaub ntawv thaum qhib cov ntaub ntawv". Xav saib xyuas qhov kev xaiv no. Thaum xaiv qhov no, tus neeg siv yuav ib txwm muaj txheej txheej tshiab thaum qhib qhov Excel spreadsheet.
4. Xav txog kev siv Pivot Ntxhuav rau summerize cov ntaub ntawv.

Yam Koj Xav Tau

• Microsoft Excel 2007
• SQL neeg rau zaub mov cov ntaub ntawv nrog rau cov neeg rau zaub mov, database thiab lub rooj los yog saib