Reading & Writing to an excel file

You can store data in a Microsoft Excel Spreadsheet and then use ASP to extract the infromation. The spreadsheet acts like a database and you can use standard SQL statements to query the data. The process is fairly simple and I will break it down into three steps:



STEP-1: Create an Excel Spreadsheet

STEP-2: Define named ranges in the spreadsheet

STEP-3: Write ASP code read the file



STEP-1:

Lets get started with the spreadsheet. You MUST have Microsoft Excel installed on your computer to create and Excel Spreadsheet. I have created a folder called "excel" under C:\Inetpub\wwwroot\
and thats where I will create/save my Excel spreadsheet.



a) Open Excel and create a spreadsheet that looks like this:







In this sheet the SR, NAME and EMAIL are the column names. When we query the data from this spreadsheet, we can limit the results by selecting only one or two columns e.g. SELECT NAME FROM my_range;



STEP-2:



Now that we have created a spreadsheet, its time to define a named range within Excel that will be treated as a table for our SQL statement. To create a named range, select all the fields that have data in them, with the column names, then go to INSERT > Name > Define... > Type in my_range > Press OK > Save your file in C:\inetpub\wwwroot\excel\excel.xls








I have saved my excel file as excel.xls.



STEP-3:



Now that we have the excel file and the named range in place, we can start working on the ASP code. Here is the code to read this excel file using a DSN-LESS approach










To test the code, point your browser to http://127.0.0.1/excel/read_excel.asp and you will see the following output:







You can customize the out anyway you want and the possibilities are endless :)



Enjoy and Happy ASP'ing.



Following are some links that were very helpful when I was digging up this information:




http://www.webdeveloper.com/forum/showthread.php?t=77596

http://www.carlprothman.net/Default.aspx?tabid=81

http://support.microsoft.com/kb/195951

MSDN


More Tutorials at http://www.NabeelAkhtar.NET

Tutorial Discussion Forums: http://Forum.NabeelAkhtar.NET