# Monday, March 18, 2013
# Sunday, March 17, 2013

Thank you to all who attended my presentations at the Orlando Code Camp this past weekend. The audiences were great and I really enjoyed the conference.

I had numerous requests for the materials, which are available below.

Sunday, March 17, 2013 11:00:00 PM (GMT Standard Time, UTC+00:00)
# Friday, March 15, 2013

OVERVIEW

Microsoft Excel data can be treated like many other data sources from within a .NET application: we can connect to an Excel data source and we can query it using Structured Query Language (SQL). We don't even need to launch Excel or even have Excel installed in order to do so. All we need is the appropriate drivers.
This is good news if we need to read Excel data on a server, such as a web server. It’s possible to user Office Interop code to launch Excel and manipulate a spreadsheet, but Microsoft advises against doing so (http://support.microsoft.com/kb/257757).

SAMPLE

Here is sample code for querying Excel data and loading it into an ADO.NET DataTable

public static DataTable GetExcelData(string fullPath)
{
    var connectionString = string.Format
        (
        @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'",
        fullPath
        );
    var adapter = new OleDbDataAdapter
        (
        "SELECT * FROM [Sheet1$]", 
        connectionString
        );
    var ds = new DataSet();
    adapter.Fill(ds, "ExcelData");
    DataTable excelTable = ds.Tables["ExcelData"];
    return excelTable;
}

The Connection String contains the driver and the full path to the Excel file. The “HDR” in the connection string indicates that the first row of the spreadsheet contains column headers. These headers will become the column names in our ADO.NET data table.
We can call this code from a web page to read an Excel file uploaded with the ASP.NET FileUpload control. The code is shown below.

protected void ReadExcelButton_Click(object sender, EventArgs e) 
{ 
    if (ExcelUploaderValidator.IsValid) 
    { 
        string fileName = ""; 
        string filePath = Server.MapPath("Upload"); 
        var fullPath = ""; 
        //var fullPath = @"C:\Test\ReadExcel\ReadExcel\bin\Debug" + @"\" + fileName; 
        if (ExcelUploader.HasFile) 
        { 
            var rand = new Random(); 
            fileName = String.Format("{0:000000}-{1}", rand.Next(0, 999999), ExcelUploader.FileName); 
            fullPath = filePath + @"\" + fileName; 
            ExcelUploader.SaveAs(fullPath); 
            DataTable excelTable = ExcelUtils.GetExcelData(fullPath); 
            ExcelGrid.DataSource = excelTable; 
            ExcelGrid.DataBind(); 
        } 
    } 
} 

In this example, we save the uploaded file to a folder on the server; then, call our function, pointing to that file, so we can pass it in as part of the connection string.
For completeness, here is the web page markup in this sample:

<div> 
    <br /> 
    <asp:FileUpload ID="ExcelUploader" runat="server"  /> 
    <asp:RegularExpressionValidator 
        runat="server" 
        ID="ExcelUploaderValidator" 
        ControlToValidate="ExcelUploader" 
        ErrorMessage="Only Excel files or CSV files are allowed" 
        ValidationExpression="^.*\.(xls|XLS|xlsx|XLSX|csv|CSV)$"> 
    </asp:RegularExpressionValidator> 
    <br /> 
</div> 
<asp:Button ID="ReadExcelButton" runat="server" onclick="ReadExcelButton_Click" 
    Text="Read Excel Document" /> 
<asp:GridView ID="ExcelGrid" runat="server"> 
</asp:GridView> 

The query assumes that the workbook we are reading contains a worksheet named “Sheet1” and it reads all the data in that worksheet, copying it into a DataTable.

LIMITATIONS

The Excel data driver only works in 32-bit .NET projects, so you will need to configure the project properties to run in 32-bit mode.

CONCLUSION

In this article, we demonstrated a simple way to read data from an Excel document and copy it into a DataTable for further manipulation and processing.

Friday, March 15, 2013 2:29:32 PM (GMT Standard Time, UTC+00:00)
# Wednesday, March 13, 2013
Wednesday, March 13, 2013 5:14:15 PM (GMT Standard Time, UTC+00:00)
# Thursday, March 7, 2013

Last week, I attended my third Microsoft Global MVP Summit. The Summit is a conference held in the Seattle area open to Microsoft MVPs.

I signed a Non-Disclosure Agreement that prevents me from talking about most of the conference content; but I can tell you about my experience and my impressions.

By far, the best part of the MVP Summit is the chance to meet so many smart people. Many of them I know by reputation before I meet them. Every year I joke that I plan to be the dumbest guy in every room at this conference. And every year, the joke is very close to reality. There are some amazing people here - those with deep knowledge of a technology; those who have built amazing products or open-source projects; those who have written books and blogs that I've read; those who produce podcasts that I listen to regularly; and those who have a story to tell about how they use technology to solve real problems. I love meeting and talking with all these people.

The sessions are good, but, other than REDACTEDREDACTEDREDACTED, I didn't hear about a whole lot of new stuff.

There are some regular events in the evening and I took advantage of those. A party at Ted Neward's house attracted a who's who of technologists and the annual Party With Palermo (hosted by Jeff Palermo of Austin, TX) always attracts a great crowd. I attended a reception for first-time MVPs, even those this was my third summit, because:

  1. It was hosted by INETA and I am on the Board of Directors
  2. It was organized by my friend Joe Guadagno, who did an amazing job
  3. INETA presented a Lifetime Achievement award to Russ Fustino at the event and I wanted to be present to congratulate Russ.

The day after the Global MVP Summit was the ASP.NET Insiders Summit organized by Scott Hanselman. I was excited to attend this conference because I was invited to join the Insiders only a few weeks ago. (I think I was the newest member at the time of the Summit). These sessions were really informative. We got a look at new and proposed language, framework, and IDE features. We also had a chance to provide feedback to the product team; and to see several open-source web frameworks. Another NDA prevents me from revealing too many details of what I saw there, but I really learned a lot from this extra day. I plan to attend the ASP.NET Summit again next year.

Two days before the MVP Summit, a group of attendees and a few other volunteers traveled to the Northwest Harvest Food Bank in Seattle to help pack fruit for needy families in the area. This was a great opportunity to meet people, have fun, and do some good.

The week was exhausting but well worth the trip. If Microsoft will have me, expect to see me at the 2014 Global MVP Summit. And I still expect to be the dumbest guy in each room.

Thursday, March 7, 2013 1:08:02 AM (GMT Standard Time, UTC+00:00)
# Monday, March 4, 2013
Monday, March 4, 2013 5:53:08 AM (GMT Standard Time, UTC+00:00)
# Thursday, February 28, 2013

In this screencast, I show how to create an Azure Virtual Machine.

Azure | Video
Thursday, February 28, 2013 3:28:00 PM (GMT Standard Time, UTC+00:00)
# Monday, February 25, 2013
# Thursday, February 21, 2013

In this screencast, I show how to create an Azure Virtual Machine.

Azure | Video
Thursday, February 21, 2013 8:28:00 PM (GMT Standard Time, UTC+00:00)
# Monday, February 18, 2013
Monday, February 18, 2013 4:53:00 PM (GMT Standard Time, UTC+00:00)