none
convert text to number for excel column in c# RRS feed

  • Question

  • Hi,

    I wrote a window form application and I did some processing on excel file in this program. but I have a problem with excel input file. the content of some columns are number but a green arrow is beside them that after open this, it says convert to number. I think they are text and they should convert to number but I want to convert them to number automatically in my program. how can I do it? I also know the index of columns.

    Monday, January 15, 2018 3:44 PM

Answers

  • Hello nadianaji,

    Please refer to below code and adjust the column index for your need.

    Excel.Application app = new Excel.Application();
                app.Visible = true;
                Excel.Workbook workbook = app.Workbooks.Open(@"C:\Users\Admin\Desktop\Test.xlsx");
                Excel.Worksheet worksheet = workbook.Worksheets[1];
                //adjust column index for your need
                int ColumnIndex = 1;
                worksheet.Columns[ColumnIndex].TextToColumns();
                worksheet.Columns[ColumnIndex].NumberFormat = "0";

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by nadianaji Tuesday, January 16, 2018 9:26 AM
    Tuesday, January 16, 2018 6:19 AM

All replies

  • Hello nadianaji,

    Please refer to below code and adjust the column index for your need.

    Excel.Application app = new Excel.Application();
                app.Visible = true;
                Excel.Workbook workbook = app.Workbooks.Open(@"C:\Users\Admin\Desktop\Test.xlsx");
                Excel.Worksheet worksheet = workbook.Worksheets[1];
                //adjust column index for your need
                int ColumnIndex = 1;
                worksheet.Columns[ColumnIndex].TextToColumns();
                worksheet.Columns[ColumnIndex].NumberFormat = "0";

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by nadianaji Tuesday, January 16, 2018 9:26 AM
    Tuesday, January 16, 2018 6:19 AM
  • Thank you very much for your help.
    Tuesday, January 16, 2018 9:27 AM
  • Hi Terry,

    I have similar issue and when I use your code  - data in the columns are moving up. (column with the data starts from 5th row). Do you have a solution/workaround for this maybe? 

    Wednesday, December 11, 2019 1:38 PM