在Datagridview中显示excel文件内容

本文关键字：文件 excel 显示 Datagridview | 更新日期: 2023-09-27 18:07:47

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 = "";
      strConnectionString = "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
  {
  }
  }

我发现源代码绑定一个excel文件的DataGridView对象在互联网上的一个winform应用程序。然而，我想知道是否有其他方法可以做到这一点，而不是使用ADO-或任何SQL-related procedures，感谢任何帮助。

在Datagridview中显示excel文件内容

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

您可以直接使用Excel COM Interop或通过一些已经存在的包装器使用Excel本身读取文件。但是我觉得你现在的做法很合理。

您所显示的代码绑定到excel文件的普通DataTable。这意味着您可以以任何您想要的方式构建DataTable并将其绑定到excel文件。它不依赖于ADO或过程。

或者我没有理解你的问题。

使用Ms-Office互操作API

你可以使用这个(不要忘记引用Microsoft.Office.Interop.Excel)。可能会有一些额外的代码，因为我把它从我的工具，但总体思路是使用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;
            }
        }
    }
相关文章：