locked
Reading Excel FIle RRS feed

  • Question

  • User1224235962 posted

    Hi All,

    I have one excel file D:\ABC.XLS

    I wanted display the Cell A5 value in one text box, Please suggest

    Monday, July 20, 2015 7:10 AM

Answers

  • User-1599850035 posted

    Here is an easy solution, imports free .NET Excel library in your program, then use follow code to get the value

     private void getValue_Click(object sender, EventArgs e)
            {   
                //using Spire to load Excel
                Workbook wb = new Workbook();
                wb.LoadFromFile("sample.xlsx");
                //get the sheet
                Worksheet sheet=wb.Worksheets[0];
                //return value of C5 to text box
                textBox1.Text= sheet.Range["C5"].Value;
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 23, 2015 2:59 AM

All replies

  • User1738843376 posted

    you can read from an Excel using ADODB:

    https://www.daniweb.com/software-development/vbnet/code/474907/how-to-read-an-excel-spreadsheet-using-adodb

    Then you just need to assign the value to the textbox

    Monday, July 20, 2015 7:52 AM
  • User-219423983 posted

    Hi Sandy1234,

    I think you also could use the “Microsoft.Office.Interop.Excel” to get the cell value. The following code you could take a look.

            public static string GetExcelOneCell(string fileName, int row, int column)
            {
                Microsoft.Office.Interop.Excel.Application app = new Application();
                Workbook wbook = app.Workbooks.Open(fileName, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                Worksheet workSheet = (Worksheet)wbook.Worksheets[1];
                string temp = ((Range)workSheet.Cells[row, column]).Text.ToString();
                wbook.Close(false, fileName, false);
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                GC.Collect();
                return temp;
            }
    

    Then you could use the following code to get the value

                string filepath = @" D:\ABC.XLS";
                this.tbxResult.Text = ComFunctions.GetExcelOneCell(filepath, 5, 1);
    

    I hope it’s useful to you.

    Best Regards,

    Weibo Zhang

    Tuesday, July 21, 2015 5:30 AM
  • User1738843376 posted

    Weibo Zhang

    I think you also could use the “Microsoft.Office.Interop.Excel” to get the cell value. The following code you could take a look.

    Interop components force to have Excel installed on the server in order to work, while ADODB don't. If you are sure that MS Excel is installed, then its an option, but the safe way to go is through ADO

    Tuesday, July 21, 2015 5:33 AM
  • User1226461060 posted
     
    You can use following code to read xls file.
    // need to pass relative path after 
                    string path = filePath;
    
    
                    if (Path.GetExtension(path) == ".xls")
                    {
                        oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
                    }
                    else if (Path.GetExtension(path) == ".xlsx")
                    {
                        oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
                    }
                    oledbConn.Open();
                    OleDbCommand cmd = new OleDbCommand(); ;
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    DataSet ds = new DataSet();
    
                    // passing list to drop-down list
    
                    // selecting distinct list of Slno 
                    cmd.Connection = oledbConn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "SELECT * FROM [Sheet1$]";// xls sheet name
                    oleda = new OleDbDataAdapter(cmd);
                    oleda.Fill(ds, "dsSlno");
    
    Thanks
    Munir
    

    Tuesday, July 21, 2015 6:17 AM
  • User-219423983 posted

    Hi 0belix,

    Interop components force to have Excel installed on the server in order to work, while ADODB don't.

    Yes, you’re right. The Microsoft. Office. Interop. Excel is not the best way to operate excel files on the server. Besides, the Open XML SDK could also be a better solution as ADODB you suggested above. But just about this issue, Microsoft. Office. Interop. Excel is also an option to achieve the goal. Thank you for your reminder.

    I hope it’s useful to you.

    Best Regards,

    Weibo Zhang

    Tuesday, July 21, 2015 9:07 AM
  • User-1599850035 posted

    Here is an easy solution, imports free .NET Excel library in your program, then use follow code to get the value

     private void getValue_Click(object sender, EventArgs e)
            {   
                //using Spire to load Excel
                Workbook wb = new Workbook();
                wb.LoadFromFile("sample.xlsx");
                //get the sheet
                Worksheet sheet=wb.Worksheets[0];
                //return value of C5 to text box
                textBox1.Text= sheet.Range["C5"].Value;
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 23, 2015 2:59 AM