مطمئناً اکسل^(Excel) برای صفحات گسترده استفاده می شود، اما آیا می دانستید که می توانید اکسل^(Excel) را به منابع داده خارجی متصل کنید؟ در این مقاله قصد داریم نحوه اتصال یک صفحه گسترده اکسل^(Excel) به جدول پایگاه داده MySQL و استفاده از داده های جدول پایگاه داده برای پر کردن صفحه گسترده را مورد بحث قرار دهیم. برای آماده شدن برای این اتصال باید چند کار انجام دهید.

آماده سازی^{(Preparation)}

ابتدا، باید جدیدترین درایور Open Database Connectivity ( ODBC ) را برای MySQL دانلود کنید. درایور ODBC^(ODBC) فعلی برای MySQL را می توان در این آدرس قرار داد

https://dev.mysql.com/downloads/connector/odbc/

مطمئن^(Make) شوید که پس از دانلود فایل، هش md5 ​​فایل را با آنچه در صفحه دانلود فهرست شده است بررسی کنید.

در مرحله بعد، باید درایوری را که به تازگی دانلود کرده اید نصب کنید.  برای شروع فرآیند نصب، روی فایل دوبار کلیک کنید. ^(Double)پس از تکمیل فرآیند نصب، باید یک نام منبع پایگاه داده^{(Database Source Name)} ( DSN ) ایجاد کنید تا با اکسل^(Excel) از آن استفاده کنید .

ایجاد DSN^{(Creating the DSN)}

DSN شامل تمام اطلاعات اتصال لازم برای استفاده از جدول پایگاه داده MySQL خواهد بود. در یک سیستم ویندوز^(Windows) ، باید روی Start ، سپس Control Panel ، سپس Administrative Tools و سپس Data Sources (ODBC) کلیک کنید. شما باید اطلاعات زیر را ببینید:

^(Notice)به تب های موجود در تصویر بالا توجه کنید. یک User DSN فقط برای کاربری که آن را ایجاد کرده است در دسترس است. سیستم DSN^{(System DSN)} برای هر کسی که بتواند وارد دستگاه شود در دسترس است. فایل DSN^{(File DSN)} یک فایل .DSN است که می تواند به سیستم های دیگری که سیستم عامل و درایورهای مشابهی را نصب کرده اند، منتقل شده و از آن استفاده شود.

برای ادامه ایجاد DSN ، روی دکمه افزودن^(Add) در گوشه بالا سمت راست کلیک کنید.

احتمالاً برای دیدن درایور MySQL ODBC 5.x^{(MySQL ODBC 5.x Driver)} باید به پایین بروید . اگر وجود نداشته باشد، هنگام نصب درایور در قسمت آماده سازی^{(Preparation)} این پست مشکلی پیش آمده است . برای ادامه ایجاد DSN ، مطمئن شوید که MySQL ODBC 5.x Driver برجسته شده است و روی دکمه Finish کلیک کنید. اکنون باید پنجره ای شبیه به لیست زیر مشاهده کنید:

در مرحله بعد باید اطلاعات لازم برای تکمیل فرم نشان داده شده در بالا را ارائه دهید. پایگاه داده و جدول MySQL که برای این پست استفاده می کنیم، روی یک ماشین توسعه است و فقط توسط یک نفر استفاده می شود. برای محیط‌های «تولید»، پیشنهاد می‌شود یک کاربر جدید ایجاد کنید و فقط به کاربر جدید امتیازات SELECT بدهید. ^(SELECT)در آینده، در صورت لزوم، می توانید امتیازات اضافی اعطا کنید.

پس از اینکه جزئیات پیکربندی منبع داده خود را ارائه کردید، باید روی دکمه Test کلیک کنید تا مطمئن شوید که همه چیز درست است. در مرحله بعد روی دکمه OK کلیک کنید. اکنون باید نام منبع داده ای را که در فرم در مجموعه قبلی فهرست شده در پنجره مدیر منبع داده ODBC ارائه کرده^{(ODBC Data Source Administrator)} اید مشاهده کنید:

ایجاد اتصال صفحه گسترده

اکنون که با موفقیت یک DSN جدید ایجاد کرده اید، می توانید پنجره ODBC Data Source Administrator را ببندید و ^{(ODBC Data Source Administrator)}Excel را باز کنید. پس از باز کردن اکسل^(Excel) ، روی نوار Data کلیک کنید. ^(Data)برای نسخه های جدیدتر اکسل^(Excel) ، روی دریافت داده^{(Get Data)} ، سپس From Other Sources و سپس From ODBC کلیک کنید.

در نسخه‌های قدیمی اکسل^(Excel) ، این فرآیند کمی بیشتر است. ابتدا باید چیزی شبیه به این را ببینید:

مرحله بعدی این است که روی پیوند Connections که درست زیر کلمه Data در لیست برگه ها قرار دارد کلیک کنید. محل پیوند Connections به رنگ قرمز در تصویر بالا مشخص شده است. باید با پنجره Workbook Connections روبرو شوید:^{(Workbook Connections)}

در مرحله بعد روی دکمه Add کلیک کنید. این به شما پنجره اتصالات موجود را نشان می دهد:^{(Existing Connections)}

