0

There is a MS Excel sheet filled with one matrix data.

How can I get only the numbers region?

Right now I'm extracting the data range with the workSheet.UsedRange. But if I delete one matrix line, it will be considered in the usedRange, even though the line has been deleted! That's just because I have previously used those cells.

So, how can I get only the range with actual things (numbers) on it?

The code:

public Array ReadData(string fullPath, Size size)
{
    Excel.Application application;
    Workbook workBook;
    Worksheet workSheet;
    Range range;

    application = new Excel.ApplicationClass();
    workBook = application.Workbooks.Open(fullPath, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    workSheet = (Worksheet)workBook.Worksheets.get_Item(1);

    //HERE IS THE PROBLEM
    range = workSheet.UsedRange; 

    if (!size.IsEmpty)
    {
        Size availableSize = new Size(range.Columns.Count, range.Rows.Count);
        if ((availableSize.Width < size.Width) || (availableSize.Height < size.Height))
        {
            string msg = string.Format("Available data range ({0}) at the sheet is smaller than needed size ({1})", availableSize, size);
            throw new ArgumentException(msg);
        }
        else if ((availableSize.Width > size.Width) || (availableSize.Height > size.Height))
        {
            range = workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[size.Width, size.Height]);
            string msg = string.Format("Available data range ({0}) at the sheet is bigger than needed size ({1})", availableSize, size);
            Trace.TraceInformation(msg);
        }
    }

    Array data;

    data = (Array)range.Cells.Value2;

    return data;
}

The possible solution was post in VB I think. In C# will be:

        range = workSheet.UsedRange;
        range = range.CurrentRegion.SpecialCells(XlCellType.xlCellTypeConstants, XlSpecialCellsValue.xlNumbers);

but it raises an excpetion "no cells found"

Pedro77
  • 5,176
  • 7
  • 61
  • 91
  • To clarify: what is the result of this `data = (Array)range.Cells.Value2;` right now? does it also include deleted rows (as empty string or smth)? – Orkun Feb 16 '12 at 14:00
  • Well, now its is working if I edit the cell and delet the item. It does not change the range anymore, maybe it is because of the excel 2010, I dont know. – Pedro77 Feb 16 '12 at 16:01

1 Answers1

1

I don't really know c#, but in Excel, you use
Range("yourRange").SpecialCells(xlCellTypeConstants, xlNumbers), or
Range("yourRange").CurrentRegion.SpecialCells(xlCellTypeConstants, xlNumbers)

http://msdn.microsoft.com/en-us/library/aa213567(v=office.11).aspx
or
SpecialCells in VSTO
or
How to get the range of occupied cells in excel sheet

Community
  • 1
  • 1
iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • What SpecialCells means? xlCellTypeConstants? Can you explain in more details? – Pedro77 Feb 16 '12 at 16:03
  • See edit ...and search a bit for yourself as well ! I gave you a golden answer, and you're not able to type just THAT word in your favorite search engine ? – iDevlop Feb 16 '12 at 16:15
  • 1
    In C#: range = range.CurrentRegion.SpecialCells(XlCellType.xlCellTypeConstants, XlSpecialCellsValue.xlNumbers); – Pedro77 Feb 16 '12 at 16:33
  • unfortunatly it is not working, when I use range = range.CurrentRegion.SpecialCells(XlCellType.xlCellTypeConstants, XlSpecialCellsValue.xlNumbers); I get an exception "no cells found". Ive tried range = workSheet.UsedRange.SpecialCells(XlCellType.xlCellTypeAllFormatConditions); but it raises the same exception. – Pedro77 Feb 17 '12 at 11:33
  • see added link in my answer ? (I cannot add links in comments) – iDevlop Feb 17 '12 at 14:02
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/7852/discussion-between-idevlop-and-pedro77) – iDevlop Feb 17 '12 at 14:17