This is a lengthy post for something I think is really worthwhile. If you’re into code exploration read on…

Last week I got frustrated while looking for a simple app that could alert me from time to time that I should get up and move/take a break instead of sitting during work hours for more than 1 | 2 | 3 | 4 | ? hours. You know…software people tend to pass maybe 1/4 of their lives stationary in a chair. That’s too much time if we think about it.

Here’s my tweet:

Got tired of looking for an #Alarm app for #Mac and decided to go with #Windows 8 Alarms app. The only problem is the lack of Hourly option.

— Leniel Macaferi (@johnleniel) April 4, 2014

Yes, Mac OS has an option to announce the time on Date & Time preferences (Clock tab). A robot voice can speak the time on the quarter hour, on the half hour and on the hour but it’s very limiting (lack configuration options). It wasn’t what I was looking for…

I then tried Mac OS Calendar app with its built-in alerts but it’s not the right tool for the job.

So I thought, let me go to Windows 8 side. I use Parallels Desktop 9 for Mac and so both operating systems live side by side in perfect harmony. With a Window 8 Parallels Virtual Machine I can switch from Mac OS Mavericks to Windows 8 and vice-versa with a 4 finger swipe gesture in the MacBook magic trackpad. This is real good. If you don’t know Parallels Desktop for Mac I highly recommend you go take a look at it.

On the Windows 8 side I just stumbled on the Alarms metro app and I really liked it.

Figure 1 - Nice looking/modern and to the point Windows 8 Alarms metro app

It has a pretty beautiful and well thought/modern UI and the Win 8 “toast” alert notification is really cool and guess what: the notifications raised in Windows also show as Mac OS Notifications¹ thanks to Parallels for Mac integration. Besides that, the notifications are shown just while you’re working/while the PC is awake. This is really what I was looking for…

Using the Alarms app you can configure the alarm repeat frequency and select a sound (the available sounds really nice too). One downside is that as of now (Windows 8.1) it doesn’t have an option to set an hourly alarm. I suggested it to the Win team. Lacking the hourly option I started creating alarms like crazy let’s say from 30 in 30 minutes just to find another limitation: the Alarms app allows only 20 alarms. I tried to cheat it looking at the Alarms.json file used to persist the alarms data. The file is saved here:

C:\Users\Leniel\AppData\Local\Packages\Microsoft.WindowsAlarms_8wekyb3d8bbwe\
LocalState\Alarms\Alarms.json

but adding a new alarm by hand in this JSON file had no effect when reopening the Alarms app. I think the app code limits the alarms when parsing this file.

Of course I could set the alarms with a minor frequency like from 1 to 1 hour (what I did and that works beautifully). As a coder and explorer I started looking for some code that would allow me to overcome the manual alarms creation/configuration and the 20 alarms limit – read Automate the whole process.

I found Alarm toast notifications sample on Windows Dev Center. With the code in hands I could play with it and understand how Windows 8 does those pretty looking “toast” notifications. This was also a great opportunity to learn a little bit about Win 8 app development since I had never played with it. I’m more a web guy doing lots of ASP.NET webstack of love development these days.

I got the .zip file with C# code and fired our dear Visual Studio 2013. I was prompted to get a developer certificate and then the solution loaded without a glitch and compiled just fine. Went through the code and what I can say is that: win development (WPF/XAML) and desktop dev in general is really not as cool as doing web ASP.NET dev – this is just my humble opinion though. Lots and lots of code just to manage guess what WINDOWS. XAML designer is a heavy weight guy and takes seconds to load even on a powerful machine. Despite this, the code is easy to understand if you’ve done something as Win Forms dev.

The sample app has basically a window with 2 buttons. When clicking the first button it dispatches a notification toast to Win 8. The are plenty of options regarding the toast alert notification but I’m only interested in simulating the click of the Default Snooze button.

Figure 2 - Alarm Notifications C# Sample app main window

I started with this idea: I want to get this app notification fired with Windows Task Scheduler. Task Scheduler is such a powerful tool in Windows and allows for a great range of configuration.

To that end I created a Task that calls the win 8 sample app from time to time, say from 1 in 1 hour (just like my original alarms idea) or from 50 in 50 minutes or any interval desired.

First attempt to see if/how this could work was: right-clicked the AlarmNotifications project in Visual Studio and selected Store => Create App Packages and then Visual Studio built a local package that got saved to

C:\temp\Alarm toast notifications sample\C#\AppPackages

I promptly installed it running the PowerShell script provided - it’s called Add-AppDevPackage.ps1.

Figure 3 - Installing the App package with PowerShell script

After installation the app called Alarm Notifications C# could be opened in Win 8 Apps. Worked perfectly.

Note: One thing I’ve learnt is that there’s no need to package and install the app manually. You can just deploy it using Visual Studio’s green/play button to Run in Local Machine. This is way better because doing it from inside Visual Studio you can change the app code and redeploy immediately with the click of that button.

Now the question is how to open this app using Task Scheduler? This is a good question!

First blocker: Win 8 metro apps cannot be opened directly. Win 8 apps are installed in this folder:

C:\Program Files\WindowsApps

I then opened the sample app’s folder

Microsoft.SDKSamples.AlarmNotification.CS_1.0.0.1_neutral__8wekyb3d8bbwe

and looked for the AlarmNotifications.exe thinking I could use this simple command

Start AlarmNotifications.exe

as the Start Program action in Task Scheduler task.

If you double-click AlarmNotifications.exe directly you’ll get this error message:

This application can only run in the context of an app container.

Oh oh… this is bad. Sure. So how can the app be opened with Task Scheduler help? Aha… 2nd good question. Googling for that and there’s a way around it called Protocol Activation. Basically you register a protocol in the project’s Package.appxmanifest file in Visual Studio like this:

Figure 4 - Defining the app Protocol that will be used as the Start Program argument in Task Scheduler

The protocol I registered for the sample app was toast. Doing this now I’m able to launch the app using Task Scheduler with this command:

Explorer toast:

This is cool man! We just overcame Win 8 metro apps .exe limitation.

Now that a protocol was defined for the app, a small piece of code is necessary and goes into the App.xaml.cs code behind:

protected override void OnActivated(IActivatedEventArgs args)
{
    if(args.Kind == ActivationKind.Protocol)
    {
        ProtocolActivatedEventArgs protocolArgs = args as ProtocolActivatedEventArgs;
                
        var rootFrame = new Frame();
        rootFrame.Navigate(typeof(MainPage), args);
                
        Window.Current.Content = rootFrame;
    }

    Window.Current.Activate();
}

This code watches over how the app was activated… in this specific case it checks for the Protocol activation method.

With this in place we can pass the ball to Task Scheduler.

Here’s the Task Scheduler task action:

Figure 5 - Task Scheduler Task’s Action explorer toast:

The trigger:

Figure 6 - Task Scheduler Task’s Trigger At Startup and repeat indefinitely every 1 minute

Note that for testing I set the task to repeat every 1 minute. This is the part you should change let’s say from 1 to 1 hour.

I exported this task’s XML and put it in this repo at GitHub. If you wish you can import this task in your Windows Task Scheduler.

You can play with the task’s schedule the way you want. There are plenty of configuration options available.

OK. Now the foundation is laid but there’s yet one blocker to overcome. The sample app has a window called MainPage and this window has 2 buttons as seen in Figure 2. The alarm notification will only be triggered when clicking the Default Snooze button in that window. So no matter if Task Scheduler opens the app, the alarm notification will never appear. What I’m doing is an automated process and so somehow that button must be clicked in the code. What to do in this case? It’s simple: simulate the button click programmatically. Let me Google for it. The answer is here:

Windows 8 Button click programmatically in C# and XAML

The next thing to do is to find in the sample app’s code where that code should go. I put it in the Toast.xaml.cs code behind file in the ToastScenario constructor:

public ToastScenario()
{
    this.InitializeComponent();

    // Let's call the XAML button click programmatically
    ButtonAutomationPeer peer = new ButtonAutomationPeer(DefaultSnooze);
           
    IInvokeProvider invokeProv = peer.GetPattern(PatternInterface.Invoke) as IInvokeProvider;
    invokeProv.Invoke();
}