بدیهی است که شما نمی خواهید روی هیچ یک از اتصالات ذکر شده کار کنید. بنابراین بر روی دکمه Browse for More… کلیک کنید. با این کار پنجره انتخاب منبع داده به شما نمایش داده می شود:^{(Select Data Source)}

درست مانند پنجره قبلی Existing Connections ، شما نمی خواهید از اتصالات لیست شده در پنجره Select Data Source استفاده کنید. بنابراین، می‌خواهید روی پوشه +Connect to New Data Source.odc کلیک کنید. برای انجام این کار، اکنون باید پنجره Data Connection Wizard را ببینید:^{( Data Connection Wizard)}

با توجه به گزینه های منبع داده لیست شده، می خواهید ODBC DSN را هایلایت کنید و روی Next کلیک کنید . مرحله بعدی Data Connection Wizard همه منابع داده ODBC موجود در سیستمی را که استفاده می کنید نمایش می دهد.

امیدواریم، اگر همه چیز طبق برنامه پیش رفته باشد، باید DSN را که در مراحل قبلی ایجاد کرده‌اید، در فهرست منابع داده  ODBC ببینید. ^(ODBC)آن را هایلایت^(Highlight) کرده و روی Next کلیک کنید .

مرحله بعدی در Data Connection Wizard ذخیره و تکمیل است. فیلد نام فایل باید به صورت خودکار برای شما پر شود. شما می توانید توضیحات ارائه دهید. توضیحات استفاده شده در مثال برای هر کسی که ممکن است از آن استفاده کند کاملاً توضیحی است. سپس روی دکمه Finish در سمت راست پایین پنجره کلیک کنید.

اکنون باید به پنجره Workbook Connection برگردید. ^{(Workbook Connection)}اتصال داده ای که ایجاد کردید باید لیست شود:

وارد کردن داده های جدول^{(Importing the Table Data)}

می توانید پنجره Workbook Connection را ببندید. ^{(Workbook Connection)}ما باید روی دکمه Existing Connections در نوار داده ^(Data)اکسل^(Excel) کلیک کنیم. دکمه Existing Connections باید در سمت چپ روی نوار Data قرار داشته باشد.^(Data)

با کلیک بر روی دکمه Existing Connections باید پنجره Existing Connections به شما نمایش داده^{(Existing Connections)} شود. شما این پنجره را در مراحل قبلی مشاهده کرده اید، تفاوت اکنون این است که اتصال داده شما باید در بالای صفحه فهرست شود:

اطمینان حاصل کنید^(Make) که اتصال داده ای که در مراحل قبلی ایجاد کرده اید برجسته شده است و سپس بر روی دکمه Open کلیک کنید. اکنون باید پنجره Import Data را ببینید:^{(Import Data)}

برای اهداف این پست، ما قصد داریم از تنظیمات پیش فرض در پنجره Import Data استفاده کنیم. ^{(Import Data)}در مرحله بعد روی دکمه OK کلیک کنید. اگر همه چیز برای شما درست بود، اکنون باید با داده های جدول پایگاه داده MySQL در کاربرگ خود به شما نمایش داده شود.

برای این پست، جدولی که با آن کار می کردیم دو فیلد داشت. فیلد اول یک فیلد INT با افزایش خودکار با عنوان ID است. فیلد دوم VARCHAR (50) و با عنوان fname است. صفحه گسترده نهایی ما به این شکل است:

همانطور که احتمالا متوجه شده اید، ردیف اول شامل نام ستون های جدول است. همچنین می‌توانید از فلش‌های کشویی کنار نام ستون‌ها برای مرتب‌سازی ستون‌ها استفاده کنید.

بسته شدن^(Wrap-Up)

در این پست به محل یافتن آخرین درایورهای ODBC برای MySQL ، نحوه ایجاد DSN ، نحوه ایجاد اتصال داده صفحه گسترده با استفاده از DSN و نحوه استفاده از اتصال داده صفحه گسترده برای وارد کردن داده ها به صفحه گسترده اکسل^(Excel) اشاره کردیم . لذت بردن!

 

Connecting Excel to MySQL

Sure Excel is used for spreadsheetѕ, but did you know you can connect Excel to еxternal data sоurces? In this article we’re going to discuss how to connect an Excеl spreadsheet to a MySQL database table and use the data in the database table to populate our spreadѕheet.  There are a few things уou need to do in order to prepare for this connection.

Preparation

First, you must download the most recent Open Database Connectivity (ODBC) driver for MySQL.  The current ODBC driver for MySQL can be located at

https://dev.mysql.com/downloads/connector/odbc/

Make sure after you download the file that you check the file’s md5 hash against that listed on the download page.

Next, you will  need to install the driver you just downloaded.  Double click the file to start the install process.  Once the install process is complete you will need to create a Database Source Name (DSN) to use with Excel.

Creating the DSN

The DSN will contain all of the connection information necessary to use the MySQL database table.  On a Windows system, you will need to click on Start, then Control Panel, then Administrative Tools, then Data Sources (ODBC).  You should see the following information:

