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

Question

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?

Answer 1

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.