CODEWITHSUNDEEP
c#stringexcelvstoexcel-interop
TwenteMaster
TwenteMaster

Reputation: 158

How to store a column of data in excel to string[] in C#

I have a string[] member like this:

private string[] mStrings = null;

and code in the constructor like this:

Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(excel_file_path);
Excel.Worksheet excelWorkSheet = (Excel.Worksheet)excelWorkBook.Sheets[2]; // I get the data from the second sheet

I want to store data in column F to mStrings, so I wrote code like this:

mStrings = excelWorkSheet.Columns["F"].Value2;

this code is wrong, so what is the right code? Thanks!

Upvotes: 0

Views: 1847

Answers (1)

sco
sco

Reputation: 81

There might be a better way of solving this, but i managed to solve your problem by getting UsedRange in the column then iterating over the column with a simple 'for' loop, storing the values to a List of strings and then passing the List to an array of strings

        string[] mColumn = null;
        List<string> mlstColumn = new List<string>();

        // get used range of column F
        Range range = excelWorkSheet.UsedRange.Columns["F", Type.Missing];

        // get number of used rows in column F
        int rngCount = range.Rows.Count;

        // iterate over column F's used row count and store values to the list
        for (int i = 1; i <= rngCount; i++)
        {
            mlstColumn.Add(excelWorkSheet.Cells[i, "F"].Value.ToString());
        }

        // List<string> --> string[]
        mColumn = mlstColumn.ToArray();

        // remember to Quit() or the instance of Excel will keep running in the background
        excelApp.Quit();

Upvotes: 1

Related Questions