Reading Excel FIle

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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;
        }