This did the trick!

I also changed the click even handler a little bit:

private void SendToast_Click(object sender, RoutedEventArgs e)
{
    Button b = sender as Button;

    if(b != null)
    {
        // Get some data from the button the user clicked on.
        string toastTemplate = b.Name;
        string alarmName = "";

        if(toastTemplate.Contains("Custom"))
        {
            alarmName = "This will make you fell better. Snooze me if you want...";
        }
        else
        {
            alarmName = "This will make you fell better. Snooze me if you want...";
        }

        // Create the toast content by direct string manipulation.
        // See the Toasts SDK Sample for other ways of generating toasts.
        string toastXmlString =
            "<toast duration=\"long\">\n" +
                "<visual>\n" +
                    "<binding template=\"ToastText02\">\n" +
                        "<text id=\"1\">Get Up and Move coder!</text>\n" +
                        "<text id=\"2\">" + alarmName + "</text>\n" +
                    "</binding>\n" +
                "</visual>\n" +
                "<commands scenario=\"alarm\">\n" +
                    "<command id=\"snooze\"/>\n" +
                    "<command id=\"dismiss\"/>\n" +
                "</commands>\n" +
                "<audio src=\"ms-winsoundevent:Notification.Looping.Alarm2\" loop=\"true\" />\n" +
            "</toast>\n";

        // Display the generated XML for demonstration purposes.
        rootPage.NotifyUser(toastXmlString, NotifyType.StatusMessage);

        // Create an XML document from the XML.
        var toastXML = new XmlDocument();
        toastXML.LoadXml(toastXmlString);

        // Create the toast and attach event listeners
        ToastNotification toast = new ToastNotification(toastXML);
        //toast.Activated += ToastActivated;
        //toast.Dismissed += ToastDismissed;
        //toast.Failed += ToastFailed;

        // Show the toast. Be sure to specify the AppUserModelId on your application's shortcut!
        ToastNotificationManager.CreateToastNotifier().Show(toast);

        // Exit the app after dispatching the toast
        Application.Current.Exit();
    }
}

It dispatches the notification immediately and closes the Application after that.

Once the toast notification appears one must take action: click the small X icon or click dismiss to make the notification go away. Of course after doing this you should actually Get Up and Move/exercise for some time. Why not go get some water or even coffee?

Figure 7 - Customized Windows 8 Toast Notification

Here’s a YouTube video where I demo the app running with the help of Task Scheduler:

You can see the toast notification appear right after I run the Task Scheduler task. See how beautiful it is an how catching is the sound. By the way, the sound can be changed too editing the <audio> tag in the toast XML template in Toast.xaml.cs:

string toastXmlString =
    "<toast duration=\"long\">\n" +
        "<visual>\n" +
            "<binding template=\"ToastText02\">\n" +
                "<text id=\"1\">Get Up and Move coder!</text>\n" +
                "<text id=\"2\">" + alarmName + "</text>\n" +
            "</binding>\n" +
        "</visual>\n" +
        "<commands scenario=\"alarm\">\n" +
            "<command id=\"snooze\"/>\n" +
            "<command id=\"dismiss\"/>\n" +
        "</commands>\n" +
        "<audio src=\"ms-winsoundevent:Notification.Looping.Alarm2\" loop=\"true\" />\n" +
    "</toast>\n";

One interesting thing is that If the toast is not closed/dismissed the sound gets louder and louder…

Let’s get up and move because back pain is back pain and sitting all day long can put you in trouble.

Hope you liked this code exploration.

¹ to get the alert notification on the Mac OS side, the modified sample app must be opened and present in the Mac OS dock; otherwise the notification can only be seen in the Windows side. This is a Parallels for Mac limitation and I hope in the future they also make it possible to show notifications from windows metro apps that are running in the background. The nice thing is that the alarm sound still plays while on the Mac OS side.

Get the codez
The modified source code is available at this GitHub repo:

https://github.com/leniel/Windows8Toast

References
Alarm toast notifications sample at Windows Dev Center

Toast notification overview (Windows Runtime apps)

31 DAYS OF WINDOWS 8 | DAY #10: TOAST NOTIFICATIONS

Windows 8.1 Preview and ‘Alarm’/’Call’ Applications

This is the 3rd post of a series of posts about NPOI 2.0.

This time we’re going to see how easy it’s to copy cells, rows and even sheets. As a bonus you also get to see the process for workbook merging/combining.

There’s nothing much to say so we’re going directly distill some “codez” in front of you…

Copying Cells and Rows
Here’s what it gets to copy Cells and Rows:

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;

namespace CopyRowsAndCellsInXls
{
    class Program
    {
        static HSSFWorkbook hssfWorkbook;

        static void Main(string[] args)
        {
            InitializeWorkbook();

            ISheet sheet = hssfWorkbook.GetSheetAt(0);

            ICell cell = sheet.GetRow(4).GetCell(1);
            cell.CopyCellTo(3); // Copy B5 to D5
            
            IRow row = sheet.GetRow(3);
            row.CopyCell(0, 1); // Copy A4 to B4

            sheet.CopyRow(0,1); // Copy row A to row B; row B will be moved to row C automatically

            WriteToFile();
        }

        static void WriteToFile()
        {
            //Write the workbook’s data stream to the root directory
            FileStream file = new FileStream(@"test.xls", FileMode.Create);

            hssfWorkbook.Write(file);

            file.Close();
        }

        static void InitializeWorkbook()
        {
            using (var fs = File.OpenRead(@"Data\test.xls"))
            { 
                hssfWorkbook = new HSSFWorkbook(fs);
            }
        }
    }
}

The code above was taken from CopyRowsAndCellsInXls sample project.

Copying Sheets
Here’s what it gets to copy Sheets:

using NPOI.HSSF.UserModel;
using System;
using System.IO;
using System.Windows.Forms;

namespace CopySheet
{
    class Program
    {
        [STAThread]
        static void Main(string[] args)
        {
            // Excel worksheet merge/combine sample
            // You will be prompted to select two Excel files/workbooks. test.xls will be created that combines/merges the sheets from those two workbooks.
            // Note: This example does not check for duplicate sheet names. Your test files must have different sheet names.
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel document (*.xls)|*.xlsx";
            ofd.Title = "Select first Excel document";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                HSSFWorkbook book1 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));

                ofd.Title = "Select second Excel document";

                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    HSSFWorkbook book2 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));

                    HSSFWorkbook merged = new HSSFWorkbook();

                    for (int i = 0; i < book1.NumberOfSheets; i++)
                    {
                        HSSFSheet sheet1 = book1.GetSheetAt(i) as HSSFSheet;

                        sheet1.CopyTo(merged, sheet1.SheetName, true, true);
                    }
                    for (int j = 0; j < book2.NumberOfSheets; j++)
                    {
                        HSSFSheet sheet2 = book2.GetSheetAt(j) as HSSFSheet;

                        sheet2.CopyTo(merged, sheet2.SheetName, true, true);
                    }

                    merged.Write(new FileStream("merged.xls", FileMode.Create, FileAccess.ReadWrite));
                }                
            }
        }        
    }
}

The code above was taken from CopySheet sample project.

To go deeper and explore all the features available for Excel 2007 you can check a handful of sample projects here (as of now 24 sample projects showcasing many available features):
https://github.com/tonyqus/npoi/tree/master/examples/xssf

Open the file NPOI.XSSF.Examples.2010.sln to have all them show up in Visual Studio’s Solution Explorer.

Today I was working with a database using LocalDB server and I had set this specific database named CBA as the default one for my user LENIEL-PC\Leniel. Setting a default database for the user is useful because when you login using SQL Server Management Studio [ SSMS ] you go directly to that database, that is, when running queries that’s the database used and not the master one. This is life saver.

I wanted to restore a backup file .bak I got from the production server using the Restore Database… command in SSMS. This is to test locally but first I had to drop my local database copy. I did drop my local copy while I was logged in to that database. I Selected the database in Object Explorer, right clicked it and selected Delete. Then checked the Close existing connections (bottom of the window), clicked OK and then the deletion succeeded. Pretty good… no.

What happened? The next time I tried to login again in SSMS this was what I got:

Figure 1 - SSMS | Cannot connect to (localdb)\v11.0.
Cannot open database requested by login. The login failed.
Login failed for user LENIEL-PC\Leniel (Microsoft SQL Server, Error: 4060)

Pretty obvious! I deleted the default database I had set to my user LENIEL-PC\Leniel. Now what? How can I login to the database server and change the default database? Good question…

This “simple” error was such a pain to fix…I tried lots of things like using SqlLocalDB.exe utility to stop and delete that specific LocalDB instance named v11.0. I would create it again afterwards if deleting it worked at least. This was the message logged in Windows Event logs when I tried to delete the instance:

Windows API call LogSqlDiagRec returned error code: 0. Windows system error message is: The operation completed successfully.
Reported at line: 3791. [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "CBA" requested by the login. The login failed.

As you see the problem was the same. It was barking about the deleted database. Damn it!

I also tried the login with the sa account but guess what… the sa account was disabled for that LocalDB instance. Even if it was enabled I would not be able to login because I didn’t know the password for sa.

Googling more I managed to find this post by Pinal Dave:

SQL SERVER – Fix : Error: 4064 – Cannot open user default database. Login failed. Login failed for user

So guess what: the solution is ridiculously easy once you know what to do of course…

Click that Options >> button in Figure 1. Now select the Connection Properties tab.

Figure 2 - SSMS Connect to Server | Connection Properties | Connect to database option

I had to type master in Connect to database field since I did not have it in the list of available databases.

Now click connect and you’re done.

Great. Thanks God I was able to login and access the database server.

What’s left?

Reset my user Default database to master so that I can login again directly. By the way, the field Default database was empty since I had dropped the database!

Doing so there’s no need to set the Connect to database option anymore.

Figure 3 - SSMS | Setting Default database for user

Sometimes things are so easy but they are hidden somewhere we just can’t figure it out at first.

Hope it helps.

This is the 2nd post of a series of posts about NPOI 2.0.

NPOI is jumping from version 1.2.5 directly to 2.0. A big reason for this is that it now supports Office 2007 formats (.xlsx and .docx). Given this fact, using NPOI 2.0 will seem a little bit more complicated than the previous version because NPOI now provides multiple namespaces, including HSSF (Excel 2003), XSSF (Excel 2007) and XWPF (Word 2007).

Don’t freak out! We’re here to help you understand how to work with all those namespaces.

Excel 2007 support
Here’s a simple sample for Excel 2007…

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;

namespace NPOI.Examples.XSSF.SetCellValuesInXlsx
{
    class Program
    {
        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            
            ISheet sheet1 = workbook.CreateSheet("Sheet1");

            sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
            
            int x = 1;
            
            for (int i = 1; i <= 15; i++)
            {
                IRow row = sheet1.CreateRow(i);
                
                for (int j = 0; j < 15; j++)
                {
                    row.CreateCell(j).SetCellValue(x++);
                }
            }

            FileStream sw = File.Create("test.xlsx");

            workbook.Write(sw);
            
            sw.Close();
        }
    }
}

To go deeper and explore all the features available for Excel 2007 you can check a handful of sample projects here (as of now 24 sample projects showcasing many available features):

https://github.com/tonyqus/npoi/tree/master/examples/xssf

Open the file NPOI.XSSF.Examples.2010.sln to have all them show up in Visual Studio’s Solution Explorer.

 Figure 1 - Excel 2007 (NPOI.XSSF) sample projects list

Word 2007 support
Here’s a simple document example for Word 2007…

using NPOI.XWPF.UserModel;
using System.IO;

namespace SimpleDocument
{
    class Program
    {
        static void Main(string[] args)
        {
            XWPFDocument doc = new XWPFDocument();

            XWPFParagraph p1 = doc.CreateParagraph();
            p1.SetAlignment(ParagraphAlignment.CENTER);
            p1.SetBorderBottom(Borders.DOUBLE);
            p1.SetBorderTop(Borders.DOUBLE);

            p1.SetBorderRight(Borders.DOUBLE);
            p1.SetBorderLeft(Borders.DOUBLE);
            p1.SetBorderBetween(Borders.SINGLE);

            p1.SetVerticalAlignment(TextAlignment.TOP);

            XWPFRun r1 = p1.CreateRun();
            r1.SetBold(true);
            r1.SetText("The quick brown fox");
            r1.SetBold(true);
            r1.SetFontFamily("Courier");
            r1.SetUnderline(UnderlinePatterns.DotDotDash);
            r1.SetTextPosition(100);

            XWPFParagraph p2 = doc.CreateParagraph();
            p2.SetAlignment(ParagraphAlignment.RIGHT);

            //BORDERS
            p2.SetBorderBottom(Borders.DOUBLE);
            p2.SetBorderTop(Borders.DOUBLE);
            p2.SetBorderRight(Borders.DOUBLE);
            p2.SetBorderLeft(Borders.DOUBLE);
            p2.SetBorderBetween(Borders.SINGLE);

            XWPFRun r2 = p2.CreateRun();
            r2.SetText("jumped over the lazy dog");
            r2.SetStrike(true);
            r2.SetFontSize(20);

            XWPFRun r3 = p2.CreateRun();
            r3.SetText("and went away");
            r3.SetStrike(true);
            r3.SetFontSize(20);
            r3.SetSubscript(VerticalAlign.SUPERSCRIPT);


            XWPFParagraph p3 = doc.CreateParagraph();
            p3.SetWordWrap(true);
            p3.SetPageBreak(true);

            //p3.SetAlignment(ParagraphAlignment.DISTRIBUTE);
            p3.SetAlignment(ParagraphAlignment.BOTH);
            p3.SetSpacingLineRule(LineSpacingRule.EXACT);

            p3.SetSetIndentationFirstLine(600);


            XWPFRun r4 = p3.CreateRun();
            r4.SetTextPosition(20);
            r4.SetText("To be, or not to be: that is the question: "
                    + "Whether 'tis nobler in the mind to suffer "
                    + "The slings and arrows of outrageous fortune, "
                    + "Or to take arms against a sea of troubles, "
                    + "And by opposing end them? To die: to sleep; ");
            r4.AddBreak(BreakType.PAGE);
            r4.SetText("No more; and by a sleep to say we end "
                    + "The heart-ache and the thousand natural shocks "
                    + "That flesh is heir to, 'tis a consummation "
                    + "Devoutly to be wish'd. To die, to sleep; "
                    + "To sleep: perchance to dream: ay, there's the rub; "
                    + ".......");
            r4.SetItalic(true);
            //This would imply that this break shall be treated as a simple line break, and break the line after that word:

            XWPFRun r5 = p3.CreateRun();
            r5.SetTextPosition(-10);
            r5.SetText("For in that sleep of death what dreams may come");
            r5.AddCarriageReturn();
            r5.SetText("When we have shuffled off this mortal coil,"
                    + "Must give us pause: there's the respect"
                    + "That makes calamity of so long life;");
            r5.AddBreak();
            r5.SetText("For who would bear the whips and scorns of time,"
                    + "The oppressor's wrong, the proud man's contumely,");

            r5.AddBreak(BreakClear.ALL);
            r5.SetText("The pangs of despised love, the law's delay,"
                    + "The insolence of office and the spurns" + ".......");

            FileStream out1 = new FileStream("simple.docx", FileMode.Create);
            doc.Write(out1);
            out1.Close();
        }
    }
}

To go deeper an explore all features available for Word 2007 you can check a handful of sample projects here (as of now 5 sample projects showcasing some available features):

https://github.com/tonyqus/npoi/tree/master/examples/xwpf

Open the file NPOI.XWPF.Examples.2010.sln to have all them show up in Visual Studio’s Solution Explorer.

Figure 2 - Word 2007 (NPOI.XWPF) sample projects list

This is the 1st post of a series of posts about NPOI 2.0.

NPOI is jumping from version 1.2.5 directly to 2.0. A big reason for this is that it now supports Office 2007 formats (.xlsx and .docx). Given this fact, using NPOI 2.0 will seem a little bit more complicated than the previous version because NPOI now provides multiple namespaces, including HSSF (Excel 2003), XSSF (Excel 2007) and XWPF (Word 2007).

