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; app.DisplayAlerts = false; 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);
Range filteredRange = range.SpecialCells(XlCellType.xlCellTypeVisible);
var netAddCount = filteredRange.Count / filteredRange.Columns.Count;
Pivot Table
PivotTables pivotTables = worksheet.PivotTables();
var firstPivot = pivotTables.Item(1);
firstPivot.PivotFields(<ColumnNo>).ClearAllFilters();
firstPivot.PivotFields(<ColumnNo>).CurrentPage = "B";
PivotField pivotFieldColumn = firstPivot.PivotFields(<ColumnName>);
pivotFieldColumn.PivotFilters.Add2(Type: XlPivotFilterType.xlAfter, Value1: DateTime.Today);
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.