Retrieving excel sheet cell value

I am usi开发者_运维知识库ng c# to read the value of particular cell of excel sheet. But as per my code I am not getting any value..

My code for getting the cell value is:

foreach (DataRow dr in sourceds.Tables[0].Rows) {
    while (clmn < dr.ItemArray.Count()) {
        value = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[row,clmn]).Text.ToString();
        ws.Cells[row, clmn] = value;
        ws.Cells[row, clmn] = value;
        clmn++;
    }
    row++;
}
wb.Save();

Here I am reading the cell from other sheet and want to insert that value in other sheet.

but I am not getting any value of "value"..

The .Text parameter will only have a value if the contents are text. Generally you want to use .Value.ToString() instead.

You mentioned getting the value from another sheet. Where do you set the sheet? You should be able to do something like wb.Sheets[0].Cells[row,clmn].Value.ToString() (assuming you want data from the first sheet).

Looking at this code you are copying items cell by cell. It's much easier to use the Select/Copy/Paste functionality instead:

Range r1 = wb.Sheets[0].Cells["C1", "E5" ]; // define corners of selection square
Range r2 = wb.Sheets[1].Cells["A1"];        // destination
r1.Select();
r1.Copy(r2);

You can try this...

MyCellValue = (((Microsoft.Office.Interop.Excel.Range)oWorkSheet.Cells[myRow, myCol]).Value2 != null ? ((Microsoft.Office.Interop.Excel.Range)oWorkSheet.Cells[myRow, myCol]).Value2.ToString().Trim() : "");

You check if the value is not null so you don't get an exception. The myCel variable can be either number (indicating the numebr of the column) or string (indicating the name of the column i.e. "A"). myRow variable is the number of the row.

1. Add Microsoft.Office.Interop.Excel.dll in your application.

2. Write following line of code in your method/or use this method:

        private void readExcel() 
        {
         object _row = 1;  
         object _column = 1;   
         Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();  
         excelApp.Visible = false;  
         excelApp.ScreenUpdating = false;  
         excelApp.DisplayAlerts = false;  
         Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(@"D:\\Book1.xlsx", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);  
         Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
         string currentSheet = "Sheet1";  
         Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);  
         Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.UsedRange;  
         string sValue = (range.Cells[_row, _column] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();  
         //sValue has your value  
        }