CODEWITHSUNDEEP
c#excelc#-4.0
SRN
SRN

Reputation: 23

How to find maximum value from an excel column?

I have values like below in excel sheet.

ID
12_001
12_008
12_010

13_001

How to find max of these values. I need the result as '13_001'. Can anybody help me?

I tried like this

string ExcelConnection = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + Filepath + " ; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
OleDbDataAdapter Id = new OleDbDataAdapter("SELECT MAX(ID) FROM [Sheet1$A2:A4]", ExcelConnection);
DataSet id = new DataSet();
Id.Fill(id);

Upvotes: 1

Views: 4194

Answers (4)

rangan
rangan

Reputation: 1

This at best can be done through a macro. In the below listing I have different values in Range A1:A10 like: 12_001 56_021 89_001 10_002 41_005 36_021 95_002 25_025 32-015 85-002 I am extracting the maximum value thro the following code. The maximum value is displayed in a Message Box:

For i = 1 To 10
If Val(Left(Cells(i, 1), 2)) > Max Then
Max = Val(Left(Cells(i, 1), 2))
Strg = Max & Right(Cells(i, 1), Len(Cells(i, 1)) - 2)
End If
Next
MsgBox Strg

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

Basically, you can get the desired output by treating the strings as text, sorting them alphabetically in descending order and then retrieving the first record from column ID.

Below code should do it:

        string ExcelConnection = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + Filepath + " ; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
        string Command = "SELECT ID FROM [Sheet1$]";

        try
        {
            OleDbConnection conn = new OleDbConnection(ExcelConnection);
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da = new OleDbDataAdapter();

            using (conn)
            {
                conn.Open();
                cmd = new OleDbCommand(Command, conn);
                da = new OleDbDataAdapter(cmd);


                DataSet id = new DataSet();
                da.Fill(id);

                DataTable idtable = id.Tables[0];
                idtable.DefaultView.Sort = idtable.Columns[0].ColumnName + " " + "DESC";
                idtable = idtable.DefaultView.ToTable();
                Console.WriteLine(idtable.Rows[0][0]);
                conn.Close();
            }
        }
        catch (Exception ex)
        {
        }

Upvotes: 0

soheil bijavar
soheil bijavar

Reputation: 1213

you can use the range if u need 

    range rng = new range; 
int64  dblMax =0; rng = Range("a1", Range("a65536").End(xlUp)) ;
dblMax = Application.WorksheetFunction.Max(rng) ;
return dblMax + 1

Upvotes: 2

user1968030
user1968030

Reputation:

If you can send function you can use this:

=MAX(INDEX(Name1!$C$2:$V$2000,0,MATCH(Overview!S$1,ID!$C$1:$V$1,0)))

Upvotes: 0

Related Questions