how to find out max value and data type for columns from excel data

Question

2.00/5 (1 vote)

See more:

Hi Friends,

I am doing one task reading data from excel and need to find columns header and respective Data type for contain data based on max value of the columns .

C#

string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", s);
                   string flag = "";
                   using (OleDbConnection con = new OleDbConnection(connString))
                   {
                       using (OleDbCommand cmd = new OleDbCommand())
                       {
                           //Read the First Sheet
                           cmd.Connection = con;
                           con.Open();
                           DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                           con.Close();
                           string firstSheet = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

                           //Read the Header Row
                           cmd.CommandText = "SELECT   * From [" + firstSheet + "]";

                           using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                           {
                               DataTable HeaderColumns = new DataTable();
                               da.SelectCommand = cmd;
                               da.Fill(HeaderColumns);
                               //DataSet ds = new DataSet();
                               //da.Fill(ds);
                               //ds.WriteXml(@"D:\test1.xml");

                               foreach (DataColumn column in HeaderColumns.Columns)
                               {

            string columnName = HeaderColumns.Rows[0][column.ColumnName].ToString();

            string coltyp = HeaderColumns.Rows.ToString();

                               }

Can you please any one help to achieve this.

regards
Arul.R

Posted 15-Feb-15 17:11pm

Arul R Ece

Updated 15-Feb-15 17:39pm

Sushil Mate

v2

Add a Solution

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)

Wendelius · Answer 1 · 2015-02-15T17:32:00

Instead of defining HDR=No why not let the driver to get the column names (Hdr=YEs). Using this the column names in your datatable should correspond the ones in Excel.

What comes to the maximum values, after filling the datatable, selct the max using a dataview [^]. Something like

C#

...
   DataTable AllData = new DataTable();
   da.SelectCommand = cmd;
   da.Fill(AllData);
   AllData.DefaultView.Sort = "ColumnName DESC"
...

For more info, see DataView.Sort[^]