To be able to automatically identify Excel formats when reading a file from the file system and to avoid you having to infer the file type yourself, NPOI provides a very convenient class NPOI.SS.WorkbookFactory.

public class WorkbookFactory
{
    public static WorkbookFactory Create(POIFSFileSystem fs){ ... }
    public static WorkbookFactory Create(OPCPackage pkg){ ... }
    public static IWorkbook Create(Stream inputStream){ ... }
    public static IFormulaEvaluator CreateFormulaEvaluator(IWorkbook workbook){ ... }
}

The difference between the first two Create methods introduced under POIFSFileSystem and OPCPackage is that POIFSFileSystem reader library uses OLE2 format and OPCPackage uses OOXML format (commonly known as ActiveX Document Format). These two libraries are used to read both Excel 2003 (.xls) and Excel 2007 (.xlsx) formats respectively and because they’re the underlying libraries, they’re not limited to reading .xls and .xlsx formats. You can also read Thumb.db using POIFSFileSystem file format for example. You can download a sample project here.

Since you already know the difference between POIFSFileSystem and OPCPackage, you should understand what these two methods do. You are required to know what kind of document you are opening and then pass the file system to WorkbookFactory that in turn can automatically store a HSSFWorkbook or XSSFWorkbook depending on the file type. As these two classes implement IWorkbook interface, in most cases you do not need to care about what class instance it returns unless you use some advanced or specific Excel 2007 feature.

The third one is the most important part to introduce today – it does automatic inference, as long as you pass a Stream object it will know/infer whether it's .xls or .xlsx. Finally it returns the appropriate workbook instance.

The last one returns an IFormulaEvaluator that describes a formula object. Here a similar inference principle applies. Both HSSF and XSSF have formula calculation/evaluation classes, namely HSSFFormulaEvaluator and XSSFFormulaEvaluator.

OK we covered a little bit of code above but it was only abstractions, let’s see some real code:

using System;
using NPOI.SS.UserModel;
using System.IO;

class Program
{
    static void Main(string[] args)
    {
        if (args.Length < 1)
        {
            Console.WriteLine("missing argument: Excel file path (both 2003 and 2007 are supported)");

            return;
        }

        using (FileStream fs = File.OpenRead(args[0]))
        {
            IWorkbook wb = WorkbookFactory.Create(fs);

            Console.WriteLine("Value of Cell B2: " + wb.GetSheetAt(0).GetRow(1).GetCell(1));
        }

        Console.Read();
    }
}

This program assumes that the workbook file with a sheet exists on the file system and that the first sheet has a value in cell B2 or an error will be raised. The following is a screenshot of the workbook in question.

Figure 1 - Excel Workbook with a single Sheet and value 5 in Cell B2

Command line statement and test parameters were as follows:

Excel 2007: WorkbookFactoryDemo.exe demo2007.xlsx

Excel 2003: WorkbookFactoryDemo.exe demo2003.xls

The result should be equal the value of cell B2 = 5.

If we analyze the above program it seems we are not accessing anything from XSSF and HSSF namespaces, but in fact the wb is certainly an instance of HSSFWorkbook or XSSFWorkbook, that is, the real instance type/format is transparent to the user. This is where the IWorkbook interface shines.

For now you can study the source code here. Many NPOI 2.0 new features are not documented yet. So keep an eye on this blog for the next installment on this series.

What’s NPOI you may ask. So here it goes:

NPOI is the .NET version of POI Java project. POI is an open source project which can help you read/write Office 2003/2007 files.

NPOI advantages
a. It's totally free to use
b. Cover most Excel features (cell styles, data formats, formulas and so on)
c. Supports .xls, .xlsx, .docx
d. Designed to be interface-oriented (in NPOI.SS namespace)
e. Supports not only export but also import
f. .NET 2.0 based even for xlsx and docx (but it also supports .NET 4.0)
g. Successful use cases all over the world
h. Great amount of basic and to the point samples
i. Professional service support (even 24*7) directly from NPOI team (not free)

My dear friend Tony Qu from China (the guy behind NPOI development) approached me and asked me to help him share NPOI 2.0 news with the English speakers. I’m always glad to help spread the word about this amazing open source project that has served me in many situations.

The most seen and shared post of this humble blog as of now has almost 9000 shares and was published on 7/02/2009 – oh God that’s exactly 4.5 years ago… its title is Creating Excel spreadsheets .XLS and .XLSX in C#. This huge amount of shares shows that a tool that does what NPOI does is highly demanded in the market. No doubt!

You know: when I wrote that post I had no idea it would be so useful to so many people. My intention with this blog is really to share useful things with the world giving back a bit of what I find/learn/improve from the great minds spread throughout this big planet earth. This is a continuous\infinite process for sure.

OK dude: that’s enough of food for thought. Let me show you what’s going on with NPOI. Here you go…

NPOI 2.0 release
The release is planned for this January. We agreed to publish posts discussing major features/enhancements that’s arriving.

Here they are:

1 - WorkbookFactory can help make HSSF and XSSF transparent for users

2 - Excel 2007 and Word 2007 support

3 - Cell, Row, Sheet copying and Workbook merging

4 - Converting Excel XLS documents to HTML format

5 - isRightToLeft and setRightToLeft in XSSF and HSSF

6 - Diagonal line in cells for both Excel 2003 and Excel 2007

7 - Excel 2007 chart support

8 - New excel functions added in formula calculation

9 - NPOI tag added to files generated with NPOI

10 - XSSFWorkbook implements IEnumerator

Keep an eye on this blog for the next posts.

NPOI source code 
https://github.com/tonyqus/npoi

NPOI NuGet package
https://www.nuget.org/packages/NPOI/2.0.6

While testing an app I came across an error: I passed a string parameter to an action method responsible for loading an existing object by name on the database side. The problem is that there was no such object on the database. To make things even more difficult to handle this action method returns a PartialView that gets loaded in the view side using jQuery $.ajax. So how and what should I return to the caller if an exception happens while processing the request inside that action method? That’s a good question.

I tried something like this:

public ActionResult SubCatList(string subcat)
{
    var sub = Database.GetLogSubCategory(subcat);

    if(sub == null)
    {
        throw new EntryNotFoundException(String.Format("SubCategory {0} does not exist", subcat));
    }

    var subcatItems = Database.GetListItemsForSubCategory(sub);

    return PartialView(subcatItems);
}

My intention was to get that beautiful formatted exception message inside $.ajax fail callback using the error parameter like this:

$.ajax({
        url: '/api/subcatlist/subcatlist?subcat=' + subcat,
        type: 'GET',
        cache: false,
        success: function(html)
        {
            $("body").prepend(html);

            // Do something more here...
        }
    }).fail(function(xhr, textStatus, error)
    {
        displayMessage(error, 'danger');
    });
}

This definitely DOES NOT WORK out of the box! So what’s the problem? The problem is that when checking/debugging the JavaScript using Firebug or Google Chrome,  the error parameter is set with a default IIS error message: Internal Server Error for the standard 500 error code. The exception message gets populated inside the xhr parameter instead in the middle of the standard page’s HTML code used by IIS.

Looking around I found something that could help in this case:

Handling Exceptions using jQuery and ASP.NET MVC

Improving the code shared on the above post, the basic idea is to have a common JsonResult action method in a BaseController for example that sets the Response.StatusDescription directly like this:

public JsonResult ThrowJsonError(Exception e)
{
    Logger.Error(e.Message, e);

    Response.StatusCode = (int)System.Net.HttpStatusCode.BadRequest;
    Response.StatusDescription = e.Message;

    return Json(new { Message = e.Message }, JsonRequestBehavior.AllowGet);
}

Now let’s use this beauty:

public ActionResult SubCatList(string subcat)
{
    var sub = Database.GetLogSubCategory(subcat);

    if(sub == null)
    {
        return ThrowJsonError(
            new EntryNotFoundException(String.Format("SubCategory {0} does not exist", subcat)));
    }

    var subcatItems = Database.GetListItemsForSubCategory(sub);

    return PartialView(subcatItems);
}

Now that beautiful formatted exception message is populated in the error parameter in $.ajax fail callback.

Objective achieved! Hope it’s useful it the future. Happy coding and 2014 for everyone…