2014
10.14

And so I thought I’ve moved on from Excel Interop to NPOI. But here I am, scouring new tricks to make my Excel export beautiful :) Recently I googled and used these tricks in my project. So I record it here hoping I won’t forget them too soon :)

I used to think that using Interop is very very difficult. But that was like 5 years ago. My recent experience shown that using the latest Visual Studio (I’m using 2012, I know, I know) will reduce the pain alot.

Before we start, few prequisites need to be addressed. First, adding the interop in your project reference.
excel-interop

Next, referencing it in the top your source code.

using Excel = Microsoft.Office.Interop.Excel;

OK, now for the first trick.

1. Make the entire row bold

This is the basic trick to make that header row of your report

myWorksheetObject.Cells[1, 1].EntireRow.Font.Bold = true;

2. Filter your data

Instead of just bolding the header, why not use the Filter. The Excel will be much more useful with Filter.

myWorksheetObject.Cells[1, 1].EntireRow.AutoFilter();

3. Auto-fit the columns width

One way to make your Excel file more pleasing to the eyes is by making the columns’ width fit the content.

myWorksheetObject.Cells[1, 1].EntireRow.EntireColumn.AutoFit();

Personally, I would combine trick 2 and 3 to make it dazzling.

myWorksheetObject.Cells[1, 1].EntireRow.AutoFilter();
myWorksheetObject.Cells[1, 1].EntireRow.EntireColumn.AutoFit();

4. Sorting the Filter

Sometimes, your user is too lazy to sort the data (use the damn filter please), so they want you to default the sorting in the filter.

myWorksheetObject.Cells[1, 1].EntireRow.AutoFilter();
myWorksheetObject.Cells[1, 1].EntireRow.EntireColumn.AutoFit();

myWorksheetObject.AutoFilter.Sort.SortFields.Clear();

//the first sort criteria, column E
//assume we have 10 rows of data, E2 to E11
myWorksheetObject.AutoFilter.Sort.SortFields.Add(
	myWorksheetObject.get_Range("E2","E11"), 
        Excel.XlSortOn.xlSortOnValues, 
        Excel.XlSortOrder.xlAscending);

//the second sort criteria, column B
//it should also consists of 10 rows of data, B2 to B11
myWorksheetObject.AutoFilter.Sort.SortFields.Add(
	myWorksheetObject.get_Range("B2","B11"), 
        Excel.XlSortOn.xlSortOnValues, 
        Excel.XlSortOrder.xlAscending);

myWorksheetObject.AutoFilter.Sort.Apply();

5. Print Preview it

Again, your user is too lazy to set the page-break, page orientation, paper size, etc. So they want you to do it.

//use A4 paper
//be cautious with below line, might not work in Excel 2007 or below
myWorksheetObject.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;

//set to paper orientation to landscape
myWorksheetObject.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;

//don't forget to print the boxes
myWorksheetObject.PageSetup.PrintGridlines = true;

//make sure everything fit into one paper-wide
myWorksheetObject.PageSetup.Zoom = false;
myWorksheetObject.PageSetup.FitToPagesWide = 1;

That’s all folks. I hope it helps.

Cheers!

Worth sharing, No?

  • Facebook
  • Twitter
  • Google
  • Delicious
  • Digg
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS
2014
09.09

Someone is using my Email Address

This is weird.. I received this email from PortalSemarang.com. I never registered to this website.

portal-semarang

portal-semarang.2

I think PortalSemarang.com doesn’t verify whether someone actually own the email address :) Had they follow the standard:

  1. User register, server send activation email
  2. User opened mail, click the activation link
  3. Account activated

None of this would happen.

PS: This is not the first time.

Worth sharing, No?

  • Facebook
  • Twitter
  • Google
  • Delicious
  • Digg
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS
2014
06.24

Here are the steps to create ASP.NET MVC 5 with Bootstrap Layout using Visual Studio 2012. Credit to Jose.M.Aguilar, which posted it at Campus MVP:

  1. Copy the project template zip file from Jose’s SkyDrive
  2. Paste it to %USERPROFILE%\Documents\Visual Studio 2012\Templates\ProjectTemplates\Visual C#\Web

After restarting Visual Studio you should see the new project template in the selection.

In case it doesn’t work

Unfortunately, above steps didn’t work for me. I suspected that my Visual Studio 2012 was looking at different folders for project templates. To find out, click Tools -> Options.

vs2012-tools

Apparently this was the case :) Now the template is available for selection when creating new Web Project.

vs2012-new-project

I hope it helps. Cheers!

Worth sharing, No?

  • Facebook
  • Twitter
  • Google
  • Delicious
  • Digg
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS