C# - Snippets

Col_Align

public void Col_Align(
    string sColFirst,
    string sColLast = "",
    string sDirection = "LEFT")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objrange;

        if (sColLast.Length == 0)
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns[sColFirst + ":" + sColLast];

        if (sDirection != "LEFT" &&
            sDirection != "RIGHT" &&
            sDirection != "CENTER")
        {
            clszMessagesGeneral.Message(
                "Incorrect direction '" + sDirection + "' " + gsCRLF +
                "sDirection must be either 'LEFT', 'RIGHT' or 'CENTER'.");
        }

        if (sDirection == "LEFT")
            objrange.HorizontalAlignment = Excel.Constants.xlLeft;

        if (sDirection == "RIGHT")
            objrange.HorizontalAlignment = Excel.Constants.xlRight;

        if (sDirection == "CENTER")
            objrange.HorizontalAlignment = Excel.Constants.xlCenter;
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            string serrortext;

            if (sColLast.Length == 0)
                serrortext = " column '" + sColFirst + "'";
            else
                serrortext = " columns '" + sColFirst + ":" + sColLast + "'";

            clsError.Handle(
                "WidthDefine",
                msCLASSNAME,
                "to align the" + serrortext + " in the '" + sDirection + "' direction.",
                mobjCOMException,
                mobjException);
        }
    }
}

Col_AutoFit

public void Col_AutoFit(
    string sColFirst,
    string sColLast = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objrange;

        if (sColLast.Length == 0)
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns[sColFirst + ":" + sColLast];

        objrange.AutoFit();
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            string serrortext;

            if (sColLast.Length == 0)
                serrortext = " column '" + sColFirst + "'";
            else
                serrortext = " columns '" + sColFirst + ":" + sColLast + "'";

            clsError.Handle(
                "AutoFit",
                msCLASSNAME,
                "autofit the" + serrortext + ".",
                mobjCOMException,
                mobjException);
        }
    }
}

Col_Copy

public void Col_Copy(
    string sColFirst,
    string sColLast = "",
    string sFromWshName = "",
    string sFromWbkName = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Workbook objworkbookbefore = null;
        Excel.Workbook objworkbook = null;

        Excel.Worksheet objworksheetbefore = null;
        Excel.Worksheet objworksheet = null;

        Excel.Range objrange;

        if (sFromWbkName.Length > 0)
        {
            objworkbookbefore = gApplicationExcel.ActiveWorkbook;

            objworkbook = gApplicationExcel.Workbooks[sFromWbkName];
            ((Excel._Workbook)objworkbook).Activate();
        }

        if (sFromWshName.Length > 0)
        {
            objworksheetbefore = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

            objworksheet = (Excel.Worksheet)gApplicationExcel.Worksheets[sFromWshName];
            objworksheet.Select();
        }

        if (sColLast.Length == 0)
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns[sColFirst + ":" + sColLast];

        objrange.Copy();

        if (sFromWbkName.Length > 0)
            ((Excel._Workbook)objworkbookbefore).Activate();

        if (sFromWshName.Length > 0)
            objworksheetbefore.Select();
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            string serrortext;

            if (sColLast.Length == 0)
                serrortext = " column '" + sColFirst + "'";
            else
                serrortext = " columns '" + sColFirst + ":" + sColLast + "'";

            if (sFromWshName.Length == 0)
                serrortext += "from the active worksheet";
            else
                serrortext += "from worksheet '" + sFromWshName + "'";

            if (sFromWbkName.Length > 0)
                serrortext += "in the workbook '" + sFromWbkName + "'";

            clsError.Handle(
                "Copy",
                msCLASSNAME,
                "copy the" + serrortext + ".",
                mobjCOMException,
                mobjException);
        }
    }
}

Col_LastUsed

public static string Col_LastUsedCol(
    string sWshName = "")
{
    Excel.Worksheet objWorksheet = null;
    Excel.Range objRange = null;

    try
    {
        if (clsError.ErrorFlag() == true)
            return null;

        objWorksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;
        objRange = (Excel.Range)objWorksheet.Range["A1"];

        return clsCol.Letter(
            objRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column
        );
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "LastUsedCol",
                msCLASSNAME,
                "",
                mobjCOMException,
                mobjException
            );
        }
    }

    return null;
}

Col_Letter

Converting a column number to its equivalent column letter.

