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.

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!

Incoming Search

c#, interop, ms excel