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!

GD Star Rating
loading...
5 New Excel Interop Tricks I Never Knew Of, 3.0 out of 5 based on 1 rating
Worth sharing ?

Incoming Search Term

Advertise Here

 

No Comment

Add Your Comment