C# 获取 Excel 行范围的最快方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12363163/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fastest way to get an Excel Range of Rows
提问by Gayan Dasanayake
In a VSTO C# project I want to get a range of rows from a set of row indexes.
在 VSTO C# 项目中，我想从一组行索引中获取一系列行。
The row indexes can be for example like "7,8,9,12,14".
行索引可以是例如“7,8,9,12,14”。
Then I want the range "7:9,12,14" rows.
然后我想要范围“7:9,12,14”行。
I now do this:
我现在这样做：
Range rng1 = sheet.get_Range("A7:A9,A12,A14", Type.Missing);
rng1 = rng1.EntireRow;
But it's a bit inefficient due to string handling in range specification.
但是由于范围规范中的字符串处理，它的效率有点低。
sheet.Rows["7:9"]
works but I can't give this
有效，但我不能给这个
sheet.Rows["7:9,12,14"] // Fails
采纳答案by Reafidy
Try this:
尝试这个：
Sheet.Range("7:9,12:12,14:14")
EDIT: Sorry if using VSTO in C# it should have been:
编辑：抱歉，如果在 C# 中使用 VSTO 应该是：
sheet.get_Range("7:9,12:12,14:14", Type.Missing)
回答by Siddharth Rout
I am not an expert in C# but AFAIK you have to use the EntireRow as you have done above. The string that you are looking for can be achieved from the .Addressproperty. For example
我不是 C# 专家，但 AFAIK 你必须像上面那样使用 EntireRow。您正在寻找的字符串可以从.Address属性中获得。例如
private void button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Range xlRange;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlWorkBook = xlexcel.Workbooks.Add();
// Set Sheet 1 as the sheet you want to work with
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlRange = xlWorkSheet.get_Range("A7:A9,A12,A14", misValue);
MessageBox.Show(xlRange.EntireRow.Address);
xlRange = xlWorkSheet.get_Range(xlRange.EntireRow.Address, misValue);
MessageBox.Show(xlRange.Address);
}
So you can write the above as
所以你可以把上面的写成
private void button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Range xlRange;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlWorkBook = xlexcel.Workbooks.Add();
// Set Sheet 1 as the sheet you want to work with
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlRange = xlWorkSheet.get_Range(":,:,:", misValue);
MessageBox.Show(xlRange.Address);
}
See the part
看零件
xlRange = xlWorkSheet.get_Range(":,:,:", misValue);
回答by SutharMonil
Here is the code you are looking for:
这是您正在寻找的代码：
int startRow, endRow, startCol, endCol, row,col;
var singleData = new object[col];
var data = new object[row,col];
//For populating only a single row with 'n' no. of columns.
var startCell = (Range)worksheet.Cells[startRow, startCol];
startCell.Value2 = singleData;
//For 2d data, with 'n' no. of rows and columns.
var endCell = (Range)worksheet.Cells[endRow, endCol];
var writeRange = worksheet.Range[startCell, endCell];
writeRange.Value2 = data;
You can have entire range, be it 1 dimensional or 2 dimensional array of cells.
您可以拥有整个范围，无论是一维还是二维的单元格数组。
This method is particularly helpful while looping through the entire excel sheet and populating data where and when required.
这种方法在遍历整个 Excel 工作表并在需要的地方和时间填充数据时特别有用。
回答by TheAtomicOption
Reafidy's edited answer is a great start, but I wanted to expand on it more than I could do in a comment. sheet.get_Range(rangeselect)is much faster than going row by row, but one thing I haven't seen mentioned yet is that the get_Range parameter has a 255 character limit.
Refidy 编辑后的答案是一个很好的开始，但我想对其进行扩展，而不是在评论中做的更多。sheet.get_Range(rangeselect)比逐行快得多，但我还没有看到提到的一件事是get_Range 参数有 255 个字符的限制。
To get around that limitation, construct a set of ranges like "8:8,10:13,14:55" as normal then use a variant of this code:
为了解决这个限制，像平常一样构造一组像“8:8,10:13,14:55”这样的范围，然后使用以下代码的变体：
string rangeSelectPart;
while (rangeSelect.Length >= 255)
{
rangeSelectPart = rangeSelect.Substring(0, rangeSelect.Substring(0,255).LastIndexOf(','));
Range multiRangePart = sheet.get_Range(rangeSelectPart, Type.Missing);
//do something with the range here using multiRangePart
rangeSelect= rangeSelect.Substring(rangeSelectPart.Length + 1);
}
Range multiRange = sheet.get_Range(rangeSelect, Type.Missing);
// do the same something with the last part of the range using multiRange
// now that the remaining rows are described in less than 255 characters
This will be significantly faster than doing operations on individual rows, but also won't fail when presented with large non-contiguous row sets.
这将比在单个行上执行操作快得多，但在呈现大型非连续行集时也不会失败。
Note that SutharMonil's answer is way fasterIFF setting values in contiguous rectangular ranges. The bottleneck going from C# to excel is usually the repeated calls through the COM objects which block while being created and updated, and his answer nicely consolidates calls.
请注意，SutharMonil 的答案是在连续矩形范围内更快的IFF 设置值。从 C# 到 excel 的瓶颈通常是通过 COM 对象的重复调用，这些对象在创建和更新时会阻塞，他的回答很好地整合了调用。
Unfortunately in my testing so far, trying to use it to work with non-string properties that aren't of type string has resulted in a type error. For example:
不幸的是，到目前为止，在我的测试中，尝试使用它来处理非字符串类型的非字符串属性会导致类型错误。例如：
object[,] colors;
//use C# to set appropriate colors to each location in array...
for(int i = 0; i < colors.get_Length(0); i++){
for(int j = 0; j < colors.get_Length(1); j++){
colors[i,j] = XlThemeColor.xlThemeColorAccent6;
}
}
//below causes a type error
formatRange.Interior.ThemeColor = color;
I'll try to remember to update if I get it to work.
如果我让它工作，我会尽量记住更新。
Lastly for repeated operations set Globals.ThisAddIn.Application.ScreenUpdating = false;and then set it to true when you're done. Without this, Excel stops to update the screen after each set of range properties is updated and that can add a lot of time to the operation.
最后为重复操作设置 Globals.ThisAddIn.Application.ScreenUpdating = false;，然后在完成后将其设置为 true。如果没有这个，Excel 会在每组范围属性更新后停止更新屏幕，这会给操作增加很多时间。
回答by ángel Ibá?ez
This code assign color to range cells based on criteria:
此代码根据条件为范围单元格分配颜色：
using Microsoft.Office.Interop.Excel;
(...)
var excel = new Application { Visible = true };
Workbook workbook = excel.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet sheet = workbook.Sheets[1];
var i = 2;
foreach (Data d in this.Datos)
{
sheet.Cells[i, 1].Value = d.Fecha;
sheet.Cells[i, 2].Value = d.Ubicacion;
sheet.Cells[i, 3].Value = d.Lote;
sheet.Cells[i, 4].Value = d.ArticuloId;
sheet.Cells[i, 5].Value = d.Articulo;
sheet.Cells[i, 6].Value = d.ColorId;
sheet.Cells[i, 7].Value = d.Color;
sheet.Cells[i, 8].Value = d.StockOriginal;
sheet.Cells[i, 9].Value = d.Diferencia;
sheet.Cells[i, 10].Value = d.StockFinal;
if (d.BackGroundColor == "#FFA061")
sheet.Range[sheet.Cells[i, 1], sheet.Cells[i, 10]].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 160, 97));
i++;
}