Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Excel Handling Using .NET C#

0.00/5 (No votes)
2 Mar 2016 1  
Tips and tricks to quickly find out how to manipulate Excel using .NET Managed Code

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here