多Excel文件导入DataGridView仅导入单个文件及导入规则优化问询

Let's tackle each problem and question you have, with practical code examples to get you sorted:

1. Why Your Program Stops After Importing One Excel File? #

Chances are, you're either overwriting your DataGridView's data source each time instead of appending rows, or you have a break/return statement inside your loop that exits after the first file. Here's how to fix it:

Solution: Combine Data from All Files First #

Instead of binding each file directly to the DataGridView, collect all data into a single DataTable first, then bind it once at the end:

// Initialize an empty DataTable to hold all combined data
DataTable combinedData = new DataTable();

// Loop through each Excel file in your directory
foreach (string filePath in yourExcelFileList)
{
    DataTable tempTable = ReadExcelData(filePath); // Your existing method to read one file

    if (combinedData.Columns.Count == 0)
    {
        // For the first file, copy both structure and data
        combinedData = tempTable.Copy();
    }
    else
    {
        // For subsequent files, append rows to the combined table
        foreach (DataRow row in tempTable.Rows)
        {
            combinedData.ImportRow(row);
        }
    }
}

// Finally, bind the combined data to your DataGridView
dataGridView1.DataSource = combinedData;

Quick Check: #

Double-check your loop code for any break or return statements that might exit the loop after processing the first file. Remove them if they're unnecessary.

2. Can You Use Worksheet Index Instead of [Sheet1$]? #

Absolutely! The approach depends on which library you're using to read Excel files:

Option 1: Using OleDb #

First, fetch all worksheet names from the Excel file, then pick the one by index:

using (OleDbConnection conn = new OleDbConnection(yourConnectionString))
{
    conn.Open();
    // Get the schema table containing worksheet info
    DataTable schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    
    // Collect valid worksheet names (filter out system tables)
    List<string> sheetNames = new List<string>();
    foreach (DataRow row in schema.Rows)
    {
        string sheetName = row["TABLE_NAME"].ToString();
        if (sheetName.EndsWith("$")) // Only include actual worksheets
            sheetNames.Add(sheetName);
    }

    // Use index 0 for the first worksheet
    string targetSheet = sheetNames[0];
    string query = $"SELECT * FROM [{targetSheet}]";
    
    // Proceed to execute the query...
}

Option 2: Using EPPlus (Recommended for .NET) #

EPPlus lets you access worksheets directly by index (note: indexes are 1-based by default, but you can use 0-based if preferred):

using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
{
    // Access the first worksheet using 0-based index
    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
    
    // Read data from this worksheet...
}

3. Can You Start Importing From a Specific Row (e.g., Row 4)? #

Yes, you can specify a starting row depending on your library:

Option 1: Using OleDb #

Use a range in your SQL query to start from row 4. For example:

// Read all data starting from row 4 (adjust column range as needed)
string query = "SELECT * FROM [Sheet1$A4:XFD]";

If your headers are in row 3 and data starts at row 4, you’ll need to manually set column names by reading row 3 first, then fetch rows 4 onwards.

Option 2: Using EPPlus #

EPPlus makes this straightforward with row indexes (1-based):

using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
{
    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
    int startRow = 4; // Start importing from row 4
    int totalRows = worksheet.Dimension.End.Row;
    int totalCols = worksheet.Dimension.End.Column;

    DataTable dt = new DataTable();

    // Add columns (assuming row 3 is your header row)
    for (int col = 1; col <= totalCols; col++)
    {
        dt.Columns.Add(worksheet.Cells[3, col].Text);
    }

    // Import data from row 4 onwards
    for (int row = startRow; row <= totalRows; row++)
    {
        DataRow dr = dt.NewRow();
        for (int col = 1; col <= totalCols; col++)
        {
            dr[col - 1] = worksheet.Cells[row, col].Text;
        }
        dt.Rows.Add(dr);
    }

    // Bind dt to your DataGridView...
}

内容的提问来源于stack exchange，提问作者sam