Back to the top

Create Sample Excel Worksheet

1. Start Microsoft Excel, and then create a new worksheet.
2. Add the following information to the new worksheet to create a simple Excel database:
   
   Collapse this tableExpand this table

   	A	B
   1	FirstName	LastName
   2	Scott	Bishop
   3	Katie	Jordan

   NOTE: Although the data starts with cell A1 in this example, you can add this data to any adjacent cells within the worksheet.
3. Highlight the rows and columns where the data resides.
4. On the Insert menu, point to Name, and then click Define.
5. In the Names in workbook text box, type myRange1, and then click OK.
6. On the File menu, click Save. In the Save in list, select the Web server root (which is typically C:\InetPub\Wwwroot\). In the File name text box, type ExcelData.xls. Click OK.
7. On the File menu, click Exit.

Back to the top

Create ASP.NET Sample Using Visual C# .NET

1. Open Microsoft Visual Studio .NET. The Visual Studio .NET Integrated Development Environment (IDE) is displayed.
2. On the File menu, point to New, and then click Project.
3. In the New Project dialog box, under Project Types, click Visual C# Projects. Under Templates, click ASP.NET Web Application.
4. In the New Project dialog box, locate the Name and Location text boxes. Notice that the Name text box is not available (it appears grayed out or dimmed). The Location text box contains the following text (or similar):
   http://localhost/WebApplication1
   Replace the text in the Location text box with http://localhost/ExcelCSTest, and then click OK. A new project is created, which includes a Web Form named WebForm1.aspx.
5. In the Visual Studio .NET IDE, locate the Solution Explorer window. If you cannot find it, click Solution Explorer on the View menu.
6. In Solution Explorer, right-click WebForm1.aspx, and then click View Designer to display the designer for the appearance of the page. The designer allows you to add controls and manipulate the appearance of the page.
7. Locate the toolbox. Depending on your IDE Option settings, the toolbox can appear as a window or a button (which often appears on the left side of the IDE). If you cannot find the toolbox, click Toolbox on the View menu.
   
   If the toolbox appears as a button, move the pointer over the button so that the contents of the toolbox are displayed.
8. When the designer view of a Web Form is active, the toolbox is divided into sections, including the Web Forms, Components, HTML, and other sections. Click the Web Forms section.
9. In the Web Forms section of the toolbox, click DataGrid, and then drag it onto the designer for WebForm1.
10. Right-click WebForm1.aspx, and then click View Code to display the code-behind page source.
11. Add the following statements to the top of the code-behind page, above the namespace section:

    using System.Data.OleDb;
    using System.Data;
    					

12. Highlight the following code, right-click the code, and then click Copy. In WebForm1.aspx.cs, paste the code into the Page_Load event:

    // Create connection string variable. Modify the "Data Source"
    // parameter as appropriate for your environment.
    String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    	"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +
    	"Extended Properties=Excel 8.0;";
    
    // Create connection object by using the preceding connection string.
    OleDbConnection objConn = new OleDbConnection(sConnectionString);
    
    // Open connection with the database.
    objConn.Open();
    
    // The code to follow uses a SQL SELECT command to display the data from the worksheet.
    
    // Create new OleDbCommand to return data from worksheet.
    OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM myRange1", objConn);
    
    // Create new OleDbDataAdapter that is used to build a DataSet
    // based on the preceding SQL SELECT statement.
    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
    
    // Pass the Select command to the adapter.
    objAdapter1.SelectCommand = objCmdSelect;
    
    // Create new DataSet to hold information from the worksheet.
    DataSet objDataset1 = new DataSet();
    
    // Fill the DataSet with the information from the worksheet.
    objAdapter1.Fill(objDataset1, "XLData");
    
    // Bind data to DataGrid control.
    DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
    DataGrid1.DataBind();
    
    // Clean up objects.
    objConn.Close();
    					

13. On the File menu, click Save All to save the project files.
14. On the Build menu, click Build to build the project. This prepares the code in the code-behind page so that it can be executed.
15. In Solution Explorer, right-click WebForm1.aspx, and then click View in Browser to run the code.

Back to the top

Additional Code Explanation

// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
	"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +
	"Extended Properties=Excel 8.0;";
				

     C drive
        - Inetpub
            - Wwwroot (which contains ExcelData.xls)
                - ExcelCSTest (which contains WebForm1.aspx)
				

     Web Root (which contains ExcelData.xls)
        - ExcelCSTest (which contains WebForm1.aspx)
				

Server.MapPath("../ExcelData.xls")
				

Back to the top