Interop Excel - Range Text to multidimensional array

Question

I am reading excel sheet with following code:

  Excel.Range uRange = xlWorkSheet.UsedRange;  
  dynamic data = uRange.Value2;

As you can see I copy used range cell values into dynamic 2D array. One column of selected range is custom formatted and with above technic I am getting just a bunch of numbers instead of nice formatted value when accessing this custom formatted cell (data[x,y]).

If I am accessing only certain cell directly with following code:

 (Excel.Range)xlWorkSheet.Cells[x, y].Text

... I can see the formatted value I need. By accessing each cell separately I am loosing a lot of time because it is slower to access cell by cell inside a loop.

Is it possible to get all Text values from selected range. Something like this:

 Excel.Range uRange =xlWorkSheet.UsedRange; 
 dynamic data = uRange.Text;

When I use above code I am getting empty variable.

I've managed to get text values with uRange.Value. What is the exactly difference between Value and Value2 — Zzz
– Zzz, Commented Jul 11, 2011 at 12:44

rjman · Accepted Answer · 2012-02-28 03:33:14Z

2

Note that if you are using COM interop in Silverlight application to access excel files that approach will not work.

Here's the solution, basically you need to iterate through columns, and get 1-dimensional array for each column. (this approach is very fast)

 foreach (var column in fullRange.Columns)
 {
   // vals will be a one-dimensional array 
   // of all values in that column!
   var vals = column.Value;

   foreach (var val in vals)
   {
       if (val == null) continue;
       string cellValue = val.ToString();
       //Debug.WriteLine(" Value read: " + cellValue);
       //do something with the value...

   }
 }

answered Feb 28, 2012 at 3:33

rjman

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Unmesh Kondolikar · Accepted Answer · 2011-09-14 13:50:04Z

1

Here's how I got it to work (formatted mine as dates):

currentWorksheet.Range["A2", "A10"].NumberFormat = "m/d/yyyy";
dynamic vals = currentWorksheet.Range["A2", "A10"].Value;

edited Sep 14, 2011 at 13:50

Unmesh Kondolikar

9,2924 gold badges41 silver badges53 bronze badges

answered Sep 14, 2011 at 13:46

user944758

111 bronze badge

Comments

Unmesh Kondolikar · Accepted Answer · 2011-09-14 13:50:43Z

0

use Object obj = uRange.Value2

edited Sep 14, 2011 at 13:50

Unmesh Kondolikar

9,2924 gold badges41 silver badges53 bronze badges

answered Aug 5, 2011 at 10:40

SSK

8033 gold badges19 silver badges43 bronze badges

Comments

Sujeet Singh · Accepted Answer · 2018-07-27 12:49:49Z

I had the Requirement: Import excel records in the datatable. So I tried with oledbConnection, but it Excel.exe does not removed from the Task Manager. So I tried below approach, and its working fine.

public void ImportData(string _FileName, int _SheetIndex)
    {
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkbook = null;
        Excel.Worksheet xlWorksheet = null;
        Excel.Range xlRange = null;

        try
        {
            Logger.Write(String.Format("Import Started for File {0}",_FileName));

            xlWorkbook = xlApp.Workbooks.Open(_FileName);
            xlWorksheet = xlWorkbook.Sheets[_SheetIndex];
            xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;

            object[,] objectArray = (object[,])xlRange.Value[Excel.XlRangeValueDataType.xlRangeValueDefault];



        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (xlRange != null)
            {
                Marshal.ReleaseComObject(xlRange);
            }

            if (xlWorksheet != null)
            {
                Marshal.ReleaseComObject(xlWorksheet);
            }

            if (xlWorkbook != null)
            {
                xlWorkbook.Close(Excel.XlSaveAction.xlDoNotSaveChanges);
                Marshal.ReleaseComObject(xlWorkbook);
            }

            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);
            Logger.Write(String.Format("Import End for File {0}", _FileName));
        }
    }

Collectives™ on Stack Overflow

Interop Excel - Range Text to multidimensional array

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related