Excel表格中的颜色非连续单元格 [英] Color non-consecutive cells in an Excel sheet

问题描述

发生这种情况：

xlValues 设置为 Excel.Range object。

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"];

---

我试图重新着色一个特定的单元格excel表，我已经找到一个解决方案，使用两个循环，但它只是太慢了。运行一个30 000个单元格的列需要几分钟。

---

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.

此解决方案使用一个布尔数组，其中单元格的颜色设置为true。（重新设计）

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;
        }
    }
}

我想做什么是这样的：

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;

类型不匹配。 （HRESULT的异常：0x80020005（DISP_E_TYPEMISMATCH））

我的第一个猜测是一个 Excel.Range 对象，覆盖我想要红色的单元格，并使用该对象代替xlRange，如下所示：

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;

我不知道是否可以制作一个 Excel.Range 对象与这样的差距，虽然，我可以使用一些帮助这个。

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.

推荐答案

我有同样的问题，原来这是一个坏的列表分隔符 - 在我的情况下，而不是逗号应该有分号。

I had the same problem and it turned out that it was a bad list separator - in my case instead of comma there should be a semicolon.

所以而不是

.Range["A1:A5,A15:A25,A50:A65"];

尝试：

private string listSep = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator;

.Range["A1:A5" + listSep + "A15:A25" + listSep + "A50:A65"];

这篇关于Excel表格中的颜色非连续单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！