ASP.NET Web - GridView control, SQL class, Web Service, and Generate Excel
I use C# language to develop website. This shows how to use gridview basically and to generate excel file.
- Theme Skin
- Ajax Control Toolkit
- GridView control
- Generate Excel Report
- Web Service
Here is the webpage we will see.
Install AJAX Control Toolkit
First, we install a good tool called Ajax Control toolkit. It's located in the packages folder created by VS2012 after you use NuGet Package Manager to download it.
Create a Tab in Visual Studio (you can named it Ajax Toolkit).
Right click on it and select: Choose Items..
Click Browse and navigate to the packages folder that VS 2012 creates with your source. Something like this ..\packages\AjaxControlToolkit.7.0123\lib\40\AjaxControlToolkit.dll
Create webpage with Gridview
Create a webpage form with separated files: view and code. Here is view page.
Generate Excel Report
In the first line, the Theme="TutorialSkin" is a theme skin file. Under the solution/project, add a skin file into App_Themes folder. Here is my skin file content.
This line "<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit" %>" is to define ajax control toolkit which we just install.
I use the ajax toolkit for the date field. There is a calendar image next it. When user clicks the calendar, a calendar shows up. You can enter a date value in the field. I use two controls of the toolkit: CalendarExtender and ValidatorCalloutExtender. Here is the code.
And here is code page. Notes:
where Resources.Resource.sql_filename is a resource text file. I use it to store all SQL that makes clean and easy to follow up. Add text file in App_GlobalResource folder and then add it into resource.resx.
In the above code, there is a line "ELClass.DB m_db = null;". It is a class for handling database SQL. I create it as ELClass.cs and put into site folder App_Code under project root. Here is its content.
In the C# code file and the function "btnExcel_Click", I call a web service "WsReport.GenerateExcelFile". The usage is
WsReport ws = new WsReport();
string url = ws.GenerateExcelFile(sql, @"template_filename.xlsx", outputFile);
The web service using a template excel file as format generates excel report and saves in a folder and return a URL (file location). Then user can download the excel file by clicking the URL link.
Here is a sample template excel file.
Create Web Service
I create a folder called "Ws" for all web services asmx files. Right click the folder "Ws" and choose "Add" > "Add New Item...", click Web Service and name it as "WsReport" and check "Place code in separate file" checkbox.
Visual Studio creates a file "WsReport.asmx" in "Ws" folder and a file "WsReport.cs" in App_Code. Here is its content.