On September 1st I completed 1 year at Chemtech.

So far I’ve worked on projects that demanded what I knew and what I didn’t know yet. This is the kind of thing I ever wanted. While I was looking for a job opportunity this was what I had in my mind.

The Computer Engineering graduation helped me to learn the basics so that I could be prepared to start working.

The real learning happens when you get to the market and start building real software that controls a huge industrial plant for example as is the case of a control panel that shows to the users the state of variables scattered across a huge industrial field consisting of a lot of machines and processes. Software that you build, test, homologate and deploy in production environment. Software that is built in partnership with the client - be it through e-mail, by phone or in person. This kind of thing gives you a valuable experience.

The projects I’ve worked on during this 1st year are the following:

Braskem - Control Panel
ASP.NET, C#, CSS, NHibernate, SQL, ORACLE, IIS

Braskem - Billing and Measurement System
ASP.NET, C#, CSS, NHibernate, Web service, SQL, ORACLE, IIS

Chemtech - Site (nicknamed chemsite)
Liferay, Java, CSS, SQL, MySQL, Tomcat

CSN - MES
VB 6, SQL, MS SQL Server

As you see I’ve used different technologies in each project. This strengthens your brain as you advance in the learning path and gives you the mindset you need to carry on so that you can work in the front line with more responsibilities and more exciting and engaging projects.

During 1 year I could learn a lot of things and made the difference constructing software products used by a lot of people (hey, take a look at the about me section at the top right side of the blog). See that I wrote a lot of people instead of millions of people. Someday I’ll achieve that "millions of people" :o)

Chemtech is a great company to work for. People are always ready and motivated to help you. Even my manager once in a while helps me. He sits by my side and starts coding with me! Everyone wins when one member of the group wins. This is the thought!

All in all, great company and great people together. That’s why Chemtech is making the difference in Brazil’s engineering scenario.

If you’re looking for an engineering job opportunity in Brazil, consider Chemtech. A company that thinks ahead of its time envisioning a great future for Brazil and the world.

I could enumerate lots of good points about Chemtech, but if you want to know more, go ahead and visit the chemsite. There you’ll find a bunch of information.

Despite a great start in my professional career I’ve passed through some life changing experiences as for example, moving from Volta Redonda to Rio de Janeiro. Wow, this moving has made me think about different aspects of life. Many things I thought before getting in Rio de Janeiro needed to be rethought. In the end you grow mentally, emotionally and spiritually. This is the time in life when everything you’ve learned has to be put in action.

All in all, I’m doing at Chemtech what I like to do and that’s all folks for my 1st year.

A big thanks to everyone who has helped me to get here. I won’t name anyone. I’ve known a lot of people. People that helped me a lot.  You know who you are.

Thanks Jesus for making real what I’ve asked you, for being with me all the time and for teaching, capacitating and advising me through this journey/endeavor.

Let’s say you have a string and want to match individual groups of characters within that string. How would you do that? That’s the question I asked myself.
The following code is the outcome of some research on how to get this done. It shows you how to capture/match and name groups of characters using a regex pattern.

class RegexGroupsNames
{
    public static void Main()
    {
        // String to be parsed
        string str = "777L777_333_4444_55555_22_20090926_1727_666666_999999999_1010101010";

        // Regex pattern
        // Here we define the groups that form our string according to our need.
        // Each group has a name so that it's easier to get the individual values.
        string pattern =
               @"(?<group1>\d{3}[A-Z]\d{3})_(?<group2>\d{3})_(?<group3>\d{4})_(?<group4>\d{5})_(?<group5>\d{2})_(?<group6>\d{8})_(?<group7>\d{4})_(?<group8>\d{6})_(?<group9>\d{9})_(?<group10>\d{10})";

        // Creating the Regex used to parse the string with the pattern defined above
        Regex regex = new Regex(pattern);

        // String is a match or not ?
        Console.WriteLine("{0} {1} a valid string.", str, Regex.IsMatch(str, pattern, RegexOptions.IgnoreCase) ? "is" : "is not");

        // Matching the string and getting the groups named above
        Match match = regex.Match(str);

        // Writing the values of each group
        Console.WriteLine("group1  = {0}", match.Groups["group1"].Value);
        Console.WriteLine("group2  = {0}", match.Groups["group2"].Value);
        Console.WriteLine("group3  = {0}", match.Groups["group3"].Value);
        Console.WriteLine("group4  = {0}", match.Groups["group4"].Value);
        Console.WriteLine("group5  = {0}", match.Groups["group5"].Value);
        Console.WriteLine("group6  = {0}", match.Groups["group6"].Value);
        Console.WriteLine("group7  = {0}", match.Groups["group7"].Value);
        Console.WriteLine("group8  = {0}", match.Groups["group8"].Value);
        Console.WriteLine("group9  = {0}", match.Groups["group9"].Value);
        Console.WriteLine("group10 = {0}", match.Groups["group10"].Value);

        // Defining the Culture to show the DateTime
        IFormatProvider culture = new CultureInfo("en-US", true);

        // Creating a DateTime variable with the data contained within groups 6 and 7
        DateTime dt = DateTime.ParseExact(match.Groups["group6"].Value + match.Groups["group7"].Value, "yyyyMMddHHmm", culture);

        Console.WriteLine(dt);
    }
}

This is the string to be parsed:

"777L777_333_4444_55555_22_20090926_1727_666666_999999999_1010101010"

It has 10 parts “groups” separated by an underscore character ( _ ).

What we want to do is to extract each individual group so that we can manipulate it anyway we want.

To accomplish that we define a regex that has the following pattern:

@"(?<group1>\d{3}[A-Z]\d{3})_(?<group2>\d{3})_(?<group3>\d{4})_(?<group4>\d{5})_(?<group5>\d{2})_(?<group6>\d{8})_(?<group7>\d{4})_(?<group8>\d{6})_(?<group9>\d{9})_(?<group10>\d{10})";

Let’s dissect the regex…

The 1st group denoted by the first pair of round brackets ( ) will look for 3 digits \d{3} followed by an uppercase letter ranging from A through Z [A-Z] followed by another 3 digits \d{3}.
The 2nd group denoted by the second pair of parenthesis will look for 3 digits, the 3rd group will look for 4 digits, the 4th group will look for 5 digits and so on… I think you got the point!  :- )

While playing with this I found an interesting thing, that is, you can give a name to each group. In this case, the name goes inside the angle brackets < > preceded by a question mark. I’ve given the name <group1> to the first group and just incremented the final number in the others.

Naming your groups is great because you can refer to them while manipulating the matched groups without having to remember the exact position inside the string. Instead of doing this:

// Writing the values of each group
Console.WriteLine(match.Groups[0].Value);
Console.WriteLine(match.Groups[1].Value);
Console.WriteLine(match.Groups[2].Value);
Console.WriteLine(match.Groups[3].Value);
Console.WriteLine(match.Groups[4].Value);
Console.WriteLine(match.Groups[5].Value);
Console.WriteLine(match.Groups[6].Value);
Console.WriteLine(match.Groups[7].Value);
Console.WriteLine(match.Groups[8].Value);
Console.WriteLine(match.Groups[9].Value);

We can do this:

// Writing the values of each group
Console.WriteLine(match.Groups["group1"].Value);
Console.WriteLine(match.Groups["group2"].Value);
Console.WriteLine(match.Groups["group3"].Value);
Console.WriteLine(match.Groups["group4"].Value);
Console.WriteLine(match.Groups["group5"].Value);
Console.WriteLine(match.Groups["group6"].Value);
Console.WriteLine(match.Groups["group7"].Value);
Console.WriteLine(match.Groups["group8"].Value);
Console.WriteLine(match.Groups["group9"].Value);
Console.WriteLine(match.Groups["group10"].Value);

Isn’t it cool!?

With this you can create your regex pattern and match the groups of characters that interest you the most.

Grouping enables you to work with separate sets of data. Naming each group enables you to refer to each one of them easily.

This is the output of the code:

Hope this helps.

