ASP.NET – Generating an Excel Report on the Fly Using the Interop.Excel Namespace
Choosing the best way to export your data from an ASP.NET Web page to Excel for your users is never an easy task. There are many ways to accomplish this task, each way having its pros and cons.
In an earlier article I covered one of the more common ways: how to take an existing report that has already been generated as an HTML table and stream it as Excel to the client browser. This is a great technique, but leaves it up to Excel to interpret the HTML that you are sending its way. Furthermore, Microsoft seems to be deprecating the markup that was previously so amazingly convenient when formatting HTML streamed to Excel. The article also goes into depth about what markup commands are still useful and what markup has been deprecated. It’s definitely worth reading this article if you are considering streaming HTML as an Excel document using the command: Response.ContentType = “application/ms-excel”
Before I go into further details on using the Interop.Excel namespace to generate Excel files, I’d like to point out that I am providing a fully functional example at the end of this article. If you are primarily interested in the code, then you can skip to the bottom of this article and you can copy and paste the code sample into a Visual Studio project where it should run without any problems.
(more…)
Regular Expressions in .NET to Surround but not Replace Text Matches
ASP.NET offers strong support for regular expressions in the System.Text.RegularExpressions Namespace. This is great since regular expressions are essential if you want to do anything beyond basic text manipulations and searches.
You can read more about regular expression support in ASP.NET on the official MSDN page. The MSDN site also includes a few good getting started examples if you are interested.
The Problem
Recently I found myself facing an interesting task: I was building a simple search form which consisted of a text area and submit button on a Web page. The desired behavior was as follows:
- Users could enter a partial term into the text area and then click the submit button to search for that partial text match.
- The codebehind would search through an MS SQL Server database to find the partial matches
- The codebehind would render the search results into a GridView control with the partial match text highlighted with a yellow background color
In theory putting this together is quite simple. The catch was figuring out how to highlight the partial text that the user searched for in the database results without changing the capitalization or other formatting.
For example if the user searched for the term EXAMPLe TexT (notice the crazy capitalization), then the data results should highlight the correct results in the search result output using the capitalization/formatting found in the database. Or more simply: the results should not depend on the user-entered search term, but should show the original text from the database surrounded by HTML markup tags. So no matter what strange capitalization the user entered, the search results should be properly capitalized and highlighted.
Set up a Multi-Line Editable GridView Disassociated from a DataSource
Multi-Line Editable Gridview Control
GridView controls are versatile multi-purpose ASP.NET widgets. I like the default functionality when hooked up to a data source, but depending on a project’s requirements I also like the ability to tweak the default functionality to make the GridView behave in different ways.
For example, in past projects users have asked me to create a more Excel-like interface for viewing and managing their data. This is certainly possible with a GridView control. To take things one step further, in some cases the GridView can even be used as a UI-specific widget that runs disassociated from a DataSource.
Basically the GridView control can be adjusted to act like a tabular widget on a Web page which allows the user to add/remove/edit multiple rows of information at once. I have not been able to find a front-end only widget of the same calibre that has similar behavior, and coding it completely from scratch using JavaScript and HTML tables seems like overkill.
(more…)
Custom Function to Inject HTML Tags Iteratively into ListView Column Text
The ASP.NET system libraries offer powerful string manipulation functionality in the form of extensive Regular Expression support as well as String-specific functions such as Split and Replace. In 99% of cases these functions are sufficient to use to quickly manipulate strings. However in one case I had to put together a special function of my own.
What was the problem & why build a custom function?
The UI to be developed was rather simple. I wanted to write a basic search Web page that contained a TextBox for the user to enter their search text, a Submit button to run the search, and a ListView control to display the search results.
Although from the UI perspective everything was very straightforward, the catch became clear with the requirement to have the search term bolded in the search results shown by the ListView control. This became a problem for the following reasons: (more…)
Using a JQuery AutoComplete Widget with an ASP.NET Web Service
I’ve been playing around with hooking up the JQuery AutoComplete widget with an ASP.NET Web Service. This is a really neat idea that adds a lot of whizzbang to a standard Web-based form. In theory one could also use ASP.NET UpdatePanel controls with traditional ASP.NET/HTML form controls, but I really wanted to look further into the JQuery Ajax functionality since this fits together very neatly with the JQuery AutoComplete widget.
I found three really good articles on the topic that I am listing below:
- Mudassar Khan’s ASP Snippets Blog article - Implement jQuery Autocomplete using Web Service in ASP.Net
- Andy Marshall’s article on CodeProject - Introduction to using jQuery with Web Services
- Luca Congiu’s article on MSDN - Use Jquery Autocomplete With Web Service (ASMX) DataSource
From reading the articles I have been able to adapt the information these authors presented into the logic I am looking for.
The Desired Functionality
Specifically what I am looking to do is to have a Web form with a text entry area that will offer a user suggestions for text while a user is typing. The options should be selectable and should pass an id value.
Furthermore, the suggestion list will kick in after the user has entered a minimum of two characters and will match anywhere within the database name field text for matches to show. To reduce lag and the amount of data being transmitted I am capping the returned results to a maximum of 500. Furthermore I am using the JSON protocol to keep the transferred data lightweight.
(more…)
VSTO for MS Outlook 2007 – Using msocontroledit and msoControlComboBox in the Commandbars
Outlook Custom Search Fields
VSTO for MS Outlook can be a powerful way to customize Outlook programmatically. However, the syntax can be finicky at times and I have found a few instances where the programming logic becomes tricky.
For example, you can use VSTO to add a text box to the Outlook command bar window. This can be put to a number of good uses when building a custom component for Outlook. My thoughts behind adding the controls to Outlook were to allow the user to select a type of search to run in the custom dropdown list, and then to use the text box for the user to add text that the system would use to run a search on custom email fields.
In my case I wanted to add a textbox and a ComboBox to my main Outlook Explorer view. The VSTO controls in Outlook are called msoControlEdit and msoControlComboBox respectively.
At first glance adding a simple text box and dropdown list sounds like a simple requirement. But as I found out, this isn’t quite the case. The problem? Outlook by default will clear any entered text from the new text box when focus is lost. I’m not sure what the reasoning behind this is, but from reading help articles and forum posts, it appears that many people are coming up against the same problem.
As a positive, the ComboBox control does retain selected text, so we will not need to worry about handling this control.
For the convenience of readers of this article; I am including a full example of how to add a textbox and a ComboBox to the Outlook main command bar at the end of this article.
(more…)
Use RAD with Visual Studio WebForms to Programmatically Query a SQLDataSource Control from Custom Code
Rapid Application Development (RAD) offers huge advantages to developers under time constraints looking to get an application released as quickly as possible. Visual Studio 2010′s WebForms is a great environment to use when looking at possible RAD solutions.
In this article I will cover the issue of using RAD with custom code to speed up writing database connections and queries. This is part of my series of articles investigating ASP.NET and Visual Studio 2010.
(more…)
VS2010 Custom Outlook Folder View Including UDFs (User Defined Fields)
If you are building an Outlook Add-In application with Visual Studio, it’s likely that at some point you will want programmatic control over the information that Outlook shows in a specific email folder.
For instance, let’s say that you have code that automatically backs up sent emails to a custom folder hierarchy. So if the email you sent includes sample code, then the email should be automatically backed up into a custom sub-folder called “Sample Code” that exists within your custom “Programming” folder within your Outlook mailbox. If that sounded confusing, here’s an image of what I’m talking about:
+Mailbox |_Programming |_Notes |_Sample_Code |_General |_Sent Items |_Etc...
(more…)
ASP.NET Overview of Binding a DropDownList to a FormView
If you use (or want to use) .NET WebForms databinding then you know that dropdown lists can be tricky to populate and mange. For example, say you have a FormView control with an embedded DropDownList control that you want to bind and pre-select when someone views the form. It’s not immediately intuitive how to do so, but it’s really quite straightforward.
Here’s how: (more…)
Outlook Custom Add-In to Archive Emails on Send Based on Keyword in Subject Line (Visual Studio 2010)
Background
The great thing about the Microsoft technology stack is that things tend to work together without the need to download and install unfamiliar libraries from unknown sources. I continue to be impressed by how Microsoft centralizes its code libraries with Visual Studio and .NET 4 to make most development tasks quite seamless.
For example I am working on an add-on component for MS Outlook 2007 that will help people archive their sent emails by including a keyword in the subject line of their email.
Visual Studio has default project templates for Office, including templates for Outlook 2007 and Outlook 2010. Happily enough, the code required to interface with Outlook is auto-generated behind the scenes and the programmer simply has to worry about writing the actual logic of the component. This is a big step forward from how things used to be!
Another big plus for my development efforts is the set-up and deployment project template that Microsoft has included with Visual Studio. Once my Outlook component has been fully coded I can simply create a Deployment project to send out to users. That way a user can install my component simply by clicking the ‘Next’ button on an installer wizard.
(more…)
ASP.NET GridView Paging Problem – Paging Increases the First Column Width
A request came to my desk today about a project that is using an ASP.NET 2.0 DataGrid control to display a set of data. The DataGrid has paging enabled, but the client was complaining that the first column of the report was far too wide for the data it was displaying.
I took a look into this and the client definitely had a point. Although the numeric values of the first column were single digit integers, the Paging information that the ASP.NET environment was appending extended the width of the first ID column to over 150 pixels which was far too wide.
The Problem with the DataGrid Control
When I looked into the HTML markup of the DataGrid the reason for the large first column became apparent. The DataGrid was displaying rows of data with 5 table cells per row, but the final paging row being appended to the DataGrid only had a single table cell into which the numeric paging links were being added.
I looked this problem up online and found that many people have run into this bug throughout the past few years. But I assume that no fix for this issue has ever been released since the DataGrid control is now deprecated.
Below is an example of what the last two rows of markup looked like for the DataGrid, with the last (paging) row containing only one table cell.
RadioButtonList Binding Overview for ASP.NET WebForms
RadioButtonList in ASP.NET
The RadioButtonList control is situationally useful. Although I don’t find myself using it often, when I do need it, I have to look up the basic functionality and how to apply it. This has led me to write this article as a guide and a code-reference.
Set up the WebForm
For starters, drag and drop a DataSource onto your Web form. In my case I will use a SQLDataSource and I will populate its Select, Update, and Delete commands with stored procedures I have created. My goal is to create a RadioButtonList that lists saved default searches for a user and to add a LinkButton control to use as a general delete button.
The following snippet illustrates adding the SQLDataSource:
WebForms Declarative Data Binding using Eval & Bind
I have been using Visual Studio’s Declarative WebForms programming model lately to quickly create Web-based form interfaces. As part of this process I have been making extensive use of control binding using the Eval() and Bind() statements, and in some cases even the old ASP Response.Write syntax. As part of my review, I will use this article to to go over the pros and cons of using either option.
As a general rule of thumb, both Eval() and Bind() work similarly for read operations but Bind() offers more functionality when binding controls to a data source for the full spectrum of Read/Write operations.
ASP.NET Using the Form Option when Defining your DataSource Parameters
When working on binding a SQLDataSource to a GridView control one goes through a series of steps in the Visual Studio DataSource configuration wizard. A step that I often use but do not look much into is that of associating a parameter source with the parameters being passed to the database.
This step is quite important and ends up saving me a lot of time when I’m hooking up a WebForm that I have built to a datasource, but although I regularly use a few of the parameter sources, for a long time I was not familiar with what some of the parameter source options even do.
For instance, the Form parameter source really didn’t seem very clear to me when I first looked at it and it took a bit of looking into to find out what it does.
If you are not sure what I’m talking about, here is a screen-shot of the Visual Studio wizard where I am selecting a Form parameter source: (more…)
Call a JavaScript Function After an ASP.NET Save Event
Why/How
It can be useful to be able to call a JavaScript function after an ASP.NET event has completed, but it is not immediately intuitive how this can be done.
Adding an onClientClick event to an ASP.NET button control will execute the JavaScript call before the ASP.NET server side code executes, so an alternate solution is required.
For example today I was working on a WebForms page that has a Gridview presenting data, and that displays a detailed form page for each row in the GridView in an IFrame when one of the GridView rows is clicked. I use a FormView to handle the detailed form page information.
After editing the data contained in the detailed form page, a user can click the save button at which point the FormView’s ASP.NET Update event is triggered, and then subsequently the button’s click event fires.
Here is an example of what the page looks like, with the Gridview at the top and a detailed forms page contained in an Iframe below: (more…)
