导入Excel文件的DataGridView [英] Importing Excel File to DataGridView

问题描述

 打开文件对话框ofImport =新的OpenFileDialog（）; 
 ofImport.Title =选择文件; 
 ofImport.InitialDirectory = @C：\; 
 ofImport.FileName = txtFileName.Text; 
 ofImport.Filter =Excel工作表（* XLSX）| *的.xlsx |所有文件（*。*）| *。*; 
 ofImport.FilterIndex = 1; 
 ofImport.RestoreDirectory = TRUE; 
 
如果（ofImport.ShowDialog（）== DialogResult.OK）
 {
 
路径字符串= System.IO.Path.GetFullPath（ofImport.FileName）; 
查询字符串=SELECT * FROM Customer.xlsx 
 OleDbConnection的康恩=新的OleDbConnection（）; 
 conn.ConnectionString = @供应商= Microsoft.ACE.OLEDB.12.0;数据源=+ ofImport.FileName +;扩展属性=+\的Excel 12.0的Xml; HDR = YES; IMEX = 1 \; 
 OleDbDataAdapter的适配器=新OleDbDataAdapter的（查询，康涅狄格州）; 
 
 // DataSet中的DataSet =新的DataSet（）; 
 adapter.Fill（dsSource）; 
 dataGridView1.DataSource = dsSource; 
 
} 
，否则
 {
 ofImport.Dispose（）; 
} 
  

我要以检索Excel数据的 DataGridView的使用集。 dsSource 被使用的数据集。

我obtaing的错误是上线 adapter.Fill（dsSource）; ：

Microsoft Access数据库引擎找不到对象XLSX 。
确保对象存在并且，您拼写其名称和路径
名称。如果XLSX不是本地对象，请检查网络
连接或与服务器管理员联系。

我能够选择该文件，但它不是在数据集中填写。

该怎么办？

解决方案

 的DialogResult的DialogResult = MessageBox.Show（当然，有些标题，MessageBoxButtons.YesNo）; 
