Excel Handling Using .NET C#

Introduction

This post will help quickly write Excel handling code in C#. We are using COM Interop here, although it's recommended to use OpenXML. I found some of the features exciting about COM Interop and more suitable to my requirement.

Background

There are so many posts available online regarding Excel Interop. But I found lot of the code misleading or not working for me. So, here I will put a working piece of code and how I have fixed few challenges.

Using the Code

All code examples are relevant to .NET 4.0 , Office 2013.

Open and Close Excel properly in .NET.

We need to be careful that Excel component has been released properly once code has been executed.

Application app = null;
Workbook workbook = null;
Worksheet worksheet = null;

try
{
    app = new Application();
    Workbooks wbs = app.Workbooks;
    app.Visible = false; //Ensure Excel Not Visible in UI
    app.DisplayAlerts = false; // Ensure any User Alert won't prompt
    workbook = wbs.Open("FILEPATH");
    worksheet = (Worksheet)workbook.Sheets[1];
}
finally
{
    ExcelHelper.ReleaseExcel(ref app, ref workbook, ref worksheet);
}

I have used Helper file to put common Excel feature and called it in many places.

internal static void ReleaseExcel(ref Application originalApp, ref Workbook originalWorkbook, ref Worksheet originalWorkSheet)
        {
            if (originalWorkbook != null)
            {
                originalWorkbook.Close(0);
            }
            if (originalApp != null)
            {
                originalApp.DisplayAlerts = true;
                originalApp.Quit();
                Marshal.ReleaseComObject(originalApp);
            }
            originalWorkSheet = null;
            originalWorkbook = null;
            originalApp = null;
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

Hope you have understand why I put such a big release Excel code in Helper method.

Apply Background Color in Excel

You can use HTMLColor or SystemColor to set any Range color. Note, any Cell, rows or columns can represent a range.

internal static void ApplyHTMLColor(Range range, string htmlColor)
{
    range.Interior.Color = System.Drawing.ColorTranslator.FromHtml(htmlColor);
}

internal static void ApplySystemOleColor(Range range, System.Drawing.Color systemColor)
{
    range.Interior.Color = System.Drawing.ColorTranslator.ToOle(systemColor);
}

Apply Number/Date Format

static string EXCEL_DATETIME_FORMAT = "dd-MMM-yy";
static string EXCEL_NUMBER_FORMAT = "#,##0.0";

internal static void ApplyRangeDateTimeFormat(Range range)
{
    range.NumberFormat = EXCEL_DATETIME_FORMAT;
}

internal static void ApplyRangeNumberFormat(Range range)
{
    range.NumberFormat = EXCEL_NUMBER_FORMAT;
}

Apply Color Scale

static string RED = "#F8696B";
static string YELLOW = "#FFEB84";
static string GREEN = "#63BE7B";

internal static void ApplyColorScale(Range range, ColorScaleEnum colorScale)
{
    range.FormatConditions.Delete();
    switch (colorScale)
    {
        case ColorScaleEnum.GreenYellowRed:
            var cfColorScale = (ColorScale)(range.FormatConditions.AddColorScale(3));
            cfColorScale.ColorScaleCriteria[1].FormatColor.Color =
                System.Drawing.ColorTranslator.FromHtml(RED);
            cfColorScale.ColorScaleCriteria[2].FormatColor.Color =
                System.Drawing.ColorTranslator.FromHtml(YELLOW);
            cfColorScale.ColorScaleCriteria[3].FormatColor.Color =
                System.Drawing.ColorTranslator.FromHtml(GREEN);
            break;
        case ColorScaleEnum.RedYellowGreen:
            cfColorScale = (ColorScale)(range.FormatConditions.AddColorScale(3));
            cfColorScale.ColorScaleCriteria[1].FormatColor.Color =
                System.Drawing.ColorTranslator.FromHtml(GREEN);
            cfColorScale.ColorScaleCriteria[2].FormatColor.Color =
                System.Drawing.ColorTranslator.FromHtml(YELLOW);
            cfColorScale.ColorScaleCriteria[3].FormatColor.Color =
                System.Drawing.ColorTranslator.FromHtml(RED);
            break;
        default:
            break;
    }
}

Apply All Border

internal static void ApplyAllBorder(Range range)
 {
     range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
     range.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
     range.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle =
                             XlLineStyle.xlContinuous;
     range.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;
     range.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
     range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
 }

Apply Header and Alternate Row Style

internal static void ApplyRowStyle(Worksheet templateExcelWorksheet)
{
    int ignoreFirst = 0;
    foreach (Range row in templateExcelWorksheet.UsedRange.Rows)
    {
        ignoreFirst++;
        if (ignoreFirst == 1)
        {
            row.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#00B0F0");
            continue;
        }
        row.Interior.Color = ignoreFirst % 2 == 0 ?
            System.Drawing.ColorTranslator.FromHtml("#CBE4F9") :
            System.Drawing.ColorTranslator.FromHtml("#E7F2FC");
    }
}

Copy Format from One Row to Another (Can Extend to Any Range)

internal static void ApplyRowFormat(Worksheet templateExcelWorksheet, int sourceRow, int destinationRow)
        {
            Range sourceRange = templateExcelWorksheet.UsedRange.Rows[sourceRow];
            sourceRange.Copy();

            Range destinationRange = templateExcelWorksheet.UsedRange.Rows[destinationRow];
            destinationRange.PasteSpecial(XlPasteType.xlPasteFormats);
        }

Insert a New Column

Range secondColumn = worksheet.Range["B1"];
                secondColumn.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight,
                    XlInsertFormatOrigin.xlFormatFromRightOrBelow);

Auto Filter

if (worksheet.AutoFilter != null)
{
    worksheet.AutoFilterMode = false;
}
worksheet.EnableAutoFilter = true;
Range range = worksheet.UsedRange;
range.AutoFilter(<COLUMN NUMBER>, <FILTER TEXT>, XlAutoFilterOperator.xlAnd, Type.Missing, true);
range.AutoFilter(<COLUMN NUMBER>, <FILTER TEXT>, XlAutoFilterOperator.xlAnd, Type.Missing, true);


//Selected data

Range filteredRange = range.SpecialCells(XlCellType.xlCellTypeVisible);

//Count of selected rows
var netAddCount = filteredRange.Count / filteredRange.Columns.Count;

Pivot Table

//Get all Pivot tables in a worksheet

PivotTables pivotTables = worksheet.PivotTables();

//Choose first Pivot
var firstPivot = pivotTables.Item(1);

//Clear Filters of a particular Column
firstPivot.PivotFields(<ColumnNo>).ClearAllFilters();

//Set Current Page of a Pivot
firstPivot.PivotFields(<ColumnNo>).CurrentPage = "B";


//Set Complex Filter on Column Label

PivotField pivotFieldColumn = firstPivot.PivotFields(<ColumnName>);
pivotFieldColumn.PivotFilters.Add2(Type: XlPivotFilterType.xlAfter, Value1: DateTime.Today);

//Get Pivot Table Cell Value

var range = firstPivot.GetPivotData("Sum of OVERALL", <ColumnName>, <ColumnValue>);
count = range.Cells[1, 1].Value;

That's all for today. Please feel free to share any suggestions. Please post if you have any doubts on code examples here and if you are trying to solve any particular problem with Excel COM.