public static string Col_Letter(
    int iColNo)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return null;

        int inumber1;

        switch (iColNo)
        {
            case 0:
                return ((char)90).ToString();   // Chr(90)

            case int n when n <= 26:
                return ((char)(iColNo + 64)).ToString();   // Chr(iColNo + 64)

            default:
                inumber1 = (int)Math.Floor(64 + ((iColNo - 1) / 26.0));
                return ((char)inumber1).ToString() +
                       ((char)(((iColNo - 1) % 26) + 65)).ToString();
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "Letter",
                "clsCol",
                "return the corresponding letter for the column number '" + iColNo + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }

    return null;
}

Col_Number

Converting a column letter to its equivalent column number.

public static int Col_Number(
    string sColChar,
    string sWshName = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return 0;

        int istartnumber;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        if (sColChar.Length == 1)
        {
            if (sWshName != "")
            {
                objworksheet = (Excel.Worksheet)gApplicationExcel.Worksheets[sWshName];
                return objworksheet.Range[sColChar + "1"].Column;
            }
            else
            {
                objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;
                return objworksheet.Range[sColChar + "1"].Column;
            }
        }
        else
        {
            objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

            istartnumber = objworksheet.Range[sColChar.Substring(0, 1) + "1"].Column;

            return (istartnumber * 26 * (sColChar.Length - 1)) +
                   clsCol.Number(sColChar.Substring(1));
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "Number",
                "clsCol",
                "return the corresponding number for the column letter '" + sColChar + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }

    return 0;
}

Col_SelectCol

public void Col_SelectCol(
    string sColFirst,
    string sColLast = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objrange;

        if (sColLast.Length == 0)
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns[sColFirst + ":" + sColLast];

        objrange.Select();
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            string serrortext;

            if (sColLast.Length == 0)
                serrortext = " column '" + sColFirst + "'";
            else
                serrortext = " columns '" + sColFirst + ":" + sColLast + "'";

            clsError.Handle(
                "SelectCol",
                msCLASSNAME,
                "select the" + serrortext + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }
}

Col_WidthDefine

public void Col_WidthDefine(
    float sngColWidth,
    string sColFirst,
    string sColLast = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objrange;

        if (sColLast.Length == 0)
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns[sColFirst + ":" + sColLast];

        objrange.ColumnWidth = sngColWidth;
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            string serrortext;

            if (sColLast.Length == 0)
                serrortext = " column '" + sColFirst + "'";
            else
                serrortext = " columns '" + sColFirst + ":" + sColLast + "'";

            clsError.Handle(
                "WidthDefine",
                msCLASSNAME,
                "define the width of " + serrortext + " to '" + sngColWidth + "' points.",
                mobjCOMException,
                mobjException
            );
        }
    }
}

Col_WidthToPoints

public static float Col_WidthToPoints(
    string sColFirst,
    string sColLast = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return 0f;

        Excel.Range objrange;

        if (sColLast.Length == 0)
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns[sColFirst + ":" + sColLast];

        return (float)objrange.Width;
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            string serrortext;

            if (sColFirst.Length == 0)
                serrortext = " column \"" + sColFirst + "\".";
            else
                serrortext = " columns \"" + sColFirst + ":" + sColLast + "\".";

            clsError.Handle(
                "WidthToPoints",
                "clsCol",
                "return the corresponding column width for" + serrortext,
                mobjCOMException,
                mobjException
            );
        }
    }

    return 0f;
}

Cols_DeleteCols

public static float Cols_DeleteCols(
    params string[] asColumns)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return 0f;

        Excel.Range objColumns;
        int icount;
        string scolumnfirst;
        string scolumnlast;

        for (icount = 1; icount <= (asColumns.Length / 2); icount++)
        {
            scolumnfirst = asColumns[2 * (icount - 1)];
            scolumnlast  = asColumns[2 * (icount - 1) + 1];

            objColumns = (Excel.Range)gApplicationExcel.Columns[scolumnfirst + ":" + scolumnlast];

            objColumns.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "DeleteCols",
                "clsCol",
                "delete the columns.",
                mobjCOMException,
                mobjException
            );
        }
    }

    return 0f;
}

Cols_InsertCols

public static float Cols_InsertCols(
    params string[] asColumns)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return 0f;

        Excel.Range objColumns;
        int icount;
        string scolumnfirst;
        string scolumnlast;

        for (icount = 1; icount <= (asColumns.Length / 2); icount++)
        {
            scolumnfirst = asColumns[2 * (icount - 1)];
            scolumnlast  = asColumns[2 * (icount - 1) + 1];

            objColumns = (Excel.Range)gApplicationExcel.Columns[scolumnfirst + ":" + scolumnlast];

            objColumns.Insert(Excel.XlInsertShiftDirection.xlShiftToRight);
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "InsertCols",
                "clsCol",
                "insert the columns.",
                mobjCOMException,
                mobjException
            );
        }
    }

    return 0f;
}

