Instructions

This lab will give you experience creating an Excel file from data pulled from a database.

• For this to work, Microsoft Office must be installed on the server that the .NET application is running on. Not only that, but when Office is installed, all .NET programming compatibility options must be enabled and installed as well.

Procedures

1. Start a new web site.
2. Add a new COM object: Microsoft Excel 11.0 Object Library
   • The process should be similar to:
     Right click solution: Add Reference > COM tab > Microsoft Excel 11.0 Object Library
   • NOTE: the object library you use is dependent upon the version of Office installed on the server.
   • NOTE: you should now see an Excel 11 entry in your <assemblies> in your web.config file.
      
3. Add a connection string to your <appSettings> in your web.config file.
   • NOTE: this is your SQL Server username and password
   • <appSettings>
           <add key="connectString" value="server=sotdev4.tech.purdue.edu;uid=xxx;pwd=yyy;database=zzz"/>
     </appSettings>
      
     
4. Set Identity Impersonation to true inside of <system.web> in your web.config file.
   • NOTE: this is your server space username and password
   • <system.web>
           <identity impersonate="true" userName="aaa" password="bbb" />
           ...
     </system.web>
      
5. Set Permissions
   • Right click on your folder > Properties > Security tab
   • Add ASPNET and give it modify permissions
   • Add Internet Guest Account and give it modify permissions
      
6. Create the database that you will use
• Here is the ERD of the database you will use
• Run this script in the DBMS to create the tables (9 tables)
• Then run this script in the DBMS to populate the tables with data (13,082 records across 9 tables)
• Make sure the script has finished loading before you copy it! Should be 13,099 lines.
   
7. Add using statements
• using Excel = Microsoft.Office.Interop.Excel;
• using System.Data.SqlClient;
• using System.IO;
• using System.Text;
• using System.Drawing;
• using System.Diagnostics;
• using System.Reflection;
   
8. Create an application that mirrors this solution
   • The first Excel file that is generated is an Inventory List that should look just like this one
   • 1069 records, 8 columns
   • Select: P.Name AS ProductName, P.ProductNumber, P.ListPrice, PrI.Shelf, PrI.Bin, PrI.Quantity, L.Name AS LocationName, L.Availability
     Use Tables: Product P, ProductInventory PrI, Location L
     Be sure to join tables in the WHERE clause
     Order by: ProductID
      
   • The second Excel file is a Work Order List of "Mountain" Items that should look just like this one
   • 980 records, 8 columns
   • Select: P.Name AS ProductName, P.ProductNumber, WO.WorkOrderID, WO.OrderQty, WO.DueDate, WOR.PlannedCost, WOR.ActualCost, SR.Name AS ScrapReason
     Use Tables: Product P, WorkOrder WO, WorkOrderRouting WOR, ScrapReason SR
     Be sure to join tables in the WHERE clause
     Add and AND to look for products LIKE '%Mountain%'
     Order by: WorkOrderID
      
   • The third Excel file is a Price List History that should look just like this one
   • 395 records, 8 columns
   • NOTE: the use of "CONVERT(VARCHAR(10),PL.StartDate,1) AS StartDate" in 3 different places where "StartDate" changes
   • SELECT P.Name AS ProductName, P.ProductNumber, P.ListPrice AS CurrentPrice, P.StandardCost, CONVERT(VARCHAR(10),PL.StartDate,1) AS StartDate, CONVERT(VARCHAR(10),PL.EndDate,1) AS EndDate, PL.ListPrice AS ListedPrice, CONVERT(VARCHAR(10),PL.ModifiedDate,1) AS ModifiedDate
     Use Tables: Product P, ProductListPriceHistory PL
     Be sure to join tables in the WHERE clause
     Order by: P.ProductID
      
   • Your aspx code should look like this - we'll focus solely on completing the cs
     • NOTE: don't copy the page declaration at the top... Use the one your project created for you.
        
   • Here is some starter .cs code to provide you with comments and structure
     • NOTE: don't copy the public partial class declaration line... Use the one your project created for you.
        

Grading