c# - c#：有没有办法从数据开始的地方检索excel中的单元格地址？

Question

我正在尝试将 excel 数据从一张表复制到另一张表。它工作正常，但问题是：在源文件中，如果数据不是从单元格 A1 开始（考虑下图），在这种情况下，我想从单元格B5复制数据。这里Some header不需要。实际数据从Emp ID单元格开始。

我尝试过的是，我可以提供一个textbox将单元格地址输入其中，然后开始从提供的单元格地址复制数据。但这引入了人工干预。我希望它自动化。对此的任何帮助表示赞赏。谢谢您的帮助。

标签： c#winformsexcel-interop

Answer 1

假设一些基本标准，下面的代码应该做到这一点。我假设的标准是：1）如果一行包含任何合并的单元格（例如您的“某些标题”），那么那不是起始行。2) 起始单元格将包含右侧单元格和其下方单元格中的文本。

private static bool RowIsEmpty(Range range)
{
  foreach (object obj in (object[,])range.Value2)
  {
    if (obj != null && obj.ToString() != "")
    {
      return false;
    }
  }

  return true;
}

private static bool CellIsEmpty(Range cell)
{
  if (cell.Value2 != null && cell.Value2.ToString() != "")
  {
    return false;
  }

  return true;
}

private Tuple<int, int> ExcelFindStartCell()
{
  var excelApp = new Microsoft.Office.Interop.Excel.Application();
  excelApp.Visible = true;

  Workbook workbook = excelApp.Workbooks.Open("test.xlsx");
  Worksheet worksheet = excelApp.ActiveSheet;

  // Go through each row.
  for (int row = 1; row < worksheet.Rows.Count; row++)
  {
    Range range = worksheet.Rows[row];

    // Check if the row is empty.
    if (RowIsEmpty(range))
    {
      continue;
    }

    // Check if the row contains any merged cells, if so we'll assume it's
    // some kind of header and move on.
    object mergedCells = range.MergeCells;
    if (mergedCells == DBNull.Value || (bool)mergedCells)
    {
      continue;
    }

    // Find the first column that contains text in this row.
    for (int col = 1; col < range.Columns.Count; col++)
    {
      Range cell = range.Cells[1, col];

      if (CellIsEmpty(cell))
      {
        continue;
      }

      // Now check if the cell to the right also contains text.
      Range rightCell = worksheet.Cells[row, col + 1];

      if (CellIsEmpty(rightCell))
      {
        // No text in right cell, try the next row.
        break;
      }

      // Now check if cell below also contains text.
      Range bottomCell = worksheet.Cells[row + 1, col];

      if (CellIsEmpty(bottomCell))
      {
        // No text in bottom cell, try the next row.
        break;
      }

      // Success!
      workbook.Close();
      excelApp.Quit();
      return new Tuple<int, int>(row, col);
    }
  }

  // Didn't find anything that matched the criteria.
  workbook.Close();
  excelApp.Quit();
  return null;
}