Web page interoperability with .NET browser controls

Everyone knows that the best way to manipulate or extract data from a hypertext page is to use JavaScript. The langue is built entirely to serve that purpose so it’s natural to think about it every time someone tell you to get an element’s text or skip unnecessary screens. Firefox’s XUL even relies on the language to control the entire application’s behavior.

That method does well most of the time but it requires that you have access to the JavaScript source and can modify the page, which is not the case in some situation:

  1. Rapidshare makes you wait for two minutes or so when you want to get some file from them for free. It’s better than before when you have to go past some feline CAPTCHA but you still have to click on the download button after the timer expire, which means you can’t click on a Rapidshare link and leave it to download itself. Will Rapidshare allow you to edit the download page to insert the JavaScript that will click the button for you? No!
  2. You want to capture stock value from your finance information agency’s web site and save it into an Excel file for later reference, what should you do? Ask Yahoo Finance to modify its web page to send some fancy XMLRequest to your server every time it updates? And even if they do so you’ll have to make a listening server for those request on your side.

Of course there are other solution to those problem other than coding it on your own. There’s an extension for Firefox called Skipscreen that will automatically download the Rapidshare file for you or you con hire a better information provider that have built-in analysis functions on their site for you.

But if you are a control freak like me and want to make sure everything work exactly as what it is intended for then crawling through thousands of line of Skipscreen’s source code can be a bit of a pain (mind you that JavaScript is neither a strong typed or object oriented language so have fun visualizing variables and objects in your head). About the stock, can you be sure that your provider doesn’t tail gate your order? (e.g. You decide to buy some interesting stock and order them to do so, they find that stock interesting too and buy the same stock before actually place your order so they get the better price)

With Internet Explorer (e.g. the default browser control)

It’s simple actually, just create a browser control, use it’s Navigate(url) method for the page you want. For demonstration, I’ll try to get time from the text box on this page.

Here’s the code:

            HtmlElement IEElement1 = webBrowser1.Document.GetElementById("clockspot");
            if (IEElement1 != null)
            {
                textBox1.Text += "ie text 1: " + IEElement1.GetAttribute("value") + "rn";
            }
            HtmlElementCollection IEElements = webBrowser1.Document.GetElementsByTagName("input");
            if (IEElements.Count > 0)
            {
                foreach (HtmlElement IEElement in IEElements)
                {
                    if (IEElement.Name == "clockspot")
                        textBox1.Text += "ie text 2: " + IEElements[0].GetAttribute("value") + "rn";
                }
            }

This shows two ways you con search for a text box on the page. The first is by element ID. Actually, the textbox only have the name attribute but since this is IE and we have hideous rule all around, it will return the text box you want. The second way is to get all <input> elements on the Pago and check their name attribute.

Once you have found the textbox, get its “value” attribute. This value is updated in real time (the clock on the page is updated with JavaScript dynamically)

Side note: Running browser control in standard compliance mode

The first time you test the C# browser control  when you have IE8 installed may make you disappointed – it does not pass. This is because by default, the control run in quirks mode (this can break a lot of stuff). To force it to render the page in standard mode you’ll have to edit the registry, add this value:

[HKEY_CURRENT_USERSoftwareMicrosoftInternet ExplorerMainFeatureControlFEATURE_NATIVE_DOCUMENT_MODE]
"MyApplication.exe"=dword:13880

Replace MyApplication with your application’s executable name. Also if you are debugging in Visual Studio, add MyApplication.vshost.exe too.

With Firefox… or more exactly, XUL runner

XUL runner is the engine behind Firefox, it renders everything from web pages to Firefox itself. Being a cross platform platform (confusing isn’t it?), it will provide you with more cross-platform flexibility and may even save you from having to recompile the code. The is – if Mono is going somewhere.

XUL runner doesn’t work with C# out of the box, you’ll have to download Skybound’s  component – GeckoFX for that. This will give you a nice drag-and-drop component on the toolbox, but you are not done yet!

