How to set certain column types in Microsoft.Office.Interop.Excel in C#?

Nicholas Piazza 526 Reputation points
2024-03-19T17:35:53.5566667+00:00

In Microsoft.Office.InterOp.Excel there is an XLColumnDataType enum. However, it is not complete. There is no mention of certain column types such as Number, Currency, Percentage, Scientific, etc. How does one set the column type to, say, Currency using C# and the Microsoft.Office.Interop.Excel DLL?

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,329 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,180 questions
Office Management
Office Management
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Management: The act or process of organizing, handling, directing or controlling something.
1,995 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiale Xue - MSFT 29,246 Reputation points Microsoft Vendor
    2024-03-20T03:16:38.37+00:00

    Hi @Nicholas Piazza , Welcome to Microsoft Q&A,

    It's true that you can't set it to currency format directly, but you can use numberformat to manually set the column's number format to achieve the same effect.

    The following are official related links:

    https://learn.microsoft.com/en-us/office/vba/api/excel.range.numberformat

    Similar code is::

    using Excel = Microsoft.Office.Interop.Excel;
    
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new Excel application
            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = true; // Make Excel visible
    
            // Add a new workbook
            Excel.Workbook workbook = excelApp.Workbooks.Add();
    
            // Get the first worksheet
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
    
            // Set the data type of the entire column to Currency
            Excel.Range column = worksheet.Columns["A"];
            column.NumberFormat = "$#,##0.00";
    
            // Example data
            worksheet.Cells[1, 1].Value = "Currency Column";
            worksheet.Cells[2, 1].Value = 1000.50;
            worksheet.Cells[3, 1].Value = 2000.75;
            worksheet.Cells[4, 1].Value = 3000.25;
    
            // Save the workbook (optional)
            workbook.SaveAs("example.xlsx");
    
            // Clean up
            workbook.Close();
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
        }
    }
    

    Best Regards,

    Jiale


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful