读取列最后一行特定单元格的数据 [英] Read column last row particular cell's data

问题描述

我有以下代码来读取excel文件中的所有记录。但是，如何检索列最后一行的特定单元格数据？以下是我的代码：

  public   static  DataTable ReadExcel（）
 {
  string  filename = SourceFolder + excelfile; 
 Excel.Application app =  new  Excel.Application（）; 
 Excel.Workbook workBook = app.Workbooks.Open（filename， 0 ， true ，< span class =code-digit> 5 ， ， ， true ，Excel.XlPlatform.xlWindows，< span class =code-string>  \t， false ，< span class =code-keyword> false ， 0 ， true ， 1 ， 0 ）; 
 workBook.SaveCopyAs（filename）; 
 workBook.Close（ false ，System.Reflection.Missing.Value，System.Reflection.Missing.Value）; 
  //   workBook.Close（true）;  
 
 DataTable fromExcelDT =  new  DataTable（）; 
  //   string myConnection =Provider = Microsoft.Jet.OLEDB.4.0; Extended Properties = Excel 8.0;数据源=+C：/Users/visual/Desktop/Test1/t1.xlsx+;;  
  string  myConnection =   Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + filename +  ;扩展属性= \Excel 12.0; HDR = YES; IMEX = 1; \; 
 OleDbConnection conn =  new  OleDbConnection（myConnection）; 
 conn.Open（）; 
 
 DataTable schemaTable = conn.GetOleDbSchemaTable（OleDbSchemaGuid.Tables， new   object  [ ] { null ， null ， null ，   TABLE}）; 
  string  SheetName; 
 
  if （schemaTable.Rows.Count ==  12 ）
 {
 SheetName = schemaTable.Rows [ 11 ] [ 2 ]。ToString（）; 
} 
  else  
 {
 SheetName = schemaTable.Rows [ 10 ] [ 2 ]。ToString（）; 
} 
 
 StringBuilder SelectStatement =  new  StringBuilder（）; 
 SelectStatement.Append（  SELECT * FROM [）。Append（SheetName）.Append （ ]）; 
 OleDbCommand cmd =  new  OleDbCommand（SelectStatement.ToString（），conn）; 
 
 
 DataSet dataset =  new  DataSet（）; 
 OleDbDataAdapter adapter =  new  OleDbDataAdapter（cmd）; 
 adapter.Fill（fromExcelDT）; 
 conn.Close（）; 
 
 返回 fromExcelDT; 
} 
 
  public   static   void  lastRow（）
 {
 DataTable ExcelDT = ReadExcel（）; 
  //  在此检索 
} 

解决方案

尝试如下

  string  lastColvalue = dataTable.Rows [dataTable.Rows.Count-1] [  ColName]。ToString（） ; 

in .net 3.5 +

  var  lastrow = dataTable.AsEnumerable（）。Last（）; 
  var  lastColvalue = lastrow.Field< string>（  COLNAME）; 

Hi, i have the following codes which is to read all the records in excel file. But how can i retrieve a column last row's particular cell's data? Below are my codes:

public static DataTable ReadExcel()
{
    string filename = SourceFolder + excelfile;
    Excel.Application app = new Excel.Application();
    Excel.Workbook workBook = app.Workbooks.Open(filename, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    workBook.SaveCopyAs(filename);
    workBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
    //workBook.Close(true);

    DataTable fromExcelDT = new DataTable();
    //string myConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + "C:/Users/visual/Desktop/Test1/t1.xlsx" + ";";
    string myConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"";
    OleDbConnection conn = new OleDbConnection(myConnection);
    conn.Open();

    DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    string SheetName;

    if (schemaTable.Rows.Count == 12)
    {
        SheetName = schemaTable.Rows[11][2].ToString();
    }
    else
    {
        SheetName = schemaTable.Rows[10][2].ToString();
    }

    StringBuilder SelectStatement = new StringBuilder();
    SelectStatement.Append("SELECT * FROM [").Append(SheetName).Append("]");
    OleDbCommand cmd = new OleDbCommand(SelectStatement.ToString(), conn);


    DataSet dataset = new DataSet();
    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
    adapter.Fill(fromExcelDT);
    conn.Close();

    return fromExcelDT;
}

public static void lastRow()
{
   DataTable ExcelDT = ReadExcel();
   //retrieve here
} 

解决方案

try like below

string lastColvalue= dataTable.Rows[dataTable.Rows.Count-1]["ColName"].ToString();

in .net 3.5 +

var lastrow= dataTable.AsEnumerable().Last();
var lastColvalue= lastrow.Field<string>("ColName");

这篇关于读取列最后一行特定单元格的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！