The basic component only provide basic functions like navigate, get/set for element text. To work with values contained in specific types of element you’ll need an experimental feature – DOM manipulation, which can be downloaded from here.

Add it to the original GeckoFX project, recompile it with the appropriate XUL version (1.8 or 1.9 or 1.9.1) and then you can use the code below:

            GeckoElementCollection FFElements = geckoWebBrowser1.Document.GetElementsByName("clockspot");
            if (FFElements.Count > 0)
            {
                GeckoElement Element = FFElements[0];
                GeckoInputElement Input = new GeckoInputElement(Element.DomObject);
                textBox1.Text += "ff text: " + Input.Value + "rn";
            }

The first step is similar to what you do with the IE control: search for the element. When it’s found, convert it to an input element (GeckoInputElement), then you can get its value. This is also updated in real time.

Conclusion

Due to time constraints, this post can only cover the very basic. You can explore other feature of the controls yourself – try replacing the “get” with “set” for example. Applications is endless. You can now make Rapidshare furious, dominate the stock market or use the internet itself for your data mining project!

Web application testing framework

This is an update section and is added on 10/07/2010

If you happened to use the .net Framework and wanted to test your web application’s behaviour in browsers, you have WaitN. Perhaps a bit of code from its first page will best illustrate what it does

[Test] 
public void SearchForWatiNOnGoogle()
{
 using (var browser = new IE("http://www.google.com"))
 {
  browser.TextField(Find.ByName("q")).TypeText("WatiN");
  browser.Button(Find.ByName("btnG")).Click();
  
  Assert.IsTrue(browser.ContainsText("WatiN"));
 }
}

Basic for Excel

* For the real action, jump straight to the “with VBA” section

It’s there a decade ago, it started off the wrong foot, generated some (bad) publicity and people started to wonder why it’s there in the first place. It’s quite surprising how people are ignoring it by now. It’s just sitting there in the corner of the file system, doing various sort of slavery work for people. Visual Basic for applications (VBA), imagine how we missed ya!

When I was a kid, I once thought that by choosing the path of a developer, I would sooner or later encounter it and do various sort of wondrous stuff with it, like some kind of smart worksheet that won’t let you type character data into numeric cells.

Sadly, that never happened. There are other ways to do that without VBA, or even without Excel since now I know, there’s Open Office and all. VBA’s role is even somewhat superseded by Visual Studio tools for Office/Applications.

Despite all that, VBA is still there, in the latest (official) version of Office for you… There’s no reason for it to wait any longer, right? This is intended to be a short (and simple) tutorial covering a fraction of what VBA can do for an average user, so you don’t have to be a total nerd to follow this.

Custom functions

Preface

Excel comes with a huge load of functions (hundreds or so) serving various purposes, but sometimes even that is not enough! Let’s say you want a function that returns the last working day before a certain date. Doing this week Excel’s function could be tedious because you need to handle three separate cases which will require two IFs:

– If the given date is Sunday, the last working date is last Friday (Two days backwards).

– If the given date is Monday, the last working date is also the last Friday (Three days backwards).

– For every other day of the week, it’s just the previous day

And also, WEEKDAY() returns a number from 1 to 7 so you have to remember which number corresponds to which day to make the IFs. The finished function should look like this:

=IF(WEEKDAY(C14)=2,C14-3,IF(WEEKDAY(C14)=1,C14-2,C14-1))

(C14 is the cell containing the original date)

Give that function to someone who haven’t read the two paragraphs above, it will take them at least 5 minutes to figure out what are you trying to do in that cell, and then you’ll waste yet another comment to explain that to them!

With VBA

You can program a new function to do the above.

1. From Excel press Alt+F11 or open the following menu/ribbon:

2. Select new module from the toolbar

3. Type in the following code in the new window

