Retrieve UsedRange without used blank cells

Question

I'm trying to convert each sheet of an excel file to an image using excel.Interop. What I have so far is

var a = new Microsoft.Office.Interop.Excel.Application();

        try
        {
            Workbook w = a.Workbooks.Open(@"C:\Path\to\excelFile.xlsx");
            foreach (Worksheet ws in w.Sheets)
            {
                string range = ws.PageSetup.PrintArea;
                ws.Protect(Contents: false);            

                Range r = ws.UsedRange;

                r.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);
                Bitmap image = new Bitmap(System.Windows.Forms.Clipboard.GetImage());
                string BitmapName = ws.Name;
                image.Save($@"C:\Path\to\bitmapsToSave\{BitmapName}.bmp");
            }

            a.DisplayAlerts = false;
            w.Close(SaveChanges: false);
        }
        finally
        {
            a.Quit();
        }

And I happen to get an image from the clipboard, which uses the range.CopyPicture method. The thing is that the range I'm getting contains some blank cells, and I read somewhere that once a cell is accessed will extend the range even if it's blank.

That means that my bitmap outputs contain a blank offset which I don't want. I'm trying to select the exact range and I've tried the following

Range r = ws.UsedRange.SpecialCells(XlCellType.xlCellTypeAllFormatConditions);

And this returns my table without headers. I don't know why, but it has something to do with them being frozen

also I tried clearing my sheet of formatting before copying range into clipboard

ws.Columns.ClearFormats();
ws.Rows.ClearFormats();

this works okay, but my table now lost all of the nice formatting it had. (Which also means this works for non-formatted tables, which is nice)

I'm trying to copy the ws instance into a new one, get the cleared Range from it, and copy it to the actual sheet but I can't access them properly

//creates a copy of the worksheet to extract its range
Worksheet _ws = ws;

//These two lines do the magic.
_ws.Columns.ClearFormats();
_ws.Rows.ClearFormats();

Range _r = _ws.UsedRange;
//Copies range of aux ws into current ws
_r.Copy(r);    

This returns the same output of the previous case, a correct cropped range but the image has no excel formatting.

The last thing I have not tried and that's because I don't know why, is to access to the Print Area and cast it somehow as a range.

I want to know how to do this or what I'm doing wrong.

EDIT:

basically with interop

opens an excel file with a table on each sheet
for each sheet get UsedRange of sheet as a Range object
    pass that range into clipboard
    using Copypicture save Clipboard to a bmp image
close file

the range that interop picks is bigger than the one that actually contains data, because if I click on a cell and delete its contents will still be considered as it contains data.

this results in bitmaps that contain my tables, but also a lot of white cells around because they had data once, but not anymore.

Code is in https://github.com/diegoquintanav/Excel2BMP

Answer 1

I assume it has something related to the range returned from UsedRange. This method appears to work properly, however my understanding is that the method may return ranges where there are empty rows at the top, bottom and/or empty columns to the left or right of the used range. Your comment below describes this situation.

_” I read somewhere that once a cell is accessed will extend the range even if it's blank.”

I personally have never experienced this and when this does happen to others… in almost all cases that I have seen, the problem is resolved when an apparently empty cell had some kind of not “normal” formatting or invisible characters. If used range is returning empty rows or columns above, below or to the left of the actual data, there is something in one of the cells. In addition, it should be obvious WHICH cell has this invisible data or formatting. Example:

If there are “apparent” empty rows at the TOP of the used range or “apparent” empty columns to the LEFT of the used range, then the TOP/LEFT cell has either invisible data or some kind of not “normal” cell formatting in one or more of the cells in that row and/or column.

If there are “apparent” empty rows at the BOTTOM of the used range or “Apparent” empty columns to the RIGHT of the used range, then the BOTTOM/RIGHT cell has either invisible data or some kind of not “normal” cell formatting in one or more of the cells in that row and/or column.

Opening the Excel worksheet and selecting one of the problem cells above to remove any data and clear any formatting, will minimum remove an empty row, column or both.

Check those “apparent” empty cells. Delete the contents, AND clear any formatting, and see if the used range does not work as expected.

Edit: Delete the extra rows and columns

Since there is a plethora of things that will cause the usedRange property to return these empty rows and columns, this includes merged cells, the easiest way to do this is by “Deleteing” the cells completely. Fortunately, the pictures show you how many of these rows or columns you need to delete.

Using the sheet named Cont4 from the link you posted as an example, UsedRange returns ten (10) extra empty columns on the right and 39 empty rows at the bottom. I found a merged cell somewhere in col AA. We can try to figure out what is causing this on a cell-by-cell basis, however it may be easier to simply select all the columns starting from column “R” to column “AA” or any column PAST column “AA”. In other words, select 10 or more columns to the right of the data. With those columns selected, right click into the selection and select “Delete”. This should obviously remove any kind of formatting/merging in those cells and problem solved. Same thing applies to the empty rows at the bottom, select at least 39 or more rows below the data then right click on the selection and select “Delete”.

The top row is a bit different. If there are one or more empty rows at the top of the worksheet, then one extra top empty row will be returned from UsedRange. I was unable to get rid of this extra top row unless I deleted all empty top rows.

Lastly, the code I pointed to in my last comment would remove these empty rows and columns and the top extra rows also, because it completely ignores formatting and merging and will remove any row that is completely empty without regard to the formatting. As long as you have the code working for you then that is all that matters. Good luck.

Answer 2

I finally managed to get the bitmaps correctly, using the answer in

How to find first and last cell in ExcelInterop and graph range in C#

I replaced

 Range r = ws.UsedRange;

With

 Range r = ws.get_Range("B2", System.Type.Missing).CurrentRegion;

I have to assume that every table on each sheet starts in the same Cell, in this case, B2. This gives me every bitmap as it should.