如何从excel数据中找出列的最大值和数据类型 [英] how to find out max value and data type for columns from excel data

问题描述

嗨朋友，



我正在执行一项从excel读取数据的任务，需要找到列标题和相应的数据类型以包含基于最大值的数据列。

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 .

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

                               }

能帮助你实现这一目标吗？



问候

Arul.R

Can you please any one help to achieve this.

regards
Arul.R

推荐答案

而不是定义HDR =否为什么不让驱动程序获取列名（ HDR = YES）。使用此数据表中的列名称应与Excel中的列名称相对应。



填充数据表后，最大值是什么，使用 dataview [ ^ ]。类似

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

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

有关详细信息，请参阅 DataView.Sort [ ^ ]

这篇关于如何从excel数据中找出列的最大值和数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！