Import excel sheet data to datagrid without using oledb

Question

In my Windows based application(C#) i want to import excel sheet to show its data in DatatGridView i dont want to use oledb

any Help

Answer 1

Have a look at the "MS Word/Excel Documents Manipulation" section of the followign question: Most useful free .NET libraries?

Answer 2

    using Excel = Microsoft.Office.Interop.Excel;

You'll obviously need to add the reference to your project, and then it's plain simple :)

    private void ProcessExcel(string filepath)
    {

            Excel.ApplicationClass ExcelObj = new Excel.ApplicationClass();

            Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(filepath, 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);

            Excel.Sheets sheets = theWorkbook.Worksheets;

            Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);

            Excel.Range range = worksheet.UsedRange;

            System.Array myvalues = (System.Array)range.Cells.Value2;

            int vertical = myvalues.GetLength(0);
            int horizontal = myvalues.GetLength(1);



            string[] headers = new string[horizontal];
            string[] data = new string[horizontal];


            DataTable ResultsHeader = New DataTable();
            DataSet ds = New DataSet();


            for (int x = 1; x <= vertical; x++)
            {
                    Utils.inicializarArrays(datos);
                    for (int y = 1; y <= horizontal; y++)
                    {
                        if (x == 1)
                        {
                            headers[y - 1] = myvalues.GetValue(x, y).ToString();
                        }
                        else
                        {
                            string auxdata = "";
                            if (myvalues.GetValue(x, y) != null)
                                auxdata = myvalues.GetValue(x, y).ToString();
                            data[y - 1] = auxdata;
                        }

                    }
                    if(x == 1) //headers
                    {
                            for(int w = 0; w < horizontal; w++)
                            {
                                    ResultsHeader.Columns.Add(New DataColumn(headers[w], GetType(string)));
                            }
                            ds.Tables.Add(ResultsHeader);
                    }
                    else
                    {
                            DataRow dataRow = ds.Tables[0].NewRow();
                            for(int w = 0; w < horizontal; w++)
                            {
                                    dataRow(headers[w]) = data[w]
                            }
                            ds.Tables[0].Rows.Add(dataRow);
                    }
            }
            DataView myDataView = new DataView();
            myDataView.Table = ds.Tables[0];
            MydataGrid.CurrentPageIndex = 0;
            MydataGrid.DataSource = myDataView;
            MydataGrid.DataBind();
    }

Answer 3

I may suggest you this solution way...

Save your excel file as csv formatted.

Use csv reader library at http://www.codeproject.com/KB/database/CsvReader.aspx It has lots of method to use as datasource for datagrid e.g.

Answer 4

I would save the Excel sheet using the CSV format, then parse it using this free fast CSV Reader C# implementation.