Microsoft.Office.Interop.Excel get_Range
public static DataTable ImportExcel(Excel.Worksheet ws)
{
DataTable dt = new DataTable(RemoveSpecialCharacters(ws.Name));
int c = ws.Cells.Find("*", System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious,
false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;
int r = ws.Cells.Find("*", System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious,
false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A1", Missing.Value);
range = range.get_Resize(r, c);
object[,] values = (object[,])range.Value2;
for (int j = 1; j <= c; j++)
{
dt.Columns.Add(RemoveSpecialCharacters(values[1, j].ToString()));
}
dt.Columns.Add("y");
for (int i = 1; i <= r; i++)
{
DataRow drRow = dt.NewRow();
for (int j = 1; j <= c; j++)
{
drRow[j - 1] = (values[i, j] == null) ? "" : values[i, j].ToString();
}
drRow["y"] = i;
dt.Rows.Add(drRow);
}
return dt;
}
private static void OutputExcel(Microsoft.Office.Interop.Excel.Worksheet ws, DataTable dt, int c, int xFrom, int yFrom, int xTo, int yTo)
{
Microsoft.Office.Interop.Excel.Range topRight = ws.Cells[yFrom, xFrom];
Microsoft.Office.Interop.Excel.Range bottomLeft = ws.Cells[yTo, xTo];
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(topRight, bottomLeft);
string[,] array = new string[dt.Rows.Count, 1];
for (int i = 0; i < dt.Rows.Count; i++)
array[i, 0] = dt.Rows[i][c].ToString();
range.Value2 = array;
}