在C#中读取Excel范围内的所有单元格值

Question

假设我有从 E2 到 E16 的范围。如何读取 E2 到 E16 单元格中的值？

- Sathish

Answer 1

您可以尝试像这样的东西。它应该有效。

您可以根据需要在内部指定范围。

this.openFileDialog1.FileName = "*.xls";
  if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
   {
      Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(
         openFileDialog1.FileName, 0, true, 5,
          "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
          0, true); 
     Excel.Sheets sheets = theWorkbook.Worksheets;
     Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
     for (int i = 1; i <= 10; i++)
     {
     Excel.Range range = worksheet.get_Range("A"+i.ToString(), "J" + i.ToString());
     System.Array myvalues = (System.Array)range.Cells.Value;
     string[] strArray = ConvertToStringArray(myvalues);
     }
}

string[] ConvertToStringArray(System.Array values)
{ 

// create a new string array
string[] theArray = new string[values.Length];

// loop through the 2-D System.Array and populate the 1-D String Array
 for (int i = 1; i <= values.Length; i++)
  {
   if (values.GetValue(1, i) == null)
    theArray[i-1] = "";
  else
   theArray[i-1] = (string)values.GetValue(1, i).ToString();
  }

  return theArray;
}

- Phani Kumar PV

---

但如果我已经有了一个固定的范围，比如从单元格和两个单元格传递给一个函数，例如fromcell = A3和Tocell = A8，我需要从A3到A8获取值。 - Sathish

如果您需要在代码中直接给定范围，则需要删除for循环，例如以下内容：Excel.Range range = worksheet.get_Range("E2", "E16"); - Phani Kumar PV

我应该使用哪个命名空间才能获取ConvertToStringArray？ - Sathish

ConvertToStringArray 是一个本地方法，而不是库方法。因此，您需要在代码中包含它，然后使用您放置它的任何命名空间引用它（可能您将其作为私有静态方法放置在读取 Excel 文件的任何类中）。 - user3439632

Answer 2

另一种替代方法。将其作为独立回答发布，因为这样可以减少混淆的空间。

Excel.Application app = new Excel.Application();

Excel.Workbook wbook = null;

Excel.Worksheet wsheet = null;

Excel.Range range = null;

app.Visible = false;

Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

string filepath = inputFile1.Value.ToString();

if (filepath != "")

{

wbook = app.Workbooks.Open(filepath, Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value);



string currentSheet = "Sheet1";

wsheet = (Excel.Worksheet)wbook.Worksheets.get_Item(currentSheet);

range = wsheet.get_Range("B6", "H20");

System.Array myvalues = (System.Array)range.Cells.Value2;

valueArray = ConvertToStringArray(myvalues);



if (app != null)

{

app.Workbooks.Close();

app.Quit();

}

app = null;

wsheet = null;

range = null;

string[] ConvertToStringArray(System.Array values)
{ 

// create a new string array
string[] theArray = new string[values.Length];

// loop through the 2-D System.Array and populate the 1-D String Array
 for (int i = 1; i <= values.Length; i++)
  {
   if (values.GetValue(1, i) == null)
    theArray[i-1] = "";
  else
   theArray[i-1] = (string)values.GetValue(1, i).ToString();
  }

  return theArray;
}

- Phani Kumar PV