Row_HeightToPoints

public static float Row_HeightToPoints(
    int iRowFirst,
    int iRowLast = 0)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return 0f;

        Excel.Range objrange;

        if (iRowLast == 0)
            iRowLast = iRowFirst;

        objrange = (Excel.Range)gApplicationExcel.Range[
            "A" + iRowFirst + ":" + "A" + iRowLast
        ];

        return (float)objrange.Height;
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            string serrortext;

            if (iRowLast == 0)
                serrortext = " row \"" + iRowFirst + "\"";
            else
                serrortext = " rows \"" + iRowFirst + ":" + iRowLast + "\"";

            clsError.Handle(
                "HeightToPoints",
                "clsRow",
                "return the corresponding row height for" + serrortext,
                mobjCOMException,
                mobjException
            );
        }
    }

    return 0f;
}

Row_LastUsed

public static int Row_LastUsedRow(
    string sWshName = "")
{
    Excel.Worksheet objWorksheet = null;
    Excel.Range objRange = null;

    try
    {
        if (clsError.ErrorFlag() == true)
            return 0;

        objWorksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;
        objRange = (Excel.Range)objWorksheet.Range["A1"];

        return objRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "LastUsedRow",
                msCLASSNAME,
                "",
                mobjCOMException,
                mobjException
            );
        }
    }

    return 0;
}

Row_LastUsedQuick

public static int Row_LastUsedRowQuick(
    string sColChar,
    string sWshName = "")
{
    Excel.Worksheet objWorksheet = null;
    Excel.Range objRange = null;

    try
    {
        if (clsError.ErrorFlag() == true)
            return 0;

        objWorksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        objRange = (Excel.Range)objWorksheet.Range[
            sColChar + gi64TOTALROWS
        ];

        objRange = objRange.End(Excel.XlDirection.xlUp);

        return objRange.Row;
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "LastUsedRowQuick",
                msCLASSNAME,
                "",
                mobjCOMException,
                mobjException
            );
        }
    }

    return 0;
}

Rows_InsertRows

public static float Rows_InsertRows(
    params long[] alRows)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return 0f;

        Excel.Range objRows;
        int icount;
        long lrowfirst;
        long lrowlast;

        for (icount = 1; icount <= (alRows.Length / 2); icount++)
        {
            lrowfirst = alRows[2 * (icount - 1)];
            lrowlast  = alRows[2 * (icount - 1) + 1];

            objRows = (Excel.Range)gApplicationExcel.Rows[
                lrowfirst + ":" + lrowlast
            ];

            objRows.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "InsertRows",
                "clsRows",
                "insert the rows.",
                mobjCOMException,
                mobjException
            );
        }
    }

    return 0f;
}

Rows_ShadeAlternate

public static void Rows_ShadeAlternate(
    long lFirstColourIndex,
    long lSecondColourIndex,
    params int[] iArrayRows)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objRows;
        int icount;
        long lrowwfirst;
        long lrowwlast;
        int inumberofrows;
        long lrownumber;

        for (icount = 1; icount <= (iArrayRows.Length / 2); icount++)
        {
            lrowwfirst = iArrayRows[2 * (icount - 1)];
            inumberofrows = iArrayRows[2 * (icount - 1) + 1];
            lrowwlast = lrowwfirst + inumberofrows - 1;

            lrownumber = lrowwfirst;

            while (lrownumber <= lrowwlast)
            {
                objRows = (Excel.Range)gApplicationExcel.Rows[
                    lrownumber + ":" + lrownumber
                ];
                objRows.Interior.ColorIndex = lFirstColourIndex;

                lrownumber++;

                if (lrownumber <= lrowwlast)
                {
                    objRows = (Excel.Range)gApplicationExcel.Rows[
                        lrownumber + ":" + lrownumber
                    ];
                    objRows.Interior.ColorIndex = lSecondColourIndex;

                    lrownumber++;
                }
            }
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "ShadeAlternate",
                "clsRows",
                "shade the rows ?? alternatively with the colours '" +
                lFirstColourIndex + "' and '" + lSecondColourIndex + "'",
                mobjCOMException,
                mobjException
            );
        }
    }
}

---

© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited Top