Investigations of the horrendous memory leak in my Office 2007 Ribbon application led me (by trial and error tbh) to an issue with the Interop Sheet.Rows.Count. I was using this - naively - to count the number of rows on a particular sheet, and was then using that figure to create a range which I cleared.
The only problem is that Sheet.Rows.Count and Sheet.Columns.Count return the maximum possible Rows/Columns on a sheet even if they are not populated with data. This is not what I wanted to do. I wanted to know the dimensions of the populated area so that I could blank it.
Coupled to this is that Range.Clear() seems to retain memory for a long period of time - this may be due however to the size of the range created by the eroneous returns of Sheet.Rows.Count/Sheet.Columns.Count.
I am now in the process of finding a different way to do this, and may have a possible solution. I will post it up when I have confirmed it.
Subscribe to:
Post Comments (Atom)
9 comments:
I was hoping to see your solution. I am looking into the same issue. I will probably look for information in the first row and then count the number of items that are not null.
This might have an impact on performance. Iterating through the Excel data does not seem to be too fast. Also formatting might prove problematical in this.
I have semi-solved this by maths - I know how data I got in last time, so I can work out how much I need to clear by purely counting the number of rows I'm reading in. Not a great solution, but not as agricultural as inspecting each row in Excel.
You can use either
1)
Range usedRange = Range.UsedRange;
Range entireRows = usedRange.EntireRows;
int count = entireRows.Count;
where count is (real)number of the rows used on your sheet.
In the case that you want to avoid counting empty(null) rows you can use following:
2)
Range usedRange = Range.CurrentRange;
Range entireRows = usedRange.EntireRows;
int count = entireRows.Count;
where currentRange is a range bounded by any combination of blank rows and blank columns.
Hope this helps.
Also, if you have problems with accessing this methods without exception due to COM object, let me know.there is a workaround for that as well.
Good luck!
Jelena
uuups,sorry..correction..
1)
Range usedRange = Worksheet.UsedRange;
Range entireRows = usedRange.EntireRow;
int count = entireRows.Count;
2)
Range usedRange = Worksheet.UsedRange;
Range currentRegion = usedRange.CurrentRegion;
Range entireRows = currentRegion.EntireRow;
int count = entireRows.Count;
I've found that UsedRange is not always reliable - however I may give this another go as I've done lots of other changes since.
i have really complicated application. my excel is dynamically feed from another application, as soon as there are updates from server.and it worked for me.
if u have any problems let me know.
Jelena,
I have implemented UsedRange. It's a little simpler that your suggestion as UsedRange has a Rows.Count property, and this gives me the row count without needing to use EntireRows. i.e.
Range workingRange = mySheet.UsedRange;
int count = workingRange.Rows.Count;
Unless there is a good reason for using EntireRows, this seems to work fine.
Thanks for you input.
- Brian
glad i could help :-)!
in my case i've used EntireRow, but Rows should be fine as well.
After all, more important was/is usage of UsedRange..
J
Post a Comment