Color non-consecutive cells in an Excel sheet

UPDATE 2: I still want to be able to make a range object with non-consecutive ranges and even though my colleagues have given up on it I personaly hate not finishing projects. If anyone can tell me why dotNets answer still gives me that error message please let me know. Edit: This is what happens:

xlValues is set as an Excel.Range object.

I have tried the following as well, all giving me the same error:

//xlValueRange = xlSheet...
.get_Range("A1:A5,A15:A25,A50:A65");
.UsedRange.Range["A1:A5,A15:A25,A50:A65"];
.Range["A1:A5,A15:A25,A50:A65"];

xlApp.ActiveWorkbook.ActiveSheet.Range["A1:A5,A15:A25,A50:A65"];
//I have also tried these alternatives with ".Select()" after the brackets and 
//", Type.Missing" inside the brackets

//This works though...
xlSheet.Range["A1:A5"];

I'm trying to recolor specific cells in an excel sheet, I have found a solution by using two loops but it's simply too slow. Running through a column of 30 000 cells takes minutes.

I have never done anything like this before and I used this tutorial to get me started.

This solution uses a bool array with cells to be colored set to true.(recolored)

//using Excel = Microsoft.Office.Interop.Excel;

xlApp = new Excel.Application();
xlApp.Visible = true;
xlBook = xlApp.Workbooks.Add(Type.Missing);
xlSheet = (Excel.Worksheet)xlBook.Sheets[1];

for (int i = 1; i < columns + 1; i++)
{
    for (int j = 1; j < rows + 1; j++)
    {
        if (recolored[j, i])
            xlSheet.Cells[j+1, i+1].Interior.Color = Excel.XlRgbColor.rgbRed;
        }
    }
}

What I would like to do is something like this:

Excel.XlRgbColor[,] color;
//Loop to fill color with Excel.XlRgbColor.rgbRed at desired cells.

var startCell = (Excel.Range)xlSheet.Cells[1, 1];
var endCell = (Excel.Range)xlSheet.Cells[rows, columns];
var xlRange = xlSheet.Range[startCell, endCell];

xlRange.Interior.Color = color;

This one gives me an error on the final line though;

Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))


My first guess would be to make an Excel.Range object that covers the cells I want to have red and use that object in place of xlRange, something like this:

RangeObject.Interior.Color = Excel.XlRgbColor.rgbRed;

I don't know if it's possible to make an Excel.Range object with gaps like that though, I could use some help on this one.


UPDATE: I have come up with another solution that's a lot faster, it's still pretty slow though and since this question isn't answered yet I though I'd update my progress.

Here's what the code looks like right now. This loop is also used to collect data for the sheet but I left those parts out:

for (int i = 1; i < columns + 1; i++)
{
    for (int j = 1; j < rows + 1; j++)
    {
        if (UserChoseToAddColor)
        {
            if (/*Reached the first cell for a range*/)
            {
                ColorStartCell = xlSheet.Cells[j, i];
            }
            else if (/*Reached the last cell for a range*/)
            {
                ColorEndCell = xlSheet.Cells[j, i];

                xlColorRange = xlSheet.Range[ColorStartCell, ColorEndCell];

                //Color that range.
                xlColorRange.Interior.Color = Excel.XlRgbColor.rgbRed;
            }
        }
    }
    backgroundWorker1.ReportProgress(i);
}

A column of 30 000 rows takes around a second to complete, and the full sheet (100-300 columns) is done in a couple of minutes. Is it possible to speed it up even more?

Answers


You can select non-consecutive cells by using comma-separated list of ranges like this:

this.Application.ActiveWorkbook.ActiveSheet.Range["A2:A4,B3:B16"].Select();

You can then re-color the selection using:

Selection.Interior.Color = ColorTranslator.ToOle(Color.Yellow);

This will get rid of the coloring loop you're having trouble with.

Also, in a VSTO add-in, you should normally never need to do new Excel.Application() in your code. this.Application in the Add-in class should give you access to the active instance of Excel.

UPDATE

Here's a piece of code that should help you pin-point your problem. I added a Ribbon to my add-in and a simple button to the Ribbon. Behind the click event of this button, I have added the following code:

    private void button1_Click(object sender, RibbonControlEventArgs e)
    {
        try
        {
            var App = Globals.ThisAddIn.Application;

            if (App == null)
                System.Windows.Forms.MessageBox.Show("App is null");
            else
            {
                var Sheet = App.ActiveSheet;

                if (Sheet == null)
                    System.Windows.Forms.MessageBox.Show("Sheet is null");
                else
                {
                    var Rng = Sheet.Range["A1:A5,A15:A25,A50:A65"];

                    if (Rng == null)
                        System.Windows.Forms.MessageBox.Show("Rng is null");
                    else
                    {
                        Rng.Select();
                    }
                }
            }
        }
        catch (Exception ee)
        {
            System.Windows.Forms.MessageBox.Show("Exception: " + ee.Message);
        }
    }

On my end this code runs successfully and selects the non-contiguous range of cells. Try this on your end and let me know what you see.

UPDATE 2

The same code works for me in a WinForms application with reference to Excel 14.0 (will hopefully work with Excel 12.0 too). Just a couple of minor changes are required. Here's the full code.

    private void button1_Click(object sender, RibbonControlEventArgs e)
    {
         try
        {
            var App = new Microsoft.Office.Interop.Excel.Application();

            if (App == null)
                System.Windows.Forms.MessageBox.Show("App is null");
            else
            {
                App.Workbooks.Add();

                var Sheet = App.ActiveSheet;

                if (Sheet == null)
                    System.Windows.Forms.MessageBox.Show("Sheet is null");
                else
                {

                    Microsoft.Office.Interop.Excel.Range Rng = Sheet.get_Range("A1");

                    Rng.Select();

                    Rng = Sheet.get_Range("A1:A5,A15:A25,A50:A65");

                    if (Rng == null)
                        System.Windows.Forms.MessageBox.Show("Rng is null");
                    else
                    {
                        Rng.Select();

                        App.Selection.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbYellow;

                        App.ActiveWorkbook.SaveAs("testtest.xlsx");

                        App.Quit();
                    }
                }
            }
        }
        catch (Exception ee)
        {
            System.Windows.Forms.MessageBox.Show("Exception: " + ee.Message);
        }
    }

Need Your Help

codeigniter - block ip if it exceeds x tries per y time

mysql apache codeigniter security ddos

Is there any "smart" way to block an IP if it accesses my project more than X times in Y time? I know the proposed way would be to block such cases from apache level but the client asks for extra

JSON POST Request on the iPhone (Using HTTPS)

iphone objective-c json ssl https

I have a WCF service hosted and I'm trying to use it within an iPhone app as a JSON POST request. I plan on using the JSON serializer later, but this is what I have for the request:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.