Excel文件连接 [英] Excel file connection

问题描述

你好，

我正在使用Windows窗体应用.NET 4.0 Framework.我需要上传一个Excel文件.用户将从浏览按钮中选择一个文件.我有另一个按钮供用户保存在我的应用程序文件夹中.现在的问题是我开始为Excel编写代码时遇到问题，因为我将从文本框获取路径并将文件保存在指定的文件夹中.我知道使用Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";进行Excel连接，但是数据源是由用户选择的，我必须从名为txtbxBrowse的文本框中获取路径.

有人可以帮我吗?到目前为止，这是我设法完成的浏览按钮.

Hi there,

I''m using windows form appliction .NET 4.0 Framework. I need to upload an Excel file. The user will choose a file from browse button. I have another button for the user to save within my application folder. The problem now is I have problem starting to write the code for Excel as I will be getting the path from a TextBox and save the file in my designated folder. I know Excel connection by using Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"; but the data source is chosen by the user and I have to get the path from the TextBox called txtbxBrowse.

Can somebody help me please? So far this is what I managed to do for the browse button.

OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.Title = "Open Excel File";
            fdlg.InitialDirectory = @"c:\";
            fdlg.Filter = "allfiles|*.xls|Excel file|*.xlsx";
            fdlg.FilterIndex = 2;
            fdlg.RestoreDirectory = true;
            if (fdlg.ShowDialog() == DialogResult.OK)
            {
                txtbxBrowse.Text = fdlg.FileName;
            }

在此先感谢


到目前为止，这是我所做的.不知道它是否有效

Thanks in advance


this is what I''ve done so far. Don''t know it works or not

string fileName = txtbxBrowse.Text;
OleDbConnection cnn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties= 'Excel 8.0;HDR=Yes;Data Source=" + fileName);
try {
    OleDbDataAdapter adp = new OleDbDataAdapter();
    OleDbCommand con = new OleDbCommand("SELECT [Arrival] from [Sheet1$]",cnn);
    cnn.Open();
    OleDbDataReader dr = con.ExecuteReader();

当我选择到达列时，我需要格式化该表的日期:dd/mm/yyyy.
转换后，我需要根据用户选择的下拉组合框检查月份和年份.
现在，我被困住了.我不知道如何仅从excel列中提取月份和年份，并与组合框下拉列表中选择的月份进行比较.

您对从哪里开始有什么建议?

When i select the arrival column, i need to format that table to date: dd/mm/yyyy.
Once i convert, i need to check the month and year against the dropdown combo box chosen by user.
Now i''m terribly stuck. I have no idea how to only extract the month and year from excel column and compare with the month selected in combobox dropdown.

Do you have any suggestion where to begin?

推荐答案

"，cnn)； cnn.Open(); OleDbDataReader dr = con.ExecuteReader();

",cnn); cnn.Open(); OleDbDataReader dr = con.ExecuteReader();

当我选择到达列时，我需要格式化该表的日期:dd/mm/yyyy.
转换后，我需要根据用户选择的下拉组合框检查月份和年份.
现在，我被困住了.我不知道如何仅从excel列中提取月份和年份，并与组合框下拉列表中选择的月份进行比较.

您对从哪里开始有什么建议?

When i select the arrival column, i need to format that table to date: dd/mm/yyyy.
Once i convert, i need to check the month and year against the dropdown combo box chosen by user.
Now i''m terribly stuck. I have no idea how to only extract the month and year from excel column and compare with the month selected in combobox dropdown.

Do you have any suggestion where to begin?

问题:

如何从Excel列中提取月份和年份?

How to extract the month and year from excel column?

只需实现...您的命令应如下所示:

It is simply to achieve... Your command should looks like:

SELECT Month([Arrival]) As [Month], Year([Arrival]) As [Year], [Arrival]) FROM [Sheet1

如果要比较组合框的月份(并且组合框包含1到12的数字形式)与excel文件中的到达日期，则命令应如下所示:
VB

If you would like to compare month from combobox (and combobox contains numerics form 1 to 12) with arrival date in excel file, your command should looks like:
VB

Dim sSQL as String = String.Empty
sSQL = "SELECT [Arrival]) FROM [Sheet1

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