正在按条件读取Excel文件

本文关键字：Excel 文件 读取 条件 | 更新日期: 2023-09-27 18:27:12

嗨，我正在编写一个WinForm应用程序，我想读取一个excel文件。我的特长是这样的：

------------------------------------------------------
first_name |last_name|ID      |Skill   |exam_date |certification_number|
john       |  smith  |12345678|engineer|2013/12/12|3543546647
john       |  smith  |12345678|electronic|2013/07/12|35477776647
.....
.....

因为我的excel没有主键，正如你所看到的，对于一个人，我可以有几行（最多20行），前3列是相同的。

我写这段代码是为了读取excel，但它只读取一行。如何读取具有相同ID的所有行？

string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:'book3.xlsx;Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
        DataSet ds = new DataSet();
        OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [SHEET1$]", ConnectionString);
        adapter.Fill(ds);
        DataRow dataRow = (from DataRow dr in ds.Tables[0].Rows where dr["ID"].ToString() == textBox1.Text select dr).FirstOrDefault();

提前感谢

正在按条件读取Excel文件

您的FirstOrDefault（）只选择'First'

试试这个：

IEnumerable<DataRow> dataRows = (from DataRow dr in ds.Tables[0].Rows where dr["ID"].ToString() == textBox1.Text select dr);
foreach (DataRow dataRow in dataRows)
{
      // do stuff with current dataRow
}

var query = from DataRow dr in ds.Tables[0].Rows where dr["ID"].ToString() == textBox1.Text select dr;
foreach (DataRow dataRow in query.ToList())
{
      // do something with dataRow 
}

在我的C#应用程序中，我使用EPPLUS库(http://epplus.codeplex.com/)我想说它真的很好很快。

以下是我使用EPPLUS打开文件表并将数据加载到DataTable:的代码

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Reflection;
using System.Text;
using System.Globalization;
using System.Diagnostics;
using System.Data;
using System.Windows;
using System.Windows.Controls;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Drawing.Chart;
using System.Xml;
public class ProjectLoad {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        public void Load ( string filePath ) {
            // Get the file we are going to process
            FileInfo existingFile = new FileInfo( filePath );
            try {
                if ( existingFile.Exists == true ) {
                    // Open and read the XlSX file.
                    using ( ExcelPackage package = new ExcelPackage( existingFile ) ) {
                        // Get the work book in the file
                        ExcelWorkbook workBook = package.Workbook;
                        if ( workBook != null ) {
                            if ( workBook.Worksheets.Count > 0 ) {
                                // read some data
                                int sheet_number = 0;
                                DataTable table = load_sheet_toDataGrid( workBook.Worksheets[sheet_number] );
                            }
                        }
            } catch ( System.IO.IOException ) {
                //error message
            }
        }
        /// <summary>
        /// loads the content of a sheet into a datatable
        /// </summary>
        /// <param name="currentWorksheet"></param>
        /// <returns></returns>
        private DataTable load_sheet_toDataGrid ( ExcelWorksheet currentWorksheet ) {
            DataTable dt = new DataTable( );
            int rows = currentWorksheet.Dimension.End.Row;
            int cols = currentWorksheet.Dimension.End.Column;
            //Add columns
            for ( int c = 0 ; c < cols ; c++ ) {
                dt.Columns.Add( currentWorksheet.Cells[0 + 1 , c + 1].Value.ToString( ) );
            }
            //add values
            for ( int r = 1 ; r < rows ; r++ ) {
                object[] ob = new object[cols];
                for ( int c = 0 ; c < cols ; c++ ) {
                    double value;
                    bool isDouble = Double.TryParse( currentWorksheet.Cells[r + 1 , c + 1].Value.ToString( ) , out value );
                    bool isDate = false;
                    DateTime date = new DateTime( );
                    if ( c == 0 && !isDouble ) {
                        isDate = DateTime.TryParse( currentWorksheet.Cells[r + 1 , c + 1].Value.ToString( ) , out date );
                    }
                    if ( isDouble ) {
                        ob[c] = value;
                    } else if ( isDate ) {
                        ob[c] = date;
                    } else {
                        ob[c] = currentWorksheet.Cells[r + 1 , c + 1].Value;
                    }
                }
                dt.Rows.Add( ob );
            }
            return dt;  
       }
}
相关文章：