Reading data from Excel file in c#

Question

Hi I have an excel file settings.xlsx. I want to read the data from this file and assign the variables used in my code with the data.

example

             Column1   Column2
        Row1 Data        500
        Row2 Address     30
        Row3 Value       FPGA 

I have Data,Address and Value as variables in my code. Can someone assist me with a pseudocode of c# to open a file and read the contents from it as per my requirement? I want to search "Data" word in the excel file and take the value next to the cell containing "Data" and assign it to the variable "Data". I know it is confusing but I really want the final data to look like something below.

Data=500 //Finally I want my variables to have the data as follows
        Address=30
        Value= FPGA  

I tried opening a file in my code.But since I am new to c#,i am not able to understand what is going wrong in my code. I am stuck at this point. Open function is giving an error. Kindly help me. I tried to open the excel file in my c# code. But somehow it is saying Open function overload method doesn't take one argument. How to open and read the file?

string Filepath = @Filename;
Excel.Application excelapp = new Excel.Application();
excelapp.Visible = true;
var MyBook = excelapp.Workbooks.Open(Filepath);

It will be really helpful if somone gives a pseudocode for the same.

Hi, I was able to open the file.

string Filepath = Path.Combine(Directory.GetCurrentDirectory(), Filename);
Excel.Application excelapp = new Excel.Application();
excelapp.Visible = true;
var Workbook = excelapp.Workbooks.Open(Filepath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0,true,1, 0);

var xlWorkSheet = (Excel.Worksheet)Workbook.Worksheets.get_Item(2); Excel.Range range = xlWorkSheet.UsedRange;

But when I try to store the cell value in my variable, it gives me an error. I somehow cannot use Cells.value. I tried using the following but not able to store the data. Can anybody help?

uint pbaudRate2 = Convert.ToUInt32(range.Value2.ToString());

Answer 1

If you can make a slight change in your excel file, there is a much easier way to read the data. You need to have Column names in the first row (any names will do e.g. "ColumnName", "ColumnValue"), and data in subsequent rows. Then you can use code like this:

string xlConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=yourfullfilepathandname.xlsx;Extended Properties='Excel 8.0;HDR=Yes;';";
var xlConn = new OleDbConnection(xlConnStr);

var da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", xlConn);
var xlDT = new DataTable();
da.Fill(xlDT);

You will now be able to access the values from the DataTable. In your case: xlDT.Rows[1][1] will hold the value of address (in this case 30). One thing to note: numbers in an Excel spreadsheet need to be retrieved as doubles and then cast if you want something else:

int myAddress = (int)(double)xlDT.Rows[1][1]; 

Answer 2

Here is a small demo how to read a range of Excel cells

    //  cf. http://support.microsoft.com/kb/302084/en-us
    Excel.Application XL = new Microsoft.Office.Interop.Excel.Application();
    Excel._Workbook WB = XL.Workbooks.Open(fileName, ReadOnly: true);
    Excel._Worksheet WS = (Excel._Worksheet)WB.Sheets[sheetName];
    Excel.Range R = WS.get_Range(GetAddress(row1, col1), GetAddress(row2, col2));
    object[,] arr = (object[,])R.Value;


    ....

    private string GetAddress(int rowNumber, int columnNumber)
    {
        int dividend = columnNumber;
        string columnName = String.Empty;
        int modulo;

        while (dividend > 0)
        {
            modulo = (dividend - 1) % 26;
            columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
            dividend = (int)((dividend - modulo) / 26);
        }

        return columnName + rowNumber;
    }

The cell values are copied to a 2D array of objects. Further processing depends on the value types.

Note that the transfer of an array is much faster than a nested loop of single cell copy operations.