如果（DialogResult的== DialogResult.Yes）
 {
 DT = dsSource.Tables [索引] 
 dt.Reset（）; 
 Excel.Workbook工作簿; 
 Excel.Worksheet NwSheet; 
 Excel.Range ShtRange; 
 Microsoft.Office.Interop.Excel.Application ExcelObj =新Microsoft.Office.Interop.Excel.Application（）; 
打开文件对话框filedlgExcel =新的OpenFileDialog（）; 
 filedlgExcel.Title =选择文件; 
 filedlgExcel.InitialDirectory = @C：\; 
 //filedlgExcel.FileName = textBox1.Text; 
 filedlgExcel.Filter =Excel工作表（* XLSX）| *的.xlsx |所有文件（*。*）| *。*; 
 filedlgExcel.FilterIndex = 1; 
 filedlgExcel.RestoreDirectory = TRUE; 
如果（filedlgExcel.ShowDialog（）== DialogResult.OK）
 {
 
 =工作簿ExcelObj.Workbooks.Open（filedlgExcel.FileName，Missing.Value，Missing.Value， 
 Missing.Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value，
 Missing.Value，Missing.Value，Missing.Value，Missing.Value，不知所踪。值，Missing.Value）; 
 NwSheet =（Excel.Worksheet）workbook.Sheets.get_Item（1）; 
 ShtRange = NwSheet.UsedRange; 
的for（int CNUM = 1; CNUM< = ShtRange.Columns.Count; CNUM ++）
 {
 dt.Columns.Add（新的DataColumn（（ShtRange.Cells [1，CNUM]作为Excel.Range）.Value2.ToString（）））; 
} 
 dt.AcceptChanges（）; 
的String [] = COLUMNNAMES新的String [dt.Columns.Count] 
的for（int i = 0; I< dt.Columns.Count;我++）
 {
 COLUMNNAMES [0] = dt.Columns [I] .ColumnName; 
} 
 //字符串[] = COLUMNNAMES（从DC IN dt.Columns.Cast<&DataColumn的GT;（）选择dc.ColumnName）.ToArray（）; 
 
 
为（INT RNUM = 2; RNUM< = ShtRange.Rows.Count; RNUM ++）
 {
的DataRow博士= dt.NewRow（）; 
的for（int CNUM = 1; CNUM< = ShtRange.Columns.Count; CNUM ++）
 {
如果（（ShtRange.Cells [RNUM，CNUM]作为Excel.Range）.Value2 ！= NULL）
 {
博士[CNUM  -  1] =（ShtRange.Cells [RNUM，CNUM]作为Excel.Range）.Value2.ToString（）; 
} 
} 
 dt.Rows.Add（DR）; 
 dt.AcceptChanges（）; 
} 
 workbook.Close（真，Missing.Value，Missing.Value）; 
 ExcelObj.Quit（）; 
 
 dataGridView1.DataSource = DT; 
  

OpenFileDialog ofImport = new OpenFileDialog();
ofImport.Title = "Select file";
ofImport.InitialDirectory = @"c:\";
ofImport.FileName = txtFileName.Text;
ofImport.Filter = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
ofImport.FilterIndex = 1;
ofImport.RestoreDirectory = true;

if (ofImport.ShowDialog() == DialogResult.OK)
{

     string path = System.IO.Path.GetFullPath(ofImport.FileName);
     string query = "SELECT * FROM Customer.xlsx";
     OleDbConnection conn = new OleDbConnection();
     conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ofImport.FileName+";Extended Properties=" + "\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
     OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);

     //DataSet dataSet = new DataSet();
     adapter.Fill(dsSource);
     dataGridView1.DataSource = dsSource;

}
else
{
     ofImport.Dispose();
}   

I want to retrive Excel data to the DataGridView using dataset. dsSource is the dataset used.

The Error I'm obtaing is on the line adapter.Fill(dsSource);:

The Microsoft Access database engine could not find the object 'xlsx'. Make sure the object exists and that you spell its name and the path name correctly. If 'xlsx' is not a local object, check your network connection or contact the server administrator.

I'm able to select the file but it is not Filling in dataset.

What to do?

解决方案

    DialogResult dialogResult = MessageBox.Show("Sure", "Some Title", MessageBoxButtons.YesNo);
    if (dialogResult == DialogResult.Yes)
    {
        dt = dsSource.Tables[Index];
        dt.Reset();
        Excel.Workbook workbook;
        Excel.Worksheet NwSheet;
        Excel.Range ShtRange;
        Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
        OpenFileDialog filedlgExcel = new OpenFileDialog();
        filedlgExcel.Title = "Select file";
        filedlgExcel.InitialDirectory = @"c:\";
        //filedlgExcel.FileName = textBox1.Text;
        filedlgExcel.Filter = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
        filedlgExcel.FilterIndex = 1;
        filedlgExcel.RestoreDirectory = true;
        if (filedlgExcel.ShowDialog() == DialogResult.OK)
        {

            workbook = ExcelObj.Workbooks.Open(filedlgExcel.FileName, Missing.Value, Missing.Value,
                 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
            ShtRange = NwSheet.UsedRange;
            for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
            {
                dt.Columns.Add(new DataColumn((ShtRange.Cells[1, Cnum] as Excel.Range).Value2.ToString()));
            }
            dt.AcceptChanges();
            string[] columnNames = new String[dt.Columns.Count];
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                columnNames[0] = dt.Columns[i].ColumnName;
            }
            //string[] columnNames = (from dc in dt.Columns.Cast<DataColumn>() select dc.ColumnName).ToArray();


            for (int Rnum = 2; Rnum <= ShtRange.Rows.Count; Rnum++)
            {
                DataRow dr = dt.NewRow();
                for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
                {
                    if ((ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2 != null)
                    {
                        dr[Cnum - 1] = (ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString();
                    }
                }
                dt.Rows.Add(dr);
                dt.AcceptChanges();
            }
            workbook.Close(true, Missing.Value, Missing.Value);
            ExcelObj.Quit();

            dataGridView1.DataSource = dt;  

这篇关于导入Excel文件的DataGridView的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！