Excel file connection

Question

4.00/5 (1 vote)

See more:

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.

C#

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

C#

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();

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?

Posted 15-Feb-12 16:22pm

Member 8642100

Updated 30-Apr-12 9:05am

Maciej Los

v3

Add a Solution

Comments

Dr.Walt Fair, PE 15-Feb-12 23:20pm

Fixed formatting.

Member 8642100 16-Feb-12 0:04am

fixed formatting?

Member 8642100 16-Feb-12 0:05am

I'm sorry but how do i go about with the fixed formatting?

thatraja 16-Feb-12 0:13am

No, he just beautified your question for more readability.
Anyway wait for the responses

Member 8642100 16-Feb-12 0:23am

ouh ok..thanks

MadhuCodePro 16-Feb-12 1:06am

You want to connect the file which was chosen by the end user?

Member 8642100 16-Feb-12 1:10am

yup..is there any way?

Member 8642100 16-Feb-12 1:11am

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();

RDBurmon 16-Feb-12 4:13am

I suspect it should work. should it ?
or have you encountered any issue ?

Member 8642100 16-Feb-12 4:27am

yup..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?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-04-30T09:30:00

Question:

How to extract the month and year from excel column?

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

SQL

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

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

VB

Dim sSQL as String = String.Empty
sSQL = "SELECT [Arrival]) FROM [Sheet1$] WHERE MONTH([Arrival]) = " & Me.ComboBox1.SelectedValue

Results: all arrival dates which month is equal to Me.ComboBox1.SelectedValue

C#

C#

String sSQL = String.Empty
sSQL = "SELECT [Arrival]) FROM [Sheet1$] WHERE MONTH([Arrival]) = " + Me.ComboBox1.SelectedValue

Results: all arrival dates which month is equal to Me.ComboBox1.SelectedValue

Becouse i have no idea, where do you want to present your results, i can't help you more. I recomend you to use DataGridView[^] component.