[C#] Microsoft.Office.Interop.Excel get_Range

  • 1078
  • 0

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;
        }