Public Function BusinessDayPrior(dt As Date) As Date
 
    Select Case Weekday(dt, vbMonday)
        Case 1
            BusinessDayPrior = dt - 3 'Monday becomes Friday
        Case 7
            BusinessDayPrior = dt - 2 'Sunday becomes Friday
        Case Else
            BusinessDayPrior = dt - 1 'All other days become previous day
    End Select
End Function

It will look like this

4. Click save, return to the main excel window, type a date you want and in C14 and =BusinessDayPrior(C14) (replace C14 with any cell of your choice), the result will look like this

Much clearer, and for an exercise, try to handle the case to suit your own specific need: tweak that function so it will handle holidays too:

– What if the given date is right after a one-day long holiday?

– What if the given date is right after a several days long holiday?

– What if you have compensated holidays (e.g. if the holiday is on weekends, you get another day off)

Automating tasks

Imagine this scenario: You are browsing through a list of stock quotes and you want to take note of profitable stocks on another sheet. If you are doing this manually you’ll have to copy the stock name, switch to the other sheet and paste it. With VBA you can have a button on the sheet that when you click, will do all three steps for you.

1. Open the visual basic editor with Alt + F11 and create a new module (see above on how to do it)

2. Add the following code to the window

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 14/10/2009 by SilentWind
'

    Dim Temp As Double
    Temp = ActiveCell.Value               'Get selected cell value
    Sheets("Sheet3").Select 
    For i = 1 To 65535                    'Scan the destination sheet…
        Range("A" + Trim(Str(i))).Select
        If (ActiveCell.Value = "") Then   '…for the first empty cell
            ActiveCell.FormulaR1C1 = Temp 'Copy it to the destination sheet
            Exit For
        End If
    Next i
End Sub

3. Draw a button on the sheet:

– In Excel 2007, go here

– In Excel 2003, right-click the toolbar, select customize, and drag the button onto the sheet

– In place of the button, you can even you an auto shape! Contrary to popular belief, it does not always require a button to do some useful task!

4. Right click the newly added button/shape and select Assign Macro…

5. Select Macro1 (the one you created at step 2)

Now, every time you select a stock name and click on the button/shape, it gets automatically added to the last empty cell in column A on sheet3.

In Excel 2003, you can drag the button onto the toolbar instead; the steps are still the same. In Excel 2007, you can only add that button to the Quick Access toolbar (well, unless you know how to use VSTO…):

– Right click the Quick Access toolbar and select Customize Quick Access Toolbar…

– Add your macro by selecting “Macros” from “Choose commands from”, select your macro and click add.

Conclusion

I think the two examples above are enough for casual user to understand and adapt for their daily use. Application is endless! Should there be any other questions, feel free to ask; I will add them to future posts if they are interesting enough.

You can download the demo workbook here.

Reference

Wikipedia’s article on this

Report @ localhost

I was developing an automated report maker. Its mission is to collect data from various sources (MSSQL, Excel and Access) to create a summary of the numbers. Things hit a few bumps along the way but finally after 2 days, the collecting phase (the one I thought was hard) is complete.

On to the “easy” part…

C# is such a great environment that they could have offered you with two report solution: the standard report control that comes with the framework and Business Object’s Crystal Report. I was looking for something that could accept parameters, use that for some SQL queries and display the result with a couple groupings.

Crystal report as overtly complex for the purpose, heh, and adding parameter to the query is not that easy as it depends on the project’s datasource.

The report control is even worse. I vividly recall from the last time I worked with such reports they have three tabs in the designer for me to edit data, layout and preview the result. But to my surprise, I can’t even find the preview command anywhere after I created a report in a Winform application. Not on the toolbar, not in the menu, nowhere to be found!

After a little twiddling around, I found another kind of project dedicated to report: the report wizard from the Business Intelligence studio (I happen to have SQL Server Express installed on the development machine). Exactly what I’m looking for! I just paste in the query, choose the groupings and voilà! Report to print! And they have three tabs…

I thought to myself it was all Microsoft stuff, and maybe the report viewer back from the winform project could view this, so I copied it over, point the control to the report, and execute it.

“Data source not found” is all I get.

It turned out that the world isn’t all pink like Microsoft thought (see the “just rename your file and it’ll work, we are Microsoft!” article). The business intelligence’s report is to be and only to be deployed on a report server, which is really not convenience for me since my version of SQL is Express and does not include the report server :(. From a winform application, the report viewer control will just ignore the queries you spent hours crafted in the .rdl report when you rename it to .rdlc (Because they name it report for different meanings on different tiers – A total solution on the server and a mere viewer on the client). They say you have to rebind the datasource. Easy for them to say when they aren’t binding a lot of queries together – by hand since Microsoft’s great data designer won’t allow you to create a new table with schema based on your queries, the situation is even worse for parameterized queries! =__=

Queries are naughty, so no rows for you!

I'm too lazy to parse that text (the same query run just fine from SSMS)

Folks on the ‘net have not been helpful this time. All they did was point back to the article, It’s surprising to see that something not impossible as viewing .rdl locally is impossible to do with what Microsoft allow you to use. (Yes, they allow you to preview the report locally just fine but they hid the damn report viewer!)

Who needs Microsoft when we have the Apache license? 😛

Somebody did it the hard way: they created a new parser and render the report from scratch. It have evolved for sometime: 0.5 from the like above to 4.0 latest. But still I does have limitations: some attributes are yet to be parsed and are ignored (for example, tables will not expand to accomodate the records) so the report may look a little off, but that could be fixed with a simple moving of parts. After all, this is open source software: if you wanted it to be better, dig in! 🙂

Anyways, it gets the job done. My reports are displaying expected numbers and passing parameters are painless. Cheers to the author!

RDL reports being viewed locally

Latest version of project RDL is available at FYIReporting’s homepage.

UPDATE: And don’t think you can just bind your data with the dataset designer, it won’t tell you anything when you choose the data set as the data source, but when you run it you get an ugly “not bind to an instance” error. In the end you’ll have to do things manually with Microsoft.
reportViewer1.LocalReport.DataSources.Clear();
reportViewer1.LocalReport.DataSources.Add(
                new ReportDataSource("Table name", new DataSetName.TableAdapter().GetData(Parameters))
);

Local browsing with CoolIris

UPDATE: You can simply use CoolIris 1.10 for the same purpsose

Full-Screen, 3D — Cooliris transforms your browser into a lightning fast, cinematic way to browse online photos and videos. Our “3D Wall” lets you fly through thousands of items in the blink of an eye on an infinitely expandable wall. To enjoy Cooliris on Google Images, Facebook, YouTube, Flickr, and hundreds of other sites, click the Cooliris icon that appears when you mouse over media on the supported site. Or enjoy Cooliris anytime by clicking the browser toolbar icon. See http://www.cooliris.com/product for details.

I was looking for an app that let me browse through my image gallery Apple style – PhotoFlip, but there seems to be none, unless I download Safari :-/. I don’t want a new browser right now, so I turned to the next best thing: CoolIris. Somehow I vividly recall this is a project started from Microsoft Research but I can’t find any trace of Microsoft there. Maybe the Cooliris team doesn’t like Microsoft in the end. 😛

Well, at least I expected it to be able to automatically detect links to picture on a page and then let me view them. It supported Flickr and Google Images off-screen loading, which is much more complicated than a list.

What a shame it doesn’t.

I’m not the only person looking for a “standalone CoolIris” to view my files, the CoolIris forum is filled with similar requests but the best response they can give is “wait” and an “CoolIris Lite” in which the effect is much simple and you seems to have to add files manually.

No problem, I can fix that.

I tried the “quick” option from the developer’s site but that turned out to be the more complicated option since I have to generate both the expression and the gallery for it to work, so I tried the full option: I created a C# application that will read files from the directory you want, write two files “media.htm” and “media.rss” to the output directory.

  • Media.htm contains one picture to activate the CoolIris icon on the toolbar, links to media.rss.
  • Media.rss is the feed that lists the files which will let CoolIris know the full list of images.

The core part of the application follows

            // Read jpg and jpeg from target directory
            string[] Files = Directory.GetFiles(textBox1.Text, "*.jp*");
            // Prepare the feed
            StreamWriter writer = new StreamWriter(textBox2.Text + "media.rss");
            writer.WriteLine("<?xml version="1.0" encoding="UTF-8" standalone="yes"?>");
            writer.WriteLine("<rss version="2.0" xmlns:media="http://search.yahoo.com/mrss" xmlns:atom="http://www.w3.org/2005/Atom">");
            writer.WriteLine("<channel>");
            writer.WriteLine("<title>Generated photos</title>");
	        writer.WriteLine("<link>file:///" + textBox2.Text.Replace("\","/") + "</link>");
            writer.WriteLine("<description>Photos from my trip to Africa.</description>");
            int Counter = 1;
            foreach (string FileName in Files)
            {
                FileInfo Item = new FileInfo(FileName);
                writer.WriteLine("<item>");
                writer.WriteLine("		<title>" + Item.Name + "</title>");
                writer.WriteLine("		<link>" + "file:///" + FileName.Replace("\","/") + "</link>");
                writer.WriteLine("		<guid>img " + Counter.ToString() + "</guid>");
                writer.WriteLine("		<media:description>" + Item.Name + "</media:description>");
                writer.WriteLine("		<media:thumbnail url="file:///" + FileName.Replace("\","/") + "" />");
                writer.WriteLine("		<media:content url="file:///" + FileName.Replace("\","/") + "" type="image/jpeg" />");
                writer.WriteLine("	</item>");
                writer.WriteLine("");
            }
            writer.WriteLine("</channel>");
            writer.WriteLine("</rss>");
            writer.Close();
            //Prepare the HTML file
            writer = new StreamWriter(textBox2.Text + "media.htm");
            writer.WriteLine("<html>");
            writer.WriteLine("  <head>");
            writer.WriteLine("    <link rel="alternate" href="media.rss" type="application/rss+xml" title="" id="gallery" />");
            writer.WriteLine("  </head>");
            writer.WriteLine("  <body>");
            foreach (string FileName in Files)
            {
                FileInfo Item = new FileInfo(FileName);
                writer.WriteLine("<a href="" + "file:///" + FileName.Replace("\", "/") + "">");
                writer.WriteLine("<img alt="[Image]" src="" + "file:///" + FileName.Replace("\", "/") + "" class="photo">");
                writer.WriteLine("</a>");
                break;
            }
            writer.WriteLine("  </body>");
            writer.WriteLine("<html>");
            writer.Close();
            //Open the HTML file with the default browser, hopefully it's the one with CoolIris installed
            System.Diagnostics.Process.Start(textBox2.Text + "media.htm");

Application interface

Fill in the directory containing the pictures you want to view in the first text box, the second could be your temp directory. Click generate to generate the files mentioned above and open them in your default browser, click the CoolIris to activate CoolIris (this extra step is required because I don’t know how to call CoolIris on a file directly; the “Launch CoolIris” application seems to open Cooliris.com only).

I also made a command line interface with which you can call the app with 2 parameters. The first will go to “Directory” and the second to “Output”. If you run the app this way it will generate, open the file and then close itself.


Result

Download the application

Requirements:

  • .NET Framework 2.0
  • CoolIris enabled browser

Tumblelogs

Tumblelog is a term coined in 2005[1] to denote blogs which favor short posts, most of the time sharing only a single item. I encountered the first such blog on 2007[2] but it didn’t really impressed me. Not until last night, when I was shown a comic translation blog [3]. Tumblelogs are just great for the purpose! It allowed instant sharing of daily comic (one thing jumps right in my thoughts: there is a bookmarklet somewhere) without any comments, category or other hassles. On the other hand, perhaps the comic blog above is too simple it don’t tell us where the comics come from, nor mention Garfield’s author (so I’m not condoning it at all, just an example).

It’s also worth a note to distinguish between tumblelog and microblog. While microblog is primarily aimed at status updates, it also allows you to post multimedia content like tumblelog. But your status could change anytime, anywhere, so you’ll need an equivalent update convenience (e.g. mobile updates). On the other hand, tumblelogs are designed to share content, and as you may encounter most content while surfing, think bookmarklet and email-to-post. You can see the gap between two services is not big, and as this article is being written, more features are being stuffed to twitter (that’s why its interface has turned in to a big bunch of text instead of one line of status :P)

Okay, too much ranting again. I only wanted a short review about current tumblelog services when I started writing!

Tumblr

Not the first Tumblelog software, but the first one is currently down so I can’t test it :P.

Tumblr seems to be the most popular tumblelog service around. Clean interface, but there are only a few themes available. None looked good enough for me :/. The theme installation from theme garden worked erratic somehow. For the functionality, Tumblr is pretty good, it pulls the page to its server after you add it with the bookmarklet; analyze the page to find contents you may want to blog about on the page.

Tumblr could be linked with twitter so that all twitter updates will be copied to your Tumblr and vice versa. Also, you can import feeds, bookmark from social bookmarks and posts from other blogging services (thus Tumblr could also act as a bridge to let your twitter followers know when you write a post or bookmark something).

Media could be uploaded to Tumblr but there is a 10MB file size limit (I’m unsure what the storage limit is, see quote below). If you want to post bigger stuff, you are on your own.

Tumblr retains the right to create limits on use and storage in its sole discretion at any time with or without notice. [Terms of service]

The bookmarklet is in the “Goodies” tab; there you’ll also find your email-to-post address, iPhone app and third party apps. The dashboard interface is a little clumsy, functions are distributed on tabs, sidebar, and a menu; but that doesn’t seem to limit Tumblr’s popularity.

You can customize the theme with HTML and CSS. You can also attach your Tumblr to your domain as long as you are able to modify A records (it took a really long time to propagate through the nameservers so you may be unable to view your blog for one or two days if you use this). There are APIs available, but I haven’t looked to see what they can do (yet).

Soup.io

Quite a competitor for Tumblr, most of the features are the same. The interface has more web 2.0 slides and fades, but the page still refreshes to update itself so I’m still not completely satisfied with this service. It allows you to import from more services (say furl or weheartit) but its bookmarklet isn’t as good. It will only detect media on popular sites; it does not analyze the page so it’s very likely that you’ll have to prepare a link to the media first.

The basic theme structure seems to be fixed. However you are allowed to theme it with CSS

Gelato

Seeing Tumblelogs in action somewhat annoy me why such a small amount of content take a whole page load? Isn’t some AJAX to flip pages are better? Then I went to the usual developer’s craze wanting to develop a whole new service that satisfies my every need, since the above are hosted by the blog provider. And as paranoid as a user may be, leaving data on a stranger’s server just doesn’t bring a nice feeling :P.

Fortunately, thank to the free culture (as in free beer :P), someone must have done the job for you! In this case it’s Pedro Santana: from Gelato’s homepage you can download the source (latest at the time of writing: 0.95).

Pretty close clone of Tumblr. To the average user Gelato only provides basic features. You can blog, change the theme, use the bookmarklet but that’s all. The advantage is you have full control over it. Its template share similarities with WordPress.

Still no AJAX for page transition, which I’m pretty disappointed since it advertised itself “built upon AJAX”. But I don’t think that would be a problem though, you have the source of a working Tumblelog!