Display excel file content in a Datagridview

private void button1_Click(object sender, EventArgs e)
{
   DataTable test = getDataFromXLS("c:\temp.xls");
   if (test != null)
   dataGridView1.DataSource = test;
}


private DataTable getDataFromXLS(string strFilePath)
{
   try
   {
      string strConnectionString = "";
      strCon开发者_Python百科nectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                 "Data Source=" + strFilePath + "; Jet OLEDB:Engine Type=5;" + "Extended Properties=Excel 8.0;";
      OleDbConnection cnCSV = new OleDbConnection(strConnectionString);
      cnCSV.Open();
      OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [Sheet1$]", cnCSV);
      OleDbDataAdapter daCSV = new OleDbDataAdapter(); daCSV.SelectCommand = cmdSelect;
      DataTable dtCSV = new DataTable();
      daCSV.Fill(dtCSV);
      cnCSV.Close();
      daCSV = null;
      return dtCSV;
  }
  catch (Exception ex)
  {
      return null;
  }
  finally
  {
  }
  }

I found that source code to bind an excel file to a DataGridView object on a winform application on the Internet. Yet, I would like to know if there are other ways to do this instead of using ADO- or any SQL-related procedures, thanks for any help.

public void CreateDataTableForExcelData(String FileName) 
{
OleDbConnection ExcelConnection = null;
        string filePath = Server.MapPath(Request.ApplicationPath + "/UploadedFile/");
        DataTable dtNew = new DataTable();
        string strExt = "";
        strExt = FileName.Substring(FileName.LastIndexOf("."));
        if (strExt == ".xls")
        {
            ExcelConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + hdnFileName.Value + ";Extended Properties=Excel 8.0;");
        }
        else
        {
            if (strExt == ".xlsx")
            {
                ExcelConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + hdnFileName.Value + ";Extended Properties=Excel 12.0;");
            }
        }
        try
        {
            ExcelConnection.Open();
            DataTable dt = ExcelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + ddlTableName.SelectedValue + @"]", ExcelConnection);
            OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
            DataSet ExcelDataSet = new DataSet();
            ExcelAdapter.Fill(dtExcel);
            ExcelConnection.Close();

        }
        catch (Exception ex)
        {

        }
        finally
        {
        }
}

You can use Excel COM Interop directly of thru some already existing wrappers to read files using Excel itself. However I find you current approach quite reasonable.

The code you're showing binds an ordinary DataTable to the excel file. This means you can build your DataTable in any way you want and bind it to the excel file. There's no dependency or whatsoever on ADO or procedures.

Or maybe I just don't understand your question.

Use Ms-Office InterOp API.

You can use this(dont forget to reference Microsoft.Office.Interop.Excel. There may be some extra code, because i took it from my tool, but overall idea is to use Excel COM):

public ArrayList ProcessWorkbook(string filePath)
        {
            string file = filePath;

            Excel.Application excel = null;
            Excel.Workbook wkb = null;
            ArrayList al = new ArrayList();
            try
            {
                excel = new Excel.Application();

                wkb = ExcelTools.OpenBook(excel, file, false, true, false);

                Excel.Worksheet sheet = wkb.Sheets["Adresses"] as Excel.Worksheet;

                Excel.Range range = null;

                if (sheet != null)
                    range = sheet.get_Range("A1:X6702", Missing.Value);


                if (range != null)
                {
                    foreach (Excel.Range r in range)
                    {
                        al.Add(r.Text);
                    }
                }
            }
            catch (Exception ex)
            {
                //if you need to handle stuff
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (wkb != null)
                    ExcelTools.ReleaseRCM(wkb);

                if (excel != null)
                    ExcelTools.ReleaseRCM(excel);
            }
            return al;
        }

//----------------
    public static class ExcelTools
    {
        public static Excel.Workbook OpenBook(Excel.Application excelInstance, string fileName, bool readOnly, bool editable,
        bool updateLinks)
        {
            Excel.Workbook book = excelInstance.Workbooks.Open(
                fileName, updateLinks, readOnly,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            return book;
        }

        public static void ReleaseRCM(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch
            {
            }
            finally
            {
                o = null;
            }
        }
    }