Notice the tabs in the image above.  A User DSN is only available to the user that created it.  A System DSN is available to anyone that can log into the machine.  A File DSN is a .DSN file that can be transported to and used on other systems that have the same OS and drivers installed.

To continue creating the DSN, click on the Add button near the top right corner.

You will probably have to scroll down to see the MySQL ODBC 5.x Driver.  If it’s not present, something went wrong with installing the driver in the Preparation section of this post.  To continue creating the DSN, make sure MySQL ODBC 5.x Driver is highlighted and click on the Finish button. You should now see a window similar to the one listed below:

Next you will need to supply the information necessary to complete the form shown above.  The MySQL database and table we’re using for this post is on a development machine and is only used by one person. For “production” environments, it is suggested you create a new user and grant the new user SELECT privileges only.  In the future, you can grant additional privileges if necessary.

After you have supplied the details for your data source configuration, you should click on the Test button to make sure everything is in working order.  Next, click on the OK button. You should now see the data source name you supplied on the form in the previous set listed on the ODBC Data Source Administrator window:

Creating the Spreadsheet Connection

Now that you have successfully created a new DSN, you can close the ODBC Data Source Administrator window and open Excel.  Once you have opened Excel, click on the Data ribbon.  For newer versions of Excel, click on Get Data, then From Other Sources, then From ODBC.

In older versions of Excel, it’s a bit more of a process. Firstly, you should see something like this:

The next step is to click on the Connections link located right under the word Data in the tab list. The location of the Connections link is circled in red in the above image. You should be presented with the Workbook Connections window:

The next step is to click on the Add button.  This will present you with the Existing Connections window:

Obviously you don’t want to work on any of the connections listed.  Therefore, click on the Browse for More… button. This will present you with the Select Data Source window:

Just like the previous Existing Connections window, you do not want to use the connections listed in the Select Data Source window.  Therefore, you want to double click on the +Connect to New Data Source.odc folder.  In doing so, you should be now see the Data Connection Wizard window:

Given the data source choices listed, you want to highlight ODBC DSN and click Next.  The next step of the Data Connection Wizard will display all of the ODBC data sources available on the system you are using.

Hopefully, if all as gone according to plan, you should see the DSN that you created in previous steps listed among the ODBC data sources.  Highlight it and click on Next.

The next step in the Data Connection Wizard is to save and finish.  The file name field should be auto filled for you.  You can supply a description.  The description used in the example is pretty self explanatory for anyone that might use it.  Next, click on the Finish button in the lower right of the window.

You should now be back at the Workbook Connection window.  The data connection you just created should be listed:

Importing the Table Data

You can close the Workbook Connection window.  We need to click on the Existing Connections button in the Data ribbon of Excel.  The Existing Connections button should be located to the left on the Data ribbon.

Clicking on the Existing Connections button should present you with the Existing Connections window.  You’ve seen this window in previous steps, the difference now is that your data connection should be listed near the top:

Make sure the data connection you created in the previous steps is highlighted and then click on the Open button.  You should now see the Import Data window:

For the purposes of this post, we are going to use the default settings on the Import Data window.  Next, click on the OK button.  If everything worked out for you, you should now be presented with the MySQL database table data in your worksheet.

For this post, the table we were working with had two fields.  The first field is an auto-increment INT field titled ID.  The second field is VARCHAR(50) and is titled fname.  Our final spreadsheet looks likes like this:

As you’ve probably noticed, the first row contains the table column names.  You can also use the drop down arrows next to the column names to sort the columns.

Wrap-Up

In this post we covered where to find the latest ODBC drivers for MySQL, how to create a DSN, how to create a spreadsheet data connection using the DSN and how to use the spreadsheet data connection to import data into an Excel spreadsheet. Enjoy!

 

Related posts

• نحوه حذف خطوط خالی در اکسل

• نحوه استفاده از ویژگی Speak Cells اکسل

• نحوه درج کاربرگ اکسل در Word Doc

• نحوه استفاده از تجزیه و تحلیل What-If اکسل

• نحوه تعمیر یک ردیف در اکسل

• سطرها و ستون ها را در یک کاربرگ اکسل گروه بندی کنید

• تفاوت بین Microsoft Excel Online و Excel For Desktop

• راهنمای VBA پیشرفته برای MS Excel

• داده های کاربرگ خود را برای چاپ در اکسل مرکز کنید

• Google Sheets در مقابل Microsoft Excel - چه تفاوت هایی دارند؟

• نحوه محاسبه امتیاز Z در اکسل

• نحوه استفاده از تابع PMT در اکسل

• چگونه خطاهای #N/A را در فرمول های اکسل مانند VLOOKUP برطرف کنیم

• نحوه درج سریع چند ردیف در اکسل

• نحوه ادغام داده ها در چندین فایل اکسل

• از نام های محدوده پویا در اکسل برای کشویی های انعطاف پذیر استفاده کنید

• چرا باید از محدوده های نامگذاری شده در اکسل استفاده کنید؟

• 3 روش برای تقسیم سلول در اکسل

• چگونه یک نمودار دایره ای در اکسل بسازیم

• زمان استفاده از Index-Match به جای VLOOKUP در اکسل