References
RegExLib.com - Regular Expression Library
http://regexlib.com/

Silverlight Regular Expression Tester
http://regexlib.com/RESilverlight.aspx

This java crawler is extremely useful if you need to search a webpage for a specific word, tag or whatever you want to analyze in the data retrieved from a given URL.

I’ve used it for example to search for a specific error message that appeared in a page when a connection to the database could not be done. It helped me to prove that the error was really caused as a consequence of the connection link failure to the database.

The crawler saves in the file system the page that contains the string you’re searching for. The name of the file contains the time from when the string was found within the page body. With this information I could match the time information present on the file name with the time accompanying the error present in the web server log.

The code was originally developed by Rodrigo Gama that is a fellow developer/coworker of mine. I just adapted the code a little bit to fit my needs.

What’s the idea behind the crawler?
The main idea behind the crawler is the following:

You pass 2 essential parameters to run the application - these are the string you want to search for and the URLs you want to verify.

A thread for each URL is then created. This is done using the PageVerificationThread.java class that implements Runnable.

The PageVerificationThread creates a notificator object that is responsible for calling the MailSender object that in its turn sends a notification (message) to the emails you hardcoded in the Main.java class.

The message is also hardcoded inside the run() method of PageVerificationThread class.

I advise you to read the comments in the code.

You’ll have to change some strings in the code as is the case of the username and password used to send the e-mails.

The Code
The crawler has 4 classes: MailSender.java, Main.java, Notificator.java and PageVerificationThread.java.

This is the Main class:

/**  * @authors Rodrigo Gama (main developer)  *          Leniel Macaferi (minor modifications and additions)  * @year 2009  */ import java.net.MalformedURLException; import java.util.ArrayList; import java.util.List; public class Main {     public static void main(String[] args) throws MalformedURLException     {         // URLs that will be verified         List<String> urls = new ArrayList<String>();         // Emails that will receive the notification         String[] emails = new String[]         { "youremail@gmail.com" };         // Checking for arguments         if(args == null || args.length < 2 || args[0] == null)         {             System.out.println("Usage: <crawler.jar> <search string> <URLs>");             System.exit(0);         }         else         {             // Printing some messages to the screen             System.out.println("Searching for " + args[0] + "...");             System.out.println("On:");             // Showing the URLs that will be verified and adding them to the paths variable             for(int i = 1; i < args.length; i++)             {                 System.out.println(args[i]);                 urls.add(args[i]);             }         }         // For each URL we create a PageVerificationThread passing to it the URL address, the token to         // search for and the destination emails.         for(int i = 0; i < urls.size(); i++)         {             Thread t = new Thread(new PageVerificationThread(urls.get(i), args[0], emails));             t.start();         }     } }

This is the PageVerificationThread class:

/**  * @authors Rodrigo Gama (main developer)  *          Leniel Macaferi (minor modifications and additions)  * @year 2009  */ import java.io.BufferedReader; import java.io.File; import java.io.FileWriter; import java.io.IOException; import java.io.InputStreamReader; import java.net.MalformedURLException; import java.net.URL; import java.net.URLConnection; import java.util.Calendar; import java.util.Properties; import java.util.TimeZone; public class PageVerificationThread implements Runnable {     private String                strUrl;     private String                searchFor;     private static Notificator    notificator = null;     private static Object         lock        = new Object();     private int                   numAttempts = 0;     public PageVerificationThread(String strUrl, String searchFor, String[] emails)     {         this.strUrl = strUrl;         this.searchFor = searchFor;         synchronized(lock)         {             if(notificator == null)             {                 notificator = new Notificator();                 // For each email, adds it to the notificator "to" list.                 for(int i = 0; i < emails.length; i++)                 {                     notificator.addDesetination(emails[i]);                 }             }         }     }     public void run()     {         try         {             URL url = new URL(strUrl);             // Time interval to rerun the thread             float numMinutes = 1;             while(true)             {                 try                 {                     Properties systemProperties = System.getProperties();                     systemProperties.put("http.proxyHost",                             "proxy.yourdomain.com");                     systemProperties.put("http.proxyPort", "3131");                     System.setProperties(systemProperties);                     URLConnection conn = url.openConnection();                     conn.setDoOutput(true);                     // Get the response content                     BufferedReader rd = new BufferedReader(                             new InputStreamReader(conn.getInputStream()));                                         String line;                                         StringBuilder document = new StringBuilder();                     // A calendar to configure the time                     Calendar calendar = Calendar.getInstance();                     TimeZone tz = TimeZone.getTimeZone("America/Sao_Paulo");                     calendar.setTimeZone(tz);                     calendar.add(Calendar.SECOND, 9);                     String timeStamp = calendar.getTime().toString();                     boolean error = false;                     // For each line of code contained in the response                     while((line = rd.readLine()) != null)                     {                         document.append(line + "\n");                         // If the line contains the text we're after...                         if(line.contains(searchFor))                         {// "is temporarily unavailable."))                             // {                             error = true;                         }                     }                     // System.out.println(document.toString());                                         // If we found the token...                     if(error)                     {                         // Prints a message to the console                         System.out.println("Found " + searchFor + " on " + strUrl);                         // Sends the e-mail                         notificator.notify("Found " + searchFor + " on " + strUrl);                         // Writing the file to the file system with time information                         FileWriter fw = null;                         try                         {                             String dir = "C:/Documents and Settings/leniel-macaferi/Desktop/out/" + strUrl.replaceAll("[^A-Za-z0-9]", "_") + "/";                             File file = new File(dir);                             file.mkdirs();                             file = new File(dir + timeStamp.replaceAll("[^A-Za-z0-9]", "_") + ".html");                             file.createNewFile();                             fw = new FileWriter(file);                             fw.append(document);                         }                         finally                         {                             if(fw != null)                             {                                 fw.flush();                                 fw.close();                             }                         }                     }                     // If error we reduce the time interval                     if(error)                     {                         numMinutes = 0.5f;                     }                     else                     {                         numMinutes = 1;                     }                     try                     {                         Thread.sleep((long) (1000 * 60 * numMinutes));                     }                     catch(InterruptedException e)                     {                         e.printStackTrace();                     }                     // A counter to show us the number of attempts so far                     numAttempts++;                     System.out.println("Attempt: " + numAttempts + " on " + strUrl + " at " + calendar.getTime().toString());                 }                 catch(IOException e)                 {                     e.printStackTrace();                 }             }         }         catch(MalformedURLException m)         {             m.printStackTrace();         }     } }

This is the Notificator class:

/**  * @author Rodrigo Gama  * @year 2009  */ import java.util.ArrayList; import java.util.List; import javax.mail.MessagingException; import javax.mail.internet.AddressException; public class Notificator {     private List<String>    to   = new ArrayList<String>();     private String          from = "leniel-macaferi";     public void addDesetination(String dest)     {         to.add(dest);     }     public synchronized void notify(String message)     {         try         {             // Sends the e-mail             MailSender.sendMail(from, to.toArray(new String[] {}), message);         }         catch (AddressException e)         {             e.printStackTrace();         }         catch (MessagingException e)         {             e.printStackTrace();         }     } }

This is the MailSender class:

/**  * @authors Rodrigo Gama  * @year 2009  */ import java.util.Properties; import javax.mail.Authenticator; import javax.mail.Message; import javax.mail.MessagingException; import javax.mail.PasswordAuthentication; import javax.mail.Session; import javax.mail.Transport; import javax.mail.internet.AddressException; import javax.mail.internet.InternetAddress; import javax.mail.internet.MimeMessage; public class MailSender {     public static void sendMail(String from, String[] toArray,             String messageText) throws AddressException, MessagingException     {         // Get system properties         Properties props = System.getProperties();         // Setup mail server (here we’re using Gmail)          props.put("mail.smtp.host", "smtp.gmail.com");         props.put("mail.smtp.starttls.enable", "true");         props.put("mail.smtp.auth", "true");         // Get session         Authenticator auth = new MyAuthenticator();         Session session = Session.getDefaultInstance(props, auth);         // Define message         MimeMessage message = new MimeMessage(session);         message.setFrom(new InternetAddress(from));         for(int i = 0; i < toArray.length; i++)         {             String to = toArray[i];             message.addRecipient(Message.RecipientType.TO, new InternetAddress(to));         }         message.setSubject("The e-mail subject goes here!");         message.setText(messageText);         // Send message         Transport.send(message);     } } class MyAuthenticator extends Authenticator {     MyAuthenticator()     {         super();     }     protected PasswordAuthentication getPasswordAuthentication()     {         // Your e-mail your username and password         return new PasswordAuthentication("username", "password");     } }

How to use it?
Using Eclipse you just have to run it as shown in this picture:

I hope you make good use of it!

Source Code
Here it is for your delight: http://leniel.googlepages.com/JavaCrawler.zip

During the second half of July and the first half of August I was working on Chemtech's site doing some maintenance. It was a good job because I could get to know new technology as is the case of Liferay. Liferay is a great enterprise portal that allows you to create a complete website solution coded in Java.

I also could verify the quality of the new Eclipse 3.5 IDE codenamed Galileo that I used during the maintenance. It's a great IDE to Java developers. It has lots of plugins that allow you to work with practically any kind of programming technology inside a fantastic set of windows for every type of task. Before using Eclipse I had only worked with NetBeans to do Java development.

I improved my skills about Tomcat too.

During a time like this you accelerate the learning process and get to know new things which are very important for any software developer.

After fixing some bugs on the site and writing in chemsite’s project wiki everything I grasped and did I came back to Volta Redonda for a two week job on CSN's MES; more on this in the next post.

You see, for the past 10 months I’ve worked with ASP.NET and Oracle (Braskem) and then I switched to work with Java and MySQL for 1 month (chemsite). Now at CSN I'm working with Visual Basic and SQL Server.

This shows that in today's world there's no bullet proof technology when we talk about programming languages and database systems. Each company has its own legacy systems that date back to two or one decade ago and such systems require certain types of interfaces developed in certain types of technologies. For example, if you look to 10 years ago (1999), C# wasn't even a programming language and so Visual Basic predominated during that time. It is most of the times impossible to a company to redevelop a really big system in a new programming language that is today's bullet proof. Those big systems consumed a lot of time and money to be constructed and in the future the programming language that is today’s bullet proof may not stand out.

As software professional you must act with any tool that is put in your hands.

I like what I do and no matter the tool I use I'm always satisfied with my job because of course, I do what I like to do, that is, software development.

To explain why Chemtech is a great place to work for in Brazil I think the above text says it all. In just 1 year I had the opportunity to work in different projects that use different technologies. A great way to leverage a career.

Thanks Jesus for that! :)

Interesting discussion at StackOverflow:
Create Excel (.XLS and .XLSX) file from C#

If you want to see how to combine NPOI + Excel Table and Chart,
take a look at the post titled NPOI with Excel Table and dynamic Chart.

NPOI 2.0 series of posts scheduled

Recently I had to implement some code to create an Excel spreadsheet/report using C#.

The task was: given an Excel spreadsheet template - a .XLS file (with formulas, pivot tables, macros, etc) I had to fill some data in one of the sheets of the spreadsheet and send this modified spreadsheet back to the user requesting such an operation (Excel report).

The following attests the need for Excel nowadays:

Excel has long been recognized as the de facto standard when it comes to presenting management reports. The unique combination of great calculation engine, excellent charting facilities, pivot tables and the possibility to perform “what if” analysis, make it the “must have” business intelligence tool.
by John Tunnicliffe

I had a great time while studying the possible ways of doing what the task asks for.

It appears to be a simple task at first but as the time passes by you get to know that this is not the case, well, till the moment this blog post was written at least, I think. :-)

Firstly I tried automating Excel through COM automation, but as the application was meant to be used in a web context it is not recommended to use automation. Why? Because COM automation for Excel is not thread safe, that is, EXCEL.EXE was not constructed to be used by concurrent users accessing the same process, in this case EXCEL.EXE; besides, Microsoft Excel must be installed on the server what is not always possible.

For more details on why you shouldn’t use Excel on the server, read this article on Microsoft Help and Support site: Considerations for server-side Automation of Office. The key part is this:

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

I’ve just experienced the above. EXCEL.EXE insisted in being alive in task manager even after processing the data and being closed in code. Each call to process a spreadsheet opens an EXCEL.EXE process on the server. With such EXCEL.EXE processes don’t being closed as they should you get lots of those processes on memory which could overload the server.

Do not use COM automation if you are developing server-side code.

After struggling with COM automation I finally got to know ExcelPackage  which works with Office Open Document Format (OOXML). It can read an .XLSX (Microsoft Excel 2007) template and create another .XLSX file based on such template, giving you lots of possibilities to work with the template copy.

I’ve gotten really happy because I had found a way of doing what the task was asking for but with a minor detail: ExcelPackage works only with .XLSX file format letting the .XLS (Microsoft Excel 2003) format out of the game. Well it turned out to be a big impediment because the client (software buyer) wouldn’t allow us to install the famous Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats on user machines so that users could open and save OOXML file formats even using Microsoft Office 2003 suite.

Discovering ExcelPackage was good but it didn’t do the trick that implies the use of an .XLS template.

I got back Googling again and strived to find an open source library that would allow me to do what I wanted. After some time I finally discovered NPOI.  Wow, it could read the .XLS template and generate the end result I wanted. Great. I downloaded NPOI 1.2.1 for .NET 2.0 binaries immediately and started playing with it to see what it could really do.

OK, after this short story, I’ll show you how to use both open source projects (ExcelPackage and NPOI).

I’ve created a new ASP.NET MVC project as can be seen in this picture:

 

In the Content folder I’ve placed the template spreadsheets.
In the Libs folder I’ve placed the DLLs necessary to use both ExcelPackage and NPOI open source projects.

The controller that interests us is the one called ExcelWriterController:

The methods that handle the creation of the spreadsheet are: ExcelPackageCreate and NPOICreate.

For each controller action (method) there’s a corresponding view that renders the UI to the user. Those views are the ones shown inside the ExcelWriter folder: ExcelPackage.aspx and NPOI.aspx.

This is the Home Page of the Excel Writer MVC Application - take a look at the tabs (ExcelPackage and NPOI) that lead you to the View pages:

Each view has a button which when clicked calls the corresponding action method on the ExcelWriterController.

This is the NPOI view page:

I’ll play with a simple spreadsheet I filled with the data I got from Excel’s blog post titled Formula to Access a List of Values Interspersed with Zeros or Blanks.

Let’s see the code that goes into the ExcelPackageCreate method:

/// <summary>
/// Creates a new Excel spreadsheet based on a template using the ExcelPackage library.
/// A new file is created on the server based on a template.
/// </summary>
/// <returns>Excel report</returns>
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult ExcelPackageCreate()
{
    try
    {
        FileInfo template = new FileInfo(Server.MapPath(@"\Content\ExcelPackageTemplate.xlsx"));

        FileInfo newFile = new FileInfo(Server.MapPath(@"\Content\ExcelPackageNewFile.xlsx"));

        // Using the template to create the newFile...
        using(ExcelPackage excelPackage = new ExcelPackage(newFile, template))
        {
            // Getting the complete workbook...
            ExcelWorkbook myWorkbook = excelPackage.Workbook;

            // Getting the worksheet by its name...
            ExcelWorksheet myWorksheet = myWorkbook.Worksheets["Sheet1"];

            // Setting the value 77 at row 5 column 1...
            myWorksheet.Cell(5, 1).Value = 77.ToString();

            // Saving the change...
            excelPackage.Save();
        }

        TempData["Message"] = "Excel report created successfully!";

        return RedirectToAction("ExcelPackage");
    }
    catch(Exception ex)
    {
        TempData["Message"] = "Oops! Something went wrong.";

        return RedirectToAction("ExcelPackage");
    }
}

Let’s see the code that goes into the NPOICreate method:

/// <summary>
/// Creates a new Excel spreadsheet based on a template using the NPOI library.
/// The template is changed in memory and a copy of it is sent to
/// the user computer through a file stream.
/// </summary>
/// <returns>Excel report</returns>
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult NPOICreate()
{
    try
    {
        // Opening the Excel template...
        FileStream fs =
            new FileStream(Server.MapPath(@"\Content\NPOITemplate.xls"), FileMode.Open, FileAccess.Read);

        // Getting the complete workbook...
        HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

        // Getting the worksheet by its name...
        HSSFSheet sheet = templateWorkbook.GetSheet("Sheet1");

        // Getting the row... 0 is the first row.
        HSSFRow dataRow = sheet.GetRow(4);

        // Setting the value 77 at row 5 column 1
        dataRow.GetCell(0).SetCellValue(77);

        // Forcing formula recalculation...
        sheet.ForceFormulaRecalculation = true;

        MemoryStream ms = new MemoryStream();

        // Writing the workbook content to the FileStream...
        templateWorkbook.Write(ms);

        TempData["Message"] = "Excel report created successfully!";

        // Sending the server processed data back to the user computer...
        return File(ms.ToArray(), "application/vnd.ms-excel", "NPOINewFile.xls");
    }
    catch(Exception ex)
    {
        TempData["Message"] = "Oops! Something went wrong.";

        return RedirectToAction("NPOI");
    }
}

One drawback of the ExcelPackage library is that it must create a file on the server. There are some modifications to the library that enables you to create the template copy on memory and send it to the user as the NPOI library does. Take a look at the ExcelPackage’s discussion page at CodePlex and specifically this thread: Why create Excel spreadsheets on the server?

The great thing about NPOI of course is that it enables you to work with the template in code and then send a copy of the spreadsheet directly to the user. The template remains intact and the user receives a modified copy of the template which contains the data processed by the application.

With NPOI when you click on the Create Excel report button you get the download dialog window:

With ExcelPackage you’d have to get the path of the file created on the server, in this case \Content\ExcelPackageNewFile.xlsx and then send that file to the user. This is an extra step and adds an additional burden to the server. I didn’t implement it and so I let this as an exercise to you.

Well, the spreadsheet included in this simple project has only formulas but you can for sure have an Excel template with lots of formulas, pivot tables, macros, etc. This gives you the power of Excel in code in a clean fashion.

Hope this helps shed some light on this topic!

Note
Using the open source libraries presented in this post you won’t need Microsoft Excel installed on the server.

Updated on 9/22/2010

There’s now EPPlus that extends ExcelPackage.

EPPlus is a .net library that reads and writes Excel 2007 files using the Open Office XML format (XLSX).

EPPlus supports ranges, cell styling, charts, pictures, shapes, named ranges, autofilters and a lot of other stuff.

Updated on 6/2/2010

I’m working on an ASP.NET project that uses .NET Framework 1.1. In such a case I needed to use NPOI 1.2.1 for .NET 1.1.

The code you’ll use with NPOI 1.2.1 for .NET 1.1 is the same presented on this post. Just pay attention to include this using directive inside your C# code:

using NPOI.HSSF.UserModel

Updated on 1/3/2010

If you like NPOI, help spread the word about it voting up on this ad:

Visual Studio 2008 C# ASP.NET MVC Web Application
You can get the Microsoft Visual Studio Project at:

http://sites.google.com/site/leniel/blog/ExcelWriterMvcProject.zip

To try out the code you can use the free Microsoft Visual Web Developer 2008 Express Edition that you can get at: http://www.microsoft.com/express/vwd/Default.aspx

References
ExcelPackage: Office Open XML Format file creation
http://excelpackage.codeplex.com/

ExcelPackage binaries download
http://excelpackage.codeplex.com/Release/ProjectReleases.aspx

NPOI
http://npoi.codeplex.com/

NPOI 1.2.1 for .NET 1.1 
http://npoi.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=33203

NPOI 1.2.1 for .NET 2.0
http://npoi.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=19351

NPOI samples
http://npoi.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=19351#DownloadId=70100